scheduling jobs that query linked servers

I have some SQL Servers that are linked and have created
a stored procedure on my primary SQL Server that queries
the linked servers. I also created a new login ID for
this.
I can run the stored proc anytime and it works great.
However, when I attempt to execute the stored proc from a
scheduled job I get the message: "Remote access not
allowed for Windows NT user activated by SETUSER."
I don't see how to get around this and still use the job
scheduler. Do I really have to use isql and put it in
scheduler software running outside MSSQL?
Can I run this T-SQL inside the scheduler with a
configuration switch somewhere? Or is this a "feature"
of MSSQL?
thanksIf you go to step properties, there is a drop down
box "run as user", from that list select the login that
has access to the linked server, that login also need to
have some access privileges to the database from which the
step is run.
hth.
>--Original Message--
>I have some SQL Servers that are linked and have created
>a stored procedure on my primary SQL Server that queries
>the linked servers. I also created a new login ID for
>this.
>I can run the stored proc anytime and it works great.
>However, when I attempt to execute the stored proc from a
>scheduled job I get the message: "Remote access not
>allowed for Windows NT user activated by SETUSER."
>I don't see how to get around this and still use the job
>scheduler. Do I really have to use isql and put it in
>scheduler software running outside MSSQL?
>Can I run this T-SQL inside the scheduler with a
>configuration switch somewhere? Or is this a "feature"
>of MSSQL?
>thanks
>
>.
>|||I tried that before and it did not work. I have not been
able to run this via any user in that drop down.
I have a situation where I can login via isql (sql server
authentication - not Windows auth.) and I am able to
execute the stored proc just fine. The stored proc
queries linked servers and works fine.
However when I try to use the Job Scheduler, I get the
error: "Remote access not allowed for Windows NT user
activated by SETUSER." When I attempt to run the job as
[self] I get: "Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'."
Unless there is a hidden config option somewhere it
appears that the job scheduler cannot be used for remote
access to linked servers unless via a windows auth. login.
Is that true? If this is supposed to be a "security
feature" then it is pointless as far as I can tell.
We're on 2000 sp 3a
Jeff
>--Original Message--
>If you go to step properties, there is a drop down
>box "run as user", from that list select the login that
>has access to the linked server, that login also need to
>have some access privileges to the database from which
the
>step is run.
>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
created
>>a stored procedure on my primary SQL Server that
queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>.
>|||I tried it myself and came to same conclusion as you for
now that you have to use windows auth. to access linked
servers via a jobschedule.
And looks like the problem is with the setuser command,
because when using nt auth. sqlagent is not using setuser
command, and if you use sql auth. then sqlagent is using
the setuser command. I tried this in QA also using setuser
for an nt auth. user who has sysadmin rights on both
servers and came back with the same error message "emote
access not allowed for Windows NT user activated by
SETUSER."
I will post more if I find something.
>--Original Message--
>I tried that before and it did not work. I have not been
>able to run this via any user in that drop down.
>I have a situation where I can login via isql (sql server
>authentication - not Windows auth.) and I am able to
>execute the stored proc just fine. The stored proc
>queries linked servers and works fine.
>However when I try to use the Job Scheduler, I get the
>error: "Remote access not allowed for Windows NT user
>activated by SETUSER." When I attempt to run the job as
>[self] I get: "Login failed for user 'NT
>AUTHORITY\ANONYMOUS LOGON'."
>Unless there is a hidden config option somewhere it
>appears that the job scheduler cannot be used for remote
>access to linked servers unless via a windows auth. login.
>Is that true? If this is supposed to be a "security
>feature" then it is pointless as far as I can tell.
>We're on 2000 sp 3a
>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login that
>>has access to the linked server, that login also need to
>>have some access privileges to the database from which
>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>created
>>a stored procedure on my primary SQL Server that
>queries
>>the linked servers. I also created a new login ID for
>>this.
>>I can run the stored proc anytime and it works great.
>>However, when I attempt to execute the stored proc from
>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>job
>>scheduler. Do I really have to use isql and put it in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>.
>|||This just gets stranger and stranger. On a hunch I set
up execution of my stored proc by a DTS package. I
schedule it and voila! it works!!?! It's not my
preferred way of doing this but if this is the kludge I
need to use to employ our of these inconsistent
behaviors, I'm going to use it.
Jeff
>--Original Message--
>I tried it myself and came to same conclusion as you for
>now that you have to use windows auth. to access linked
>servers via a jobschedule.
>And looks like the problem is with the setuser command,
>because when using nt auth. sqlagent is not using
setuser
>command, and if you use sql auth. then sqlagent is using
>the setuser command. I tried this in QA also using
setuser
>for an nt auth. user who has sysadmin rights on both
>servers and came back with the same error
message "emote
>access not allowed for Windows NT user activated by
>SETUSER."
>I will post more if I find something.
>
>>--Original Message--
>>I tried that before and it did not work. I have not
been
>>able to run this via any user in that drop down.
>>I have a situation where I can login via isql (sql
server
>>authentication - not Windows auth.) and I am able to
>>execute the stored proc just fine. The stored proc
>>queries linked servers and works fine.
>>However when I try to use the Job Scheduler, I get the
>>error: "Remote access not allowed for Windows NT user
>>activated by SETUSER." When I attempt to run the job
as
>>[self] I get: "Login failed for user 'NT
>>AUTHORITY\ANONYMOUS LOGON'."
>>Unless there is a hidden config option somewhere it
>>appears that the job scheduler cannot be used for
remote
>>access to linked servers unless via a windows auth.
login.
>>Is that true? If this is supposed to be a "security
>>feature" then it is pointless as far as I can tell.
>>We're on 2000 sp 3a
>>Jeff
>>--Original Message--
>>If you go to step properties, there is a drop down
>>box "run as user", from that list select the login
that
>>has access to the linked server, that login also need
to
>>have some access privileges to the database from which
>>the
>>step is run.
>>hth.
>>--Original Message--
>>I have some SQL Servers that are linked and have
>>created
>>a stored procedure on my primary SQL Server that
>>queries
>>the linked servers. I also created a new login ID
for
>>this.
>>I can run the stored proc anytime and it works
great.
>>However, when I attempt to execute the stored proc
from
>>a
>>scheduled job I get the message: "Remote access not
>>allowed for Windows NT user activated by SETUSER."
>>I don't see how to get around this and still use the
>>job
>>scheduler. Do I really have to use isql and put it
in
>>scheduler software running outside MSSQL?
>>Can I run this T-SQL inside the scheduler with a
>>configuration switch somewhere? Or is this
a "feature"
>>of MSSQL?
>>thanks
>>
>>.
>>.
>>.
>.
>

Scheduling a task using DTS

Hey Guys!
I have a SQL Update Query that I want to run automatically everyday andI have read through the posts and I do not understand very clearly howto go about creating one.
The SQL query I want to run is this:
UPDATE Rental
SET TotalFee = ExtraFee + TotalFee
WHERE DaysOverdue >= 0
Once I have made a DTS package. How do I declare it in my coding?
Thanks in advance!
If you have access to add a job in SQL Agent, you can create a job and just run the query. You don't need DTS at all. If you don't have access to create a new job, then you'll have to do something outside of SQL using a different scheduling mechanism. Either way, there's no need for DTS to run a query like this. DTS is more for importing and/or exporting data and objects. Even if you did it in DTS, it's the SQL Agent service you'd use to create and schedule a job to run the DTS package.|||I never knew that there was another way of scheduling a SQL task.
I have tried to create a SQL Agent Task by going to Management >> SQL Server Agent >> Jobs
But for some reason it is not working even through I have scheduled a job so that it will run the query automatically.
I tested the SQL Agent Job by right clicking over my job and selecting 'Start Job'. Then an error occurs:
error 22022SQLServerAgent is not currently running so it cannot be notified forthis action
So how do i go about running it?
|||You have to start the Agent service. Once that's running, it will run the job on the schedule you define. Refer to Books on Line for more details on the Agent service.|||It works now!
Thanks!
sql

