error putting a variable in a string

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)

error putting a variable in a string

Author

Topic

JordyDB
Starting Member

8 Posts

Posted - 2012-03-27 : 05:37:29

select * FROM OpenQuery(ADSI, 'SELECT mail, title, displayName, sAMAccountName, givenName FROM' +@pad+ 'where objectClass = ''User''' )

why do I get an error on the @pad part?

rajarajan
Starting Member

48 Posts

Posted - 2012-03-27 : 07:45:00

select * FROM OpenQuery(ADSI, 'SELECT mail, title, displayName, sAMAccountName, givenName FROM table name where objectClass = ''' +@PAR + '''' )

JordyDB
Starting Member

8 Posts

Posted - 2012-03-27 : 08:04:30

quote:
Originally posted by rajarajan

select * FROM OpenQuery(ADSI, 'SELECT mail, title, displayName, sAMAccountName, givenName FROM table name where objectClass = ''' +@PAR + '''' )

the parameter must be after the from...

rajarajan
Starting Member

48 Posts

Posted - 2012-03-27 : 08:08:56

declare @wcID char(4)
declare @sql_str nvarchar (4000)

SET @wcID = '1'

SET @sql_str =
'SELECT COUNT(coil_num) AS coilCnt,
SUM(produced_wgt) AS weight,
SUM(produced_length) AS length,
SUM(bonus_base_pct) AS bonus
FROM ''' + @@CONNECTIONS+ '''
WHERE wc_num = ''' + @wcID + '''
AND coil_num = coil_num_at_wc
AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-12-15 07:00:00''
AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') < ''2005-12-15 19:00:00'''

SET @sql_str = N'select * from OPENQUERY(MIPS, ''' + REPLACE(@sql_str, '''', '''''') + ''')'

PRINT @sql_str

EXEC (@sql_str)

'try this'

JordyDB
Starting Member

8 Posts

Posted - 2012-03-27 : 08:19:05

quote:
Originally posted by rajarajan

declare @wcID char(4)
declare @sql_str nvarchar (4000)

SET @wcID = '1'

SET @sql_str =
'SELECT COUNT(coil_num) AS coilCnt,
SUM(produced_wgt) AS weight,
SUM(produced_length) AS length,
SUM(bonus_base_pct) AS bonus
FROM ''' + @@CONNECTIONS+ '''
WHERE wc_num = ''' + @wcID + '''
AND coil_num = coil_num_at_wc
AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-12-15 07:00:00''
AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') < ''2005-12-15 19:00:00'''

SET @sql_str = N'select * from OPENQUERY(MIPS, ''' + REPLACE(@sql_str, '''', '''''') + ''')'

PRINT @sql_str

EXEC (@sql_str)

'try this'

Create Procedure MyCode
as
Begin

Set Quoted_Identifier OFF
DECLARE @pad NVARCHAR(1000)

DECLARE C1 CURSOR READ_ONLY
FOR
SELECT top(1) 'LDAP://'+adpathuser
FROM tblsetting
OPEN C1
FETCH NEXT FROM C1 INTO
@pad

BEGIN
select * FROM OpenQuery(ADSI, 'SELECT mail, title, displayName, sAMAccountName, givenName FROM ''LDAP://OU=Employees,OU=Users,OU=Sint-Amands,DC=excon,DC=local'' where objectClass = ''User''' )
FETCH NEXT FROM C1 INTO
@pad
END

CLOSE C1
DEALLOCATE C1

End

This is the query that's works, but now I just want the ldap to be variable

rajarajan
Starting Member

48 Posts

Posted - 2012-03-27 : 09:31:42

declare @wcID char(4)
declare @sql_str nvarchar (4000)

SET @wcID = sELECT top(1) 'LDAP://'+adpathuser ---
FROM tblsetting

SET @sql_str =
'SELECT mail, title, displayName, sAMAccountName, givenName FROM ''' +@wcID + ''' where objectClass = ''User'''

SET @sql_str = N'select * from OPENQUERY(MIPS, ''' + REPLACE(@sql_str, '''', '''''') + ''')'

PRINT @sql_str

EXEC (@sql_str)

U have frame the query before using the open query, coz in open query u can' do string manipulation

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources