Excel to SQL

[swiny]

Hi is there a way that a user can export excel file to sql monthly without using ssis/dts?
(This could be manual... doesn't have to be automated.)

User doesn't have acces to SQL Management studio.

Thanks.

 

[ousoonerjoe]

One way is to create a View and a data connection that can be used to generate the report via Excel. Just be mindful of security and permissions when using this approach.

--------------------------------------------------
Stubbornness is a virtue -- if you are right. --Chuck Noll
--------------------------------------------------

 

[drlex]

Alternatively, have a job running periodically on the SQL server that watches a folder for import files.

soi là, soi carré

 

[jrbarnett]

Use VBA in excel to open an ado connection to the server, then VBA to call a stored proc or insert statements to load the data.
I do this all the time at work.

John

 

[swiny]

Thanks for all the reply.

I have never used VBA in excel prior to this.
Do you have a sample on how to open ado conn and calling a proc?
Thanks.

 

[jrbarnett]

Rather off topic for a SQL Server forum - you are more likely to get relevant help in the VBA Programming forum, but here goes:

Public Function RunStoredProc

    Const DB_CONNECT As String = "Provider=SQLOLEDB;Server=(local);Database=xxxxx;User ID=yyyyy;Password=zzzzz;Trusted_Connection=False;App=MyExcelApp"

    Dim cn As ADODB.Connection
    Dim Cmd As ADODB.Command
    Dim rst As ADODB.Recordset

    With cn
        .ConnectionString = DB_CONNECT
        .CursorLocation = adUseClient
        .Open
    End With

    Set Cmd = New ADODB.Command
    With Cmd
        .CommandType = adCmdStoredProc
        .CommandText = "YourStoredProcNameHere"
        '.Parameters("@Param1").Value = ActiveWorkbook.Name
        '.Parameters("@projectid").Value = "xxxx"
    End With

   Set rst = cmd.Execute
   ' Do what you want with the results here

   cn.Close ' Disconnect
End Function

Before you can use this, you will need to:
1. Add a reference to your code for the Microsoft ActiveX Data Objects library (newer the better)

2. Change the DB_CONNECT line with connection information for your database server and database.

3. Change the .CommandText line to the name of your stored procedure (be sure to prefix with the owner name if necessary), plus appropriate parameters.

Following the .Execute line, you will need to make some use of the results - display them in the worksheet, use them in formulae etc.

If you run this while a SQL Profiler trace running it will show as being MyExcelApp under management studio active connections (the App= section of the connection string).

John