Conditionally Expand a Table

I have a form that has a table in the center with text boxes above and below
where the table may contain 1 to n number of rows. When the table has 4 or
more rows the page looks well balanced but anything less makes it look too
compressed.
I would like to conditionally add some blank table rows and have been
attempting this by adding rows and setting the visibility property using
RowCount(). Problem is I have 2 groups in this table so have separate row
counts. I did a test and added RowCount() to a text box in the table header
and it shows the total for the entire table. Is there a syntax for RowCount
that will allow me to reference the header RowCount from each of the group
row visibility properties?
Or is there a better way to set a minimum table size?
Thankstry putting your items inside of a rectangle as a group - I think your table
will expand and contract within the bounds of the rectangle and keep your
text boxed on either side too.
"Mike Harbinger" wrote:
> I have a form that has a table in the center with text boxes above and below
> where the table may contain 1 to n number of rows. When the table has 4 or
> more rows the page looks well balanced but anything less makes it look too
> compressed.
> I would like to conditionally add some blank table rows and have been
> attempting this by adding rows and setting the visibility property using
> RowCount(). Problem is I have 2 groups in this table so have separate row
> counts. I did a test and added RowCount() to a text box in the table header
> and it shows the total for the entire table. Is there a syntax for RowCount
> that will allow me to reference the header RowCount from each of the group
> row visibility properties?
> Or is there a better way to set a minimum table size?
> Thanks
>
>

Conditional Views

I have a view which has to be versatile enough to perform different calculations. For instance, using the view below I may want the stock level for that particular wine or in another instance I would the need the stock level for all wines that share the same type. Note that I would never need a combonation of both in the same view.

Is there anyway to specify <myVar> before or when calling this view?

CREATE VIEW vwWines
AS

SELECT
tblWine.ID,
tblWine.WineTypeID,
StockQty =
CASE
WHEN <myVar> = 1
THEN tblWine.StockQty
ELSE
(
SELECT
SUM(StockQty)
FROM
tblWine AS tblWineTwo
WHERE
tblWine.WineTypeID = tblWineTwo.WineTypeID
)
END
FROM
tblWine
Note: This view has been simplified and been done on the fly so may contain errorsdo this as a stored procedure and pass the variable in from the application.|||Why? Why not have two views and let the front end decide which one to call, based on the need of the operator? Follow the KISS principle.|||Keep It Simple Stupid? It took me a minute to remember that one.

I do not know. Maybe having one peice of code to maintain instead of 2. It does not really matter. Either way is valid.|||Exactly, I had thought of that but there is other C# code that are executed on the results and to duplicate it would cause more work in the long run.

I've also thought about using stored procedures but I'm using an ORM tool (MyGeneration dOOdads) which produce C# code classes for each of the tables and view. It automatically produces the stored procedures and connection code. I wouldn't want to do it this way as I'd have to provide other means of connecting to the database which again isn't good for maintainence.

I'm going to experiment a little more but if I can't crack it, I'm going to go with the method of creating two view just to keep the code cleaner.|||I've also thought about using stored procedures but I'm using an ORM tool (MyGeneration dOOdads) which produce C# code classes for each of the tables and view.Dear Lord...please tell me you aren't using NHibernate.
Tools such as this are a bad idea. They inevitably lead to ineffecient code, unscalable applications, and insecure databases.|||Lol, I'm using something called MyGeneration dOOdads (http://www.mygenerationsoftware.com/portal/dOOdads/Overview/tabid/63/Default.aspx) and it seems to have worked find over the past two years over various projects. Obviously its not flexible enough to cover problems like these but for what it provides, it's definitely worth the trade off. It's the #1 downloaded .NET tool on Download.com, apparently.|||"An Amazing 48k Architecture that Supports the 1.1 and 2.0 .NET Framework
Transactions, Dynamic Queries, and a Highly Intuitive API"
Dynamic Queries are to be AVOIDED. Tools such as this violate the most basic principles of database application design. They do so in the name of short-term development gains, and at the expense of long-term quality.|||But surely you would require the use of dynamic queries even without the help of an ORM tool, for example, an advanced search form. Or am I thinking of the wrong sort of dynamic queries?|||No, you would not need dynamic queries for an automated search form. And if dynamic sql is required it should be constructed with a stored procedure, not by an interface or middle-tier, which should not even have access to the underlying tables.

Conditional split on date ?

Hi,

I have a DT_DATE column. I'd like to achieve a conditional split to ignore all records for which the date is below a specific hardcoded date (eg: 2007-03-01).

I'm having a hard time trying to express this using the conditional split transform.

What is the correct syntax to express a DT_DATE literal ?

eg:
[date] < (DT_DATE) "2007-03-01"

regards

Thibaut

What you have should work fine. I built a little test package to verify, and each of these worked as expected:

Code Snippet

HireDate < (DT_DATE)"1998-01-30"

Code Snippet

[HireDate] < (DT_DATE)"1998-01-30"

Code Snippet

HireDate < (DT_DATE)"01/30/1998"

Code Snippet

[HireDate] < (DT_DATE)"01/30/1998"

What behavior are you experiencing that prompts you to ask the question?

|||Are you sure [date] is a DT_DATE column and not a DT_DBTIMESTAMP column? That is, does it contain a time component?

Just double checking.

Conditional split error message

Getting the below error msg on my conditional split. I changed the error output to ignore errors and that keeps the error msg from appearing (and everything seems to work normally), but why would it evaluate to NULL?

Thanks

[Conditional Split - Find rows with balances [3412]] Error: The expression "FINDSTRING(Column0,"OPENING",1) > 0 || FINDSTRING(Column0,"CLOSING",1) > 0" on "output "Balance Rows" (3415)" evaluated to NULL, but the "component "Conditional Split - Find rows with balances" (3412)" requires a Boolean results. Modify the error row disposition on the output to treat this result as False (Ignore Failure) or to redirect this row to the error output (Redirect Row). The expression results must be Boolean for a Conditional Split. A NULL expression result is an error.

Can Column0 be NULL?|||

I honestly don't know how.

The data file has between 4 and 6 rows on any given day - 4 of those rows always have "CLOSING" or "OPENING" in them.

So the conditional split should ignore the other rows, right?

That's why I don't understand why it's finding a null?

|||Try this expression:

FINDSTRING((ISNULL(Column0) ? "" : Column0),"OPENING",1) > 0 || FINDSTRING((ISNULL(Column0) ? "" : Column0),"CLOSING",1) > 0|||The problem may be that when evaluating Column0, some of the columns are NULL, and hence when the conditional split tries to evaluate the statement I provided to you a few days ago, it may fail. Using the new statement I just posted, we "trap" the fact that if the column is NULL, we set its contents to "" and continue on with the FINDSTRING statement.|||

So it's throwing the "NULL" error if finds any null columns (regardless of whether the row has OPENING or CLOSING in it, because it has to evaluate ALL rows?)

Such as, for example:

1234, ,1234 intstead of 1234," ",1234 ?

Is the first case above considered a null?

(Although I looked through my file, and I do not see any null fields at all)

|||Well, yes, that'd be true. ALL rows pass through the conditional split. The output path that gets chosen depends on which condition evaluates to true.|||

So to answer the other question,

a blank between file delimiters is considered a null value to ssis?

Such as 1234, ,1234 ?

I want to clarify this because it's got me concerned, as this situation is also causing problems with another file where it can't convert a value "without loss of data", because a numeric field is blank.

I was told to convert the value to string first, then convert it back to a numeric. Is this considered a best practice for working with numeric values?

Thanks

|||

Yes, it will be trated as NULL if your Flat File Source is configured to parse it that way. There is the property on the source adapter to control that.

It is not best the practice to convert numeric data to strings and back, but you need to make sure your numeric data is really numeric. NULLs should be fine if you can handle them downstream. It looks like your conditional split was not prepared for them.

Thanks,

~Bob

Conditional Split

