Monday, March 12, 2012
How to add record into SQL Server 2000?
This is my codes:
<%
Dim sql,rs
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\InetPub\wwwroot\daly\fpdb\market.mdb"
sql="INSERT INTO testing (fname,lname)"
sql=sql & " VALUES "
sql=sql & "('Shawn', 'mike')"
objConn.Execute sql
objConn.close
%>
This is my error:
Microsoft JET Database Engine error '80004005'
Operation must use an updateable query.
/VITA_SHOW/test_VITAFormhandler.asp, line 137
Thank you very much!!!
Shawn
scn@.daly.comNot sure why you are getting that error, but a much better design strategy is to create a stored procedure that inserts the record based upon supplied parameters. You should avoid giving applications direct access to data tables.|||your problem might have been either...
1. you dont have the NT permissions for a file-based database or for the folder that contains the file
2. the file might have been exclusive
3. theres another thats still connected that needs to be closed
for more info on issuing this problem, goto MS white papers
http://support.microsoft.com/default.aspx?scid=kb;EN-US;306269|||What type of object is "testing"? I suspect it's not a table, but rather a query.|||You say that you are updating SQL Server 2000, but your code seems to be looking at a Microsoft Access database - the Jet engine and the .mdb extension both refer to Access.
Andy|||objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\InetPub\wwwroot\daly\fpdb\market.mdb"
First off, the database is Access, not SQL, as can be seen from the connection string above.
If you are trying to use access, then the reason for the error is that the IUSR_SERVERNAME (or the authenticated web user, if applicable) does not have write access to the folder and file at c:\InetPub\wwwroot\daly\fpdb\market.mdb. Make sure you set read/write privileges for IUSR_SERVERNAME or Everyone for both the MDB file and the folder in which it resides.
If you are in fact trying to connect to an MSSQL server, you will need to do one of the following:
a) If you have access to the ODBC configuration, set up a DSN, and use the following syntax:
objConn.Open "DSNNAME","USERNAME","PASSWORD"
b) If you don't have access to create a DSN, use the following syntax:
objConn.Open "Driver={SQL Server};UID=USERNAME;password=PASSWORD;database=DA TABASE_NAME;server=SERVER_NAME_OR_IP"
Friday, March 9, 2012
How to add assembly in GAC to SQL 2005?
I want use Microsoft.Office.Interop.Excel to access excel file my clr. But that assembly is installed in GAC. How could I register it in SQL 2005?
thanks!
You can copy the .dll file out of the GAC and try to register it. I have not tried this assembly, but I would expect that there may be some issues using it within SQL Server. You could give it a shot, of course.Cheers,
-Isaac|||
Hi,
I am also trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."
So how do you copy the dll from the GAC and register it? Windows Explorer does not give me a copy option. Assuming the Microsoft.Office.Interop.Excel dll can be copied, would you use the "CREATE ASSESSMBLY" command to register it?
Any help is greatly appreciated.
Rocco
|||Rocco Mastrangelo wrote:
So how do you copy the dll from the GAC and register it? Windows Explorer does not give me a copy option. Assuming the Microsoft.Office.Interop.Excel dll can be copied, would you use the "CREATE ASSESSMBLY" command to register it?
Normally interop assemblies (as well as system assemblies) located in the gac also has a location outside the GAC, but I can not find the Excel.dll. If it had a location outside you would then run CREATE ASSEMBLY against it in that location.
As I can't find it you have to do it as Isaac says. Use command line and cd to where the physical location of that assembly in the GAC: windows\assembly\gac\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c
copy it from there to somewhere on your hard-drive and run CREATE ASSEMBLY against it. However, this assembly has dependencies on other assemblies (use ILDASM to view the dependencies) . So you need to copy those as well out of the GAC, unless they are assemblies that SQL Server loads from the GAC (such as mscorlib, System.Data etc) and put them in the same location as your original assembly. Notice that these dependency assemblies can have dependencies on other assemblies too.
Finally, you have to create the assembly with permission set UNSAFE.
Niels
How to add assembly in GAC to SQL 2005?
I want use Microsoft.Office.Interop.Excel to access excel file my clr. But that assembly is installed in GAC. How could I register it in SQL 2005?
thanks!
You can copy the .dll file out of the GAC and try to register it. I have not tried this assembly, but I would expect that there may be some issues using it within SQL Server. You could give it a shot, of course.Cheers,
-Isaac|||
Hi,
I am also trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."
So how do you copy the dll from the GAC and register it? Windows Explorer does not give me a copy option. Assuming the Microsoft.Office.Interop.Excel dll can be copied, would you use the "CREATE ASSESSMBLY" command to register it?
Any help is greatly appreciated.
Rocco
|||Rocco Mastrangelo wrote:
So how do you copy the dll from the GAC and register it? Windows Explorer does not give me a copy option. Assuming the Microsoft.Office.Interop.Excel dll can be copied, would you use the "CREATE ASSESSMBLY" command to register it?
Normally interop assemblies (as well as system assemblies) located in the gac also has a location outside the GAC, but I can not find the Excel.dll. If it had a location outside you would then run CREATE ASSEMBLY against it in that location.
As I can't find it you have to do it as Isaac says. Use command line and cd to where the physical location of that assembly in the GAC: windows\assembly\gac\Microsoft.Office.Interop.Excel\12.0.0.0__71e9bce111e9429c
copy it from there to somewhere on your hard-drive and run CREATE ASSEMBLY against it. However, this assembly has dependencies on other assemblies (use ILDASM to view the dependencies) . So you need to copy those as well out of the GAC, unless they are assemblies that SQL Server loads from the GAC (such as mscorlib, System.Data etc) and put them in the same location as your original assembly. Notice that these dependency assemblies can have dependencies on other assemblies too.
Finally, you have to create the assembly with permission set UNSAFE.
Niels