dynmica use @db

Hi There,
I've number of databases, each one is having invoice table and a amount
fields in it.
I want to use somethig like this in a dynamic way using loop, i can generate
all database what all i need in a cursor, but i can not use like this
use @.db_name in a loop
use db1
select @.amt = sum(amount) from invoice
use db2
select @.amt = sum(amount) from invoice
@.tot = @.tot + @.amt
print @.tot
Thanks
GaneshYou can use 3-part naming like :
SELECT @.tot = SUM( amount )
FROM ( SELECT SUM( amount ) FROM db1.dbo.invoice
UNION ALL
SELECT SUM( amount ) FROM db2.dbo.invoice
UNION ALL
... ) D ( amount ) ;
PRINT @.tot
Anith|||If you really want to do it in a loop...and already have the Cursor logic
setup, you need to do something like this:
DECLARE db_cursor CURSOR FOR
SELECT dbname FROM yourtable
--Get first name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @.dbname
WHILE (@.@.fetch_status <> -1)
BEGIN
SELECT @.cmd = 'use ' + @.dbname
EXEC (@.cmd)
select @.amt = sum(amount) from invoice
set @.tot = @.tot + @.amt
FETCH NEXT FROM db_cursor INTO @.dbname
END
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:22D76D45-8A91-4DFD-84DC-1BB2D15DEA97@.microsoft.com...
> Hi There,
> I've number of databases, each one is having invoice table and a amount
> fields in it.
> I want to use somethig like this in a dynamic way using loop, i can
> generate
> all database what all i need in a cursor, but i can not use like this
> use @.db_name in a loop
> use db1
> select @.amt = sum(amount) from invoice
> use db2
> select @.amt = sum(amount) from invoice
> @.tot = @.tot + @.amt
> print @.tot
>
> --
> Thanks
> Ganesh|||I got error message like this
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Invoice'.
Thanks
Ganesh
"VC" wrote:

> If you really want to do it in a loop...and already have the Cursor logic
> setup, you need to do something like this:
> DECLARE db_cursor CURSOR FOR
> SELECT dbname FROM yourtable
> --Get first name
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @.dbname
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> SELECT @.cmd = 'use ' + @.dbname
> EXEC (@.cmd)
> select @.amt = sum(amount) from invoice
> set @.tot = @.tot + @.amt
> FETCH NEXT FROM db_cursor INTO @.dbname
> END
>
> "Ganesh" <gsganesh@.yahoo.com> wrote in message
> news:22D76D45-8A91-4DFD-84DC-1BB2D15DEA97@.microsoft.com...
>
>|||Well, then the table Invoice must not exist in the particular db that you
are in when the error occurs.
I would add a statement "print @.cmd" before the "select @.amt..." statement
to see which db you are currently using when the error occurs.
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:8C338237-CD72-4553-BEBF-2FE48E6CF804@.microsoft.com...
>I got error message like this
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Invoice'.
>
> --
> Thanks
> Ganesh
>
> "VC" wrote:
>|||No Invoice exists, But the Exec is a kind of connection, once we exected
after that it closes the connection, That's why i could not execute next
statment using exec
then i changed the select statement into the same exec now it works
Thanks for your help
Thanks
Ganesh
"VC" wrote:

> Well, then the table Invoice must not exist in the particular db that you
> are in when the error occurs.
> I would add a statement "print @.cmd" before the "select @.amt..." statement
> to see which db you are currently using when the error occurs.
>
> "Ganesh" <gsganesh@.yahoo.com> wrote in message
> news:8C338237-CD72-4553-BEBF-2FE48E6CF804@.microsoft.com...
>
>

Dynmaic parameter on Subscription report

Is it possible to have a dynamic parameter value such as current date
to a subscription base report?
Thanks,
Voss.Yes. You can set the default parameter =today() in the designer.
"Voss" wrote:
> Is it possible to have a dynamic parameter value such as current date
> to a subscription base report?
> Thanks,
> Voss.
>sql

Dyncamic SQL

Hi
I am trying to populate a Temp table with dynamic SQL but it does not seem
to work and the error message returned is that is it an invalid object,
leading me to believe that it does not get created:
-- ****************************************
********
USE Pubs
GO
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
EXEC (@.SQLString)
SELECT * FROM #TempParamFilter
-- ****************************************
********
Is there something wrong with my syntax?
Kind Regards
RickyThe problem is SCOPE. The # temp table you created goes away after the
dynamic SQL finishes executing. Based on what you posted, you don't even
need dynamic SQL to do this particular job:
USE Pubs
GO
SELECT * INTO #TempParamFilter FROM Authors
SELECT * FROM #TempParamFilter
DROP TABLE #TempParamFilter
"Ricky" <ricky@.msn.com> wrote in message
news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi
> I am trying to populate a Temp table with dynamic SQL but it does not seem
> to work and the error message returned is that is it an invalid object,
> leading me to believe that it does not get created:
> -- ****************************************
********
> USE Pubs
> GO
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
> EXEC (@.SQLString)
> SELECT * FROM #TempParamFilter
>
> -- ****************************************
********
> Is there something wrong with my syntax?
> Kind Regards
> Ricky
>|||Hi Mike
It was a simple example, I should have posted the real situation. What I am
really trying to achieve is a dynamic WHERE clause, appended to a table and
then populate a temporary table. Is this possible.
I mention Dynamic WHERE clause, since the Column may change, depending on
the parameter supplied.
e.g
@.StockParam = 'Q1HTS'
then WHERE clause would be : WHERE QStock = @.StockParam
or
@.StockParam = 'T1HTS'
then WHERE clause would be : WHERE AlphaStock = @.StockParam
so what I thought about doing, was to have my basic select statement and
then append a dynamic WHERE clause as a variable and then populate a #Table
to SELECT from , later when compiling the final recordset.
Hope this makes sense.
Kind Regards
Ricky
"Mike C#" <xyz@.xyz.com> wrote in message
news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> The problem is SCOPE. The # temp table you created goes away after the
> dynamic SQL finishes executing. Based on what you posted, you don't even
> need dynamic SQL to do this particular job:
> USE Pubs
> GO
> SELECT * INTO #TempParamFilter FROM Authors
> SELECT * FROM #TempParamFilter
> DROP TABLE #TempParamFilter
> "Ricky" <ricky@.msn.com> wrote in message
> news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
seem
>|||The problem is that the temp table is available only within the scope of
EXEC. Either use a global temp table or use the SELECT within the scope of
the EXEC like:
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
SELECT * FROM #TempParamFilter '
EXEC (@.SQLString) ;
Anith|||Probably not the most performant, but
WHERE
QStock = CASE @.StockParam
WHEN 'Q1HTS' THEN @.StockParam
ELSE QStock END
AND
AlphaStock = CASE @.StockParam
WHEN 'T1HTS' THEN @.StockParam
ELSE AlphaStock END
What do you need a #temp table for?
"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
> Hope this makes sense.
> Kind Regards
> Ricky
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> seem
>|||Hi Anith
How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Kind Regards
Ricky
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
> The problem is that the temp table is available only within the scope of
> EXEC. Either use a global temp table or use the SELECT within the scope of
> the EXEC like:
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
> SELECT * FROM #TempParamFilter '
> EXEC (@.SQLString) ;
> --
> Anith
>|||"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
It's possible if you create the temp table before executing the dynamic sql.
Here's an example to get you started. Note that the temp table is created
outside of the dynamic SQL, but the dynamic SQL has access to it. It won't
work the other way around. Also note that this example uses sp_executesql
to parameterize the query. sp_executesql requires NVARCHAR data, and helps
protect against SQL injection:
USE pubs
GO
CREATE TABLE #temp_emp(emp_id VARCHAR(9) NOT NULL PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
minit CHAR(1) NOT NULL,
lname VARCHAR(30) NOT NULL)
DECLARE @.emp_last_name NVARCHAR(30)
SELECT @.emp_last_name = N'Smith'
DECLARE @.dyn_sql NVARCHAR(512)
SELECT @.dyn_sql = N'INSERT INTO #temp_emp (emp_id, fname, minit, lname) ' +
N'SELECT emp_id, fname, minit, lname ' +
N'FROM employee ' +
N'WHERE lname = @.lname'
EXEC dbo.sp_executesql @.dyn_sql, N'@.lname NVARCHAR(30)', @.lname =
@.emp_last_name
SELECT *
FROM #temp_emp
DROP TABLE #temp_emp|||> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Because a single EXEC() call represents one 'scope' (I'm not sure if that's
a valid noun there, but oh well). The second SELECT is occuring in the same
scope as that which created the #temp table.|||Another way to think about the scope of something like EXEC() is a typical
popup window in a browser (the good kind, not the annoying advertisements).
In most cases, the popup window could jump through several different pages
and do all kinds of things, and the window that opened it couldn't care less
and usually doesn't have any knowledge of what is going on in the popup.
"Ricky" <ricky@.msn.com> wrote in message
news:OPO%23KpVkGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi Anith
> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
> Kind Regards
> Ricky
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u4VydtVkGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Because a single EXEC() call represents one 'scope' (I'm not sure if
> that's a valid noun there, but oh well). The second SELECT is occuring in
> the same scope as that which created the #temp table.
You know, I always hated the phrase "well-defined scope" for table
variables, etc. It implies that everything else has "poorly-defined scope"
:) But from a marketing perspective I guess "well-defined scope" sounds
better than "limited scope" or "extremely tight scope" :)