I am using a conditional split to evaluate the condition below. It should only send records to my SQL Server database if the PatientZip matches one of the eight below and the PatientCity is not Wichita Falls (you wouldn't believe how bad this is mispelled sometimes). I checked the output table and it has all records for the zipcodes below both matching and non-matching the cityname of Wichita Falls. The table should not have entries for records with the cityname of Wichita Falls. Do I have the code correct or could I have missed something?

LTRIM(PatientCity) != "Wichita Falls" && (PatientZip == "76301" || PatientZip == "76302" || PatientZip == "76305" || PatientZip == "76306" || PatientZip == "76307" || PatientZip == "76308" || PatientZip == "76309" || PatientZip == "76310")

One thing to look at is if you're sending the correct output from the Conditional Split to your destination.

Another thing is that you may want to RTRIM to catch trailing spaces instead of just LTRIMming to catch leading spaces.

|||

Thanks for the replay Matthew. I added the RTRIM as you suggested. My output name for my condition is "Bad City Name" and the default output name is "Correct City Name". I connected each output to different SQL Server tables that are exactly the same except for the table names. The Bad City Name output table is still being populated with data that is actually correct (city = "Wichita Falls" and is in the zipcodes listed above). The Correct City Name output table is being populated with any and all entries except (city = "Wichita Falls" and is in the zipcodes listed above).

As a check; I just ran the following query against the source database after replacing the logical operators with their SQL equivalents and the double quotes (") with single quotes (') and the query returned exactly what I am attempting to achieve with Integration Services.

select PatientName, PatientCity, PatientState, PatientZip

from ampfm.rpt_PatientDemographics

where LTRIM(RTRIM(PatientCity)) != 'Wichita Falls'

and (PatientZip = '76301' or PatientZip = '76302'

or PatientZip = '76305' or PatientZip = '76306'

or PatientZip = '76307' or PatientZip = '76308'

or PatientZip = '76309' or PatientZip = '76310')

I am at a loss as to why the Integration Services routine is not returning the correct row data. I must have something designed incorrectly. This is the first of several similar packages I am creating as the cornerstone to our audit process, but I need the correct data in the output (reporting) tables first. Please advise anything you feel may be in error that I can check.

Thanks!

|||

Have you tried putting a data viewer on the path going into and out of the conditional split? It might help to see what data you are getting in, and what data is on which path going out... (perhaps you have your tables flipped on your destinations, etc)

|||

I appreciate your post. Yes, I had earlier added data viewers and they showed the same data that querying the output tables were showing. I have everything set correctly as far as I can tell, it just isn't working as expected.

I finally deleted the conditional split and went with a Lookup object using the query below and it is pulling the correct information and putting it in the correct output tables. I guess I'll try to tackle conditional split issues at another time.

select PatientName, PatientCity, PatientState, PatientZip

from ampfm.rpt_PatientDemographics3

where LTRIM(RTRIM(PatientCity)) <> 'Wichita Falls'

and LTRIM(RTRIM(PatientZip)) IN ('76301','76302',

'76305','76306','76307','76308','76309','76310')

Thanks to all who have responded.

Conditional SELECT

Dear Group

I'm having trouble with the statement below. I tried CASE and IF
without success. What I'm trying to do:
There is a field in the database called Business_TelNo. If the field
has some value, I would like to return a generated field
(LaBusinessTelNo), which is the label of Busines_TelNo, reading
'Phone:'
If Business_TelNo has no value, the label should be set to ''.

Something like this:
SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,
IF (LEN(Business_TelNo) > 0) BEGIN SELECT 'Phone: ' AS LaBusinessTelNo
END ELSE BEGIN SELECT '' AS LaBusinessTelNo END
FROM i2b_vw_contact

This is working:
SELECT i2b_vw_contact.Business_TelNo AS Business_TelNo,
'Phone: ' AS LaBusinessTelNo
FROM i2b_vw_contact

PS: I know it would be much easier to add some logic in the
application but need to do this in SQL.

Thanks very much for your time and efforts!

MartinSELECT business_telno,
CASE WHEN business_telno>'' THEN 'Phone: ' ELSE '' END AS labusiness_telno
FROM i2b_vw_contact

You can find the CASE and IF syntax in Books Online but understand that CASE
is an *expression* whereas IF is a *statement* and therefore IF can't be
used as part of a query.

--
David Portas
SQL Server MVP
--|||Thanks David!
Have a nice day :-)

Conditional Formatting in a Matrix Control

Hi there.

I am creating a report that the requirements need different background colors based on the row or column as shown below:

Community Total # of Respondents Resident's Overall Satisfaction Rating Quality of Repair May '07 41 3.6 5.0 April '07 14 1.8 3.0 Q2 '07 55 2.7 4.0 March '07 36 3.6 3.0 February '07 28 4.0 1.2 January '07 22 2.2 4.0 Q1 '07 86 3.3 2.7 YTD '07 141 3.0 3.2 December '06 33 3.8 4.2 November '06 27 2.6 5.0 October '06 42 1.8 3.0 Q4 '06 102 2.7 4.1 September '06 58 4.0 2.2 August '06 84 2.0 1.6 July '06 52 3.2 3.4 Q3 '06 194 3.1 2.4 June '06 40 2.4 4.2 May '06 41 3.6 5.0 April '06 14 1.8 3.0 Q2 '06 95 2.6 4.1 March '06 67 N/A 3.8 February '06 38 N/A 2.8 January '06 N/A 3.8 N/A Q1 '06 105 3.8 3.3 YTD '06 496 2.9 3.5 Rolling 12 Month Average 477 2.9 3.3

'

I can get the row colors to work great with an expression, but when I try to add the gray column with conditional formatting for the Resident's Overall Satisfaction Rating question, it clobbers my row formatting. I am thinking that I will have to do some gnarly expression in each of the rows and columns using the InScope function. Does that sound about right, or is there an easier way?

Thanks, Mike

Actually, this was easy once I looked at it again. On the detail cell, I just added another condition that identified the column in question and set the color appropriately. Works great.

Sometimes it just takes another look!

- Mike

conditional formatting - noob question

Hello,

I do not know vb. How can I turn the below idea into VB.NET and embed in a table object in RS?

IF Fields!someField.Value LIKE "%string%" THEN Fields!someField.Value

.. the goal is to only include values that contain the string, and exclude all other values from the table.

TYIA...You can try some filtering on the Table - right click on table - filters - add the field with a Like filter.|||

Yes, you should filter on the table.
Note: Like filters work with * instead of %. E.g. ="abc*"
-- Robert

Conditional Filtering in Reporting Services

I am using the following SQL query in a SSRS 2005 report (see below).
I need dbo.FilteredNew_Assets.new_assettypename = "Computer" ONLY when
the value of dbo.FilteredNew_Assets.new_assignedemployeeid is NOT
null. In other words, only when
dbo.FilteredNew_Assets.new_assignedemployeeid has a value do I need
dbo.FilteredNew_Assets.new_assettypename to be filtered.
Is this a task that needs to be accomplished within my SQL query or
somewhere within the context of the actual report? Either way, how do
I accomplish this?
SELECT dbo.FilteredNew_Employee.new_employeeid,
dbo.FilteredNew_Assets.new_assignedemployeeid,
dbo.FilteredNew_Employee.new_employeetypename,
dbo.FilteredNew_Employee.new_firstname,
dbo.FilteredNew_Employee.new_lastname,
dbo.FilteredNew_Assets.new_assettypename,
dbo.FilteredNew_Assets.new_computertypename,
dbo.FilteredNew_Assets.new_manufacturer,
dbo.FilteredNew_Assets.new_model,
dbo.FilteredNew_Assets.new_modelnumber,
dbo.FilteredNew_Assets.new_assetsid,
dbo.FilteredNew_Assets.new_name
FROM dbo.FilteredNew_Employee LEFT OUTER JOIN
dbo.FilteredNew_Assets ON
dbo.FilteredNew_Employee.new_employeeid = dbo.FilteredNew_Assets.new_assignedemployeeid
ORDER BY dbo.FilteredNew_Employee.new_lastnamePut filterednew_assets into a derived table like this and you=B4re able
to link on this derived table.
The union all reunites both sets of data, one set containing specific
conditional filter
SELECT *
FROM dbo.FilteredNew_Employee LEFT OUTER JOIN
(
SELECT
FNA.new_assignedemployeeid,
FNA.new_assettypename,
FNA.new_computertypename,
FNA.new_manufacturer,
FNA.new_model,
FNA.new_modelnumber,
FNA.new_assetsid
FROM dbo.FilteredNew_Assets FNA
WHERE FNA.new_assignedemployeeid IS NOT NULL And
FNA.New_assettypename =3D "Computer"
UNION ALL
SELECT
FNA.new_assignedemployeeid,
FNA.new_assettypename,
FNA.new_computertypename,
FNA.new_manufacturer,
FNA.new_model,
FNA.new_modelnumber,
FNA.new_assetsid
FROM dbo.FilteredNew_Assets FNA
WHERE FNA.new_assignedemployeeid IS NULL
) DerivedNewAssets ON dbo.FilteredNew_Employee.new_employeeid =3D
DerivedNewAssets.new_assignedemployeeid

Conditional calculation in SQL for CR

I am trying to calculate a value using an IF in a SQL statement. Below is what I am trying. I am not sure what is wrong with the IF. The error says missing operator.

SELECT
cr_401k_data_sheet1.`Department`,
SUM (cr_401k_data_sheet1.`Gross`) As Gross,
SUM (cr_401k_data_sheet1.`Contribution`) As Contribution,
SUM( cr_401k_data_sheet1.Gross*.05) As Limit,
(if SUM(Contribution) <> Limit then Contribution) As Match
FROM
`cr_401k_data_sheet1` cr_401k_data_sheet1
GROUP BY
cr_401k_data_sheet1.`Department`,

Thanks in advance,
vmonyou cannot use IF in crystal SQL, infect you cannot use IF in any SQL, untill you are using a stored procedure

how ever you can do this calulation in Crystal, from your query it seems like you want to calculate MACTH where Sum(Contribution) <> SUM(LIMIT), thats you can do in crystal by writing a formula.

Condition validation on Crystal report Fields

Hi Folks,

I am CR XI..I have 2 numeric Fields in Report.I want to update the Field Data based on Below Condition.

Let us assume report Fields Like A, B

Condition: if A>10 and B=10 then B='Good'(String)
else B=B(earlier data)

Please help me out How do I apply this logic

Urgent...

ThnaksIf the field was numeric in the DB, and you want to change the value of the field in some records to an alphanumeric ('Good') you gotta problem. Create another field.|||Hi Folks,

I am CR XI..I have 2 numeric Fields in Report.I want to update the Field Data based on Below Condition.

Let us assume report Fields Like A, B

Condition: if A>10 and B=10 then B='Good'(String)
else B=B(earlier data)

Please help me out How do I apply this logic

Urgent...

Thnaks

you can't update field of your data base and which are used in
crystal report.

you would have create a formula to it.

condense this working SQL into an algorithm

What is listed below works but I would like to condense it into a loop. Any
ideas? Thank you. -Greg
-- peform substring at these locations in column 1,5,9,13,17,21,25
CREATE TABLE #one
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #one
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #two
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #two
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 5, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #three
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #three
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 9, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #four
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #four
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 13, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #five
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #five
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 17, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #six
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #six
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 21, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE #seven
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO #seven
(AreaCode, TimeZone)
select distinct SUBSTRING(AreaCode, 25, 3) AS AreaCode,TimeZone
from ZipCodeDatabase_DELUXE
CREATE TABLE total
(AreaCode varchar(50),
TimeZone varchar(50))
insert into total
SELECT * FROM #one
UNION ALL
SELECT * FROM #two
UNION ALL
SELECT * FROM #three
UNION ALL
SELECT * FROM #four
UNION ALL
SELECT * FROM #five
UNION ALL
SELECT * FROM #six
UNION ALL
SELECT * FROM #sevenUntested:
INSERT total
SELECT DISTINCT SUBSTRING(AreaCode, a.Start * 4 + 1, 3)
, TimeZone
FROM ZipCodeDatabase_DELUXE, (SELECT 0 UNION SELECT 1 UNION SELECT 2
UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) a (Start)
-Alan|||Awesome Alan ! Works perfectly! Now I have to digest what you did along with
my lunch. ;-) -appreciatively -greg
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1141750087.253473.75260@.z34g2000cwc.googlegroups.com...
> Untested:
> INSERT total
> SELECT DISTINCT SUBSTRING(AreaCode, a.Start * 4 + 1, 3)
> , TimeZone
> FROM ZipCodeDatabase_DELUXE, (SELECT 0 UNION SELECT 1 UNION SELECT 2
> UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) a (Start)
> -Alan
>|||well, first lets break down your results into a set of UNIONed queries:
INSERT total
SELECT SUBSTRING(AreaCode, 1, 3), TimeZone FROM ...
UNION
SELECT SUBSTRING(AreaCode, 1, 3), TimeZone FROM ...
Simple enough, right? Next, when you have multiple entities in your
FROM clause with no JOIN expression, SQL extrapolates all combinations.
SELECT *
FROM (SELECT 1 Number UNION SELECT 2) a
, (SELECT 'A' UNION SELECT 'B') b (Letter)
This is what I did, only I used a subquery that unioned the numbers 0
through 6. I recognized a linear pattern of your start position, so I
used that in the SUBSTRING function. While I could've not used that
formula and used the values 1, 5, 9, et cetera in my UNIONed list of
numbers, I thought it made things a little cleaner to use the formula
in the SUBSTRING function. The above query shows two ways of naming
your columns. I used the latter.
-Alan|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
instead of CHAR(3)' Likewise area_)code? etc.
Why do your tables all have no keys' Don't you know that there are no
loops in a declarative language? There are UNIONs in SQL and you can
use them to split up this mess. But why would have such poor data in
the first place?|||--CELKO-- (jcelko212@.earthlink.net) writes:
> Your DDL sucks. Please give an example of a time_zone that is CHAR(50)
> instead of CHAR(3)' Likewise area_)code? etc.
char(3) for a time zone? Need char(5), sign + four digits.
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|||>> har(3) for a time zone? Need char(5), sign + four digits. <<
That is a time displacement; a zone is "PST', etc. Also, I seem to
remember that we got rid of the "old fractional hours" displacements a
few yers ago, so you can use an integer off of UTC.|||--CELKO-- wrote:
> That is a time displacement; a zone is "PST', etc. Also, I seem to
> remember that we got rid of the "old fractional hours" displacements a
> few yers ago, so you can use an integer off of UTC.
Except there are still places in the world which are, for instance, 5
1/2 hours ahead of UTC (India)
Damien

Concurrent Queries

Hi
Would like to ask anyone who knows what are the probable causes of the error below
This SQL Server has been optimized for %d concurrent queries. This limit has been exceeded by %d queries and performance may be adversely affected
Does adding memory helps. this error occurs every 5 mins
JefYou can get the error when you are using MSDE or are using
the Personal Edition. If you are getting the error that
often, you should consider upgrading to SQL Server standard
or higher. Adding memory won't make a difference.
-Sue
On Mon, 1 Dec 2003 15:06:10 -0800, "Jeff"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>Would like to ask anyone who knows what are the probable causes of the error below:
>This SQL Server has been optimized for %d concurrent queries. This limit has been exceeded by %d queries and performance may be adversely affected.
>Does adding memory helps. this error occurs every 5 mins.
>Jeff
>

Concurrency Issues

Is there any way to get the sample below working so that both "threads" are guaranteed to get unique and incrementing values?

I'm suspecting the answer is no. You can use transactions on completely database oriented operations that read/write to a database and complete. But there aren't complete synchronization controls for operations like below that try to return a value to an outside process.

IF OBJECT_ID('SimpleTable') IS NOT NULL
DROP TABLE SimpleTable

CREATE TABLE SimpleTable (
A INTEGER
)
INSERT INTO SimpleTable (A) VALUES (1)

-- Run in one window
DECLARE @.value INTEGER

BEGIN TRANSACTION
SELECT TOP 1 @.value = A FROM SimpleTable
WAITFOR DELAY '00:00:05'
UPDATE SimpleTable SET A = @.value + 1
COMMIT TRANSACTION

SELECT @.value
SELECT A FROM SimpleTable

-- Run in a second window
DECLARE @.value INTEGER

BEGIN TRANSACTION
SELECT TOP 1 @.value = A FROM SimpleTable
UPDATE SimpleTable SET A = @.value + 1
COMMIT TRANSACTION

SELECT @.value
SELECT A FROM SimpleTableUse an identity property, instead of code. While you might loose a few values if threads (spids) die for some reason, and you might cause other holes by deleting rows, the values will be unique and monotonically increasing.

-PatP|||Use an identity property, instead of code. While you might loose a few values if threads (spids) die for some reason, and you might cause other holes by deleting rows, the values will be unique and monotonically increasing.

-PatP

Actually, I did just that. However, I was kind of curious if it was possible to acheive with transactions or with some explicit locking calls.|||There definitely is a way to do it using just SQL statements with Transact-SQL's locking model. The identity process is simpler and supported though.

-PatP|||You'll need to use (TABLOCK) or (UPDLOCK) as table hints to ensure ACID properties of a transaction while attempting to generate an artificial IDENTITY value.|||You'll need to use (TABLOCK) or (UPDLOCK) as table hints to ensure ACID properties of a transaction while attempting to generate an artificial IDENTITY value.

