Showing posts with label building. Show all posts
Showing posts with label building. Show all posts

Thursday, March 22, 2012

Easier way of building pivot tables in MS SQL Server

Dear All

I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.

Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?

I would be very grateful for any help with this.

wujtehacjuszWhat version of SQL Server?

SQL Server 2005 has the PIVOT command.

On Jul 10, 5:19 am, wujtehacjusz <wujtehacj...@.gmail.comwrote:

Quote:

Originally Posted by

Dear All
>
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
>
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
>
I would be very grateful for any help with this.
>
wujtehacjusz

|||For SQL 2000, check out http://www.rac4sql.net/
--
Hope this helps.

Dan Guzman
SQL Server MVP

"wujtehacjusz" <wujtehacjusz@.gmail.comwrote in message
news:1184059198.208520.109590@.q75g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

Dear All
>
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
>
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
>
I would be very grateful for any help with this.
>
wujtehacjusz
>

Wednesday, March 21, 2012

Dynamically Writing & Rendering Report

I have a report which will change almost everytime it is requested. I am
building the query and writing the RDL on the fly. I don't want to save the
RDL to the report server as it will be of no future use. Is there a way to
pass the RDL as perhaps a string or something similar and ask the
ReportServer to render that instead of a saved report.
Many thanks in advance for any help.
SimonThis functionality is not supported in the current release but is on wish
list for a future release. For now, you'll need to publish the report in
order to render it, i.e., call CreateReport() and then call Render(). You
can always delete it once you're done.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote in message
news:%23VX8qckbEHA.2544@.TK2MSFTNGP10.phx.gbl...
> I have a report which will change almost everytime it is requested. I am
> building the query and writing the RDL on the fly. I don't want to save
the
> RDL to the report server as it will be of no future use. Is there a way to
> pass the RDL as perhaps a string or something similar and ask the
> ReportServer to render that instead of a saved report.
> Many thanks in advance for any help.
> Simon
>|||Thanks Ravi,
Thats a bit of pain as I could have 100's if not 1000's of stray reports on
the server that would only ever be used once. I now need to write a cleanup
routine to peridoically remove the unused reports from the server. A majorly
inefficient process but looks like the only option.
Do you have any idea when the next iteration of SQL Reporting is planned for
that will include this functionality?
Simon
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote
> This functionality is not supported in the current release but is on wish
> list for a future release. For now, you'll need to publish the report in
> order to render it, i.e., call CreateReport() and then call Render(). You
> can always delete it once you're done.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services|||The next release of reporting services is in SQL Server 2005. This feature
is on wish list for that release.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Simon Dingley" <newsgroups@.nospam-creativenrg.co.uk> wrote in message
news:ee0IXHxbEHA.796@.TK2MSFTNGP09.phx.gbl...
> Thanks Ravi,
> Thats a bit of pain as I could have 100's if not 1000's of stray reports
on
> the server that would only ever be used once. I now need to write a
cleanup
> routine to peridoically remove the unused reports from the server. A
majorly
> inefficient process but looks like the only option.
> Do you have any idea when the next iteration of SQL Reporting is planned
for
> that will include this functionality?
> Simon
>
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote
> > This functionality is not supported in the current release but is on
wish
> > list for a future release. For now, you'll need to publish the report in
> > order to render it, i.e., call CreateReport() and then call Render().
You
> > can always delete it once you're done.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
>

Sunday, March 11, 2012

dynamically created dataset

I am building a mailing list report.

I have the report all built to display name, address, etc and this works well if i build the dataset in RS.

Problem:
I want to use this same report to build mailing list for any group of people the user selects while using a c# application.

Question:
Is there anyway to build a dataset in an application then send it to RS?

thanks
lucas

Hi,

Starting with SQL Server 2005, Microsoft has added rdlc reports. These reports can be embedded within a C# application by using the reportviewer.

Here is an example: http://msdn2.microsoft.com/en-us/library/ms251724(VS.80).aspx.

References: http://msdn2.microsoft.com/en-us/library/ms251671(VS.80).aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||I've messed around with the rdlc reports w/ the reportviewer but i don't see how that would help me.

the report viewer lets you swap which report is shown in the viewer, but i want to swap the dataset that the report gets its data from.|||What i ended up doing is making a stored procedure that accepted a 'where clause' parameter that was built in the application. i then use the SP as the data source for the report.

IF @.WhereClause + '' <> '' SET @.statement = @.statement + ' AND ' + @.WhereClause|||Several in fact as explained in this article.

dynamically created dataset

I am building a mailing list report.

I have the report all built to display name, address, etc and this works well if i build the dataset in RS.

Problem:
I want to use this same report to build mailing list for any group of people the user selects while using a c# application.

Question:
Is there anyway to build a dataset in an application then send it to RS?

thanks
lucas

Hi,

Starting with SQL Server 2005, Microsoft has added rdlc reports. These reports can be embedded within a C# application by using the reportviewer.

Here is an example: http://msdn2.microsoft.com/en-us/library/ms251724(VS.80).aspx.

References: http://msdn2.microsoft.com/en-us/library/ms251671(VS.80).aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||I've messed around with the rdlc reports w/ the reportviewer but i don't see how that would help me.

the report viewer lets you swap which report is shown in the viewer, but i want to swap the dataset that the report gets its data from.|||What i ended up doing is making a stored procedure that accepted a 'where clause' parameter that was built in the application. i then use the SP as the data source for the report.

IF @.WhereClause + '' <> '' SET @.statement = @.statement + ' AND ' + @.WhereClause|||Several in fact as explained in this article.

Sunday, February 26, 2012

Dynamic USE <YourDBNAME>

I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.

I have a script (or at least I'm trying to have a script) that will create (install) a database for me, then create the tables in the database.

I'm using a variable for the database name because the same database may be used for different projects on the same server. I only wanted to set the name of the database once.

I was hoping that after I created the database that I could somehow tell the script to start using that database.

Use @.mydbname is incorrect and if I use the stored proc to execute dynamically, it is not maintained after executing the proc. (Some silly thing about scope).

Does anyone have any ideas I could use?

ThanksWhere is your script stored? As a file, or as a stored procedure, or in a table?

If you are using an interface, like VB, then the simplest solution is to have the interface execute one script to create the database, switch to the new database, and then execute a second script to create the objects.

If you are running a stored procedure or an sql file, you could package the entire code as dynamic SQL and issue the USE statement in your code. sp_Execute can handle multiple statements, including USE. It is limited in size, I believe, so you would probably have to break your code up by object and inclued the USE statement in each section.

This question has come up before, so you could browse previous answers, but I have to say I have never seen a multi-database design like this that didn't result in an ongoing mess of version discrepancies, data duplication, and fragile patchworks of DTS packages to keep the whole thing moving. You'll probably tell me that this is not an option, but build scalability into your database design from day one and you will avoid a multitude of problems.|||Originally posted by rmillman
I know all about using dynamic queries and building queries on the fly, but I have a problem with trying to dynamically Use a database.

Thanks

I guess, the solution you are looking for is to use dynamic SQL. You will have to use the EXECUTE statement. Paste this code in the query analyzer, and run it:

USE master
GO

DECLARE @.DB AS VarChar(50)
SET @.DB = 'YourNewDB'
EXECUTE('CREATE DATABASE ' + @.DB)
EXECUTE('CREATE TABLE ' + @.DB + '.dbo.YourFirstNewTable(YourFirstCol int)')
-- add your other stuff
GO

Is this you are looking for?|||Originally posted by rmillman
I have a script [...] that will create (install) a database for me, then create the tables in the database.


Just checking: have you tried the -d <dbname> switch in isql ? Just like your sentence above, your batch file would have two parts. First create the database, then swith to it and use it. I would structure the BAT file as follows:

set DBNAME=mydb
isql -Q "create database .%DBNAME% ..." -S ... -U ...

isql -S ... -U ... -d %DBNAME% -i script.sql

The above executes the create database statement on the command line, so you caan substitute the variable. In the second invocation, the "script.sql" is where you create all your objects. This is pure command-line mode, so it is easily repeatable.

Sunday, February 19, 2012

dynamic sql string too long

hello,
i have a stored procedure, which is building a dynamic sql statement with
DECLARE @.myStatement NVARCHAR( 4000 )
SET @.myStatement = 'blablabla'
and than
i do an
EXEC ( @.myStatement )
The problem ist, that the statement now exceeds the maximum length of 4000
nvarchars.
How can I create statements which are longer than 4000?
Switching to varchar is not a good solution.
Currently my statment is about 80.000 characters long!
thank you
michael
Michael Zdarsky"Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
> hello,
> i have a stored procedure, which is building a dynamic sql
statement with
> DECLARE @.myStatement NVARCHAR( 4000 )
> SET @.myStatement = 'blablabla'
> and than
> i do an
> EXEC ( @.myStatement )
> The problem ist, that the statement now exceeds the maximum length
of 4000
> nvarchars.
> How can I create statements which are longer than 4000?
> Switching to varchar is not a good solution.
> Currently my statment is about 80.000 characters long!
> thank you
> michael
Michael Zdarsky,
An eighty thousand character SQL string?
May I ask why it is that long?
If you have SQL Server 2005, you may use NVARCHAR(MAX).
Sincerely,
Chris O.|||Use multiple variables.
DECLARE @.st2 NVARCHAR(4000),
@.st2 NVARCHAR(4000);
EXEC(@.st1+@.st2);
Or, find ways around making such a long string. Why is your query so long?
A massive IN() list? See http://www.aspfaq.com/2248 for some elegant ways
around that.
A
"Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
> hello,
> i have a stored procedure, which is building a dynamic sql statement with
> DECLARE @.myStatement NVARCHAR( 4000 )
> SET @.myStatement = 'blablabla'
> and than
> i do an
> EXEC ( @.myStatement )
> The problem ist, that the statement now exceeds the maximum length of 4000
> nvarchars.
> How can I create statements which are longer than 4000?
> Switching to varchar is not a good solution.
> Currently my statment is about 80.000 characters long!
> thank you
> michael
>
> --
> Michael Zdarsky
>|||Michael Zdarsky (zdarsky_at_zac_dash_it_dot_com) writes:
> i have a stored procedure, which is building a dynamic sql statement with
> DECLARE @.myStatement NVARCHAR( 4000 )
> SET @.myStatement = 'blablabla'
> and than
> i do an
> EXEC ( @.myStatement )
> The problem ist, that the statement now exceeds the maximum length of 4000
> nvarchars.
> How can I create statements which are longer than 4000?
> Switching to varchar is not a good solution.
> Currently my statment is about 80.000 characters long!
80000? Ouch!
Well, you can do
EXEC(@.sql1 + @.sql2 + @.sql3 + ...)
In SQL 2005 you can you use nvarchar(MAX), which makes things a lot simpler.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hello Aaron,
thank you for your answer.
This is a high complex query which is generated depending on user input.
the user input is sort of filter criterion for a 2GB database to search
through
special entries.
You are right with massive in list, but the problem is,
that each in list can be longer than 4000 NVARCHAR, so I have to split the
inlists in different variables.
But the in list problem is only one part of the problem.
The filter is realy complex. I haven't seen more complex queries before.
And the greatest problem is the mass data input in that query by user, throu
gh
selection lists on the UI.
I can not work with ids, because the selected data from the UI can be saved
and reused with a different database.
The id's of the selected data wouldn't match, so I have to use the pure UI
data.
Thanks
Michael
Michael Zdarsky
"Chris2" wrote:

> "Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
> news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
> statement with
> of 4000
> Michael Zdarsky,
> An eighty thousand character SQL string?
> May I ask why it is that long?
> --
> If you have SQL Server 2005, you may use NVARCHAR(MAX).
>
> Sincerely,
> Chris O.
>
>|||Hello Aaron,
thank you for your answer.
This is a high complex query which is generated depending on user input.
the user input is sort of filter criterion for a 2GB database to search
through
special entries.
You are right with massive in list, but the problem is,
that each in list can be longer than 4000 NVARCHAR, so I have to split the
inlists in different variables.
But the in list problem is only one part of the problem.
The filter is realy complex. I haven't seen more complex queries before.
And the greatest problem is the mass data input in that query by user, throu
gh
selection lists on the UI.
I can not work with ids, because the selected data from the UI can be saved
and reused with a different database.
The id's of the selected data wouldn't match, so I have to use the pure UI
data.
Thanks
Michael
--
Michael Zdarsky
"Aaron Bertrand [SQL Server MVP]" wrote:

> Use multiple variables.
> DECLARE @.st2 NVARCHAR(4000),
> @.st2 NVARCHAR(4000);
> EXEC(@.st1+@.st2);
> Or, find ways around making such a long string. Why is your query so long
?
> A massive IN() list? See http://www.aspfaq.com/2248 for some elegant ways
> around that.
> A
>
> "Michael Zdarsky" <zdarsky_at_zac_dash_it_dot_com> wrote in message
> news:367E3B17-2F65-43AC-9D8C-E2F3C0D2542D@.microsoft.com...
>
>|||> You are right with massive in list, but the problem is,
> that each in list can be longer than 4000 NVARCHAR, so I have to split the
> inlists in different variables.
So if you already have them in separate variables, can't you still dump the
list into table form, a la http://www.aspfaq.com/2248 ?
A|||Michael Zdarsky (zdarsky_at_zac_dash_it_dot_com) writes:
> You are right with massive in list, but the problem is,
> that each in list can be longer than 4000 NVARCHAR, so I have to split the
> inlists in different variables.
If your dynamic SQL is only for lists for IN, you are barking up the
wrong tree. Not only is it difficult to manage, you get awful performance.
Have a look at http://www.sommarskog.se/arrays-in-sql.html for several
alternative methods. And you can use ntext for your lists.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
===========
step 0 - declare local variables:
--
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
---
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
----
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
---
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
ReaTry using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam =
MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========
> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> ---
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> ---
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>

Friday, February 17, 2012

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
===========
step 0 - declare local variables:
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
Rea
Try using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam =
MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========
> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
=========== step 0 - declare local variables:
--
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
---
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
----
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
---
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
ReaTry using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam = MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> ---
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> ---
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>

Wednesday, February 15, 2012

Dynamic SQL Concatenation

In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
However, in FIGURE 1, I'm trying to add a WHERE subquery that should return
FIGURE 2's results but only for the last 10 days. I'm getting a strange
error in QA. Can someone help me with my syntax in FIGURE 1 near the ERROR
comment?
-- FIGURE 1:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
int, @.timeID int, @.dtMaxDate datetime
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.timeID = '1'
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
IF @.timeID = 1 -- ERROR IS IN NEXT 2 LINES
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ' +
CAST(@.dtMaxDate AS VARCHAR(55))
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
EXEC(@.SQL)
-- FIGURE 2:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID int
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM
Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE
Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55)) + '
GROUP BY
Customers.CompanyName'What's the error you're getting?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"scott" <sbailey@.mileslumber.com> wrote in message
news:OVCoeAt7FHA.204@.TK2MSFTNGP10.phx.gbl...
> In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
> However, in FIGURE 1, I'm trying to add a WHERE subquery that should
> return FIGURE 2's results but only for the last 10 days. I'm getting a
> strange error in QA. Can someone help me with my syntax in FIGURE 1 near
> the ERROR comment?
> -- FIGURE 1:
> declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
> int, @.timeID int, @.dtMaxDate datetime
> SET @.Country = N'Germany'
> SET @.ShipVia = '1'
> set @.typeID = '1'
> SET @.timeID = '1'
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> SET @.SQL = 'SELECT Customers.CompanyName, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + '
> FROM Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
> IF @.timeID = 1 -- ERROR IS IN NEXT 2 LINES
> SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ' +
> CAST(@.dtMaxDate AS VARCHAR(55))
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
> EXEC(@.SQL)
> -- FIGURE 2:
> declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
> int
> SET @.Country = N'Germany'
> SET @.ShipVia = '1'
> set @.typeID = '1'
> SET @.SQL = 'SELECT Customers.CompanyName, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + '
> FROM
> Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE
> Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55)) + '
> GROUP BY
> Customers.CompanyName'
>|||scott (sbailey@.mileslumber.com) writes:
> In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
> However, in FIGURE 1, I'm trying to add a WHERE subquery that should
> return
> FIGURE 2's results but only for the last 10 days. I'm getting a strange
> error in QA. Can someone help me with my syntax in FIGURE 1 near the ERROR
> comment?
Please, next time include the error message you get.
Here is what I think is suspect:

> CAST(@.dtMaxDate AS VARCHAR(55))
Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
is not subject to different interpretation.
Or even, better throw this code away entirely. I don't know what you
are trying to achieve, but if this is an exercise with dynamic SQL,
please to it the proper way and use sp_executesql instead. Then you
don't have to deal with cast and convert to interpolate variable values.
Please see sp_executesql in Books Online.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '6'.
What's confusing is their is no '6' in my code.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uadSxFt7FHA.2176@.TK2MSFTNGP14.phx.gbl...
> What's the error you're getting?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:OVCoeAt7FHA.204@.TK2MSFTNGP10.phx.gbl...
>|||Below in FIGURE 1 is my code using the new date format. I'm getting the
below error below. Can you help me solve this? I hear what you're saying
about the other method, but want to get this one fixed due to time
constraints.
ERROR:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
-- FIGURE 1:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
int, @.timeID int, @.dtMaxDate datetime
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.timeID = '1'
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE
Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
IF @.timeID = 1
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ' +
convert(char(8), @.dtMaxDate, 112)
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
EXEC(@.SQL)
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715E9F9B8E8FYazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Please, next time include the error message you get.
> Here is what I think is suspect:
>
> Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
> is not subject to different interpretation.
> Or even, better throw this code away entirely. I don't know what you
> are trying to achieve, but if this is an exercise with dynamic SQL,
> please to it the proper way and use sp_executesql instead. Then you
> don't have to deal with cast and convert to interpolate variable values.
> Please see sp_executesql in Books Online.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||CONVERT (varchar(12), @.dtMaxDate, 101) fixed it. thanks for your input.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715E9F9B8E8FYazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Please, next time include the error message you get.
> Here is what I think is suspect:
>
> Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
> is not subject to different interpretation.
> Or even, better throw this code away entirely. I don't know what you
> are trying to achieve, but if this is an exercise with dynamic SQL,
> please to it the proper way and use sp_executesql instead. Then you
> don't have to deal with cast and convert to interpolate variable values.
> Please see sp_executesql in Books Online.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Scott (sbailey@.mileslumber.com) writes:
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '6'.
> What's confusing is their is no '6' in my code.
It's imperative when you work with dynamic SQL to include things like:
IF @.debug = 1 PRINT @.sql
so that you can see what you actually have produced.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns971668A0F963EYazorman@.127.0.0.1...
> It's imperative when you work with dynamic SQL to include things like:
> IF @.debug = 1 PRINT @.sql
How about, instead, using a macro and putting it in debug mode? :)
http://www.datamanipulation.net/tsqlmacro
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

