Configurations

Hello, I had a question I thought someone might be able to answer before I test my theory.

If I run an outer package through SQL Server Agent and specify it to use a configuration file, will the children packages it call also use the configuration file? My instinct is no it will not.

If this is in fact the case, would it be considered best practice to just specify the configuration file for each package through an indirect environment variable? Is it possible to set an environment variable on the set values tab within sql server agent?

Then my only concern is how would developers test packages on their machines, should they also set up the same environment variable on their machine?

Any thoughts?

Okay, I think I'm answering my own question, but perhaps others can benefit.

I think it is best to configure configurations in SSIS indirectly through an environment variable and then set the environment variable through sql server agent - command file tab, which will use the SetX command to modify the environment variable. This way all packages outer and inner can use the same dynamically configured configuration file! Although I supposse if multiple jobs tried to configure the same environment variable there could be issues, you'd have name your environment variables appropriately.

Thoughts?

|||

Configuration are not inherited between packages as you suspected.

I think you can say using indirect config files is good practice, and making you devs have the same env variable is the way to go. It actually works very well I find. I like the fact that I use the same mechanism to control connections and such like all the way from local development through to production. Configurations just work so well like that.

The Set tab in SQL Agent is not for environment variables, Set in that case is the same type of set that you do in the config file, xpath style property set, but available on teh execution command rather than wrapped in a file.

BTW, use CmdExec steps and DTEXEC, much better as you can use the step log.

|||Thanks for the response. Could you please clarify what you mean by your last statement?|||

The SSIS job step type is not very good. The CmdExec / Operating System step type supports logging of all output of the step. If you use DTEXEC and turn on step logging you get all the console output. Why is this good? Well put simply the SSIS step log capture is pants. Some errors are impossible to diagnose with it. A quick examples are file permissions, or other errors that happen before a package starts executing, see the top bit of this page -http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

|||Okay, I understand what your saying and completely agree. This is how things were done at a prior company, but here they prefer using ssis job steps for the ease of seeing what package is running. That is why I wanted to work around configuring environment variables through SQL Server Agent istead of within a batch file. Still, logging may be insufficient as you pointed out. Thanks.sqlsql

Configuration file for Connection Info (how do i set password)

Hi All,

I'm new to SSIS. I'm trying to build a simple package, run it on dev and migrate it to our stg and tst environments. It works on my dev environment great! It simply loads data from a flat file into a table. The trouble i'm having is exporting the connection string info to an xml file and runnign it on other environments. I am not storing the package on the db. I am saving to a file and running through SQL Server BI Development Studio.

I go to "Configuration File" and export InitialCatalog, ServerName, User, Password to the file. I can manually change the xml to the new environment but don't know how to set the password. It's not in the xml and i am constantly getting "Login failed for 'sa'" errors. Anyone know how to do this. I CAN get this whole process to work if i use Windows authentication security but this isn't an option. I need to use the db security.

Thanks,
Dave

check this out, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=516292&SiteID=1

kushpaw

|||Thanks! I figured what i was doing wrong. I exported config to xml file. I select Username, Password, InitialCatalog, and Servername to export. Problem i was having was that i the actual password value was not being exported, only the xml tags. I had to manually go in and enter the password value to the xml. It works great now.|||

tenaciousd wrote:

Thanks! I figured what i was doing wrong. I exported config to xml file. I select Username, Password, InitialCatalog, and Servername to export. Problem i was having was that i the actual password value was not being exported, only the xml tags. I had to manually go in and enter the password value to the xml. It works great now.

I recommend you only store the ConnectionString property. That is an amalgamation of all the other properties that you listed there.

-Jamie

sqlsql

Conditionally skip or jump steps in a job

Is it possible to conditionally skip or jump steps in a job without failing
a
step?
For example I don’t want to run a step if the are no records in a table.
I also need to loop based on a condition.About loops:
http://www.sqldts.com/default.aspx?246
About conditional execution:
http://www.sqldts.com/default.aspx?218
http://www.sqldts.com/default.aspx?214
Chris
"Dave" wrote:

> Is it possible to conditionally skip or jump steps in a job without failin
g a
> step?
> For example I don’t want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>|||You could place your (IF.. BEGIN.. END) condition within the T-SQL or stored
procedure beging called.
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:27AF395F-A67D-43E6-9F40-009ACCCB0877@.microsoft.com...
> Is it possible to conditionally skip or jump steps in a job without
> failing a
> step?
> For example I don't want to run a step if the are no records in a table.
> I also need to loop based on a condition.
>sqlsql

conditionally executing sql statement

