November 7, 2012 at 7:27 am
Hi -
I am new to SQL express, can someone please guide me to how I can schedule a task to run truncate table command since there is no maintenace plan available with this version of SQL. Thanks.
November 7, 2012 at 7:30 am
Setup a windows scheduled task which executes a batch file that contains the nessesary SQLCMD switches and pass in the command in a SQL file via the -i switch.
November 7, 2012 at 7:36 am
Thank you. I will give it a shot hopfully it works.
November 7, 2012 at 8:02 am
The named pipe for that instance I want to schedule the truncate table task in disabled, I get an error when trying to use sqlcmd. Any other way, please?
November 7, 2012 at 8:15 am
what error do you get?
November 7, 2012 at 8:20 am
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
November 7, 2012 at 8:24 am
Now we fall into the troubleshooting steps of why it cannot connect.
Server up and running?
Correct server name listed in the -S switch?
Can connect via SSMS to the server?
Server set to allow remote connections?
As its SQL Express, you will more than likely have a named instance so you will need to ensure that the -S switch is correctly set -S computername\SQLExpress
November 7, 2012 at 8:40 am
I do have a named instance, and i did use the correct name. What I saw is that named pipe for that instance is disabled. I can connect to the named instance using management studio without a problem.
November 7, 2012 at 8:44 am
Try disabling the named pipes client protocol, it should be the last one it falls back on anyway, typical config is that it goes shared memory, tcp/ip, named pipes.
November 7, 2012 at 8:54 am
Below is what I got. How about using stored procedure? All I need is to have a job truncate two tables from two different databases to get truncated on weekly bases.
HResult 0x274D, Level 16, State 1
TCP Provider: No connection could be made because the target machine actively re
fused it.
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
November 7, 2012 at 8:55 am
There is no agent in SQL Express so the only way to schedule it is to use a Windows task.
That being said do you have a full version of SQL installed anywhere like Standard Edition or Enterprise Edition, which you could utilise its agent to run a SSIS package?
As the error has now changed to target machine refused the connection, I would say something is stopping the communication between the client and the server, are you running SQLCMD from the machine where SQLExpress is installed?
November 7, 2012 at 9:13 am
I am on the machine when trying to run the SQLCMD. I have other SQL versions elsewhere to use, can you please guide me to how I setup a package to connect to remore server? Thanks.
November 8, 2012 at 1:52 am
Create a package and then a connection manager which connects to your SQL Express instance, then drag in a Execute SQL task, give it the connection manager name and the truncate table command, save it to a full SQL installation, then schedule it as a job on that full SQL server. Ensure that the Full SQL server can connect to the SQL Express server otherwise you will get a package failure.
November 8, 2012 at 6:15 am
Great, will try it and see if it works. Thanks.
November 9, 2012 at 8:40 am
FYI... Here is the batch file line that I use to run a .sql script.
sqlcmd -S MyServerName\SQLEXPRESS -E -i C:\SQLScripts\SomeScriptToRun.sql -o C:\SQLBackupLogs\SomeDescription_backuplog.txt
It will write the output log to the SomeDescription_backuplog.txt file, and will run the .sql script noted.
Just make sure the script exists in that path and file name, and does what you want. The -E uses Windows Authentication, so if you do it this way, just make sure the user who sets up the scheduled task to run this batch file has the appropriate Windows Authenticated authority within SQl Server to run the script.
Just put the line above in a .bat file and schedule a Windows task to run it when you want.
Hope this helps.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply