SQL Server Performance Forum – Threads Archive

Automated DB Creation by script

I have a text file db.txt which has db details seperated by ":" like foldername<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />bname:loginname<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />wd:mdf Now I need to create a dbcreate script which will read this .txt file, and browse the path bassed on foldername for db folder and create the .mdf and .ldf there with loginname and user.Moreover Username=loginname. something like inserting information is sysdatabases,syslogins, The structure of datapath will be as under There is a file server,db server each on different machine.These databases will be created on a seperate machine(named fileserver) There exist a db folder in file server as Ddataaskaskblahdbif foldername is ask…. db should be created in Ddataaskaskblahdb and so on Here is what I have tried doing for dbcreate script,but I feel I am going wrong <% Dim con, rs, folder, line Set con=Server.CreateObject("ADODB.Connection") set rs=server.CreateObject("ADODB.Recordset") con.Open "PROVIDER=SQLOLEDB;UID=xxx<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />wd<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />xxx;server=xxx.xxx.xx.xxx" set fsobj = server.CreateObject("Scripting.FileSystemObject") set getfsobj = fsobj.OpenTextFile("C:db.txt") while not getfsobj.AtEndOfStream line = getfsobj.ReadLine line = split(line,":") folder = trim(line(0)) nsql= "insert into sysdatabases (name,dbid,sid,mode,status,status2,crdate,reserved,category,cmptlevel,filename,version)values() " rs.Open nsql,con %> Please suggest me how to achive my desired goal

Lesson #1
Don’t insert into system tables. If you know about the existence of system tables, then you should also know about not touching them. You can create all sorts of objects using the part of the T-SQL language that is known as "DDL" (CREATE DATABASE, CREATE TABLE, etc.). You can handle all security issues through stored procedures (sp_addlogin, sp_addrole, sp_addrolemember, etc.). Lesson # last:
Read BOL.

Yes Adriaan I know that I should not touch system table and what I was doing was wrong.Can you pls tell me ,is it possible to use create database script using a loop to generate the db untill the end of file.Can you gv an example to create this.

You need to find a way to "merge" the parameters into a string that contains the entire series of DDL statements (starting with CREATE DATABASE, etc.). After the merge, you can execute the whole thing as a single statement, provided that your ADO object supports text this long (perhaps limited to 255 characters?). I’m not sure whether an ADODB.Recordset object is the proper choice for DDL, so you’ll have to debug or wait for an ADO expert’s opinion.

i would try and find some way you can use find and replace (in a text editor) to put in the CREATE DATABASE, etc…then u can execute the resultant script from QA ‘I reject your reality and substitute my own’ – Adam Savage

If I do Something like this ,I am getting error.I guess I am going wrong way..please guide <% Dim con, rs, dom, line,dbname Dim folder1 Dim folder2 Dim folder3 Const DIR = "D:data" Set con=Server.CreateObject("ADODB.Connection") set rs=server.CreateObject("ADODB.Recordset") con.Open "PROVIDER=SQLOLEDB;UID=xxx<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />wd<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />xxx;server=xxx.xxx.xx.xxx" set fsobj = server.CreateObject("Scripting.FileSystemObject") set getfsobj = fsobj.OpenTextFile("C:db.txt") while not getfsobj.AtEndOfStream line = getfsobj.ReadLine line = split(line,":") folder = trim(line(0)) dbname=trim(line(1)) nsql= "CREATE DATABASE ‘"& dbname &"" ON PRIMARY (NAME=’"& dbname & _dat &"’,FILENAME=’"& DIR & folder1 & "" & folder2 & "" & folder3 & "" & foldername & "" "”,SIZE=10,MAXSIZE=50,FILEGROWTH=10%),LOG ON (NAME=’"& dbname & _log &"’,SIZE=10,MAXSIZE=50,FILEGROWTH=10%)" rs.Open nsql,con %>

(1) Do a "manual merge" in QA, then run the statement and check the error messages. (2) BOL specifies no quotes around the database name in the CREATE DATABASE syntax.

do mean to merge create db,sp_addlogin,sp_addrole etc in a single batch

No, I meant to type out the whole string, including the values of the parameters, exactly as VBScript would write it out. Execute that in QA and you should see some errors. Probably just the quotes around the database name, which are syntactically incorrect.

If I replace the sql string above with sql= "CREATE DATABASE panther ON PRIMARY (NAME=’panther_dat’,FILENAME=’Ddatapa
pantherpanther_data.mdf’,SIZE=10,MAXSIZE=50,FILEGROWTH=10%)LOG ON (NAME=’panther_log’,FILENAME=’Ddatapa
pantherpanther_data.ldf’,SIZE=10,MAXSIZE=50,FILEGROWTH=10%)" its working fine.So I guess there is no problem with ADO ,May be I am missing or doing something wrong in syntax Same with if I do with sp_Addlogin its working but with the privious one for all the list I m getting error Say If I do like this to make db of all the names listed in db.txt file sql= "CREATE DATABASE "& dbname &" ON PRIMARY (NAME=’"& dbname & _dat &"’,FILENAME=’D:ADMINSQLDATAMSSQLData "& dbname & _dat & .mdf &"’,SIZE=10,MAXSIZE=50,FILEGROWTH=10%),LOG ON (NAME=’"& dbname & _log &"’,FILENAME=’D:ADMINSQLDATAMSSQLData"& dbname & _dat & .ldf &"’,SIZE=10,MAXSIZE=50,FILEGROWTH=10%)" I am getting following error Error Type: Microsoft VBScript compilation (0x800A040<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Invalid character /ServU/create_database.asp, line 23, column 66 sql= "CREATE DATABASE "& dbname &" ON PRIMARY (NAME=’"& dbname & _dat &"’,FILENAME=’D:ADMINSQLDATAMSSQLData "& dbname & _dat & .mdf &"’,SIZE=10,MAXSIZE=50,FILEGROWTH=10%),LOG ON (NAME=’"& dbname & _log &"’,FILENAME=’D:ADMINSQLDATAMSSQLData"& dbname & _dat & .ldf &"’,SIZE=10,MAXSIZE=50,FILEGROWTH=10%)" —————————————————————–^ Please suggest where I am wrong.

Moderators if this a wrong forum for this post please move this to devloper forum

Could it be this simple: there is no colon (<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> after the drive letter (D).

]]>