I need to write .sql file in a dynamic way.
inside the file i need to decide at run time (of the sql script) whether or
not to run a SQL statement that I don't know it's content while dynamically
creating the sql script, this sql statement might include a GO statement
what makes a problem putting it inside an if begin end block.
for example
in my script a runtime check
select @.runStatement = configVal from database at script runtime execution
if @.runStatement =1
begin
-- here comes an unknown sql statement at the time of creating the sql
script that might include a GO command which will break the syntax of the
entire block
-- go <- this here makes a TSQL error for the end command since it
breaks the begin / end block.
end
can someone recomend of an approach for how to solve this?
execute sql is not an option here since the internal SQL statement might be
larget then 4000 nvarchar characters and I can not declarae a @.ntext local
variable
TIA.>> inside the file i need to decide at run time (of the sql script) whether
Under normal circumstances, this is a poor way to write SQL code. The kludgy
workaround is to assign the SQL statement to a variable, replace the tokens
that are not needed and use EXEC or sp_ExecuteSQL to execute it.
The right way can be suggested only if you can explain the overall
situation. Why do you have to resort to such complex approach? Is there a
3rd party tool involved?
Anith|||martin (news.microsoft.com) writes:
> I need to write .sql file in a dynamic way.
> inside the file i need to decide at run time (of the sql script) whether
> or not to run a SQL statement that I don't know it's content while
> dynamically creating the sql script, this sql statement might include a
> GO statement what makes a problem putting it inside an if begin end
> block.
> for example
> in my script a runtime check
> select @.runStatement = configVal from database at script runtime execution
> if @.runStatement =1
> begin
> -- here comes an unknown sql statement at the time of creating the
> sql script that might include a GO command which will break the syntax
> of the entire block
> -- go <- this here makes a TSQL error for the end command since it
> breaks the begin / end block.
> end
>
> can someone recomend of an approach for how to solve this?
> execute sql is not an option here since the internal SQL statement might
> be larget then 4000 nvarchar characters and I can not declarae a @.ntext
> local variable
Are you on SQL 2000 or SQL 2005?
If you are on SQL 2000, I would srtongly recommend that you run the
control loop from a client. It could be very difficult to sort out
from SQL only. It could be a little easier on SQL 2005, since there
you can work with nvarchar(MAX) and you could do the batch splitting
in CLR code.
I echoes Aniths suggestion that you could be better served by telling
us the full story. This could give you better suggestions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||it should support database modifications / upgrades.
the commands are not known at the time of designing the tool that will
execute the statements.
i understand that running each script from a client tool like a VB.NET app
is a good option but is it not possible to run it from sql script file using
some goto label....?
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:eedlU0VaGHA.1020@.TK2MSFTNGP02.phx.gbl...
> Under normal circumstances, this is a poor way to write SQL code. The
> kludgy workaround is to assign the SQL statement to a variable, replace
> the tokens that are not needed and use EXEC or sp_ExecuteSQL to execute
> it.
>
> The right way can be suggested only if you can explain the overall
> situation. Why do you have to resort to such complex approach? Is there a
> 3rd party tool involved?
> --
> Anith
>|||martin (news.microsoft.com) writes:
> it should support database modifications / upgrades.
> the commands are not known at the time of designing the tool that will
> execute the statements.
> i understand that running each script from a client tool like a VB.NET
> app is a good option but is it not possible to run it from sql script
> file using some goto label....?
Possible and possible. With severe kludges maybe. And it depends on the SQL
Server version.
If the purpose of the tool is run scripts for database changes, I strongly
recommend using a control part in a client language.
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.mspxsqlsql

Conditional SQL Triggers

Hi, I've been handed a task at work where I need to use SQL triggers to solve the problem.

I need to be able to run a Trigger when, and only when, a cell in a specific column in a row changes to a specific value.

For instance, say I have the following table:
CREATE TABLE source
(
ID tinyint NOT NULL,
contacttype tinyint NOT NULL,
)

If one of the rows is UPDATE'd to contacttype = 2, I want to fire a trigger, but not if it changes to anything else.

How can this be performed?You can attach a trigger to a table to respond to inserts, deletes or updates. I think you can narrow it down to a specific column (if columns_updated). Anything else goes in the trigger itself. See BOL for TRIGGER.|||create trigger blah on update
as
IF UPDATE(contacttype)
BEGIN
IF SELECT contacttype FROM inserted = 2
BEGIN
...insert code here...
END
END|||Understand, the TRIGGER will always fire. As shown, you want to control the logic inside the trigger.

What action do you need to take?

Just make sure the affected rows use the id of that row as the reason to modify that data|||Thanks for all the replies!

I tried mitchell007's code, and it helped a lot. I am now able to run SQL statements if a certain column is updated.
But I had a problem with the line: IF SELECT contacttype FROM inserted = 2
This results in a parse error. "Incorrect syntax near the keyword SELECT" and "Incorrect syntax near '='."

Here is my trigger code:
CREATE TRIGGER AddContact ON ContactTable
FOR UPDATE
AS
IF UPDATE(contacttype)
BEGIN
IF SELECT contacttype from inserted = 2
BEGIN
print 'contacttype modified!'
END
END

Brett, my ultimate goal is to detect when a row in a contact table changes the value of the 'contacttype' column. If a row is created with contacttype = 2, or if an existing row is updated to that value, I want to create a new row in a different database.

Also, how can I know which row was altered? When all the trigger filters pass, I want to extract the updated row, and insert most of it's data into a different database.|||Okay, I've been working with this for some hours now, and have come a little further, but still have some obstacles to climb over.

First, when I try to use inserted.contacttype to get the value from the updated table, and into my new table, I get a "Error 128: The name 'contacttype' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

This works:
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma VALUES (1,2,3,4)
END

But this doesn't:
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma VALUES (inserted.contacttype,2,3,4)
END

Second, I am still struggeling with getting the trigger to run the INSERT statement only when contacttype changes to a specific number. Right now the INSERT fires when the contacttype field is updated to any value.|||The select statement after the update is not necessary. Try this:


CREATE TRIGGER AddContact ON ContactTable
FOR UPDATE
AS
IF UPDATE(contacttype)
BEGIN
IF inserted.contacttype = 2
BEGIN
<perform needed operations here>
END
END|||Here is the complete trigger I've written so far, with tomh53's suggestion. With the "IF inserted.category_idx = 2" line I get a "Error 107: The column prefix 'inserted' does not match with a table name or alias name used in the query."

CREATE TRIGGER conditionalinsert
ON crm5.contact
FOR UPDATE
AS
IF UPDATE(category_idx)
BEGIN
IF inserted.category_idx = 2
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma SELECT department, contact_id, name, number1, number2, business_idx, orgNr from inserted
END
END|||Scalpel ... my apologies for ** bad ** code. Try this:

CREATE TRIGGER conditionalinsert
ON crm5.contact
FOR UPDATE
AS
IF UPDATE(category_idx)
BEGIN
INSERT INTO tmp_mycoteam.dbo.Firma
SELECT department, contact_id, name, number1, number2, business_idx, orgNr
FROM inserted
WHERE inserted.category_idx = 2
END

Conditional Running of DTS Jobs

We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
However, there are certain specific holidays we don't want to run these jobs
that are stored in a holiday table. Since parameters can't be passed between
job steps, what is the best way for aborting the job on a specific day
without running the DTS job? Is there a way to set a success or failure flag
in jobs and use this flag to abort before running the next job step?
--
Larry Menzin
American Techsystems Corp.Couldn't you check your holiday table in each job step, using sql...for
example...
/*get today*/
select @.today = (select getdate())
/*if today isn't in the holiday table then kick off dts job*/
if not exists (select * from holiday_table where holiday = @.today)
xp_cmdshell 'dtsrun .....blah, blah'
Not sure if my syntax is quite right, and not sure how your dates are
formatted, but something like this should work.|||You could also create a job step as the first step, that checks the date and
fails the job.
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
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:624FC617-C35C-4FDF-95F9-D31C5349C85D@.microsoft.com...
> We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
> However, there are certain specific holidays we don't want to run these
> jobs
> that are stored in a holiday table. Since parameters can't be passed
> between
> job steps, what is the best way for aborting the job on a specific day
> without running the DTS job? Is there a way to set a success or failure
> flag
> in jobs and use this flag to abort before running the next job step?
> --
> Larry Menzin
> American Techsystems Corp.

Conditional Running of DTS Jobs

We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
However, there are certain specific holidays we don't want to run these jobs
that are stored in a holiday table. Since parameters can't be passed between
job steps, what is the best way for aborting the job on a specific day
without running the DTS job? Is there a way to set a success or failure flag
in jobs and use this flag to abort before running the next job step?
Larry Menzin
American Techsystems Corp.
Couldn't you check your holiday table in each job step, using sql...for
example...
/*get today*/
select @.today = (select getdate())
/*if today isn't in the holiday table then kick off dts job*/
if not exists (select * from holiday_table where holiday = @.today)
xp_cmdshell 'dtsrun .....blah, blah'
Not sure if my syntax is quite right, and not sure how your dates are
formatted, but something like this should work.
|||You could also create a job step as the first step, that checks the date and
fails the job.
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
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:624FC617-C35C-4FDF-95F9-D31C5349C85D@.microsoft.com...
> We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
> However, there are certain specific holidays we don't want to run these
> jobs
> that are stored in a holiday table. Since parameters can't be passed
> between
> job steps, what is the best way for aborting the job on a specific day
> without running the DTS job? Is there a way to set a success or failure
> flag
> in jobs and use this flag to abort before running the next job step?
> --
> Larry Menzin
> American Techsystems Corp.

Conditional Running of DTS Jobs

We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
However, there are certain specific holidays we don't want to run these jobs
that are stored in a holiday table. Since parameters can't be passed between
job steps, what is the best way for aborting the job on a specific day
without running the DTS job? Is there a way to set a success or failure flag
in jobs and use this flag to abort before running the next job step?
--
Larry Menzin
American Techsystems Corp.Couldn't you check your holiday table in each job step, using sql...for
example...
/*get today*/
select @.today = (select getdate())
/*if today isn't in the holiday table then kick off dts job*/
if not exists (select * from holiday_table where holiday = @.today)
xp_cmdshell 'dtsrun .....blah, blah'
Not sure if my syntax is quite right, and not sure how your dates are
formatted, but something like this should work.|||You could also create a job step as the first step, that checks the date and
fails the job.
--
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
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:624FC617-C35C-4FDF-95F9-D31C5349C85D@.microsoft.com...
> We have several DTS packages run as SQL Agent jobs using DTSRUN methods.
> However, there are certain specific holidays we don't want to run these
> jobs
> that are stored in a holiday table. Since parameters can't be passed
> between
> job steps, what is the best way for aborting the job on a specific day
> without running the DTS job? Is there a way to set a success or failure
> flag
> in jobs and use this flag to abort before running the next job step?
> --
> Larry Menzin
> American Techsystems Corp.

Conditional Rows (New to reporting services)

I appologize in advance if this is a question that has already been asked. My
problem is this, I am creating a report that will run on a daily basis, and I
need to add weekly total lines. Something along the lines of, if the day is
friday then add the row, which would calculate the totals for the week.
The other option, would be to populate all of the days of the month,
including a week total and month total line every time I run it. Help! I am
stuck here...
Thank you!
lhagertyUse a different dataset with a table or list which fetches the week row? If
it's not friday no row will be returned. Test the number of rows fetched to
determine if you display the table or list
Erik
"lhagerty" wrote:
> I appologize in advance if this is a question that has already been asked. My
> problem is this, I am creating a report that will run on a daily basis, and I
> need to add weekly total lines. Something along the lines of, if the day is
> friday then add the row, which would calculate the totals for the week.
> The other option, would be to populate all of the days of the month,
> including a week total and month total line every time I run it. Help! I am
> stuck here...
> Thank you!
> lhagerty|||Thank you I will try that!
"Erik van der Aar" wrote:
> Use a different dataset with a table or list which fetches the week row? If
> it's not friday no row will be returned. Test the number of rows fetched to
> determine if you display the table or list
> Erik
> "lhagerty" wrote:
> > I appologize in advance if this is a question that has already been asked. My
> > problem is this, I am creating a report that will run on a daily basis, and I
> > need to add weekly total lines. Something along the lines of, if the day is
> > friday then add the row, which would calculate the totals for the week.
> > The other option, would be to populate all of the days of the month,
> > including a week total and month total line every time I run it. Help! I am
> > stuck here...
> > Thank you!
> > lhagerty

Conditional Parameters

Does anyone know if there is a way to have conditional parameters? Ex: User
asked would you like to run report by representative or area? Then when the
user chose representative a mulit select prompt would appear with a list of
representatives to choose from or if they chose area then a multi selection
of areas to choose from would appear.Hi Kim,
Yes it can be done. From your post, you need to create 3 dataset.
1. Which gives you Rep.. or Area
2. Which gives you the list of rep for selection (multi selection)
3. Once you select it displayes the result.
On 2 & 3 data option give an if condition accordingly
e.g
If @.rep = 1
select * from rep
else
select * from Area
Any doubts, Let me know.
Amarnath
"KimB" wrote:
> Does anyone know if there is a way to have conditional parameters? Ex: User
> asked would you like to run report by representative or area? Then when the
> user chose representative a mulit select prompt would appear with a list of
> representatives to choose from or if they chose area then a multi selection
> of areas to choose from would appear.|||Thank you! I will try that.
"Amarnath" wrote:
> Hi Kim,
> Yes it can be done. From your post, you need to create 3 dataset.
> 1. Which gives you Rep.. or Area
> 2. Which gives you the list of rep for selection (multi selection)
> 3. Once you select it displayes the result.
> On 2 & 3 data option give an if condition accordingly
> e.g
> If @.rep = 1
> select * from rep
> else
> select * from Area
> Any doubts, Let me know.
> Amarnath
>
> "KimB" wrote:
> > Does anyone know if there is a way to have conditional parameters? Ex: User
> > asked would you like to run report by representative or area? Then when the
> > user chose representative a mulit select prompt would appear with a list of
> > representatives to choose from or if they chose area then a multi selection
> > of areas to choose from would appear.

Conditional Formatting - not so conditional?

I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.

=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")

I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.

Is it because this is a matrix report? What am I doing wrong?

Thanks in advance . . .What part of the matrix do you have this in, and what values show in the textbox?|||OK, well you pointed me right to my problem. The values are percentages. I was doing my conditional formatting based on 3 instead of .03 . . .

That is fixed but I still have something else wrong. A very small number of values that are not .03 are coloring red. I am also getting a warning that says "[rsNonAggregateInMatrixCell] The Color expression for the textbox ‘textbox22’ references a field outside an aggregate function. Value expressions in matrix cells should be aggregates, to allow for subtotaling."

I assume this message is related to my problem.

Here is a sample of what the output is looking like. All of the values formatted correctly except for the 2.60 %. The 2.60% was colored red as if it was >= .03. I know you can't see the colors here:

WRAP

3.50 %

1.22 %

1.15 %

3.13 %

2.43 %

2.60 %

14.21 %

0.00 %

8.41 %

6.14 %

5.23 %

5.23 %

2.42 %

2.42 %

1.87 %

1.87 %

2.39 %

2.39 %

4.17 %

4.17 %

3.15 %

|||

A matrix cell is always in the intersection of a row and a column group. Usually, several detail rows will go into the same matrix cell instance based on the row/column grouping. That's the reason why you get the warning. By just referencing a field without aggregate function (=iif(Fields!Wrap.Value >= 0.03 , "Red", "Green") ), you reference just the field value of the first row in that cell - while the textbox in the cell may actually show the aggregate of the values.

You should use the following color expression instead: =iif(Sum(Fields!Wrap.Value) >= 0.03 , "Red", "Green")

-- Robert

|||I made the change that you suggested. Which eliminates the warning that I was receiving. However, I am still getting inconsistent results with the formatting. For instance, of the following values only 5.09, and the two 0.00's are colored green.

Could it be because I have enabled drill-down in this report? Even if I drill down to the lowest level the colors still are not consistently populating based on the conditions in this formula.

WRAP

5.09 %

2.73 %

0.00 %

0.00 %

1.77 %

2.55 %

3.61 %

6.68 %

3.02 %

3.50 %

2.86 %

Conditional execution of DTS package task

I've searched everywhere for this but can't find the answer

I want to run a DTS package that simply executes a SQL statement to get
a count of rows in a table, if the resulting number is greater than
zero I want to execute another step in the same package, else just
quit.

I don't to pass global vars from one package to another

How can I do this?

Any help would be greatHi Scouser,

I've got a suggestion: Create a stored proc that does the count of the
records and then execute the DTS from the procedure, if the condition
is true.
Look at this message if you want to see how to execute the DTS-Package
from a stored procedure:
http://groups.google.de/group/borla...62580101f3 8fe

Michael
www.zankl-it.de

Conditional AND statement in stored procedure Select query?

This should be simple but I can't figure it out. So I have a select query an
d
I want to run a conditional AND if a value is not NULL. Here is the type of
statment I want to run.
-- Declared values not shown.
SELECT * FROM MyTABLE
WHERE city = @.city
AND department = @.department
IF @.statusCode is NOT NULL
AND status IN (SELECT Item FROM TsqlSplit(@.statusCode) TsqlSplit)
END
-- The TsqlSplit function simply takes in a string of statusCodes "1,4,6"
and splits them out for the IN clause. It works fine if a string is actually
passed but I simply want to execute the IN clause if the @.statusCode value
passed to the procedure is not NULL.
I looked at the WHEN THEN clause but that only seems to work for equalities
AND status = WHEN @.statusCode IS NULL THEN ...
Thanks folks.SQL will short-circuit when the first part of an OR condition fails, so
this should work (performance may suffer though)
...
and (@.statusCode is null
or status in (select item from dbo.tsqlSplit(@.statusCode))
another option is to default the @.statusCode variable to the list of
possible status codes. then you won't have to check if @.statusCode is null.
Ramez wrote:
> This should be simple but I can't figure it out. So I have a select query
and
> I want to run a conditional AND if a value is not NULL. Here is the type o
f
> statment I want to run.
> -- Declared values not shown.
> SELECT * FROM MyTABLE
> WHERE city = @.city
> AND department = @.department
> IF @.statusCode is NOT NULL
> AND status IN (SELECT Item FROM TsqlSplit(@.statusCode) TsqlSplit)
> END
> -- The TsqlSplit function simply takes in a string of statusCodes "1,4,6"
> and splits them out for the IN clause. It works fine if a string is actual
ly
> passed but I simply want to execute the IN clause if the @.statusCode value
> passed to the procedure is not NULL.
> I looked at the WHEN THEN clause but that only seems to work for equalitie
s
> AND status = WHEN @.statusCode IS NULL THEN ...
> Thanks folks.

condensing log files

I need to set up an automated way to condense log files in a database. I
successfully got a "dbcc shrinkdatabase" command to run in TranSQL, but I
was thinking that if I can create a stored proc that runs through all the
tables in a dabase, I could schedule that to run and it would always keep my
log file sizes down. So I wrote code, but of course the first parameter of
the "dbcc shrinkdatabase" command requires a 'table object' and that is my
problem. How can I write the code to use the table names as table objects
instead of varchar vairables? Here is the code that I have so far:
declare @.name as varchar(50)
create table #tableNames
(name varchar(50))
insert into #tableNames select TABLE_NAME from INFORMATION_SCHEMA.TABLES
while select count(*) from #tableNames > 0
BEGIN
@.name = select top 1 from #tableNames
dbcc shrinkdatabase (@.name, truncateonly)
delete from #tableNames where name = @.name
END
drop table #tableNames
Scott Schuman
Medical AssociatesThere are undocumented stored procedures designed to execute dynamic SQL
against databases and tables:
Undocumented sp_MSforeachdb and sp_MSforeachtable Stored Procedures
http://www.dbazine.com/sql/sql-articles/larsen5
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:OnHXUDD$FHA.1548@.TK2MSFTNGP10.phx.gbl...
>I need to set up an automated way to condense log files in a database. I
> successfully got a "dbcc shrinkdatabase" command to run in TranSQL, but I
> was thinking that if I can create a stored proc that runs through all the
> tables in a dabase, I could schedule that to run and it would always keep
> my
> log file sizes down. So I wrote code, but of course the first parameter
> of
> the "dbcc shrinkdatabase" command requires a 'table object' and that is my
> problem. How can I write the code to use the table names as table objects
> instead of varchar vairables? Here is the code that I have so far:
> declare @.name as varchar(50)
> create table #tableNames
> (name varchar(50))
> insert into #tableNames select TABLE_NAME from INFORMATION_SCHEMA.TABLES
> while select count(*) from #tableNames > 0
> BEGIN
> @.name = select top 1 from #tableNames
> dbcc shrinkdatabase (@.name, truncateonly)
> delete from #tableNames where name = @.name
> END
> drop table #tableNames
> Scott Schuman
> Medical Associates
>|||Schoo (scott.schuman@.nospam.ma-hc.com) writes:
> I need to set up an automated way to condense log files in a database.
> I successfully got a "dbcc shrinkdatabase" command to run in TranSQL,
> but I was thinking that if I can create a stored proc that runs through
> all the tables in a dabase, I could schedule that to run and it would
> always keep my log file sizes down. So I wrote code, but of course the
> first parameter of the "dbcc shrinkdatabase" command requires a 'table
> object' and that is my problem. How can I write the code to use the
> table names as table objects instead of varchar vairables? Here is the
> code that I have so far:
It seems to me to be a poor idea. Shrinking is good if a file has
exploded because a of some special operation. But shrinking the log
regularly is meaningless, the log will grow again, and that will cost
you performance.
Also read this article from Tibor Karazsi's site:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp.
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

Concurrency (recap and idea).....

With respect to my (now not so recent) thread on Concurrency, I would like
to run my idea past you gurus to see if its a runner. First, a brief recap:

I have a single user system (one user, one copy of the software, one copy of
MSDE, one machine) that I wish to convert into a multi-user/single database
networked system. The problem I had was that a lot of information is
fetched from the database and cached in the client program (the program
implements a tree structure, similar to a file system, and each of the nodes
in the system has properties). The concurrency issue revolved around having
multiple users updating these properties and possibly able to modify the
tree structure and there being no way to notify the other clients that they
need to refresh their data structures. Consider the system to be similar to
VSS to look at (and in VSS, people can make modifications to the tree
structure also!).

Ok, one of the suggestions was time stamping each record. So, when one user
modifies the record, a second user can detect whether their timestamp is
different and thus whether or not their update is invalid (and also whether
or not the client program needs to refresh the properties of the given
node). How about instead of a timestamp I simply use a reference counter.
ie. an integer that increments every time the record is modified (same
principle). I don't need to know when it was changed, just that the two
reference counters are different between when I fetched and when I am
updating the record.

Secondly, I think I have to distinguish between a change in properties and a
change in structure. For example, User A doesn't need to know about a
change in properties for a node he is not currently looking at. However,
that same user will want to be told about any change to the overall tree
structure. So, I was thinking that any operations involving modifications
to the tree structure should set a "structure changed" flag in the database
(increment a counter). After any operation is performed, the client
compares its "changed" flag to the database value to see if it needs to
reload the tree structure.

Do you think this is workable?

Thanks.

RobinRegarding your first question, this is known as optimistic concurrency and
is usually implemented with a timestamp data type, not to be confused with a
datetime column. The timestamp datatype contains a binary value unrelated
to date/time and is automatically updated by SQL Server whenever the row is
updated. Consequently, you can use a technique like:

UPDATE MyTable
SET MyData = @.NewMyDataValue
WHERE MyID = @.MyID AND
MyTimestamp = @.OldMyTimestamp
IF @.@.ROWCOUNT = 0
BEGIN
RAISERROR ('Data has been deleted or updated by another user', 16, 1)
END

You can use a similar technique to detect structure changes to your tree.
Save the current timestamp value in a program variable and check
periodically as desired.

SELECT LastUpdatedByUser, LastUpdateTime, TreeTimeStamp
FROM MyTrees
WHERE MyTreeID = 1

The above table can be updated as follows. You can also include this in
triggers or procs for convenience, depending on how you perform data
manipulation in your app.

UPDATE MyTrees
SET LastUpdatedByUser = SUSER_SNAME(),
LastUpdateTime = CURRENT_TIMESTAMP
WHERE MyTreeID = 1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:cp6lcq$ns4$1$8302bc10@.news.demon.co.uk...
> With respect to my (now not so recent) thread on Concurrency, I would like
> to run my idea past you gurus to see if its a runner. First, a brief
> recap:
> I have a single user system (one user, one copy of the software, one copy
> of MSDE, one machine) that I wish to convert into a multi-user/single
> database networked system. The problem I had was that a lot of
> information is fetched from the database and cached in the client program
> (the program implements a tree structure, similar to a file system, and
> each of the nodes in the system has properties). The concurrency issue
> revolved around having multiple users updating these properties and
> possibly able to modify the tree structure and there being no way to
> notify the other clients that they need to refresh their data structures.
> Consider the system to be similar to VSS to look at (and in VSS, people
> can make modifications to the tree structure also!).
> Ok, one of the suggestions was time stamping each record. So, when one
> user modifies the record, a second user can detect whether their timestamp
> is different and thus whether or not their update is invalid (and also
> whether or not the client program needs to refresh the properties of the
> given node). How about instead of a timestamp I simply use a reference
> counter. ie. an integer that increments every time the record is modified
> (same principle). I don't need to know when it was changed, just that the
> two reference counters are different between when I fetched and when I am
> updating the record.
> Secondly, I think I have to distinguish between a change in properties and
> a change in structure. For example, User A doesn't need to know about a
> change in properties for a node he is not currently looking at. However,
> that same user will want to be told about any change to the overall tree
> structure. So, I was thinking that any operations involving modifications
> to the tree structure should set a "structure changed" flag in the
> database (increment a counter). After any operation is performed, the
> client compares its "changed" flag to the database value to see if it
> needs to reload the tree structure.
>
> Do you think this is workable?
>
> Thanks.
>
> Robin|||Thanks, this is useful stuff.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:rHCtd.38154$bP2.3402@.newssvr12.news.prodigy.c om...
> Regarding your first question, this is known as optimistic concurrency and
> is usually implemented with a timestamp data type, not to be confused with
> a datetime column. The timestamp datatype contains a binary value
> unrelated to date/time and is automatically updated by SQL Server whenever
> the row is updated. Consequently, you can use a technique like:
> UPDATE MyTable
> SET MyData = @.NewMyDataValue
> WHERE MyID = @.MyID AND
> MyTimestamp = @.OldMyTimestamp
> IF @.@.ROWCOUNT = 0
> BEGIN
> RAISERROR ('Data has been deleted or updated by another user', 16, 1)
> END
> You can use a similar technique to detect structure changes to your tree.
> Save the current timestamp value in a program variable and check
> periodically as desired.
> SELECT LastUpdatedByUser, LastUpdateTime, TreeTimeStamp
> FROM MyTrees
> WHERE MyTreeID = 1
> The above table can be updated as follows. You can also include this in
> triggers or procs for convenience, depending on how you perform data
> manipulation in your app.
> UPDATE MyTrees
> SET LastUpdatedByUser = SUSER_SNAME(),
> LastUpdateTime = CURRENT_TIMESTAMP
> WHERE MyTreeID = 1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:cp6lcq$ns4$1$8302bc10@.news.demon.co.uk...
>> With respect to my (now not so recent) thread on Concurrency, I would
>> like to run my idea past you gurus to see if its a runner. First, a
>> brief recap:
>>
>> I have a single user system (one user, one copy of the software, one copy
>> of MSDE, one machine) that I wish to convert into a multi-user/single
>> database networked system. The problem I had was that a lot of
>> information is fetched from the database and cached in the client program
>> (the program implements a tree structure, similar to a file system, and
>> each of the nodes in the system has properties). The concurrency issue
>> revolved around having multiple users updating these properties and
>> possibly able to modify the tree structure and there being no way to
>> notify the other clients that they need to refresh their data structures.
>> Consider the system to be similar to VSS to look at (and in VSS, people
>> can make modifications to the tree structure also!).
>>
>> Ok, one of the suggestions was time stamping each record. So, when one
>> user modifies the record, a second user can detect whether their
>> timestamp is different and thus whether or not their update is invalid
>> (and also whether or not the client program needs to refresh the
>> properties of the given node). How about instead of a timestamp I simply
>> use a reference counter. ie. an integer that increments every time the
>> record is modified (same principle). I don't need to know when it was
>> changed, just that the two reference counters are different between when
>> I fetched and when I am updating the record.
>>
>> Secondly, I think I have to distinguish between a change in properties
>> and a change in structure. For example, User A doesn't need to know
>> about a change in properties for a node he is not currently looking at.
>> However, that same user will want to be told about any change to the
>> overall tree structure. So, I was thinking that any operations involving
>> modifications to the tree structure should set a "structure changed" flag
>> in the database (increment a counter). After any operation is performed,
>> the client compares its "changed" flag to the database value to see if it
>> needs to reload the tree structure.
>>
>>
>> Do you think this is workable?
>>
>>
>> Thanks.
>>
>>
>>
>> Robin
>>

Concatenation

I am attempting to run the following sql:
select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
case st.name
when 'varchar' then sc.length
when 'char' then sc.length
when 'numeric' then convert(varchar(10),sc.xprec) + ',' + convert(varchar(10),
sc.xscale)
else sc.length
end as size
from sysobjects so, syscolumns sc, systypes st
where so.xtype = 'U'
and so.name <> 'dtproperties'
and so.id = sc.id
and sc.xtype = st.xtype
order by so.name, sc.name, st.name
I get an error stating:
Syntax error converting the varchar value '18,2' to a column of data type
smallint.
How can I rewrite the concatenation to allow the comma to join the numbers?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1Robert,
Try:
select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
case st.name
when 'varchar' then CONVERT(VARCHAR(20),sc.length)
when 'char' then CONVERT(VARCHAR(20),sc.length)
when 'numeric' then convert(varchar(10),sc.xprec) + '.' +
convert(varchar(10),sc.xscale)else CONVERT(VARCHAR(20),sc.length) end as
size
from sysobjects so, syscolumns sc, systypes st
where so.xtype = 'U'
and so.name <> 'dtproperties'
and so.id = sc.id
and sc.xtype = st.xtype
order by so.name, sc.name, st.name
HTH
Jerry
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:55d586276a600@.uwe...
>I am attempting to run the following sql:
> select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
> case st.name
> when 'varchar' then sc.length
> when 'char' then sc.length
> when 'numeric' then convert(varchar(10),sc.xprec) + ',' +
> convert(varchar(10),
> sc.xscale)
> else sc.length
> end as size
> from sysobjects so, syscolumns sc, systypes st
> where so.xtype = 'U'
> and so.name <> 'dtproperties'
> and so.id = sc.id
> and sc.xtype = st.xtype
> order by so.name, sc.name, st.name
> I get an error stating:
> Syntax error converting the varchar value '18,2' to a column of data type
> smallint.
> How can I rewrite the concatenation to allow the comma to join the
> numbers?
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200510/1

Concatenation

I am attempting to run the following sql:
select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
case st.name
when 'varchar' then sc.length
when 'char' then sc.length
when 'numeric' then convert(varchar(10),sc.xprec) + ',' + convert(varchar(10
),
sc.xscale)
else sc.length
end as size
from sysobjects so, syscolumns sc, systypes st
where so.xtype = 'U'
and so.name <> 'dtproperties'
and so.id = sc.id
and sc.xtype = st.xtype
order by so.name, sc.name, st.name
I get an error stating:
Syntax error converting the varchar value '18,2' to a column of data type
smallint.
How can I rewrite the concatenation to allow the comma to join the numbers?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200510/1Robert,
Try:
select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
case st.name
when 'varchar' then CONVERT(VARCHAR(20),sc.length)
when 'char' then CONVERT(VARCHAR(20),sc.length)
when 'numeric' then convert(varchar(10),sc.xprec) + '.' +
convert(varchar(10),sc.xscale)else CONVERT(VARCHAR(20),sc.length) end as
size
from sysobjects so, syscolumns sc, systypes st
where so.xtype = 'U'
and so.name <> 'dtproperties'
and so.id = sc.id
and sc.xtype = st.xtype
order by so.name, sc.name, st.name
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d586276a600@.uwe...
>I am attempting to run the following sql:
> select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
> case st.name
> when 'varchar' then sc.length
> when 'char' then sc.length
> when 'numeric' then convert(varchar(10),sc.xprec) + ',' +
> convert(varchar(10),
> sc.xscale)
> else sc.length
> end as size
> from sysobjects so, syscolumns sc, systypes st
> where so.xtype = 'U'
> and so.name <> 'dtproperties'
> and so.id = sc.id
> and sc.xtype = st.xtype
> order by so.name, sc.name, st.name
> I get an error stating:
> Syntax error converting the varchar value '18,2' to a column of data type
> smallint.
> How can I rewrite the concatenation to allow the comma to join the
> numbers?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200510/1

Concatenation

I am attempting to run the following sql:
select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
case st.name
when 'varchar' then sc.length
when 'char' then sc.length
when 'numeric' then convert(varchar(10),sc.xprec) + ',' + convert(varchar(10),
sc.xscale)
else sc.length
end as size
from sysobjects so, syscolumns sc, systypes st
where so.xtype = 'U'
and so.name <> 'dtproperties'
and so.id = sc.id
and sc.xtype = st.xtype
order by so.name, sc.name, st.name
I get an error stating:
Syntax error converting the varchar value '18,2' to a column of data type
smallint.
How can I rewrite the concatenation to allow the comma to join the numbers?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200510/1
Robert,
Try:
select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
case st.name
when 'varchar' then CONVERT(VARCHAR(20),sc.length)
when 'char' then CONVERT(VARCHAR(20),sc.length)
when 'numeric' then convert(varchar(10),sc.xprec) + '.' +
convert(varchar(10),sc.xscale)else CONVERT(VARCHAR(20),sc.length) end as
size
from sysobjects so, syscolumns sc, systypes st
where so.xtype = 'U'
and so.name <> 'dtproperties'
and so.id = sc.id
and sc.xtype = st.xtype
order by so.name, sc.name, st.name
HTH
Jerry
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:55d586276a600@.uwe...
>I am attempting to run the following sql:
> select so.name as 'Table_Name', sc.name as 'Col_Name', st.name,
> case st.name
> when 'varchar' then sc.length
> when 'char' then sc.length
> when 'numeric' then convert(varchar(10),sc.xprec) + ',' +
> convert(varchar(10),
> sc.xscale)
> else sc.length
> end as size
> from sysobjects so, syscolumns sc, systypes st
> where so.xtype = 'U'
> and so.name <> 'dtproperties'
> and so.id = sc.id
> and sc.xtype = st.xtype
> order by so.name, sc.name, st.name
> I get an error stating:
> Syntax error converting the varchar value '18,2' to a column of data type
> smallint.
> How can I rewrite the concatenation to allow the comma to join the
> numbers?
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200510/1

concatenating sql fields and parameters

Hi,
I am trying to run a update stored procedure where one of the fields is
dynamic eg.
UPDATE table
SET field_ + @.number = @.a_value
WHERE (key = @.key_value)
@.number is chosen by the user and the field_## can be anything from field_01
to field_99.
Is there a way i can do this? The above method doesn’t work.
Any help will be greatly appreciated.
Many ThanksIn t-SQL, you will have to use each columns explcitly in the SET clause,
with commas separating each column assignments. For syntax, refer to the
topic UPDATE in SQL Server Books Online.
Perhaps with Dynamic SQL you might be able to kludge it out. For details,
refer to EXEC & sp_ExecuteSQL in SQL Server Books Online. On a side note, it
is possible that you have a flawed design which force you to use such
meaningless constructs in your code.
Anith|||Hi Vortex
consider rewriting as:
EXECUTE('UPDATE table SET field_' + @.number + ' = ' +@.a_value + ' WHERE
key = ' + @.key_value)
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"vortex" wrote:

> Hi,
> I am trying to run a update stored procedure where one of the fields is
> dynamic eg.
> UPDATE table
> SET field_ + @.number = @.a_value
> WHERE (key = @.key_value)
> @.number is chosen by the user and the field_## can be anything from field_
01
> to field_99.
> Is there a way i can do this? The above method doesn’t work.
> Any help will be greatly appreciated.
> Many Thanks
>|||I will give it a try,
I am creating a stored procedure with your update command, if I use the
method you suggested will SQL have to compile the sp every time a new value
is used or will it just compile the once. (Speed is required, that is why I
am using a sp)
Thanks
Khalid
"Chandra" wrote:
> Hi Vortex
> consider rewriting as:
> EXECUTE('UPDATE table SET field_' + @.number + ' = ' +@.a_value + ' WHERE
> key = ' + @.key_value)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> ---
>
> "vortex" wrote:
>|||If you can suggest a better way of doing it, i would be a very happy bunny a
s
i have a lot more stored procedures to write :(
thanks
"Anith Sen" wrote:

> In t-SQL, you will have to use each columns explcitly in the SET clause,
> with commas separating each column assignments. For syntax, refer to the
> topic UPDATE in SQL Server Books Online.
>
> Perhaps with Dynamic SQL you might be able to kludge it out. For details,
> refer to EXEC & sp_ExecuteSQL in SQL Server Books Online. On a side note,
it
> is possible that you have a flawed design which force you to use such
> meaningless constructs in your code.
> --
> Anith
>
>|||It is not as ease as it seems. For example, Chandra's solution will fail if
@.number is tinyint/int/bigint because you can not those data types have
greater precedence than varchar so sql server will try to convert 'UPDATE
table SET field_' to tinyint/int/bigint and this will give an error. The sam
e
will happen @.a_value, you have to quote it between apostrophes for char /
varchar / datetime values. The same with @.key_value. You will have to use
dynamic sql and bunch on lines to accomodate the statement to the variables
data type.
I will not write about readability and maintenance of your final code, you
can guess what will be the result.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"vortex" wrote:

> I will give it a try,
> I am creating a stored procedure with your update command, if I use the
> method you suggested will SQL have to compile the sp every time a new valu
e
> is used or will it just compile the once. (Speed is required, that is why
I
> am using a sp)
> Thanks
> Khalid
> "Chandra" wrote:
>
>|||>> If you can suggest a better way of doing it,..
Better way of doing an UPDATE or changing the design? Regarding the UPDATE,
did you refer to the manual for exact syntax?
Regarding the design, with simple one-liners as in your initial post, it is
hard to suggest anything meaningful. Post some more information regarding
this table, the entity type that is being modelled and the attributes
involved. Also provide some details regarding the business model and how
this table fits into the overall schema.
Generally it is hard to provide accurate design suggestions over newsgroup
responses, however with the above requested info, you could perhaps get
started.
Anith