Run SQL-Server Query in Excel

Hi guys,
is there a way to run a SQL-query on a SQL-Server
within Excel?
(Is it possible to create any JOIN-querys and DATEDIFF-functions within
Excel-Microsoft Query?)
Thanks much guys,
Mike
Yes, is possible.
1. Start to record Macro.
2. Data > Import External Data > New Database Query .. > ...... > and so
on ..(Build a simple query and rerturn data to Excel)
3. Stop record Macro and Edit . You can change your query and run Macro
whenever you want.
"Maik Richter" <maik.richter2@.gmx.de> wrote in message
news:%23XeAJjvcEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi guys,
> is there a way to run a SQL-query on a SQL-Server
> within Excel?
> (Is it possible to create any JOIN-querys and DATEDIFF-functions within
> Excel-Microsoft Query?)
> Thanks much guys,
> Mike
>

Run SQL-Server Query in Excel

Hi guys,
is there a way to run a SQL-query on a SQL-Server
within Excel?
(Is it possible to create any JOIN-querys and DATEDIFF-functions within
Excel-Microsoft Query?)
Thanks much guys,
MikeYes, is possible.
1. Start to record Macro.
2. Data > Import External Data > New Database Query .. > ...... > and so
on ..(Build a simple query and rerturn data to Excel)
3. Stop record Macro and Edit . You can change your query and run Macro
whenever you want.
"Maik Richter" <maik.richter2@.gmx.de> wrote in message
news:%23XeAJjvcEHA.1000@.TK2MSFTNGP12.phx.gbl...
> Hi guys,
> is there a way to run a SQL-query on a SQL-Server
> within Excel?
> (Is it possible to create any JOIN-querys and DATEDIFF-functions within
> Excel-Microsoft Query?)
> Thanks much guys,
> Mike
>

Run Sql Stored Procedure From Excel

Hi

I want to create a Stored Procedure in SQL with three variables that are declared in an Excel file.

Thus, when the user enters the three variables into

Cells A1, B1, C1

Then hits a button...

The Stored procedure in SQL 2000 will run - and then return the results to a refreshable pivot table in the Excel file.

Any ideas?

Quote:

Originally Posted by flickimp

Hi

I want to create a Stored Procedure in SQL with three variables that are declared in an Excel file.

Thus, when the user enters the three variables into

Cells A1, B1, C1

Then hits a button...

The Stored procedure in SQL 2000 will run - and then return the results to a refreshable pivot table in the Excel file.

Any ideas?

Write an Excel Macro for this.
Connect to SQL Server from Excel and call a procedure as required.|||New to macros.

Any tips?|||

Quote:

Originally Posted by flickimp

New to macros.

Any tips?

You can find lots of examples by little web searching.
Kindly POST what you tried so that we could help in case of any problem!!|||Hi

Heres the Stored Procedure

Say in Excel -- cell A1 = @.Type and cell B1 = @.LSS

Create Proc sp_Flickimp_test
@.TypeVarchar(3),
@.LSSVarchar(3)
as
SelectProvider_Code,
Commissioner_Code,
LSS_Flag,
Financial_Year,
Financial_Month,
Specialty_Code,
Description_Contract,
Activity_Type,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_Grouped,
HRG_Code,
Specialised_Services_Code,
Local_AdmitUnit_Code,
Total_Spells, Tariff_Initial_Amount, Tariff_ShortStay_Spells,
Tariff_ShortStay_Adj, Tariff_SpecServ_Adj, Tariff_Final_Amount,
Tariff_LS_Spells, Tariff_LS_Days, Tariff_LS_Rate, Tariff_LS_Payment,
Tariff_Total_Payment, Local_Total_Payment, Local_AdmitUnit_Adj

Fromtbl_BaseLine_UHL_Spells_Trend_0708 a

LeftOuter Join dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code

LeftOuter Join dbo.tbl_Refs_IP_SpecServ_0708 c
on a.Specialised_Services_Code = c.SpecServ

Where(Financial_year = '2006/07' and Financial_Month between '7' and '12')
or(Financial_year = '2007/08' and Financial_Month between '1' and '6')
andCommissioner_Code = @.Type
andLSS_Flag = @.LSS|||As a full member now, you should know that we expect your code to be posted in [CODE] tags (See How to Ask a Question).

This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use the tags in future.

MODERATOR|||

Quote:

Originally Posted by flickimp

Hi

Heres the Stored Procedure

Say in Excel -- cell A1 = @.Type and cell B1 = @.LSS

Create Proc sp_Flickimp_test
@.TypeVarchar(3),
@.LSSVarchar(3)
as
SelectProvider_Code,
Commissioner_Code,
LSS_Flag,
Financial_Year,
Financial_Month,
Specialty_Code,
Description_Contract,
Activity_Type,
CaseWhen Activity_Type in ('Emergency','Non-elective') Then 'Non-Elective'
Else Activity_Type End as Activity_Type_Grouped,
HRG_Code,
Specialised_Services_Code,
Local_AdmitUnit_Code,
Total_Spells, Tariff_Initial_Amount, Tariff_ShortStay_Spells,
Tariff_ShortStay_Adj, Tariff_SpecServ_Adj, Tariff_Final_Amount,
Tariff_LS_Spells, Tariff_LS_Days, Tariff_LS_Rate, Tariff_LS_Payment,
Tariff_Total_Payment, Local_Total_Payment, Local_AdmitUnit_Adj

Fromtbl_BaseLine_UHL_Spells_Trend_0708 a

LeftOuter Join dbo.tbl_Refs_Specialty_UHL b
on a.Specialty_Code = b.Code

LeftOuter Join dbo.tbl_Refs_IP_SpecServ_0708 c
on a.Specialised_Services_Code = c.SpecServ

Where(Financial_year = '2006/07' and Financial_Month between '7' and '12')
or(Financial_year = '2007/08' and Financial_Month between '1' and '6')
andCommissioner_Code = @.Type
andLSS_Flag = @.LSS

Alright!! Did you try calling this from a Macro.
You need to connect to SQL server from Excel.|||Hi

Sorry for not using the tags.

The Stored procedure was created in SQL Query Analyzer 2000.

I tried to use MS Query to pull the SP back with the variables, but no joy.

What steps must I take to allow the parameters from excel cells to be picked up by the stored procedure so that it returns the results back to excel?

Run Excel macro from SQL Server Agent

Hi,
I have a DTS package that (among other things) runs an Excel macro.
The macro pulls in data from the server, formats it, then saves the
workbook to a network drive. The package runs fine when executed
locally but will not run as a scheduled job since Excel is not
installed on the server. I need to be able to schedule this job
somehow, and as you can imagine, the DBA and network admins are not
about to install Office on the server. Is there a way around this?Without OLE automation (i.e. *.dll) you would not be able to run the macro.
I don't think there is a way without installing the component on the server.
--
-oj
<jono@.mailinator.com> wrote in message
news:1123803060.725514.253790@.z14g2000cwz.googlegroups.com...
> Hi,
> I have a DTS package that (among other things) runs an Excel macro.
> The macro pulls in data from the server, formats it, then saves the
> workbook to a network drive. The package runs fine when executed
> locally but will not run as a scheduled job since Excel is not
> installed on the server. I need to be able to schedule this job
> somehow, and as you can imagine, the DBA and network admins are not
> about to install Office on the server. Is there a way around this?
>

Run Excel macro from SQL Server Agent

Hi,
I have a DTS package that (among other things) runs an Excel macro.
The macro pulls in data from the server, formats it, then saves the
workbook to a network drive. The package runs fine when executed
locally but will not run as a scheduled job since Excel is not
installed on the server. I need to be able to schedule this job
somehow, and as you can imagine, the DBA and network admins are not
about to install Office on the server. Is there a way around this?
Without OLE automation (i.e. *.dll) you would not be able to run the macro.
I don't think there is a way without installing the component on the server.
-oj
<jono@.mailinator.com> wrote in message
news:1123803060.725514.253790@.z14g2000cwz.googlegr oups.com...
> Hi,
> I have a DTS package that (among other things) runs an Excel macro.
> The macro pulls in data from the server, formats it, then saves the
> workbook to a network drive. The package runs fine when executed
> locally but will not run as a scheduled job since Excel is not
> installed on the server. I need to be able to schedule this job
> somehow, and as you can imagine, the DBA and network admins are not
> about to install Office on the server. Is there a way around this?
>

Run Excel macro from SQL Server Agent

