Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
 Posted - 2003-05-19 : 15:33:32
|
| Hello all. I have a quick question. I have a Job to move some data around for me. That was the fairly easy part. This job is using a database that changes names every couple of weeks. My question is: Do I have to explicitly name the step: @database_name = N'TheNameOfMyDatabase' in my job step? If jobs are server wide, why do I have to do this?According to Books Online, if I leave this blank, it will default to the Master DB. We keep our database names in line with the version of our software, so keeping with a constant database name is not an option right now. It will be in the future.If I leave this blank, and Master does not contain the proper tables for the job to execute, what will happen?Sorry if I sound like a nervous newbie, but that is what I am! Thanks all!Aj |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-19 : 16:23:01
|
| First, I would recommend that you do not change your database name. That will solve all of your job problems. Second, if you really are going to change database names with each new version, then you are going to have to modify the jobs each and every time as well. The reason why the step requires a database name is because it needs to know which database to run the code against. Take for example Query Analyzer. In order for you to run code against tables in say a database named DBName1, then you need to run USE DBName1 OR you will need to change the database in the drop down in QA. How is the server supposed to know which database to use otherwise? If you don't specify it, it will use master.So the easiest way around this is to not change your database name. Otherwise, you are stuck with modifying the job each time.Tara |
 |
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2003-05-19 : 16:36:01
|
| That is what I was afraid of. Oh well, that is what they pay me the peanuts for.Aj |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-19 : 16:37:17
|
| if the job is calling SPs in the database then you can modify it to call them dynamically.declare @dbname varchar(128)select @dbname = 'mydb'declare @sql varchar(2000)select @sql = 'exec ' + @dbname + '..spname'exec (@sql)Then just calculate the dbname - maybe search sysdatabases.Just set it to run in master and it will exec the SPs in the database you give it.Could even get the db name from a constant database/table.But as Tara says the database names shouldn't change.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-19 : 16:42:24
|
quote:
if the job is calling SPs in the database then you can modify it to call them dynamically.declare @dbname varchar(128)select @dbname = 'mydb'declare @sql varchar(2000)select @sql = 'exec ' + @dbname + '..spname'exec (@sql)Then just calculate the dbname - maybe search sysdatabases.Just set it to run in master and it will exec the SPs in the database you give it.Could even get the db name from a constant database/table.But as Tara says the database names shouldn't change.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Yeah I had thought of that too, but I didn't want to recommend it since database names really shouldn't change. What benefit does it offer except in Aj's case he'll know what version it is quickly? Why not just store this information in a database table instead? I know that some people change database names for their non-prod environments, but a database name should not change in production. IMHO, of course.Tara |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-05-19 : 16:47:35
|
| Agreed Tara.Just because somethings possible it doesn't mean it's a good idea.Reminds me ofJust because somethings released it doesn't mean it works or is a good idea to use.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ajthepoolman
Constraint Violating Yak Guru
384 Posts |
Posted - 2003-05-20 : 09:40:32
|
| The software we are developing is nearing completion. We rollout new changes about every week to our testing department and about every 3 weeks to the end users.We thought it would help us out by renaming the database so it showed which version of the software it was most compatabile with. For instance, xxx_2_2_Test or xxx_2_3_Prod. We do have a "system" table that stores that information now, but we have not fully modified the code to retrieve that information and check its own compliance.I was dinking around with this yesterday and did come up with a short cursor that will loop through the Master table looking for a database name that is "LIKE" the one I need. Then I use that name to dynamically set the DatabaseName in my job step. I will test it more today.Thanks for all the input!Aj |
|
|
|
|
|
|
|
|
New to Jobs