473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

How to query another server with stored procedure

4
Hello!
I have this stored procedure:
Expand|Select|Wrap|Line Numbers
  1. set ANSI_NULLS ON
  2. set QUOTED_IDENTIFIER ON
  3. GO
  4.  
  5. ALTER PROCEDURE [dbo].[test_proc] 
  6. AS
  7. BEGIN
  8.     DECLARE @DestDB nvarchar(100)
  9.     DECLARE @SQL nvarchar (2000)
  10.     SET @SQL = NULL
  11.     SELECT @DestDB = param1 from [SERVER1\SERVER1].master.dbo.CAConfig    
  12.     SET @DestDB = '[SERVER1\SERVER1].' + @DestDB + '.dbo.TestDBFromServer1'
  13.    SELECT * FROM @DestDB
  14. END
  15.  
This stored procedure is on a machine called Server2 and i try to read some tables from Server1.
The database that i need to work with is on Server1,but the name changes every time that i reboot the machine and the name of this database is in the colon "param1" from [SERVER1\SERVER1].master.dbo.CAConfig so I have to get the database's name before doing a query to the desired table. (it is a database for WINCC software from Siemens)
Ok..the problem is that the procedure stops with an error at the SELECT command " Must declare the variable '@DestDB'."
Why is that??
If i change the SELECT code to
Expand|Select|Wrap|Line Numbers
  1.  SET SQL='SELECT * FROM '+@DestDB  
  2. EXEC(SQL)
it works!
Nov 23 '09 #1
3 2956
Delerna
1,134 Expert 1GB
Because in
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM @DestDB 
  2.  
@DestDB is interpreted as a table variable
but you have declared it as nvarchar(100) which is not a table variable
Therefore you get the error which might be a bit cryptic because you have declared it.
A more sensible message might be "Type mismatch on @DestDB" because that is what it is.

Then again many error messages tend to be cryptic




In your second example you are using @DestDB
as a string and concatenating it to another string
The resulting string is then used as a dynamic query.
This is legal and correct and therefore works
Nov 27 '09 #2
Delerna
1,134 Expert 1GB
In case you didn't know. A table variable is declared and used thus
Expand|Select|Wrap|Line Numbers
  1. declare @tblVar table (field1 int,field2 varchar(10),field3 float)
  2.  
  3. insert into @tblVar select 1,'Test',1.2
  4. insert into @tblVar select 2,'String',5.2
  5.  
  6. select * from @tblVar 
  7.  
it is a table that exists in RAM instead of on a disc
Nov 27 '09 #3
Mirku
4
Thanks!
It makes sense!
I do not have very much experience with SQL and the code that I have is a mixture from diferents howto's!
Nov 28 '09 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

5
by: Bruno Alexandre | last post by:
Hi guys, withou using SP, I want to be able to add a Parameter to the SQL Query and retrive the Recordset so I can use the Paging property under the recorset object.... how can I do this? I'm...
5
by: Rob Wahmann | last post by:
I have a web-based admin section for a site and I would like to be able to query the SQL Server database size and display it within my admin area. Is there a function or method of doing this? My...
1
by: A.M. de Jong | last post by:
When I perform a query on a linked Oracle server in the Query analyser I have no prboblem' to perform this query. However, when I create this query in a stored procedure I get a compilation error...
10
by: Thomas R. Hummel | last post by:
I have a stored procedure that suddenly started performing horribly. The query plan didn't look right to me, so I copy/pasted the code and ran it (it's a single SELECT statement). That ran pretty...
12
by: John Scott | last post by:
Ok...here we go... I am trying to manually run an SQL server back up job from c#. Here is a snippet of my code to begin the process: string strSQL = "USE msdb " + "\n if exists(select * from...
1
by: Sreeneet | last post by:
Hi all, I want to call a stored procedure which is written in SQL Server from an ms-access query. It is having some parameters also and the stored procedure will return some records. Is there...
2
by: Adam Rogas | last post by:
I have a couple of complex stored procedures that work well and quickly once they have compiled. The problem I am running into is that every once in a while they want to refresh thier execution...
6
by: lesperancer | last post by:
SELECT distinct b.t_orno, b.t_pono FROM tblMonthlyBooking AS b, tblFilterDate, tblFilterDate AS tblFilterDate_1 WHERE (((b.t_yearMonth) Between . And .)); tblMonthlyBooking is a sql server...
3
by: comp_databases_ms-sqlserver | last post by:
This post is related to SQL server 2000 and SQL Server 2005 all editions. Many of my stored procedures create temporary tables in the code. I want to find a way to find the query plan for these...
1
by: central_scrutinizer | last post by:
I have an ASP that has been working fine for several months, but it suddenly broke. I wonder if windows update has installed some security patch that is causing it. The problem is that I am...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.