Dynamic Scripting for USE [DB]

Greetings,

I am having a difficult time figuring out a way to get this to work. The scripting executes without error, but does nothing. What I want it to do is change the connection to the database retrieved by the cursor.

DECLARE
@.PAR1 VARCHAR (256);
DECLARE DB_PAR CURSOR FOR SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE '%HRN%TEST';

OPEN DB_PAR
FETCH DB_PAR INTO @.PAR1
BEGIN
EXECUTE ('USE ' + @.PAR1 +';')
PRINT @.PAR1 /*Show what the par value is */
END;
CLOSE DB_PAR;
DEALLOCATE DB_PAR;

Anyone have any ideas?

Something else I thought of. Oracle provides the ability to change the database connection by issuing "CONN USER/PASSWORD@.INSTANCE". I suppose SQL Server might provide something like this in SQLCMD, but I'm not using that right now.

Hi,

dioes the query SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE '%HRN%TEST'; bring something back ? I guess not, right ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Yes, it does return a value. The database that I am looking for is "jeff_hrn_test" and taht is what returns in the variable when I print it.

If you run SELECT * FROM SYS.SYSDATABASES on any database within a SQL Server instance, it returns the full listing of all Databases on that Server. I just am looking for a specific name. It must contain HRN and TEST in the name of the Database.

So, it has a value, but the dynamic SQL doesn't actually do anything from what I can see.

|||

I have been able to determine an answer, potentially.

The problem is that I am executing this within dynamic sql. So, as soon as the execute completes, the context switches back.

Therefore, if I want to set the context, then run my next block of SQL Code, I have to make it into one gigantic EXECUTE!

I'm hoping to find a better way to do this. FRUSTRATING!!

By the way, Here's a sample code block:

DECLARE @.DB VARCHAR (50);
SET @.DB=(SELECT NAME FROM SYS.DATABASES WHERE NAME LIKE '%HRN%TEST')
EXECUTE('USE [' + @.DB + '];
SELECT * FROM TABLE')

Dynamic Date Function for @date

Greetings...
I have a report that is being deployed w/ the parameters @.sdate (Start Date)
and @.edate (End Date). My goal is to setup a subscription to a few folks w/
the default dates to be the previous day. I tried using the =datetime.today
-1 but recieved errors during compile. If someone has a solution to this
please reply, it will be most helpful.
Thanks,
--
Ben Sullins
Our Vacation Store
http://ovstravelfolio.comFor others in the same boat I have found the solution to this...
To set the default value for report parameters using an expression you must
first be in 'layout' view in report designer. From there you will need to
select 'Report' from the properties window. Then click on the elipse (...)
next to 'Report Parameters'. From there you will see the Report Parameters
Dialogue box, this is where you set the default value for the parameters. You
can use other query results or functions like this,
=datetime.today.adddays(-1).tostring("mm/dd/yy").
Hope this helps!
"Ben Sullins" wrote:
> Greetings...
> I have a report that is being deployed w/ the parameters @.sdate (Start Date)
> and @.edate (End Date). My goal is to setup a subscription to a few folks w/
> the default dates to be the previous day. I tried using the =datetime.today
> -1 but recieved errors during compile. If someone has a solution to this
> please reply, it will be most helpful.
> Thanks,
> --
> Ben Sullins
> Our Vacation Store
> http://ovstravelfolio.com

Dynamic Column Naming

Greetings,
Very simple question, how do i assign a column same name as its value?
declare @.seas varchar(5) set @.seas = 'Donie'
SELECT @.seas as (')
NOT static please, dynamic. So, SELECT @.seas as 'Donie' won't do me any
good.
Much obliged,
Don
*** Sent via Developersdex http://www.examnotes.net ***don larry wrote:
> Greetings,
> Very simple question, how do i assign a column same name as its value?
> declare @.seas varchar(5) set @.seas = 'Donie'
> SELECT @.seas as (')
> NOT static please, dynamic. So, SELECT @.seas as 'Donie' won't do me
> any good.
> Much obliged,
> Don
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
You need to use dynamic sql, which has security implications if you are
using stored procedures.
For example:
declare @.seas varchar(5)
declare @.sql nvarchar(1000)
set @.seas = 'Donie'
set @.sql = N'SELECT [' + @.seas + N'] from dbo.mytable'
Exec sp_executesql @.sql
David Gugick
Imceda Software
www.imceda.com|||The first obvious question would be "Why would you want to do this?"
Assuming there was some reason, you have to use dynamic SQL to get this kind
of
dynamic feature.
Declare @.Sql VarChar(8000)
Declare @.Seas VarChar(5)
Set @.Sql= 'Select ' + QuoteName(@.Seas, '''') + ' As ' + QuoteName(@.Seas)
Exec(@.Sql)
Thomas
"don larry" <donlarry17@.hotmail.com> wrote in message
news:%23mwQMTAXFHA.2776@.TK2MSFTNGP12.phx.gbl...
> Greetings,
> Very simple question, how do i assign a column same name as its value?
> declare @.seas varchar(5) set @.seas = 'Donie'
> SELECT @.seas as (')
> NOT static please, dynamic. So, SELECT @.seas as 'Donie' won't do me any
> good.
> Much obliged,
> Don
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>> how do i assign a column same name as its value? <<
Okay, you missed the whole idea of data modeling, RDBMS, and just about
everything else for the past 35 years in the field. Not a minor
misunderstanding, but all of it at the foundation level. Entities,
attributes and values are TOTALLY DIFFERENT! This is like being in a
math newsgroup and having someone ask "Can I make 2+2=5 If I have
really large values of 2?"
Really bad SQL programmers can use proprietary, dynamic code to kludge
things like this. But if you actually want to be competent, get a good
book on RDBMS and data model, read it and stop programming until you
understand what you are doing.