Stored Procedure Problems - 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 2005 Forums

Transact-SQL (2005)

Stored Procedure Problems

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-23 : 11:07:36

I have to pull the informations from multiple servers, i created linked servers (Server01 and Server02), I created the stored procedure. The following stored procedure returns all datas even i passed @FieldName and @FieldValue.

Can you check anybody why its returnning all datas.

Create PROC [dbo].[SP1]
@FieldName nvarchar(100),
@FieldValue nvarchar(100) AS
BEGIN
declare @SQL nvarchar(2000)
SET @SQL = N'SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server01].[MASTER].SYS.DATABASES b, [Server01].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]=''Server01'' AND b.database_id=c.dbid AND b.[NAME] NOT IN (''master'')
UNION
SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server02].[MASTER].SYS.DATABASES b, [Server02].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]=''Server02'' AND b.database_id=c.dbid AND b.[NAME] NOT IN (''master'')'
IF @FieldName !=''
BEGIN
SET @SQL =@SQL + ' AND ' + @FieldName + ' like ''%' + replace(@FieldValue,'''','''''') + '%'''
END
EXECUTE sp_executeSQL @SQL
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 11:11:43

use PRINT(@SQL) instead EXEC and posted the SQL query built here

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

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-23 : 11:19:36

If i execute stored procedure by

EXECUTE SP1 '',''

Its okay it pull all data.

But, if i execute by passing value:

EXECUTE SP1,'data_source','Server02'

Its pull all information from Server01 and Server02, Actually it has to pull from only Server02

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 11:21:30

did you see what i suggested? post the result of PRINT(@SQL) when you execute this

EXECUTE SP1,'data_source','Server02'

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

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-23 : 11:28:16

I tried to print out the following script. But it does not added AND @FieldValue and @FieldName on first server so, where is have to add IF @FieldName !='' condition ??

declare @FieldName nvarchar(100)
declare @FieldValue nvarchar(100)
set @FieldName='data_source'
set @FieldValue='Server02'
declare @SQL nvarchar(2000)
SET @SQL = N'SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server01].[MASTER].SYS.DATABASES b, [Server01].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]=''Server01'' AND b.database_id=c.dbid AND b.[NAME] NOT IN (''master'')
UNION
SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server02].[MASTER].SYS.DATABASES b, [Server02].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]=''Server02'' AND b.database_id=c.dbid AND b.[NAME] NOT IN (''master'')'
IF @FieldName !=''
BEGIN
SET @SQL =@SQL + ' AND ' + @FieldName + ' like ''%' + replace(@FieldValue,'''','''''') + '%'''
END
print @SQL

OUT PUT :

SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server01].[MASTER].SYS.DATABASES b, [Server01].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]='Server01' AND b.database_id=c.dbid AND b.[NAME] NOT IN ('master')
UNION
SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server02].[MASTER].SYS.DATABASES b, [Server02].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]='Server02' AND b.database_id=c.dbid AND b.[NAME] NOT IN ('master') AND data_source like '%Server02%'

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-23 : 11:41:29

Why i am getting error?

declare @FieldName nvarchar(100)
declare @FieldValue nvarchar(100)
set @FieldName='data_source'
set @FieldValue='Server02'
declare @SQL nvarchar(2000)
SET @SQL = N'SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server01].[MASTER].SYS.DATABASES b, [Server01].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]=''Server01'' AND b.database_id=c.dbid AND b.[NAME] NOT IN (''master'')
'
IF @FieldName !=''
BEGIN
SET @SQL =@SQL + ' AND ' + @FieldName + ' like ''%' + replace(@FieldValue,'''','''''') + '%'''
END
print @SQL
'
UNION
SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server02].[MASTER].SYS.DATABASES b, [Server02].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]=''Server02'' AND b.database_id=c.dbid AND b.[NAME] NOT IN (''master'')'
IF @FieldName !=''
BEGIN
SET @SQL =@SQL + ' AND ' + @FieldName + ' like ''%' + replace(@FieldValue,'''','''''') + '%'''
END
print @SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-23 : 12:08:03

error? what error are you getting?

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

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-23 : 12:38:00

Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '
UNION
SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server02].[MASTER].SYS.DATABASES b, [Server'.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-02-23 : 12:50:02

It works. Thanks for help

declare @FieldName nvarchar(100)
declare @FieldValue nvarchar(100)
set @FieldName='data_source'
set @FieldValue='Server02'
declare @SQL nvarchar(2000)
SET @SQL = N'SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server01].[MASTER].SYS.DATABASES b, [Server01].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]=''Server01'' AND b.database_id=c.dbid AND b.[NAME] NOT IN (''master'')
'
IF @FieldName !=''
BEGIN
SET @SQL =@SQL + ' AND ' + @FieldName + ' like ''%' + replace(@FieldValue,'''','''''') + '%'''
END
SET @SQL =@SQL + '
UNION
SELECT a.[data_source],b.[Name],c.[HostName],c.[CPU]
FROM SYS.SERVERS a,[Server02].[MASTER].SYS.DATABASES b, [Server02].[MASTER].SYS.SYSPROCESSES c
WHERE a.[data_source]=''Server02'' AND b.database_id=c.dbid AND b.[NAME] NOT IN (''master'')'
IF @FieldName !=''
BEGIN
SET @SQL =@SQL + ' AND ' + @FieldName + ' like ''%' + replace(@FieldValue,'''','''''') + '%'''
END
print @SQL

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-23 : 12:50:21

and you don't have a problem that table a doesn't join to anything?

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

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-23 : 12:51:26

And why is this Dynamic SQL?

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

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources