MSSQL Plugin

Same problem with back button

I actually have the same problem. I'm attaching my script.

Hello,
I compiled your script (changing only the sql server name and credentials) and everything worked.
Can you explain in more detail the problems you are experiencing?

Hello,

The problem is when you've hit the back button after the DB name page. This is what I did...I entered the db name and click next and it works fine...takes me to the components page. But when I go back to the DB page from the components page and enter the DB name again then the installer crashes.

Why does it crash?

Also, try entering a DB name that doesn't exist in your server and click next. There'll be a pop-up message. Click 'No' on that pop-up. Try to enter the DB name again and click Next. The installer crashes.

I hope I've explained clearly.

That's a bug.
I am working on that already. I will release a fixed version soon.
The bug is that when you close the connection to the db not all the variables are cleared (but they are released). So when to connect again you will try to use some unallocated memory.
Regards
Stefano

Plugin version 2.0.1 released.
Fixed the allocation bug.
Fixed documentation too (windows versions compatibility)
Regards
Stefano

Installer crash

Hi,

I installed the plugin and compiled the MSSQL_OLEDBTest.nsi without errors.
If I try the installer on a machine with SQL Server 2000 the installer crashs during logon without any message.
The logon credentials are correct.
I tried it on 3 different machines.

Do I miss something.

cornhoulio

Hello,
what is the size of installer?
are the plugins placed in the tmp directory at execution time?
do you fill in the right values in the dialog asking for server name and credentials?
Stefano

Hi Stefano,

the size of MyApp.exe is 125kb.
I can't see the plugin dll in the temp dir of the installer.
I have to correct my statement. The installer crashes after the screen choose installation location.
My own script crashes during log on.

cornhoulio

Try the attached installer, on my pc works fine
Regards
Stefano

Hi Stefano,

your installer works also fine for me.
But I still unable to build my own installer with this plugin.
I am using NSIS 2.35. I copied all files from the plugin zip archive to my NSIS install directory.
What is wrong?

cornhoulio

I think you should upgrade to version 2.42 at least, since the plugin is written for the new plugin-api

Stefano

I'm trying to run a simple test of this in a function, it blows up right after login.

Running the test nsi in the .zip seems to work fine, so I guess I'm doing something wrong/unexpected. I'm just learning NSIS so that seems pretty likely 🙂

Here's what I have in the function

MSSQL_OLEDB::SQL_Logon "$sqlsvr" "" ;"$8"
pop $0
messagebox MB_OK $0 ; returns 0
pop $0
messagebox MB_OK $0 ; returns 'Login Successful'
MSSQL_OLEDB::SQL_GetError
Pop $0
messagebox MB_OK $0 ; returns 0
Pop $0
messagebox MB_OK $0 ; returns 'SQL State: 0x00e143d8 - Native: 0 - Message:'
MSSQL_OLEDB::SQL_Execute "select @@Version"
Pop $0
messagebox MB_OK $0
Pop $0
messagebox MB_OK $0
MSSQL_OLEDB::SQL_GetError
Pop $0
messagebox MB_OK $0
Pop $0
messagebox MB_OK $0
MSSQL_OLEDB::SQL_GetRow
Pop $0
messagebox MB_OK $0
Pop $0
messagebox MB_OK $0
MSSQL_OLEDB::SQL_GetError
Pop $0
messagebox MB_OK $0
Pop $0
messagebox MB_OK $0
MSSQL_OLEDB::SQL_Logout

[B]

The installer crashes after the MB_OK click with the SQL State: error.

Unhandled exception at 0x00b41be4 in Mongoose.exe: 0xC0000005: Access violation reading location 0x00000000.

The debug break pointer is on MSSQL_OLEDB.dll:
00B41BE4 mov eax,dword ptr [ebp]

This happens consistently whether I'm executing a sql file or just a command. SQLOLEDB works fine, and i see nothing in a trace to indicate a problem with SQL. Nothing suspicious in the event logs otherwise.

I tried redirecting the last popped $0 back to a label, but no dice.

running under:
Windows 2003 SP2, SQL 2005 SP3.
NSIS version 2.46

If anyone has any other ideas for SQL providers in NSIS I'd be greatful; i have an install proejct that needs to use a ton of it.

Thanks in advance for any help.

Update: Adding /NOUNLOAD to all of the MSSQL_OLEDB calls seemes to have resolved the issue.
I thought /NOUNLOAD wasn't needed anymore?
In any case, its working but if anyone has any other ideas or tips for using NSIS with SQL I'd love to hear them.

Are you using latest version (2.0.1) of the plugin?
It seems you are using an older version, please check file version of MSSQL_OLEDB.DLL in $PROGRAMFILES\nsis\plugins folder.
I use the plugin for a lot of stuff related to sql server and have no problems.

It appears that was my problem, or at least partially

The version of MSSQL_OLDEB in Mssql_oledb2.zip on the wiki is 2.0.0 (Aug 1 2009), you mentioned 2.0.1 ?
Where do i get that version?

Also, you might consider taking the old .zip off the wiki or making the distinction between the all-caps MSSQL_OLEDB.zip (version 1.4.0) and Mssql_oledb2.zip (2.0.0) more clear.

It's stuff like this that makes me want to run back in to the cold, uncaring arms of InstallShield......okay not really, but this wiki-and-forum based mess is pretty horrific.

Finding a plugin [that works] is hard enough, finding the right file in the right thread instead of the barely distinguishable files attached all over the wiki (some have multiple wiki pages with multiple versions of files!!) is a train wreck. Get what you pay for i guess?

Sorry for ranting. I do very much appreciate your work, sgiusto. Cheers to you.

Thank you for ranting!
I updated the plugin page. Now the download section should be easier to understand.
The dll in the zip file is version 2.0.1. I updated only the 'product version' field in the resource file and not the 'file version' field.
Please check if 'product version' is 2.0.1.
I will fix this in a future release.

I really like the MSSQL_OLEDB plugin, I use it to build the application DB from SQL scripts.

However my application is quite large and has heaps of stored procedures. I dont really want to put each stored proc in its own file - it's too much of a maintenance overhead.

What I have dont instead is:
1. get NSIS to open the file
2. iterate through the file line by line, creating an SQL string
3. When the loop hits a "GO", run the script then continue iterating

See here for an example:


This seems to have worked quite well for me. I dont know if this is the preferred method, but as a few people has asked about it I thought I's share!

I use similar approach.
I store each long statement in a separate file, then I parse a text file like this:
X <path to script to execute>
I <short sql statement to be executed>
X <path to other script>
I <sql command>
I <sql command>
....


Each version of the application has its own text file with scripts specific to that version:
20091012.txt
20100101.txt
and so on.

So a fresh installation will execute all the scripts, an update will execute scripts from previous version up to current.

Access violation bug

Hello.

Did anybody try to run this plugin using Unicode build of nsis? I've tried to make it work for 2 days but failed due to installer crush on ExecuteScript function. All other functions work almost well, but when I call "ExecuteScript" I just get AV exception.

The same project runs perfectly under ANSI build.

It's good that source code is included, but not all files are present, so I couldn't fix this bug:
You're allocating memory:

line 356: Command=(TCHAR *)HeapAlloc(heap,0,fileLen+1);

and then you insert last '\0'

line 376: Command[pos]=_T('\0');

but in Unicode it inserts 2(!) chars and you run out of borders of "fileLen+1". The best practice is to allocate +10 bytes(or to use +sizeof(TCHAR)) to avoid such "bugs".

Sample installer fails also.

P.S. Why you are so stingy when allocating

line 208: TCHAR command[1024];

for SQL query? 1024 is not enough for good script of procedure creation. Why it's not mentioned in wiki about this hidden restriction which causes 100% AV on large queries?

Hello,
I will check and correct this issue, when I checked the test installer in unicode I didn't got any error...
I will add the 1024 limit to the documentation. If you need longer commands I suggest to use ExecuteScript (if it works... 😁 )
I provide the whole source code with the plugin. The files you are missing are prolly part of nsis sources.

Regards
Stefano

Thanks for fast reply.

This kind of errors may happen and may not depending on variety of conditions, starting from complier and ending up with irregular sun activity. 😉
I use this config:

NSIS v.2.45.1-Unicode
UMSSQL_OLEDB 2.0.1.0
Testing on Windows 7 and Windows Server 2003

Moreover, on Win2k3 installer had three types of behavior:
1) autoclose on error without any messages (~80%)
2) proposal to start debagger(~19%)
3) "as supposed to run" (~1%, 1 or 2 lucky cases)

If you need longer commands I suggest to use ExecuteScript

I tried to, but failed. I began a search for solution towards "Execute" and was very disappointed, when it couldn't stand my 16KB SQL script 🙂

