Dynamic report

Hi all,
I want to create a dynamic report by using code, for ex a report with dynamic column , but dont know where to start and which method is best.
Could you show me some examples about this ?
ThanksThere's a dll that I use called p2smon.dll. This dll has 2 functions:

From scr8_ttxado.pdf which can be found by searching http://support.businessobjects.com/search/advsearch.asp

Using Active Data driver functions

The Active Data driver P2smon.dll (Pdsmon.dll for 16-bit) has two functions that create a TTX file based on a recordset that exists in the VB project. This is the preferred method to create a Data Definition file, as the TTX file will always match the recordset (or vice versa). The two functions available in the DLL are:

CreateFieldDefFile() - Creates a TTX file at runtime based on a recordset.

CreateReportOnRuntimeDS() - Creates a TTX file at runtime based on a recordset. A report (RPT) file is also created off the new TTX file, and there is an option to open the RPT file in the Crystal Reports designer.

These functions are purely for development purposes (they are not required at runtime).

NOTE CreateReportOnRuntimeDS( ) does not place any fields on the report. A blank report is created instead.

The function declarations for CreateFieldDefFile() and CreateReportOnRuntimeDS() are as follows:

CreateFieldDefFile()

Declare Function CreateFieldDefFile Lib "p2smon.dll"(lpUnk As Object, ByVal fileName As String, ByVal bOverWriteExistingFile As Long) As Long

Parameter Description

- LpUnk The active data source used to create the field definition file. In C or C++, this is a pointer to an IUnknown derived COM interface relating to a DAO or ADO Recordset. In Visual Basic, this is a Recordset or Rowset object.

- Filename The path and file name of the field definition file to be created.

- bOverWriteExistingFile If a field definition file already exists with the specified path and file name, this flag indicates whether or not to overwrite that file.

CreateReportOnRuntimeDS()

Declare Function CreateReportOnRuntimeDS Lib "p2smon.dll" ( lpUnk As Object, ByVal reportFile As String, ByVal fieldDefFile As String, ByVal bOverWriteFile As Long, ByVal bLaunchDesigner As Long) As Long

Parameter Description

- LpUnk The active data source used to create the field definition file. In C or C++, this is a pointer to an Iunknown derived COM interface relating to a DAO or ADO Recordset. In Visual Basic, this is a Recordset or Rowset object.

- ReportFile The path and file name of the report file to be created.

- FieldDefFile The path and file name of the field definition file to be created.

- BoverWriteFile If a field definition file already exists with the specified path and file name, this flag indicates whether or not to overwrite that file.

- BlaunchDesigner If True (1), Crystal Reports is launched with the newly created report file opened. Crystal Reports must be installed on the system.

NOTE Since a TTX file is a tab-separated text file, it can be manually created or edited using Microsoft Notepad or any other text editor. This method is not recommended for creating TTX files due to possible typing errors.sql

Dynamic refresh of report model in the report builder

Is it possible to dynamically refresh the report model of the report builder?

could it even be using code with any of the interfaces?

When we add a table or add a column to the table in database , will the report model get refreshed automatically or do we need to do it externally. If so, can we use any of the objects and write a custom code in VB.

Please review the following threads:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=363475&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1101825&SiteID=1

Dynamic Quote Problem

I'm trying to insert a quarter and year variable in CODE (BAD) section below
in a dynamic sql statement from Northwind. I'm trying to make CODE (BAD)
look like CODE (GOOD) section. I'm having trouble getting the correct quotes
around the @.YearName and @.QtrName1 part in CODE (BAD) section.
Can someone copy/paste CODE (BAD) section into Northwind and fix quotes?
CODE (BAD)***********************************
**
declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000)
declare @.QtrName1 int, @.QtrName2 int, @.YearName int
set @.QtrName1 = '1'
set @.QtrName2 = '2'
set @.YearName = '1997'
SET @.SQL1 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
''' + '''-''' + ''' + CAST(@.QtrName1 AS VARCHAR(55)) + ''' + ' AS Quarter,
'
SET @.SQL1 = @.SQL1 + 'COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID'
SET @.SQL2 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
''' + '''-''' + ''' + CAST(@.QtrName2 AS VARCHAR(55)) + ''' + ' AS Quarter,
'
SET @.SQL2 = @.SQL2 + 'COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID'
SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2
EXEC(@.SQL)
CODE (GOOD)**********************************
***
SELECT 'Qtr 1997-1' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID
UNION ALL
SELECT 'Qtr 1997-2' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductIDScott wrote:

