Creating a Batch File to read, create and execute scripts(stored procedures)

Question

1.00/5 (1 vote)

See more:

SQL-Server-2005

Hi,

i tried SQLCMD and created a batch file

-S = DEVELOPER04 stands for my Local Server
-E = for trusted connection
-d = oMBMIS is my database

KR_REPORTS_manual_insert_script.sql is the script
this script will create table KR_REPORTS and populate it with records also included in the script i did.

Here's the batch file:
SQLCMD -SDEVELOPER04 -E -doMBMIS -iC:\mbkSCRIPTS\KR_REPORTS_manual_insert_script.sql

and when I double-click my batch file and check result in my SSMS. There was no KR_REPORTS table.?

Did I miss something?

Posted 8-Feb-12 21:38pm

Alan Tuscano

Add a Solution

3 solutions

Solution 2

Try running it via a CMD prompt - it may be that the script raises a problem which is being reported, but you are missing it because the batch window is immediately destroyed.

Posted 8-Feb-12 21:43pm

OriginalGriff

Comments

Alan Tuscano 9-Feb-12 3:58am

i tried running

SQLCMD -E -dMBMIS -iC:\mbkSCRIPTS\KR_REPORTS_manual_insert_script.sql

in the command prompt and got this error;

HResult 0x2, Level 16, State 1
Named Pipes Provider: could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occureed while establishing a connection to the server.
When connecting to SQL SERVER 2005, this failure 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.

I'm testing batch file in my Local Server named DEVELOPER04

OriginalGriff 9-Feb-12 4:06am

Well - you have your error message!
But - you missed the instance name off the command: try running the batch file directly by typing the name into your cmd prompt. Clearly, you will have to specify or move to the appropriate folder, blah, de blah...

Sekhar Murthy 27-Mar-21 9:38am

Need your help!!

I have one create schema file and 13 create tables .sqfile and 13 stored procedure .sql when we wanted to execute we are clicking on every file and we will point to our database where we wanted to execute can any have the .bat file in ready how to execute with .bat file to save this 27 clicks wanted to execute with one and only one click

Thanks
M.chandra

OriginalGriff 27-Mar-21 9:58am

First off, discover punctuation, and how it helps people understand what you are trying to say to them.

Second, remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.

Third, ask a question, not try to hijack an existing one ... Oh, I see you already did. So you're just being rude and arrogant. I see. Do you think that helps you, at all?

Alan Tuscano 9-Feb-12 4:37am

But i have resolved the "remote server" error, as i'm testing another module that needs the linked servers.

Solution 1

C:\>sqlcmd -S mlk98\sqlexpress -d BomAudit -Q "EXEC RefreshFromPDC_PRDSTR"

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server
"PDCPASSTHRUACCESS" returned message "'J:\Innovations\ENGADMIN
\PDI\PdcDataAudit\PdcPassthru.mdb' is not a valid path. Make sure
that the path name is spelled correctly and that you are connected to
the server on which the file resides.".
Msg 7303, Level 16, State 1, Server MLK98\SQLEXPRESS, Procedure
vewReadPdc_part_bom, Line 3
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "PDCPASSTHRUACCESS".

That is my command line and the resulting error message. I am running it as myself both in the cmd shell and within SSMS.

Posted 8-Feb-12 21:43pm

Rakesh Meel

Comments

Alan Tuscano 9-Feb-12 4:38am

Hi, Rakesh.. could not understand?

Sekhar Murthy 27-Mar-21 9:37am

Need your help!!

I have one create schema file and 13 create tables .sqfile and 13 stored procedure .sql when we wanted to execute we are clicking on every file and we will point to our database where we wanted to execute can any have the .bat file in ready how to execute with .bat file to save this 27 clicks wanted to execute with one and only one click

Thanks
M.chandra

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Alan Tuscano · Accepted Answer · 2012-02-08T22:51:00

Solution 3

I updated my batch file, and it run.

SQLCMD -S DEVELOPER04\MBWIN -doMBMIS -iC:\mbkSCRIPTS\KR_REPORTS_script.sql
pause

I checked oMbMis and KR_Reports tables is created and populated with records.. :)

Posted 8-Feb-12 22:51pm

Alan Tuscano

Updated 8-Feb-12 22:52pm

v2