Scheduling a query to be executed at given time in SQL Server 2005

Hi friend!

I want to schedule a query or procedure running at given time regularly eg: at 12AM daily. Please tell me how to do that in sql server 2005.

We use SQL server 2005 developer edition.

Thanks in advance.

SS2005 has a scheduller, SS Agent. Make sure it is installed and the service is up and running. You can get access to it through SSManagement Studio. BTW, this is a SSIS forum; if you have further question I will move this thread to a more appropiate forum.

Scheduling a query at regular intervals in sql server 2005

Hi friend!

I want to schedule a query or procedure running at regular intervals eg.@. 12AM on everyday. Please help me to do this in SQL server 2005

Thanks/Regards

Antony S

There are 3 choices I can think of off the top of my head;

1/ Use SQL Agent (not available in express) and create a T-SQL job.

2/ Use the windows task scheduler to call SQLCMD and then have it call a .sql file with your code in it.

3/ Use a service broker timer.

|||Books online reference http://msdn2.microsoft.com/en-us/library/ms191439.aspx|||Create a SQL Job and a schedule inside of the job to run everyday at 12am. Make sure that SQLAgent is enabled.sql

scheduling a query

I need to scheduling a query to update some records.
I have to use a script in windows task manager or there is another way?
Many ThanksThe SQL Agent is a wonderful wat to schedule a query. Create a job and out
your query in a step.
"Simone" wrote:

> I need to scheduling a query to update some records.
> I have to use a script in windows task manager or there is another way?
> Many Thanks
>
>|||If you are using an edition of SQL Server which has an enterprise
manager and therefor a UI for SQL Server Agent, you can just create a
job with your query. Add a new job, add a new step (Transact SQL
Execution), paste your statement in there and add a schedule for it and
voil=E1, you=B4re done. iof you do=B4n=B4t have a GUI, you can use the
goold old fashioned AT to do your jobs. Just schedule your job by the
Windows Scheduler or AT, suing OSQL to run the query: OSQL -SServername
-UUsername -PPassword -Q"Here is my query".
HTH, jens Suessmeyer.|||you can create a job and schedule that
Jobs are under management-->Sql Server agent-->Jobs
make it a one step job paste your query or proc call in the step
command window and schedule the job
http://sqlservercode.blogspot.com/

scheduling a query

I need to scheduling a query to update some records.
I have to use a script in windows task manager or there is another way?
Many ThanksIf you are using an edition of SQL Server which has an enterprise
manager and therefor a UI for SQL Server Agent, you can just create a
job with your query. Add a new job, add a new step (Transact SQL
Execution), paste your statement in there and add a schedule for it and
voil=E1, you=B4re done. iof you do=B4n=B4t have a GUI, you can use the
goold old fashioned AT to do your jobs. Just schedule your job by the
Windows Scheduler or AT, suing OSQL to run the query: OSQL -SServername
-UUsername -PPassword -Q"Here is my query".
HTH, jens Suessmeyer.|||The SQL Agent is a wonderful wat to schedule a query. Create a job and out
your query in a step.
"Simone" wrote:
> I need to scheduling a query to update some records.
> I have to use a script in windows task manager or there is another way?
> Many Thanks
>
>|||you can create a job and schedule that
Jobs are under management-->Sql Server agent-->Jobs
make it a one step job paste your query or proc call in the step
command window and schedule the job
http://sqlservercode.blogspot.com/

scheduling a query

I need to scheduling a query to update some records.
I have to use a script in windows task manager or there is another way?
Many Thanks
The SQL Agent is a wonderful wat to schedule a query. Create a job and out
your query in a step.
"Simone" wrote:

