Showing posts with label excel. Show all posts
Showing posts with label excel. Show all posts

Wednesday, March 28, 2012

How to assign values to a variable from a xls sheet?

I've got this query inside a Sql Task against a Excel connection and I'd like to insert that value into a user variable called "Proyecto". How do I such thing?

select Proyecto from [Carga$]

TIA,

I'm so sorry it's solved!!

I promise you that from now on I'll try not be so impatient..

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

Sunday, February 19, 2012

How to access to excel,csv or other formatted file using OLE DB(SQL Native Client)?

Hi~.

First, is it possible to access(read/write) to the excel,csv or my-own-formatted file using OLE DB(SQL Native Client)?

If possible, how?

You can create linked server to the excel spreadsheet as shown in : http://msdn2.microsoft.com/en-us/library/ms190479.aspx

You can use OpenRowset (http://msdn2.microsoft.com/en-us/library/ms190312.aspx)

or OpenQuery( http://msdn2.microsoft.com/en-us/library/ms188427.aspx) to retrieve data.

Providing an example using VB6 / ADO using SQLNCLI

Sub main()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "Provider=SQLNCLI;Data Source=<datasourcename>;Integrated Security=SSPI;"

'Refer to table using linked server
rs.Open "select * from <linkedsrvname>...TestTable", cn

'OpenRowset
Set rs = cn.Execute("select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\TestBook.xls','select * from [TestTable]')")

'OpenQuery
Set rs = cn.Execute("select * from OpenQuery(ExcelShare,'select * from TestTable')")

While Not rs.EOF
For i = 0 To rs.Fields.Count - 1
MsgBox rs.Fields.Item(i)
Next i
rs.MoveNext
Wend

Set rs = Nothing
cn.Close

End Sub