tsql that reads a parameter from a table?
-
i'm not the best at tsql ok... so i have a question, and forgive me if this is
fundamental.
how exactly do you create an sql script that reads a parameter from a table, then
execute another script based on it?
thoughts? examples?
_________________________
-
It depends on what you mean by reading a parameter. Do you mean "get the parameter name so I can build a query where I have to specify the parameter name and some value" or do you mean the value of a parameter? The first would involve dynamic SQL. The second could likely be accomplished using JOINs.
K. Brian Kelley
@kbriankelley -
excellent question.
it's a value of a parameter actually.
so based on parameter 1, 2, or 3 that is entereed (which is another question)
the script looks in table 'X' for what the parameter values are in the table.
does that make sense?
_________________________
-
any takers?
_________________________
-
You can look at using a CASE statement for this. That's one easy way to do it.
K. Brian Kelley
@kbriankelley -
excellent starting position... some thing like this perhaps?
select [type], --column_name
case
when [type] = 'full' then (/*backup script here*/)
else (/*other backup script here*/)
end,
from butype --table_name
_________________________
-
Maybe this is what you're looking for?:
declare @whatToDo varchar(30)
select @whatToDo = [type]
from buttype
if @whatToDo = 'full'
begin
/* backup code here */
end
else if @whatToDo = 'incremental'
begin
/* just to show 'else if' 🙂 */
end
else
begin
/* other backup code here */
end
Success!
-
could thish work as well?
declare @WhatToDo varchar(30)
set @whatToDo 'select [type] from buttype'
... ...
_________________________
-
I apologize, I'm having a hard time following exactly what you're trying to do. If you're getting a parameter and then wanting to execute some statement, like a backup statment, use the structure like Henk Kok gave. If you're just trying to return data based on a parameter, you may be able to do this with a CASE statement and a JOIN or two, which is where my statements come in.
K. Brian Kelley
@kbriankelley -
no no... probably my fault.
lets see if i can expalin this properly.
basically you have a .vbs script that will run a tsql backup script.
once a parameter is passed into the script; the script will run against
a server, but before the tsql backup script can execute it will need
proper access to the server so it will have a table in some other database
that it will look at for the right username/password.
this [accounts] table only contains the following columns.
servername, username, password
so the tsql script sees the server it's running against, then looks at
this table with a simple select statement. if the servere is 'myserver',
then it will run a select against the table. find the username/password
that match up against that servername, and pass those as the login
credentials so it can perform the backup.
you may ask...
"why not have the same account for all sql servers so you can
hard code the username/password into it only once?"
the answer is... it's not completely my project. so i don't have the
say so, but i can try to make it all work.
so that is what i'm trying to do, in addition to running
a backup script based on a parameter that was passed into it earlier,
but that is another thread i think. obviously i'm in over my head on
this if i'm asking about logic, but thats what the net is good for,
and i can use the advice.
hope all this makes sense.
_________________________
-
Ok, then maybe this is - or at least looks like - what you need,
this is VBScript code (not tested(!), I used hyphens to show code indenting, these should be removed):
'OLE BD connectionstring for the database that contains the server,username,password table
'if you need another type of connectionstring see http://www.connectionstrings.com/
const BU_PARAMS_CONNECTSTRING = "Data Source=;Initial Catalog=;User ID=<username;Password=;"
'this is the name of the server and database that will be backed up
'you mentioned that you've already have code
'to give these parameters a value (perhaps using the WshArguments object?),
'so in the following code I'll just assume that servername has a valid value
dim servername, databasename
'object variables for connection, command and recordset objects
dim oCon, oCmd, oSet
'variable for the sql commandtext
dim sql
'no effort to handle sql injection
sql = "select username, password from accounts where servername = '" & servername & "'"
set oCon = createobject( "adodb.connection" )
call oCon.Open( BU_PARAMS_CONNECTSTRING )
'execute the statement directly, it will return a recordset
set oSet = oCon.execute( sql )
if not oSet.EOF then
--DatabaseToBackupConnectString = "Data Source=" & servername & ";" & _
----"Initial Catalog=" & databasename & ";" & _
----"User ID=" & oSet.fields( "username" ).value & ";" & _
----"Password=" & oSet.fields( "password" ).value & ";"
else
--'something went wrong...
end if
oSet.close
set oSet = nothing
oCon.close
'for simplicity I'll just assume that the connectionstring is successfully constructed
'we'll reuse the connection object, reopening it with the new connectionstring
call oCon.open( DatabaseToBackupConnectString )
set oCmd = createobject( "adodb.command" )
with oCmd
--'using a constant for the backup device and name
--const BACKUP_DEVICE = "c:\backupdata"
--const BACKUP_NAME = "my_backup"
--set .ActiveConnection = oCon
-- 'simple backup statement, should suffice to show the use of parameters
--.CommandText = "backup database ? to ? with name = ?"
--'now, we'll set the values for the questionmarks
--'you could also construct the entire sql string (as with the previous select),
--'but I think this is "cleaner"
-- using fixed values instead of ADODB constants like adVarChar and adParameterDirectionInput
--call .Parameters.Add( .CreateParameter( "name1", 200, 1, 40, databasename )
--call .Parameters.Add( .CreateParameter( "name2", 200, 1, 100, BACKUP_DEVICE )
--call .Parameters.Add( .CreateParameter( "name1", 200, 1, 40, BCKAUP_NAME )
--'now we'll execute the backup command
--call .Execute()
--'clean up
--set .ActiveConnection = nothing
end with
set oCmd = nothing
oCon.close
set oCon = nothing
-
Henk Kok...
WOW THANKS! ABOVE AND BEYOND!! MANY THANKS MY FRIEND!
YOU ROCK!
_________________________
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply