Solved

SQL Server Script

Posted on 2009-03-31
10
259 Views
Last Modified: 2012-05-06
I have a script which runs fine on my SQL Server Management Studio, but when I run it from a bat file I get a Error message

Change Database Context to VBScanWDB
Msg 7202, level 11 , state 2, Server MASChiCK\SQLExpress, line 2
Could not find server 'ChickRWJ in sysservers. Exe sp_addlinkedserver to add the server to syservers.

The CHICKRWJ is already linked into the Server so why would I run the addlinkserver  Attached is my SQL Script
sqlcmd -S 64.57.248.239,9998\CHIEF -l 65534 -U sa -P golf  -i C:|name.sql

Open in new window

0
Comment
Question by:daruffin
10 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24032123
Your problem is probably what DB you connect to by default.  Add the specific db to your command string.
0
 
LVL 25

Expert Comment

by:reb73
ID: 24032264
It doesn't look like a problem with DB not being specified in the command, if DB is omitted the default database for the login will  be used automatically (and besides this is the SA login)..

Can you verify if the specified linked server does exist by running the following command using sqlcmd -

select * from master.sys.servers
0
 

Author Comment

by:daruffin
ID: 24032270
Here is the what the script the bat file calls and this works when I run it from SQL Server Express, but when I run it from a bat file I get the error
0
 

Author Comment

by:daruffin
ID: 24032289
Use VBScanWDB
GO

INSERT INTO ActivityCode
SELECT * FROM [CHICKRWJ].[VBScanWDB].[dbo].[ActivityCode]

INSERT INTO Department
SELECT * FROM [CHICKRWJ].[VBScanWDB].[dbo].[Department]

INSERT INTO Employee
SELECT * FROM [CHICKRWJ].[VBScanWDB].[dbo].[Employee]


INSERT INTO IM2_InventoryItemWhseDetl
SELECT * FROM [CHICKRWJ].[VBScanWDB].[dbo].[IM2_InventoryItemWhseDetl]

INSERT INTO IM3_ItemCosting
SELECT * FROM [CHICKRWJ].[VBScanWDB].[dbo].[IM3_ItemCosting]

INSERT INTO Parts
SELECT * FROM [CHICKRWJ].[VBScanWDB].[dbo].[Parts]

INSERT INTO Settings
SELECT * FROM [CHICKRWJ].[VBScanWDB].[dbo].[Settings]

INSERT INTO WorkTicket
SELECT * FROM [CHICKRWJ].[VBScanWDB].[dbo].[WorkTicket]

INSERT INTO WorkTicketParts
SELECT * FROM [CHICKRWJ].[VBScanWDB].[dbo].[WorkTicketParts]

GO

The Link Server name is CHICKRWJ how come I can run this from a Bat file useing

sqlcmd  -i c:\DELVBScanData.sql
sqlcmd  -i c:\ImportVBscanData.sql

bat file
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24032420
Probably because the linked server does not exist on "64.57.248.239,9998\CHIEF", but it does on your local box.  
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:daruffin
ID: 24032622
reb73

when I run this cmd I get a error message stating
SQLcmd" select Unknown option. enter -? for help
0
 

Author Comment

by:daruffin
ID: 24032717
The link server is on my local box and I'm pulling data from the server on the WAN. I can run this script manually but I can't run it from a using a script and executing it from a bat file
0
 
LVL 25

Accepted Solution

by:
reb73 earned 250 total points
ID: 24032734
You have to modify your command as follows (using the -Q switch to issue a commandline query and exit)

sqlcmd -S 64.57.248.239,9998\CHIEF -l 65534 -U sa -P golf  -Q "select * from master.sys.servers"
0
 

Author Comment

by:daruffin
ID: 24032868
I get server_id Name

------------------------------------------------------
------------------------------------------------------
-----------------------------------------------------
------------------------------------------------------
0 MASCHICK\SQLExpress

The ChickRWJ does not exist on my loca master file. I'm not sure why since I link it in and I can pull data from there
0
 

Author Closing Comment

by:daruffin
ID: 31564993
This was what I was looking for it works fine now. Thanks
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Another way of doing this SQL 8 46
Need help creating a stored procedure 4 56
Inserting a column in a table that creates an ID and row number 4 52
CROSS APPLY 4 43
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now