Hi,
I have a DTS package that (among other things) runs an Excel macro.
The macro pulls in data from the server, formats it, then saves the
workbook to a network drive. The package runs fine when executed
locally but will not run as a scheduled job since Excel is not
installed on the server. I need to be able to schedule this job
somehow, and as you can imagine, the DBA and network admins are not
about to install Office on the server. Is there a way around this?Without OLE automation (i.e. *.dll) you would not be able to run the macro.
I don't think there is a way without installing the component on the server.
-oj
<jono@.mailinator.com> wrote in message
news:1123803060.725514.253790@.z14g2000cwz.googlegroups.com...
> Hi,
> I have a DTS package that (among other things) runs an Excel macro.
> The macro pulls in data from the server, formats it, then saves the
> workbook to a network drive. The package runs fine when executed
> locally but will not run as a scheduled job since Excel is not
> installed on the server. I need to be able to schedule this job
> somehow, and as you can imagine, the DBA and network admins are not
> about to install Office on the server. Is there a way around this?
>

run a DTS

Hi,

I am trying to run a DTS that export the table to a different server as an excel file.

In the DTS I specified UNC path for the file to be exported.

Now, I login the SQL Server with a network user MyUser and run the package manually and I see the excel file is created in the remote server. I want to do the same thing with a SQL Agent Job task. I created a stored procedure and put my DTSRun all in it as follows;

dtsrun /S MyServer /U MyUSer /P MyPass /N MyDTS . I also create the job and make the owner and “RunAs” MyUser and run the task I get the following error. Apparently since MyUser is a network user, it could not login to SQL Server as I defined in my DTSRun. Is there any way I can get this DTS run under that user without changing SQL Agent user which is running under a system user.

Error:

DTSRun:Loading...

Error:-2147217843 (80040E4D); Provider Error:18456 (4818)

Error string:Login failed for user ‘MyUSer’.

Error source:Microsoft OLE DB Provider for SQL Server

Help file:

Help context:0

(null)

First make a login for your MyUser windows account and assign it to the sysadmin role - make sure that MyUser is a local admin on the server that SQL is running on. Then create a Proxy account for the SQL Server (right click on the agent and select properties, then go the the Job System) use MyUser as the proxy account. Look at SQL Server Agent Properties (Job System Tab) on BOL for more information.

Brad

run a DTS

Hi,

I am trying to run a DTS that export the table to a different server as an excel file.

In the DTS I specified UNC path for the file to be exported.

Now, I login the SQL Server with a network user MyUser and run the package manually and I see the excel file is created in the remote server. I want to do the same thing with a SQL Agent Job task. I created a stored procedure and put my DTSRun all in it as follows;

dtsrun /S MyServer /U MyUSer /P MyPass /N MyDTS . I also create the job and make the owner and “RunAs” MyUser and run the task I get the following error. Apparently since MyUser is a network user, it could not login to SQL Server as I defined in my DTSRun. Is there any way I can get this DTS run under that user without changing SQL Agent user which is running under a system user.

Error:

DTSRun: Loading...

Error: -2147217843 (80040E4D); Provider Error: 18456 (4818)

Error string: Login failed for user ‘MyUSer’.

Error source: Microsoft OLE DB Provider for SQL Server

Help file:

Help context: 0

(null)

No but you can clone your account to run the Agent with the proxy account, try the thread below for details. If it did not run start over it works. Hope this helps.

http://forums.asp.net/thread/1358665.aspx

run a DTS

Hi,

I am trying to run a DTS that export the table to a different server as an excel file.

In the DTS I specified UNC path for the file to be exported.

Now, I login the SQL Server with a network user MyUser and run the package manually and I see the excel file is created in the remote server. I want to do the same thing with a SQL Agent Job task. I created a stored procedure and put my DTSRun all in it as follows;

dtsrun /S MyServer /U MyUSer /P MyPass /N MyDTS . I also create the job and make the owner and “RunAs” MyUser and run the task I get the following error. Apparently since MyUser is a network user, it could not login to SQL Server as I defined in my DTSRun. Is there any way I can get this DTS run under that user without changing SQL Agent user which is running under a system user.

Error:

DTSRun:Loading...

Error:-2147217843 (80040E4D); Provider Error:18456 (4818)

Error string:Login failed for user ‘MyUSer’.

Error source:Microsoft OLE DB Provider for SQL Server

Help file:

Help context:0

(null)

First make a login for your MyUser windows account and assign it to the sysadmin role - make sure that MyUser is a local admin on the server that SQL is running on. Then create a Proxy account for the SQL Server (right click on the agent and select properties, then go the the Job System) use MyUser as the proxy account. Look at SQL Server Agent Properties (Job System Tab) on BOL for more information.

Brad