Thank you. That will work. Except BOL calls those locking "hints" which means the solution isn't guaranteed to work even though it probably will on today's implementations.|||I don't see how it will not work, but you're free to refute it.|||It won't work if a future implementation (either a future version or service pack of SQL Server) decides to ignore the locking "hint". The database engine is completely free to obey or ignore "hints" at will so you shouldn't base an algorithm on that.

I don't see how it will not work, but you're free to refute it.

concatenation of '0' + converted interger value into a string?

I am unsure as to why I cannot concatenate '0' with the when '1' case
statement below. Even thought the convert statement explicitly converts the
integer, my results remain unchanged. I would appreciate any assistance...
SELECT
case LEN(datepart(m,trandate))
when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
when '2' then DATEPART(M,trandate)
end
FROM Offtable where trandate is not nullHi Jeff,
You can strip the monthpart out of string representation of a date, that
will always include a leading zero when necessary, so you don't have to
worry about that, for example:
SELECT CONVERT(CHAR(2), trandate, 1)
FROM Offtable where trandate is not null
Style 1 with convert returns mm/dd/yy, and we are only interested in the
leftmost two characters, so a CHAR(2) will do.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message
news:uYhK335dDHA.1636@.TK2MSFTNGP12.phx.gbl...
> I am unsure as to why I cannot concatenate '0' with the when '1' case
> statement below. Even thought the convert statement explicitly converts
the
> integer, my results remain unchanged. I would appreciate any
assistance...
>
> SELECT
> case LEN(datepart(m,trandate))
> when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
> when '2' then DATEPART(M,trandate)
> end
> FROM Offtable where trandate is not null
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0181_01C37783.62DF5350
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
There I go again:
select
replace (str (datepart (mm, TranDate), 2), ' ', '0')
from
Offtable
where
trandate is not null
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:OEKO955dDHA.1152@.TK2MSFTNGP11.phx.gbl...
You can rewrite the statement:
select
replace (str (TranDate, 2), ' ', '0')
from
Offtable
where
trandate is not null
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jeff Humphrey" <jeffhumphrey@.cox-internet.com> wrote in message =news:uYhK335dDHA.1636@.TK2MSFTNGP12.phx.gbl...
I am unsure as to why I cannot concatenate '0' with the when '1' case
statement below. Even thought the convert statement explicitly converts =the
integer, my results remain unchanged. I would appreciate any =assistance...
SELECT
case LEN(datepart(m,trandate))
when '1' then '0' + CONVERT(varchar(1),DATEPART(M, trandate))
when '2' then DATEPART(M,trandate)
end
FROM Offtable where trandate is not null
--=_NextPart_000_0181_01C37783.62DF5350
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

There I go again:
select
replace (str =(datepart (mm, TranDate), 2), ' ', '0')
from
=Offtable
where
trandate is not null
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Tom Moreau" = wrote in message news:OEKO955dDHA.1152=@.TK2MSFTNGP11.phx.gbl...
You can rewrite the =statement:
select
replace (str =(TranDate, 2), ' ', '0')
from
=Offtable
where
trandate is not null
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jeff Humphrey" wrote in message news:uYhK335dDHA.1636=@.TK2MSFTNGP12.phx.gbl...I am unsure as to why I cannot concatenate '0' with the when '1' =casestatement below. Even thought the convert statement explicitly converts theinteger, my results remain unchanged. I would appreciate =any assistance...SELECT case LEN(datepart(m,trandate)) when '1' then '0' =+ CONVERT(varchar(1),DATEPART(M, trandate)) =when '2' then DATEPART(M,trandate) end FROM Offtable where =trandate is not null

---

--=_NextPart_000_0181_01C37783.62DF5350--

Concatenating a fully qualified name

In the code below, the DatabaseName and table_id are variables. The table_id
is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
that I can pass it to the parameter @.database. In other words, how do I
concatenate so that it will be something like this @.db.dbo.tblName:
CREATE PROCEDURE sampleProcedure
(
@.database varchar (100),
@.table_id uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE @.db varchar (100)
SELECT @.db = @.database
INSERT INTO DatabaseName.dbo.tblName
SELECT * FROM tblName where table_id=@.table_idHello,
You need to use Dynamic SQL to do this.After that execute the Dynamic SQL
using EXEC or SP_ExecuteSQL. Take a look int the article.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92783839-67A2-4880-938D-699519E3A9C0@.microsoft.com...
> In the code below, the DatabaseName and table_id are variables. The
> table_id
> is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
> that I can pass it to the parameter @.database. In other words, how do I
> concatenate so that it will be something like this @.db.dbo.tblName:
> CREATE PROCEDURE sampleProcedure
> (
> @.database varchar (100),
> @.table_id uniqueidentifier
> )
> AS
> SET NOCOUNT ON
> DECLARE @.db varchar (100)
> SELECT @.db = @.database
> INSERT INTO DatabaseName.dbo.tblName
> SELECT * FROM tblName where table_id=@.table_id

Concatenating a fully qualified name

In the code below, the DatabaseName and table_id are variables. The table_id
is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
that I can pass it to the parameter @.database. In other words, how do I
concatenate so that it will be something like this @.db.dbo.tblName:
CREATE PROCEDURE sampleProcedure
(
@.database varchar (100),
@.table_id uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE @.db varchar (100)
SELECT @.db = @.database
INSERT INTO DatabaseName.dbo.tblName
SELECT * FROM tblName where table_id=@.table_idHello,
You need to use Dynamic SQL to do this.After that execute the Dynamic SQL
using EXEC or SP_ExecuteSQL. Take a look int the article.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92783839-67A2-4880-938D-699519E3A9C0@.microsoft.com...
> In the code below, the DatabaseName and table_id are variables. The
> table_id
> is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
> that I can pass it to the parameter @.database. In other words, how do I
> concatenate so that it will be something like this @.db.dbo.tblName:
> CREATE PROCEDURE sampleProcedure
> (
> @.database varchar (100),
> @.table_id uniqueidentifier
> )
> AS
> SET NOCOUNT ON
> DECLARE @.db varchar (100)
> SELECT @.db = @.database
> INSERT INTO DatabaseName.dbo.tblName
> SELECT * FROM tblName where table_id=@.table_id

Concatenating a fully qualified name

In the code below, the DatabaseName and table_id are variables. The table_id
is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
that I can pass it to the parameter @.database. In other words, how do I
concatenate so that it will be something like this @.db.dbo.tblName:
CREATE PROCEDURE sampleProcedure
(
@.database varchar (100),
@.table_id uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE @.db varchar (100)
SELECT @.db = @.database
INSERT INTO DatabaseName.dbo.tblName
SELECT * FROM tblName where table_id=@.table_id
Hello,
You need to use Dynamic SQL to do this.After that execute the Dynamic SQL
using EXEC or SP_ExecuteSQL. Take a look int the article.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92783839-67A2-4880-938D-699519E3A9C0@.microsoft.com...
> In the code below, the DatabaseName and table_id are variables. The
> table_id
> is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
> that I can pass it to the parameter @.database. In other words, how do I
> concatenate so that it will be something like this @.db.dbo.tblName:
> CREATE PROCEDURE sampleProcedure
> (
> @.database varchar (100),
> @.table_id uniqueidentifier
> )
> AS
> SET NOCOUNT ON
> DECLARE @.db varchar (100)
> SELECT @.db = @.database
> INSERT INTO DatabaseName.dbo.tblName
> SELECT * FROM tblName where table_id=@.table_id