PDA

View Full Version : SQL support problem



ferland
06-30-2004, 03:16 PM
Hi,

I was at first really excited about SQL support in InstallShieldX. But now, it seems that some basic functionnality doesn't work well.

My problem: Valid SQL scripts doesn't work with InstallShield X. I get the "There was an error running the SQL Server script" message box really often.

I first discovered that scripts used by IS X cannot contain double quotes to set a string value. Fine!
Then I read a post about Unicode file not supported. No problemo, now that I know it.

Knowing this, I still have non-working scripts, who are still valid if you execute them in a query analyzer.

I also turned a non-working script into a working one without changing one line of my script. But I don't know how. I thought I had a receipe to reproduce it by playing with read-only attributes and/or file names with spaces, but I wasn't able to reproduce it.

When the script is not working, I can see in the SQL trace (Profiler) that not a single line of my script was executed.

So I have some questions:
Is there an issue about the read-only status of a SQL query file?
Is there an issue about the length of a SQL query file?
Is there an issue about using the SQL keyword "use database" in the script?
AND the most important, what IS is doing with the script before passing it to SQL server for the execution??

Thanks a lot for any kind of feedback. I can also give you any kind of supplemental of information and files you need.

miryamgb
07-07-2004, 09:25 AM
Hi ferland

I had a problem whith unix style new lines.

If you open sqript in Notepad you can see the special symbol insted of new line.

Do have get any additional information from the log file?


1. Haven't tried read only files
2. My longest script just 1.5 Mg
3. I use separate connection for each database


Miryam

ferland
07-16-2004, 08:14 AM
I didn't see new line characters when opening my scripts in Notepad.
And I didn't found useful info in the Log.

What I saw really often, in the SQL Profiler (the trace tool for SQL) was some thing like this:

My script:
select * from myTable

What I saw in Profiler:
select * from select * from myTable

It's like the end of lines are all mixed up.

PilotBob
07-16-2004, 03:55 PM
Double quotes are only supported for strings if you have SET QUOTEDIDENTIFIER OFF. Do you have this at the top of your scripts? Generally you should use single quotes for strings in SQL... that works no matter what the QUOTEDIDENTIFIER SETTING is.

BOb

ferland
08-02-2004, 03:36 PM
Thanks for your advice.

I eliminated all double-quotes before. That was part of the problem. But I still have problems. In the coming weeks, I'll try to work on a simple receipe to reproduce the problem.

skim35
08-03-2004, 12:06 PM
I ran into the same problem.

I've been working with IS X for about two weeks. Start out the database connection by going through UI & SQL wizard. After some code modification, the system stop running my SQL statements. I found out that call to SQLServerSelectLogin() function is required. Even if IS provide other functions for collecting SQL login data, none of them work unless you make call to this function.

Then I started to get this "There was an error running the SQL server Script. Setup will now terminte" message.

I debugged through the code, located the source of the error message: ISSQLServerComponentInstall(STRING).

The function has one parameter (component name) and verified to be correct. My SQL component has only two statements:

CREATE DATABASE dbname
GO

The file is in ASCII, copied & pasted into installshield from note pad (I don't expect any garbage characters in text stream) Now I am pretty sure that this is a problem within InstallShield X.

gemini_neo
10-06-2004, 12:40 PM
Hi All
There is some problem in executing SQL statement from Installshield.
Remove all 'GO' statement from your script, except before and after statements like create stored procudure or create function.
I dont know reason of that but this works at my end . you can also try this.

Gavin Johnson
02-24-2005, 05:09 AM
I have a large script which executes correctly through InstallShield X, containing table creates and stored procedure creates with lots of GO statements.

The following Stored Procedure fails to execute regardless of what I do. The statement works fine in osql and isql/w, so what does Installshield do to the statement to 'mash it up' before it sends it to SQL Server, and what can I do to resolve it?

The nested IF may well be the straw that breaks this camel's back...

Stored Proc:

/*---------------------------------------------------------------------
Declare all program variables together.
---------------------------------------------------------------------*/
DECLARE @Error int, /* error number*/
@SPName varchar(50)


SET @SPName = 'usp_DI_AuditLog_Select_V1'

/* If we haven't passed an audit id, set default */
if @AuditID is null SET @AuditID = 2000000000

if (@JobRef is null)
/* No job specified */
SELECT TOP 500 * FROM DI_AuditLog
WHERE DIA_AuditLogID < @AuditID
ORDER BY DIA_AuditLogID DESC
Else
if (@JobEntryRef is null)
/* No job entry specified */
SELECT TOP 500 * FROM DI_AuditLog
WHERE DIA_JobEntryRef IN (SELECT DIJ_JobEntryRef FROM DI_JobEntry WHERE DIJ_JobRef = @JobRef)
AND DIA_AuditLogID < @AuditID
ORDER BY DIA_AuditLogID DESC
Else
/* Job entry specified */
SELECT TOP 500 * FROM DI_AuditLog
WHERE DIA_JobEntryRef = @JobEntryRef
AND DIA_AuditLogID < @AuditID
ORDER BY DIA_AuditLogID DESC

/*---------------------------------------------------------------------
Immediately check the value of @@error for successful condition.
---------------------------------------------------------------------*/
IF ( @@ERROR <> 0 )
BEGIN
/* Report an error and rollback the transaction if required*/
SET @Error = @@ERROR
RAISERROR ( 'ERROR:%d <%d>: Select Failed',16,1,@Error,@SPName)
RETURN @Error
END

RETURN 0
go



--Further Info

When running trace on executed SQL statement, it would appear that whatever parser is running before the statement gets to SQL Server it is stripping out part of the stored procedure command!

Trace statement below :-

CREATE PROCEDURE usp_DI_AuditLog_Select_V1
@JobRef uniqueidentifier,
@JobEntryRef uniqueidentifier,
@AuditID int
AS
SET NOCOUNT ON
DECLARE @Error int,
@SPName varchar(50)
SET @SPName = 'usp_DI_AuditLog_Select_V1'
SELECT TOP 500 * FROM DI_AuditLog
WHERE DIA_AuditLogID < @AuditID
ORDER BY DIA_AuditLogID DESC
Else
if (@JobEntryRef is null)
SELECT TOP 500 * FROM DI_AuditLog
WHERE DIA_JobEntryRef IN (SELECT DIJ_JobEntryRef FROM DI_JobEntry WHERE DIJ_JobRef = @JobRef)
AND DIA_AuditLogID < @AuditID
ORDER BY DIA_AuditLogID DESC
Else
SELECT TOP 500 * FROM DI_AuditLog
WHERE DIA_JobEntryRef = @JobEntryRef
AND DIA_AuditLogID < @AuditID
ORDER BY DIA_AuditLogID DESC
IF ( @@ERROR <> 0 )
BEGIN
SET @Error = @@ERROR
RAISERROR ( 'ERROR:%d <%d>: Select Failed',16,1,@Error,@SPName)
RETURN @Error
END