Archive: MSSQL Plugin
MSSQL Plugin
A new plugin to execute SQL commands, parse results and handle errors. Useful to create SQL devices, backup devices before program installation, check or modify SQL configuration, ...
http://nsis.sourceforge.net/MSSQL_OLEDB_plug-in
nsis.sourceforge.net/MSSQL_OLEDB_plug-in
Looks good. but can updates be accomplished with this?
Also when executing MSSQL_OLEDB::SQL_Execute which takes a string query can there be several statements in the string?
I'll answer my own issue. Updates will work as long as you just execute a single query at a time. Putting multiple queries wont work. Silly me forgot that simple principle. :P
Very nice :D
Stu
Is there any trick to getting the plugin to work with files. I am having trouble making this work:
MSSQL_OLEDB::SQL_ExecuteScript /NOUNLOAD "$INSTDIR\filename.sql"
It never completes. I call this after the file is installed to the hard drive so it should exist. I also use File filename.sql command in the beginning of the function. I have tested the sql file using the sqlcmd at the command line and it works without any errors.
Thanks.
The script must consist of a single SQL statement.
You cannot execute more than a command at a time
The sample script given with the plugin is working correctly?
Ok. So if I understand correctly, when the plugin executes a script file "somefile.sql" there can only be one command in the file?
I understand that using the Execute command it can only be one, but I thought with the ExecuteScript command the file that is referenced could have more than one command.
Thanks.
The problem is how to handle multiple result sets.
I whould have to parse the script file, execute the first command and prepare the result set. let the user get the rows of the first result set or handle the error and then execute the second command and so on.
It's just a bit too complex for an installation utility.
I could assume a file with a command per line. but this will limit the length of the single command that can be executed.
But the problem of the multiple result sets will remain.
Regards
Stefano
Where did the actual plugin go from that page?
The Zip file only contains an executable.
Stu
Where is the plugin? There is only exe example on that page
Hi there,
I fixed the zip file. Now it should be ok.
sorry for the inconvenience.
Thank you very much
Tell me what if I don't want to use SQL username and password and instead to use Windows securety? What do I need to do in MSSQL_OLEDB::SQL_Logon ?
leave username and password blank (empty strings). it will use integrated authentication
I was trying to logon to SQL 2005 and was not able.
While using SQLCMD I wasable to connect wihout any problem.
Do you have any idea what can be the problem? Is this plugin suppose to work with SQL 2005? Are there any special things that suppose to be installedon this PC in order for this plugin to work?
Thank you
The plugin use SQLOLEDB driver, and has been tested against SQL2000 and SQL2005.
Integrated security logon has been tested against sql2005 and works.
the syntax to use is:
MSSQL_OLEDB::SQL_Logon /NOUNLOAD "$SQLSERVER" "" ""
You must provide username and password as empty strings (you cannot omit the parameters)
The variable $SQLSERVER is the name of the sql box.
The SQL OLEDB driver is usually part of the operating system from windows XP and above. The SQL client installs it also and the MDAC package from microsoft does the same.
Hope this helps
Login Question
First, great! Really useful, thanks for all your effort!
Now to my issue, if I try to logon to a server at localhost\instanceName it doesn't login however if I try machineName\instanceName it does connect without any problems.
Any idea how to get around this? Using other tools I can connect via the localhost.
Thanks
------
:rolleyes: Seems to have been some interference between keyboard and chair.
Right way to do it : "(local)\instanceName"
Another way that works but is pointless! Look up the machine name in registry and use that.
Thanks for being a sounding board :D
You can also try 127.0.0.1\instanceName
Hi
I'm using actually something like this, and it works:
ExecWait "osql -U$SQL_User -P$SQL_Password -S$SQL_Server -d$SQL_DataBase -i$TEMP\sem2400.sql"
I want to use "MSSQL OLEDB plug-in", because I like attach method ;). That's greater than my vbs method.
But I don't find how to do this.
I'm trying this:
MSSQL_OLEDB::SQL_Logon /NOUNLOAD "$SQL_Server" "$SQL_User" "$SQL_Password"
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "sp_attach_single_file_db [$SQL_DataBase],[$INSTDIR\DATA\Semaphore_Data.MDF]"
(it works)
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "USE $SQL_DataBase"
MSSQL_OLEDB::SQL_ExecuteScript /NOUNLOAD "$TEMP\sem2002.sql"
(it don't works)
When i'm lauching my nsis excutable, it blocked and crash nsis executable. I think ExecuteScript can only use SQL file ,if "USE DATABASE" is defined under it.
Do you know something to do with ExecuteScript action?
Best Regards
Sorry for my english.
UP pls :confused:
As already mentioned, the SQL script can only contain one query instruction.
Stu
New Plugin Version Released (1.4)
Added support for SQL Scripts in Unicode format
This plugin seems to be able to handle whatever you throw at it in the sql script now (other than GO commands). Is that the case? For instance, I did something like this:
OutFile TestSQL.exe
Section "TestSQL"
detailprint "Log on to SQL server"
MSSQL_OLEDB::SQL_Logon /NOUNLOAD "machine1" "" ""
pop $0
detailprint $0
pop $0
detailprint $0
MSSQL_OLEDB::SQL_Execute /NOUNLOAD "use DB1"
pop $0
detailprint $0
pop $0
detailprint $0
MSSQL_OLEDB::SQL_ExecuteScript /NOUNLOAD "Script.SQL"
pop $0
detailprint $0
pop $0
detailprint $0
MSSQL_OLEDB::SQL_GetError /NOUNLOAD
pop $0
detailprint $0
pop $0
detailprint $0
MSSQL_OLEDB::SQL_Logout
SectionEnd
CREATE TABLE [dbo].[myTable](
[col1] [int] IDENTITY(1,1) NOT NULL,
[col2] [int] NOT NULL,
[col3] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[col4] [bit] NOT NULL,
[col5] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[col6] [bit] NOT NULL,
CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED
(
[col1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY];
ALTER TABLE [dbo].[myTable] WITH CHECK ADD CONSTRAINT [FK_myForeignKey] FOREIGN KEY([col2])
REFERENCES [dbo].[myOtherTable] ([col2]);
INSERT INTO [dbo].[myTable](
col2,col3,col4,col6)
SELECT 999,101010,1,0;
INSERT INTO [dbo].[myTable](
col2,col3,col4,col6)
SELECT 1000,101010,1,0;
INSERT INTO [dbo].[myTable](
col2,col3,col4,col6)
SELECT 888,100000,1,0;
INSERT INTO [dbo].[myTable](
col2,col3,col4,col6)
SELECT 889,100000,1,0;
UPDATE myTable SET col6 = 1 WHERE col3 = 101010;
UPDATE myTable SET col4 = 0 WHERE col3 = 100000;
Anybody?
The script will execute, but you will miss the result sets and/or the errors that will be raised by statements before the last one.
So it is better to have a single statement per file (better control of what happens)
Dear sgiusto,
Why the size of script file must be smaller than 60kb ?
Can I modify the default size in source file ?
Thanks.:)
Unicode Version
For the ones that asked, I managed to compile the plugin for the Unicode version of NSIS.
I attach a pre-release version, please test it since I do not have any unicode system ad hand.
Scripts are supportet in unicode format only.
Regards
Stefano
PS: this version coforms with the new plugin api. No need to use /UNLOAD any more.
Version 2.0 Released
Hello,
I just released version 2.0 of the plugin.
You can find it here:
http://nsis.sourceforge.net/MSSQL_OLEDB_plug-in
Major improvements are:
* Support for new plugin API (no more need to use /NOUNLOAD switch)
* Unicode version (unicode scripts are handled ONLY by unicode version)
* Unlimited script-file size
Regards
Stefano
Installer crash when using Back button
Hello.
I'm using MSSQL_OLEDB plugin. I do a logon, execute and logout in a page. Everything is working ok. Then I press next, and then back. When I then enter my SQL Page, only a logon is done and then the installer crash. Is there a special task I have to do if I would like to have the back button enabled?
Hello,
upon plugin initilization some static structures are allocated. Pushing the back button may cause the plugin to try to use some structure that is no more in memory (due to logout)
Can you post some example code so I can try it?
regards
Stefano
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
Archive: MSSQL Plugin
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"[B]
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
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:
http://ontheperiphery.veraida.com/co...tiple-commands
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".
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... :D )
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-UnicodeMoreover, on Win2k3 installer had three types of behavior:
UMSSQL_OLEDB 2.0.1.0
Testing on Windows 7 and Windows Server 2003
If you need longer commands I suggest to use ExecuteScriptI 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- SQL Server 2008 Express R2
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
Originally posted by sworddRather off-topic, but: Note that your users may not all have .NET installed.
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.
Originally posted by MSGthx for that hint :rolleyes: my installer takes care of that part anyway because the app is .net based.
Rather off-topic, but: Note that your users may not all have .NET installed.
Hi there,
I am also experiencing this logon timeout issue on 2008 R2 Server.
I'm using Standard Edition with a default instance, on a local box. It has failed with both Mixed Mode authentication and Windows Authentication only.
Let me know if you need any more information to help diagnose the issue.
Thanks.
Login to localhost\sqlexpress doesn't work on some machines
Originally posted by njpride
First, great! Really useful, thanks for all your effort!
Now to my issue, if I try to logon to a server at localhost\instanceName it doesn't login however if I try machineName\instanceName it does connect without any problems.
Any idea how to get around this? Using other tools I can connect via the localhost.
Thanks
------
:rolleyes: Seems to have been some interference between keyboard and chair.
Right way to do it : "(local)\instanceName"
Another way that works but is pointless! Look up the machine name in registry and use that.
Thanks for being a sounding board :D
Not able to login with different windows user id. How to give different windows useid
Quote:
Not able to login with different windows user id. How to give different windows useid
[QUOTE=SRINIVAS_hp;2896183]Not able to login with different windows user id. How to give different windows useid[/QUOTE
LogOn how i can give different user id info.?
in windows,
i have logged in as say user, "ABC"
But "ABC" user does not have the permission to access sql agent. SO i have to give another user say "def",
Like ${OLEDB}::SQL_Logon "$SQLSERVER" "def" "$SQLPASSWORD".
But i am getting
Loggin on to SQL server RAJATSSRS.qa.englab.local\GRYSQL2008
1
Error initializing OLEDB Connection (Initialize)
0
SQL State: 0x80040e4d - Native: 0 - Message: Login failed for user 'englab\rnair'.
But it works with "sa" user.
Kindly help me in solving this issue
|
Originally Posted by sgiusto (Post 2576551) Try the attached installer, on my pc works fine Regards Stefano Not able to login with different windows user id. How to give different windows useid |