Hi David/Andy/whatever ,

That's definately an option although its less preferable than putting the sproc into the target database. We prefer to keep everything "within" if...

Based on what you yourself said before Sushila I'm pretty sure anythnig using OLEDB linked servers isn't going to work.

I'm currently exploring ways of doing it using the sp_OA* sprocs!

-Jamie

Noel,

I've got this nailed now, thanks very much.

It was made more difficult by the fact that there were 4 "value" fields and depending on which element was getting picked I...

Sushila,

Not that its important but...

The views I want to create are provided for reporting purposes and the database I'm trying to create them in is our reporting database. The views are (or...

Nope!

select *

from openrowset('SQLOLEDB','SERVER=dwsqldevinc4;database=dwMETADATA;UID=***;PWD=***', 'create view myview as select * from loadprocess;select null') a

gives error:

Server: Msg 8180, Level 16, State 1, Line 1

Statement(s) could not be prepared.

Server: Msg 170, Level 15, State 1,...

Nice idea, but using this statement:

select *

from openrowset('SQLOLEDB','SERVER=dwsqldevinc4;database=dwMETADATA;UID=***;PWD=***', 'create view myview as select * from loadprocess') a

I got this error:

Could not process object 'create view myview as select * from loadprocess'. The...

Great suggestion Remi,

I'll give it a go and report back!

Yeah, that's what I'm doing. I routinely add a BIT parameter to my sprocs (default to 0) to indicate whether to print out the dynamic SQL or not!

It would just...

Hi Remi,

Let me give you a simple example. I have the following sproc:

create proc MyProc as

 declare @str as varchar(100)

 set @str = 'select * from sysobjects'

 select * from sysobjects

 exec (@str)

If I then...

Yeah that's exactly right Jesper. Its only for debugging purposes so dropping it elsewhere is a little bit of overkill. however if that's the only option...so be it!

-Jamie

This should get you started. You can modify it to work for any month!

create procedure MyProc

as

 declare @str varchar(8000)

 declare @counter integer

 set @counter = 1

 set @str = 'create table MyTable ([desc] varchar(50)'

 while @counter <= 31

 begin

  set @str = @str + ',...

I'm guessing here but that I think that should be achievable using dynamic SQL.

It won't like the zeros at the start of the column names though, you'll have to wrap...

Thanks Allan.

That's alot to get thru

I've almost nailed this myself but if I have any more problems I'll pick up some of your...

Peter,

That last point is a good one. IMO developers should never be let loose near a relational database and that is truer with the advent of SQL2005 than ever before.

-Jamie

Superb, thank you Noel.

I'm having a few fun and games with my string exceeding 8000 chars but I'm working on that. The Yukon windowing functions would help but of course if I...