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)
 TSQL-Query...need help .. please...

Author  Topic 

sqlinsider
Starting Member

2 Posts

Posted - 2008-02-05 : 08:28:28
Hey all...
i have a problem with a query like this...:

declare @table1 nvarchar(max);
declare @table2 nvarchar(max);
declare @table3 nvarchar(max);
declare @table4 nvarchar(max);
declare @table5 nvarchar(max);
declare @table6 nvarchar(max);


Set @table1 = (SELECT dtPartition.partitionid FROM dtPartition WHERE dtPartition.status like ('0'))
Set @table2 = (SELECT dtPartition.partitionid FROM dtPartition WHERE dtPartition.status like ('0'))
Set @table3 = N'dbo.dtEvent_'
Set @table4 = N'dbo.dtPrincipal_'
Set @table5 = @table3 + @table1
Set @table6 = @table4 + @table2

SELECT

dtEvent.EventNo as "Event ID:",
dtEvent.creationtime as "Erstellt am:",
dtPrincipal.struser AS "User Account:",
dtMachine.description AS "Computer:"

FROM

@table5 AS dtEvent,
dtMachine,
@table6 AS dtPrincipal

WHERE

dtMachine.id = dtEvent.eventmachineid
AND dtPrincipal.id = dtEvent.primaryuserid
AND dtEvent.eventno IN ('528')


Theire is an error....but i don't know what i can do...

Thanks.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-02-05 : 08:41:54
What is the error?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-05 : 08:43:10
Make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-02-05 : 09:13:40
@table is not a table, it's an nvarchar, so you can't select from it. To use it as a table name you'd have to use dynamic sql

Jim
Go to Top of Page

sqlinsider
Starting Member

2 Posts

Posted - 2008-02-05 : 09:50:47
Hi all...thanks...i have already a solution but now one more problem...

My query:


declare @table1 nvarchar(max);
declare @table2 nvarchar(max);

declare @sql varchar(max)

Set @table1 = (SELECT dtPartition.partitionid FROM dtPartition WHERE dtPartition.status like ('0'))
Set @table2 = (SELECT dtPartition.partitionid FROM dtPartition WHERE dtPartition.status like ('0'))




set @sql = 'SELECT

dtEvent.EventNo as [Event ID:],
dtEvent.creationtime as [Erstellt am:],
dtPrincipal.struser AS [User Account:],
dtMachine.description AS [Computer:]
FROM
dtEvent_' + @table1 + ' dtEvent,
dtMachine,
dtprincipal_' + @Table2 + ' dtPrincipal
WHERE
dtMachine.id = dtEvent.eventmachineid
AND dtPrincipal.id = dtEvent.primaryuserid
AND dtEvent.eventno IN (''528'') '


EXEC(@SQL)


I would like to use this script as an view, but i can't, because i have theire variables declared...and it isn't allowed in an view...


Thanks

Go to Top of Page
   

- Advertisement -