Scripting a database

I just starting working for a new client this week. They have a custom install program written in VB.NET which installs the database to SQL Server 2000. Normally, when I want to create a SQL script to create a database, I would go into Enterprise Manager, right-click on a database, and select All Tasks | Generate SQL Script. However, that's not how this client works. Much to my horror, the CREATE DATABASE, CREATE TABLE, etc. statements are both hard-coded and hand-coded within the install program itself. When I asked them why they weren't using SQL Server's scripting capabilities to automatically generate the script, they said that SQL Server scripting is buggy and that it doesn't work properly. It was hard getting specific details but they said it would leave out indexes, for example. Now, I'm no SQL Server expert, but I've never had a problem using SQL Server's scripting capabilities. The database they use isn't that big - maybe 20-30 tables. So, before I make an issue out of it, I was wondering what everyone else's experience with SQL Server 2000's scripting features? If you've used SQL Servers scripting to recreate a database, does it work? Did you have issues?The script is the same whether you run it all together through one or more script files versus issuing the commands one be one from an installer. Perhaps they had trouble with the ordering of the index creating script so that it was being called at the wrong time. Either that or some other dependancy was out of synch. In any event it shouldn't be the script per se but the order that it is being applied.

Maybe if you can see one of the script that failed you could figure out what's going on.|||They're not using SQL Server 2000's scripting capabilities at all. All the DDL statements are hand coded in the VB.NET installation program. This seems like a time-consuming and error-prone process. They said that the reason why they are not using SQL Server's script generation feature is because it is broken.|||What I meant was that the script is just a bunch of DDL as is the hand-coding. If there is a problem with the Sql Server generated script it's probably that something is in the wrong order and/or they didn't include the necessary checkboxes to includes indices "Options" tab.

But if their hand-generated DDL is working fine then I wouldn't mess with it. What they might want to do is place it all into a text file (don't call it a script...) and have the installer read that in and execute it. This text file can then be an embedded resource inside the install so that it's not visible to users but can be accessed inside the installer as if it were just another file. This at least would get all the DDL together in one place and not need to be intermixed with the code. There is a walk through for this in the VS.Net help system in the Windows installer section.|||Right now, the client has just one developer. However, my consulting company will probably be adding another 3 developers. I'm the architect/lead developer; we don't have a DBA. Including myself, that means a total of 5 developers will probably be working on the project. I'm worried that with 5 developers, the database could get f**cked up. Obviously, I need some sort of process to ensure the database 'integrity' remains intact.|||Are you just working with a single instance of the database or do you have to worry about multiple instances of the same db installed at different locations?

Do you have SourceSafe or equivalent? You could keep the scripts or installer code that does it there in order to maintain some control and accountability. You'd perhaps want to make it fine grained enough so that it's not just one file that someone always has checked out. I like to have "If then else" scripts for most database objects. If a table doesn't exist, create it. If it does exist check each column. If a column doesn't exist create it. This is a bit of a pain to set up initially but is relatively easy to maintain thereafter.|||There will be multiple instances of the database at remote locations to worry about. These remote locations will be our customers.

No, they are not using source control. We already discussed the importance of source control with them. We'll set up VSS as soon as we get additional developers.

Scripter and Database Mail

I currently have VB.Net program that scripts out all objects in a server for DR purposes. I just realized that I don't have DB Mail included. I have not been able to find a way to use the Scripter for this. Does anyone know if the Scripter can do DB Mail or will I need to resort to writing queries?

Hi greenie,

DB Mail objects cannot be scripted via Scripter class. We're looking into adding this functionality in a future release.

|||Thanks - I would certainly appreciate the functionality given the DB Mail configuration can get fairly complex. I guess my workaround will be to query the tables and output to my recovery file

Scripter and Database Mail

I currently have VB.Net program that scripts out all objects in a server for DR purposes. I just realized that I don't have DB Mail included. I have not been able to find a way to use the Scripter for this. Does anyone know if the Scripter can do DB Mail or will I need to resort to writing queries?

Hi greenie,

DB Mail objects cannot be scripted via Scripter class. We're looking into adding this functionality in a future release.

|||Thanks - I would certainly appreciate the functionality given the DB Mail configuration can get fairly complex. I guess my workaround will be to query the tables and output to my recovery filesql

Script Task errors

I have a custom .dll that I put in MS.Net framework 2.0 + Gac for my development environment to create packages. I have a script task that references this dll and the precompile option is set to "False". When I change the precompile option to "True" and remove the dll from .net framework 2.0, I get errors. My question is why doesn't dtexecui.exe utility check GAC since I have set my precompile property for script tasks from false to true?

Is it possible that the script task is not getting refereshed in the package? I checked the .dtsx in notepad and found the binary listed right below the actual script. Any help would be appreciated.

