SQL Scripts

[Earl]

I've got the install package finally doing most of what it should do, but it still fails to create the database with the MS SQL SERVER object. I've got a fairly lengthy script in there so I'm curious what the ins and outs are of script length? Any problems running multiple scripts? Also, the ANSI warning -- what would happen were it NOT an ANSI script?

[jimo]

Earl,

Try downloading the Alternate SQL Server Plugin from the plugins forum and use it. The advantage of the Alternate SQL Plugin is that it reads in your scipt at compile time and uses a different connection method to connect to the SQL Server.

[Earl]

Thanks Jim. I'll give it a go. I've noticed that the plugin is NOT creating my database on the server, despite being checked to do so. Server went in fine, as did all other prerequisites, it simply refuses to create the database (logging in as 'sa'). I did not use mixed mode, as I did not want to have to deal with a password -- could this be the issue?

[Earl]

The new plugin is pretty cool -- much easier to work with. And it did in fact create my database this time, so that's a big improvement. But the script still is not creating tables and sprocs. Not really sure what to check either -- clearly the plugin is executing because it creates the database. Anything that would stop the script from running?

[jimo]

If the database is being created thats half the battle, have you run this script in a query window? Any errors, however insiginificant may cause the script to stop executing.

Also, if you are using the "Create database if absent" check box, trying adding "GO" as the first line of your script.

[Earl]

That's some helpful advice. I've spent the better part of the afternoon reviewing my scripts and what I found was interesting. The tables and stored procedures (about 800 altogether) were initially brought over from SQL2k. But when I scripted the same procedures out of SQLEXPRESS, I noticed I was getting double quotes for singles and a single quote right before END statements. Probably half of the 800 procedures would never have functioned! I'm baffled as to what is causing that, but I'm sure I'll get to the bottom of it.

[Steve]

Hi Earl;
I've been struggling with same problem that you describe for quite some time now.
I have found that in both IA native SQL Database and in the new Alternate SQL, my script will not create the database no matter how I approach it. If I enable the 'Create database if absent' checkbox both native and alternate modules will create the database, but since the script is not run there are no tables or SP's created at all.
This tells me that IA can communicate with the SQL instance in both native and alternate modes and that IA in fact can communicate with SQL express for the purposes of creating a mdf, however executing the script simply does not happen.

With reagrd to the single quote/double quote issue, SQL express is supposed to be able fully backward compatible (able to run the SQL 2000 script in Express) but unfortunately not the other way around.
If indeed the problem turns out to be the quote format, then unfortunatly our setups will be unable to create databases for anything other than SQL Express.
I have tested and found that running an SQL 2000 script directly in SQL Express Management Studio (Query) will in fact properly create the database. Thus my conclusion at this point is that the problem lays in IA's inabilty to execute the script both in native and alternate modes.

I have offered to send IA support a completely stripped down IA project that exhibits this problem. I'll update this post with the results when I hear back from them.

I know that this does not resolve your problem but I thought that my test efforts to date may provide you some clues or save you a bit of testing time.

Best regards,
Steve

[MichaelNesmith]

We have reports from the field that the latest standard SQL plug-in included in the 6.14 package has resolved connection and execution issues. Please give it a try!