Re: Use SQL to get Filename...
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 01/22/05
- Next message: Michael C#: "Re: Determining CPU requirements for Datawarehouse"
- Previous message: JRStern: "Re: how can I list running jobs"
- In reply to: Mandy: "Re: Use SQL to get Filename..."
- Next in thread: DHatheway: "Re: Use SQL to get Filename..."
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 21 Jan 2005 17:24:25 -0800
I'm glad it worked!
-- HTH ---------------- Kalen Delaney SQL Server MVP www.SolidQualityLearning.com "Mandy" <clafarge@NOSPAMgraphiced.com> wrote in message news:eZGw29AAFHA.3472@TK2MSFTNGP14.phx.gbl... > Wonderful. > > Sorry, I didn't realize that was why did had the Execute. I didn't know > you couldn't use variables. > > The DB is now being created successfully. > > Thanks so much for your help! > > > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:OuzTRpAAFHA.1296@TK2MSFTNGP10.phx.gbl... >> Mandy >> >> Filenames cannot actually be variables. Please read the full example I >> posted. That example builds a TSQL command from string constants plus >> variables and then puts the whole thing in an EXEC string. >> >> Please read about dynamic execution for more examples. >> >> Also, in the future, note that just saying that something fails is >> usually >> not helpful. If you get an error, tell us what the error message says. >> -- >> HTH >> ---------------- >> Kalen Delaney >> SQL Server MVP >> www.SolidQualityLearning.com >> >> >> "Mandy" <clafarge@NOSPAMgraphiced.com> wrote in message >> news:efA0AfAAFHA.3368@TK2MSFTNGP10.phx.gbl... >> > Excellent! Thank you! >> > >> > NOW I'm having implementing it :(. See below for code. @dbFileName >> > and >> > @logFileName ARE getting the correct values, but it fails >> > on: >> > FILENAME = @dbFileName, >> > >> > Any idea why? >> > >> > Thank you. >> > >> > DECLARE @newDBLocation NVARCHAR(520), @dbFileName NVARCHAR(520), >> > @logFileName NVARCHAR(520) >> > SELECT TOP 1 @newDBLocation = SUBSTRING(filename, 1, >> > CHARINDEX(N'master.mdf', LOWER(filename)) - 1) FROM sysfiles >> > SELECT @dbFileName = @newDBLocation + N'TLCDATABASENAME_Data.MDF' >> > SELECT @logFileName = @newDBLocation + N'TLCDATABASENAME_Log.LDF' >> > >> > CREATE DATABASE [TLCDATABASENAME] ON ( >> > NAME = N'TLCDATABASENAME_Data', >> > FILENAME = @dbFileName, >> > SIZE = 100, >> > FILEGROWTH = 10% >> > ) LOG ON ( >> > NAME = N'TLCDATABASENAME_Log', >> > FILENAME = N@logFileName, >> > SIZE = 100, >> > FILEGROWTH = 10% >> > ) >> > GO >> > >> > >> >> Hi Mandy >> >> >> >> Take a look at the sysfiles table, available in each database. >> >> There is also a legacy table called sysdevices in master, that >> >> contains >> >> the >> >> filenames of the system databases. The script to build the Northwind >> >> database uses that table, and constructs a new file path based on >> >> that. >> >> You >> >> can see that script in your SQL Server \install directory, but I will >> >> copy >> >> key parts here: >> >> >> >> DECLARE @device_directory NVARCHAR(520) >> >> SELECT @device_directory = SUBSTRING(phyname, 1, >> >> CHARINDEX(N'master.mdf', >> >> LOWER(phyname)) - 1) >> >> FROM master.dbo.sysdevices >> >> WHERE (name = N'master') >> >> >> >> EXECUTE (N'CREATE DATABASE Northwind >> >> ON PRIMARY (NAME = N''Northwind'', FILENAME = N''' + >> >> @device_directory >> >> + >> >> N'northwnd.mdf'') >> >> LOG ON (NAME = N''Northwind_log'', FILENAME = N''' + >> >> @device_directory >> >> + >> >> N'northwnd.ldf'')') >> >> go >> >> >> >> -- >> >> HTH >> >> ---------------- >> >> Kalen Delaney >> >> SQL Server MVP >> >> www.SolidQualityLearning.com >> >> >> >> >> >> "Mandy" <clafarge@NOSPAMgraphiced.com> wrote in message >> >> news:Oriscg$$EHA.1300@TK2MSFTNGP14.phx.gbl... >> >> > How do I get the FileName for a Database using TSQL (through >> >> > OSQL.exe)? >> >> > >> >> > I'm writing a script to create a new Database, and I need to be able >> >> > to >> >> > generate the CREATE DATABASE tag's FileName parameter based >> >> > on where it belongs on any given MS SQL Server. This includes MS >> >> > SQL 7 >> >> > and 2000. Is there a good way to do this? >> > >> > >> >> > >
- Next message: Michael C#: "Re: Determining CPU requirements for Datawarehouse"
- Previous message: JRStern: "Re: how can I list running jobs"
- In reply to: Mandy: "Re: Use SQL to get Filename..."
- Next in thread: DHatheway: "Re: Use SQL to get Filename..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
Loading