> I need to scheduling a query to update some records.
> I have to use a script in windows task manager or there is another way?
> Many Thanks
>
>
|||If you are using an edition of SQL Server which has an enterprise
manager and therefor a UI for SQL Server Agent, you can just create a
job with your query. Add a new job, add a new step (Transact SQL
Execution), paste your statement in there and add a schedule for it and
voil=E1, you=B4re done. iof you do=B4n=B4t have a GUI, you can use the
goold old fashioned AT to do your jobs. Just schedule your job by the
Windows Scheduler or AT, suing OSQL to run the query: OSQL -SServername
-UUsername -PPassword -Q"Here is my query".
HTH, jens Suessmeyer.
|||you can create a job and schedule that
Jobs are under management-->Sql Server agent-->Jobs
make it a one step job paste your query or proc call in the step
command window and schedule the job
http://sqlservercode.blogspot.com/

Scheduled Job Sometimes Error Sometimes Not

I have a scheduled job contain 2 steps. Step 1 is execute DTS Package
using CmdExec 'DTSRun', step 2 using TSQL to execute just simple query.
This job execute recurely every day, every 6 hours.
The problem is, sometimes this job was successfull, but sometimes
failed with error as below :

...
...
Process Exit Code -1073741819. The step failed.

Please help me, what the cause of the error?
Any help would be greatly appreciated.Resant (resant_v@.yahoo.com) writes:
> I have a scheduled job contain 2 steps. Step 1 is execute DTS Package
> using CmdExec 'DTSRun', step 2 using TSQL to execute just simple query.
> This job execute recurely every day, every 6 hours.
> The problem is, sometimes this job was successfull, but sometimes
> failed with error as below :
> ...
> ...
> Process Exit Code -1073741819. The step failed.
> Please help me, what the cause of the error?
> Any help would be greatly appreciated.

In View Job History, there is a check box for "Show step details". Check
that box, and you may find some more details.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply.
The error that I've post is taken after I've checked "Show step
details". OK, I'll post the error again completely :

Executed as user: XXXX\xxxx. ...un OnStart:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_10 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTS... Process
Exit Code -1073741819. The step failed.|||Resant (resant_v@.yahoo.com) writes:
> Thanks for your reply.
> The error that I've post is taken after I've checked "Show step
> details". OK, I'll post the error again completely :
> Executed as user: XXXX\xxxx. ...un OnStart:
> DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_6 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_10 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_10 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
> DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_3 DTSRun OnStart: DTSStep_DTSDataPumpTask_2
> DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
> DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
> DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_4
> DTSRun OnStart: DTSStep_DTSDataPumpTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_4 DTSRun OnStart: DTSStep_DTS... Process
> Exit Code -1073741819. The step failed.

That's pretty anonymous. Unfortunately I don't know DTS, so I don't
have any suggestions to offer. Maybe the people in
microsoft.public.sqlserver.dts has any ideas.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Some Googling suggests that the error may be caused by a Windows bug:

http://support.microsoft.com/defaul...kb;EN-US;306497
http://groups-beta.google.com/group...lr=&sa=N&tab=wg

If you don't already have the latest servicepacks for Windows and
MSSQL, it would probably be worth installing them to see if that
resolves the issue. Since a few people seem to have seen this error in
connection with a virus infection, that would be something else to
check. If this doesn't help, then it might be worth calling Microsoft.

Simon

Scheduled DTS import and Delete Query

