This might be near

as you mentioned create table of database sysobjects

CREATE TABLE #obj (dbid int,dbname varchar(128),objid int,objname varchar(128))

insert sysobject details

EXEC sp_MSforeachdb @command1 = "INSERT INTO #obj SELECT d.dbid,d.name,s.id,s.name FROM...

Also if your Option table has no sequencing identifier then whilst select on the table (even for cursor) will probably give the data in the right order but it cannot...

I see nothing wrong with monthnumber but monthname will always be the same because you are using GETDATE()

use

DATENAME(month, INDEX_DAT) AS 'Month Name'

for month name

and

'Q'+CAST(DATEPART(qq, INDEX_DAT) as varchar) AS...

What do who_waitresource and block_waitresource contain. Please specifiy their datatypes and some sample data would be useful. What do you mean by 'is part of'.

Edited by - davidburrows on...

I agree with Jay, I do everything on procs although I have'nt needed to have complex queries with optional where tests. I try to keep my sql as far away...

I do all my procs thru script files. Compile into dev, test and if all OK compile into uat and live. I still only have one script but different environments....

Why not create a staging table with a single column say char(80). Insert the formatted header details first then extract and format the data then DTS this table to your...

Another possiblity is to create a proc in the linked server that does all the work (SET IDENTITY_INSERT etc) and call that proc to do the insert

EXEC server.database..proc ID,cola,colb....

You cannot use OPENQUERY without a SELECT. The SELECT NULL fools sql server and it executes the whole query.

Define your output columns on your target table.

Set transformations for all other matching columns.

Create ActiveX script to parse the input date to the separate output columns.

The following works OK for me (SQL7 SP4)

To insert one row

SELECT * FROM OPENQUERY(CCC03,'SELECT NULL SET IDENTITY_INSERT [database].. ON INSERT INTO [database].. (COL1,COL2) VALUES (value1,value2)...

Sorry Frank, is that better.

simonmeadows,

Be careful with using DateSerial and other date conversions. If you pass invalid or 'insane' values to DateSerial or even ISDATE you may or may not get a valid date...

Two things

Firstly your code

-- Remove the comma at the end

IF ( RIGHT(@pstrNames, 1) = ',' )

SET @pstrNames = LEFT(@pstrNames, LEN(@pstrNames) - 1)

 

You will not...

If @FirstName and @LastName are the variables and contain NULL for 'Not Supplied' then

WHERE (@FirstName IS NULL OR @FirstName = FirstName)

AND (@LastName IS NULL OR @LastName = LastName)

or if you...