Dear friens,
I need to pass a column as a parameter in my query. I did this:
ALTER PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]
@.Direccao nvarchar(11),
@.prmFieldName nvarchar(25),
@.prmFieldValue nvarchar(25)
AS
BEGIN
IF @.prmFieldValue='*' OR @.prmFieldValue=''
BEGIN
SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN
dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN
dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN
dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN
dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)
ORDER BY Nome
END
ELSE
BEGIN
DECLARE @.SQL varchar(7000)
SET @.Direccao='CGD-DAS'
SET @.prmFieldName='UserID'
SET @.prmFieldValue='C095122'
SET @.SQL = 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN
dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN
dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN
dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN
dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)
AND '+ @.prmFieldName +' =' + @.prmFieldValue + ' '
EXEC @.SQL
END
END
THE COMAND EXECUTE SUCESSFULLY IN QUERY ANALISER, BUT IN ASP.NET 2.0 CLIENT RETURNS THE FOLLOWING ERROR:
Server Error in '/WS_GestaoDesktop' Application.
The name 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: The name 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.
Source Error:
Line 3269: }Line 3270: dsHardware.dtGD_SP_GET_UsersByDIR_CODDataTable dataTable = new dsHardware.dtGD_SP_GET_UsersByDIR_CODDataTable(); Line 3271: this.Adapter.Fill(dataTable); Line 3272: return dataTable; Line 3273: } |
The problem is based here:
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)
if you want to pass a static value use
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = ''' + @.Direccao + ''')
if you want to pass a identifier use
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = ' + @.Direccao + ')
HTh, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Dear Friend,I changed as you told, but the error still there! :-(
CREATE PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]
@.Direccao nvarchar(11),
@.prmFieldName nvarchar(25),
@.prmFieldValue nvarchar(25)
AS
BEGIN
IF @.prmFieldValue='*' OR @.prmFieldValue=''
BEGIN
SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN
dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN
dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN
dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN
dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)
ORDER BY Nome
END
ELSE
BEGIN
DECLARE @.SQL varchar(7000)
SET @.Direccao='CGD-DAS'
SET @.prmFieldName='UserID'
SET @.prmFieldValue='C095122'
SET @.SQL = 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName, dbo.HARDWARE.New_Computername,
dbo.ModeloPC.MOD_ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome,
dbo.HARDWARE.Migrada, dbo.HARDWARE.MigraViaChange, dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN
dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN
dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN
dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN
dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='+ @.Direccao +')
AND '+ @.prmFieldName +' =' + @.prmFieldValue + ' '
EXEC @.SQL
END
END
ERROR:
Msg 203, Level 16, State 2, Procedure GD_SP_GET_UsersByDIR_COD, Line 49
The name 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.H' is not a valid identifier.
|||
change the following things..
CREATE PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]
@.Direccao nvarchar(11),
@.prmFieldName nvarchar(25),
@.prmFieldValue nvarchar(25)
AS
BEGIN
IF @.prmFieldValue='*' OR @.prmFieldValue=''
BEGIN
SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN
dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN
dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN
dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN
dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)
ORDER BY Nome
END
ELSE
BEGIN
DECLARE @.SQL varchar(7000)
SET @.Direccao='CGD-DAS'
SET @.prmFieldName='UserID'
SET @.prmFieldValue='C095122'
SET @.SQL = 'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName, dbo.HARDWARE.New_Computername,
dbo.ModeloPC.MOD_ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome,
dbo.HARDWARE.Migrada, dbo.HARDWARE.MigraViaChange, dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN
dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN
dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN
dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN
dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='''+ @.Direccao +''')
AND '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '
EXEC @.SQL
END
END
|||
Manid,
I changed as you told, but the error still there! :-(
Regards.
|||I guess you are either trying to change another procedure than you are executing or you will have to provide the whole snippet of code via mail or something to make it reproducable.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Dear Jens,
I created a more simple query, based in the same problem:
CREATE PROCEDURE TEMP
AS
DECLARE @.SQL varchar(8000)
DECLARE @.prmFieldName varchar(20)
DECLARE @.prmFieldValue varchar(20)
SET @.prmFieldName='UserID'
SET @.prmFieldValue='C095122'
SET @.SQL = 'SELECT New_Computername
FROM HARDWARE
WHERE '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '
EXEC @.SQL
The logical is the some of other queries, but in this one I can't executed it, because returns the following error:
Msg 2812, Level 16, State 62, Procedure TEMP, Line 15
Could not find stored procedure 'SELECT New_Computername
FROM HARDWARE
WHERE UserID ='C095122' '.
(1 row(s) affected)
If you can put this query with column parameter workink good, my problem probably is resolved for the other queries.
Thanks!
|||Sorry and blame on me for not seeing this, you will have to wriite the exec as follows:
EXEC(@.SQL)
HTH, jens K. Suessmeyer.
http://www.sqlserver2005.de|||
Dear friends,
I created a new simple query to try to resolve the problem of passing a column as parameter, using sp_executesql, but still return error. :-(
ALTER PROCEDURE TEMP
@.prmFieldName nvarchar(25),
@.prmFieldValue nvarchar(25)
AS
DECLARE @.SQL varchar(8000)
SET @.prmFieldName='UserID'
SET @.prmFieldValue='C095122'
SET @.SQL = N'SELECT New_Computername
FROM HARDWARE
WHERE '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '
EXECUTE sp_executesql @.SQL;
ERROR:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@.statement' of type 'ntext/nchar/nvarchar'.
|||
Dear Friends,
I found the solution for my problem.
I must use EXECTUTE sp_executesql @.SQL in spite of EXEC @.SQL, and I must use nvarchar(4000) in spite of varchar(8000).
The final Result:
ALTER PROCEDURE [dbo].[GD_SP_GET_UsersByDIR_COD]
@.Direccao nvarchar(11),
@.prmFieldName nvarchar(30),
@.prmFieldValue nvarchar(25)
AS
BEGIN
IF @.prmFieldValue='*' OR @.prmFieldValue=''
BEGIN
SELECT TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN
dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN
dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN
dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN
dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD = @.Direccao)
ORDER BY Nome
END
ELSE
BEGIN
DECLARE @.SQL nvarchar(4000)
SET @.SQL = N'SELECT
TOP (100) PERCENT dbo.ADServico_User.UserID, dbo.ADUser.UserName AS Nome, dbo.HARDWARE.New_Computername AS Computername,
dbo.ModeloPC.MOD_ModeloPC AS ModeloPC, dbo.Monitor.MON_Monitor AS Monitor, dbo.Status.StatusNome AS Status,
dbo.HARDWARE.Migrada AS Interven??o, dbo.HARDWARE.MigraViaChange AS [Migrada via Change], dbo.HARDWARE.StatusID,
dbo.HARDWARE.NS_ID
FROM dbo.ModeloPC INNER JOIN
dbo.SERVICO INNER JOIN
dbo.ADServico_User ON dbo.SERVICO.S_GrupoServico = dbo.ADServico_User.GrupoServico INNER JOIN
dbo.HARDWARE ON dbo.ADServico_User.UserID = dbo.HARDWARE.UserID INNER JOIN
dbo.Status ON dbo.HARDWARE.StatusID = dbo.Status.ID ON dbo.ModeloPC.MODELO_ID = dbo.HARDWARE.MODELO_ID INNER JOIN
dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID INNER JOIN
dbo.DIRECCAO ON dbo.SERVICO.S_NomeDir = dbo.DIRECCAO.DIR_COD LEFT OUTER JOIN
dbo.ADUser ON dbo.HARDWARE.UserID = dbo.ADUser.UserID
WHERE (dbo.HARDWARE.StatusID <> 6) AND (dbo.DIRECCAO.DIR_COD ='''+ @.Direccao +''')
AND '+ @.prmFieldName +' =''' + @.prmFieldValue + ''' '
EXECUTE sp_executesql @.SQL
END
END
THANKS FOR ALL YOUR IMPORTANT SUPPORT!!!