Hi there,
I am new to SQL Server, we are running SQL Server 7
I am having some trouble with creating a delete query.
Basically, i have developed a Access database that exports data to a .csv
file, to be imported to SQL server via a scheduled DTS package.
Most of the .csv files will only have new data to ammend to the SQL tables
which is fine doing the above.
But some of the .csv files have the entire data dump of the Access table and
needs to imported over the current data in the SQL table.
What i was thinking was to make a Query/View to delete the data in the
relevant table and then call the DTS package.
I have no idea how to do this - View? Stored Procedure?
But ideally it would delete the data and then call the DTS package, all in
one function.....
Also, would it then be possible to schedule/automate this service?
Thanks for any help
Regards
GuyOK
Reimporting all the data is fine if you have
A. No constraints reliant on the data
B. Your quantity of data is not too large
Personally I would do things like this
1. Have a working table in a staging area to hold each CSV.
2. Import each CSV into their respective staging table
3. Use TSQL statements to compare the working table data with that is in
your destination tables. By comparing Key values you can determine whether
the Data in the real tables requires
1. Adding
2. Updating
3. Deleteing.
As for scheduling then yes you can do this using SQL Server Agent and a Job.
Be careful to read this though
http://support.microsoft.com/?kbid=269074
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Guy Hocking" <guy.hocking@.bradflack.com> wrote in message
news:O6KLUlnsDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> I am new to SQL Server, we are running SQL Server 7
> I am having some trouble with creating a delete query.
> Basically, i have developed a Access database that exports data to a .csv
> file, to be imported to SQL server via a scheduled DTS package.
> Most of the .csv files will only have new data to ammend to the SQL tables
> which is fine doing the above.
> But some of the .csv files have the entire data dump of the Access table
and
> needs to imported over the current data in the SQL table.
> What i was thinking was to make a Query/View to delete the data in the
> relevant table and then call the DTS package.
> I have no idea how to do this - View? Stored Procedure?
> But ideally it would delete the data and then call the DTS package, all in
> one function.....
> Also, would it then be possible to schedule/automate this service?
> Thanks for any help
> Regards
> Guy
>|||In article <O6KLUlnsDHA.2244@.TK2MSFTNGP09.phx.gbl>, Guy Hocking
<guy.hocking@.bradflack.com> writes
>Hi there,
>I am new to SQL Server, we are running SQL Server 7
>I am having some trouble with creating a delete query.
>Basically, i have developed a Access database that exports data to a .csv
>file, to be imported to SQL server via a scheduled DTS package.
>Most of the .csv files will only have new data to ammend to the SQL tables
>which is fine doing the above.
>But some of the .csv files have the entire data dump of the Access table and
>needs to imported over the current data in the SQL table.
>What i was thinking was to make a Query/View to delete the data in the
>relevant table and then call the DTS package.
>I have no idea how to do this - View? Stored Procedure?
>But ideally it would delete the data and then call the DTS package, all in
>one function.....
>Also, would it then be possible to schedule/automate this service?
>Thanks for any help
>Regards
>Guy
>
You can use the Execute SQL Task to run a Delete statement on your
table. Just link this with workflow to run before your the export
(DataPump) task. You will need to open the package manually to add this,
if still using the Wizard only.
--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org|||Thanks for that Allan,
I dont think the amount of data isnt too much (how much is too much?), and i
would rather to all the guinea-pig work in Access. The data is currently
exported into another table in Access with the same naming convention and
design as its SQL equivalent, then the data is exporeted to CSV, once done
the data is tagged in access so it is not exported again.
However, some of the tables will be very different as the data will change
frequently, it seems alot easier to re-import as its only 30-40k of records,
nothing monsterous and it doesnt take an age as iv tested it.
This difficulty is i have no idea where to write the query? view, stored
procedure, i really dont know........i cant seem to create a "delete"
function view, it only seems to do the SELECT function.
I am very new to SQL, so i apoligise for my ignorance
Guy
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:Or4GN9nsDHA.536@.tk2msftngp13.phx.gbl...
> OK
> Reimporting all the data is fine if you have
> A. No constraints reliant on the data
> B. Your quantity of data is not too large
> Personally I would do things like this
> 1. Have a working table in a staging area to hold each CSV.
> 2. Import each CSV into their respective staging table
> 3. Use TSQL statements to compare the working table data with that is in
> your destination tables. By comparing Key values you can determine
whether
> the Data in the real tables requires
> 1. Adding
> 2. Updating
> 3. Deleteing.
> As for scheduling then yes you can do this using SQL Server Agent and a
Job.
> Be careful to read this though
> http://support.microsoft.com/?kbid=269074
>
>
> --
> --
> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "Guy Hocking" <guy.hocking@.bradflack.com> wrote in message
> news:O6KLUlnsDHA.2244@.TK2MSFTNGP09.phx.gbl...
> > Hi there,
> >
> > I am new to SQL Server, we are running SQL Server 7
> > I am having some trouble with creating a delete query.
> >
> > Basically, i have developed a Access database that exports data to a
.csv
> > file, to be imported to SQL server via a scheduled DTS package.
> > Most of the .csv files will only have new data to ammend to the SQL
tables
> > which is fine doing the above.
> > But some of the .csv files have the entire data dump of the Access table
> and
> > needs to imported over the current data in the SQL table.
> >
> > What i was thinking was to make a Query/View to delete the data in the
> > relevant table and then call the DTS package.
> > I have no idea how to do this - View? Stored Procedure?
> > But ideally it would delete the data and then call the DTS package, all
in
> > one function.....
> >
> > Also, would it then be possible to schedule/automate this service?
> >
> > Thanks for any help
> >
> > Regards
> >
> > Guy
> >
> >
>|||ok allsorted, thanks ever so much 4 your help
Kind regards
guy
"Darren Green" <darren.green@.reply-to-newsgroup-only.uk.com> wrote in
message news:HF+FI9EP4ew$Ewl9@.sqldts.com...
> In article <O6KLUlnsDHA.2244@.TK2MSFTNGP09.phx.gbl>, Guy Hocking
> <guy.hocking@.bradflack.com> writes
> >Hi there,
> >
> >I am new to SQL Server, we are running SQL Server 7
> >I am having some trouble with creating a delete query.
> >
> >Basically, i have developed a Access database that exports data to a .csv
> >file, to be imported to SQL server via a scheduled DTS package.
> >Most of the .csv files will only have new data to ammend to the SQL
tables
> >which is fine doing the above.
> >But some of the .csv files have the entire data dump of the Access table
and
> >needs to imported over the current data in the SQL table.
> >
> >What i was thinking was to make a Query/View to delete the data in the
> >relevant table and then call the DTS package.
> >I have no idea how to do this - View? Stored Procedure?
> >But ideally it would delete the data and then call the DTS package, all
in
> >one function.....
> >
> >Also, would it then be possible to schedule/automate this service?
> >
> >Thanks for any help
> >
> >Regards
> >
> >Guy
> >
> You can use the Execute SQL Task to run a Delete statement on your
> table. Just link this with workflow to run before your the export
> (DataPump) task. You will need to open the package manually to add this,
> if still using the Wizard only.
> --
> Darren Green (SQL Server MVP)
> DTS - http://www.sqldts.com
> PASS - the definitive, global community for SQL Server professionals
> http://www.sqlpass.org
>|||OK
Too much is if it takes too long. Too long is business driven.
In my solution I would advocate inserting ALL records into the staging area
tables and then using ExecuteSQL tasks to do the
INSERT, UPDATE, DELETE queries.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Guy Hocking" <guy.hocking@.bradflack.com> wrote in message
news:O4KsyCosDHA.2208@.TK2MSFTNGP10.phx.gbl...
> Thanks for that Allan,
> I dont think the amount of data isnt too much (how much is too much?), and
i
> would rather to all the guinea-pig work in Access. The data is currently
> exported into another table in Access with the same naming convention and
> design as its SQL equivalent, then the data is exporeted to CSV, once done
> the data is tagged in access so it is not exported again.
> However, some of the tables will be very different as the data will change
> frequently, it seems alot easier to re-import as its only 30-40k of
records,
> nothing monsterous and it doesnt take an age as iv tested it.
> This difficulty is i have no idea where to write the query? view, stored
> procedure, i really dont know........i cant seem to create a "delete"
> function view, it only seems to do the SELECT function.
> I am very new to SQL, so i apoligise for my ignorance
> Guy
>
>
> "Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
> news:Or4GN9nsDHA.536@.tk2msftngp13.phx.gbl...
> > OK
> >
> > Reimporting all the data is fine if you have
> >
> > A. No constraints reliant on the data
> > B. Your quantity of data is not too large
> >
> > Personally I would do things like this
> >
> > 1. Have a working table in a staging area to hold each CSV.
> > 2. Import each CSV into their respective staging table
> > 3. Use TSQL statements to compare the working table data with that is
in
> > your destination tables. By comparing Key values you can determine
> whether
> > the Data in the real tables requires
> >
> > 1. Adding
> > 2. Updating
> > 3. Deleteing.
> >
> > As for scheduling then yes you can do this using SQL Server Agent and a
> Job.
> > Be careful to read this though
> >
> > http://support.microsoft.com/?kbid=269074
> >
> >
> >
> >
> > --
> >
> > --
> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> > "Guy Hocking" <guy.hocking@.bradflack.com> wrote in message
> > news:O6KLUlnsDHA.2244@.TK2MSFTNGP09.phx.gbl...
> > > Hi there,
> > >
> > > I am new to SQL Server, we are running SQL Server 7
> > > I am having some trouble with creating a delete query.
> > >
> > > Basically, i have developed a Access database that exports data to a
> .csv
> > > file, to be imported to SQL server via a scheduled DTS package.
> > > Most of the .csv files will only have new data to ammend to the SQL
> tables
> > > which is fine doing the above.
> > > But some of the .csv files have the entire data dump of the Access
table
> > and
> > > needs to imported over the current data in the SQL table.
> > >
> > > What i was thinking was to make a Query/View to delete the data in the
> > > relevant table and then call the DTS package.
> > > I have no idea how to do this - View? Stored Procedure?
> > > But ideally it would delete the data and then call the DTS package,
all
> in
> > > one function.....
> > >
> > > Also, would it then be possible to schedule/automate this service?
> > >
> > > Thanks for any help
> > >
> > > Regards
> > >
> > > Guy
> > >
> > >
> >
> >
>

