February 8, 2011 at 9:09 am
I have a situation where I have a mixed environment of 1 32-bit WinXP box (mine - the lowly DBA), and several other 64-bit Windows 7 boxes (my developers). 😉
The issue is specific to the creation script to several databases we are currently working on. When my developers need to run a newly updated copy of a given db's creation script, then are always having to change the path location for the .MDF and .LDF files (because they put theirs under the (x86) Program Files directory where I simply place it under the normal Program Files directory - the one that is always in place as it comes out of version management).
The issue comes in when they forget to change this path. They wind up with the script throwing errors, but all of the tables are then created under the master db, because the script starts off with "USE master".
Is there a clean way for me to ensure that the master doesn't continually get populated in their local instances, or is this just the way it's going to be for those who don't look before their leap?
Thanks for any and all input!
February 8, 2011 at 9:35 am
so your script is using a hardcoded path, and sometimes the path does not exist, right?
i'd build the path to the files based on the existence of data i can discover in the database: if there's a "Program Files (x86)" in sysaltfiles, you know it's a 64 bit with a 32 bit installation...otherwise everythings in plain old Program files, right?
declare @path varchar(200)
if exists(select * from sysaltfiles Where [filename] like '%Program Files (x86)')
SET @path = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA'
else
SET @path = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA'
Lowell
February 8, 2011 at 1:37 pm
Awesome! Thank you for this gem!
February 8, 2011 at 1:49 pm
glad i could help!
Lowell
February 9, 2011 at 8:27 am
I'm actually still having issues implementing this feature. I am getting errors where LIKE and ELSE are at. Not certain why though.
:ermm:
February 9, 2011 at 9:21 am
Rich,
can you run the select within the exists statement? I had to add master.. in front of sysaltfiles
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 9:25 am
Mike - great specific question! No - that is where I am getting the error Incorrect syntax near the keyword 'LIKE'.
Any thoughts?
February 9, 2011 at 9:30 am
Quick additional note...
If I remark out the LIKE portion of the SELECT statement (and set the name to simply look for the 'master' entry - which the SELECT state will perform fine), I still get the following errors with the SET and ELSE portions of the query...
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'ELSE'.
February 9, 2011 at 9:32 am
Can you paste the syntax you're using?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 9:39 am
Absolutely!
DECLARE @PATH VARCHAR(200)
IF EXISTS(SELECT * FROM sys.sysaltfiles WHERE [NAME] = 'master' LIKE '%Program Files (x86)')
SET @PATH = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'
ELSE
SET @PATH = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\'
I'd like to put this at the beginning of my db creation script, so it will determine what to use. I am thinking that I need to sets of entries though in order for this to work with the initial creation - yes? Like for example. . .
USE [master]
GO
--Drop the Database if it already exists
IF DB_ID('DB_NAME') IS NOT NULL
BEGIN
DROP DATABASE DB_NAME
END
/****** Object: Database [DB_NAME] Script Date: 12/30/2010 10:30:00 ******/
CREATE DATABASE [DB_NAME] ON PRIMARY
( NAME = N'DB_NAME_dat', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DB_NAME.mdf' , SIZE = 4288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'DB_NAME_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DB_NAME.ldf' , SIZE = 1536KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
--Would I also need to put another set of CREATE DATABASE lines here for the non(x86) paths?
Thanks again, Mike
February 9, 2011 at 9:53 am
Rich,
the problem is with the where clause. You have where name = 'master like ... This won't work as you found out. Try this:
USE [master]
GO
--Drop the Database if it already exists
IF DB_ID('DB_NAME') IS NOT NULL
BEGIN
DROP DATABASE DB_NAME
END
IF EXISTS(SELECT * FROM sys.sysaltfiles WHERE [Filename] LIKE '%Program Files (x86)')
begin
CREATE DATABASE [DB_NAME] ON PRIMARY
( NAME = N'DB_NAME_dat',
FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DB_NAME.mdf' , SIZE = 4288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )LOG ON( NAME = N'DB_NAME_log',
FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DB_NAME.ldf' , SIZE = 1536KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
end
else
begin
CREATE DATABASE [DB_NAME] ON PRIMARY
( NAME = N'DB_NAME_dat',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DB_NAME.mdf' , SIZE = 4288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )LOG ON( NAME = N'DB_NAME_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\DB_NAME.ldf' , SIZE = 1536KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
end
GO
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 10:13 am
I guess I don't know how to format the filename after the WHERE statement. I've tried it in brackets, single quotes, and using another 'name' in the sys.sysaltfiles result set (like mastlog), and I keep getting errors.
/****** Object: Database [OnDemand] Script Date: 12/30/2010 10:30:00 ******/
IF EXISTS(SELECT * FROM sys.sysaltfiles WHERE mastlog LIKE '%Program Files (x86)')
Msg 207, Level 16, State 1, Line 9
Invalid column name 'mastlog'.
February 9, 2011 at 10:17 am
the where clause is looking for a column within the table specified in the from clause. I think you're trying to use the files from the master database to compare to the '%Program Files' string, but you don't need to. If you just use the filename field in the sysaltfiles table to compare, then it will work. Since you are using exists it will return true if the string appears in any database. You are jus tchecking for the existence of the folder. It will exist if any database was setup with files in this folder (not just master).
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2011 at 10:30 am
Fixed it...One of my programmers noticed that the %Program Files (x86) was missing the additional '%'. I had previously tried that on some earlier code, but we also switched by to [Filename], and the combo of the 2 works fine now.
Many thanks again Mike!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply