Please help ...

Hello,

Could somebody please tell me , how to export stored procedure data to EXCEL using DTS ?

Please advice me!Your stored procedure is probably returning a a set of records?

In that case, just take the query in your storder proc and create a DTS job to run that query into an excel file. If you need to then be able to kick off that job at will, you can use the DTSRUN utility from a stored procedure to start it.

If you need to send variables to the DTS job so that it can dynamically select only certain records, you can pass parameters to a stored procedure and then use techniques discussed here:

http://www.databasejournal.com/features/mssql/article.php/1461481
http://www.databasejournal.com/features/mssql/article.php/1462571

You were a little sketchy on what you need to do.

Please give me sample coding for "exporting table to excel file".

Thanks !
You could go down the OA_Create route - I believe this was originally cribbed
from www.swynk.org.
CREATE PROCEDURE ExportToExcel (
@.server sysname = null,
@.uname sysname = null,
@.pwd sysname = null,
@.QueryText varchar(200) = null,
@.filename varchar(200) = 'C:\NorthwindCategories.xls'
)
AS
DECLARE @.SQLServer int,
@.QueryResults int,
@.CurrentResultSet int,
@.object int,
@.WorkBooks int,
@.WorkBook int,
@.Range int,
@.hr int,
@.Columns int,
@.Rows int,
@.indColumn int,
@.indRow int,
@.off_Column int,
@.off_Row int,
@.code_str varchar(100),
@.result_str varchar(255)
IF @.QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END
-- Sets the server to the local server
IF @.server IS NULL SELECT @.server = @.@.servername
-- Sets the username to the current user name
IF @.uname IS NULL SELECT @.uname = SYSTEM_USER
SET NOCOUNT ON
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.SQLServer OUT
IF @.hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END
-- Connect to the SQL Server
IF @.pwd IS NULL
BEGIN
EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname
IF @.hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
ELSE
BEGIN
EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname, @.pwd
IF @.hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
PRINT @.QueryText
SELECT @.result_str = 'ExecuteWithResults("' + @.QueryText + '")'
EXEC @.hr = sp_OAMethod @.SQLServer, @.result_str, @.QueryResults OUT
IF @.hr <> 0
BEGIN
PRINT 'error with method ExecuteWithResults'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'CurrentResultSet', @.CurrentResultSet
OUT
IF @.hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'Columns', @.Columns OUT
IF @.hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'Rows', @.Rows OUT
IF @.hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
END
EXEC @.hr = sp_OACreate 'Excel.Application', @.object OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.object, 'WorkBooks', @.WorkBooks OUT
IF @.hr <> 0
BEGIN
PRINT 'error create WorkBooks'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.WorkBooks, 'Add', @.WorkBook OUT
IF @.hr <> 0
BEGIN
PRINT 'error with method Add'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.object, 'Range("A1")', @.Range OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
SELECT @.indRow = 1
SELECT @.off_Row = 0
SELECT @.off_Column = 1
WHILE (@.indRow <= @.Rows)
BEGIN
SELECT @.indColumn = 1
WHILE (@.indColumn <= @.Columns)
BEGIN
EXEC @.hr = sp_OAMethod @.QueryResults, 'GetColumnString', @.result_str OUT,
@.indRow, @.indColumn
IF @.hr <> 0
BEGIN
PRINT 'error get GetColumnString'
RETURN
END
EXEC @.hr = sp_OASetProperty @.Range, 'Value', @.result_str
IF @.hr <> 0
BEGIN
PRINT 'error set Value'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.Range, 'Offset', @.Range OUT, @.off_Row,
@.off_Column
IF @.hr <> 0
BEGIN
PRINT 'error get Offset'
RETURN
END
SELECT @.indColumn = @.indColumn + 1
END
SELECT @.indRow = @.indRow + 1
SELECT @.code_str = 'Range("A' + LTRIM(str(@.indRow)) + '")'
EXEC @.hr = sp_OAGetProperty @.object, @.code_str, @.Range OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
END
SELECT @.result_str = 'exec master..xp_cmdshell ''del ' + @.filename + ''',
no_output'
EXEC(@.result_str)
SELECT @.result_str = 'SaveAs("' + @.filename + '")'
EXEC @.hr = sp_OAMethod @.WorkBook, @.result_str
IF @.hr <> 0
BEGIN
PRINT 'error with method SaveAs'
RETURN
END
EXEC @.hr = sp_OAMethod @.WorkBook, 'Close'
IF @.hr <> 0
BEGIN
PRINT 'error with method Close'
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT 'error destroy Excel.Application'
RETURN
END
EXEC @.hr = sp_OADestroy @.SQLServer
IF @.hr <> 0
BEGIN
PRINT 'error destroy SQLDMO.SQLServer'
RETURN
END
GO
"SOHO" wrote:

>
> --
> Thanks !
>
>
|||Not an actual xls file, but excell will treat it like one and it will be
associated with excell on most users machines
Read KB#890775 first.
****************************
declare @.cmd varchar(255)
select @.cmd =
'bcp "pubs.dbo.authors" out "c:\authors.csv" -c -t"," -r\n -U"sa" -P""'
exec master.dbo.xp_cmdshell @.cmd
****************************
kcwms
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:%23ZWTPpkdFHA.1040@.TK2MSFTNGP10.phx.gbl...
>
> --
> Thanks !
>
>
|||Thanks for your reply.
Thanks !
"BarryC" <BarryC@.discussions.microsoft.com> glsD:4A433E6C-3276-466E-8B52-5EC3DD615DA2@.microsoft.com...[vbcol=seagreen]
> You could go down the OA_Create route - I believe this was originally
> cribbed
> from www.swynk.org.
> CREATE PROCEDURE ExportToExcel (
> @.server sysname = null,
> @.uname sysname = null,
> @.pwd sysname = null,
> @.QueryText varchar(200) = null,
> @.filename varchar(200) = 'C:\NorthwindCategories.xls'
> )
> AS
> DECLARE @.SQLServer int,
> @.QueryResults int,
> @.CurrentResultSet int,
> @.object int,
> @.WorkBooks int,
> @.WorkBook int,
> @.Range int,
> @.hr int,
> @.Columns int,
> @.Rows int,
> @.indColumn int,
> @.indRow int,
> @.off_Column int,
> @.off_Row int,
> @.code_str varchar(100),
> @.result_str varchar(255)
> IF @.QueryText IS NULL
> BEGIN
> PRINT 'Set the query string'
> RETURN
> END
> -- Sets the server to the local server
> IF @.server IS NULL SELECT @.server = @.@.servername
> -- Sets the username to the current user name
> IF @.uname IS NULL SELECT @.uname = SYSTEM_USER
> SET NOCOUNT ON
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.SQLServer OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create SQLDMO.SQLServer'
> RETURN
> END
> -- Connect to the SQL Server
> IF @.pwd IS NULL
> BEGIN
> EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname
> IF @.hr <> 0
> BEGIN
> PRINT 'error Connect'
> RETURN
> END
> END
> ELSE
> BEGIN
> EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname,
> @.pwd
> IF @.hr <> 0
> BEGIN
> PRINT 'error Connect'
> RETURN
> END
> END
> PRINT @.QueryText
> SELECT @.result_str = 'ExecuteWithResults("' + @.QueryText + '")'
> EXEC @.hr = sp_OAMethod @.SQLServer, @.result_str, @.QueryResults OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method ExecuteWithResults'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'CurrentResultSet',
> @.CurrentResultSet
> OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get CurrentResultSet'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'Columns', @.Columns OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Columns'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'Rows', @.Rows OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Rows'
> RETURN
> END
> EXEC @.hr = sp_OACreate 'Excel.Application', @.object OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Excel.Application'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.object, 'WorkBooks', @.WorkBooks OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create WorkBooks'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.WorkBooks, 'Add', @.WorkBook OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method Add'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.object, 'Range("A1")', @.Range OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Range'
> RETURN
> END
> SELECT @.indRow = 1
> SELECT @.off_Row = 0
> SELECT @.off_Column = 1
> WHILE (@.indRow <= @.Rows)
> BEGIN
> SELECT @.indColumn = 1
> WHILE (@.indColumn <= @.Columns)
> BEGIN
> EXEC @.hr = sp_OAMethod @.QueryResults, 'GetColumnString', @.result_str OUT,
> @.indRow, @.indColumn
> IF @.hr <> 0
> BEGIN
> PRINT 'error get GetColumnString'
> RETURN
> END
> EXEC @.hr = sp_OASetProperty @.Range, 'Value', @.result_str
> IF @.hr <> 0
> BEGIN
> PRINT 'error set Value'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.Range, 'Offset', @.Range OUT, @.off_Row,
> @.off_Column
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Offset'
> RETURN
> END
> SELECT @.indColumn = @.indColumn + 1
> END
> SELECT @.indRow = @.indRow + 1
> SELECT @.code_str = 'Range("A' + LTRIM(str(@.indRow)) + '")'
> EXEC @.hr = sp_OAGetProperty @.object, @.code_str, @.Range OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Range'
> RETURN
> END
> END
> SELECT @.result_str = 'exec master..xp_cmdshell ''del ' + @.filename + ''',
> no_output'
> EXEC(@.result_str)
> SELECT @.result_str = 'SaveAs("' + @.filename + '")'
> EXEC @.hr = sp_OAMethod @.WorkBook, @.result_str
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method SaveAs'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.WorkBook, 'Close'
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method Close'
> RETURN
> END
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> PRINT 'error destroy Excel.Application'
> RETURN
> END
> EXEC @.hr = sp_OADestroy @.SQLServer
> IF @.hr <> 0
> BEGIN
> PRINT 'error destroy SQLDMO.SQLServer'
> RETURN
> END
> GO
>
> "SOHO" wrote:
|||Thanks for your reply.
Thanks !
"kcwms" <noneOfYoBusiness@.me.net> glsD:eBCSdQndFHA.3184@.TK2MSFTNGP15.phx.g bl...
> Not an actual xls file, but excell will treat it like one and it will be
> associated with excell on most users machines
> Read KB#890775 first.
> ****************************
> declare @.cmd varchar(255)
> select @.cmd =
> 'bcp "pubs.dbo.authors" out "c:\authors.csv" -c -t"," -r\n -U"sa" -P""'
> exec master.dbo.xp_cmdshell @.cmd
> ****************************
> kcwms
>
> "SOHO" <hkwin2000@.hotmail.com> wrote in message
> news:%23ZWTPpkdFHA.1040@.TK2MSFTNGP10.phx.gbl...
>

Please give me sample coding for "exporting table to excel file".

--
Thanks !You could go down the OA_Create route - I believe this was originally cribbed
from www.swynk.org.
CREATE PROCEDURE ExportToExcel (
@.server sysname = null,
@.uname sysname = null,
@.pwd sysname = null,
@.QueryText varchar(200) = null,
@.filename varchar(200) = 'C:\NorthwindCategories.xls'
)
AS
DECLARE @.SQLServer int,
@.QueryResults int,
@.CurrentResultSet int,
@.object int,
@.WorkBooks int,
@.WorkBook int,
@.Range int,
@.hr int,
@.Columns int,
@.Rows int,
@.indColumn int,
@.indRow int,
@.off_Column int,
@.off_Row int,
@.code_str varchar(100),
@.result_str varchar(255)
IF @.QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END
-- Sets the server to the local server
IF @.server IS NULL SELECT @.server = @.@.servername
-- Sets the username to the current user name
IF @.uname IS NULL SELECT @.uname = SYSTEM_USER
SET NOCOUNT ON
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.SQLServer OUT
IF @.hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END
-- Connect to the SQL Server
IF @.pwd IS NULL
BEGIN
EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname
IF @.hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
ELSE
BEGIN
EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname, @.pwd
IF @.hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
PRINT @.QueryText
SELECT @.result_str = 'ExecuteWithResults("' + @.QueryText + '")'
EXEC @.hr = sp_OAMethod @.SQLServer, @.result_str, @.QueryResults OUT
IF @.hr <> 0
BEGIN
PRINT 'error with method ExecuteWithResults'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'CurrentResultSet', @.CurrentResultSet
OUT
IF @.hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'Columns', @.Columns OUT
IF @.hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'Rows', @.Rows OUT
IF @.hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
END
EXEC @.hr = sp_OACreate 'Excel.Application', @.object OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.object, 'WorkBooks', @.WorkBooks OUT
IF @.hr <> 0
BEGIN
PRINT 'error create WorkBooks'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.WorkBooks, 'Add', @.WorkBook OUT
IF @.hr <> 0
BEGIN
PRINT 'error with method Add'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.object, 'Range("A1")', @.Range OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
SELECT @.indRow = 1
SELECT @.off_Row = 0
SELECT @.off_Column = 1
WHILE (@.indRow <= @.Rows)
BEGIN
SELECT @.indColumn = 1
WHILE (@.indColumn <= @.Columns)
BEGIN
EXEC @.hr = sp_OAMethod @.QueryResults, 'GetColumnString', @.result_str OUT,
@.indRow, @.indColumn
IF @.hr <> 0
BEGIN
PRINT 'error get GetColumnString'
RETURN
END
EXEC @.hr = sp_OASetProperty @.Range, 'Value', @.result_str
IF @.hr <> 0
BEGIN
PRINT 'error set Value'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.Range, 'Offset', @.Range OUT, @.off_Row,
@.off_Column
IF @.hr <> 0
BEGIN
PRINT 'error get Offset'
RETURN
END
SELECT @.indColumn = @.indColumn + 1
END
SELECT @.indRow = @.indRow + 1
SELECT @.code_str = 'Range("A' + LTRIM(str(@.indRow)) + '")'
EXEC @.hr = sp_OAGetProperty @.object, @.code_str, @.Range OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
END
SELECT @.result_str = 'exec master..xp_cmdshell ''del ' + @.filename + ''',
no_output'
EXEC(@.result_str)
SELECT @.result_str = 'SaveAs("' + @.filename + '")'
EXEC @.hr = sp_OAMethod @.WorkBook, @.result_str
IF @.hr <> 0
BEGIN
PRINT 'error with method SaveAs'
RETURN
END
EXEC @.hr = sp_OAMethod @.WorkBook, 'Close'
IF @.hr <> 0
BEGIN
PRINT 'error with method Close'
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT 'error destroy Excel.Application'
RETURN
END
EXEC @.hr = sp_OADestroy @.SQLServer
IF @.hr <> 0
BEGIN
PRINT 'error destroy SQLDMO.SQLServer'
RETURN
END
GO
"SOHO" wrote:
>
> --
> Thanks !
>
>|||Not an actual xls file, but excell will treat it like one and it will be
associated with excell on most users machines
Read KB#890775 first.
****************************
declare @.cmd varchar(255)
select @.cmd ='bcp "pubs.dbo.authors" out "c:\authors.csv" -c -t"," -r\n -U"sa" -P""'
exec master.dbo.xp_cmdshell @.cmd
****************************
kcwms
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:%23ZWTPpkdFHA.1040@.TK2MSFTNGP10.phx.gbl...
>
> --
> Thanks !
>
>|||Thanks for your reply.
--
Thanks !
"BarryC" <BarryC@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D:4A433E6C-3276-466E-8B52-5EC3DD615DA2@.microsoft.com...
> You could go down the OA_Create route - I believe this was originally
> cribbed
> from www.swynk.org.
> CREATE PROCEDURE ExportToExcel (
> @.server sysname = null,
> @.uname sysname = null,
> @.pwd sysname = null,
> @.QueryText varchar(200) = null,
> @.filename varchar(200) = 'C:\NorthwindCategories.xls'
> )
> AS
> DECLARE @.SQLServer int,
> @.QueryResults int,
> @.CurrentResultSet int,
> @.object int,
> @.WorkBooks int,
> @.WorkBook int,
> @.Range int,
> @.hr int,
> @.Columns int,
> @.Rows int,
> @.indColumn int,
> @.indRow int,
> @.off_Column int,
> @.off_Row int,
> @.code_str varchar(100),
> @.result_str varchar(255)
> IF @.QueryText IS NULL
> BEGIN
> PRINT 'Set the query string'
> RETURN
> END
> -- Sets the server to the local server
> IF @.server IS NULL SELECT @.server = @.@.servername
> -- Sets the username to the current user name
> IF @.uname IS NULL SELECT @.uname = SYSTEM_USER
> SET NOCOUNT ON
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.SQLServer OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create SQLDMO.SQLServer'
> RETURN
> END
> -- Connect to the SQL Server
> IF @.pwd IS NULL
> BEGIN
> EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname
> IF @.hr <> 0
> BEGIN
> PRINT 'error Connect'
> RETURN
> END
> END
> ELSE
> BEGIN
> EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname,
> @.pwd
> IF @.hr <> 0
> BEGIN
> PRINT 'error Connect'
> RETURN
> END
> END
> PRINT @.QueryText
> SELECT @.result_str = 'ExecuteWithResults("' + @.QueryText + '")'
> EXEC @.hr = sp_OAMethod @.SQLServer, @.result_str, @.QueryResults OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method ExecuteWithResults'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'CurrentResultSet',
> @.CurrentResultSet
> OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get CurrentResultSet'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'Columns', @.Columns OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Columns'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'Rows', @.Rows OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Rows'
> RETURN
> END
> EXEC @.hr = sp_OACreate 'Excel.Application', @.object OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Excel.Application'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.object, 'WorkBooks', @.WorkBooks OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create WorkBooks'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.WorkBooks, 'Add', @.WorkBook OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method Add'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.object, 'Range("A1")', @.Range OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Range'
> RETURN
> END
> SELECT @.indRow = 1
> SELECT @.off_Row = 0
> SELECT @.off_Column = 1
> WHILE (@.indRow <= @.Rows)
> BEGIN
> SELECT @.indColumn = 1
> WHILE (@.indColumn <= @.Columns)
> BEGIN
> EXEC @.hr = sp_OAMethod @.QueryResults, 'GetColumnString', @.result_str OUT,
> @.indRow, @.indColumn
> IF @.hr <> 0
> BEGIN
> PRINT 'error get GetColumnString'
> RETURN
> END
> EXEC @.hr = sp_OASetProperty @.Range, 'Value', @.result_str
> IF @.hr <> 0
> BEGIN
> PRINT 'error set Value'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.Range, 'Offset', @.Range OUT, @.off_Row,
> @.off_Column
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Offset'
> RETURN
> END
> SELECT @.indColumn = @.indColumn + 1
> END
> SELECT @.indRow = @.indRow + 1
> SELECT @.code_str = 'Range("A' + LTRIM(str(@.indRow)) + '")'
> EXEC @.hr = sp_OAGetProperty @.object, @.code_str, @.Range OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Range'
> RETURN
> END
> END
> SELECT @.result_str = 'exec master..xp_cmdshell ''del ' + @.filename + ''',
> no_output'
> EXEC(@.result_str)
> SELECT @.result_str = 'SaveAs("' + @.filename + '")'
> EXEC @.hr = sp_OAMethod @.WorkBook, @.result_str
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method SaveAs'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.WorkBook, 'Close'
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method Close'
> RETURN
> END
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> PRINT 'error destroy Excel.Application'
> RETURN
> END
> EXEC @.hr = sp_OADestroy @.SQLServer
> IF @.hr <> 0
> BEGIN
> PRINT 'error destroy SQLDMO.SQLServer'
> RETURN
> END
> GO
>
> "SOHO" wrote:
>>
>> --
>> Thanks !
>>
>>|||Thanks for your reply.
--
Thanks !
"kcwms" <noneOfYoBusiness@.me.net> ¼¶¼g©ó¶l¥ó·s»D:eBCSdQndFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Not an actual xls file, but excell will treat it like one and it will be
> associated with excell on most users machines
> Read KB#890775 first.
> ****************************
> declare @.cmd varchar(255)
> select @.cmd => 'bcp "pubs.dbo.authors" out "c:\authors.csv" -c -t"," -r\n -U"sa" -P""'
> exec master.dbo.xp_cmdshell @.cmd
> ****************************
> kcwms
>
> "SOHO" <hkwin2000@.hotmail.com> wrote in message
> news:%23ZWTPpkdFHA.1040@.TK2MSFTNGP10.phx.gbl...
>>
>> --
>> Thanks !
>>
>

Please give me sample coding for "exporting table to excel file".

Please give me sample coding for "exporting table to excel file".You could go down the OA_Create route - I believe this was originally cribbe
d
from www.swynk.org.
CREATE PROCEDURE ExportToExcel (
@.server sysname = null,
@.uname sysname = null,
@.pwd sysname = null,
@.QueryText varchar(200) = null,
@.filename varchar(200) = 'C:\NorthwindCategories.xls'
)
AS
DECLARE @.SQLServer int,
@.QueryResults int,
@.CurrentResultSet int,
@.object int,
@.WorkBooks int,
@.WorkBook int,
@.Range int,
@.hr int,
@.Columns int,
@.Rows int,
@.indColumn int,
@.indRow int,
@.off_Column int,
@.off_Row int,
@.code_str varchar(100),
@.result_str varchar(255)
IF @.QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END
-- Sets the server to the local server
IF @.server IS NULL SELECT @.server = @.@.servername
-- Sets the username to the current user name
IF @.uname IS NULL SELECT @.uname = SYSTEM_USER
SET NOCOUNT ON
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.SQLServer OUT
IF @.hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END
-- Connect to the SQL Server
IF @.pwd IS NULL
BEGIN
EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname
IF @.hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
ELSE
BEGIN
EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname, @.pwd
IF @.hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
PRINT @.QueryText
SELECT @.result_str = 'ExecuteWithResults("' + @.QueryText + '")'
EXEC @.hr = sp_OAMethod @.SQLServer, @.result_str, @.QueryResults OUT
IF @.hr <> 0
BEGIN
PRINT 'error with method ExecuteWithResults'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'CurrentResultSet', @.CurrentResultSet
OUT
IF @.hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'Columns', @.Columns OUT
IF @.hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
END
EXEC @.hr = sp_OAMethod @.QueryResults, 'Rows', @.Rows OUT
IF @.hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
END
EXEC @.hr = sp_OACreate 'Excel.Application', @.object OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.object, 'WorkBooks', @.WorkBooks OUT
IF @.hr <> 0
BEGIN
PRINT 'error create WorkBooks'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.WorkBooks, 'Add', @.WorkBook OUT
IF @.hr <> 0
BEGIN
PRINT 'error with method Add'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.object, 'Range("A1")', @.Range OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
SELECT @.indRow = 1
SELECT @.off_Row = 0
SELECT @.off_Column = 1
WHILE (@.indRow <= @.Rows)
BEGIN
SELECT @.indColumn = 1
WHILE (@.indColumn <= @.Columns)
BEGIN
EXEC @.hr = sp_OAMethod @.QueryResults, 'GetColumnString', @.result_str OUT,
@.indRow, @.indColumn
IF @.hr <> 0
BEGIN
PRINT 'error get GetColumnString'
RETURN
END
EXEC @.hr = sp_OASetProperty @.Range, 'Value', @.result_str
IF @.hr <> 0
BEGIN
PRINT 'error set Value'
RETURN
END
EXEC @.hr = sp_OAGetProperty @.Range, 'Offset', @.Range OUT, @.off_Row,
@.off_Column
IF @.hr <> 0
BEGIN
PRINT 'error get Offset'
RETURN
END
SELECT @.indColumn = @.indColumn + 1
END
SELECT @.indRow = @.indRow + 1
SELECT @.code_str = 'Range("A' + LTRIM(str(@.indRow)) + '")'
EXEC @.hr = sp_OAGetProperty @.object, @.code_str, @.Range OUT
IF @.hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
END
SELECT @.result_str = 'exec master..xp_cmdshell ''del ' + @.filename + ''',
no_output'
EXEC(@.result_str)
SELECT @.result_str = 'SaveAs("' + @.filename + '")'
EXEC @.hr = sp_OAMethod @.WorkBook, @.result_str
IF @.hr <> 0
BEGIN
PRINT 'error with method SaveAs'
RETURN
END
EXEC @.hr = sp_OAMethod @.WorkBook, 'Close'
IF @.hr <> 0
BEGIN
PRINT 'error with method Close'
RETURN
END
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT 'error destroy Excel.Application'
RETURN
END
EXEC @.hr = sp_OADestroy @.SQLServer
IF @.hr <> 0
BEGIN
PRINT 'error destroy SQLDMO.SQLServer'
RETURN
END
GO
"SOHO" wrote:

>
> --
> Thanks !
>
>|||Not an actual xls file, but excell will treat it like one and it will be
associated with excell on most users machines
Read KB#890775 first.
****************************
declare @.cmd varchar(255)
select @.cmd =
'bcp "pubs.dbo.authors" out "c:\authors.csv" -c -t"," -r\n -U"sa" -P""'
exec master.dbo.xp_cmdshell @.cmd
****************************
kcwms
"SOHO" <hkwin2000@.hotmail.com> wrote in message
news:%23ZWTPpkdFHA.1040@.TK2MSFTNGP10.phx.gbl...
>
> --
> Thanks !
>
>|||Thanks for your reply.
--
Thanks !
"BarryC" <BarryC@.discussions.microsoft.com> glsD:4A433E6C-3276-466E-8B52-5EC3DD615
DA2@.microsoft.com...[vbcol=seagreen]
> You could go down the OA_Create route - I believe this was originally
> cribbed
> from www.swynk.org.
> CREATE PROCEDURE ExportToExcel (
> @.server sysname = null,
> @.uname sysname = null,
> @.pwd sysname = null,
> @.QueryText varchar(200) = null,
> @.filename varchar(200) = 'C:\NorthwindCategories.xls'
> )
> AS
> DECLARE @.SQLServer int,
> @.QueryResults int,
> @.CurrentResultSet int,
> @.object int,
> @.WorkBooks int,
> @.WorkBook int,
> @.Range int,
> @.hr int,
> @.Columns int,
> @.Rows int,
> @.indColumn int,
> @.indRow int,
> @.off_Column int,
> @.off_Row int,
> @.code_str varchar(100),
> @.result_str varchar(255)
> IF @.QueryText IS NULL
> BEGIN
> PRINT 'Set the query string'
> RETURN
> END
> -- Sets the server to the local server
> IF @.server IS NULL SELECT @.server = @.@.servername
> -- Sets the username to the current user name
> IF @.uname IS NULL SELECT @.uname = SYSTEM_USER
> SET NOCOUNT ON
> EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.SQLServer OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create SQLDMO.SQLServer'
> RETURN
> END
> -- Connect to the SQL Server
> IF @.pwd IS NULL
> BEGIN
> EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname
> IF @.hr <> 0
> BEGIN
> PRINT 'error Connect'
> RETURN
> END
> END
> ELSE
> BEGIN
> EXEC @.hr = sp_OAMethod @.SQLServer, 'Connect', null, @.server, @.uname,
> @.pwd
> IF @.hr <> 0
> BEGIN
> PRINT 'error Connect'
> RETURN
> END
> END
> PRINT @.QueryText
> SELECT @.result_str = 'ExecuteWithResults("' + @.QueryText + '")'
> EXEC @.hr = sp_OAMethod @.SQLServer, @.result_str, @.QueryResults OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method ExecuteWithResults'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'CurrentResultSet',
> @.CurrentResultSet
> OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get CurrentResultSet'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'Columns', @.Columns OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Columns'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.QueryResults, 'Rows', @.Rows OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Rows'
> RETURN
> END
> EXEC @.hr = sp_OACreate 'Excel.Application', @.object OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Excel.Application'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.object, 'WorkBooks', @.WorkBooks OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create WorkBooks'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.WorkBooks, 'Add', @.WorkBook OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method Add'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.object, 'Range("A1")', @.Range OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Range'
> RETURN
> END
> SELECT @.indRow = 1
> SELECT @.off_Row = 0
> SELECT @.off_Column = 1
> WHILE (@.indRow <= @.Rows)
> BEGIN
> SELECT @.indColumn = 1
> WHILE (@.indColumn <= @.Columns)
> BEGIN
> EXEC @.hr = sp_OAMethod @.QueryResults, 'GetColumnString', @.result_str OUT,
> @.indRow, @.indColumn
> IF @.hr <> 0
> BEGIN
> PRINT 'error get GetColumnString'
> RETURN
> END
> EXEC @.hr = sp_OASetProperty @.Range, 'Value', @.result_str
> IF @.hr <> 0
> BEGIN
> PRINT 'error set Value'
> RETURN
> END
> EXEC @.hr = sp_OAGetProperty @.Range, 'Offset', @.Range OUT, @.off_Row,
> @.off_Column
> IF @.hr <> 0
> BEGIN
> PRINT 'error get Offset'
> RETURN
> END
> SELECT @.indColumn = @.indColumn + 1
> END
> SELECT @.indRow = @.indRow + 1
> SELECT @.code_str = 'Range("A' + LTRIM(str(@.indRow)) + '")'
> EXEC @.hr = sp_OAGetProperty @.object, @.code_str, @.Range OUT
> IF @.hr <> 0
> BEGIN
> PRINT 'error create Range'
> RETURN
> END
> END
> SELECT @.result_str = 'exec master..xp_cmdshell ''del ' + @.filename + ''',
> no_output'
> EXEC(@.result_str)
> SELECT @.result_str = 'SaveAs("' + @.filename + '")'
> EXEC @.hr = sp_OAMethod @.WorkBook, @.result_str
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method SaveAs'
> RETURN
> END
> EXEC @.hr = sp_OAMethod @.WorkBook, 'Close'
> IF @.hr <> 0
> BEGIN
> PRINT 'error with method Close'
> RETURN
> END
> EXEC @.hr = sp_OADestroy @.object
> IF @.hr <> 0
> BEGIN
> PRINT 'error destroy Excel.Application'
> RETURN
> END
> EXEC @.hr = sp_OADestroy @.SQLServer
> IF @.hr <> 0
> BEGIN
> PRINT 'error destroy SQLDMO.SQLServer'
> RETURN
> END
> GO
>
> "SOHO" wrote:
>|||Thanks for your reply.
Thanks !
"kcwms" <noneOfYoBusiness@.me.net> glsD:eBCSdQndFHA.3184@.TK2MSFTNGP15.phx.gbl...[vb
col=seagreen]
> Not an actual xls file, but excell will treat it like one and it will be
> associated with excell on most users machines
> Read KB#890775 first.
> ****************************
> declare @.cmd varchar(255)
> select @.cmd =
> 'bcp "pubs.dbo.authors" out "c:\authors.csv" -c -t"," -r\n -U"sa" -P""'
> exec master.dbo.xp_cmdshell @.cmd
> ****************************
> kcwms
>
> "SOHO" <hkwin2000@.hotmail.com> wrote in message
> news:%23ZWTPpkdFHA.1040@.TK2MSFTNGP10.phx.gbl...
>[/vbcol]

Please fix the post-SP1 excel export issue

Though it has been well-documented in the community since SP1 was released, I'd like to reiterate that many of us are having critical issues related to element hiding and excel export.
I, for one, have built a highly dynamic reporting system that relies heavily on the use visibility for showing/hiding subreports. My users require export to excel.
The question to Microsoft is: Can you fix this issue that was introduced in SP1? Please provide us with an SP1a update that will allow elements to be hidden & thus NOT show up in the excel export...just as it works in the HTML & PDF renderings.
Regards,
Todd SmartIt may be too much work to "re-tool", but have you considered DTS services?
However, I too, am waiting for a fix on Excel exporting!
-KB
"Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
news:5430232F-211C-439A-A4B0-F644AA68B740@.microsoft.com...
> Though it has been well-documented in the community since SP1 was
released, I'd like to reiterate that many of us are having critical issues
related to element hiding and excel export.
> I, for one, have built a highly dynamic reporting system that relies
heavily on the use visibility for showing/hiding subreports. My users
require export to excel.
> The question to Microsoft is: Can you fix this issue that was introduced
in SP1? Please provide us with an SP1a update that will allow elements to
be hidden & thus NOT show up in the excel export...just as it works in the
HTML & PDF renderings.
>
> Regards,
> Todd Smart|||I'm not sure what you want us to do. You would rather have items that are
hidden and can never be toggled?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
news:5430232F-211C-439A-A4B0-F644AA68B740@.microsoft.com...
> Though it has been well-documented in the community since SP1 was
> released, I'd like to reiterate that many of us are having critical issues
> related to element hiding and excel export.
> I, for one, have built a highly dynamic reporting system that relies
> heavily on the use visibility for showing/hiding subreports. My users
> require export to excel.
> The question to Microsoft is: Can you fix this issue that was introduced
> in SP1? Please provide us with an SP1a update that will allow elements to
> be hidden & thus NOT show up in the excel export...just as it works in the
> HTML & PDF renderings.
>
> Regards,
> Todd Smart|||Hi Brian,
If I have a subreport (or any other element for that matter) that is hidden due to an expression result of [True], that subreport should not be rendered in the Excel export. It should not even have an Excel cell associated with it.
In other words, I would want the render of Excel to work just like the HTML & PDF render works...and how it worked prior to SP1.
Regards,
Todd
"Brian Welcker [MSFT]" wrote:
> I'm not sure what you want us to do. You would rather have items that are
> hidden and can never be toggled?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
> news:5430232F-211C-439A-A4B0-F644AA68B740@.microsoft.com...
> > Though it has been well-documented in the community since SP1 was
> > released, I'd like to reiterate that many of us are having critical issues
> > related to element hiding and excel export.
> >
> > I, for one, have built a highly dynamic reporting system that relies
> > heavily on the use visibility for showing/hiding subreports. My users
> > require export to excel.
> >
> > The question to Microsoft is: Can you fix this issue that was introduced
> > in SP1? Please provide us with an SP1a update that will allow elements to
> > be hidden & thus NOT show up in the excel export...just as it works in the
> > HTML & PDF renderings.
> >
> >
> > Regards,
> >
> > Todd Smart
>
>|||OK, we'll take a look at this and if it is a regression, we will fix it in
SP2.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
news:423C4612-1C70-4178-9DCA-321019F859E0@.microsoft.com...
> Hi Brian,
> If I have a subreport (or any other element for that matter) that is
> hidden due to an expression result of [True], that subreport should not be
> rendered in the Excel export. It should not even have an Excel cell
> associated with it.
> In other words, I would want the render of Excel to work just like the
> HTML & PDF render works...and how it worked prior to SP1.
>
> Regards,
> Todd
> "Brian Welcker [MSFT]" wrote:
>> I'm not sure what you want us to do. You would rather have items that are
>> hidden and can never be toggled?
>> --
>> Brian Welcker
>> Group Program Manager
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
>> news:5430232F-211C-439A-A4B0-F644AA68B740@.microsoft.com...
>> > Though it has been well-documented in the community since SP1 was
>> > released, I'd like to reiterate that many of us are having critical
>> > issues
>> > related to element hiding and excel export.
>> >
>> > I, for one, have built a highly dynamic reporting system that relies
>> > heavily on the use visibility for showing/hiding subreports. My users
>> > require export to excel.
>> >
>> > The question to Microsoft is: Can you fix this issue that was
>> > introduced
>> > in SP1? Please provide us with an SP1a update that will allow elements
>> > to
>> > be hidden & thus NOT show up in the excel export...just as it works in
>> > the
>> > HTML & PDF renderings.
>> >
>> >
>> > Regards,
>> >
>> > Todd Smart
>>|||Brian, are there any work arounds ? I am also finding that visible tables are
not being displayed in the export to excel, and the start of the table data
appears several rows down (80 rows) .
Regards
Stuart
"Brian Welcker [MSFT]" wrote:
> OK, we'll take a look at this and if it is a regression, we will fix it in
> SP2.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
> news:423C4612-1C70-4178-9DCA-321019F859E0@.microsoft.com...
> > Hi Brian,
> >
> > If I have a subreport (or any other element for that matter) that is
> > hidden due to an expression result of [True], that subreport should not be
> > rendered in the Excel export. It should not even have an Excel cell
> > associated with it.
> >
> > In other words, I would want the render of Excel to work just like the
> > HTML & PDF render works...and how it worked prior to SP1.
> >
> >
> > Regards,
> >
> > Todd
> >
> > "Brian Welcker [MSFT]" wrote:
> >
> >> I'm not sure what you want us to do. You would rather have items that are
> >> hidden and can never be toggled?
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >> "Todd Smart" <ToddSmart@.discussions.microsoft.com> wrote in message
> >> news:5430232F-211C-439A-A4B0-F644AA68B740@.microsoft.com...
> >> > Though it has been well-documented in the community since SP1 was
> >> > released, I'd like to reiterate that many of us are having critical
> >> > issues
> >> > related to element hiding and excel export.
> >> >
> >> > I, for one, have built a highly dynamic reporting system that relies
> >> > heavily on the use visibility for showing/hiding subreports. My users
> >> > require export to excel.
> >> >
> >> > The question to Microsoft is: Can you fix this issue that was
> >> > introduced
> >> > in SP1? Please provide us with an SP1a update that will allow elements
> >> > to
> >> > be hidden & thus NOT show up in the excel export...just as it works in
> >> > the
> >> > HTML & PDF renderings.
> >> >
> >> >
> >> > Regards,
> >> >
> >> > Todd Smart
> >>
> >>
> >>
>
>

Pivot Tables in Excel

I Created pivot tables and pivot charts in excel getting data from an external source (SQL Server 2000) using an ODBC Connection. The problem is when i want to use the same spreadsheet acceess the server from outside through the internet using an ODBC connection.

The odbc which access the sql server remotely uses as server name the ip address of the server than sql server running to.

The pivot tables when it was created it used the odbc connection accessed from with in the LAN (Server name /SERVERGROUP/SQLSERVER).

I was just wondering if there is an easy way to modify the code that the pivot tables uses to make the connection to the SQL Server with out
recreating all those pivot tables and pivot charts.

When I try to open the file remotely it fails to make a connection of course.

Any help is appreciated!

ThanksIf you are using an Excel Macro to do your queries, you are in luck. All you need to do is open the VB Editor and edit the connection string.

Otherwise, you will most likely have to recreate your queries (but if you do so, create a Macro, so you can edit it moreeasily in the future). All you need to do it turn on the Macro Recorder, build your queries as you did before, even perform any formatting, then stop the recorder. Now, if you ever need to tweek the query or change the datasource, it's simply a matter of editing the Marco code.

-b|||well thanks i knew that I could do that but I have over 20 graphs and 10 reports so it would be nice if there is a way for a quick and dirty solution. Saying all that if there is not a way to access the code that excel creates on the background when you use those pivot table wizards so I could manually modify the connection string then I guess I need to redo it from scrach and use macros this time.

Thanks for your input

Pivot Tables and MDX

Hi!
I want to write some MDX queries that pull the same data I'm retriving into
an excel pivot table that is hitting my cube. Up front, I'm pretty sure thi
s is not doable, but need to ask anyway. I want some way of hooking into th
e MSOLAP provider to see th
e MDX that's generated and sent to analysis services to retrieve the data in
to the pivot table. This would be an excellent shortcut to getting my MDX q
uery results to match what I'm getting in the pivot table.
Any anyone know of a way to see the MDX that's being generated from the pivo
t table?
-thanksInclude property Log File in your connection string.
Log File="C:\work\Sales_stock\mdx\excel_log\log.txt"
in log.txt you will find mdx queries.
Ramunas Balukonis
"brian p" <anonymous@.discussions.microsoft.com> wrote in message
news:399BECD7-270E-4539-BAC0-C628EB09D2B8@.microsoft.com...
> Hi!
> I want to write some MDX queries that pull the same data I'm retriving
into an excel pivot table that is hitting my cube. Up front, I'm pretty
sure this is not doable, but need to ask anyway. I want some way of hooking
into the MSOLAP provider to see the MDX that's generated and sent to
analysis services to retrieve the data into the pivot table. This would be
an excellent shortcut to getting my MDX query results to match what I'm
getting in the pivot table.
> Any anyone know of a way to see the MDX that's being generated from the
pivot table?
> -thanks