DynamicSQL & the Index Tuning wizard.

Some facts first:
1. Large application running on SQL Server 2000.
2. Stored Procedures use a lot of dynamic SQL.
3. Performance is a major issue.
4. Focussing on the database:
4.1 Start up SQL Profiler.
4.2 Run the application for a period of time (normal customer usage) to
generate the workload file.
4.3 Run the ITW on this workload file.
Questions:
(a) What kind of output can I expect from the ITW since the t-sql code is
heavily using dynamic SQL?
(b) Will the ITW be able to properly analyze the workload file in this
scenario?
(c) Are there any other issues that I should be aware of when using the SQL
Profiler and ITW for dynamic SQL code analysis?
TIA
Cheers!
SQLCatZ
SQLCatz,
Yes, the ITW will work fine with dynamic SQL. You might also want to
look at the profiler trace yourself and run some queries against it. I
look for:
1) CPU intensive queries (CPU column)
2) IO intensive queries (reads, writes columns)
3) Long running queries (duration column)
If you get the top 10 culprits from each of those categories, you will
have eliminated 90% of your poor performing queries. You want to get the
biggest bangs for your buck, and not waste time on things that don't
really matter.
Use the ITW as a guide to making decisions about which indexes to apply,
in most cases you won't want to blindly implement what it suggests.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
SQLCatz wrote:
> Some facts first:
> 1. Large application running on SQL Server 2000.
> 2. Stored Procedures use a lot of dynamic SQL.
> 3. Performance is a major issue.
> 4. Focussing on the database:
> 4.1 Start up SQL Profiler.
> 4.2 Run the application for a period of time (normal customer usage) to
> generate the workload file.
> 4.3 Run the ITW on this workload file.
> Questions:
> (a) What kind of output can I expect from the ITW since the t-sql code is
> heavily using dynamic SQL?
> (b) Will the ITW be able to properly analyze the workload file in this
> scenario?
> (c) Are there any other issues that I should be aware of when using the SQL
> Profiler and ITW for dynamic SQL code analysis?
> TIA
> Cheers!
> SQLCatZ
>
sql

dynamically creating temp tables

I have a dynamic sql which uses "select into" to create a temp table. It has
to be dynamic because the fieldnames are soft coded.I need to join this temp
table (that i created using the dynamic sql) with another table.Since the
temp table goes out of scope after the exec statement i am not able to use i
t
in my second query.
Thanks in advance!Can't you perform the join within the same block of dynamic SQL?
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!|||Hi HP
You already have an active thread going on this topic, in this newsgroup;
you do not need to start another one.
Thanks
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"HP" <HP@.discussions.microsoft.com> wrote in message
news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>I have a dynamic sql which uses "select into" to create a temp table. It
>has
> to be dynamic because the fieldnames are soft coded.I need to join this
> temp
> table (that i created using the dynamic sql) with another table.Since the
> temp table goes out of scope after the exec statement i am not able to use
> it
> in my second query.
> Thanks in advance!
>|||Actually i have to use that temp table in 2 queries.If i include those selec
t
stetements within the same block , the dynamic sql would be big, and the
execution of the dynamic statement could be slow.Correct me if I am wrong.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can't you perform the join within the same block of dynamic SQL?
>
> "HP" <HP@.discussions.microsoft.com> wrote in message
> news:BC97CA3E-DFC3-4DB5-AB56-605047D3D527@.microsoft.com...
>
>|||> Actually i have to use that temp table in 2 queries.If i include those
> select
> stetements within the same block , the dynamic sql would be big, and the
> execution of the dynamic statement could be slow.Correct me if I am wrong.
You're using dynamic SQL and #temp tables. I doubt the size of your dynamic
SQL is going to have a measurable impact on that kind of performance.
If the dynamic SQL is too big for a single varchar(8000) you can always try:
EXEC ( @.tempTableCreation +';' + @.sqlJoin1 + ';' + @.sqlJoin2 )

dynamically changing the connection properties

I want to transfer data from one server to another by using SSIS. i want the connection string to be dynamic and also according to the some other variable, the transforming data is changing.

Could you provide me the solution thet how i am able to change my connetction string dynamically and the other variable too.

i am using VS 2003 as front end and SQL server 2005 as a backhand.

Due to VS.NET 2003 i am able to create DTS packages but i have to migrate it and then anly i am able to use it in JOB in SQL server agent of SQL server 2005.

is that any code or any stored procedure from which i am able to migrate DTS packages to SSIS packages.

Thank you

You need Business Intelligence Developers Studio or Visual Studio 2005 to edit SSIS packages. You can use configurations to change connection strings or variables, or you can set them by using the /SET switch for DTEXEC.|||You can reset the connection string using Script task in you SSIS and you can acess the conneciton variable as DTS.Connection|||

I suggest that you use configurations, or variables and expressions before you start with Script Tasks. The latter are harder to support. Using one of the more structured options should be easier, and also more manageable going forward, particularly when looking to future versions.

dynamicall execute SP without dynamic sql

How would you go about dynamically executing a stored proc dependent on
a variable? I cannot use dynamic sql.Why do you want to do without dynamic sql?

Madhivanan|||You can use a parameter instead of a proc name (see EXECUTE in Books
Online):

exec @.p

But this is problematic if different procedures may require different
parameters. Or if the number of procedures is relatively small, then
you could just use IF ... ELSE ... to conditionally execute a proc.

Simon|||Use IF statements

IF @.var = 'Proc1'
EXEC Proc1
IF @.var = 'Proc2'
EXEC Proc2
...

If you create a new proc you just need to add it to the list. You could
even generate the list automatically from the info schema ROUTINES
view.

--
David Portas
SQL Server MVP
--|||That sounds like a interesting solution that would probably work. How
would you generate that list and then incorporate it into the if
statements. Thanks!|||Just query against the routine_name column and then cut and paste into
your SP. You still can't expect to automate that process entirely
without using dynamic SQL. Does that matter? Assuming you have adequate
change control procedures in place it shouldn't be a problem.

--
David Portas
SQL Server MVP
--

Dynamic YTD based on user selection on Month

I have a user how wants to be able to see the YTD numbers based on the month they select in an attribute hierarchy with in the Time Dimension.

I would like to create a calculated member called YTD that they can use to get this information based on the Month they select in the Month attribute

The Time Dimensions are as follows: Fiscal time(Year, Qtr, Month) Month(Month)

Measure = Net Sales

View Format should be

Years

2000 2001 2002 2003 2004 2005

Customer $ $ $ $ $ $

YTD should work if the user drills down to Qtr or Month, if they selected a member in the Month attribute or not.

The Time dimension is only loaded with the years that are in the cube so if the user selects Dec but no data is in the cube for Dec 2005. 2005 should still show up on the report.

One of the issues I have run into is determining if the user is looking(selected) something from the Fiscal Hierarchy of from the Month Attirbute. If I use [Time].[Month].currentmember.name I get the name of the month the user selected in Month Attribute but if the user selects "All" months in the Month attribute but selects the Months from the Fiscal hierarchy I still get the month name and not the value "All".

Please Help.

Here is an example of a calculated member that I added to the "AdventureWorks" cube that will return the "Calendar" YTD internet sales amount based on what has been selected as the current member of the Date.Calendar hierarchy. The MDX will also work in AS2K.

Create Member CurrentCube.[Measures].[CYTD Internet Sales Amount]

AS

Aggregate(

PeriodsToDate(

[Date].[Calendar].[Calendar Year],

[Date].[Calendar].CurrentMember),

Measures.[Internet Sales Amount]),