> so that you can see what you actually have produced.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 21 Nov 2005 18:19:56 -0600, Scott wrote:

>CONVERT (varchar(12), @.dtMaxDate, 101) fixed it. thanks for your input.
Hi Scott,
I severely doubt it. You're not getting errors anymore, but your results
are incorrect.
If you had followed Erlands original advice to print the SQL string you
are executing, you'd have seen that your original query created code
like this:
WHERE Orders.OrderDate <= 20051123
Since 20051123 is a numeric and OrderDate is a datetime, SQL Server
attempts to convertthe numeric 20051123 to datetime. Conversion rule
says that the number is the number of days since jan 1st 1900. If I
didn't miscalculate, adding 20,051,123 to the start of 1900 takes us
some 55,000 years in the future - well beyond the maximum capacity of
the datetime datatype. That's whhy you got the error.
Your "fix" now generates code like this:
WHERE Orders.OrderDate <= 11/23/2005
This will first divide 11 by 23 (using integer division, yielding 0),
then divide that by 2005 (result still 0). This will then be converted
to datetime ==> 0 days after jan 1st 1900 is jan 1st 1900. Regardless of
the value of @.dtMaxDate, you'll be comparing to 1900-01-01.
The correct way to pass a date literal is to use the yyyymmdd format,
and to enclose it in single quotes:
WHERE Orders.OrderDate <= '20051123'
Or to fix this particular line of your code:
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ''' +
convert(char(8), @.dtMaxDate, 112) + ''''
(Note that a single quotes within single quotes must be doubled!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks, that fixed it.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:4vd7o19vos75ef7eumvphndgcafspqkjv7@.
4ax.com...
> On Mon, 21 Nov 2005 18:19:56 -0600, Scott wrote:
>
> Hi Scott,
> I severely doubt it. You're not getting errors anymore, but your results
> are incorrect.
> If you had followed Erlands original advice to print the SQL string you
> are executing, you'd have seen that your original query created code
> like this:
> WHERE Orders.OrderDate <= 20051123
> Since 20051123 is a numeric and OrderDate is a datetime, SQL Server
> attempts to convertthe numeric 20051123 to datetime. Conversion rule
> says that the number is the number of days since jan 1st 1900. If I
> didn't miscalculate, adding 20,051,123 to the start of 1900 takes us
> some 55,000 years in the future - well beyond the maximum capacity of
> the datetime datatype. That's whhy you got the error.
>
> Your "fix" now generates code like this:
> WHERE Orders.OrderDate <= 11/23/2005
> This will first divide 11 by 23 (using integer division, yielding 0),
> then divide that by 2005 (result still 0). This will then be converted
> to datetime ==> 0 days after jan 1st 1900 is jan 1st 1900. Regardless of
> the value of @.dtMaxDate, you'll be comparing to 1900-01-01.
>
> The correct way to pass a date literal is to use the yyyymmdd format,
> and to enclose it in single quotes:
> WHERE Orders.OrderDate <= '20051123'
> Or to fix this particular line of your code:
> SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' +
> convert(char(8), @.dtMaxDate, 112) + ''''
> (Note that a single quotes within single quotes must be doubled!)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)