> I'm trying to insert a quarter and year variable in CODE (BAD) section bel
ow
> in a dynamic sql statement from Northwind. I'm trying to make CODE (BAD)
> look like CODE (GOOD) section. I'm having trouble getting the correct quot
es
> around the @.YearName and @.QtrName1 part in CODE (BAD) section.
> Can someone copy/paste CODE (BAD) section into Northwind and fix quotes?
> CODE (BAD)***********************************
**
> declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000)
> declare @.QtrName1 int, @.QtrName2 int, @.YearName int
> set @.QtrName1 = '1'
> set @.QtrName2 = '2'
> set @.YearName = '1997'
> SET @.SQL1 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
> ''' + '''-''' + ''' + CAST(@.QtrName1 AS VARCHAR(55)) + ''' + ' AS Quarter
,
> '
> SET @.SQL1 = @.SQL1 + 'COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID'
> SET @.SQL2 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
> ''' + '''-''' + ''' + CAST(@.QtrName2 AS VARCHAR(55)) + ''' + ' AS Quarter
,
> '
> SET @.SQL2 = @.SQL2 + 'COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID'
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2
> EXEC(@.SQL)
>
>
> CODE (GOOD)**********************************
***
> SELECT 'Qtr 1997-1' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID
> UNION ALL
> SELECT 'Qtr 1997-2' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID
You don't need dynamic SQL for any of that, so why do it? Extraneous
quotes etc removed from the following. If you quote it up again it
should work. It looks like you've left out WHERE clauses for the
quarters though.
SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
+ CAST(@.qtrname1 AS VARCHAR(55)) AS Quarter,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
INNER JOIN Orders
ON Orders.OrderID = [Order Details].OrderID
UNION ALL
SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
+ CAST(@.qtrname2 AS VARCHAR(55)) AS Quarter,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
INNER JOIN Orders
ON Orders.OrderID = [Order Details].OrderID ;
David Portas
SQL Server MVP
--|||Scott (sbailey@.mileslumber.com) writes:
> I'm trying to insert a quarter and year variable in CODE (BAD) section
> below in a dynamic sql statement from Northwind. I'm trying to make CODE
> (BAD) look like CODE (GOOD) section. I'm having trouble getting the
> correct quotes around the @.YearName and @.QtrName1 part in CODE (BAD)
> section.
Nah, getting order into nested quotes is something I leave as an exercise
to the poor student. :-)
But some hints:
1) The function quotename() can sometimes be handy.
2) If you use SET QUOTED_IDENTIFIER OFF, you can also use " as quote
delimiter. You cannot use this, if there indexed views involved, or
you need to use indexes on computed columns. But it does make
composition of SQL strings easier.
3) There is actually no obligation to do this in T-SQL. After all, building
SQL strings is about string manipulation, and that is not a strong
point of T-SQL. Doing in client code may be better.
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|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> You don't need dynamic SQL for any of that, so why do it? Extraneous
> quotes etc removed from the following. If you quote it up again it
> should work. It looks like you've left out WHERE clauses for the
> quarters though.
David, has it never occurred to you that what you see may only be a piece
of the actual problem? Since I wrote a crosstab query for Scott earlier
this wend, I strongly suspect that his query will include a dynamic
number of UNION ALL things.
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|||it was a simple example without the where. thanks for the extra info.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns972A4E50E881Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> Nah, getting order into nested quotes is something I leave as an exercise
> to the poor student. :-)
> But some hints:
> 1) The function quotename() can sometimes be handy.
> 2) If you use SET QUOTED_IDENTIFIER OFF, you can also use " as quote
> delimiter. You cannot use this, if there indexed views involved, or
> you need to use indexes on computed columns. But it does make
> composition of SQL strings easier.
> 3) There is actually no obligation to do this in T-SQL. After all,
> building
> SQL strings is about string manipulation, and that is not a strong
> point of T-SQL. Doing in client code may be better.
>
>
> --
> 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,
A simple solution to this (which I give at the risk of
encouraging too much dynamic SQL, which can be
downright dangerous) is to use REPLACE, based on
a model query. It's quite easy to set up, and it avoids
most headaches.
declare @.SQL varchar(4000)
set @.SQL = '
SELECT
''Qtr $$@.YearName$$-$$@.QtrName1$$'' AS Qtr,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID
UNION ALL
SELECT
''Qtr $$@.YearName$$-$$@.QtrName2$$'' AS Qtr,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
ON Products.ProductID = [Order Details].ProductID
'
set @.SQL = REPLACE(@.SQL,'$$@.YearName$$',@.YearName)
set @.SQL = REPLACE(@.SQL,'$$@.QtrName1$$',@.QtrName1)
set @.SQL = REPLACE(@.SQL,'$$@.QtrName2$$',@.QtrName2)
The only quotes you have to double in this case are the
few in the model query. If any of the substituted parameters
can contain quotes, you will have to do additional replacements
like
set @.param = replace(@.param,char(39),char(39)+char(39
))
Even other complications, like an unknown number of UNION ALL
clauses, are not so hard to handle this way.
Steve Kass
Drew University
Scott wrote:

>I'm trying to insert a quarter and year variable in CODE (BAD) section belo
w
>in a dynamic sql statement from Northwind. I'm trying to make CODE (BAD)
>look like CODE (GOOD) section. I'm having trouble getting the correct quote
s
>around the @.YearName and @.QtrName1 part in CODE (BAD) section.
>Can someone copy/paste CODE (BAD) section into Northwind and fix quotes?
>CODE (BAD)***********************************
**
>declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000)
>declare @.QtrName1 int, @.QtrName2 int, @.YearName int
>set @.QtrName1 = '1'
>set @.QtrName2 = '2'
>set @.YearName = '1997'
>SET @.SQL1 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
>''' + '''-''' + ''' + CAST(@.QtrName1 AS VARCHAR(55)) + ''' + ' AS Quarter,
>'
>SET @.SQL1 = @.SQL1 + 'COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID'
>SET @.SQL2 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
>''' + '''-''' + ''' + CAST(@.QtrName2 AS VARCHAR(55)) + ''' + ' AS Quarter,
>'
>SET @.SQL2 = @.SQL2 + 'COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID'
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2
> EXEC(@.SQL)
>
>
>CODE (GOOD)**********************************
***
>SELECT 'Qtr 1997-1' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
>FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID
>UNION ALL
>SELECT 'Qtr 1997-2' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
>FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID
>
>|||i realize now that i don't need dynamic sql for this satement, but for
learning reasons, what would be the correct syntax for the year and qtr
concatenation using dynamic sql be?
sql quotes are tricky.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134340924.362906.149210@.g14g2000cwa.googlegroups.com...
> Scott wrote:
>
>
> You don't need dynamic SQL for any of that, so why do it? Extraneous
> quotes etc removed from the following. If you quote it up again it
> should work. It looks like you've left out WHERE clauses for the
> quarters though.
> SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
> + CAST(@.qtrname1 AS VARCHAR(55)) AS Quarter,
> COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories
> INNER JOIN Products
> ON Categories.CategoryID = Products.CategoryID
> INNER JOIN [Order Details]
> ON Products.ProductID = [Order Details].ProductID
> INNER JOIN Orders
> ON Orders.OrderID = [Order Details].OrderID
> UNION ALL
> SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
> + CAST(@.qtrname2 AS VARCHAR(55)) AS Quarter,
> COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories
> INNER JOIN Products
> ON Categories.CategoryID = Products.CategoryID
> INNER JOIN [Order Details]
> ON Products.ProductID = [Order Details].ProductID
> INNER JOIN Orders
> ON Orders.OrderID = [Order Details].OrderID ;
> --
> David Portas
> SQL Server MVP
> --
>|||Erland Sommarskog wrote:
> David, has it never occurred to you that what you see may only be a piece
> of the actual problem? Since I wrote a crosstab query for Scott earlier
> this wend, I strongly suspect that his query will include a dynamic
> number of UNION ALL things.
>
It occurred to me, that's why I asked.
David Portas
SQL Server MVP
--|||Scott (sbailey@.mileslumber.com) writes:
> i realize now that i don't need dynamic sql for this satement, but for
> learning reasons, what would be the correct syntax for the year and qtr
> concatenation using dynamic sql be?
> sql quotes are tricky.
Nah, just overwhelming the more you nest. The basic rule is simple: any
nested quote needs to be doubled.
In addition to everything else, the syntax colouring in Query Analyzer is
helpful here. If an expression like CAST(@.YearName AS VARCHAR(55)) comes out
read, you are an odd number of quotes.
If it is a consolation, also experienced SQL programmers like me have
to fight a battle with all the '. It was oh so easier back in the days
I could use " as well. So that's the real story I don't post any example -
I probably get it wrong. :-)
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.mspxsql

Dynamic Query

I am trying to write some code to go through all the tables in my database
and do a row count. What I have done so far is open a cursor to sysobject
and loop round the tables in it. Then I am building a query in a string
"SELECT COUNT(*) FROM" + tablename . My next step is to EXEC the created
string. The problem is getting the result back from the count, can anybody
tell me how I do this?
thanks
Gav--Try this
Use Northwind
Declare @.sql nvarchar(1000), @.tablename nvarchar(100) ,@.Count int
SET @.tablename = 'Employees'
SET @.sql = 'SELECT @.Count =COUNT(*) FROM ' + @.tablename
exec sp_executeSQL @.sql, N'@.Count int OUTPUT' , @.Count OUTPUT
print @.Count
~Bala|||Select object_name(id), rows from sysindexes where indid<1
Madhivanan|||<balacr@.gmail.com> wrote in message
news:1111658074.290946.83940@.f14g2000cwb.googlegroups.com...
> --Try this
> Use Northwind
> Declare @.sql nvarchar(1000), @.tablename nvarchar(100) ,@.Count int
> SET @.tablename = 'Employees'
> SET @.sql = 'SELECT @.Count =COUNT(*) FROM ' + @.tablename
> exec sp_executeSQL @.sql, N'@.Count int OUTPUT' , @.Count OUTPUT
> print @.Count
> ~Bala
>
Ths was great until I move the code from my Dev box to one of the servers
I want to run this on. Seems the sp_executeSQL does not exist, any ideas
why?
Gav|||Which version of SQL server are you using?
I am not sure whether 'sp_ExecuteSQL' was on SQL 7
Bala|||They are all on the same version SQL 2000 SP3a.. Only difference is the box
I am now trying to run it on is a SAP database server, box I wrote it on was
not.
Gav
<balacr@.gmail.com> wrote in message
news:1111665107.148760.270850@.g14g2000cwa.googlegroups.com...
> Which version of SQL server are you using?
> I am not sure whether 'sp_ExecuteSQL' was on SQL 7
> Bala
>|||Can you tell me the exact error that you are getting?|||Could not find stored procedure 'sp_executeSQL'.
<balacr@.gmail.com> wrote in message
news:1111669257.820312.152460@.l41g2000cwc.googlegroups.com...
> Can you tell me the exact error that you are getting?
>|||My guess is that the server is case sensitive. The name of the procedure is
sp_executesql, not
sp_executeSQL.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:d1ufp2$teb$1@.newsreaderg1.core.theplanet.net...
> Could not find stored procedure 'sp_executeSQL'.
> <balacr@.gmail.com> wrote in message
> news:1111669257.820312.152460@.l41g2000cwc.googlegroups.com...
>|||Spot on. Thanks to everyone for their help. :o)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwvlAeHMFHA.576@.TK2MSFTNGP15.phx.gbl...
> My guess is that the server is case sensitive. The name of the procedure
is sp_executesql, not
> sp_executeSQL.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
> news:d1ufp2$teb$1@.newsreaderg1.core.theplanet.net...
>

Dynamic Proxies - ServiceDescriptionImporter()

Hi,
I have a DPE (data processig extension) that calls a Dynamic Proxy DLL -
this DLL has been declared in a Code Group of the rssrvpolicy.config file -
for developmental purposes, it uses the "FullTrust" PermissionSet and it all
works well ... to a point.
In the dynamic proxy code when I try and instantiate a new instance of the
class: ServiceDescriptionImporter() I get an exception (see below) I'm
stumped as to why I get it.
Anyone got ideas - it seems to be a security related issue as when I run the
report request via the RS Report Designer UI, it works ok.
(btw: the Get Online Help link in the error takes me t a MS page tat tells
me there's no current help available)
thanks,
- Simon
Query execution failed for data set 'MyDataset'. (rsErrorExecutingCommand)
Get Online Help
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet
grants, PermissionSet denied, PermissionSet demands) at
System.RuntimeType.CreateInstanceImpl(Boolean publicOnly) at
System.Activator.CreateInstance(Type type, Boolean nonPublic) at
System.Web.Services.Description.ServiceDescriptionImporter..ctor() at
My.Infrastructure.WebServices.DynamicWebServiceProxy.BuildAssemblyFromWsdl(String
strWsdl) in
d:\development\infrastructure\webservices\dynamicwebserviceproxy\dynamicwebserviceproxy.cs:line 318
Line 318: ServiceDescriptionImporter sdi = new ServiceDescriptionImporter();I discovered that the DPE and the Dynamic Proxy needed to be using the
Permission Set - they werent.
When aligned to use FullTrust, both worked OK

Dynamic order by case expression problem

Hello,
I want to do a dynamic order but with several criterias, my code look like:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price,stock
WHEN @.order = 'S' THEN stock,price
ELSE name,price
END
But it does not work, MSSQL doesn't like to have more than one value for
the order by, the code below works but that not what i want:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END
How can i do ?
ThanksOne method is with multiple CASE expressions in your ORDER BY clause:
SELECT name,price,stock
FROM products
ORDER BY
CASE @.order
WHEN 'P' THEN price
WHEN 'S' THEN stock
ELSE name
END,
CASE @.order
WHEN 'P' THEN stock
WHEN 'S' THEN price
ELSE price
END
Hope this helps.
Dan Guzman
SQL Server MVP
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Hi
IF @.order="P"
SELECT name,price,stock FROM products ORDER BY price,stock
IF @.order="S"
SELECT name,price,stock FROM products ORDER BY stock,price
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Untested:
SELECT name,price,stock FROM products
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END,
CASE WHEN @.order = 'P' THEN stock
WHEN @.order = 'S' THEN price
ELSE price
"Not4u" <Not4u@.chez.com> wrote in message
news:43203431$0$11421$626a14ce@.news.free.fr...
> Hello,
> I want to do a dynamic order but with several criterias, my code look
> like:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||Uri's suggestion might be much better for performance|||Good one Uri and might perform better than the multiple Case.
You just forgot one:
IF @.order not in('S', 'P')
SELECT name,price,stock FROM products ORDER BY name,price
Or he can use Else.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ubd7mXHtFHA.4080@.TK2MSFTNGP12.phx.gbl...
> Hi
> IF @.order="P"
> SELECT name,price,stock FROM products ORDER BY price,stock
> IF @.order="S"
> SELECT name,price,stock FROM products ORDER BY stock,price
>
> "Not4u" <Not4u@.chez.com> wrote in message
> news:43203431$0$11421$626a14ce@.news.free.fr...
>|||Thanks it's work great.
Not4u wrote:
> Hello,
> I want to do a dynamic order but with several criterias, my code look like
:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price,stock
> WHEN @.order = 'S' THEN stock,price
> ELSE name,price
> END
> But it does not work, MSSQL doesn't like to have more than one value for
> the order by, the code below works but that not what i want:
> SELECT name,price,stock FROM products
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END
> How can i do ?
> Thanks|||AK wrote:
> Uri's suggestion might be much better for performance
>
Before discovering the dynamic order by (with case), i used the "IF then"
My select statment is much more complicated than the exemple in this
post and i have multiple order by conditions, the code managing is
easier with the "ORDER BY CASE".
What do you mean by much better performance ?
Thanks|||if at compile time there is an appropriate index, then SQL Server can
satisfy one ORDER BY clause without a sort. If you are specific:
IF @.order="P"
SELECT name,price,stock FROM products ORDER BY price,stock
the optimizer has a better chance to give you a better plan FOR THIS
PARTICULAR BRANCH of your IF statement.
If you are not specific:
ORDER BY
CASE WHEN @.order = 'P' THEN price
WHEN @.order = 'S' THEN stock
ELSE name
END,
the optimizer will utilize "one size fits all" approach, it will always
sort. SQL Server is very good at sorting, but still sorting is not
repeat not free...|||AK wrote:
> if at compile time there is an appropriate index, then SQL Server can
> satisfy one ORDER BY clause without a sort. If you are specific:
> IF @.order="P"
> SELECT name,price,stock FROM products ORDER BY price,stock
> the optimizer has a better chance to give you a better plan FOR THIS
> PARTICULAR BRANCH of your IF statement.
> If you are not specific:
> ORDER BY
> CASE WHEN @.order = 'P' THEN price
> WHEN @.order = 'S' THEN stock
> ELSE name
> END,
> the optimizer will utilize "one size fits all" approach, it will always
> sort. SQL Server is very good at sorting, but still sorting is not
> repeat not free...
>
My request is like this :
SELECT name,
(select min(price) from Price
INNER JOIN Reference ON
Price.id_reference = Reference.id_reference
WHERE Reference.id_product = Products.id_product
) as 'price'
,stock
FROM products
ORDER BY
CASE @.order
WHEN 'P' THEN price
WHEN 'S' THEN stock
ELSE name
END,
CASE @.order
WHEN 'P' THEN stock
WHEN 'S' THEN price
ELSE price
ENDsql

Dynamic Login & User Creation

Hello. I'm trying to create a new login and username inside a trigger using variables.

This code works:

create login testUserName with password = 'testPassword'

The problem comes when I try to use variables for the username and password like this.

create login @.username with password = @.password

Does anybody know how to get around this problem?

BTW, the error message it gives is this, but I really doubt that semicolons have anything to do with it. If I literally type my data in the create login call it works fine, but when I use variables it doesn't.

Msg 102, Level 15, State 1, Line 14

Incorrect syntax near '@.username'.

Msg 319, Level 15, State 1, Line 14

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.

SQL Server doesn't allow you to specify variables in most DDL statements. So the only way is to form the CREATE LOGIN statement as a string and execute it using dynamic SQL. See the EXECUTE topic in Books Online for more details on how to execute SQL statements dynamically.|||

This doesn′t work unless you wrap it in dynamic SQL:

DECLARE @.SQLString VARCHAR(400)
SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + WITH PASSWORD ' + @.PASSWORD
EXEC(@.SQLSTRING)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks a lot! Just in case anybody else has this problem, here's some code that does what I wanted.

declare @.USERNAME varchar(50);

declare @.PASSWORD varchar(50);

set @.USERNAME = 'testUserName2';

set @.PASSWORD = 'testPassword';

DECLARE @.SQLString VARCHAR(400)

SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)

EXEC(@.SQLSTRING)

|||

You need to protect the dynamic SQL against SQL injection attacks. So you need to quote the login name which is an identifier otherwise potentially someone could provide a malicious login name which can be used to do attack the database. The password part is hard to protect since it is just a string. So you will have to validate it for certain characters in the front-end.

declare @.USERNAME varchar(50);

declare @.PASSWORD varchar(50);

set @.USERNAME = quotename('testUserName2'); -- Use quotename to form the identifier

set @.PASSWORD = 'testPassword';

DECLARE @.SQLString VARCHAR(400)

SET @.SQLString = 'CREATE LOGIN ' + @.USERNAME + ' WITH PASSWORD = ' + CHAR(39) + @.PASSWORD + CHAR(39)

EXEC(@.SQLSTRING)

|||Thanks for the tip. We're currently protecting against it on the front end but I recently heard there are steps I can take in the database for protection, so I'll have to look into that. Thanks.

Dynamic Link server

I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from table2 where column2=1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks
|||The problem here is, it is not accepting linked server name in the variable.
DECLARE @.Server as varchar(30)
SET @.Server = 'MyServer'
select * from openquery ( @.Server , 'select * from table1')
is not working.
select * from openquery ( MyServer , 'Select * from table1')
Is working fine.
Rajesh A
+91-9886124372
S7 Software Solutions
"NOTHING IS IMPOSSIBLE. IMPOSSIBLE IT SELF CONTAIN POSSIBLE."
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23BWvov6dIHA.484@.TK2MSFTNGP06.phx.gbl...
Hi
Untested
SELECT * INTO #tbl from openquery (@.ServerName,'select column1 from table2 where column2=1')
IF EXISTS (SELECT * FROM table1 JOIN #tbl ON ........ WHERE......)
BEGIN
END
"RajeshA" <rajeshaz09@.hotmail.com> wrote in message news:AACB61B3-729D-4A2C-8ED5-A0C862CDDD9F@.microsoft.com...
I am receiving link server name as a parameter to my stored procedure. The code looks like
create procedure xxxxxx @.ServerName as char(30) as
begin
if exists ( Select item from table1 join (select * from openquery (@.ServerName,'select column1 from table2 where column2=1')) as b on table1.item = b.column1 where qty > 0 )
begin
print 'IF Body'
end
end
Above code I giving error.
Msg 170, Level 15, State 1, Procedure xxxxxx, Line 3
Line 3: Incorrect syntax near '@.ServerName'.
can't we give dynamic server name in the OpenQuery? Is there any alternative method please suggest me
Thanks

dynamic killer

Hi, guys!

I want to build a scheduled job to kill any connections from server
'WS1187' built by some VB applications. The code reads something like
this:

declare @.id int
begin
set @.id = (select spid from sysprocesses where hostname='WS1187' and
program_name='Visual Basic')
kill @.id
end
GO

It doesn't compile. The error message is
"Server: Msg 170, Level 15, State 1, Line 5
Line 4: Incorrect syntax near '@.id'."

Is there any way we can kill a user process with a dynamic "spid"
built based on certain business rules?

Thanks in advance.

Gary"Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
news:171bd226.0410072058.4d1b0ab0@.posting.google.c om...
> Hi, guys!
> I want to build a scheduled job to kill any connections from server
> 'WS1187' built by some VB applications. The code reads something like
> this:
> declare @.id int
> begin
> set @.id = (select spid from sysprocesses where hostname='WS1187' and
> program_name='Visual Basic')
> kill @.id
> end
> GO
> It doesn't compile. The error message is
> "Server: Msg 170, Level 15, State 1, Line 5
> Line 4: Incorrect syntax near '@.id'."
> Is there any way we can kill a user process with a dynamic "spid"
> built based on certain business rules?
> Thanks in advance.
> Gary

Check the syntax for KILL in BOL, and you'll see that it doesn't allow a
variable for the SPID, so you need to use dynamic SQL:

exec('kill ' + convert(varchar, @.id))

Simon

Dynamic Index Drop and Create

I am working on a data warehouse that is SQL Server 2000 based and what
I need to do is create two stored procs that do the following psuedo
code. I can write it myself but if someone has already written it then
why write it again or if you know of solutions that are close to what I
want that would be a good start. The reason that these need to be in
two seperate procs is that I want to seperate out the Drop and Creates
from the stored procedure that is loading the table. Instead of
hardcoding the drops and creates I want this procedure to be more
dynamic so that as we add indexes for tunning we don't have to maintain
our stored procedures.
proc_DropIndexes @.DatabaseName, @.TableName
- Write all necessary current index information to a work table for
@.DatabaseName and @.TableName to allow for recreation
- Drop all current indexes for @.DatabaseName and @.TableName
proc_CreateIndexes @.DatabaseName, @.TableName
- Read all necessary current index information from a work table for
@.DatabaseName and @.TableName to allow for recreation
- Create all indexes for @.DatabaseName and @.TableName
Thanks,
SpencerThe following appear to be good starts...
http://www.code-magazine.com/articleprint.aspx?quickid=0301101&printmode=true
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/1f4dd406fa18d676/8b9cc0d4526e1913?lnk=st&q=scripting+sql+server+indexes+create+drop&rnum=16&hl=en#8b9cc0d4526e1913

Dynamic formatting

Can someone please provide me some pointers on dynamic formatting. How
can I change the formatting information from code.
Where on SQL Books online can I get information on dynamic formatting
Thanks
KarenOn Jul 6, 10:03 am, KarenM <karenmiddl...@.yahoo.com> wrote:
> Can someone please provide me some pointers on dynamic formatting. How
> can I change the formatting information from code.
> Where on SQL Books online can I get information on dynamic formatting
> Thanks
> Karen
I'm not sure that I understand your question; however, you can select
a field in Layout view -> select F4 (for the Properties window) ->
select <Expression...> to the right of Format and you can enter an
expression w/an if, choice or switch statement based on the value in
the field, etc. Here are a couple of examples.
=iif(CStr(Fields!FieldName.Value) Like "*.*", "$#,0.00", "#,0") -or-
something like
=iif(Fields!FieldName.Value < 0, "(#,0)", "#,0") -or- something like
=switch(CStr(Fields!FieldName.Value) Like "*.*", "$#,0.00", Fields!
FieldName.Value < 0, "(#,0)", true, "#,0")
Also, this link might be helpful.
http://msdn2.microsoft.com/en-us/library/ms157328.aspx
Regards,
Enrique Martinez
Sr. Software Consultant

Dynamic For Loop

My below code returns a union fine in northwind. However, I'd like add some
FOR LOOP's while building the SELECT statements to cut down on the redundant
code. As you can see, each @.SQLx statement has a variable from 1 to 3. I
created this exmple to get an idea of using a FOR LOOP to cut down on code.
CODE:
declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000), @.SQL3
varchar(4000)
declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
@.dtEndDate1 datetime
declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
datetime, @.dtEndDate3 datetime
declare @.typeID int, @.debug int
set @.typeID = '1'
set @.dtStartDate1='19960101'
set @.dtEndDate1='19961231'
set @.dtStartDate2='19970101'
set @.dtEndDate2='19971231'
set @.dtStartDate3='19980101'
set @.dtEndDate3='19981231'
set @.dtYear1='1996'
set @.dtYear2='1997'
set @.dtYear3='1998'
SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
set @.debug = 0
IF @.typeID = 1
BEGIN
SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
END
IF @.typeID = 2
BEGIN
SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
END
SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate1, 112) + ''''
SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate2, 112) + ''''
SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate3, 112) + ''''
SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
IF @.debug = 1
PRINT @.SQL
ELSE
EXEC(@.SQL)Scott
SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
See soem examples
DECLARE @.i INT
SET @.i=1
WHILE @.i<100
BEGIN
INSERT INTO Table VALUES (@.i)
SET @.i=@.i+1
END
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
> My below code returns a union fine in northwind. However, I'd like add
> some FOR LOOP's while building the SELECT statements to cut down on the
> redundant code. As you can see, each @.SQLx statement has a variable from 1
> to 3. I created this exmple to get an idea of using a FOR LOOP to cut down
> on code.
> CODE:
> declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000),
> @.SQL3 varchar(4000)
> declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
> @.dtEndDate1 datetime
> declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
> datetime, @.dtEndDate3 datetime
> declare @.typeID int, @.debug int
> set @.typeID = '1'
> set @.dtStartDate1='19960101'
> set @.dtEndDate1='19961231'
> set @.dtStartDate2='19970101'
> set @.dtEndDate2='19971231'
> set @.dtStartDate3='19980101'
> set @.dtEndDate3='19981231'
> set @.dtYear1='1996'
> set @.dtYear2='1997'
> set @.dtYear3='1998'
> SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
> SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
> SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
> set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
> set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
> set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
> set @.debug = 0
> IF @.typeID = 1
> BEGIN
> SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
> END
> IF @.typeID = 2
> BEGIN
> SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
> SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
> SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
> END
> SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate1, 112) + ''''
> SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate2, 112) + ''''
> SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate3, 112) + ''''
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
> IF @.debug = 1
> PRINT @.SQL
> ELSE
> EXEC(@.SQL)
>|||A better way would be something like this:
SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
MAX(O.Freight) AS maxoffreight
FROM Customers AS C
JOIN Orders AS O
ON C.customerid = O.customerid
WHERE O.orderdate BETWEEN @.startdate AND @.enddate
GROUP BY YEAR(O.orderdate) ;
David Portas
SQL Server MVP
--|||I guess what I'm asking is there a way in SQL to create dynamic variables?
So for example, my code would loop 3 times and each time my variables like
@.dtStartDate1 would increase from @.dtStartDate1 to @.dtStartDate2 and then
@.dtStartDate3.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aBO7zFBGHA.2512@.TK2MSFTNGP09.phx.gbl...
> Scott
> SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
> See soem examples
> DECLARE @.i INT
> SET @.i=1
> WHILE @.i<100
> BEGIN
> INSERT INTO Table VALUES (@.i)
> SET @.i=@.i+1
> END
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
>|||I would do it like that except we have custom year begin and end dates and i
didn't want to take time to write a custom function.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134997569.867513.55250@.f14g2000cwb.googlegroups.com...
>A better way would be something like this:
> SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
> MAX(O.Freight) AS maxoffreight
> FROM Customers AS C
> JOIN Orders AS O
> ON C.customerid = O.customerid
> WHERE O.orderdate BETWEEN @.startdate AND @.enddate
> GROUP BY YEAR(O.orderdate) ;
> --
> David Portas
> SQL Server MVP
> --
>|||scott wrote:
> I would do it like that except we have custom year begin and end dates and
i
> didn't want to take time to write a custom function.
>
Are your annual begin and end dates fixed in the Calendar year or are
they based on some specific non-Gregorian calendar rules? If it's a
Calendar year, just use DATEADD to offset the date by the required
number of days. If it's specific company calendar logic, create a
calendar table and populate it with the information about your
customer's calendar. Join the calendar table into the query. Either
way, you don't need dynamic SQL.
David Portas
SQL Server MVP
--

Dynamic derivation of Heading - is it possible

Hi
I have a query which produces effectively a pivottable. Is there any way I can dynamically assign the column headings ie the code on each line after AS rather than hard coded as I have currently

Extract of Current SP

CREATE PROC dbo.FairValeSummaryPivot
@.BatchRunID INT
AS
SET NOCOUNT ON
SELECT
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn1 - Tn0' ,
MIN(CASE WHEN Tn = '0' THEN PVBalance END) AS 'Tn0 - Tn-1' ,
MIN(CASE WHEN Tn = '-1' THEN PVBalance END) AS 'Tn-1 - Tn-2',
MIN(CASE WHEN Tn = '-2' THEN PVBalance END) AS 'Tn-2 - Tn-3',
MIN(CASE WHEN Tn = '-3' THEN PVBalance END) AS 'Tn-3 - Tn-4',
MIN(CASE WHEN Tn = '-4' THEN PVBalance END) AS 'Tn-4 - Tn-5',
-- and so on
FROM FVSummary
WHERE BatchRunID = @.BatchRunID
GO

what I would like would be along the lines of

MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn' + Tn + ' - Tn' + Tn-1, ,

Hope this is clear
CheersDynamic SQL is all that comes to my mind from the SQL perspective, but this is really a presentation issue so I think it should be handled at the client rather than in the SQL itself.

-PatP|||Dynamic SQL is the only method of assigning variable column headers. But I would discourage you from doing this because no reporting application (Crystal, Access, Active Reports...) is going to be able to deal with output that has a different layout for each result set.
There is (almost) never a good reason for doing what you are trying to do, and in essence that is why it is difficult to do.|||Do a google on ags crosstab. Also (if it's still out there) RAC for SQL.

Regards,

hmscott

dynamic declaration of table type -- any solution?

We got to build a table dynamically using the following code,
====================================
declare @.Part int
declare @.examId int
declare @.tSQL varchar(2000)
declare @.colname varchar(100)
select @.Part = 6

select @.tSQL = 'declare @.Report table ('
Declare mycolumns cursor for
SELECT SubjectCode FROM tblSubjectsMaster WHERE (Part = @.Part) order by SubjectName
OPEN mycolumns
FETCH NEXT FROM mycolumns
INTO @.colname
WHILE @.@.FETCH_STATUS = 0
BEGIN
Select @.tSQL = @.tSQL + @.colname + ' varchar(100),'
FETCH NEXT FROM mycolumns
INTO @.colname
END
CLOSE mycolumns
DEALLOCATE mycolumns
Select @.tSQL = @.tSQL + ' Total int, Result varchar(200), Place int)'
exec(@.tSQL)
==================================
Then in the next line, I'm giving the following line.
select * from @.Report
which is gioving the following error
======================
Must declare the variable '@.Report'.

Assumption is that there is some scope related problem...But the requirement is demanding to think in this lines.

Any body of any guess on any resolution.

Thanks in advance.

Thanks & Regards
Srinivasa ReddyHi,

A table variable just exists until a go or exec command are executed. You have to create a temp table.

/Mats

Dynamic DataSource

How do I change a DataSource on the fly. Do I make the Data Source and the Catalog values parameters in the report then in code feed the parms in like you would a other report parms or in code just make a new definition then call rs2005.SetDataSourceContents(reportPath, definition);?
I have had trouble finding help on this subject and would appreciate knowing how you made it work or a link to a useful help doc.
Thanks
-JWIf you're talking about a report you intend to publish to the report server, the way to do this is:
1) create a static report specific data source (specify the connection string explicitly). Do not use a shared data source reference!
2) build your report as you normally would
3) test that it works :-)
4) change the connection string in your report specific data source to be an expression.

For example, if you are using SQL Server 2005 as your data source:
Original: data source=localhost\instanceName; initial catalog=AdventureWorks
Expression Based: ="data source=" + Parameters!P1.value + "; initial catalog=" + Parameters!P2.value

You might need to add quotes if your catalog name has spaces. You can use either parameters or an expression. For, example you might have a function you define in your report that looks up the right database for a given user:
="data source=" + Parameters!P1.value + "; initial catalog=" + Code.LookUpDatabaseForUser(Globals!UserID)

The variations on this theme are endless. You might use a different database if you have a different language to get the right group names, etc.

The thing to note is that the databases all have to have the same schema so that your query works.

Of course, you could then make you query to be expression based... but that's adding a whole lot of complexity and should be considered only if you really need it for your report.

-Lukasz|||Thank You|||

do you have a sample for rs2000? Thanks.

|||Expression based connection strings are new in RS 2005.

Thanks
Tudor

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
peleg
On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
.....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com
|||thnaks alot
"amish" wrote:

> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>
|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
>
>
>
>
>
> - Show quoted text -
:-)

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
pelegOn Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com|||thnaks alot
"amish" wrote:

> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
>
>
>
>
>
>
>
>
>
> - Show quoted text -
:-)

Dynamic cursor variable

i have this code in which i define a @.TempTableCursor
but is there a way that the TargetTable will be
TargetTable1,TargetTable2,..TargetTable(i)
so when i define set the @.TempTableCursor it will have in the
defenition the TargetTable with a dynamic changing number?
[code]
Declare @.TempTableCursor cursor
Set @.TempTableCursor = Cursor Local FAST_FORWARD
For Select * From TargetTable
[/code]
thnaks in advance
pelegOn Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> i have this code in which i define a @.TempTableCursor
> but is there a way that the TargetTable will be
> TargetTable1,TargetTable2,..TargetTable(i)
> so when i define set the @.TempTableCursor it will have in the
> defenition the TargetTable with a dynamic changing number?
> [code]
> Declare @.TempTableCursor cursor
> Set @.TempTableCursor = Cursor Local FAST_FORWARD
> For Select * From TargetTable
> [/code]
> thnaks in advance
> peleg
declare @.sql nvarchar(4000)
declare @.table varchar(100)
set @.table = 't'
set @.sql = N'
set @.cur = cursor for
select
* from ' + @.table + '; open @.cur'
exec sp_executesql @.sql, N'@.cur cursor output', @.cur
output
if cursor_status('variable', '@.cur') = 1
begin
.....................
.....................
end
if cursor_status('variable', '@.cur') >= 0
close @.cur
deallocate @.cur
Regards
Amish Shah
http://shahamish.tripod.com|||thnaks alot
"amish" wrote:
> On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> > i have this code in which i define a @.TempTableCursor
> > but is there a way that the TargetTable will be
> > TargetTable1,TargetTable2,..TargetTable(i)
> > so when i define set the @.TempTableCursor it will have in the
> > defenition the TargetTable with a dynamic changing number?
> > [code]
> > Declare @.TempTableCursor cursor
> > Set @.TempTableCursor = Cursor Local FAST_FORWARD
> > For Select * From TargetTable
> > [/code]
> >
> > thnaks in advance
> > peleg
> declare @.sql nvarchar(4000)
> declare @.table varchar(100)
> set @.table = 't'
> set @.sql = N'
> set @.cur = cursor for
> select
> * from ' + @.table + '; open @.cur'
>
> exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> output
>
> if cursor_status('variable', '@.cur') = 1
> begin
> .....................
> .....................
> end
>
> if cursor_status('variable', '@.cur') >= 0
> close @.cur
>
> deallocate @.cur
> Regards
> Amish Shah
> http://shahamish.tripod.com
>|||On Aug 2, 5:38 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> thnaks alot
>
> "amish" wrote:
> > On Aug 2, 3:44 pm, pelegk1 <pele...@.discussions.microsoft.com> wrote:
> > > i have this code in which i define a @.TempTableCursor
> > > but is there a way that the TargetTable will be
> > > TargetTable1,TargetTable2,..TargetTable(i)
> > > so when i define set the @.TempTableCursor it will have in the
> > > defenition the TargetTable with a dynamic changing number?
> > > [code]
> > > Declare @.TempTableCursor cursor
> > > Set @.TempTableCursor = Cursor Local FAST_FORWARD
> > > For Select * From TargetTable
> > > [/code]
> > > thnaks in advance
> > > peleg
> > declare @.sql nvarchar(4000)
> > declare @.table varchar(100)
> > set @.table = 't'
> > set @.sql = N'
> > set @.cur = cursor for
> > select
> > * from ' + @.table + '; open @.cur'
> > exec sp_executesql @.sql, N'@.cur cursor output', @.cur
> > output
> > if cursor_status('variable', '@.cur') = 1
> > begin
> > .....................
> > .....................
> > end
> > if cursor_status('variable', '@.cur') >= 0
> > close @.cur
> > deallocate @.cur
> > Regards
> > Amish Shah
> >http://shahamish.tripod.com- Hide quoted text -
> - Show quoted text -
:-)

Dynamic Cursor

I am trying to use a dynamic cursor in a stored procedure:
The code looks like this :

/************************************************** ***
set @.sFormula = 'Monthlyformula'
set @.sStartDate = '02/01/2004'
set @.sEndDate = '02/01/2004'

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @.sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '+ @.sStartDate +' and '+ @.sEndDate +')' )
/************************************************** ***

And this is what it is interpreting

select populateid From appgridrows where histdisplaygrid = 3 And Monthlyformula Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between 02/01/2004 and 02/01/2004)

My problem is Is there anyway that I can put the quotes before those dates('02/01/2004') so that my cursor has some records returned

Thanks in advance

SKwhat happens if you try

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @.sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '''+ @.sStartDate +''' and '''+ @.sEndDate +''')' )

or is it

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @.sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between ''+ @.sStartDate +'' and ''+ @.sEndDate +'')' )|||Dynamic SQL AND a Cursor..

Johnny...tell him/her they've won...

Is this inside a sproc?

What are you ultimatley trying to do?

In other words, what action is applied to the cursor rows?|||Originally posted by Paul Young
what happens if you try

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @.sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '''+ @.sStartDate +''' and '''+ @.sEndDate +''')' )

or is it

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @.sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between ''+ @.sStartDate +'' and ''+ @.sEndDate +'')' )

Either of the Methods deosnt work|||I am trying to get records from a sql into cursor, in which the sql is determined by the input parameters for that SP. Thats why the sql is determined dynamically

Thanks

Originally posted by Brett Kaiser
Dynamic SQL AND a Cursor..

Johnny...tell him/her they've won...

Is this inside a sproc?

What are you ultimatley trying to do?

In other words, what action is applied to the cursor rows?

Dynamic Cross-Tab Query too long?

I am using the Dynamic Cross-Tab code supplied in an article from SQL
Server Magazine (http://www.winnetmag.com/SQLServer/...608/15608.html).
I modified the script to generate a temp table inside the stored
procedure, and then use this temp table as the source for the
cross-tab. However, the problem seems to be that the dynamic SQL
string generated by the script is longer than what can be stored in
the @.SQL variable. The Cross-tab works great, so long as the amount of
data to be pivoted is small.

Is there any way around this? E.g. a User defined type, or another
data type which can store more characters?

Thanks,

Tim

CREATE procedure CBN_CrossTab

@.StudyID varchar(100), --Model ID passed from web app - Only one model
can be selected
@.Level int --The level to which the taxonomy should be rolled up

As

DECLARE

@.Table as sysname, --Table to crosstab
@.OnRows as nvarchar(128), --Groupuing key values (on rows)
@.OnRowsAlias as sysname, --Alias for grouping cloumn
@.OnCols as nvarchar(128), --destination columns (on columns)
@.SumCol as sysname, --data cels
@.SQL AS varchar(8000), -- String to hold generated SQL String
@.NEWLINE as char(1) --Holds the New Line Character for the code

SET @.OnRowsAlias = Null
SET @.SumCol = Null
SET @.NEWLINE = CHAR(10)

-- Generate the Temp table for the taxa and counts
CREATE TABLE #RefOrganisms (sampleid int, txtTaxa varchar(75),
fltCount float)

INSERT INTO #RefOrganisms(sampleid, txtTaxa, fltCount)

SELECT dbo.tblsampledata.sampleid,
dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @.Level, " ") AS Taxa,
SUM(dbo.tblbenthic.[count] /
dbo.tblsitedetail.numberofreps) AS SumCount
FROM dbo.tblstudylist INNER JOIN
dbo.tblsite ON dbo.tblstudylist.studyid =
dbo.tblsite.study_id INNER JOIN
dbo.tblsitedetail ON dbo.tblsite.siteid =
dbo.tblsitedetail.site_id INNER JOIN
dbo.tblsampledata ON
dbo.tblsitedetail.sitedetailsid = dbo.tblsampledata.sitedetails_id
INNER JOIN
dbo.tblbenthic ON dbo.tblsampledata.sampleid =
dbo.tblbenthic.sample_id INNER JOIN
dbo.iter_intlist_to_table(@.StudyID) i ON
dbo.tblstudylist.studyid = i.number INNER JOIN
dbo.tblbenthictaxa ON dbo.tblbenthic.organism_tsn =
dbo.tblbenthictaxa.tsn
WHERE (dbo.tblsampledata.qaqc = 0) AND (dbo.tblsampledata.status =
2) AND (dbo.tblbenthictaxa.rank_id >= @.Level)
GROUP BY
dbo.tblsampledata.sampleid,
dbo.CBN_RecursTaxa(dbo.tblbenthic.organism_tsn, @.Level, " ")

-- Identify the Temp table info for the CrossTab

SELECT @.Table = '#RefOrganisms'
SELECT @.OnRows = 'sampleid'
SELECT @.OnCols = 'txtTaxa'
SELECT @.OnRowsAlias = Null
SELECT @.SumCol = 'fltCount'

--STEP1 BEGININNING OF SQL STRING
SET @.sql = 'SELECT'+ @.newline +' '+ @.onrows +
CASE
WHEN @.ONROWSALIAS IS NOT NULL THEN ' AS ' + @.ONROWSALIAS
ELSE ''
END

CREATE TABLE #KEYS(KEYVALUE NVARCHAR(100)NOT NULL PRIMARY KEY)

DECLARE @.KEYSSQL AS VARCHAR (1000)

SET @.KEYSSQL = 'INSERT INTO #KEYS ' + 'SELECT DISTINCT CAST(' +
@.ONCOLS + '
AS NVARCHAR(100)) ' + 'FROM ' + @.TABLE
EXEC (@.KEYSSQL)

DECLARE @.KEY AS NVARCHAR(100)

SELECT @.KEY = MIN(KEYVALUE) FROM #KEYS

WHILE @.KEY IS NOT NULL
BEGIN
SET @.SQL = @.SQL + ' ,'+ @.NEWLINE +
' SUM(CASE CAST(' + @.ONCOLS +
' AS NVARCHAR(100))' + @.NEWLINE +
' WHEN N''' + @.KEY +
''' THEN '+ CASE
WHEN @.SUMCOL IS NULL THEN '1'
ELSE @.SUMCOL
END + @.NEWLINE +
' ELSE 0' + @.NEWLINE +
' END) AS [' + @.KEY + ']'
SELECT @.KEY = MIN(KEYVALUE) FROM #KEYS
WHERE KEYVALUE > @.KEY
END

SET @.SQL = @.SQL + @.NEWLINE +
'FROM ' + @.TABLE + @.NEWLINE +
'GROUP BY ' + @.ONROWS + @.NEWLINE +
'ORDER BY ' + @.ONROWS

PRINT @.SQL --+ @.NEWLINE --FOR DEBUG
EXEC (@.SQL)
GO"Tim Pascoe" <tim.pascoe@.cciw.ca> wrote in message
news:19555f2b.0402240832.1e138923@.posting.google.c om...
> I am using the Dynamic Cross-Tab code supplied in an article from SQL
> Server Magazine
(http://www.winnetmag.com/SQLServer/...608/15608.html).
> I modified the script to generate a temp table inside the stored
> procedure, and then use this temp table as the source for the
> cross-tab. However, the problem seems to be that the dynamic SQL
> string generated by the script is longer than what can be stored in
> the @.SQL variable. The Cross-tab works great, so long as the amount of
> data to be pivoted is small.
> Is there any way around this? E.g. a User defined type, or another
> data type which can store more characters?
> Thanks,
> Tim

<snip
One possibility is to use several variables, then execute them like this:

EXEC(@.sql1 + @.sql2 + @.sql3 + ...)

See EXECUTE in Books Online for more information. There are data types which
hold more than 8000 characters (text, ntext), but they cannot be used with
EXEC().

Simon