Concatenating fields: following

Showing posts with label following. Show all posts

Thursday, March 29, 2012

Configure Integration Services on a Cluster

We are attempting to setup Integration Services on a Cluster per the
following article:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
We have the following Cluster Groups/resources
"Cluster Group"
Disk Q - Quorum
Disk R - MSDTC
MSDTC
MSDTC IP Address
Cluster Name
Cluster IP
"SQL1 Group"
Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)
SQL1 Integration Services
"SQL2 Group"
Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2)
SQL Server Agent (SQL2)
SQL Full Text (SQL2)
SQL2 Integration Services
"SQL3 Group"
Disk W - Data Files
Disk X- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL3)
SQL Server Agent (SQL3)
SQL Full Text (SQL3)
SQL3 Integration Services
The problem is that the integration services resources in the various
cluster groups won't come online on all nodes - only certain nodes. We're
getting an error indicating:
An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]
Does anyone know how this should be setup? Should we have seperate
integration services resources in every group or only one group? Why are we
getting the error mentioned above?
Thanks,
BradOps I just realized there is a seperate group for SQL Server Clustering.
Sorry for the cross posting!
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:%23oc4RZc7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> We are attempting to setup Integration Services on a Cluster per the
> following article:
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> We have the following Cluster Groups/resources
> "Cluster Group"
> Disk Q - Quorum
> Disk R - MSDTC
> MSDTC
> MSDTC IP Address
> Cluster Name
> Cluster IP
> "SQL1 Group"
> Disk S - Data Files
> Disk T - Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL1)
> SQL Server Agent (SQL1)
> SQL Full Text (SQL1)
> SQL1 Integration Services
> "SQL2 Group"
> Disk U - Data Files
> Disk V- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL2)
> SQL Server Agent (SQL2)
> SQL Full Text (SQL2)
> SQL2 Integration Services
> "SQL3 Group"
> Disk W - Data Files
> Disk X- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL3)
> SQL Server Agent (SQL3)
> SQL Full Text (SQL3)
> SQL3 Integration Services
> The problem is that the integration services resources in the various
> cluster groups won't come online on all nodes - only certain nodes. We're
> getting an error indicating:
> An error occured attempting to read properties for the 'xxx Integration
> Services' resource.
> The object already exists.
> Error ID: 5010 (00001392)
> Do you want to ignore this error and continue
> [yes] [yes to all] [no]
> Does anyone know how this should be setup? Should we have seperate
> integration services resources in every group or only one group? Why are
> we getting the error mentioned above?
> Thanks,
> Brad
>|||Hello Brad,
I understand that you are not able to configure cluster resource for SSIS
properly. As I know, SSIS is not cluster-aware, so you'll have to install
on all nodes. However, SSIS can be installed within a virtual server on a
failover cluster as described here:
http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
That's much the way Analysis Services was done in 2000 -make it a generic
resource. It still requires installing the stuff local on each node.
Hope this is helpful. Please let's know if you have further questions or
concerns.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||This works as advertised...
SQL Server 2005 Books Online
How to: Configure Integration Services on a Cluster
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
Terry Duffy
Frontline DBA
Peter Yang [MSFT] wrote:
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================|||Terry -
We followed the instructions in the article you mentioned. (Thats the same
link I included in my original post). However failover functionality does
not seem to be working right in all cases.
I have to assume we did something wrong I'm just not sure what we missed or
misunderstood. Do you have any idea if there should be an instances of the
Integration Services per SQL virtual server or one instance for tht cluster
total?
Brad
"Terry" <tduffy@.calamos.com> wrote in message
news:1160658501.995672.58180@.k70g2000cwa.googlegroups.com...
> This works as advertised...
> SQL Server 2005 Books Online
> How to: Configure Integration Services on a Cluster
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> Terry Duffy
> Frontline DBA
> Peter Yang [MSFT] wrote:
>> Hello Brad,
>> I understand that you are not able to configure cluster resource for SSIS
>> properly. As I know, SSIS is not cluster-aware, so you'll have to install
>> on all nodes. However, SSIS can be installed within a virtual server on a
>> failover cluster as described here:
>> http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
>> That's much the way Analysis Services was done in 2000 -make it a generic
>> resource. It still requires installing the stuff local on each node.
>> Hope this is helpful. Please let's know if you have further questions or
>> concerns.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>>
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> ======================================================>|||Peter -
Thanks for the link. Unfortunately its much the same information as the link
in my original post except with pictures. I've loaded SSIS on all nodes so I
don't believe that's a problem. I'm still a little unclear if we have a 4
node active/active/active/passive cluster with three SQL virtual servers -
do we have three SSIS cluster resources (one for each sql virtual server) or
just one SSIS cluster resource? Maybe I will go back and try to start from
scratch and see if that makes any difference. Any additional suggestions you
might have would be appreciated.
Thanks
Brad
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:UY5GxTe7GHA.4348@.TK2MSFTNGXA01.phx.gbl...
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hello Brad,
Since SSIS does not support multiple instances, only a single resource is
necessary. You need to remove redudent ones to test the sitaution. Also, it
is suggested that you install it a seperate group since you have mauliple
instances installed. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Configure Distributor error

I am trying to "Configure Distributor" prior to setting up the publications. When I go through the wizard, at the very last step I get the following error:

******************************************************************************************

SQL Server could not configure 'serverName' as a Distributor.

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot find the principal 'sa', because it does not exist or you do not have permission.
'distribution' is not configured as a distribution database.
Changed database context to 'master'. (Microsoft SQL Server, Error: 15151)

******************************************************************************************

I have renamed the 'sa' account. Is there a place I need to change it to the new name for the distributor to work? Thanks.

Currently in SQL Server 2005, replication doesn't support renamed sa login. To make it work, you must change renamed sa login name back to 'sa'. However, this is a valid request and we will consider it in SQL Server 2005 service pack or next release of SQL Server.

Thanks for reporting the problem.

Zhiqiang

This posting is provided "AS IS" with no warranties, and confers no rights.

Configurations like ANSI_WARNINGS

Recently I executed the MS Best Practices Analyzer in my test server, and
the report analysis alerted to me to configure the following options for
true:
ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT,
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER to ON and NUMERIC_ROUNDABOUT to
off.
But I'm with this doubts:
If I configure these options using sp_configure 'user options', I imagine
that all my client connection goes to be configured for this configuration,
unless the client uses any SET xxx.
If I configure these options in each database with sp_dboption, when they
would have effect? They go bypass the sp_confgure configurations?
Is there some rule to help me decide if I must or it does not qualify them?
Thanks
Esio Nunes
Hi,
sp_dboption changes settings for a database.
sp_configure to change server-level settings, and the SET statement to
change settings that affect only the current session.
Thanks
Yogish
|||OK, but what means with de sp_dboption? When this setting will be applied?
Esio
"Yogish" <yogishkamathg@.icqmail.com> escreveu na mensagem
news:068B7FE1-77FF-44D6-A596-2CD81CEA01D8@.microsoft.com...
> Hi,
> sp_dboption changes settings for a database.
> sp_configure to change server-level settings, and the SET statement to
> change settings that affect only the current session.
> --
> Thanks
> Yogish
|||Hi Esio,
Lets assume, you have 5 database in your SQL Server. When you use
sp_configure, it will apply to all the 5 database. And when you use
sp_dboption it will apply to a specific database (one of the 5).
HTH
Thanks
yogish
|||I understand.
So, I have db_a and db_b. Both are with ansi warnings off.
I change with sp_dboption 'db_a','ansi warnings','on' the configuration.
Tthe user conect to db_b (with no config changed), so the ansi warning is
off, if the user change to db_a then ansi warning will be "changed" to on?
"Yogish" <yogishkamathg@.icqmail.com> escreveu na mensagem
news:353F32BC-E3D6-4B46-ACC6-1D223512E1CF@.microsoft.com...
> Hi Esio,
> Lets assume, you have 5 database in your SQL Server. When you use
> sp_configure, it will apply to all the 5 database. And when you use
> sp_dboption it will apply to a specific database (one of the 5).
> HTH
> --
> Thanks
> yogish
|||That's correct.
Thanks
Yogish
sql sql

Configurations like ANSI_WARNINGS

Recently I executed the MS Best Practices Analyzer in my test server, and
the report analysis alerted to me to configure the following options for
true:
ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT,
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER to ON and NUMERIC_ROUNDABOUT to
off.
But I'm with this doubts:
If I configure these options using sp_configure 'user options', I imagine
that all my client connection goes to be configured for this configuration,
unless the client uses any SET xxx.
If I configure these options in each database with sp_dboption, when they
would have effect? They go bypass the sp_confgure configurations?
Is there some rule to help me decide if I must or it does not qualify them?
Thanks
Esio NunesHi,
sp_dboption changes settings for a database.
sp_configure to change server-level settings, and the SET statement to
change settings that affect only the current session.
Thanks
Yogish|||OK, but what means with de sp_dboption? When this setting will be applied?
Esio
"Yogish" <yogishkamathg@.icqmail.com> escreveu na mensagem
news:068B7FE1-77FF-44D6-A596-2CD81CEA01D8@.microsoft.com...
> Hi,
> sp_dboption changes settings for a database.
> sp_configure to change server-level settings, and the SET statement to
> change settings that affect only the current session.
> --
> Thanks
> Yogish|||Hi Esio,
Lets assume, you have 5 database in your SQL Server. When you use
sp_configure, it will apply to all the 5 database. And when you use
sp_dboption it will apply to a specific database (one of the 5).
HTH
--
Thanks
yogish|||I understand.
So, I have db_a and db_b. Both are with ansi warnings off.
I change with sp_dboption 'db_a','ansi warnings','on' the configuration.
Tthe user conect to db_b (with no config changed), so the ansi warning is
off, if the user change to db_a then ansi warning will be "changed" to on?
"Yogish" <yogishkamathg@.icqmail.com> escreveu na mensagem
news:353F32BC-E3D6-4B46-ACC6-1D223512E1CF@.microsoft.com...
> Hi Esio,
> Lets assume, you have 5 database in your SQL Server. When you use
> sp_configure, it will apply to all the 5 database. And when you use
> sp_dboption it will apply to a specific database (one of the 5).
> HTH
> --
> Thanks
> yogish|||That's correct.
Thanks
Yogish

Configurations like ANSI_WARNINGS

Recently I executed the MS Best Practices Analyzer in my test server, and
the report analysis alerted to me to configure the following options for
true:
ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT,
CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER to ON and NUMERIC_ROUNDABOUT to
off.
But I'm with this doubts:
If I configure these options using sp_configure 'user options', I imagine
that all my client connection goes to be configured for this configuration,
unless the client uses any SET xxx.
If I configure these options in each database with sp_dboption, when they
would have effect? They go bypass the sp_confgure configurations?
Is there some rule to help me decide if I must or it does not qualify them?
Thanks
Esio NunesHi,
sp_dboption changes settings for a database.
sp_configure to change server-level settings, and the SET statement to
change settings that affect only the current session.
--
Thanks
Yogish|||OK, but what means with de sp_dboption? When this setting will be applied?
Esio
"Yogish" <yogishkamathg@.icqmail.com> escreveu na mensagem
news:068B7FE1-77FF-44D6-A596-2CD81CEA01D8@.microsoft.com...
> Hi,
> sp_dboption changes settings for a database.
> sp_configure to change server-level settings, and the SET statement to
> change settings that affect only the current session.
> --
> Thanks
> Yogish|||Hi Esio,
Lets assume, you have 5 database in your SQL Server. When you use
sp_configure, it will apply to all the 5 database. And when you use
sp_dboption it will apply to a specific database (one of the 5).
HTH
--
Thanks
yogish|||I understand.
So, I have db_a and db_b. Both are with ansi warnings off.
I change with sp_dboption 'db_a','ansi warnings','on' the configuration.
Tthe user conect to db_b (with no config changed), so the ansi warning is
off, if the user change to db_a then ansi warning will be "changed" to on?
"Yogish" <yogishkamathg@.icqmail.com> escreveu na mensagem
news:353F32BC-E3D6-4B46-ACC6-1D223512E1CF@.microsoft.com...
> Hi Esio,
> Lets assume, you have 5 database in your SQL Server. When you use
> sp_configure, it will apply to all the 5 database. And when you use
> sp_dboption it will apply to a specific database (one of the 5).
> HTH
> --
> Thanks
> yogish|||That's correct.
--
Thanks
Yogish

Configurations error

Please help I donâ't have idea how to start!
The report server worked well. Now I have the following configurations error:
=== Pre-bind state information === LOG: DisplayName = ReportingServicesWebUserInterface
(Partial)
LOG: Appbase = file:///C:/Programme/Microsoft SQL Server/MSSQL/Reporting
Services/ReportManager
LOG: Initial PrivatePath = bin
Calling assembly : (Unknown).
===
LOG: Policy not being applied to reference at this time (private, custom,
partial, or location-based assembly bind).
LOG: Post-policy reference: ReportingServicesWebUserInterface
LOG: Attempting download of new URL
file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET
Files/reports/432533e3/60266174/ReportingServicesWebUserInterface.DLL.
LOG: Attempting download of new URL
file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET
Files/reports/432533e3/60266174/ReportingServicesWebUserInterface/ReportingServicesWebUserInterface.DLL.
LOG: Attempting download of new URL file:///C:/Programme/Microsoft SQL
Server/MSSQL/Reporting
Services/ReportManager/bin/ReportingServicesWebUserInterface.DLL.
LOG: Publisher policy file is not found.
LOG: No redirect found in host configuration file
(C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\aspnet.config).
LOG: Using machine configuration file from
C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\config\machine.config.
LOG: Post-policy reference: ReportingServicesWebUserInterface,
Version=8.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91Really funy!
It was really a space problem. I just delete the log-files from Reporting
services. I got space.
And it works
"Soan" wrote:
> Please help I donâ't have idea how to start!
> The report server worked well. Now I have the following configurations error:
> === Pre-bind state information ===> LOG: DisplayName = ReportingServicesWebUserInterface
> (Partial)
> LOG: Appbase = file:///C:/Programme/Microsoft SQL Server/MSSQL/Reporting
> Services/ReportManager
> LOG: Initial PrivatePath = bin
> Calling assembly : (Unknown).
> ===> LOG: Policy not being applied to reference at this time (private, custom,
> partial, or location-based assembly bind).
> LOG: Post-policy reference: ReportingServicesWebUserInterface
> LOG: Attempting download of new URL
> file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET
> Files/reports/432533e3/60266174/ReportingServicesWebUserInterface.DLL.
> LOG: Attempting download of new URL
> file:///C:/WINDOWS/Microsoft.NET/Framework/v1.1.4322/Temporary ASP.NET
> Files/reports/432533e3/60266174/ReportingServicesWebUserInterface/ReportingServicesWebUserInterface.DLL.
> LOG: Attempting download of new URL file:///C:/Programme/Microsoft SQL
> Server/MSSQL/Reporting
> Services/ReportManager/bin/ReportingServicesWebUserInterface.DLL.
> LOG: Publisher policy file is not found.
> LOG: No redirect found in host configuration file
> (C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\aspnet.config).
> LOG: Using machine configuration file from
> C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\config\machine.config.
> LOG: Post-policy reference: ReportingServicesWebUserInterface,
> Version=8.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

Sunday, March 25, 2012

Configuration failing: The path is not of a legal form.

When I push Add in the configuration dialog, I cannot even get to the first page of the configuration wizard, due to the following error, in several solutions (but not in several others). All have at least one existing configuration (necessarily, to get passwords for connections).
**
An error occurred while a new configuration was being added.
Additional Information:
The path is not of a legal form. (mscorlib)

PS: How have the rest of you been posting? Passport hasn't been working much this week, so I've been unable to post. Is there a way to post without using passport? Please tell me if so :)

What types of configuration (XML configuration file, Environment variable, etc) do your packages use?|||This is a known issue when you edit the package "standalone",
i.e. without creating the SSIS project. A workaround is to create
the project, add the package to the project, and then edit
the configuration.

We plan to fix this issue for SP1 (it was reported too late to
consider changing this for RTM release).

Thanks,
Michael.|||(Sorry, I have to wade through a lot of script errors and bad passport certificate errors to be able to post to this forum.)
The configuration involved is an environment variable pointing at an XML config file.
The package is in a project in a solution.
However, the solution has two packages, one of which calls the other.
Because of the bug that Execute SSIS task cannot handle relative paths, we have to use a mapped drive to call the package, so the target package is called via a mapped drive fully qualified file path.
I've since discovered that the bug here is that if the package is opened from the mapped drive, even though it is the same file, the Package Configuration Wizard will give this mscorlib error. The workaround for this bug is to be careful and be sure to first close the package, then open it by double-clicking it in the solution, when you need to examine or edit package configurations.
Of course, I hope this bug gets fixed, but truly, it is not nearly as bad as the bug that relative paths get wrecked and fail in the Execute SSIS task.
|||I see - this indeed seems like a different bug.
Could you please open a bug on betaplace?|||re: bug on betaplace
I invested some time trying to get into betaplace, which involved waving my cursor around to find an invisible button I had to click, and then getting to a page which said I would be granted access at some point. I tried several times on successive days, but I never got further than that. I posted my travails several times to the newsgroup, but noone had any suggestions, so I finally gave it up as something that only costs me time, and brought no benefit to anyone (as I was never able to get to a point of actually accessing any information, much less posting new bugs).
Could you open the bug on betaplace? I have a suspicion that it probably works much better for people working at Microsoft :)

|||OK, I'll open a bug directly in our bug tracking system.|||Hi,
I know its probably to late for you ( Perry_Rapp )
but for other who trying to find solution to the "The path is not of a legal form" problem here it is:

1. open a new project/solution
2. copy the old project\s to the new "solution folder"
3. add the old project\s to the new solution from the new "solution folder".
(right click on "solution" -> Add -> Existing Project... )

Note:
if you have virtual folders in your solution first add the old project\s to the new solution root and after saving .. create the virtual folders

Configuration failing: The path is not of a legal form.

PS: How have the rest of you been posting? Passport hasn't been working much this week, so I've been unable to post. Is there a way to post without using passport? Please tell me if so :)

We plan to fix this issue for SP1 (it was reported too late to
consider changing this for RTM release).

Configuration failing: The path is not of a legal form.

PS: How have the rest of you been posting? Passport hasn't been working much this week, so I've been unable to post. Is there a way to post without using passport? Please tell me if so :)
What types of configuration (XML configuration file, Environment variable, etc) do your packages use?|||This is a known issue when you edit the package "standalone",
i.e. without creating the SSIS project. A workaround is to create
the project, add the package to the project, and then edit
the configuration.

We plan to fix this issue for SP1 (it was reported too late to
consider changing this for RTM release).

Perry_Rapp )
but for other who trying to find solution to the "The path is not of a legal form" problem here it is:

Thursday, March 22, 2012

Conection Error

Whenever I try and connection to SQL Server, I keep
getting the following error: The procedure entry point
SetfilesecurityI could not be located in the dynamic link
library Msdart.dll. I have just installed MDAC 2.8 and re-
installed SQL Server, but I'm still encountering the same
problem. I am running SQL Server Personal edition SP3a on
Windows XP. Thanksmsdart.dll is an MDAC file. Try reinstalling MDAC, maybe something you
installed later wrote over that file.
Use the MDAC component checker (from microsoft.com/downloads) to see if you
have a file mismatch with msdart.dll.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||I had (have) the same thing happening to me-2.8 hosed Delphi's ado
connection component. I always get this error when I try to create an
ado connection object.
I was able to fix it by manually replacing the files and changing
registry settings. MDAC will not rollback, the files are under
protection so I had to get a program to turn off Windows File
Protection, it was a huge hassle.
You need to get the 2.7 installer, extract the files, run the comparison
utility to identify what to change, get the utility to turn off file
protection, copy the files (and to dllcache), change the registry.
It worked for a day and now it's broken again. aargh!
the rule: DON'T UPGRADE TO MDAC 2.8.
I'm contemplating reformatting my hard disk and reinstalling
Win2K...WITHOUT MDAC 2.8.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
Did you ever figure out how to get past this error? I'm getting the
same error message, although the actual situation is somewhat different.
I just posted the following message at
http://forums.aspfree.com/t35176/shtml:
This error just started occuring today on an application I've been
working on for two years. The error message I receive is this:
The procedure entry point SetFileSecurityI could not be located in the
dynamic link library MSDART.dll.
The error occurs when I call:
System.Web.Mail.SmptMail.Send(message).
The error only occurs if I previously set the following property:
System.Web.Mail.SmtpMail.SmtpServer = "serverName"
I can't quite place my finger on the source of the problem. Here are two
potential causes of the problem:
A couple weeks ago, I had installed the 2005 Express verions of SQL
Server, c#, and Web Developer, but didn't notice any major problems
until now. Installing SQL Server 2005 caused a few minor issues with
Enterprise Manager on the Sql Server 2000 instance, but I was able to
solve those. I uinstalled those products, and repaired the installation
of VS2003 to no avail.
I almost ended up reformatting my hard drive over the weekend, after I
attempted to install a personal instance of Oracle 10g. After completing
the install, I was unable to reboot my computer. The system would boot
up, give about 150 warnings about delayed writes failing, and then crash
with STOP 0x00000027. The system was even hanging on reboot to safe
mode. It took me several hours to actually get my system to a bootable
state...After rebuilding the Master Boot Record, uninstalling oracle
from safe mode, about 25 reboots, a bios reset, yada, yada, yada...I
was back in.
I can't find much information on this error. One person here reported
the same problem when accessing SQL Server:
http://www.codecomments.com/sql/mes...C8d1e01c40513%2
4864b0f00%24a001280a%40phx.gbl %3E
They suggested it may have to do with installing MDAC 2.8, but I'm
pretty sure I've had that installed for a while. Anyways, the server is
running MDAC 2.8 without a problem, so I'm pretty sure it is something
else. Does anybody have any ideas?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Conditionally hiding column in matrix

I have a matrix with the following format
Date
Month RowGroup1 Group2 Amount
I have month in the rows because I want to page on month when
exporting to excel in order to produce a new sheet. This seem to work
ok except for the following.
1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
is displayed, I correctly get a report paged by month but the date
column shows all date between 7/1 and 8/31 on both sheets, regardless
of having a value in the amount field. So for July all August dates
are displayed and for July all August values are displayed. Can I
hide dates where the amount is null or missing?
2. When I export to excel the individual sheets are called sheet1,
sheet2... Is there a way to give the name of each sheet the
corresponding month value?
Thanks for your assistance?
DaveRather than putting the month in the matrix, put the matrix in a list which
groups by month.
You cannot control the Excel sheet names in the current version.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Dave" <davidbr93@.yahoo.com> wrote in message
news:703390f1.0408311012.2310ed6f@.posting.google.com...
> I have a matrix with the following format
> Date
> Month RowGroup1 Group2 Amount
>
> I have month in the rows because I want to page on month when
> exporting to excel in order to produce a new sheet. This seem to work
> ok except for the following.
>
> 1. I'm passing in a date range (7/1/2004 - 8/31/2004) When the report
> is displayed, I correctly get a report paged by month but the date
> column shows all date between 7/1 and 8/31 on both sheets, regardless
> of having a value in the amount field. So for July all August dates
> are displayed and for July all August values are displayed. Can I
> hide dates where the amount is null or missing?
> 2. When I export to excel the individual sheets are called sheet1,
> sheet2... Is there a way to give the name of each sheet the
> corresponding month value?
> Thanks for your assistance?
> Dave

Tuesday, March 20, 2012

Conditional XQuery: How to select a desirable node when it occurs multiple times

I would very much appreicate if someone could help me with the following

Return CountryCodes node based on the following rules:

(1) Ignore <AlternativeState> completely

(2) When <CurrentEvent>MarketSize</CurrentEvent> get CountryCodes from <MarketSize> node only

(3) When <CurrentEvent>MarketShare</CurrentEvent> get CountryCodes from <OtherEvents> node only

(4) When <CurrentEvent> doesn't exist then xml would have only one CountryCodes; get that node

I have come up with the following so far which is far from what is desirable

SELECT UsageID, Countries.Code.query('

for $CountryCode in .

return data($CountryCode)

') AS CountryCodes

FROM UsageAnalysis

CROSS APPLY xmlState.nodes('//*[not(self::AlternativeState)]/*/CountryCodes') AS Countries(Code)

Please keep in mind xml comes from a table column.

The following are three possible simplified cases

Case 1

<State>

<StatsState>

<CurrentState>

<MarketSize>

<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>

</MarketSize>
<CurrentEvent>MarketSize</CurrentEvent>
</CurrentState>

</StatsState>

</State>

Case 2

<State>

<DefinitionState>

<CountryCodes>BR</CountryCodes>

</DefinitionState>

</State>

Case 3

<State>

<CountryCodes>FR</CountryCodes>

<AlternativeState>

<OtherEvents>

<CountryCodes>FR</CountryCodes>

</OtherEvents>

<MarketSize>

<CountryCodes>FR,FP,FG</CountryCodes>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

<CountryCodes>,FR</CountryCodes>

</MarketSize>

</CurrentState>

</StatsState>

</State>

Hope this solve your problem:

Code Snippet

declare @.x xml

set @.x =

'<State>

</MarketSize>

<CurrentEvent>MarketSize</CurrentEvent>

</CurrentState>

</StatsState>

</State>

<State>

</DefinitionState>

</State>

<State>

</OtherEvents>

</MarketSize>

<CurrentEvent>MarketShare</CurrentEvent>

</AlternativeState>

</OtherEvents>

<CurrentEvent>MarketShare</CurrentEvent>

</MarketSize>

</CurrentState>

</StatsState>

</State>'

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then $s/StatsState/CurrentState/MarketSize/CountryCodes

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then $s/StatsState/CurrentState/OtherEvents/CountryCodes

else $s//CountryCodes

)

|||

Should this also be returned?

<CountryCodes>,FR</CountryCodes>

Please excuse me because I am rather new to the XML sector. I am confused by the question and the answer. I coded this up:

declare @.x xml
set @.x =
'<State>
<StatsState>
<CurrentState>
<MarketSize>
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes>
</MarketSize>
<CurrentEvent>MarketSize</CurrentEvent>
</CurrentState>
</StatsState>
</State>
<State>
<DefinitionState>
<CountryCodes>BR</CountryCodes>
</DefinitionState>
</State>
<State>
<StatsState>
<CurrentState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
<AlternativeState>
<OtherEvents>
<CountryCodes>FR</CountryCodes>
</OtherEvents>
<MarketSize>
<CountryCodes>FR,FP,FG</CountryCodes>
</MarketSize>
<CurrentEvent>MarketShare</CurrentEvent>
</AlternativeState>
</OtherEvents>
<CurrentEvent>MarketShare</CurrentEvent>
<MarketSize>
<CountryCodes>,FR</CountryCodes>
</MarketSize>
</CurrentState>
</StatsState>
</State>'

select coalesce (
nullif(t.c.query('./StatsState/CurrentState/MarketSize/CountryCodes').value('.','varchar(20)'), ''),
nullif(t.c.query('./StatsState/CurrentState/OtherEvents/CountryCodes').value('.','varchar(20)'),''),
t.c.query('./DefinitionState/CountryCodes').value('.','varchar(20)')
)
as CountryCodes
from @.x.nodes('State') t(c)

and received this result:

/*
CountryCodes
--
KT,LC,VG,SU,TT,UY,VE
BR
,FR
*/

Do the correct results need to include the markup such that the results should look more like this:

/*
CountryCodes
--
<CountryCodes>KT,LC,VG,SU,TT,UY,VE</CountryCodes><CountryCodes>BR</CountryCodes><CountryCodes>,FR</CountryCodes>
*/

(Trying to learn what is going on -- and I'm a bit confused.)

I appreciate the help.

|||

Jinghao, thanks very much. Your provided snippet does exactly what I have been trying to achieve. The only change I decided to introduce is to use data() so that I could get the scalar values for country codes as follows:

select @.x.query('

for $s in /State

return

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketSize")

then data($s/StatsState/CurrentState/MarketSize/CountryCodes)

else (

if (data(($s/StatsState/CurrentState/CurrentEvent)[1]) = "MarketShare")

then data($s/StatsState/CurrentState/OtherEvents/CountryCodes)

else data($s//CountryCodes)

)

Result set from your query:

Results after introducing data()

KT,LC,VG,SU,TT,UY,VE BR FR

Now I could use a function call to return a list of country codes.

Thanks again for your help.

|||

Kent,

I must say that it took me a while to fully understand the solution you suggested by clever use of COALESCE. It did exactly what I was trying to achieve. i.e get a list of selected country codes.

KT,LC,VG,SU,TT,UY,VE

I just wanted to have a list of countries, without having any markups. i.e. just the scalar values of <countryCodes>

Your response has shown me another use of COALESCE function and I very much appreciate your help

Monday, March 19, 2012

Conditional summaring

Hi all
I'm trying to create a summery table of rain data that holds a record
for every 0.1mm of rain in the following format:

Station Name, Time, Value
A, 2002-12-03 14:44:41.000, 0.1
A, 2002-12-03 14:45:45.000, 0.1
A, 2002-12-03 14:49:45.000, 0.1
A, 2002-12-09 05:30:35.000, 0.1
A, 2002-12-09 05:30:37.000, 0.1
A, 2002-12-09 05:33:05.000, 0.1
B, 2002-12-09 05:32:47.000, 0.1
B, 2002-12-09 05:33:00.000, 0.1
B, 2002-12-09 05:35:00.000, 0.1
B, 2002-12-09 05:37:48.000, 0.1
...
B, 2003-02-09 01:32:47.000, 0.1
B, 2003-02-09 05:32:47.000, 0.1

The problem is that I need to count rain events for each station and
produce the starting and ending time of the event (based on the Time
field) and to summarize the amount of rain that fell during the event.
An "event" for that matter is a case where there is a gap of at least
12 hours with no rain before and after a series of rain measurements
(i.e., a rain storm). The data in the table is written in such a way
that each row hold the time where an amount on 0.1mm of rain was
counted by the rain gauge.

For example, the above date should produce:

Station Name, Start Time, End Time, Total rain
A, 2002-12-03 14:44:41.000, 2002-12-03 14:49:45.000, 0.3
A, 2002-12-09 05:30:35.000, 2002-12-09 05:33:05.000, 0.3
B, 2002-12-09 05:32:47.000, 2002-12-09 05:37:48.000, 0.3
B, 2003-02-09 01:32:47.000, 2003-02-09 05:32:47.000, 0.2

As a newbie to SQL I don't know if it can be done.

Many thanks,
IlikOn 14 Feb 2005 08:00:24 -0800, Ilik wrote:

>Hi all
>I'm trying to create a summery table of rain data that holds a record
>for every 0.1mm of rain in the following format:
>Station Name, Time, Value
>A, 2002-12-03 14:44:41.000, 0.1
>A, 2002-12-03 14:45:45.000, 0.1
>A, 2002-12-03 14:49:45.000, 0.1
>A, 2002-12-09 05:30:35.000, 0.1
>A, 2002-12-09 05:30:37.000, 0.1
>A, 2002-12-09 05:33:05.000, 0.1
>B, 2002-12-09 05:32:47.000, 0.1
>B, 2002-12-09 05:33:00.000, 0.1
>B, 2002-12-09 05:35:00.000, 0.1
>B, 2002-12-09 05:37:48.000, 0.1
>...
>B, 2003-02-09 01:32:47.000, 0.1
>B, 2003-02-09 05:32:47.000, 0.1
>The problem is that I need to count rain events for each station and
>produce the starting and ending time of the event (based on the Time
>field) and to summarize the amount of rain that fell during the event.
>An "event" for that matter is a case where there is a gap of at least
>12 hours with no rain before and after a series of rain measurements
>(i.e., a rain storm). The data in the table is written in such a way
>that each row hold the time where an amount on 0.1mm of rain was
>counted by the rain gauge.
>For example, the above date should produce:
>Station Name, Start Time, End Time, Total rain
>A, 2002-12-03 14:44:41.000, 2002-12-03 14:49:45.000, 0.3
>A, 2002-12-09 05:30:35.000, 2002-12-09 05:33:05.000, 0.3
>B, 2002-12-09 05:32:47.000, 2002-12-09 05:37:48.000, 0.3
>B, 2003-02-09 01:32:47.000, 2003-02-09 05:32:47.000, 0.2
>As a newbie to SQL I don't know if it can be done.

Hi Ilik,

It can be done, but it's quite complicated. You have to use several
tricks, then combine them into one query.

Trick 1: Find the start of a rain period. Each row for which no other row
exists with a time value in the preceding 12 hours marks the start of a
rain period. You can find these using NOT EXISTS or using an OUTER JOIN.
SELECT Station
, Time
FROM MyTable AS a
WHERE NOT EXISTS (SELECT *
FROM MyTable AS b
WHERE b.Station = a.Station
AND b.Time < a.Time
AND b.Time >= DATEADD(hour, -12, a.Time))
or
SELECT a.Station
, a.Time
FROM MyTable AS a
LEFT OUTER JOIN MyTable AS b
ON b.Station = a.Station
AND b.Time < a.Time
AND b.Time >= DATEADD(hour, -12, a.Time)
I'll use the NOT EXISTS version in the remainder of this message; if
performance is important for you, I'd advise you to test both versions.

Trick 2: Find the end of a rain period. This is basically the same
technique as trick 1.

Trick 3: Match up the start and end times of each rain period. For each
start, the matching end is the FIRST of all end's that occur AFTER that
start.
SELECT a.Station
, a.Time AS StartTime
, MIN(b.Time) AS EndTime
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Station = a.Station
AND b.Time > a.Time
WHERE NOT EXISTS (SELECT *
FROM MyTable AS c
WHERE c.Station = a.Station
AND c.Time < a.Time
AND c.Time >= DATEADD(hour, -12, a.Time))
AND NOT EXISTS (SELECT *
FROM MyTable AS d
WHERE d.Station = b.Station
AND d.Time > b.Time
AND d.Time <= DATEADD(hour, +12, b.Time))
GROUP BY a.Station
, a.Time

Trick 4: Find all rows between start and end time of a rain period. This
can be done by using the result of the previous step as a derived table
and joining that to the original table, but in this case, I decided to
extend the result of the previous step with one correlated subquery.
SELECT a.Station
, a.Time AS StartTime
, MIN(b.Time) AS EndTime
,(SELECT SUM(e.Value)
FROM MyTable AS e
WHERE e.Station = a.Station
AND e.Time >= a.Time
AND e.Time <= MIN(b.Time)) AS TotalRain
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Station = a.Station
AND b.Time > a.Time
WHERE NOT EXISTS (SELECT *
FROM MyTable AS c
WHERE c.Station = a.Station
AND c.Time < a.Time
AND c.Time >= DATEADD(hour, -12, a.Time))
AND NOT EXISTS (SELECT *
FROM MyTable AS d
WHERE d.Station = b.Station
AND d.Time > b.Time
AND d.Time <= DATEADD(hour, +12, b.Time))
GROUP BY a.Station
, a.Time

Note: all the queries above are untested. To get tested replies, you need
to post SQL that will recreate your tables and data on my system, as shown
in www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||[posted and mailed, please reply in news]

Ilik (levyi@.walla.co.il) writes:
> The problem is that I need to count rain events for each station and
> produce the starting and ending time of the event (based on the Time
> field) and to summarize the amount of rain that fell during the event.
> An "event" for that matter is a case where there is a gap of at least
> 12 hours with no rain before and after a series of rain measurements
> (i.e., a rain storm). The data in the table is written in such a way
> that each row hold the time where an amount on 0.1mm of rain was
> counted by the rain gauge.

By no means a trivial problem. As Hugo said, CREATE TABLE and INSERT
statements are alwyas a good thing. Then again, the table was easy
to compose in this case, as were the INSERT statement. And your
narrative and sample data, was very good. Thanks!

Here is a multi-step approach. Maybe not the most elegant, but it
gives the correct result. If data volumes are large, performance
could be an issue, though.

A particular note on the datediff exercise. I do datediff per minute,
not per hour. This is because datediff(HOUR, '00:15:00', '12:10:10')
is 12, although it's not fully 12 hours. datediff always counts
cross boundaries. Also, I found that coalesce needed special care.
Originally I had 19000101, and 99991231, but that gave me overflow.

CREATE TABLE data(station char(1) NOT NULL,
time datetime NOT NULL,
value float NOT NULL,
CONSTRAINT pk_data PRIMARY KEY (station, time))
go
INSERT data (station, time, value)
SELECT 'A', '20021203 14:44:41.000', 0.1 UNION
SELECT 'A', '20021203 14:45:45.000', 0.1 UNION
SELECT 'A', '20021203 14:49:45.000', 0.1 UNION
SELECT 'A', '20021209 05:30:35.000', 0.1 UNION
SELECT 'A', '20021209 05:30:37.000', 0.1 UNION
SELECT 'A', '20021209 05:33:05.000', 0.1 UNION
SELECT 'B', '20021209 05:32:47.000', 0.1 UNION
SELECT 'B', '20021209 05:33:00.000', 0.1 UNION
SELECT 'B', '20021209 05:35:00.000', 0.1 UNION
SELECT 'B', '20021209 05:37:48.000', 0.1 UNION
SELECT 'B', '20030209 01:32:47.000', 0.1 UNION
SELECT 'B', '20030209 05:32:47.000', 0.1 UNION
SELECT 'B', '20030209 18:32:47.000', 0.1 UNION
SELECT 'B', '20030212 05:32:47.000', 0.1
go
CREATE TABLE #temp (station char(1) NOT NULL,
time datetime NOT NULL,
n int NOT NULL,
starttime datetime NULL,
endtime datetime NULL,
value float NOT NULL,
PRIMARY KEY (station, n))
go
INSERT #temp (station, time, value, n)
SELECT a.station, a.time, a.value,
(SELECT COUNT(*) + 1 FROM data b
WHERE a.station = b.station AND a.time > b.time)
FROM data a
go
UPDATE b
SET starttime = CASE WHEN datediff(minute,
coalesce(a.time, dateadd(DAY, -1, b.time)),
b.time) > 60*12
THEN b.time
END,
endtime = CASE WHEN datediff(minute,
b.time,
coalesce(c.time, dateadd(DAY, 1, b.time))) > 60*12
THEN b.time
END
FROM #temp b
LEFT JOIN #temp a ON a.station = b.station AND b.n - 1 = a.n
LEFT JOIN #temp c ON b.station = c.station AND b.n + 1 = c.n
go
UPDATE #temp
SET starttime = (SELECT MAX(b.starttime)
FROM #temp b
WHERE b.station = a.station
AND b.starttime < a.time)
FROM #temp a
WHERE a.starttime IS NULL
go
SELECT station, starttime, MAX(endtime), SUM(value)
FROM #temp
GROUP BY station, starttime
go
DROP TABLE #temp
DROP TABLE data

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 15 Feb 2005 21:28:13 +0000 (UTC), Erland Sommarskog wrote:

>By no means a trivial problem. As Hugo said, CREATE TABLE and INSERT
>statements are alwyas a good thing. Then again, the table was easy
>to compose in this case, as were the INSERT statement.

Hi Erland,

You're absolutely right. I guess this just shows that I'm lazy ;-)

I just stole your work to test my query and found one small error. The
corrected version (adapted to the table and column names you've chosen) is

SELECT a.station
, a.time AS Starttime
, MIN(b.time) AS Endtime
,(SELECT SUM(e.value)
FROM data AS e
WHERE e.station = a.station
AND e.time >= a.time
AND e.time <= MIN(b.time)) AS TotalRain
FROM data AS a
INNER JOIN data AS b
ON b.station = a.station
AND b.time >= a.time-- The change is in this line
WHERE NOT EXISTS (SELECT *
FROM data AS c
WHERE c.station = a.station
AND c.time < a.time
AND c.time >= DATEADD(hour, -12, a.time))
AND NOT EXISTS (SELECT *
FROM data AS d
WHERE d.station = b.station
AND d.time > b.time
AND d.time <= DATEADD(hour, +12, b.time))
GROUP BY a.station
, a.time

> And your
>narrative and sample data, was very good. Thanks!

I can only second that.

>Here is a multi-step approach. Maybe not the most elegant, but it
>gives the correct result. If data volumes are large, performance
>could be an issue, though.

I ran some quick tests for a first impression. Based on the difference
from start to end time for both queries, using the sample data provided,
it was too close to call. Increasing the amount of test data (copying the
insert two more times and changing the station names to C, D, E and F)
resulted in a small advantage for my query, but with so little test data,
that is not really saying anything.

With set statistics io on, my query used

Table 'data'. Scan count 151, logical reads 302, physical reads 0,
read-ahead reads 0.

And your code used

Table '#temp__(snip)'. Scan count 0, logical reads 86, physical reads 0,
read-ahead reads 0.
Table 'data'. Scan count 43, logical reads 86, physical reads 0,
read-ahead reads 0.

Table '#temp__(snip)'. Scan count 85, logical reads 254, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 44, physical reads 0,
read-ahead reads 0.

Table '#temp__(snip)'. Scan count 25, logical reads 98, physical reads 0,
read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 26, physical reads 0,
read-ahead reads 0.

Table '#temp__(snip)'. Scan count 1, logical reads 2, physical reads 0,
read-ahead reads 0.

Adding up these numbers shows that your version appears to do quite some
more work than my version.

I'd be interested to hear from Ilik how the versions compare on his
system, with real data.

>A particular note on the datediff exercise. I do datediff per minute,
>not per hour. This is because datediff(HOUR, '00:15:00', '12:10:10')
>is 12, although it's not fully 12 hours. datediff always counts
>cross boundaries. Also, I found that coalesce needed special care.
>Originally I had 19000101, and 99991231, but that gave me overflow.

Why didn't you use DATEADD(hour, 12, a.time) > b.time, then? Wouldn't that
have solved all these issues?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have to walk two dogs and install a Pergo floor this morning, so I
cannot work out the code in detail.

Can I assume that the measurements are collected by device that is
giving you a collection time? This means the data comes over time, and
it is a few microseconds behind the current time.

Instead of modeling the data collection form, model the final results:

CREATE TABLE Rainfall
(station_name CHAR(5) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
rain_accumulation DECIMAL (5,2) DEFAULT 0.00 NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (station_name, start_time));

When a new reading comes in:

1) If it is within 12 hours of the start_time of the current row (i.e.
MAX(end_time) , then add 0.1 to the rain_accumulation and the end_time
CURRENT_TIMESTAMP in an UPDATE.

2) If it is more than 12 hours after the start_time of the current row,
then

2.1) update end_time to CURRENT_TIMESTAMP to close out the current
period.
2.2) insert new row with the new start_time, etc.

Something like this in pseudo-code.

CREATE PROCEDURE PostNewReading(@.my_station_name, @.new_reading_time)
AS
BEGIN
UPDATE Rainfall
SET end_time
= CASE WHEN <<less than 12 hours
FROM most recent>>
THEN @.new_reading_time
ELSE end_time END,
rain_accumulation
= CASE WHEN <<less than 12 hours
FROM most recent>>
THEN rain_accumulation + 0.1
ELSE rain_accumulation END;

INSERT INTO Rainfall
SELECT @.my_station_name, @.reading_time,
CURRENT_TIMESTAMP, 0.1
FROM Rainfall
WHERE <<reading more than 12 hours
FROM most recent>>;
END;

The idea is to put the procedural logic into CASE expressions in an
UPDATE and an INSERT statement. Use ELSE clauses to leave data
unchanged. No proprietary T-SQL control of flow stuff at all. No
tricky self-joins or cursors.|||Hi all
First, my sincere apologies for not replying earlier, but I was busy
over my head in the last couple of days with other projects.
In reply to CELKO, you're assuming that the data is being collected at
real time, but this is not the case. The data goes back a few decades
in some cases (starting at 1965), so I cannot evaluate it on entry.

As for Erland's solution, I run it only for a few minutes because of
his remark that performance can be an issue there. I tried running the
Hugo's query with the "NOT EXISTS" option. It run for two hours before
I stopped it... The second option ("LEFT OUTER JOIN ") I stopped after
15min ....
The table holding the data has over 1.3 million records, that besides
rain gauge data also has other data types (e.g., water conductivity,
water level, water chemistry etc.) in the "Class_Name" column. I didn't
mention this in my original post because I thought I could deal with it
after I'll get your help with the basic query, but now I see it is much
more complex then I thought. I added this to Hugo's query as you can
see below. So the efficiency of the query is an important issue here.
If you have any ideas on how to make it run faster?

Here is the query as I run it with both options:

--START
SELECT a.Location_Name
, a.Time AS StartTime
, MIN(b.Time) AS EndTime
,(SELECT SUM(e.Value)
FROM V_Cell AS e
WHERE e.Location_Name =
a.Location_Name
AND e.Class_Name = 'Rain'
AND e.Time >= a.Time
AND e.Time <= MIN(b.Time)) AS
TotalRain
FROM V_Cell AS a
INNER JOIN V_Cell AS b
ON b.Location_Name = a.Location_Name
AND b.Class_Name = 'Rain'
AND b.Time >= a.Time
-- WHERE NOT EXISTS (SELECT *
-- FROM V_Cell AS c
-- WHERE c.Location_Name =
a.Location_Name
LEFT OUTER JOIN V_Cell AS c
ON c.Location_Name = a.Location_Name
AND c.Class_Name = 'Rain'
AND c.Time < a.Time
AND c.Time >= DATEADD(hour, -12, a.Time)
-- AND NOT EXISTS (SELECT *
-- FROM V_Cell AS d
-- WHERE d.Location_Name =
b.Location_Name
LEFT OUTER JOIN V_Cell AS d
ON d.Location_Name = b.Location_Name
AND d.Class_Name = 'Rain'
AND d.Time > b.Time
AND d.Time <= DATEADD(hour, +12, b.Time)
GROUP BY a.Location_Name
, a.Time

--END

Many thanks,
Ilik|||On 17 Feb 2005 01:06:22 -0800, Ilik wrote:

(snip)
>If you have any ideas on how to make it run faster?

Hi Ilik,

I don't think there's much room for simplification of the query, so I'd
look into indexes. I don't expect my query to return results in a fraction
of a second, but two hours appears to be too much. I'm afraid that you
have no indexes on your table that can be used for this query.

In order for us to advise on this, we need to know the actual table
structure (as CREATE TABLE statement, **INCLUDING** all constraints [esp
primary key and unique!]) and all extra indexes you might have defined.
You might wish to run sp_help V_Cell to get a full report on all indexes
and constraints.

Another thing you could do is to run the index tuning wizard (ITW): load
my query in a QA window, select "Query" / "Index Tuning Wizard" and see
what it advises you. ITW is certainly not always perfect, but it never
hurts to check out what it comes up with.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Ilik (levyi@.walla.co.il) writes:
> As for Erland's solution, I run it only for a few minutes because of
> his remark that performance can be an issue there. I tried running the
> Hugo's query with the "NOT EXISTS" option. It run for two hours before
> I stopped it... The second option ("LEFT OUTER JOIN ") I stopped after
> 15min ....
> The table holding the data has over 1.3 million records, that besides
> rain gauge data also has other data types (e.g., water conductivity,
> water level, water chemistry etc.) in the "Class_Name" column. I didn't
> mention this in my original post because I thought I could deal with it
> after I'll get your help with the basic query, but now I see it is much
> more complex then I thought. I added this to Hugo's query as you can
> see below. So the efficiency of the query is an important issue here.
> If you have any ideas on how to make it run faster?

Your data volumes are certainly in par with what I expected. (No one
would have a table like that with only a few thousands of rows!) But
without test data available, it's difficult to post a solution that
tested for performance.

For my query batch, it could be an idea to let it run for a little
longer, and see what actually takes time.

Certainly there is all reason to review the indexing of the table, and
as Hugo said, posting the complete CREATE TABLE and CREATE INDEX
statement for the table may help.

One solution that cannot be ruled out completely, is to run a cursor
over the lot. That is by no means not going to be blinding fast, but
you can quite soon get a grip of when it will complete. (As you would
be able to see how much have been processed.) But that would really be
a last-ditch attempt if nothing else works.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> The data goes back a few decades in some cases (starting at 1965),
so I cannot evaluate it on entry .. The table holding the data has over
1.3 million records, that besides rain gauge data also has other data
types (e.g., water conductivity, water level, water chemistry etc.) in
the "Class_Name" column. <<

Ouch! I think is a case where I would go with a cursor, since it
sounds like you will do this only once. That gives you one scan thru
the whole table.

Any other method I can think of would use a self-join to get the start
and finish times of each collection period. Even with indexing,
self-joins take time.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1108745004.509101.63100@.c13g2000cwb.googlegro ups.com...
> >> The data goes back a few decades in some cases (starting at 1965),
> so I cannot evaluate it on entry .. The table holding the data has over
> 1.3 million records, that besides rain gauge data also has other data
> types (e.g., water conductivity, water level, water chemistry etc.) in
> the "Class_Name" column. <<
> Ouch! I think is a case where I would go with a cursor, since it
> sounds like you will do this only once. That gives you one scan thru
> the whole table.
> Any other method I can think of would use a self-join to get the start
> and finish times of each collection period. Even with indexing,
> self-joins take time.

<Uninformed musings>
As others have stated, it is difficult to gauge the efficacy of the
suggestions without knowing more about the table structure.

Is it possible for the OP to extract the "Rain" records and create a new
table with appropriate indexing? He stated there are 1.3 million records
TOTAL but how many are "Rain" records and will it reduce the work to an
acceptable level for one of the aforementioned queries to complete?

How about a hybrid approach? What if you cursor-ed through the data by
"Station Name" and use one of the queries with the additional condition of
"Station Name"? I presume a table/list of the station names exist otherwise
you could "SELECT DISTINCT". As Hugo K. has demonstrated, the queries work
well enough for a small case which this would be especially if there is an
index on "station Name".
</Uninformed musings|||Hi all,
Thanks for your suggestions.
After a long consideration with my DB admin, we've decided to use the
curser approach as you suggest.
Currently, our db holds only about 9,000 records of type Rain but this
number is expected to grow
in the future since only a small part of the data has been introduced
into the database.
So using an all-at-once query of the data will be a very slow process.
Since I'm fairly new to SQLServer, I don't know how to run a script on
it directly. But since anyway
I only need to produce a web-report, I'm using MathLab for this (thou
VB or asp could do the job just as easy).

again, many thanks for your help,
Ilik

Conditional Sum?

Hi,

Is it possible to have a conditional sum based on an item type existance in a set of values?

Example if i have the following set:

I just wanna sum B else if B doens't exist sum A

Best Regards,

Luis Simoes

Have an invisible textbox in your report with the expression and say the name of the textbox is textbox20:

Count(IIf(Fields!ItemType.Value = "A", Fields!YourField.Value, 0))

Then use this expression for summing:

Sum(IIf(ReportItems1textbox20.Value > 0, IIf(Fields!ItemType.Value = "A", Fields!YourField.Value, 0), 0))

Shyam

|||

Yes that would do the trick!

I can also use that count in the field formula right? Or it does affect performance that much?

Thanks,

Regards,

Luis Simoes

|||

Hello,

Doing this gives me two errors in the summing field.

"The value expression for the textbox 'textbox30' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers."

"The value expression for the textbox ‘textbox30’ refers to the report item ‘textbox28’. Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope."

Any idea why?

Sunday, March 11, 2012

Conditional Split query

Hi,

I have the following table in MsAccess

EmployeesA

empId integer,

empName varchar(60),

empAge integer,

empStatus char(1) - can be N,D or S - New, Deleted or Shifted

and the following in Sql2005

EmployeesB

Id smallint,

Name varchar(60),

Age int,

Status char(1) - Bydefault 'N'

I have written a Foreach File package that populates the sql server tables (EmployeesB) from Access(EmployeesA). However i want to check for a condition now.

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

How do I do it for each table each row in EmployeesA using a foreach file loop?

Thanks,

ron

If you are using a data flow inside your For Each, you can use the techniques shown in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1211340&SiteID=1) to determine whether the row should be inserted or updated. The thread is dicussing specifically whether the row already exists or not, so you may need to add a conditional split to your data flow.

|||

Hi,

thanks for the reply. I had already seen that link. I cannot do a look up as Employees B will already contain millions of rows.

I just want to know this step by step if you could explain. I am so new to this SSIS.

How will I specify conditions :

If empStatus = N in EmployeesA, then insert a new record in EmployeesB

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

If empStatus = S in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status as 'S' in EmployeesB and insert a new row.

Which control to use. Where to specify etc.

thanks,

|||

Ok,

I have figured out most of it through a friend. Just tell me this:

What i am doing is :

For status D, I do a Lookup and if found, I have to use an OLE DB Command tranform to do the update.

What query do I fire in the look up over here. If that row exists, after that what to do in the OLEDB command. ?How to pass the current row?

thanks.

|||Do you mean how to update the row that was matched in the lookup? Why can you not use the same fields you used in the lookup for the match and put them into the WHERE clause of your update? Sometimes it is cleaner to return a key column or two from the lookup, and them as basis for the WHERE clause.|||

Can you state an example. What should be in the Lookup and what in the OledbCommand based on my table.

Thanks

|||

An example, do you mean for this problem-

If empStatus = D in EmployeesA, then search for that field in the EmployeesB by passing empname and age and if found, mark the Status field in EmployeesB as 'D'

I would be tempted to skip the lookup. Use a Conditional Split to get a feed of all EmployeesA rows where empStatus = "D", then use a command to do the update. Set your connection, and end the SQL statement -

UPDATE EmployeesB

SET empStatus = 'D'

WHERE empname = ?

AND age = ?

Map the two input columns empname and age to the two parameters, to complete the OLD-DB Command setup.

This avoids the costs of a lookup, which may be faster overall. If there is no match, then no update happens, which is the same overall outcome as if the lookup had failed to find anything and the command was not run.

It may be faster to use a Lookup to help filter out the non-matches, it really depends on row counts and ratios of lookup hits to misses. Test both if you are worried about performance, but it is often faster to attempt and "fail" than to prevent the "fail" in the first place in SSIS.

|||

Darren,

you know what..that worked like a charm Smile i removed the look up and did as you said..I will try the rest and if everything works, i will close this thread. thankuuuuuu.

If you could have a look at this thread too, I will be much obliged.

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

thanks.

Conditional sorting

I'm attempting to sort using the following conditions:
If the score is greater or equal to passing score, sort by score
descending, test taken date descending. If score is less than passing
score, sort by test taken date descending, score descending.
Expected results:
3 95 2005-01-02 00:00:00
1 90 2005-02-02 00:00:00
2 50 2004-02-02 00:00:00
4 75 2003-03-02 00:00:00
Actual results:
2 50 2004-02-02 00:00:00
4 75 2003-03-02 00:00:00
3 95 2005-01-02 00:00:00
1 90 2005-02-02 00:00:00
How can I achieve the expected results?
Here's the SQL I've tried so far:
CREATE TABLE CourseData (
id int,
SCORE int,
TESTTAKEN smalldatetime,
PASSSCORE int
)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(1,90,'2005-02-02',80)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(2,50,'2004-02-02',80)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(3,95,'2005-01-02',80)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(4,75,'2003-03-02',80)
SELECT
id,
SCORE,
TESTTAKEN
FROM
CourseData
ORDER BY
CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END DESC,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END DESC
DROP TABLE CourseData
TIA.
PTry,
...
ORDER BY
CASE WHEN SCORE >= PASSSCORE THEN SCORE end desc,
case when SCORE < PASSSCORE then TESTTAKEN END DESC,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN END DESC,
CASE WHEN SCORE < PASSSCORE THEN SCORE END DESC
go
AMB
"go559@.hotmail.com" wrote:

> I'm attempting to sort using the following conditions:
> If the score is greater or equal to passing score, sort by score
> descending, test taken date descending. If score is less than passing
> score, sort by test taken date descending, score descending.
> Expected results:
> 3 95 2005-01-02 00:00:00
> 1 90 2005-02-02 00:00:00
> 2 50 2004-02-02 00:00:00
> 4 75 2003-03-02 00:00:00
> Actual results:
> 2 50 2004-02-02 00:00:00
> 4 75 2003-03-02 00:00:00
> 3 95 2005-01-02 00:00:00
> 1 90 2005-02-02 00:00:00
> How can I achieve the expected results?
> Here's the SQL I've tried so far:
> CREATE TABLE CourseData (
> id int,
> SCORE int,
> TESTTAKEN smalldatetime,
> PASSSCORE int
> )
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(1,90,'2005-02-02',80)
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(2,50,'2004-02-02',80)
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(3,95,'2005-01-02',80)
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(4,75,'2003-03-02',80)
> SELECT
> id,
> SCORE,
> TESTTAKEN
> FROM
> CourseData
> ORDER BY
> CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END DESC,
> CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END DESC
> DROP TABLE CourseData
>
>
> TIA.
> P
>|||Thank you for the reply, it solved my issue. However, I'm not quite
clear why it works. Using the data set I posted, I evaluated the ORDER
BY clause using your solution and mine.
Example 1:
VALUES(1,90,'2005-02-02',80)
Mine:
ORDER BY SCORE DESC, TESTTAKEN DESC
Yours:
ORDER BY SCORE DESC, NULL DESC, TESTTAKEN DESC, NULL DESC
Example 2:
VALUES(2,50,'2004-02-02',80)
Mine:
ORDER BY TESTTAKEN DESC, SCORE DESC
Yours:
ORDER BY NULL DESC, TESTTAKEN DESC, NULL DESC, SCORE DESC
Since the null else clauses are ignored, then it appears that both
solutions produce the same ORDER BY clause. Would you please let me
know what I'm missing?
TIA.
P
Alejandro Mesa wrote:
> Try,
> ...
> ORDER BY
> CASE WHEN SCORE >= PASSSCORE THEN SCORE end desc,
> case when SCORE < PASSSCORE then TESTTAKEN END DESC,
> CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN END DESC,
> CASE WHEN SCORE < PASSSCORE THEN SCORE END DESC
> go
>
> AMB
>
> "go559@.hotmail.com" wrote:
>
passing|||Try with two rows, each one belonging to a diff group.
AMB
"go559@.hotmail.com" wrote:

> Thank you for the reply, it solved my issue. However, I'm not quite
> clear why it works. Using the data set I posted, I evaluated the ORDER
> BY clause using your solution and mine.
> Example 1:
> VALUES(1,90,'2005-02-02',80)
> Mine:
> ORDER BY SCORE DESC, TESTTAKEN DESC
> Yours:
> ORDER BY SCORE DESC, NULL DESC, TESTTAKEN DESC, NULL DESC
> Example 2:
> VALUES(2,50,'2004-02-02',80)
> Mine:
> ORDER BY TESTTAKEN DESC, SCORE DESC
> Yours:
> ORDER BY NULL DESC, TESTTAKEN DESC, NULL DESC, SCORE DESC
> Since the null else clauses are ignored, then it appears that both
> solutions produce the same ORDER BY clause. Would you please let me
> know what I'm missing?
> TIA.
> P
> Alejandro Mesa wrote:
> passing
>|||On 10 Feb 2005 11:29:02 -0800, go559@.hotmail.com wrote:

>Thank you for the reply, it solved my issue. However, I'm not quite
>clear why it works. Using the data set I posted, I evaluated the ORDER
>BY clause using your solution and mine.
>Example 1:
>VALUES(1,90,'2005-02-02',80)
>Mine:
>ORDER BY SCORE DESC, TESTTAKEN DESC
>Yours:
>ORDER BY SCORE DESC, NULL DESC, TESTTAKEN DESC, NULL DESC
(snip)
Hi P,
Not exactly. Your ORDER BY clause is:
Now you should know that CASE is an expression that can ony result in one
datatype. Which datatype depends on the datatypes of the various THEN
clauses and the WHEN clause. For the first CASE, the datatype of THEN
SCORT is int; the datatype of TESTTAKEN is smalldatetime. According to the
datatype precedence rules, SCORE will have to be converted to
smalldatetime.
The implicit conversion of int to smalldatetime works like this: take the
value of the integer and add that number of days to 19000101. So if the
score is 60, the result after converting to smalldatetime will be 19000302
and that's the value that will be used for the sorting.
It's actually quite easy to see for yourself why your order by won't work
as you'd like it to: just add the CASE expressions from the ORDER BY to
the SELECT clause:
SELECT
id,
SCORE,
TESTTAKEN,
CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END AS ordering1,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END AS ordering2
FROM
CourseData
ORDER BY
CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END DESC,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END DESC
In Alejandro's version, no different datatypes are mixed within the same
CASE expressions. Therefore, there are no implicit conversions and
everything is working as expected.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Conditional Rollback

I have inherited a lot of SQL2005 TSQL, that has the following generic catch structure:

BEGIN CATCH
IF @.@.TRANCOUNT > 0 AND (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH

From BOL, @.@.TRANCOUNT returns the number of active transactions for the current connection and XACT_STATE()) is a scalar function that reports the transaction state of a session, indicating whether or not the session has an active transaction, and whether or not the transaction is capable of being committed.

My question is: Should the tests be OR'd rather than AND'd?

The line in question would then read:

IF @.@.TRANCOUNT > 0 OR (XACT_STATE()) <> 0

BOL says,

Both the XACT_STATE and @.@.TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @.@.TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.

ie,

Instead of using the @.@.Trancount you can always check the XACT_STATE. Bcs @.@.TRANCOUNT never tells you wheather you have to do Commit or rollback. It only gives the number of transaction begin.

But

XACT_STATE = 0 no transaction active

XACT_STATE = 1 active transaction available & can be writable & commitable/rollbackable -- Commit/rollback

XACT_STATE = -1 active transaction available & uncommitable -- Rollbacked

|||

Thankyou Manivannan,

The TSQL in question runs after an error within the try section has been caught. My question relates as whether the functions should be OR'd instead of AND'd. Possibly only one of them should be used.

|||

Ok now, My direct answer is XACT_STATE is more enough to use....(trustable). Need not have or/and only XACT_STATE is enough to validate the condition.

|||

If I may summerise

BEGIN CATCH
IF @.@.TRANCOUNT > 0 AND (XACT_STATE()) <> 0
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH
should become

BEGIN CATCH
IF (XACT_STATE()) <> 0 -- < Suggested simpler construction.
BEGIN
ROLLBACK TRANSACTION
END
-- task specfic steps
END CATCH

Is this your recommendation?

|||Yes...Exactly..

conditional query

Hi there!

I'm struggling to write the correct SQL to do the following task:

there are 3 fields in a table: SSize1, SSize2, SSize3.
the data type of all three is integer.
i need to do a simple calculation to determine SSize based on the following condition:
if SSize3 <>0 then
SSize = SSize1 + (SSize2/SSize3)
else SSize = SSize1
end if

can anyone please help me? thanks in advance!

regards
leeDoes your SQL dialect support the CASE operator? That would be how I'd approach solving your problem.

-PatP|||do you want to get the SSize per each row?
If so, I think you have to store these 3 fields first in a temp table. Then use a cursor, or something like that,for your calculations to compute SSize per row.

If the SSize would get the total of these 3 fields, get first the sum for each of these fields. Placed it in integer variables, then proceed to your calculations. Try this

select @.SSize1 = sum(SSize1), @.SSize2 = sum(SSize2), @.SSize3 = sum(SSize3)
from table_name

if @.SSize3 <> 0 then
SSize = @.SSize1 + (@.SSize2/@.SSize3)
else
SSize = @.SSize1
end if

hope this would work on you :)|||Or, perhaps,

SELECT DECODE(SSize3, 0, SSize1, SSize1 + (SSize2 / SSize3)) SSize
FROM table_name;

Conditional Processing from a Common Table Expression (CTE)

I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:

WITH Orders_CTE (TerritoryId, ContactId)
AS
(
SELECT TerritoryId, ContactId
FROM Sales.SalesOrderHeader
WHERE (ContactId < 200)
)
IF Orders_CTE.TerritoryId > 3
BEGIN
/* Do some processing here */

END
ELSE
BEGIN
/* Do something else here */

END

When I try this, I get a syntax error near the keyword 'IF'

Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!

WITH statement is a part of SELECT/INSERT/UPDATE/DELETE statement. As result you code doesn't work.

What processing do you need?

|||

What exactly you want to do on the /* Do Some processing here */. It might help you to give the rite solution.

You can end the CTE expression with INSERT/DELETE/UPDATE/SELECT. Othere than this 4 statement none of them are allowed.

|||

Thanks for the quick response.

All I need to do in each part of the IF ...ELSE..... is to perform a select, but which select statement to use depends on a field in the CTE. One select pulls data from a table in current database, whereas the other pulls data from a table on a linked server. The tables have similar but not exactly the same structures.

As I mentioned, I have what I need working using a cursor, I was just wondering if this were possible with a CTE, but based on what you and other repsondents have stated, it appears unlikely.

Thursday, March 8, 2012

conditional logic in stored procedure

Hello.

Looking for a smarter way to code the following. I have a stored
procedure I will be passing several variables to. Some times, some of
the fields used in a WHERE clause will not be passed, and I would like
to avoid having to code a bunch of if statements to set the executing
code. For example, below I would only like to execute the LIKE
conditions only when the variable in question is not NULL. I did a
test and if the variable is set to null, obviously the select does not
return what I'm expecting.

if @.switch = "B"
SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3
end

Thanks for any help or information with this.>> I would only like to execute the LIKE conditions only when the
variable in question is not NULL. I did a test and if the variable is
set to null, obviously the select does not return what I'm expecting.
<<

SELECT *
FROM Foobar
WHERE kbtitle LIKE COALESCE(@.ins1, kbtitle)
AND ikbtitle LIKE COALESCE(@.ins2, ikbtitle)
AND ikbtitle NOT LIKE COALESCE(@.ins3, '')
AND ikbbody LIKE COALESCE(@.ins1, ikbbody)
AND ikbbody LIKE COALESCE(@.ins2, ikbbody)
AND ikbbody NOT LIKE COALESCE(@.ins3,'')|||Hi Jason,

Here's one suggestion. Change your params to '%' if they're null.
That way you don't need the IF statement. I would also rewrite the
"not like" clause as it's CPU intensive. - Louis

select @.ins1=isnull(@.ins1,'%')
select @.ins2=isnull(@.ins2,'%')
select @.ins3=isnull(@.ins3,'%')

SELECT * from ikb where
ikbtitle like @.ins1 and
ikbtitle like @.ins2 and
ikbtitle not like @.ins3 and
ikbbody like @.ins1 and
ikbbody like @.ins2 and
ikbbody not like @.ins3

Thursday, March 29, 2012

Configure Integration Services on a Cluster

Configure Distributor error

Configurations like ANSI_WARNINGS

Configurations like ANSI_WARNINGS

Configurations like ANSI_WARNINGS

Configurations error

Sunday, March 25, 2012

Configuration failing: The path is not of a legal form.

Configuration failing: The path is not of a legal form.

Configuration failing: The path is not of a legal form.

Thursday, March 22, 2012

Conection Error

Conditionally hiding column in matrix

Tuesday, March 20, 2012

Conditional XQuery: How to select a desirable node when it occurs multiple times

Monday, March 19, 2012

Conditional summaring

Conditional Sum?

Sunday, March 11, 2012

Conditional Split query

Conditional sorting

Conditional Rollback

conditional query

Conditional Processing from a Common Table Expression (CTE)

Thursday, March 8, 2012

conditional logic in stored procedure

Concatenating fields

Blog Archive

About Me