I have been using this plug in for two years and have been very pleased with the results. My installer setups up our customer with SQL 2005 Express and then loads the database.

Recently, more of our customers have started using SQL 2008 Express. I have also decided that as most of our customers are now using Server 2008 and Windows 7, I need to switch to installing SQL 2008 Express as 2005 generates Known Comparability messages that in turn create tech support calls.

I have run into a problem with this plug in when trying to access a SQL 2008 Express server. It fails with this message.

Could not connect to SQL Server : SQL State: 0x80004005 - Native: 0 - Message: [DBNETLIB][ConnectionOpen (Invalid Instance()).]Invalid connection.

The same code works fine with SQL 2005 and MSDE.

Has anyone else been having this issue?

Hello,
I ran the example given with the plugin against a windows 7 ultimate 64bit with SQL 2008 express using SQL security and got no problems.

I am using the plugin to deploy my application with sql2008 without problems.

From the error code you posted it seems you have some kind of problem with a named instance.
Hope this helps
Stefano

SQLServer 2008

Hello, I tried it with a SQLServer 2008 (default instance) and was not able to connect to SQLServer. (No problem with SQLServer 2005 upgraded to 2008...)
Does any body have the same problem? Does anybody know how to workaround or if a fix is needed in the plugin? (Or if I'm missing any special configuration of the plugin needed for SQLServer 2008...)

Many thanks!

Hello,
what error code is returned upon failure?
please read my previous post. I use sql 2008 without any problem.
Regards
Stefano

Timeout

Hello there,

i use your plugin in my installer and most of the time it works really great.
Thanks for your great work!

My installer installs a local MS SQL Server 2008 Express R2 during setup.
Later in the installation process i use your plugin to do a connection test
against the (already restarted and fully running) DB using named pipes.

MSSQL_OLEDB::SQL_Logon "127.0.0.1\InstanceName" "user" "password"

On a small number of machines im getting a sql timeout from your plugin when doing this.
I've made a loop around the logon part and let the user repeat the connection test.
Sometimes it works on the third or fourth attempt.

What timeout do you use internally in your plugin for the logon cmd?
Is there a way to increase the timeout?
Is it maybe possible that you expose the timeout parameter in your plugin?

Regards
Swordd

Logon Timeout

Hi,

I have been using this plugin for a couple of years and I am really happy with the results thus far.

However, I have been getting some issues similar to the previous post.

My installer creates a new named instance of SQLServer 2005 Express, connects and runs scripts to create the applications database.

This had been all working fine, until a client tried installing it on a box with SQLServer 2008. It is now timing out, trying to connect to the database.

I have not yet tried a loop, as per the previous post.

Hello,
connection timeout could be set before opening the connection, I will consider adding such a feature in future.
A little research on google show that connection timeout problems with sql2008 are no so infrequent. Changing the timeout value could not fix the problem.
Are you using SQL Express, Standard, Enterprise?
Are you using default instance or named instance?
What is your default protocol?
Is the sql box local or remote?
My tests are negative so far (the connections are working as expected).
Give me some more details, so I can make more focused tests.
Regards
Stefano

Hi Stefano,

In my situation, the answers to your questions are as follows:
* SQLExpress
* Named Instance
* Shared Memory (but network protocols are all switched on)
* Local Box

Some other details
* oSQL worked fine
* ODBC connection (using SQL Native Client) testd successfully

I am also doing some more testing to see if there is some specific combination that sets this off. I will post as soon as I have more information.

As an aside, could the OLEDB driver be simply interchanged with SQL Native client?
If so, could that be made parameter?

David

Originally Posted by sgiusto View Post

Hello,
Are you using SQL Express, Standard, Enterprise?
Are you using default instance or named instance?
What is your default protocol?
Is the sql box local or remote?
Stefano

- SQL Server 2008 Express R2
- named instance
- default protocol is named pipe (IP also switched on)
- sql box is local

i tried various sql tools (osql, ems,...) no timeout problems there.
As a workaround i wrote a simple commandline tool in .Net to do all my database related stuff now. I call it from the installer with execwait and some params.
Works like a charme.

Regards

Originally Posted by swordd View Post

As a workaround i wrote a simple commandline tool in .Net to do all my database related stuff now. I call it from the installer with execwait and some params.
Works like a charme.

Rather off-topic, but: Note that your users may not all have .NET installed.