After changing the PreCompile option to true, please open the script editor and close it. At this moment the precompilation should happen and you should no longer need the .dll in .NET framework folder (well, at least not until you need to modify the script again).

If you are still getting the errors after this, could you be more specific about what errors do you get?

|||

I replced my script tasks and everything works fine. So the only thing I can think of now is that when I started developing the package, we were on June cpt then Sept. CTP. With RTM, I cannot get the package to compile and work without errors unless I remove the script task, readd it, and reset the reference. Any thoughts?

sql

Script Task

How do I specify a different script language in my "Script Task"?. The default is Visual Basic .NET. I would like to use c# for all my scripting task.

Thanks,

Sergio

You can only use VB.NET in 2005. You will be able to use C# in SQL Server 2008.
|||

Thanks for your reply, I guess I will be forced to learn vb.net. I am a c# developer and unfortunally this will slow me down since there will be a learning curve.

Thanks,

Sergio

|||

Sergio wrote:

Thanks for your reply, I guess I will be forced to learn vb.net. I am a c# developer and unfortunally this will slow me down since there will be a learning curve.

Thanks,

Sergio

I promise you, if you know C# its not a very steep learning curve at all.

Script for Publishing

Can the script files be written in any .net language? Writing code in VB
makes me ill.You could write a C# custom app using SOAP API.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"?" <?@.discussions.microsoft.com> wrote in message
news:A37994BD-2958-457F-9C93-295B93406201@.microsoft.com...
> Can the script files be written in any .net language? Writing code in VB
> makes me ill.

Script component to send email

Is there a way I can on any exception, call my .NET code using System.Mail in SSIS 2005 and use my custom code to send emails rather than the Mail component? The mail component sucks, you can't change the look & feel of the email being sent using CSS but if I can fire off my own code to send the email, then that would work great.

To ensure your script task fails on any error, you would create an On Error event handler and add the task in there.

The script task can use just about any .Net component but the system stuff is there already. Try System.Net.Mail for a start and see if that does what you want. If not you may have to get a third-party component, then just add a reference to it. For new components you will need to place them in the framework folder to be able to add a reference e.g. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\

|||So I would just do an OnError then a script component to call my .Net email function? Then just pass through the System variables to the script variable? cool.|||Yes, event handlers are great for this. No more messing about with workflow constraints for this type of package (container) wide handling.

SCOPE_IDENTITY() returning the ID value of an inserted record.

There are loads of postings on the net about this problem but none I have found explain the cause.

Whenever returning a value from a TableAdapter.Insert method followed by a SELECT SCOPE_IDENTITY() , the value returned is always 1. I have run the same select in SQL management studion and the correct value is returned but with a 1 showing in the column selector (just to the left of the first column. The column selector column is not data column. This must be the reason that issuing a SELECT after an INSERT does not work when using a TableAdapter isert method.

Has anyone come across the solution for this issue?

Thanks

Can you provide your code?|||

Here is the Stored Proc

set

ANSI_NULLSON

set

QUOTED_IDENTIFIERON

GO

ALTER

PROCEDURE [dbo].[InsertNewEnquiry_Client]

(

@.Salutation

varchar(10),

@.Name

varchar(60),

@.Address1

varchar(60),

@.Address2

varchar(60),

@.Address3

varchar(60),

@.Town

nvarchar(50),

@.PostCode

char(10),

@.County

char(60),

@.Telephone1

char(12),

@.Telephone2

char(12),

@.email

char(30)

)

AS

SETNOCOUNTOFF;

INSERT

INTO tblClient(Salutation,Name, Address1, Address2, Address3, Town, PostCode, County, Telephone1, Telephone2, email)

VALUES

(@.Salutation,@.Name,@.Address1,@.Address2,@.Address3,@.Town,@.PostCode,@.County,@.Telephone1,@.Telephone2,@.email);

SELECT

ClientID, Salutation,Name, Address1, Address2, Address3, PostCode, County, Telephone1, Telephone2, Telephone3, emailFROM tblClientWHERE(ClientID=SCOPE_IDENTITY())|||And can you show us the code which calls this stored procedure?|||int ClientID = Convert.ToInt32(clientTableAdapter.InsertStoredProc(cboClientSalutation.SelectedItem, txtClientName,text ......) );|||

The 1 your getting is most likely the default result of an Insert which is to return the number of records that have been entered.

I think you need to set the ExecuteMode property on your function to scalar.

|||

Actually, I've just looked at your sql again and your selecting an entire record at the end, not the identity on its own.

Simply put SELECT SCOPE_IDENTITY() if you want just the id returned, plus make sure you set the executemode on the function to scalar as I said before.

Hope that helps.