> Dear all,
> i had a table named saletable i had fields like
> itemgroupid,itemgroupname,saleqty,saleam
ount and saledate.
> i want to sum the saleqty and saleamount by every month and year.by every itemgoup
name
suppose that every year has january month and 31 days in january so i want
that for every year and every month i should sum the qty and sale amount for
Y/M but by Itemgroupname
do not want to give criteria
> for eaxmple
> itemgroupid itemgroupname saleqty saleamount date
> ----
--
> 23101102 HERO LINER 10.00 50467.29 2004-01
-07
> 23101102 HERO LINER 20.00 10924.58 2005-01
-07
> 23101102 HERO LINER 10.00 50467.29 2004-02
-07
> 23101102 HERO LINER 20.00 10924.58 2005-02
-07
> 23101102 HERO LINER 10.00 50467.29 2004-01
-07
> 23101102 HERO LINER 20.00 10924.58 2005-01
-07
> 23204101 Trading Mudgard 10.00 6000.00 2005-04-21
> 23204101 Trading Mudgard 10.00 50467.29 2004-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-02-07
> 23204101 Trading Mudgard 10.00 50467.29 2004-01-07
> 23204101 Trading Mudgard 20.00 10924.58 2005-01-07
> I WANT like this
> for every month and year
> 23204101 Trading Mudgard 20.00 100934.58 2004-01
> 23204101 Trading Mudgard 60.00 2326000.00 2005-01
> 23204101 Trading Mudgard 10.00 6000.00 2005-07
> thanx
--
waiting for solution
from
SufianSELECT itemgroupid, itemgroupname, MIN(date),
SUM(saleqty), SUM(saleamount)
FROM SaleTable
GROUP BY YEAR(date), MONTH(date)
If that's not what you want then please post DDL, sample data, required
results. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Thanx David it worked and once again thanx
--
waiting for solution
from
Sufian
"David Portas" wrote:
> SELECT itemgroupid, itemgroupname, MIN(date),
> SUM(saleqty), SUM(saleamount)
> FROM SaleTable
> GROUP BY YEAR(date), MONTH(date)
> If that's not what you want then please post DDL, sample data, required
> results. See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>
>
Showing posts with label named. Show all posts
Showing posts with label named. Show all posts
Tuesday, March 27, 2012
Sunday, March 25, 2012
Cursor for MSAccess table
I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!"Gaspar" <gaspar@.no-reply.com> wrote in message
news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>I need to process some data from a read-only MSAccess table named "Padron"
>that has 350000+ records.
> I declare the linked server:
> EXEC sp_addlinkedserver
> @.server = 'PADRONELECTORAL',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
> The problem is that when I open the cursor, the operation takes too long:
> DECLARE tabla CURSOR LOCAL FOR
> SELECT * FROM
> PADRONELECTORAL...Padron
> OPEN tabla
> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
> solve the problem.
> Note: If I try to open the cursor with a table that has a few of records,
> the operation executes immediately, so I guess the problem is related with
> record count.
> Thanks!
Why do you need a cursor? Perhaps there's a way to achieve the same result
without a cursor. If you describe your problem with DDL and sample data then
someone might be able to help.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I need to process some data from a read-only MSAccess table named "Padron"
>> that has 350000+ records.
>> I declare the linked server:
>> EXEC sp_addlinkedserver
>> @.server = 'PADRONELECTORAL',
>> @.provider = 'Microsoft.Jet.OLEDB.4.0',
>> @.srvproduct = 'OLE DB Provider for Jet',
>> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
>> The problem is that when I open the cursor, the operation takes too long:
>> DECLARE tabla CURSOR LOCAL FOR
>> SELECT * FROM
>> PADRONELECTORAL...Padron
>> OPEN tabla
>> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
>> solve the problem.
>> Note: If I try to open the cursor with a table that has a few of records,
>> the operation executes immediately, so I guess the problem is related with
>> record count.
>> Thanks!
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data then
> someone might be able to help.
>|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!"Gaspar" <gaspar@.no-reply.com> wrote in message
news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>I need to process some data from a read-only MSAccess table named "Padron"
>that has 350000+ records.
> I declare the linked server:
> EXEC sp_addlinkedserver
> @.server = 'PADRONELECTORAL',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
> The problem is that when I open the cursor, the operation takes too long:
> DECLARE tabla CURSOR LOCAL FOR
> SELECT * FROM
> PADRONELECTORAL...Padron
> OPEN tabla
> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
> solve the problem.
> Note: If I try to open the cursor with a table that has a few of records,
> the operation executes immediately, so I guess the problem is related with
> record count.
> Thanks!
Why do you need a cursor? Perhaps there's a way to achieve the same result
without a cursor. If you describe your problem with DDL and sample data then
someone might be able to help.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>> I need to process some data from a read-only MSAccess table named "Padron"
>> that has 350000+ records.
>> I declare the linked server:
>> EXEC sp_addlinkedserver
>> @.server = 'PADRONELECTORAL',
>> @.provider = 'Microsoft.Jet.OLEDB.4.0',
>> @.srvproduct = 'OLE DB Provider for Jet',
>> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
>> The problem is that when I open the cursor, the operation takes too long:
>> DECLARE tabla CURSOR LOCAL FOR
>> SELECT * FROM
>> PADRONELECTORAL...Padron
>> OPEN tabla
>> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
>> solve the problem.
>> Note: If I try to open the cursor with a table that has a few of records,
>> the operation executes immediately, so I guess the problem is related with
>> record count.
>> Thanks!
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data then
> someone might be able to help.
>|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Cursor for MSAccess table
I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!
This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data then
> someone might be able to help.
>
|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!
This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data then
> someone might be able to help.
>
|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
Cursor for MSAccess table
I need to process some data from a read-only MSAccess table named
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!"Gaspar" <gaspar@.no-reply.com> wrote in message
news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>I need to process some data from a read-only MSAccess table named "Padron"
>that has 350000+ records.
> I declare the linked server:
> EXEC sp_addlinkedserver
> @.server = 'PADRONELECTORAL',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
> The problem is that when I open the cursor, the operation takes too long:
> DECLARE tabla CURSOR LOCAL FOR
> SELECT * FROM
> PADRONELECTORAL...Padron
> OPEN tabla
> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
> solve the problem.
> Note: If I try to open the cursor with a table that has a few of records,
> the operation executes immediately, so I guess the problem is related with
> record count.
> Thanks!
Why do you need a cursor? Perhaps there's a way to achieve the same result
without a cursor. If you describe your problem with DDL and sample data then
someone might be able to help.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data th
en
> someone might be able to help.
>|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
"Padron" that has 350000+ records.
I declare the linked server:
EXEC sp_addlinkedserver
@.server = 'PADRONELECTORAL',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
The problem is that when I open the cursor, the operation takes too long:
DECLARE tabla CURSOR LOCAL FOR
SELECT * FROM
PADRONELECTORAL...Padron
OPEN tabla
Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
solve the problem.
Note: If I try to open the cursor with a table that has a few of
records, the operation executes immediately, so I guess the problem is
related with record count.
Thanks!"Gaspar" <gaspar@.no-reply.com> wrote in message
news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
>I need to process some data from a read-only MSAccess table named "Padron"
>that has 350000+ records.
> I declare the linked server:
> EXEC sp_addlinkedserver
> @.server = 'PADRONELECTORAL',
> @.provider = 'Microsoft.Jet.OLEDB.4.0',
> @.srvproduct = 'OLE DB Provider for Jet',
> @.datasrc = 'E:\Datos\Padrones\Electoral-2007.mdb'
> The problem is that when I open the cursor, the operation takes too long:
> DECLARE tabla CURSOR LOCAL FOR
> SELECT * FROM
> PADRONELECTORAL...Padron
> OPEN tabla
> Is there any way to optimize it? I tried with FAST_FORWARD but it didn't
> solve the problem.
> Note: If I try to open the cursor with a table that has a few of records,
> the operation executes immediately, so I guess the problem is related with
> record count.
> Thanks!
Why do you need a cursor? Perhaps there's a way to achieve the same result
without a cursor. If you describe your problem with DDL and sample data then
someone might be able to help.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||This is what I need:
For every record in PADRONELECTORAL...Padron:
- Read it from table
- Process read data calling a Stored Procedure
- Save the modified records in a SQL table.
That's why I use cursors. Any other idea?
Thanks again.
David Portas wrote:
> "Gaspar" <gaspar@.no-reply.com> wrote in message
> news:eXOp7US1HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Why do you need a cursor? Perhaps there's a way to achieve the same result
> without a cursor. If you describe your problem with DDL and sample data th
en
> someone might be able to help.
>|||On 3 Aug, 11:56, Gaspar <gas...@.no-reply.com> wrote:
> This is what I need:
> For every record in PADRONELECTORAL...Padron:
> - Read it from table
> - Process read data calling a Stored Procedure
> - Save the modified records in a SQL table.
> That's why I use cursors. Any other idea?
Yes. Rewrite the proc so that you can process the whole table at once
and then you aren't forced to process each row individually (assuming
you are allowed to create a new proc!). Unfortunately you still didn't
give us a spec or post any code so it's hard to help you any further.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the
content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Friday, February 24, 2012
Cube Calculation TAB Error
Hi, I have been using a cube for reporting purposes and notice that when i open cube and click on CALCULATION tab for named calculation reason; i got error looklike 'Unexpected error occured : Attempted to read or write protected memory, this is often an indication that other memory is corrupt .'
Could anyone please reply is it configuration problem or reinstalled VS2005 which is higly unlikely for me at the moment.
Many Thanks
Nasir
Check out http://support.microsoft.com/default.aspx/kb/926421.
Could anyone please reply is it configuration problem or reinstalled VS2005 which is higly unlikely for me at the moment.
Many Thanks
Nasir
Check out http://support.microsoft.com/default.aspx/kb/926421.
Sunday, February 19, 2012
CTE in a DSV named query
Hello,
I have a CTE that I want to put into a DSV named query. here is the CTE (which anyone can run):
WITH mycte AS (SELECT TOP (200) object_id, name, column_id, system_type_id
FROM sys.columns)
SELECT object_id, name, column_id, system_type_id
FROM mycte AS mycte_1
I can put that into the named query editor and run it, no problems. Upon clicking "OK" I get:
Incorrect syntax near ')'
Incorrect syntax near the keyword 'with'/ If this statement is a common table expression or an xmlnamespaces clause the previous statement must be terminated with a semicolon.
So, I put a semicolon in front of it so it looks liek this:
;WITH mycte AS (SELECT TOP (200) object_id, name, column_id, system_type_id
FROM sys.columns)
SELECT object_id, name, column_id, system_type_id
FROM mycte AS mycte_1
This time I get:
Unable to parse query text
Incorrect syntax near ')'
Incorrect syntax near ';'
Please don't tell me that CTEs are not allowed in a DSV. That would be very VERY bad.
Please will someone try this for me? Can you repro the problem?
Thanks
Jamie
Jamie,
You might want to pose this question to the SQL Server Programming folks as well - from what I can tell, the problem is that the Named Query is embedded as a subquery, and I'm not sure how/whether a CTE can be used in a subquery. The generated query looks like:
select [CTETest].*
from
(
WITH mycte AS (SELECT TOP (200) object_id, name, column_id, system_type_id
FROM sys.columns)
SELECT object_id, name, column_id, system_type_id
FROM mycte as mycte_1
) AS [CTETest]
Defining the CTE at the outer select works, but obviously doesn't help for the DSV:
WITH mycte AS (SELECT TOP (200) object_id, name, column_id, system_type_id
FROM sys.columns)
select [CTETest].*
from
(
SELECT object_id, name, column_id, system_type_id
FROM mycte as mycte_1
) AS [CTETest]
Thanks Deepak,
Fundamentally though, the fact that you can't put valid T-SQL into a DSV is bad bad bad wouldn't you agree?
I'm not too enamoured with this.
-Jamie
|||Jamie, I share your frustation - just curious whether the CTE scenario was considered when the DSV/Named Query framework was being architected? Maybe someone from MS can shed some light on this, meanwhile I'll post a question to see if there's any work-around on the relational side...|||To follow up - I got this response from Erland on the SQL Server Programming newsgroup, which suggests that an SSAS bug report be opened. There are some XSL Cartridge files that can be tweaked to change the generated SQL for other databases (DB2, SQL 2000, etc), but I'm not sure that there is a cartridge for SQL Server 2005 itself.
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/5150eba86d06e5b0
>>
microsoft.public.sqlserver.programming > Can CTE be used inside a FROM subquery?
This query works:
WITH mycte AS (SELECT TOP (200) object_id, name, column_id,
system_type_id
FROM sys.columns)
select [CTETest].*
from
(
SELECT object_id, name, column_id, system_type_id
FROM mycte as mycte_1
) AS [CTETest]
That is, the CTE should be at the head of the outer query.
If Analysis Services generates the incorrect syntax, I assume that
this is a bug in AS, and I suggest that you submit a bug on
http://lab.msdn.microsoft.com/ProductFeedback/.
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
...
>>
|||Thank you Deepak (and to Erland). I have raised this at the feedback centre - feel free to vote for it and add comments.
http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackId=FDBK49479
Thanks
Jamie
|||The reason why we are trying to wrap it as sub select statement is to avoid some statements like Create, Delete, order by.
You can try to remove the subselect capability in the cartridge by removing the line in Sql2000.xsl
<mssqlcrt:supports-subselect />
The cartridges are located in the following directories for tools and engine respectively.
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\DataWarehouseDesigner\UIRdmsCartridge
C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\Cartridges
After removing this, you should be able to create the named query but it may hurt the performance when processing in engine.
Tuesday, February 14, 2012
CSV
I am attempting to exports the results of a stored Procedure into a CSV. The
table is a temp tabel named #FinalPrivate. I need to allow a user to download
the file from a .NET Web Application. Is there someone that can point me in a
direction?
This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?
|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:
> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
> to the clipboard, and then paste those values into Excel. Then, in Excel,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> The
> download
> in a
>
>
|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
crit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?
|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
..NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/archi.../14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on[vbcol=seagreen]
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
copy[vbcol=seagreen]
Excel,[vbcol=seagreen]
different[vbcol=seagreen]
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...[vbcol=seagreen]
CSV.[vbcol=seagreen]
me[vbcol=seagreen]
|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:
> I am attempting to exports the results of a stored Procedure into a CSV. The
> table is a temp tabel named #FinalPrivate. I need to allow a user to download
> the file from a .NET Web Application. Is there someone that can point me in a
> direction?
table is a temp tabel named #FinalPrivate. I need to allow a user to download
the file from a .NET Web Application. Is there someone that can point me in a
direction?
This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?
|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:
> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
> to the clipboard, and then paste those values into Excel. Then, in Excel,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> The
> download
> in a
>
>
|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
crit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?
|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
..NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/archi.../14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on[vbcol=seagreen]
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
copy[vbcol=seagreen]
Excel,[vbcol=seagreen]
different[vbcol=seagreen]
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...[vbcol=seagreen]
CSV.[vbcol=seagreen]
me[vbcol=seagreen]
|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:
> I am attempting to exports the results of a stored Procedure into a CSV. The
> table is a temp tabel named #FinalPrivate. I need to allow a user to download
> the file from a .NET Web Application. Is there someone that can point me in a
> direction?
CSV
I am attempting to exports the results of a stored Procedure into a CSV. The
table is a temp tabel named #FinalPrivate. I need to allow a user to download
the file from a .NET Web Application. Is there someone that can point me in a
direction?This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:
> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
> to the clipboard, and then paste those values into Excel. Then, in Excel,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> > I am attempting to exports the results of a stored Procedure into a CSV.
> The
> > table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> > the file from a .NET Web Application. Is there someone that can point me
> in a
> > direction?
>
>|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
écrit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
.NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/archive/2005/07/14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
> > This sounds like a one-time, or not frequent need. If the results are
> > <65000 records, it is probaby easiest to highlight the results in query
> > analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to
copy
> > to the clipboard, and then paste those values into Excel. Then, in
Excel,
> > choose to "save as" type = comma delimited / CSV.
> >
> > I do this often and it's easier than your alternatives. If you need an
> > automated way that works by itself every 24 hours, then that's a
different
> > story.
> >
> > "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> > wrote in message
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> > > I am attempting to exports the results of a stored Procedure into a
CSV.
> > The
> > > table is a temp tabel named #FinalPrivate. I need to allow a user to
> > download
> > > the file from a .NET Web Application. Is there someone that can point
me
> > in a
> > > direction?
> >
> >
> >|||How do you create this DTS Package for exporting into a text file? I would
like to create and copy into a flat text file, but only see Bulk Insert that
will copy into SQL Tables. Could you provide some insight into how to export
using DTS.
Thanks!
"Christian Hamel" wrote:
> Why not simply creating a DTS (or SSIS if you're on 2005) package that will
> create that CSV file? Then it is a one click execute process.
>
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
> écrit dans le message de news:
> 7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> >I am attempting to exports the results of a stored Procedure into a CSV.
> >The
> > table is a temp tabel named #FinalPrivate. I need to allow a user to
> > download
> > the file from a .NET Web Application. Is there someone that can point me
> > in a
> > direction?
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:
> I am attempting to exports the results of a stored Procedure into a CSV. The
> table is a temp tabel named #FinalPrivate. I need to allow a user to download
> the file from a .NET Web Application. Is there someone that can point me in a
> direction?
table is a temp tabel named #FinalPrivate. I need to allow a user to download
the file from a .NET Web Application. Is there someone that can point me in a
direction?This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:
> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
> to the clipboard, and then paste those values into Excel. Then, in Excel,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> > I am attempting to exports the results of a stored Procedure into a CSV.
> The
> > table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> > the file from a .NET Web Application. Is there someone that can point me
> in a
> > direction?
>
>|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
écrit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
.NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/archive/2005/07/14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
> > This sounds like a one-time, or not frequent need. If the results are
> > <65000 records, it is probaby easiest to highlight the results in query
> > analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to
copy
> > to the clipboard, and then paste those values into Excel. Then, in
Excel,
> > choose to "save as" type = comma delimited / CSV.
> >
> > I do this often and it's easier than your alternatives. If you need an
> > automated way that works by itself every 24 hours, then that's a
different
> > story.
> >
> > "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> > wrote in message
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> > > I am attempting to exports the results of a stored Procedure into a
CSV.
> > The
> > > table is a temp tabel named #FinalPrivate. I need to allow a user to
> > download
> > > the file from a .NET Web Application. Is there someone that can point
me
> > in a
> > > direction?
> >
> >
> >|||How do you create this DTS Package for exporting into a text file? I would
like to create and copy into a flat text file, but only see Bulk Insert that
will copy into SQL Tables. Could you provide some insight into how to export
using DTS.
Thanks!
"Christian Hamel" wrote:
> Why not simply creating a DTS (or SSIS if you're on 2005) package that will
> create that CSV file? Then it is a one click execute process.
>
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
> écrit dans le message de news:
> 7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> >I am attempting to exports the results of a stored Procedure into a CSV.
> >The
> > table is a temp tabel named #FinalPrivate. I need to allow a user to
> > download
> > the file from a .NET Web Application. Is there someone that can point me
> > in a
> > direction?
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:
> I am attempting to exports the results of a stored Procedure into a CSV. The
> table is a temp tabel named #FinalPrivate. I need to allow a user to download
> the file from a .NET Web Application. Is there someone that can point me in a
> direction?
CSV
I am attempting to exports the results of a stored Procedure into a CSV. The
table is a temp tabel named #FinalPrivate. I need to allow a user to downloa
d
the file from a .NET Web Application. Is there someone that can point me in
a
direction?This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:
> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to cop
y
> to the clipboard, and then paste those values into Excel. Then, in Exce
l,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com..
.
> The
> download
> in a
>
>|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
crit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
.NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/arch...7/14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on[vbcol=seagreen]
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
>
copy[vbcol=seagreen]
Excel,[vbcol=seagreen]
different[vbcol=seagreen]
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...[vbcol=seagreen]
CSV.[vbcol=seagreen]
me[vbcol=seagreen]|||How do you create this DTS Package for exporting into a text file? I would
like to create and copy into a flat text file, but only see Bulk Insert that
will copy into SQL Tables. Could you provide some insight into how to expor
t
using DTS.
Thanks!
"Christian Hamel" wrote:
> Why not simply creating a DTS (or SSIS if you're on 2005) package that wil
l
> create that CSV file? Then it is a one click execute process.
>
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
> écrit dans le message de news:
> 7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:
> I am attempting to exports the results of a stored Procedure into a CSV. T
he
> table is a temp tabel named #FinalPrivate. I need to allow a user to downl
oad
> the file from a .NET Web Application. Is there someone that can point me i
n a
> direction?
table is a temp tabel named #FinalPrivate. I need to allow a user to downloa
d
the file from a .NET Web Application. Is there someone that can point me in
a
direction?This sounds like a one-time, or not frequent need. If the results are
<65000 records, it is probaby easiest to highlight the results in query
analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to copy
to the clipboard, and then paste those values into Excel. Then, in Excel,
choose to "save as" type = comma delimited / CSV.
I do this often and it's easier than your alternatives. If you need an
automated way that works by itself every 24 hours, then that's a different
story.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
> I am attempting to exports the results of a stored Procedure into a CSV.
The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
download
> the file from a .NET Web Application. Is there someone that can point me
in a
> direction?|||Yes, this is something that needs to be done ~5 times a week by customers on
demand. I need to complete this task programable. Any other thoughts?
"HK" wrote:
> This sounds like a one-time, or not frequent need. If the results are
> <65000 records, it is probaby easiest to highlight the results in query
> analyzer (hit CTRL - A on any cell to select all cells), hit CTRL-C to cop
y
> to the clipboard, and then paste those values into Excel. Then, in Exce
l,
> choose to "save as" type = comma delimited / CSV.
> I do this often and it's easier than your alternatives. If you need an
> automated way that works by itself every 24 hours, then that's a different
> story.
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
> wrote in message news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com..
.
> The
> download
> in a
>
>|||Why not simply creating a DTS (or SSIS if you're on 2005) package that will
create that CSV file? Then it is a one click execute process.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
crit dans le message de news:
7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>I am attempting to exports the results of a stored Procedure into a CSV.
>The
> table is a temp tabel named #FinalPrivate. I need to allow a user to
> download
> the file from a .NET Web Application. Is there someone that can point me
> in a
> direction?|||It seems this is an ASP.NET issue, and not a TSQL issue. You simply take
the results of the stored procedure and output as comma delimited in your
.NET code. Here is a very quick example I just pulled from google; it is
not be the best for your needs. The trick for letting the user vjew the
file is the line that outputs the "contenttype" to the browser.
http://dotnet.org.za/keithrull/arch...7/14/39202.aspx
That example is more meant for Excel output but I share it because it goes
into detail about things.
However, I prefer to use a method with ContentType="text/csv", which lets
the user download the file immediately as a CSV file. Look for an example
with that. To quote Jim Buyens in a google groups thread, he writes:
If you're only sending data, the easiest approach is to execute this code:
Response.ContentType = "text/csv"
Response.AddHeader "content-disposition",_
"attachment; filename=yourfile.csv"
then send the visitor a comma-separated-values file via Response.Write, then
call Response.End to make sure that no HTML or other output follows the
data.
"sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com>
wrote in message news:D9471EF6-7601-47A6-A723-5B1045B150F6@.microsoft.com...
> Yes, this is something that needs to be done ~5 times a week by customers
on[vbcol=seagreen]
> demand. I need to complete this task programable. Any other thoughts?
> "HK" wrote:
>
copy[vbcol=seagreen]
Excel,[vbcol=seagreen]
different[vbcol=seagreen]
news:7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...[vbcol=seagreen]
CSV.[vbcol=seagreen]
me[vbcol=seagreen]|||How do you create this DTS Package for exporting into a text file? I would
like to create and copy into a flat text file, but only see Bulk Insert that
will copy into SQL Tables. Could you provide some insight into how to expor
t
using DTS.
Thanks!
"Christian Hamel" wrote:
> Why not simply creating a DTS (or SSIS if you're on 2005) package that wil
l
> create that CSV file? Then it is a one click execute process.
>
> "sorourke1@.hotmail.com" <sorourke1hotmailcom@.discussions.microsoft.com> a
> écrit dans le message de news:
> 7C1D66D1-7038-4084-B139-68D3C7DBB18B@.microsoft.com...
>
>|||Try www.sqlscripter.com to export data to text/csv.
It's free.
"sorourke1@.hotmail.com" wrote:
> I am attempting to exports the results of a stored Procedure into a CSV. T
he
> table is a temp tabel named #FinalPrivate. I need to allow a user to downl
oad
> the file from a .NET Web Application. Is there someone that can point me i
n a
> direction?
Subscribe to:
Posts (Atom)