pivot tables & offline cube access

Is it possible to have multiple pivot tables in the same Excel workbook accessing the same offline cube file?

Currently, when the user is connected to the server it connects to a .asdatabase to populate many pivot tables. I need to allow the user to access a local cube file when they are not connected to the server.

I am using excel vba macros to connect the pivot caches to the online data source, which works perfectly. I would like to do the same to connect the pivot caches to the local cube file.

Is this possible? The local cube file is created off the server database using MDX (create global cube).

Thanks,
Lyn

If this is AS2005 - there is a bug in this area that is being fixed in the SP2 release. I think the fix would allow you to do what you've described.|||

Is this possible? The local cube file is created off the server database using MDX (create global cube).

yes possible , but there is a problem. u have to run this MDX statment manually , I tries to put it in a schedule in a jobs list , but it says the MDX syntax is worng, any help?

Karim

pivot tables & offline cube access

Is it possible to have multiple pivot tables in the same Excel workbook accessing the same offline cube file?

Currently, when the user is connected to the server it connects to a .asdatabase to populate many pivot tables. I need to allow the user to access a local cube file when they are not connected to the server.

I am using excel vba macros to connect the pivot caches to the online data source, which works perfectly. I would like to do the same to connect the pivot caches to the local cube file.

Is this possible? The local cube file is created off the server database using MDX (create global cube).

Thanks,
Lyn

If this is AS2005 - there is a bug in this area that is being fixed in the SP2 release. I think the fix would allow you to do what you've described.|||

Is this possible? The local cube file is created off the server database using MDX (create global cube).

yes possible , but there is a problem. u have to run this MDX statment manually , I tries to put it in a schedule in a jobs list , but it says the MDX syntax is worng, any help?

Karim

pivot tables & offline cube access

Is it possible to have multiple pivot tables in the same Excel workbook accessing the same offline cube file?

Currently, when the user is connected to the server it connects to a .asdatabase to populate many pivot tables. I need to allow the user to access a local cube file when they are not connected to the server.

I am using excel vba macros to connect the pivot caches to the online data source, which works perfectly. I would like to do the same to connect the pivot caches to the local cube file.

Is this possible? The local cube file is created off the server database using MDX (create global cube).

Thanks,
Lyn

If this is AS2005 - there is a bug in this area that is being fixed in the SP2 release. I think the fix would allow you to do what you've described.|||

Is this possible? The local cube file is created off the server database using MDX (create global cube).

yes possible , but there is a problem. u have to run this MDX statment manually , I tries to put it in a schedule in a jobs list , but it says the MDX syntax is worng, any help?

Karim

Pivot table very slow

We are using MS SQL Server 2000 with its Analysis Services and Excel 2002.
We have 3 facts tables (less than 100 rows each) ,
8 dimensions (from 10 to 1000 members),
3 regular cubes and 1 virtual cube (with 20 calculated members).
The pivot table is produced from the virtual cube.
In Excel, when I put 2 dimensions side by side as row fields with all the calculated members,
it's very very slow (2-3 minutes).
When I remove 1 dimension as row field, it's fast (5 sec).
Or when I replaced all calculated members by regular measures (with 2 dimensions as row fields),
it's fast too !
I have tried different storage design or adding "default isolation mode=1" to Excel
connection string but it's still very slow.
Your help/suggestions will be very appreciated.
Thanks.
Create aggregation based on those two dimensions, or create an extra
dimension (dimension1 -> dimension 2), see if they help
Eric Li
SQL DBA
MCDBA
T.Huynh wrote:

> We are using MS SQL Server 2000 with its Analysis Services and Excel 2002.
> We have 3 facts tables (less than 100 rows each) ,
> 8 dimensions (from 10 to 1000 members),
> 3 regular cubes and 1 virtual cube (with 20 calculated members).
> The pivot table is produced from the virtual cube.
> In Excel, when I put 2 dimensions side by side as row fields with all the calculated members,
> it's very very slow (2-3 minutes).
> When I remove 1 dimension as row field, it's fast (5 sec).
> Or when I replaced all calculated members by regular measures (with 2 dimensions as row fields),
> it's fast too !
> I have tried different storage design or adding "default isolation mode=1" to Excel
> connection string but it's still very slow.
> Your help/suggestions will be very appreciated.
> Thanks.
>
|||How do you create an extra
dimension (dimension1 -> dimension 2) in Analysis Services ?
I have dim 1 (20 members) and dim 2 (1000 members).
Thanks for your advice.
|||Take a look to SQL Server Accelerator for BI. it includes an utility to
create your own Aggregations
ALEJANDRO LEGUIZAMO
MVP SQL - Colombia
"T Huynh" <anonymous@.discussions.microsoft.com> wrote in message
news:3D6120F1-228E-4E52-8BBA-E9F548970030@.microsoft.com...
> How do you create an extra
> dimension (dimension1 -> dimension 2) in Analysis Services ?
> I have dim 1 (20 members) and dim 2 (1000 members).
> Thanks for your advice.

Pivot table very slow

We are using MS SQL Server 2000 with its Analysis Services and Excel 2002.
We have 3 facts tables (less than 100 rows each) ,
8 dimensions (from 10 to 1000 members),
3 regular cubes and 1 virtual cube (with 20 calculated members).
The pivot table is produced from the virtual cube.
In Excel, when I put 2 dimensions side by side as row fields with all the ca
lculated members,
it's very very slow (2-3 minutes).
When I remove 1 dimension as row field, it's fast (5 sec).
Or when I replaced all calculated members by regular measures (with 2 dimens
ions as row fields),
it's fast too !
I have tried different storage design or adding "default isolation mode=1" t
o Excel
connection string but it's still very slow.
Your help/suggestions will be very appreciated.
Thanks.Create aggregation based on those two dimensions, or create an extra
dimension (dimension1 -> dimension 2), see if they help
Eric Li
SQL DBA
MCDBA
T.Huynh wrote:

> We are using MS SQL Server 2000 with its Analysis Services and Excel 2002.
> We have 3 facts tables (less than 100 rows each) ,
> 8 dimensions (from 10 to 1000 members),
> 3 regular cubes and 1 virtual cube (with 20 calculated members).
> The pivot table is produced from the virtual cube.
> In Excel, when I put 2 dimensions side by side as row fields with all the
calculated members,
> it's very very slow (2-3 minutes).
> When I remove 1 dimension as row field, it's fast (5 sec).
> Or when I replaced all calculated members by regular measures (with 2 dime
nsions as row fields),
> it's fast too !
> I have tried different storage design or adding "default isolation mode=1"
to Excel
> connection string but it's still very slow.
> Your help/suggestions will be very appreciated.
> Thanks.
>|||How do you create an extra
dimension (dimension1 -> dimension 2) in Analysis Services ?
I have dim 1 (20 members) and dim 2 (1000 members).
Thanks for your advice.|||Take a look to SQL Server Accelerator for BI. it includes an utility to
create your own Aggregations
ALEJANDRO LEGUIZAMO
MVP SQL - Colombia
"T Huynh" <anonymous@.discussions.microsoft.com> wrote in message
news:3D6120F1-228E-4E52-8BBA-E9F548970030@.microsoft.com...
> How do you create an extra
> dimension (dimension1 -> dimension 2) in Analysis Services ?
> I have dim 1 (20 members) and dim 2 (1000 members).
> Thanks for your advice.

Pivot Table Bug with AdventureWorks

A user pointed out an odd bug / inconsistency that occurs when using Excel pivot tables. Depending on how attributes are related to each other, Excel does some odd things when cross joining attributes from the same dimension on an axis.

Example 1. "Correct" cross join of attributes:

Connect to AdventureWorks from Excel pivot table. Drop in "Internet Sales Amount".

On rows add "State-Province" and "Total Children". Row 4 should read "State-Province, Total Children, Total".

Click the drop-down on "Total Children" and select "1".

The results should be filtered so that you only see states where there are internet sales for families with 1 child.

Example 2. "Incorrect" cross join of attributes:

Connect to AdventureWorks from Excel pivot table. Drop in "Internet Sales Amount".

On rows add "State-Province" and "Country". Row 4 should read "State-Province, Country, Total".

Click the drop-down on "Country" and select "United States".

The results *should* be filtered so that you only see states belonging to the United States. Instead, we still see states belonging to other countries.

Shouldn't the "United States" selection on the rows preclude the states from other countries? I don't understand why the pairs of attributes in examples 1 and 2 interact so differently. I would appreciate any meaningful explanation than I can pass on to the user.

Are you using Excel 2003 or Excel 2007? Not sure if I recreated the examples exactly or not, but in Excel 2007, it seems to behave as you expect it to. In the first example, I get the states filtered to just those that had customers with a total children value of 1. And in the second example, I get the states filtered to just those in the United States.

Unless I'm doing something wrong, this seems to work in Excel 2007. I don't have Excel 2003 loaded on my current machine, but could get to another machine to test this if you are indeed using Excel 2003...

Another question would be what service pack level you have installed for Analysis Services. I have SP2 installed (which I don't think would change the behaviour of Excel, but...) So, if you are using Excel 2007, what service package are you running for SSAS?

Dave Fackler

|||

I started with Excel 2003. I saw the bizarre behavior. Then I saved the document and opened in Excel 2007. And still saw the bizarre behavior.

In 2007 I have to enable "classic layout" to get both "State-Province" and "Country" to both show up side-by-side (stacked) on rows. Then I just uncheck all countries and check only "United States". However the states and provinces of other countries are still displayed in the first column.

I'm on SP2 of SSAS. Thanks in advance.

|||

Is it possible that you have the Country attribute from the Geography dimension selected with the State-Province attribute from the Customer dimension (or vice versa)? Both dimensions contain geography attributes. While they are named the same, they represent attributes in different dimensions. Thus, they are related only through the fact table. That means that filtering of one will not filter values from the other (although the filtering does affect the fact data as expected).

Dave Fackler

|||

The problem happens when the "State-Province" is added in rows with the "Country" of the *same* (Customer) dimension. We actually noticed the problem first in our production cubes and then reproduced it in AdventureWorks.

To be clear, add "State-Province" in rows so it shows in the *first* column, add "Country" in the *second* column, and then try to filter the country by the "United States". Unfortunately this doesn't preclude states from other countries.

This is such an obviously inconsistency but it only occurs when attributes are related to each other in the way that "State-Province" and "Country" are. I'm surprised that I haven't heard of other complaints. Also unusual is the fact that my own MDX expressions with these two attributes never return the same data that Excel is displaying.

Anybody else see this problem?