Schedule query results to file...

Hi there.
I have many needs. One of these happens to be a requirement to set up a
scheduled task which calls a simple stored procedure and then sent the
resulting record set to a file - preferably in .csv format.
Anybody any ideas how to go about this? My stored proc is very basic (select
* from...) so there's nothing fancy with the resulting records it returns...
.
Any help would greatly appreciated!
All the best,
LenCreate a DTS package that execute the sp and schedule it.
AMB
"len" wrote:

> Hi there.
> I have many needs. One of these happens to be a requirement to set up a
> scheduled task which calls a simple stored procedure and then sent the
> resulting record set to a file - preferably in .csv format.
> Anybody any ideas how to go about this? My stored proc is very basic (sele
ct
> * from...) so there's nothing fancy with the resulting records it returns.
...
> Any help would greatly appreciated!
> All the best,
> Len|||use osql with output file
"len" <len@.discussions.microsoft.com> wrote in message
news:F20E1C59-A4C7-433A-822F-9153B74B0E5C@.microsoft.com...
> Hi there.
> I have many needs. One of these happens to be a requirement to set up a
> scheduled task which calls a simple stored procedure and then sent the
> resulting record set to a file - preferably in .csv format.
> Anybody any ideas how to go about this? My stored proc is very basic
> (select
> * from...) so there's nothing fancy with the resulting records it
> returns....
> Any help would greatly appreciated!
> All the best,
> Len