sp_executesql issue - 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

SQL Server 2008 Forums

Transact-SQL (2008)

sp_executesql issue

Author

Topic

MaxOvrdrv
Starting Member

5 Posts

Posted - 2012-03-14 : 12:18:30

hey,

i am agetting an error with my code, and i was wondering if one of you could help me out? i'm really getting depressed about this... seems that every time i get close, something new pops up.

Here's my code:


set @spcall = N'EXEC(somedbname.dbo.someprocname @IN_param1=''@tmpparam1'', 
    @IN_param2=''@tmpparam2'', 
    @IN_param3=@tmpparam3, 
    @IN_param4=''@tmpparam4'', 
    @IN_param5=''@tmpparam5'', 
    @OUT_param1=@outparam1 OUTPUT, 
    @OUT_param2=@outparam2 OUTPUT, 
    @OUT_param3=@outparam3 OUTPUT, 
    @OUT_param4 = @outparam4 OUTPUT) AT SOMESERVER'

    set @params = N'@tmpparam1 VARCHAR(10), 
    @tmpparam2 VARCHAR(10), 
    @tmpparam3 INT, 
    @tmpparam4 VARCHAR(20), 
    @tmpparam5 VARCHAR(8), 
    @outparam1 int OUTPUT, 
    @outparam2 VARCHAR(100) OUTPUT, 
    @outparam3 VARCHAR(100) OUTPUT, 
    @outparam4 DATETIME OUTPUT'

EXEC sp_executesql @spcall, @params, 
    @tmpparam1=@localparam1, 
    @tmpparam2=@localparam2, 
    @tmpparam3=@localparam3, 
    @tmpparam4=@localparam4, 
    @tmpparam5=@localparam5,  
    @outparam1=@localoutparam1 OUTPUT, 
    @outparam2=@localoutparam2 OUTPUT, 
    @outparam3=@localoutparam3 OUTPUT, 
    @outparam4=@localoutparam4 OUTPUT;

Here's my error:
incorrect syntax near 'somedbname'.

any help would be greatly appreciated! thanks a ton!

Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 12:58:37

why do you need to use sp_executesql?

wont this be enough?


EXECsomedbname.dbo.someprocname @IN_param1=@tmpparam1, 
    @IN_param2=@tmpparam2, 
    @IN_param3=@tmpparam3, 
    @IN_param4=@tmpparam4, 
    @IN_param5=@tmpparam5, 
    @OUT_param1=@outparam1 OUTPUT, 
    @OUT_param2=@outparam2 OUTPUT, 
    @OUT_param3=@outparam3 OUTPUT, 
    @OUT_param4 = @outparam4 OUTPUT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MaxOvrdrv
Starting Member

5 Posts

Posted - 2012-03-14 : 13:18:46

No... i need to use the parentheses in order to execute this proc at another location (AT SOMESERVER), and if i use the parentheses like so:


EXEC (somedbname.dbo.someprocname @IN_param1=@tmpparam1, 
    @IN_param2=@tmpparam2, 
    @IN_param3=@tmpparam3, 
    @IN_param4=@tmpparam4, 
    @IN_param5=@tmpparam5, 
    @OUT_param1=@outparam1 OUTPUT, 
    @OUT_param2=@outparam2 OUTPUT, 
    @OUT_param3=@outparam3 OUTPUT, 
    @OUT_param4 = @outparam4 OUTPUT) AT SOMESERVER

i get the "expecting STRING, TEXT_LEX... " error. in order to get away from that one, i need to put the statement within a string (N'), but when i do that, i can no longer specify output parameters locally because they would also have to be in the string... And, since i actually want to pass values to the proc as parameters, and get values back from the proc as output params as well, i need to call sp_executesql to get the results i want...

here's the actual flow of this from top to bottom/how it should be:

1) Code(app) calls Proc1
2) Proc1 calls another proc (Proc2), that is in another database
3) Proc2 calls another proc (Proc3), that is in another server completely
4) Proc3 returns values to Proc2
5) Proc2 bubbles up those values to Proc1
6) Proc1 stores values in local variables
7) Proc1 performs other things with received values
8) Proc1 returns a formatted result set to the Code

I don't know if this makes sense, but because of the complexity of things (2 databases on the same server, and 2 different servers completely), the only solution i could find was to use sp_executesql. If i try an INSERT-EXEC with a temp table, i cannot perform distributed transaction because the 3rd proc is on another server...

if i try to run the command without parentheses (e.g.: EXEC somedbname.dbo.someprocname ... ... ... AT SOMESERVER), i get the "could not locate entry in sysdatabases for somedbname" error.

any help would really be appreciated.

Thanks!

hope this clarifies.

MaxOvrdrv
Starting Member

5 Posts

Posted - 2012-03-14 : 14:27:58

forgot to mention: if i call the proc with the linkedserver directly:
[SomeServer].somedbname.dbo.someproc

then i get the dreadful:
OLE DB provider "MSDASQL" for linked server "SomeServer" returned message "Parameter type cannot be determined for at least one variant parameter.".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-14 : 15:45:29

why do you need so many cross db cross server calls? what does procs do and what do they return? which process will trigger this execution flow?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MaxOvrdrv
Starting Member

5 Posts

Posted - 2012-03-15 : 11:30:49

these are the daily things that i must live with. Basically, the information we require is not owned by us, therefore, we must deal with linked servers, and other DBs to get it... then once we have it, we have to format it for our application.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 15:42:06

i'm still not convinced the current is not best and most efficient way. But i cant suggest you anything unless i've a clear idea of bigger picture

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 15:53:27

Are you saying you can not call the sproc on the other server?

As long as the other server will allow an RPC you should be fine

Just did a test to make sure

DECLARE @rc int, @Error int, @Error_Message varchar(255), @Rowcount int, @Rowcount_Total int;
EXEC [NJROS1BBLD0304\DEV2K08].[myActions].[dbo].[usp_SEL_myActionMessageSummaries]
@AKA_ID = 'X124192'
, @PageSize = 100
, @PageNumber = 1
, @Sort_Column = null
, @Sort_Direction = null
, @User = 'X002548'
, @rc=@rc OUTPUT, @Error=@Error OUTPUT, @Error_Message=@Error_Message OUTPUT, @Rowcount=@Rowcount OUTPUT
, @Rowcount_Total=@Rowcount_Total OUTPUT;
SELECT @rc AS [RC], @Error AS [Error], @Error_Message AS [Error_Message], @Rowcount AS [RowCount]
, @Rowcount_Total AS [RowCount_Total];

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 15:59:59

Only thing is you need to set RPC and RPC out properties to 1 in linked server options

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources