Script / Batch for Import - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Script / Batch for Import

Author

Topic

Riposte
Starting Member

2 Posts

Posted - 2014-06-26 : 07:02:10

Hi,

Iam running a SQL Server 2012 Express.

Due that I dont have SSIS and can not plan tasks. So I have to write a script to do the following:

1. Delete Data in Database1 - Table1 on Server 1, Database1 - Table2 on Server 1,Database1 - Table3 on Server 1

2. Delete Data in Database2 - Table1 on Server 1, Database2 - Table2 on Server 1,Database2 - Table3 on Server 1

3. Connect to Server 2

4.Copy Data from Database1 - Table1 on Server 2 to Database1 - Table1 on Server 1 ... and so on

5.Send me a message @xy.com when a error appears

Then I have to run the batch file every day from monday to friday night.

My main problem is the part with the connection. Iam able to write the connection string but I dont know how to execute the string.

I hope someone can help me here.

Thanks in advance.

Riposte

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-26 : 13:39:39

You can use a linked server on server1 to run commands on server2. Once in place, you would use the 4-part naming convention for the remote object on server2: select * from [server2\instance1].db1.dbo.table1

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Riposte
Starting Member

2 Posts

Posted - 2014-06-27 : 05:38:19

Hi,

I created now a new linked server (it is a IBM DB2 AS400 server) via ODBC. I can see the tables but I dont get access to the data.

When I try to select something I get a error:

-- [xx].[xx].[xx].[cx] contains no columns that can be selected or the current user does not have permissions on that object.

or

SELECT *

FROM [xx].[xx].[xx].[cx]

ERROR:
Msg 7399, Level 16, State 1, Line 4
Der OLE DB-Anbieter 'MSDASQL' für den Verbindungsserver 'xy' hat einen Fehler gemeldet. Der Anbieter hat keine Informationen zu dem Fehler bereitgestellt.
Msg 7312, Level 16, State 1, Line 4
Ungültige Verwendung des Schemas oder Katalogs für den OLE DB-Anbieter 'MSDASQL' für den Verbindungsserver 'xy'. Ein vierteiliger Name wurde bereitgestellt, der Anbieter macht jedoch die Schnittstellen nicht verfügbar, die zum Verwenden eines Katalogs oder Schemas erforderlich sind.

Any idear? In MS access everything works fine (linked tables).

NOTE: If you ever get errors during the "linking process" make sure that you have setup a SYSTEM DSN ODBC Connection and not a USER DSN. If you setup a USER DSN you will have problems to connect to the server.

http://blogs.datadirect.com/2013/04/sql-server-linked-server-odbc-connection-salesforce-management-studio.html

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-27 : 14:21:41

I can't help with a DB2 linked server. Haven't a clue on that one. I can only help with a linked server that is pointing to a SQL Server instance.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources