Thanks alot, this is helping me loads!

Just one thing....

in the insert statement, the second value 'name' is what i need to get from
the select statement. in sysobjects all the user tables are type='U'. So i
kind of want to do... select into the table (databasename, name, null, null,
'N') . Its just proving difficult to get that name. Do i need to declare
the name as well?

Thanks again

-----Original Message-----
From: thomas.[Email Address Removed] [mailto:thomas.[Email Address Removed] Monday, June 17, 2002 2:41 PM
To: [Email Address Removed] [Email Address Removed] RE: T-SQL



Good suggestion Dave, I tried it putting it into variables and got the
following:


declare @iv_database_name varchar(30)
declare @mystr varchar(200)

select @iv_database_name = 'test'
select @mystr = 'insert into administration..optimise_database_output
values ('''
+@iv_database_name+''', name,null,null,N from '
+@iv_database_name+'..sysobjects where type=''U'''')'
print @mystr

Results:
insert into administration..optimise_database_output values ('test',
name,null,null,N from test..sysobjects where type='U'')


I suspect that you want it to look something like:

insert into administration..optimise_database_output select 'test',
name,null,null,N from test..sysobjects where type='U'

Notice that is no select statement in yours although there is a from clause
and a where clause the final 'U' is double quoted

. . . Tom

Tom Zeblisky
Reuters

On 06/17/2002 07:09:11 AM Dave Rayner wrote:
> Hi Meg,
>
> I'm not sure, but are you trying to do something like this...
>
> EXEC 'insert into administration..optimise_database_output SELECT ''' +
> @iv_database_name + ''',name,null,null,N from
> '+@iv_database_name+'..sysobjects where type=''U'''
>
> This statement almost certainly won't work because I can't test it, but
> it is often useful to print a string like this in QA to check for syntax
> ie
>
> declare @iv_database_name varchar(30)
> select @iv_database_name='DB_Name'
>
> Print 'insert into administration..optimise_database_output SELECT ''' +
> @iv_database_name + ''',name,null,null,N from
> '+@iv_database_name+'..sysobjects where type=''U'''
>
> Dave
>
> -----Original Message-----
> From: Meg Hardy [mailto:[Email Address Removed] Sent: 17 June 2002 11:59
> To: SQLDBA (E-mail)
> Subject: T-SQL
>
>
> Hi!
>
> I am driving myself insane trying to get this bit of code to work. Its
> part of a sp that I am writing. Can anyone see what i'm doing wrong
> here, heres what i have written:
>
> EXEC 'insert into administration..optimise_database_output values
> ('''+@iv_database_name+''', name,null,null,N from
> '+@iv_database_name+'..sysobjects where type=''U'''')'
>
> My syntax must be wrong, pleeeeeease help!
>
> Thanks.... Meg




-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com

Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.

This message has been checked for all known viruses by the
MessageLabs Virus Scanning Service. For further information visit
http://www.messagelabs.com/


**********************************************************************
DISCLAIMER
Any opinions expressed in this email are those of the individual and not necessarily of Mondial Assistance (UK) Ltd. This email and any files transmitted with it, including replies and forwarded copies (which may contain alterations) subsequently transmitted from the Company, are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that you have received this email in error and that any use is strictly prohibited.

If you have received this email in error please notify the IT support desk by telephone on +44 (0)208 2393984 or via email to
[Email Address Removed] including a copy of this message. Please then delete this email and destroy any copies of it. Thank you.

Mondial Assistance (UK) Ltd
Mondial House
102 George Street
Croydon
CR9 1AJ

Mondial Assistance (UK) Ltd Registered in England No.1710361 Registered office as above
**********************************************************************


This message has been checked for all known viruses by the
MessageLabs Virus Scanning Service. For further information visit
http://www.messagelabs.com/
MS Sql Server LazyDBA home page