Dynamic WHERE & Dates

My dynamic sql correctly returns the top 10 dates with records if @.timeID
equals 1. I need the @.timeID equals 2 part to return records between
@.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
keep my example simple).
In the below section where @.timeID = 2, I'm getting a syntax error. Can
anyone help with the part below that tests IF @.timeID = 2 ? I just need some
quote help with that part of the where clause.
This runs on Northwind.
CODE ****************************************
***
declare @.SQL varchar(1000), @.typeID int, @.timeID int
declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
set @.typeID = 1
SET @.timeID = 1
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
set @.dtEndDate = @.dtMaxDate
SET @.SQL = 'SELECT Orders.OrderDate, '
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 '
IF @.timeID = 1 /* Return last 10 days */
SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
@.dtMaxDate, 112) + ''''
IF @.timeID = 2 /* should Return records between start and end date */
PRINT 'BELOW CODE GIVES ERROR'
SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
@.dtStartDate + ' AND '
+ CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
EXEC(@.SQL)Basically, I think your dynamic SQL should look like this when done:
=====
'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11),
@.dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' +
CONVERT(CHAR(11), @.dtEndDate) + ''''
=====
From your example below, it looks like the CONVERT portion needs to be
within the quotes and the @.tdStartDate and @.tdEndDates need to be appended
to the query with their values. Also, what date format are you expecting by
converting into CHAR(8)?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott" <sbailey@.mileslumber.com> wrote in message
news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
> In the below section where @.timeID = 2, I'm getting a syntax error. Can
> anyone help with the part below that tests IF @.timeID = 2 ? I just need
> some quote help with that part of the where clause.
> This runs on Northwind.
> CODE ****************************************
***
> declare @.SQL varchar(1000), @.typeID int, @.timeID int
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
> set @.typeID = 1
> SET @.timeID = 1
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
> set @.dtEndDate = @.dtMaxDate
> SET @.SQL = 'SELECT Orders.OrderDate, '
> 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 '
> IF @.timeID = 1 /* Return last 10 days */
> SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
> @.dtMaxDate, 112) + ''''
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
> + CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
> EXEC(@.SQL)
>
>|||Scott
I don't understand why would want to use dynamic sql but this is a fixed
code
-- PRINT 'BELOW CODE GIVES ERROR'
SET @.SQL = @.SQL + 'CONVERT (char(8), Orders.OrderDate, 112) >=''' +
convert(char(8),@.dtStartDate ,112)+ ''' AND '
+ 'CONVERT (char(8), Orders.OrderDate, 112) <='''
+convert(char(8),@.dtEndDate,112)+''''
BTW if IF @.timeID = 1, I got this error
Line 28: Incorrect syntax near '>'.
"Scott" <sbailey@.mileslumber.com> wrote in message
news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
> In the below section where @.timeID = 2, I'm getting a syntax error. Can
> anyone help with the part below that tests IF @.timeID = 2 ? I just need
> some quote help with that part of the where clause.
> This runs on Northwind.
> CODE ****************************************
***
> declare @.SQL varchar(1000), @.typeID int, @.timeID int
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
> set @.typeID = 1
> SET @.timeID = 1
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
> set @.dtEndDate = @.dtMaxDate
> SET @.SQL = 'SELECT Orders.OrderDate, '
> 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 '
> IF @.timeID = 1 /* Return last 10 days */
> SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
> @.dtMaxDate, 112) + ''''
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
> + CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
> EXEC(@.SQL)
>
>|||Scott (sbailey@.mileslumber.com) writes:
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
Weren't you the guy with time constraints? Why then are you wasting the
time with building complete SQL strings, when using sp_executesql is so
much easier?
And why do you insist on not printing out the generated SQL? You could
at least include the generated SQL in your posts, so that it's easier for
people to spot the syntax errors.

> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
You have string concatanated with string, and then there is a comparison
operator in the middle of all this. That will obviously give a syntax
error.
And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in
an WHERE clause. When you put a column in an expression, this prevents
any index from that column from being used. Thus, this can have a serious
impact on performance.
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|||1. I'd really appreciate it if you would expand on your "avoid doing things
like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to pass dates
from ASP to my SPROCs as "11/24/2005". How or what syntax can I use on that
date format to avoid a performance hit?
2. As far as sp_executesql, I wanted to use that method, but the syntax is
more confusing that my method. If I good get some good simple examples, I'd
go that way.
3. My reason for going the way I am is if I hard coded every sql statement
with the matrix of variables I'm sending this SPROC, it would be 1,000 lines
or more. Normally they don't get this out of hand, but this one is for a
graph with lots of options.
4. I'll do better at printing the SQL from now on.
Thanks for any pointers on my date performance issue #1 above.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718610C29F6DYazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> Weren't you the guy with time constraints? Why then are you wasting the
> time with building complete SQL strings, when using sp_executesql is so
> much easier?
> And why do you insist on not printing out the generated SQL? You could
> at least include the generated SQL in your posts, so that it's easier for
> people to spot the syntax errors.
>
> You have string concatanated with string, and then there is a comparison
> operator in the middle of all this. That will obviously give a syntax
> error.
> And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in
> an WHERE clause. When you put a column in an expression, this prevents
> any index from that column from being used. Thus, this can have a serious
> impact on performance.
> --
> 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|||Thanks for the efforts.
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:enDhqcL8FHA.1140@.tk2msftngp13.phx.gbl...
> Basically, I think your dynamic SQL should look like this when done:
> =====
> 'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM
> Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11),
> @.dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' +
> CONVERT(CHAR(11), @.dtEndDate) + ''''
> =====
> From your example below, it looks like the CONVERT portion needs to be
> within the quotes and the @.tdStartDate and @.tdEndDates need to be appended
> to the query with their values. Also, what date format are you expecting
> by converting into CHAR(8)?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>|||Scott (sbailey@.mileslumber.com) writes:
> 1. I'd really appreciate it if you would expand on your "avoid doing
> things like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to
> pass dates from ASP to my SPROCs as "11/24/2005". How or what syntax can
> I use on that date format to avoid a performance hit?
No, you don't have that pass dates that way from ASP. Even more you
shouldn't. That is not even a date as far as I am concerned.
The way to pass data from a client is to use parameters, and let the
the client interpret the date according to the regional setting. The is
then passed to SQL Server as a binary value, as dates are binary values
in SQL Server. (As they are in Windows, by the way.)
I don't do ASP, but in plain Visual Basic it looks something like:
cmd.CommandType = adCmdStoredProcedure
cmd.CommandText = "dbo.some_sp"
cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
And obviously you are doing something like that already, as the date
variables in your example were declared as datetime, not as character.

> 2. As far as sp_executesql, I wanted to use that method, but the syntax
> is more confusing that my method. If I good get some good simple
> examples, I'd go that way.
SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
@.params = N'@.par1 int, @.par2 datetime'
EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
How is this more confusing that trying to get quotes and date formats
correct, something you appear to have big problems with.
As for writing dynamic searches, have you looked at my web site where
I have a longer article on the topic, with both static and dynamic methods?
http://www.sommarskog.se/dyn-search.html. There are also tips for people
that use EXEC().
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|||The part of sp_executesql that gets confusing is the N' syntax. Does N' mean
it's a text type?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718E396131E5Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> No, you don't have that pass dates that way from ASP. Even more you
> shouldn't. That is not even a date as far as I am concerned.
> The way to pass data from a client is to use parameters, and let the
> the client interpret the date according to the regional setting. The is
> then passed to SQL Server as a binary value, as dates are binary values
> in SQL Server. (As they are in Windows, by the way.)
> I don't do ASP, but in plain Visual Basic it looks something like:
> cmd.CommandType = adCmdStoredProcedure
> cmd.CommandText = "dbo.some_sp"
> cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
> And obviously you are doing something like that already, as the date
> variables in your example were declared as datetime, not as character.
>
> SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
> @.params = N'@.par1 int, @.par2 datetime'
> EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
> How is this more confusing that trying to get quotes and date formats
> correct, something you appear to have big problems with.
> As for writing dynamic searches, have you looked at my web site where
> I have a longer article on the topic, with both static and dynamic
> methods?
> http://www.sommarskog.se/dyn-search.html. There are also tips for people
> that use EXEC().
>
> --
> 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 your site in search_orders_1, for example, how does SQL know what
@.xorderid is when you only declare @.orderid at the beginning of the SPROC? I
see @.xorderid listed in the @.paramlist after you use it as a WHERE criteria,
but how does SQL know what @.xorderid is?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718E396131E5Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> No, you don't have that pass dates that way from ASP. Even more you
> shouldn't. That is not even a date as far as I am concerned.
> The way to pass data from a client is to use parameters, and let the
> the client interpret the date according to the regional setting. The is
> then passed to SQL Server as a binary value, as dates are binary values
> in SQL Server. (As they are in Windows, by the way.)
> I don't do ASP, but in plain Visual Basic it looks something like:
> cmd.CommandType = adCmdStoredProcedure
> cmd.CommandText = "dbo.some_sp"
> cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
> And obviously you are doing something like that already, as the date
> variables in your example were declared as datetime, not as character.
>
> SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
> @.params = N'@.par1 int, @.par2 datetime'
> EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
> How is this more confusing that trying to get quotes and date formats
> correct, something you appear to have big problems with.
> As for writing dynamic searches, have you looked at my web site where
> I have a longer article on the topic, with both static and dynamic
> methods?
> http://www.sommarskog.se/dyn-search.html. There are also tips for people
> that use EXEC().
>
> --
> 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|||> The part of sp_executesql that gets confusing is the N' syntax. Does N' me
an it's a text type?
It mean it is Unicode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <sbailey@.mileslumber.com> wrote in message news:ut24UCY8FHA.3952@.TK2MSFTNGP12.phx.g
bl...
> The part of sp_executesql that gets confusing is the N' syntax. Does N' me
an it's a text type?
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9718E396131E5Yazorman@.127.0.0.1...
>

Dynamic Where

Not to familiar with storedprocedures or their ablities. I'm attempting to
shorten syntax of procedure. Previously I had evaluated all the variables
and then set the statement. Is it possible to have static portions and add
to other portions as I have attempted below in where clause?
CREATE PROCEDURE dbo.TaskView
(@.udi int,
@.cName nvarchar(50),
@.cat varchar(100),
@.sdate smalldatetime,
@.fdate smalldatetime,
@.pastdue bit,
@.inactive bit,
@.utype int
)
AS
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid = dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
@.cat) AND (dbo.facility.cNam LIKE @.cName)
if @.pastdue = 1
begin
AND (dbo.task.DueDate < GETDATE())
end
if @.sdate=null or @.fdate=null
begin
AND (dbo.task.DueDate >Getdate()-1)
end
else
begin
AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
end
if @.utype>0
begin
AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
end
else
begin
AND (dbo.utable.udi = @.udi)
end
GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
ORDER BY dbo.task.DueDateI don't know what you mean by:
"Previously I had evaluated all the variables
and then set the statement."
If I understand you correctly then you will need to use dynamic sql but
trust me you don't want to do that.
You could try something like this:
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid =
dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid
INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi =
utable_1.udi
WHERE (dbo.task.inactive = CASE WHEN @.inactive IS NULL THEN
dbo.task.inactive ELSE @.inactive END) etc, etc, etc...
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Nathan Carroll" <nath_an@.thelosth_orizon.com> wrote in message
news:OgljnRX6FHA.1036@.tk2msftngp13.phx.gbl...
> Not to familiar with storedprocedures or their ablities. I'm attempting
> to
> shorten syntax of procedure. Previously I had evaluated all the variables
> and then set the statement. Is it possible to have static portions and
> add
> to other portions as I have attempted below in where clause?
>
> CREATE PROCEDURE dbo.TaskView
> (@.udi int,
> @.cName nvarchar(50),
> @.cat varchar(100),
> @.sdate smalldatetime,
> @.fdate smalldatetime,
> @.pastdue bit,
> @.inactive bit,
> @.utype int
> )
> AS
> SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
> FROM dbo.facility INNER JOIN
> dbo.category ON dbo.facility.fid = dbo.category.fid
> INNER JOIN
> dbo.task ON dbo.facility.fid = dbo.task.fid AND
> dbo.category.caID = dbo.task.caID INNER JOIN
> dbo.response ON dbo.task.tid = dbo.response.tid
> INNER JOIN
> dbo.utable ON dbo.response.udi = dbo.utable.udi
> INNER JOIN
> dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
> JOIN
> dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
> WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
> @.cat) AND (dbo.facility.cNam LIKE @.cName)
> if @.pastdue = 1
> begin
> AND (dbo.task.DueDate < GETDATE())
> end
> if @.sdate=null or @.fdate=null
> begin
> AND (dbo.task.DueDate >Getdate()-1)
> end
> else
> begin
> AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
> end
> if @.utype>0
> begin
> AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
> end
> else
> begin
> AND (dbo.utable.udi = @.udi)
> end
> GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
> ORDER BY dbo.task.DueDate
>|||yes you can did not get your question. You can use th + operator to
concate for dynamic sql
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Dynamic Where

Not to familiar with storedprocedures or their ablities. I'm attempting to
shorten syntax of procedure. Previously I had evaluated all the variables
and then set the statement. Is it possible to have static portions and add
to other portions as I have attempted below in where clause?
CREATE PROCEDURE dbo.TaskView
(@.udi int,
@.cName nvarchar(50),
@.cat varchar(100),
@.sdate smalldatetime,
@.fdate smalldatetime,
@.pastdue bit,
@.inactive bit,
@.utype int
)
AS
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid = dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
@.cat) AND (dbo.facility.cNam LIKE @.cName)
if @.pastdue = 1
begin
AND (dbo.task.DueDate < GETDATE())
end
if @.sdate=null or @.fdate=null
begin
AND (dbo.task.DueDate >Getdate()-1)
end
else
begin
AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
end
if @.utype>0
begin
AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
end
else
begin
AND (dbo.utable.udi = @.udi)
end
GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
ORDER BY dbo.task.DueDate
I don't know what you mean by:
"Previously I had evaluated all the variables
and then set the statement."
If I understand you correctly then you will need to use dynamic sql but
trust me you don't want to do that.
You could try something like this:
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid =
dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid
INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi =
utable_1.udi
WHERE (dbo.task.inactive = CASE WHEN @.inactive IS NULL THEN
dbo.task.inactive ELSE @.inactive END) etc, etc, etc...
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"Nathan Carroll" <nath_an@.thelosth_orizon.com> wrote in message
news:OgljnRX6FHA.1036@.tk2msftngp13.phx.gbl...
> Not to familiar with storedprocedures or their ablities. I'm attempting
> to
> shorten syntax of procedure. Previously I had evaluated all the variables
> and then set the statement. Is it possible to have static portions and
> add
> to other portions as I have attempted below in where clause?
>
> CREATE PROCEDURE dbo.TaskView
> (@.udi int,
> @.cName nvarchar(50),
> @.cat varchar(100),
> @.sdate smalldatetime,
> @.fdate smalldatetime,
> @.pastdue bit,
> @.inactive bit,
> @.utype int
> )
> AS
> SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
> FROM dbo.facility INNER JOIN
> dbo.category ON dbo.facility.fid = dbo.category.fid
> INNER JOIN
> dbo.task ON dbo.facility.fid = dbo.task.fid AND
> dbo.category.caID = dbo.task.caID INNER JOIN
> dbo.response ON dbo.task.tid = dbo.response.tid
> INNER JOIN
> dbo.utable ON dbo.response.udi = dbo.utable.udi
> INNER JOIN
> dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
> JOIN
> dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
> WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
> @.cat) AND (dbo.facility.cNam LIKE @.cName)
> if @.pastdue = 1
> begin
> AND (dbo.task.DueDate < GETDATE())
> end
> if @.sdate=null or @.fdate=null
> begin
> AND (dbo.task.DueDate >Getdate()-1)
> end
> else
> begin
> AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
> end
> if @.utype>0
> begin
> AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
> end
> else
> begin
> AND (dbo.utable.udi = @.udi)
> end
> GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
> ORDER BY dbo.task.DueDate
>
|||yes you can did not get your question. You can use th + operator to
concate for dynamic sql
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Dynamic Where

Not to familiar with storedprocedures or their ablities. I'm attempting to
shorten syntax of procedure. Previously I had evaluated all the variables
and then set the statement. Is it possible to have static portions and add
to other portions as I have attempted below in where clause?
CREATE PROCEDURE dbo.TaskView
(@.udi int,
@.cName nvarchar(50),
@.cat varchar(100),
@.sdate smalldatetime,
@.fdate smalldatetime,
@.pastdue bit,
@.inactive bit,
@.utype int
)
AS
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid = dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
@.cat) AND (dbo.facility.cNam LIKE @.cName)
if @.pastdue = 1
begin
AND (dbo.task.DueDate < GETDATE())
end
if @.sdate=null or @.fdate=null
begin
AND (dbo.task.DueDate >Getdate()-1)
end
else
begin
AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
end
if @.utype>0
begin
AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
end
else
begin
AND (dbo.utable.udi = @.udi)
end
GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
ORDER BY dbo.task.DueDateI don't know what you mean by:
"Previously I had evaluated all the variables
and then set the statement."
If I understand you correctly then you will need to use dynamic sql but
trust me you don't want to do that.
You could try something like this:
SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
FROM dbo.facility INNER JOIN
dbo.category ON dbo.facility.fid =dbo.category.fid
INNER JOIN
dbo.task ON dbo.facility.fid = dbo.task.fid AND
dbo.category.caID = dbo.task.caID INNER JOIN
dbo.response ON dbo.task.tid = dbo.response.tid
INNER JOIN
dbo.utable ON dbo.response.udi = dbo.utable.udi
INNER JOIN
dbo.uFac ON dbo.facility.fid = dbo.uFac.fid
INNER
JOIN
dbo.utable utable_1 ON dbo.uFac.udi =utable_1.udi
WHERE (dbo.task.inactive = CASE WHEN @.inactive IS NULL THEN
dbo.task.inactive ELSE @.inactive END) etc, etc, etc...
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
--|||http://www.sommarskog.se/dyn-search.html
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Nathan Carroll" <nath_an@.thelosth_orizon.com> wrote in message
news:OgljnRX6FHA.1036@.tk2msftngp13.phx.gbl...
> Not to familiar with storedprocedures or their ablities. I'm attempting
> to
> shorten syntax of procedure. Previously I had evaluated all the variables
> and then set the statement. Is it possible to have static portions and
> add
> to other portions as I have attempted below in where clause?
>
> CREATE PROCEDURE dbo.TaskView
> (@.udi int,
> @.cName nvarchar(50),
> @.cat varchar(100),
> @.sdate smalldatetime,
> @.fdate smalldatetime,
> @.pastdue bit,
> @.inactive bit,
> @.utype int
> )
> AS
> SELECT dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101) AS smalldate, dbo.task.tid
> FROM dbo.facility INNER JOIN
> dbo.category ON dbo.facility.fid = dbo.category.fid
> INNER JOIN
> dbo.task ON dbo.facility.fid = dbo.task.fid AND
> dbo.category.caID = dbo.task.caID INNER JOIN
> dbo.response ON dbo.task.tid = dbo.response.tid
> INNER JOIN
> dbo.utable ON dbo.response.udi = dbo.utable.udi
> INNER JOIN
> dbo.uFac ON dbo.facility.fid = dbo.uFac.fid INNER
> JOIN
> dbo.utable utable_1 ON dbo.uFac.udi = utable_1.udi
> WHERE (dbo.task.inactive = @.inactive) AND (dbo.category.Category LIKE
> @.cat) AND (dbo.facility.cNam LIKE @.cName)
> if @.pastdue = 1
> begin
> AND (dbo.task.DueDate < GETDATE())
> end
> if @.sdate=null or @.fdate=null
> begin
> AND (dbo.task.DueDate >Getdate()-1)
> end
> else
> begin
> AND (dbo.task.DueDate BETWEEN @.sdate AND @.fdate)
> end
> if @.utype>0
> begin
> AND (utable_1.udi = @.udi) AND (dbo.utable.utype = @.utype)
> end
> else
> begin
> AND (dbo.utable.udi = @.udi)
> end
> GROUP BY dbo.facility.cNam, dbo.category.Category, dbo.task.tName,
> CONVERT(char(20), dbo.task.DueDate, 101), dbo.task.tid, dbo.task.DueDate
> ORDER BY dbo.task.DueDate
>|||yes you can did not get your question. You can use th + operator to
concate for dynamic sql
--
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/

Dynamic way to change Font, Size, etc.

Hi,
I have one report format for 20 companies. Some of them require differnt
fonts and font sizes. I don't want to duplicate reports because they need
different font format. Is there a dymanic way to change font using one report
only? Thanks.
ChuckThere are two possible ways to do this:
1) Have your font settings in expressions that are populated by report
parameters or data. (It really works!) You can set default parameter
values.
2) Create a process that dynamically updates the RDL (report source) for
each company. This would mean inserting an extra step at either deployment
time or run time.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Chuck" <Chuck@.discussions.microsoft.com> wrote in message
news:3B1DF44A-14C7-4D90-B9D3-A3E67C913E0F@.microsoft.com...
> Hi,
> I have one report format for 20 companies. Some of them require differnt
> fonts and font sizes. I don't want to duplicate reports because they need
> different font format. Is there a dymanic way to change font using one
> report
> only? Thanks.
>
> Chuck|||Now I get some ideas to work on my project. Thanks so much for your reply.
-Chuck
"Jeff A. Stucker" wrote:
> There are two possible ways to do this:
> 1) Have your font settings in expressions that are populated by report
> parameters or data. (It really works!) You can set default parameter
> values.
> 2) Create a process that dynamically updates the RDL (report source) for
> each company. This would mean inserting an extra step at either deployment
> time or run time.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Chuck" <Chuck@.discussions.microsoft.com> wrote in message
> news:3B1DF44A-14C7-4D90-B9D3-A3E67C913E0F@.microsoft.com...
> > Hi,
> >
> > I have one report format for 20 companies. Some of them require differnt
> > fonts and font sizes. I don't want to duplicate reports because they need
> > different font format. Is there a dymanic way to change font using one
> > report
> > only? Thanks.
> >
> >
> > Chuck
>
>|||Another solution without having to add all those Parameters would be to
create a custom class that returns the font and font size etc. Then reference
the class in an expression. I have done this and it works great. Then you
could either distrubute a seperate class for each customer or have the class
read the information in from file and just distrubute a seperate file for
each.
See "Writing Custom Code" in the RS Help
Dave
"Chuck" wrote:
> Hi,
> I have one report format for 20 companies. Some of them require differnt
> fonts and font sizes. I don't want to duplicate reports because they need
> different font format. Is there a dymanic way to change font using one report
> only? Thanks.
>
> Chuck

Dynamic way to change font, font size, etc. in reports

Hi,
I have one report format for many clients. Some of them request different
fonts and font sizes. I don't want to duplicate reports because of differnt
fonts. Is there a dynamic way to change font or font size using only one
report format? Thanks.
ChuckChuck,
Custom code either embedded or as a custom assembly is the answer. This
article is a good starting point. http://odetocode.com/Articles/130.aspx.
Also, refer to the BOL for using custom assemblies.
You can use custom code to set font properties in your report.
"Chuck" wrote:
> Hi,
> I have one report format for many clients. Some of them request different
> fonts and font sizes. I don't want to duplicate reports because of differnt
> fonts. Is there a dynamic way to change font or font size using only one
> report format? Thanks.
>
> Chuck

Dynamic Visibility Problem

I think, first I better explain the characteristic of the report:
? It?s a report of categories and their items(or products)
? the report has a lot of information
? At first only appears the list of all categories(which fulfill all the requirements)
+ Category Nº1
+ Category Nº2
When I clicked, I?ll appear:
Category Nº1
P1
P2
Category Nº2
P2
P4
The problem is, that at first appears 18 pages instead of 1 page, and I don?t know why, I really hope that you can?t help me.
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comSo all your drill down are expanded by default ?
"Rita Fonseca" <ritafonsecasa@.hotmail.com> wrote in message
news:f46b26c3-6b9f-4992-a716-b9cb7e34bc5d@.developmentnow.com...
>I think, first I better explain the characteristic of the report:
> . It's a report of categories and their items(or products)
> . the report has a lot of information
> . At first only appears the list of all categories(which fulfill all the
> requirements)
> + Category Nº1
> + Category Nº2
> When I clicked, I'll appear:
> Category Nº1
> P1
> P2
> Category Nº2
> P2
> P4
>
> The problem is, that at first appears 18 pages instead of 1 page, and I
> don't know why, I really hope that you can't help me.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Your "hidden" should be true then it will hide all the values and on toggle
it comes up.
Amarnath, MCTS
"Rita Fonseca" wrote:
> I think, first I better explain the characteristic of the report:
> â?¢ Itâ's a report of categories and their items(or products)
> â?¢ the report has a lot of information
> â?¢ At first only appears the list of all categories(which fulfill all the requirements)
> + Category Nº1
> + Category Nº2
> When I clicked, Iâ'll appear:
> Category Nº1
> P1
> P2
> Category Nº2
> P2
> P4
>
> The problem is, that at first appears 18 pages instead of 1 page, and I donâ't know why, I really hope that you canâ't help me.
>
> From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com
>|||I have found a kind of answer,
A put -> =FALSE , In the table visibility field
but now the problem is q goes has to leave everything in a single page
I try to used a function --Ceiling-- but doesn?t work, It doesn?t create a break page
From http://www.developmentnow.com/g/115_2007_3_0_0_949975/Dynamic-Visibility-Problem.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||Re: Dynamic Visibility Problem Julien Bonnier
So all your drill down are expanded by default ?
Rpta:
No, not really only is the user wante
From http://www.developmentnow.com/groups/viewthread.aspx?newsgroupid=115&threadid=94997
Posted via DevelopmentNow.com Group
http://www.developmentnow.com

Dynamic views

I havea archive of tables and i want to make a dynamic view for the tables. like a view that shows the last 90 days of data.
is that possible without runnning a script every day or sumthin of that sort?
plz helpI think this will do,

create view myview
as
select * from yourarchivetable where date < dateadd(day, -90, getdate())|||correction,

create view myview
as
select * from yourarchivetable where date > dateadd(day, -90, getdate|||that works ok if the archival table is known. but the archival tables are usally dated (ie name contains the date or year in it) in that case what do u do? the archival tables are monthly tables

say if the view wants last 90 days and today is the 12dec so the view should capture dec, nov, oct, and spet data.

now is this possible ?|||I think what you are looking for here is a partitioned view. It doesn't work exactly the way you want it to, but it should give you what you need with minimal administrative overhead and good performance.

Basically, each of your underlying tables needs to have a check contstraint applied (that should be easy, since you indicate in your post that they are grouped logically by month).

Then you create a view that unions together all the data from the various tables (I'm assuming that the table definitions are the same).

You could probably even figure out a way to programmatically update the view definition on the first day of each month to add the new table (and eliminate the oldest table if you're only going for 90 days).

The check constraints in the underlying tables are important for performance.

Check out Partitioned Views in SQL BOL.

hmscott|||Well rite now i have scripts running running as jobs periodically that keep the views updated for say fiscal year, fiscal calendar year, last 90 days, last 60 days etc.

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?
Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

Dynamic views

Is it possible to use an IF statement or CASE when creating a view?
I want to something like.
IF Myvariable=n
selelect * from mytable where X=n
Else
selelect * from mytable where X=aYou can use CASE in a view but not IF. You cannot use variables or
parameters in views though. Do this in the WHERE clause when you query
the view.
BTW, don't use SELECT * in views. The results can be unreliable if the
base table changes. List all the required columns by name.
David Portas
SQL Server MVP
--|||no. but you can use stored procedure instead
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:

> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||Thanks guys, can I call a SPROC from within a view?
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>|||Nope. If you need a 'parametrized view' you can achieve that by creating a
table function.
What exactly is the purpose of this view?
ML|||> Thanks guys, can I call a SPROC from within a view?
No. A view is no more than a select statement, you can not use variables,
parameters, dml statements other than "select", etc.
Can you tell us what are you trying to accomplish?
AMB
"Geo" wrote:

> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>|||No. You might try using a table-valued function. Table-valued functions
can't call procs either but they do work quite like views and they can
contain procedural code and make use of parameters. See the CREATE
FUNCTION topic in Books Online.
David Portas
SQL Server MVP
--|||Try out UDF returning a TABLE.
UDF will let you pass parameters & can then be used in the FROM clause of a
SELECT statement same way you use tables & views.
Rakesh
"Geo" wrote:

> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||You are missing the concept of a VIEW. It is a virtual table, not a
procedure. Do you expect other tables to change on the fly? And CASE
is an expression, not a statement.|||hi geo,
its the other way around
A stored procedure can call a view
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:

> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>

Dynamic Views

Hi,
I created view like this: "SELECT * FROM TABLE_NAME"
But after change the table structure (adding, deleting, modifiying fields);
when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
table structure.
I always drop and create view after structure change.
Is there any way to create "dynamic" view?Sereza
It is strongly recomended to avoid using SELECT * in the production.
Run sp_refreshview 'view'
"Sergey Amanov" <a@.a.com> wrote in message
news:eyI1mh25EHA.2180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I created view like this: "SELECT * FROM TABLE_NAME"
> But after change the table structure (adding, deleting, modifiying
fields);
> when I use select statement (SELECT * FROM VIEW_NAME) view doesn't see new
> table structure.
> I always drop and create view after structure change.
> Is there any way to create "dynamic" view?
>
>

Dynamic view

Is it possible to create dynamic sql views in Sql Server 2005?

I have a requirement of having to change the Sql View depending on the user selection criteria.
Is it possible?

No but you can create a stored procedure with dynamic sql kludge

If you would change the view (with an alter view statement) what would happen when 2 users would hit the view at the same time (problem)

sp with dynamic sql is your best bet (probably including a temp table, I don't know your requirements so that's tough to answer)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks.

I cannot use stored procedure, any other option to programattically alter the view?|||What is your requirement that means that you have to "dynamically change the view"?|||

Krutika wrote:

Thanks.

I cannot use stored procedure, any other option to programattically alter the view?

No. Well, you can use CASE statements in your view if that helps.|||No, A "view" is always the same list of fields.

Also, if you create a view which says:

CREATE VIEW V_TABLEA AS
SELECT * FROM TABLEA

When you create the view, it caches the list of fields it is going to display. This means if you add or delete a field from TABLEA, these changes will not show in the view. You need to recreate or alter the view to get a new field list.

Your only option is a stored procedure.

Dynamic View

Dear All,
Hoping you might be able to help me out with a SQL issue.
Want to have a view that contains a join of two tables:
SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
Problem is that the ID column exists in both tables (and contains the
same value in both) so it wont work as a view, even though it runs fine
as a query.
We keep on adding columns to the tables, and am sick of having to
remember to redefine the view each time and specify all the columns we
want (real one is much more complex than this one).
Can you suggest a way of creating a dynamic view which returns all the
columns, but only one instance of the ID column?
Have tried -
select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id'
Which will give me a list of all the columns except ID, but the results
are in the form of a recordset. When I try to use this in the view:
SELECT
dbo.VEC_CASE.*,
(select column_name + ', '
from information_schema.columns where table_name = 'vec_mi'
and column_name <> 'id' )
FROM dbo.VEC_MI INNER JOIN
dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
It complains that the subquery returns more than one value.
Is there a way to convert the contents of a recordset into a single
string?
Also tried to create a Stored Procedure / Function to return the
results of the subquery, but cant get the view to recognise the name of
the stored procedure - thinks it is a column.
Also, this whole approach would mean that the design of the view might
change upon execution and so the view might not allow me to do this in
any event.
All assistance gratefully accepted.
Thanks,
Martinjumpa (martin@.jumpa.co.uk) writes:
> Hoping you might be able to help me out with a SQL issue.
> Want to have a view that contains a join of two tables:
> SELECT dbo.VEC_CASE.*, dbo.VEC_MI.*
> FROM dbo.VEC_MI INNER JOIN
> dbo.VEC_CASE ON dbo.VEC_MI.ID = dbo.VEC_CASE.ID
> Problem is that the ID column exists in both tables (and contains the
> same value in both) so it wont work as a view, even though it runs fine
> as a query.
> We keep on adding columns to the tables, and am sick of having to
> remember to redefine the view each time and specify all the columns we
> want (real one is much more complex than this one).
> Can you suggest a way of creating a dynamic view which returns all the
> columns, but only one instance of the ID column?
Keep on adding the columns *that you need* to the view. SELECT * is
generally frowned upon in production code. Say that in five years from
now, someone is looking at the tables and says "hm, I wonder if that
column foo is really used for something real". Well, if SELECT statements
and views only lists columns that are actually used for something, it
can be quite easy to find out, at least if all access is through stored
procedure. But with the SELECT statement like the above, you need to
dive into the client code.
No big deal? There may be a cost for maintaining the value in foo,
and one may consider a redesign that would be a lot easier if we
got forget about foo. But if it's impossible to tell whether foo is
in use, it will have to stay.
And so the system grows, acquiring a bigger and bigger backpack of
legacy, making the system difficult to maintain and evolve.
So keep on adding the columns that are really needed in the view, and
no others.
And, no, there is no "SELECT * - thatcolmn".
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 6 Jun 2006 06:37:44 -0700, jumpa wrote:

>Dear All,
>Hoping you might be able to help me out with a SQL issue.
(snip)
>All assistance gratefully accepted.
Hi Martin,
In addiition to Erland's reply - both Querty Analyzer and SQL Server
Management Studio allow you to quickly copy alll column names of a table
to a query window by using drag & drop from the object explorer. After
that, you'll just have to remove the duplicates and the unneeded
columns, add prefixes, and you're done.
Hugo Kornelis, SQL Server MVP|||Thanks for the responses guys. This is the first time i've ever used a
discussion group for help with development, definitely not the last.
Agreed, will just have to continue with the manual route.
Incidentally, have since written a stored procedure which drops and
recreates the view with all columns.
Many thanks,
Martin

Dynamic Variables?

Hey,

I have two tables of data, table1 and table2. In Table1, there is a
"id" field, a "name" field, and an e-mail field. In table2, there is an
"id" field, and paramters that belong to the certain "id". When I want
to create a new record, I want to make both of the entries at the same
time and have their "id"s match. I thought that the best way of doing
this was to create the data in table1, then get the "id" field of that
entry by way of matching the "name", and then use that id for the "id"
field value for the entry to table2. The problem is this... I don't
know how to collect the value of table1.id and store it in a variable
to be sent with the rest of the data to table2. This is what I tried:

----------

@.name varchar(8000),
@.email varchar(8000)

INSERT INTO table1 (name, email)
VALUES (@.name, @.email)

SELECT id
FROM table1
WHERE table1.name = @.name

----------

>From there, I don't know what to do...SCOPE_IDENTITY function.

See Books Online for details.

--
David Portas
SQL Server MVP
--|||@.name varchar(8000),
@.email varchar(8000)

INSERT INTO table1 (name, email)
VALUES (@.name, @.email)

INSERT INTO table2 (id, p1, p2, p3)
VALUES (@.@.identity, 'val1', 'val2', 'val3')|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. IDENTITY cannot be a relational
key. Have you ever seen a CHAR(8000) name or email address? You will
now!

You need to learn how to design an RDBMS and at least learn to use the
right words. Throw this mess out and start over.|||Celko,

Did you have anything useful to add, or do you just like to hear
yourself talk? I was disapointed that your post didn't include a
shameless plug for your latest book -- although I can't imagine anyone
would pay for the privilege of your abuse when they can get it for free
on the newsgroups.

Dynamic Variables in DTS packages.

Hi,
I have built a DTS package to load a file into the database. I want the
package to be such that it can be run on any database. So, I tried
defining a Global Variable by creating a "Global Variable Task" and
creating a global variable for the "Initial Catalog" Property. But when
I try to run this package from the command line using the dtsrun
utility, the global variable is discarded and data is loaded into the
Database specified when I create the DTS. Is there a way to resolve
this? I also tried assigning it an Environmental Variable instead of
the Global Variable but it didn't work.
Thanks,
KPHi
There is no Global Variables task! Do you have a dynamic properties task to
assign the global variable to the property? Do you have workflow in place to
force the dynamic properties task to execute before the task for which the
properties are changed?
John
"kpraoasp@.yahoo.com" wrote:

> Hi,
> I have built a DTS package to load a file into the database. I want the
> package to be such that it can be run on any database. So, I tried
> defining a Global Variable by creating a "Global Variable Task" and
> creating a global variable for the "Initial Catalog" Property. But when
> I try to run this package from the command line using the dtsrun
> utility, the global variable is discarded and data is loaded into the
> Database specified when I create the DTS. Is there a way to resolve
> this? I also tried assigning it an Environmental Variable instead of
> the Global Variable but it didn't work.
> Thanks,
> KP
>

Dynamic Variables in DTS packages.

Hi,
I have built a DTS package to load a file into the database. I want the
package to be such that it can be run on any database. So, I tried
defining a Global Variable by creating a "Global Variable Task" and
creating a global variable for the "Initial Catalog" Property. But when
I try to run this package from the command line using the dtsrun
utility, the global variable is discarded and data is loaded into the
Database specified when I create the DTS. Is there a way to resolve
this? I also tried assigning it an Environmental Variable instead of
the Global Variable but it didn't work.
Thanks,
KPHi
There is no Global Variables task! Do you have a dynamic properties task to
assign the global variable to the property? Do you have workflow in place to
force the dynamic properties task to execute before the task for which the
properties are changed?
John
"kpraoasp@.yahoo.com" wrote:
> Hi,
> I have built a DTS package to load a file into the database. I want the
> package to be such that it can be run on any database. So, I tried
> defining a Global Variable by creating a "Global Variable Task" and
> creating a global variable for the "Initial Catalog" Property. But when
> I try to run this package from the command line using the dtsrun
> utility, the global variable is discarded and data is loaded into the
> Database specified when I create the DTS. Is there a way to resolve
> this? I also tried assigning it an Environmental Variable instead of
> the Global Variable but it didn't work.
> Thanks,
> KP
>

Dynamic values in parameters

I have 3 parameters which I populate from 3 sprocs. However, I want the
value selected for sproc1 to determine the possible values in sproc2 and the
selected value in sproc2 to determine the value in sprocs3. How do I go
about this?
Thanks
Frank AshleyFrank,
Search for "Cascading Parameters" in Reporting Services books online.
There's an example for T-SQL which should apply to stored procs.
"Frank Ashley" wrote:
> I have 3 parameters which I populate from 3 sprocs. However, I want the
> value selected for sproc1 to determine the possible values in sproc2 and the
> selected value in sproc2 to determine the value in sprocs3. How do I go
> about this?
>
> Thanks
> Frank Ashley
>
>

Dynamic value on Tool tip

I have 2 columns in a table,

one is name and other is percentage,

but in web page(front end), only name column is displayed,

when the cursor is focused on the name, it should display the corresponding percentage for that name. ( Like a tool tip).

can anyone help me on this

Quote:

Originally Posted by sajithamol

I have 2 columns in a table,

one is name and other is percentage,

but in web page(front end), only name column is displayed,

when the cursor is focused on the name, it should display the corresponding percentage for that name. ( Like a tool tip).

can anyone help me on this

Check the following link

http://msdn2.microsoft.com/en-us/library/system.windows.forms.tooltip.settooltip.aspx

Dynamic User Interface to accept values for a Parameterized Report

Hi,
Can any one help me to generate dynamic user interface so that end user
can give inputs to a parameterized report.I am using reporting services and
asp.net.Pls send if any one has code or link.
Thanks in advance
regards
ajay kannaHi,
All you have to do is refer the book "Hitchiker Guides to Reporting Service"
. that has the solution and sample code that you asked for.
let us know how it goes.
Thanks
Bava
"ajay" wrote:
> Hi,
> Can any one help me to generate dynamic user interface so that end user
> can give inputs to a parameterized report.I am using reporting services and
> asp.net.Pls send if any one has code or link.
> Thanks in advance
> regards
> ajay kanna
>|||Can i know how why need a dynamic user interface in the first place. dont you
find the current one provided by report manager is good.
"ajay" wrote:
> Hi,
> Can any one help me to generate dynamic user interface so that end user
> can give inputs to a parameterized report.I am using reporting services and
> asp.net.Pls send if any one has code or link.
> Thanks in advance
> regards
> ajay kanna
>

dynamic use of stored procedure resultset

Hello,

I have a stored procedur like this:

--------------
ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

create TABLE #T1
( column_1 varchar(10) ,
column_2 varchar(100) )

insert into #T1
execute ('execute ' + @.SQLString )

select * from #T1
--------------

The problem is that I want to call different procedures that can give back different columns.
Therefor I would have to define the table #T1 generically.But I don't know how.
Can anyone help me on this problem?

thank you
Werneryou do not need the creation of the temporary table.

ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

execute ('execute ' + @.SQLString )

this solves your problem but this is the most useless SP ever and your going to have stuff like cached execution plans that do not match the query you are executing.|||This sproc is very dangerous. Whoever has rights to execute it has right to execute arbitrary sql scripts, such as "truncate table AllMyCustomers". Do you really want that?

You are asking for trouble if you put this in a production system.|||Hello,

I have a stored procedur like this:

--------------
ALTER PROCEDURE dbo.pdpd_DynamicCall
@.SQLString varchar(4096) = null
AS

create TABLE #T1
( column_1 varchar(10) ,
column_2 varchar(100) )

insert into #T1
execute ('execute ' + @.SQLString )

select * from #T1
--------------

The problem is that I want to call different procedures that can give back different columns.
Therefor I would have to define the table #T1 generically.But I don't know how.
Can anyone help me on this problem?

thank you
Werner
Like jezemine says - dynamic SQL needs to be encapsulated in a very controlled fashion.

I have a similar situation where our "configurator" actually has column names to define a mapping between Inventory and Sales. Long story.

We offer the column names on a drop-down list.

The Stored Procedure that does the implementation is passed column names in various positions. Those column names are a result of a drop-down box (so the can't just formulate their own SQL scripting), and they are of limited size (ie: only big enough for a reasonable column name - like 40 characters).

For your case; if you had a list of column names, types, and sizes rather than the full SQL statement, you could use them to build your temp table.

I have never tried passing an array (or collection) as an SP_ parameter, but that would be ideal if you have an unknown number of columns.

You could also first do some parsing verify that they are valid column names (no spaces or punctuation) to further ensure they aren't passing in DLL commands like "truncate table ...".

So your Stored Procedure would supply all SQL keywords and restrict any from being passed.|||Thank you all for the detailled help!!
Especially the security aspect is a part I have to rethink.

best regards
Werner

Dynamic use of "inserted" and "deleted" in a trigger

Hello,
Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger?
I get an "invalid object name" with these statement.
set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
EXEC sp_executesql @.sqlcmd
Any other suggestions?
Thanks!
Per>'insert mydatabase.dbo.mytable select * from inserted'
what is dynamic here? No need, If i understand correctly
TRY THIS
INSERT INTO mydatabase.dbo.mytable
SELECT <COLUMN LIST AS IN my table order> from inserted
if schema is different or identity is in the mytables, it throws an error.
if you are expecting something else, do post
Regards
R.D
"Per Buus S?rensen" wrote:

> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger
?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||Sorry for the bad example, it should have been:
set @.sqlcmd = 'insert ' + @.db +'.dbo.' + @.table + ' select * from inserted'
EXEC sp_executesql @.sqlcmd
Per
"R.D" wrote:
> what is dynamic here? No need, If i understand correctly
> TRY THIS
> INSERT INTO mydatabase.dbo.mytable
> SELECT <COLUMN LIST AS IN my table order> from inserted
> if schema is different or identity is in the mytables, it throws an error.
> if you are expecting something else, do post
> Regards
> R.D
>
> "Per Buus S?rensen" wrote:
>|||Hi
No you cannot
"Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a
> trigger?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
as short, concise and efficient as possible because they run in a
transaction.
I find that the best way to create generic trigger code is to generate
it semi-automatically at design-time using the information_schema
views. This is very easy to do as long as the trigger code is identical
or similar in each case. If you prefer you can put your common code in
a stored proc and then insert the contents of the Inserted / Deleted
tables into local temporary table(s) that the proc can use.
Hope this helps.
David Portas
SQL Server MVP
--|||As Portas said, you have to use intermediate temp table because each exec
will have its own scope and inserted table is not in exec() scope.
well if you stick to using dynamic sql for peculiar reasons
use this
create table ##mytemptable<data definition same as inserted table)
insert into ##mytemptable select * from inserted
exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
drop table ##mytemptable
--
if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put N'
before string.
Regards
R.D
"Uri Dimant" wrote:

> Hi
> No you cannot
> "Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
> message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
>
>|||The "temp" solution was my first through about a work around, however I dont
know the definition of the source table, since users can change layout from
ERP system, so the trigger should be as general as possiple.
Is it possiple easierly to create a copy of the source table definition as a
temp table?
Per
"R.D" wrote:
> As Portas said, you have to use intermediate temp table because each exec
> will have its own scope and inserted table is not in exec() scope.
> well if you stick to using dynamic sql for peculiar reasons
> use this
> create table ##mytemptable<data definition same as inserted table)
> insert into ##mytemptable select * from inserted
> exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
> drop table ##mytemptable
> --
> if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put
N'
> before string.
> Regards
> R.D
> "Uri Dimant" wrote:
>|||I am aware of the performance issue about such a trigger, but it will be use
d
on smaller master data tables, which updates x other similar tables in other
databases.
Users can setup new databases them self, so flexibility is more important
than performance.
Per
"David Portas" wrote:

> No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
> as short, concise and efficient as possible because they run in a
> transaction.
> I find that the best way to create generic trigger code is to generate
> it semi-automatically at design-time using the information_schema
> views. This is very easy to do as long as the trigger code is identical
> or similar in each case. If you prefer you can put your common code in
> a stored proc and then insert the contents of the Inserted / Deleted
> tables into local temporary table(s) that the proc can use.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Like I suggested before, it sounds like code generation is the way to
go. Much simpler to maintain than dynamic code.
David Portas
SQL Server MVP
--|||I see that the server creates the temp table with correct definition, if it
doesn't exists.
So that solves my problem...so far.
Per
"Per Buus S?rensen" wrote:
> The "temp" solution was my first through about a work around, however I do
nt
> know the definition of the source table, since users can change layout fro
m
> ERP system, so the trigger should be as general as possiple.
> Is it possiple easierly to create a copy of the source table definition as
a
> temp table?
> Per
> "R.D" wrote:
>

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.

Dynamic USE

I want to run a .sql script that updates data from specific tables using
OSQL. The problem i am having is that i need to run the same script on 300
servers. The database naming scheme are as follows:
server 1 : server1_testdb
server 2 : server2_testdb
server 3: server3_testdb
Question:
How can I write one script that can be used for all 300 servers. All
databases have the same structure and are identical except that it resides in
300 servers that is where the prefix (server1_, server2_, etc.) come from.
Does it mean I have to write 300 scripts to specify the database where the
script will be run.
Ex.
script 1: USE server1_testdb
UPDATE tbl_sample ...
script 2: USE server2_testdb
UPDATE tbl_sample ...
script 3: USE server3_testdb
UPDATE tbl_sample ...
Is there a way to employ the 'USE' statement dynamically? Is there a way to
pass a variable to the USE statement such as:
DECLARE @.sampledb sysname
SELECT @.sampledb = master.sysdatabases.name.........
USE @.sampledb
Help highly appreciated.
Looks to me like you should do this with SMO (SQL 2005) or DMO (earlier).
In both cases, your connect strings will determine which server/DB to use.
At that point, it's all SQL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:24723016-1F59-4B31-A779-988ECA6EA444@.microsoft.com...
I want to run a .sql script that updates data from specific tables using
OSQL. The problem i am having is that i need to run the same script on 300
servers. The database naming scheme are as follows:
server 1 : server1_testdb
server 2 : server2_testdb
server 3: server3_testdb
Question:
How can I write one script that can be used for all 300 servers. All
databases have the same structure and are identical except that it resides
in
300 servers that is where the prefix (server1_, server2_, etc.) come from.
Does it mean I have to write 300 scripts to specify the database where the
script will be run.
Ex.
script 1: USE server1_testdb
UPDATE tbl_sample ...
script 2: USE server2_testdb
UPDATE tbl_sample ...
script 3: USE server3_testdb
UPDATE tbl_sample ...
Is there a way to employ the 'USE' statement dynamically? Is there a way to
pass a variable to the USE statement such as:
DECLARE @.sampledb sysname
SELECT @.sampledb = master.sysdatabases.name.........
USE @.sampledb
Help highly appreciated.
|||If you have SQL Server 2005 among your servers and all your servers are
visible from the server with SQL Server 2005 installed use SQLCMD
command-line utility and its features combined with some simple Windows
shell commands.
Create a sql script (save it as script.sql) like:
USE $(dbname)
GO
UPDATE ... -- put your statement here
Create simple Windows batch (save it as batch.bat in the same location as
script.sql) like:
for /L %%n IN (1,1,300) DO sqlcmd -S server%%n -E -v
dbname=server%%n_testdb -i script.sql
Then just run the batch and you should get what you want. I assume that you
have the same Windows login on all SQL Server instances.
Let me know if it works.
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uytkownik "morphius" <morphius@.discussions.microsoft.com> napisa w
wiadomoci news:24723016-1F59-4B31-A779-988ECA6EA444@.microsoft.com...
>I want to run a .sql script that updates data from specific tables using
> OSQL. The problem i am having is that i need to run the same script on
> 300
> servers. The database naming scheme are as follows:
> server 1 : server1_testdb
> server 2 : server2_testdb
> server 3: server3_testdb
> Question:
> How can I write one script that can be used for all 300 servers. All
> databases have the same structure and are identical except that it resides
> in
> 300 servers that is where the prefix (server1_, server2_, etc.) come from.
> Does it mean I have to write 300 scripts to specify the database where the
> script will be run.
> Ex.
> script 1: USE server1_testdb
> UPDATE tbl_sample ...
> script 2: USE server2_testdb
> UPDATE tbl_sample ...
> script 3: USE server3_testdb
> UPDATE tbl_sample ...
> Is there a way to employ the 'USE' statement dynamically? Is there a way
> to
> pass a variable to the USE statement such as:
> DECLARE @.sampledb sysname
> SELECT @.sampledb = master.sysdatabases.name.........
> USE @.sampledb
> Help highly appreciated.
>

DYNAMIC USE

I would need go along the current sql server using a cursor (another
alternatives will be welcomed) and changing of database
Something like that:
DECLARE @.BD AS CHAR(20)
declare cursorbd cursor fast_forward for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status = 0
begin
USE @.BD
GO
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
fetch next from cursorBD into @.BD
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
But it isn't working.
Regards,The GO command signals the end of a batch of T-SQL statements. You put yours
in the middle of what you wanted to be the batch, splitting it in two.
But that's not the real problem. The trouble starts with you considering
using a cursor. You don't need to.
I could very well guess what you're trying to do, but I'd instead prefer you
to tell us.
So, here it goes:
What are you trying to do? Display a list of table names for all your
databases?
ML
p.s. if you answer 'yes' to my last question, we're half way there. :)|||DECLARE @.BD varCHAR(100)
declare @.string nvarchar(100)
set @.bd=3D''
declare cursorbd cursor for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT
IN('MASTER','PUBS','MODEL','TE=ADMPDB','
MSDB','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status =3D 0
begin
set @.string =3D N' USE '+@.BD+''
--print @.string
exec sp_executesql @.string
SELECT TABLE_NAME FROM
INFORMATION_SCHEMA.TABLES
fetch next from cursorBD into @.BD
END=20
CLOSE CURSORBD=20
DEALLOCATE CURSORBD|||Hi
You shoudl read http://www.sommarskog.se/dynamic_sql.html on issues
regarding dynamic SQL. For you query use three part naming instead of the US
E
statement
declare cursorbd cursor fast_forward for
SELECT 'SELECT TABLE_NAME FROM ' + QUOTENAME(NAME) +
'.INFORMATION_SCHEMA.TABLES' FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
declare @.sqlstmt nvarchar(4000)
open cursorbd
fetch next from cursorBD into @.sqlstmt
while @.@.fetch_status = 0
begin
exec (@.sqlstmt)
fetch next from cursorBD into @.sqlstmt
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
John
"Enric" wrote:

> I would need go along the current sql server using a cursor (another
> alternatives will be welcomed) and changing of database
> Something like that:
> DECLARE @.BD AS CHAR(20)
> declare cursorbd cursor fast_forward for
> SELECT NAME FROM MASTER.DBO.SYSDATABASES
> WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
> open cursorbd
> fetch next from cursorBD into @.BD
> while @.@.fetch_status = 0
> begin
> USE @.BD
> GO
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> fetch next from cursorBD into @.BD
> END
> CLOSE CURSORBD
> DEALLOCATE CURSORBD
> But it isn't working.
> Regards,|||Since this sounds like an administrative task, and not production code,
then you could use one of Microsoft's undocumented stored procedures to
help: sp_MSforeachdb
EXEC sp_MSforeachdb @.command1="SELECT TABLE_NAME as ? FROM
INFORMATION_SCHEMA.TABLES"
Of course, this will return all databases (including system and test),
and I would definitely NOT use this in production, as it may change in
future releases, yada, yada.
If you want to use a cursor, then you'll need to use dynamic SQL to
build your SQL statement:
DECLARE @.BD AS VARCHAR(20)
DECLARE @.SQL as varchar (200)
declare cursorbd cursor fast_forward for
SELECT NAME FROM MASTER.DBO.SYSDATABASES
WHERE NAME NOT IN('MASTER','PUBS','MODEL','TEMPDB','MSD
B','NORTHWIND')
open cursorbd
fetch next from cursorBD into @.BD
while @.@.fetch_status = 0
begin
SET @.SQL = 'USE ' + @.BD + '
SELECT TABLE_NAME AS ' + @.BD + '
FROM INFORMATION_SCHEMA.TABLES '
EXEC (@.SQL)
fetch next from cursorBD into @.BD
END
CLOSE CURSORBD
DEALLOCATE CURSORBD
Finally, you could use SQL-DMO and a scripting language to do this
task; this option is the most powerful, because it allows you to treat
every SQL entity as an object, and expose their properties through an
evet-drive interface. I use this to script out my development
databases in order to check them into source control.
http://msdn.microsoft.com/library/d...br />
3tlx.asp
VBScript (abbreviated)
SET SQLServer = CreateObject("SQLDMO.SqlServer")
SET Database = CreateObject("SQLDMO.Database")
SET Table = CreateObject("SQLDMO.Table")
SET View = CreateObject("SQLDMO.View")
SET Proc = CreateObject("SQLDMO.StoredProcedure")
SET Func = CreateObject("SQLDMO.UserDefinedFunction")
SET Index = CreateObject("SQLDMO.Index")
SQLServer.LoginSecure = TRUE
SQLServer.Connect ServerName
For each Database in SQLServer.Databases
If Database.SystemObject = False Then
For Each Table In Database.Tables
'write out the tables to a file here
Next
End if
Next
HTH
Stu

Dynamic USE

I want to run a .sql script that updates data from specific tables using
OSQL. The problem i am having is that i need to run the same script on 300
servers. The database naming scheme are as follows:
server 1 : server1_testdb
server 2 : server2_testdb
server 3: server3_testdb
Question:
How can I write one script that can be used for all 300 servers. All
databases have the same structure and are identical except that it resides in
300 servers that is where the prefix (server1_, server2_, etc.) come from.
Does it mean I have to write 300 scripts to specify the database where the
script will be run.
Ex.
script 1: USE server1_testdb
UPDATE tbl_sample ...
script 2: USE server2_testdb
UPDATE tbl_sample ...
script 3: USE server3_testdb
UPDATE tbl_sample ...
Is there a way to employ the 'USE' statement dynamically? Is there a way to
pass a variable to the USE statement such as:
DECLARE @.sampledb sysname
SELECT @.sampledb = master.sysdatabases.name.........
USE @.sampledb
Help highly appreciated.Looks to me like you should do this with SMO (SQL 2005) or DMO (earlier).
In both cases, your connect strings will determine which server/DB to use.
At that point, it's all SQL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:24723016-1F59-4B31-A779-988ECA6EA444@.microsoft.com...
I want to run a .sql script that updates data from specific tables using
OSQL. The problem i am having is that i need to run the same script on 300
servers. The database naming scheme are as follows:
server 1 : server1_testdb
server 2 : server2_testdb
server 3: server3_testdb
Question:
How can I write one script that can be used for all 300 servers. All
databases have the same structure and are identical except that it resides
in
300 servers that is where the prefix (server1_, server2_, etc.) come from.
Does it mean I have to write 300 scripts to specify the database where the
script will be run.
Ex.
script 1: USE server1_testdb
UPDATE tbl_sample ...
script 2: USE server2_testdb
UPDATE tbl_sample ...
script 3: USE server3_testdb
UPDATE tbl_sample ...
Is there a way to employ the 'USE' statement dynamically? Is there a way to
pass a variable to the USE statement such as:
DECLARE @.sampledb sysname
SELECT @.sampledb = master.sysdatabases.name.........
USE @.sampledb
Help highly appreciated.|||If you have SQL Server 2005 among your servers and all your servers are
visible from the server with SQL Server 2005 installed use SQLCMD
command-line utility and its features combined with some simple Windows
shell commands.
Create a sql script (save it as script.sql) like:
USE $(dbname)
GO
UPDATE ... -- put your statement here
Create simple Windows batch (save it as batch.bat in the same location as
script.sql) like:
for /L %%n IN (1,1,300) DO sqlcmd -S server%%n -E -v
dbname=server%%n_testdb -i script.sql
Then just run the batch and you should get what you want. I assume that you
have the same Windows login on all SQL Server instances.
Let me know if it works.
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
U¿ytkownik "morphius" <morphius@.discussions.microsoft.com> napisa³ w
wiadomo¶ci news:24723016-1F59-4B31-A779-988ECA6EA444@.microsoft.com...
>I want to run a .sql script that updates data from specific tables using
> OSQL. The problem i am having is that i need to run the same script on
> 300
> servers. The database naming scheme are as follows:
> server 1 : server1_testdb
> server 2 : server2_testdb
> server 3: server3_testdb
> Question:
> How can I write one script that can be used for all 300 servers. All
> databases have the same structure and are identical except that it resides
> in
> 300 servers that is where the prefix (server1_, server2_, etc.) come from.
> Does it mean I have to write 300 scripts to specify the database where the
> script will be run.
> Ex.
> script 1: USE server1_testdb
> UPDATE tbl_sample ...
> script 2: USE server2_testdb
> UPDATE tbl_sample ...
> script 3: USE server3_testdb
> UPDATE tbl_sample ...
> Is there a way to employ the 'USE' statement dynamically? Is there a way
> to
> pass a variable to the USE statement such as:
> DECLARE @.sampledb sysname
> SELECT @.sampledb = master.sysdatabases.name.........
> USE @.sampledb
> Help highly appreciated.
>

Dynamic USE

I want to run a .sql script that updates data from specific tables using
OSQL. The problem i am having is that i need to run the same script on 300
servers. The database naming scheme are as follows:
server 1 : server1_testdb
server 2 : server2_testdb
server 3: server3_testdb
Question:
How can I write one script that can be used for all 300 servers. All
databases have the same structure and are identical except that it resides i
n
300 servers that is where the prefix (server1_, server2_, etc.) come from.
Does it mean I have to write 300 scripts to specify the database where the
script will be run.
Ex.
script 1: USE server1_testdb
UPDATE tbl_sample ...
script 2: USE server2_testdb
UPDATE tbl_sample ...
script 3: USE server3_testdb
UPDATE tbl_sample ...
Is there a way to employ the 'USE' statement dynamically? Is there a way to
pass a variable to the USE statement such as:
DECLARE @.sampledb sysname
SELECT @.sampledb = master.sysdatabases.name.........
USE @.sampledb
Help highly appreciated.Looks to me like you should do this with SMO (SQL 2005) or DMO (earlier).
In both cases, your connect strings will determine which server/DB to use.
At that point, it's all SQL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:24723016-1F59-4B31-A779-988ECA6EA444@.microsoft.com...
I want to run a .sql script that updates data from specific tables using
OSQL. The problem i am having is that i need to run the same script on 300
servers. The database naming scheme are as follows:
server 1 : server1_testdb
server 2 : server2_testdb
server 3: server3_testdb
Question:
How can I write one script that can be used for all 300 servers. All
databases have the same structure and are identical except that it resides
in
300 servers that is where the prefix (server1_, server2_, etc.) come from.
Does it mean I have to write 300 scripts to specify the database where the
script will be run.
Ex.
script 1: USE server1_testdb
UPDATE tbl_sample ...
script 2: USE server2_testdb
UPDATE tbl_sample ...
script 3: USE server3_testdb
UPDATE tbl_sample ...
Is there a way to employ the 'USE' statement dynamically? Is there a way to
pass a variable to the USE statement such as:
DECLARE @.sampledb sysname
SELECT @.sampledb = master.sysdatabases.name.........
USE @.sampledb
Help highly appreciated.|||If you have SQL Server 2005 among your servers and all your servers are
visible from the server with SQL Server 2005 installed use SQLCMD
command-line utility and its features combined with some simple Windows
shell commands.
Create a sql script (save it as script.sql) like:
USE $(dbname)
GO
UPDATE ... -- put your statement here
Create simple Windows batch (save it as batch.bat in the same location as
script.sql) like:
for /L %%n IN (1,1,300) DO sqlcmd -S server%%n -E -v
dbname=server%%n_testdb -i script.sql
Then just run the batch and you should get what you want. I assume that you
have the same Windows login on all SQL Server instances.
Let me know if it works.
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uytkownik "morphius" <morphius@.discussions.microsoft.com> napisa w
wiadomoci news:24723016-1F59-4B31-A779-988ECA6EA444@.microsoft.com...
>I want to run a .sql script that updates data from specific tables using
> OSQL. The problem i am having is that i need to run the same script on
> 300
> servers. The database naming scheme are as follows:
> server 1 : server1_testdb
> server 2 : server2_testdb
> server 3: server3_testdb
> Question:
> How can I write one script that can be used for all 300 servers. All
> databases have the same structure and are identical except that it resides
> in
> 300 servers that is where the prefix (server1_, server2_, etc.) come from.
> Does it mean I have to write 300 scripts to specify the database where the
> script will be run.
> Ex.
> script 1: USE server1_testdb
> UPDATE tbl_sample ...
> script 2: USE server2_testdb
> UPDATE tbl_sample ...
> script 3: USE server3_testdb
> UPDATE tbl_sample ...
> Is there a way to employ the 'USE' statement dynamically? Is there a way
> to
> pass a variable to the USE statement such as:
> DECLARE @.sampledb sysname
> SELECT @.sampledb = master.sysdatabases.name.........
> USE @.sampledb
> Help highly appreciated.
>