Format_String = "Currency",

Non_Empty_Behavior = { Measures.[Internet Sales Amount] };

HTH,

- Steve

Dynamic YTD

Is it possible to show YTD information (considering our fiscal year starts in October) and have it dynamically change year-to-year? What I need to be able to display is information from Oct1 through Sept 30, but I don't want to go in every year and change the date range. Is this possible?

Short answer is, yes, it is possible.

But the specifics depend on how you are retrieving/filtering your data... is it:

1- Using a SQL relationship datasource (so we need to make a T-SQL query that does what you want)

2- Using an Analysis data source (so we'd need to make an MDX query that does what you want)

3- Any data source, then filtering the data in Reporting Services (we'd need to make a VB Expression then)

4- Something else entirely (in which case we'll need to know how you're getting your data)

#3 could introduce some performance issues, #1 is the easiest to implement.

Let us know which you use, and can help from there.

G

|||

Thanks for the reply. I am pulling data using SQL (#1).

Would you have an example of a T-SQL statement that I could look at?

|||

Yes, something like this should work

DECLARE @.FY as datetime

SET @.FY = CASE WHEN MONTH(GetDate()) < 10 THEN

CAST(CAST(Year(DateAdd(yy,-1,GetDate())) as varchar(4)) + '-10-01' as datetime)

ELSE

CAST(CAST(Year(GetDate()) as varchar(4)) + '-10-01' as datetime)

END

SELECT * From YourTable

WHERE YourDateColumn BETWEEN @.FY AND DateAdd(yy,1,@.FY)

If you can't run a script and declare a variable to reuse it, just replace the @.FY with the CASE...END function to start with.

The function can be improved a tad still, but this gives you the idea. Check your boundaries, I'm not sure off the top of my head if the 1st of October of the next year will be included as written. The BETWEEN operator may not meet your needs.

G

|||

This is what I needed. Thanks, G.

-Jody

Dynamic YTD

Is it possible to show YTD information (considering our fiscal year starts in October) and have it dynamically change year-to-year? What I need to be able to display is information from Oct1 through Sept 30, but I don't want to go in every year and change the date range. Is this possible?

Short answer is, yes, it is possible.

But the specifics depend on how you are retrieving/filtering your data... is it:

1- Using a SQL relationship datasource (so we need to make a T-SQL query that does what you want)

2- Using an Analysis data source (so we'd need to make an MDX query that does what you want)

3- Any data source, then filtering the data in Reporting Services (we'd need to make a VB Expression then)

4- Something else entirely (in which case we'll need to know how you're getting your data)

#3 could introduce some performance issues, #1 is the easiest to implement.

Let us know which you use, and can help from there.

G

|||

Thanks for the reply. I am pulling data using SQL (#1).

Would you have an example of a T-SQL statement that I could look at?

|||

Yes, something like this should work

DECLARE @.FY as datetime

SET @.FY = CASE WHEN MONTH(GetDate()) < 10 THEN

CAST(CAST(Year(DateAdd(yy,-1,GetDate())) as varchar(4)) + '-10-01' as datetime)

ELSE

CAST(CAST(Year(GetDate()) as varchar(4)) + '-10-01' as datetime)

END

SELECT * From YourTable

WHERE YourDateColumn BETWEEN @.FY AND DateAdd(yy,1,@.FY)

If you can't run a script and declare a variable to reuse it, just replace the @.FY with the CASE...END function to start with.

The function can be improved a tad still, but this gives you the idea. Check your boundaries, I'm not sure off the top of my head if the 1st of October of the next year will be included as written. The BETWEEN operator may not meet your needs.

G

|||

This is what I needed. Thanks, G.

-Jody

Dynamic y axis labeling

Hi

I'm trying to label the y axis from a dataset but keep running in to problems as it only takes ints and the data from the database is text, does anyone know the best way to do this?

Thanks

L

I would suggest using a bar graph. This will enable you to put text on the y-axis and the values will be on the x-axis.

Put the dataset field you want on the y-axis in the category groups and you will be good to go.

Dynamic XSD Schema Location For XML Source

I want to be able to programmatically set the schema location for an XML source. I first thought it would be a simple task using expressions and variables but it doesn't appear to allow anything in the way of setting it at runtime. Is this possible?

It does allow it, but you don't set the expression directly on the component. You have to set it on the data-flow that contains the component.

Switch back to control-flow and look at expressions of the data-flow. Its self-explanatory from there.

-Jamie

|||Beautiful! Thank you.|||Thanks Jamie... just wanted to chime in and say this was really helpful - I would have never thought of looking on the properties of the data flow.

dynamic XML using for XML explicit

Hello everyone,
I had written a procedure to generate the XML in which the xml
structure was static.
eg.
<root>
<parent1>
<child></child>
</parent1>
<parent2>
<child></child>
</parent2>
</root>
for this i wrote two select queries and using UNION ALL and FOR XML explicit
generated it.
now the structure of XML to be generated is dynamic depending on the
parameter that is passes to the storedprocedure.
so the xml cud be <root><parent><subparent><child><subchild> for one
parameter
and
<root><child>
for another and <root><parent><child>
for other so it wud keep changing.
My question is how should i write the procedure to generate dynamic XML.
i can know the no of levels as soon as the parameter os given.
i'd be glad if somebody could tell me an approach to generate dynamic XML.
thanks and regards,
toby
Hello Toby,
If you only have those three possibilities to worry about, consider using
a T-SQL case based on the parameter value, then using having a clause that
executes the correct FOR XML EXPLICIT query as part of that. Otherwise, you're
asking for basically non-maintainable code.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||And if you use SQL Server 2005, you can use nested FOR XML PATH expressions
to make the code more modular and maintainable...
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74ed968c7ba38196ca340@.news.microsoft.com ...
> Hello Toby,
> If you only have those three possibilities to worry about, consider using
> a T-SQL case based on the parameter value, then using having a clause that
> executes the correct FOR XML EXPLICIT query as part of that. Otherwise,
> you're asking for basically non-maintainable code.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

dynamic XML using for XML explicit

Hello everyone,
I had written a procedure to generate the XML in which the xml
structure was static.
eg.
<root>
<parent1>
<child></child>
</parent1>
<parent2>
<child></child>
</parent2>
</root>
for this i wrote two select queries and using UNION ALL and FOR XML explicit
generated it.
now the structure of XML to be generated is dynamic depending on the
parameter that is passes to the storedprocedure.
so the xml cud be <root><parent><subparent><child><subchild> for one
parameter
and
<root><child>
for another and <root><parent><child>
for other so it wud keep changing.
My question is how should i write the procedure to generate dynamic XML.
i can know the no of levels as soon as the parameter os given.
i'd be glad if somebody could tell me an approach to generate dynamic XML.
thanks and regards,
tobyHello Toby,
If you only have those three possibilities to worry about, consider using
a T-SQL case based on the parameter value, then using having a clause that
executes the correct FOR XML EXPLICIT query as part of that. Otherwise, you'
re
asking for basically non-maintainable code.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||And if you use SQL Server 2005, you can use nested FOR XML PATH expressions
to make the code more modular and maintainable...
Best regards
Michael
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74ed968c7ba38196ca340@.news.microsoft.com...
> Hello Toby,
> If you only have those three possibilities to worry about, consider using
> a T-SQL case based on the parameter value, then using having a clause that
> executes the correct FOR XML EXPLICIT query as part of that. Otherwise,
> you're asking for basically non-maintainable code.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

Dynamic Windows Authentication

Hi

The Parent Package has a Loop Container. The Loop Container calls the Child Package and in the process the connection manager properties are changed using package configurations.

When the packages are tested in BIDS both Windows and SQL authentication work.

When the packages are scheduled on SQL authentication works and Windows authentication fails. (The account which executes the schedule has permissions to all the data sources required. The SQL Agent Service Account has permissions. In addition a crendential was created using a different account which also has permissions and this also failed.) If the connection managers are made static then the schedule executes.

Any ideas would be appreciated.

Thanks

Try this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

The error seems to be:

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

The dynamic windows authentication works interactively in BIDS but when scheduled it fails in the parent and child packages. An expression is used in the parent package and configurations are used in the child package. If the connections are made static the packages execute succesfully.

The KB on Troubleshooting Kerberos Delegation (http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx) alludes to NTLM authentication as being the source for null user problems but why would the static package work and not the dynamic one?

Thanks

Dynamic Windows Authentication

Hi

The Parent Package has a Loop Container. The Loop Container calls the Child Package and in the process the connection manager properties are changed using package configurations.

When the packages are tested in BIDS both Windows and SQL authentication work.

When the packages are scheduled on SQL authentication works and Windows authentication fails. (The account which executes the schedule has permissions to all the data sources required. The SQL Agent Service Account has permissions. In addition a crendential was created using a different account which also has permissions and this also failed.) If the connection managers are made static then the schedule executes.

Any ideas would be appreciated.

Thanks

Try this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

The error seems to be:

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

The dynamic windows authentication works interactively in BIDS but when scheduled it fails in the parent and child packages. An expression is used in the parent package and configurations are used in the child package. If the connections are made static the packages execute succesfully.

The KB on Troubleshooting Kerberos Delegation (http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx) alludes to NTLM authentication as being the source for null user problems but why would the static package work and not the dynamic one?

Thanks

Dynamic Width of textbox

Hi,
how can i set the width of a textbox dynamically ?
Ex: The text box has to expand in width to fit the availble data. It
shouldnt word wrap.
ex: hello how are you NOT hello how
are youSince you can not use an expression for textbox width, I do not think you
can do this...You would probably end up with bigger problems anyway, because
textboxes overlapping would be a problem unless you moved the others that
were affected by the size change anyway...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:E1CF20D3-CD14-4DB6-A380-3B5C694C26CB@.microsoft.com...
> Hi,
> how can i set the width of a textbox dynamically ?
> Ex: The text box has to expand in width to fit the availble data. It
> shouldnt word wrap.
> ex: hello how are you NOT hello how
> are you

Dynamic WHERE statement

If I pass a variable to @.Cost_Center that is 'SMS', 'SMP' OR 'ALL'
'ALL' will return records that have either 'SMS' or 'SMP' in them.
Now I am using an if statement that has the entire SQL statement in the body
of the
conditional.
I tried this
Where
IF (@.Cost_Center = 'All')
Begin
code...
and i.cost_center in ('SMS','SMP')
End
ELSE
Begin
code...
and i.cost_center = @.Cost_Center
End
Thanks
JimYou might want to check out the following article for some ideas:
http://www.sommarskog.se/dyn-search.html
Anith|||Thank you
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23glqcpZBFHA.4072@.TK2MSFTNGP10.phx.gbl...
> You might want to check out the following article for some ideas:
> http://www.sommarskog.se/dyn-search.html
> --
> Anith
>|||SELECT ...
FROM Foobar
WHERE cost_center = @.my_cost_center
OR cost_center
IN (CASE WHEN @.my_cost_center = 'ALL'
THEN 'SMP' ELSE '' END,
CASE WHEN @.my_cost_center = 'ALL'
THEN 'SMS' ELSE '' END));

dynamic where prob

hi!

i can't seem to get this right...

create procedure PRstudentInfo

(

@.pStudentCode varchar(20) = NULL,

@.pHidden bit

)

as

begin

declare @.select varchar(max), @.where varchar(max)

set @.select =

'select b.name, c.studentcode from basicinfo b inner join fullinfo c on b.code = c.studentcode'

set @.where =

'WHERE isnull(c.studentcode,'''') ' + 'like ' + '''%' + isnull(@.pStudentCode,'') + '%''' + ' and ' +

' isnull(c.hidden,'''') = ''' + isnull(@.pHidden,'') '' --> here is the error

exec(@.select + @.where)

end

--

the error message is :

Incorrect syntax near ''

i also tried this:

' isnull(c.confidential,'''') = ''' + isnull(@.pConfidential,'') + ' '

The data types varchar and bit are incompatible in the add operator.

A tip : add the following code line:

print @.select + @.where , to see how sql string is made.

I suppose you have to put a space :

'hereWHERE isnull(c.studentcode,'''') etc

|||

Change it to this:

' isnull(c.hidden,'''') = ''' + cast(isnull(@.pHidden,'') as char(1))

|||WOuld be nice if you can describe the next time, what you are trying to do within your code.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Dynamic WHERE operator based on user input

Let's say I have a table with 3 fields: an ID field (primary key, set as an id field, etc.), a Name field (nvarchar50), and an Age field (int). I have a form that has three elements:

DropDownList1: This drop down list contains 3 choices- "=", ">", and "<".

Age: This text box is where someone would enter a number.

Button1: This is the form's submit button.

I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.

The code-behind is shown below. The part I'm confused about is that if I load this page, the query works the -first- time. Then, if I try to change the parameters in the form and submit it, I get the following error:

"The variable name'@.Age' has already been declared. Variable names must be unique within a query batch or stored procedure."

Any help would be appreciated.

Here is what I'm using in my code behind:

protected void Button1_Click(object sender, EventArgs e)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT * FROM People WHERE Age ");
switch (DropDownList1.SelectedValue)
{
case "=":
sb.Append("= ");
break;
case ">":
sb.Append("> ");
break;
case "<":
sb.Append("< ");
break;
}
sb.Append("@.Age");
SqlDataSource1.SelectCommand = sb.ToString();
SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, Age.Text);
}

i would create 3 stored procedures first with "<" second with "=" and third with ">"

then, in code behind

if (DropDownList1.SelectedValue.ToString() == "<") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("LessThan_toate", conn);//cnn = your conn string command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader;// your dataReader DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }if (DropDownList1.SelectedValue.ToString() =="=") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("EqualsWith", conn); command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader; DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }if (DropDownList1.SelectedValue.ToString() ==">") { SqlConnection conn =new SqlConnection(ConnectionString); SqlCommand command =new SqlCommand("GreatherThen", conn); command.CommandType = CommandType.StoredProcedure; conn.Open(); SqlDataReader reader = command.ExecuteReader(); DataList1.DataSource = reader; DataList1.DataBind(); reader.Close(); command.Connection.Close(); conn.Close(); }

sure, my stored procedures doesn't have input parameters but you can change easly that

|||

adammckee:

I want someone to be able to search the database for entries where the Age is either equal to ("="), greater than (">"), or less than ("<") whatever number they enter into TextBox1.

So basically you want everything in the table? what are you excluding?

|||

If you have the Age value when you click the Button, why don't you just add the value directly into your select statement instead of worrying about a parameter.

protected void Button1_Click(object sender, EventArgs e){SqlDataSource1.SelectCommand = String.Format("SELECT * FROM People WHERE Age {0} {1}",DropDownList1.SelectedValue,Age.Text);}

|||Either I don't understand what you're doing or you're making it more complicated than it should be. Wouldn't this work?

Private Sub cmd_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cmd.ClickDim sQueryAs String sQuery ="SELECT * FROM People WHERE Age " &Me.DropDownList1.SelectedValue &" " &Me.txtAge.Text SqlDataSource1.SelectCommand = sQueryEnd Sub

|||

This worked nicely, but doesn't this leave me open to SQL injection? I was attempting to do this with the SelectParameters method to avoid that.

To clarify what I'm trying to do for the other person that asked, I want to give the user the ability to search for people in the database based on their age. If the user wants to search for anyone that's over 50, they pull down the drop down list, select the ">" operator then type "50" in the text field and perform the search.

ecbruck:

If you have the Age value when you click the Button, why don't you just add the value directly into your select statement instead of worrying about a parameter.

protected void Button1_Click(object sender, EventArgs e){SqlDataSource1.SelectCommand = String.Format("SELECT * FROM People WHERE Age {0} {1}",DropDownList1.SelectedValue,Age.Text);}

|||

If you're worried about it, then go ahead and use a Parameter instead. However, I would at least recommend declaratively placing the Parameter within your SqlDataSource on your ASPX page and then simply setting the DeefaultValue of the property within your Click event handler or the SqlDataSource.Selecting event handler.

|||

For anyone interested, this is what works for me. Thanks again for the advice.

1protected void Button1_Click(object sender, EventArgs e)2 {3 System.Text.StringBuilder sb =new System.Text.StringBuilder();4 sb.Append("SELECT * FROM People WHERE Age ");5switch (DropDownList1.SelectedValue)6 {7case"=":8 sb.Append("= ");9break;10case">":11 sb.Append("> ");12break;13case"<":14 sb.Append("< ");15break;16 }17 sb.Append("@.Age");18 SqlDataSource1.SelectCommand = sb.ToString();19if (SqlDataSource1.SelectParameters["Age"] ==null)20 SqlDataSource1.SelectParameters.Add("Age", TypeCode.Int32, TextBox1.Text);21else22 SqlDataSource1.SelectParameters["Age"].DefaultValue = TextBox1.Text;23 }