Query Syntax: error

Showing posts with label error. Show all posts

Friday, March 30, 2012

Query, what's wrong here?

Hi
I get an error when I try to limit the "digits" I want to summerize, I have
only added the line
"and digits in (select digits from series)"
when it there the it fails and gives the errpr Msg 446, Level 16, State 9,
Line 8
The only thing I can imagine is that it's because I'm "joining" with another
table. the datatypes are identical.
declare @.site int
declare @.calltype char(1)
set @.site = 1
set @.calltype = 'E'
SELECT digits, COUNT(*) AS total, /* Line 9 */
COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC'
THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'RL'
AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND durationofcall >= 40
THEN 1 END) AS ubesvaret
FROM V2tickets WHERE
siteid = @.site AND calltype = @.calltype AND LEN(digits) = 4
/* adding this statement gives the errpr Msg 446, Level 16, State 9, Line 8
*/
and digits in (select digits from series)
GROUP BY digits
ORDER BY digits
regards
HenryThe error is due to a collation conflict. It looks like the Digits
column in Series must have a different collation to the Digits column
in V2tickets. Either change one of the collations or specify an
explicit collation in the query.
See Books Online for more information on collations.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> skrev i en
meddelelse news:1108503649.678190.90910@.z14g2000cwz.googlegroups.com...
> The error is due to a collation conflict. It looks like the Digits
> column in Series must have a different collation to the Digits column
> in V2tickets. Either change one of the collations or specify an
> explicit collation in the query.
> See Books Online for more information on collations.
Been there, done that, fixed the problem, a lot of stuff to keep track off
here.
Thanks for pointing me the right direction.
regards
Henry

Query Works in SQL 2000 but not in SQL 2005

The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

=======================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)

when I modify the query like the following in SQL Server 2005 it works. Now the problem is since it is adynamically generated query from our application based on users selection of criteria, it means a lot to us to change the code.

PLEASE HELP....

=====================================================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'

EXCEPT

((SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ))

This is not a bug. This is due to the order in which certain expressions are evaluated. The query optimizer is free to move expressions around to determine the optimum plan. You could get this error in SQL Server 2000 also but it is more easy to hit in SQL Server 2005 due to newer optimizations to match computed column indexes/indexed views. This is documented in the behavior changes topic in BOL. See the link below and search for "Expressions in queries":

http://msdn2.microsoft.com/en-us/library/ms143359(SQL.90).aspx

Basically, you need to use CASE expression in all the places where you are doing the CONVERT(float) with ISNUMERIC check in the WHEN clause. You will of course get slightly bad performance due to the complexity of the expressions. The correct approach however is to normalize the table and not mix different data domains in the same column.

|||

One of my biggest concerns is that in SQL Server 2000 compatibility mode the behaviour is different to SQL Server 2005.

Also, it would be really useful if there were a simple keyword which would switch off some of the query optimisation.

sql

Query Works in SQL 2000 but not in SQL 2005

The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to float.

=======================

PLEASE HELP....

=====================================================

SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@.%'

EXCEPT

http://msdn2.microsoft.com/en-us/library/ms143359(SQL.90).aspx

|||

One of my biggest concerns is that in SQL Server 2000 compatibility mode the behaviour is different to SQL Server 2005.

Also, it would be really useful if there were a simple keyword which would switch off some of the query optimisation.

Query Works in QA But not in Code

I am building a query in code (ASP) and when I execute the query, I get the
following error:
========================================
========
Microsoft OLE DB Provider for SQL Server error '80040e37'
Invalid object name 'Circuits2005'.
========================================
=========
I added a Response.Write to list the query and it is as follows:
========================================
=========
SELECT DISTINCT Circuits2005.CircuitID, Circuits2005.CircuitName FROM
Circuits2005 INNER JOIN SPScores2005 ON SPScores2005.CircuitID =
Circuits2005.CircuitID UNION SELECT DISTINCT Circuits2005.CircuitID,
Circuits2005.CircuitName FROM Circuits2005 INNER JOIN DPScores2005 ON
DPScores2005.CircuitID = Circuits2005.CircuitID ORDER BY CircuitName
========================================
===========
If I copy/past that into QA, it works fine? "Circuits2005" is a valid table
name?
Any ideas?
Waynewho owns the table?
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:OZIcCcEVFHA.628@.tk2msftngp13.phx.gbl...
>I am building a query in code (ASP) and when I execute the query, I get the
>following error:
> ========================================
========
> Microsoft OLE DB Provider for SQL Server error '80040e37'
> Invalid object name 'Circuits2005'.
> ========================================
=========
> I added a Response.Write to list the query and it is as follows:
> ========================================
=========
> SELECT DISTINCT Circuits2005.CircuitID, Circuits2005.CircuitName FROM
> Circuits2005 INNER JOIN SPScores2005 ON SPScores2005.CircuitID =
> Circuits2005.CircuitID UNION SELECT DISTINCT Circuits2005.CircuitID,
> Circuits2005.CircuitName FROM Circuits2005 INNER JOIN DPScores2005 ON
> DPScores2005.CircuitID = Circuits2005.CircuitID ORDER BY CircuitName
> ========================================
===========
> If I copy/past that into QA, it works fine? "Circuits2005" is a valid
> table name?
> Any ideas?
> Wayne
>|||In addition, make sure you're actually hitting the proper database. Make
sure, if the user you're connecting with has a different default database
than the one containing the Circuits2005 table, that you are switching to
the proper database before performing the select.
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:uvfIzeEVFHA.4000@.TK2MSFTNGP14.phx.gbl...
> who owns the table?
> "Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
> news:OZIcCcEVFHA.628@.tk2msftngp13.phx.gbl...
the
>|||Hi Wayne
rewrite the query as
SELECT DISTINCT Circuits2005.CircuitID, Circuits2005.CircuitName FROM
Circuits2005 INNER JOIN SPScores2005 ON SPScores2005.CircuitID =
Circuits2005.CircuitID UNION SELECT DISTINCT Circuits2005.CircuitID,
Circuits2005.CircuitName FROM <database_name>..Circuits2005 Circuits2005
INNER JOIN DPScores2005 ON
DPScores2005.CircuitID = Circuits2005.CircuitID ORDER BY CircuitName
best Regards,
Chandra
---
"Wayne Wengert" wrote:

> I am building a query in code (ASP) and when I execute the query, I get th
e
> following error:
> ========================================
========
> Microsoft OLE DB Provider for SQL Server error '80040e37'
> Invalid object name 'Circuits2005'.
> ========================================
=========
> I added a Response.Write to list the query and it is as follows:
> ========================================
=========
> SELECT DISTINCT Circuits2005.CircuitID, Circuits2005.CircuitName FROM
> Circuits2005 INNER JOIN SPScores2005 ON SPScores2005.CircuitID =
> Circuits2005.CircuitID UNION SELECT DISTINCT Circuits2005.CircuitID,
> Circuits2005.CircuitName FROM Circuits2005 INNER JOIN DPScores2005 ON
> DPScores2005.CircuitID = Circuits2005.CircuitID ORDER BY CircuitName
> ========================================
===========
> If I copy/past that into QA, it works fine? "Circuits2005" is a valid tabl
e
> name?
> Any ideas?
> Wayne
>
>|||Thanks for the responses. It turns out that although the initial connection
was correct, there was another connection statement that still pointed to an
old database. I got rid of that and things are working fine now.
Wayne
"Wayne Wengert" <wayneSKIPSPAM@.wengert.org> wrote in message
news:OZIcCcEVFHA.628@.tk2msftngp13.phx.gbl...
>I am building a query in code (ASP) and when I execute the query, I get the
>following error:
> ========================================
========
> Microsoft OLE DB Provider for SQL Server error '80040e37'
> Invalid object name 'Circuits2005'.
> ========================================
=========
> I added a Response.Write to list the query and it is as follows:
> ========================================
=========
> SELECT DISTINCT Circuits2005.CircuitID, Circuits2005.CircuitName FROM
> Circuits2005 INNER JOIN SPScores2005 ON SPScores2005.CircuitID =
> Circuits2005.CircuitID UNION SELECT DISTINCT Circuits2005.CircuitID,
> Circuits2005.CircuitName FROM Circuits2005 INNER JOIN DPScores2005 ON
> DPScores2005.CircuitID = Circuits2005.CircuitID ORDER BY CircuitName
> ========================================
===========
> If I copy/past that into QA, it works fine? "Circuits2005" is a valid
> table name?
> Any ideas?
> Wayne
>

Wednesday, March 28, 2012

Query with user-define function John Bell

Yes!! It works. But just a point. In the [Index] column I have both strings
and numbers. When I query [Index]=AA there is an error: Invalid column name
'AA'
Any more suggestion?
Thank you
Helen
"John Bell" wrote:

> Hi
> Maybe
> CREATE TABLE foo ( [index] int not null identity(1,1), a int, b int, c
> varchar(10) )
> INSERT INTO Foo ( a, b, c ) SELECT 5,9,'a+@.q+3*b'
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
> where [Index]=1'
> FROM dbo.foo where [Index]=1
> EXEC(@.sql)
> John
>
> "Helen" <Helen@.discussions.microsoft.com> wrote in message
> news:A3A0BB12-2B9D-4173-81B3-F17D30F6D59B@.microsoft.com...
>
>Hi
If your column is a character datatype use 'AA' As quotes will require
escaping with a second quote within the string you end up with:
CREATE TABLE foo ( [index] CHAR(2) not null , a int, b int, c
varchar(10) )
INSERT INTO Foo ( [Index],a, b, c ) SELECT 'AA',5,9,'a+@.q+(3*b)'
DECLARE @.sql VARCHAR(255)
SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
where [Index]=''AA'''
FROM dbo.foo where [Index]='AA'
EXEC(@.sql)
John
"Helen" <Helen@.discussions.microsoft.com> wrote in message
news:196B3526-043F-40E1-9BD5-F1D6669C093A@.microsoft.com...
> Yes!! It works. But just a point. In the [Index] column I have both
> strings
> and numbers. When I query [Index]=AA there is an error: Invalid column
> name
> 'AA'
> Any more suggestion?
> Thank you
> Helen
> "John Bell" wrote:
>|||Yes!!! It is all right now. I wonder how to write this in visualbasic.net
Thanks
Helen
"John Bell" wrote:

> Hi
> If your column is a character datatype use 'AA' As quotes will require
> escaping with a second quote within the string you end up with:
> CREATE TABLE foo ( [index] CHAR(2) not null , a int, b int, c
> varchar(10) )
> INSERT INTO Foo ( [Index],a, b, c ) SELECT 'AA',5,9,'a+@.q+(3*b)'
> DECLARE @.sql VARCHAR(255)
> SELECT @.sql = 'DECLARE @.q int SET @.q=7 SELECT a,b,' + c+ ' FROM dbo.foo
> where [Index]=''AA'''
> FROM dbo.foo where [Index]='AA'
> EXEC(@.sql)
> John
> "Helen" <Helen@.discussions.microsoft.com> wrote in message
> news:196B3526-043F-40E1-9BD5-F1D6669C093A@.microsoft.com...
>
>

Wednesday, March 7, 2012

Query times out when executed from 1 server

I have a weird problem.
It all started when one of our clients reported having an error
accessing one of our pages. We found that the cause of the error
resulted in a timed-out query.
When we execute a certain query using Query analyser from our Web
Server, the query times out.
When executing exactly the same query using any other server/machine
with Query Analyser, the query runs within 1 second.
Same SQL Server, same database, same query.
We've pinpointed the problem to a nvarchar column containing the
substring "CO." in one of its returned value.
When we remove the point from the "CO.", the query runs without
problems from our web server.
Anybody has an idea of why this could happen and how we can prevent
this from happening.
Many Thanx.Hi,
Probably your statistics might be outdated. Could you execute a update
statistics on that table and try executing the query.
See the execution plan for the index usage as well.
Thanks
Hari
SQL Server MVP
"Alex V" <alex.vezeau@.gmail.com> wrote in message
news:1128706938.421519.122200@.g49g2000cwa.googlegroups.com...
>I have a weird problem.
> It all started when one of our clients reported having an error
> accessing one of our pages. We found that the cause of the error
> resulted in a timed-out query.
> When we execute a certain query using Query analyser from our Web
> Server, the query times out.
> When executing exactly the same query using any other server/machine
> with Query Analyser, the query runs within 1 second.
> Same SQL Server, same database, same query.
> We've pinpointed the problem to a nvarchar column containing the
> substring "CO." in one of its returned value.
> When we remove the point from the "CO.", the query runs without
> problems from our web server.
> Anybody has an idea of why this could happen and how we can prevent
> this from happening.
> Many Thanx.
>|||Updated the statistics and problem still there.|||Other example of timeout:
Select name as NAME_USER FROM USERTABLE WHERE USERID = 4
This query times out on our webserver but not on any other server.
If I change the NAME_USER for NAMEUSER, it works on all servers.
If I change the 4 for a 6, Times out on the web server but works on all
the other servers.
If I change the 4 for a 6 and, NAMEUSER for NUSER, it works on all
servers ...
And it goes on and on ...

Query timeout expired on Analysis 2005

Hello

During deployment process of large db ~200M rows I got the error

Error 1 OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00

The Analysis 2005 "data source view" connected to mssql 2000 that select the 200M rows from view.

Please advice what to do.

Thanks

Amir

If you are using the Enterprise Edition of SSAS2005 have a look at partions. You can divide cubes by using partitions and select different time periods for each partition.

With these you do not have to load all data at once.

Regards

Thomas Ivarsson

Query Timeout

I have a query I run in MSDE that will give me a timeout error message. It
reads:
"Timeout expired. The time out period elapsed prior to completion of the
operation or the server is not responding."
I know the server is responding, because the query seems to run anyway,
though I'm not sure the results are accurate. Is there any way to extend the
timeout period so this does not happen. The query running is adding
numerical records to one table based on criteria in the query and values in
another table.
What application are you using to query the database? If it's something your
wrote in-house and you are using ADO then set the CommandTimeout property of
the connection to 0 (zero). Some ADO libraries default to 30 seconds for a
timeout.
Jim
"Richard" <Richard@.discussions.microsoft.com> wrote in message
news:B8EFC32E-54F0-4CBB-A2F9-36699FA895B6@.microsoft.com...
> I have a query I run in MSDE that will give me a timeout error message.
It
> reads:
> "Timeout expired. The time out period elapsed prior to completion of the
> operation or the server is not responding."
> I know the server is responding, because the query seems to run anyway,
> though I'm not sure the results are accurate. Is there any way to extend
the
> timeout period so this does not happen. The query running is adding
> numerical records to one table based on criteria in the query and values
in
> another table.

Saturday, February 25, 2012

Query time out while inserting data

Hi guys,
when inserting data in a table i am getting the following
error:
Error : -2147217871
Source : Microsoft OLE DB Provider for SQL Server
Descripion : Timeout expired
Help Context : 1000440
The data is added through a stored procedure which is
called under MTS from a dll which in turn is accessed
through a ASP page.
The table has arround 150,000 records. This is not a bluk
insert. Why am i getting this error, is this a locking
problem?
Please reply ASAP
Thanking you.Could be a locking problem. Use EM, Current Activity, Profiler, sp_who, sp_who2 and sp_lock to
troubleshoot.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Saleem Subhi" <saleem_subhi@.hotmail.com> wrote in message
news:39d201c37614$137a6e00$a301280a@.phx.gbl...
> Hi guys,
> when inserting data in a table i am getting the following
> error:
> Error : -2147217871
> Source : Microsoft OLE DB Provider for SQL Server
> Descripion : Timeout expired
> Help Context : 1000440
> The data is added through a stored procedure which is
> called under MTS from a dll which in turn is accessed
> through a ASP page.
> The table has arround 150,000 records. This is not a bluk
> insert. Why am i getting this error, is this a locking
> problem?
> Please reply ASAP
> Thanking you.
>

Query that works in Management Studio, fails in Reporting Services

I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:

declare @.starttime as datetime
declare @.endtime as datetime
declare @.timezone as integer
declare @.date as datetime

set @.timezone = 1
set @.date = '5/1/2007'

set @.starttime = dateadd(hh, @.timezone, @.date)
set @.endtime = dateadd(d, 1, @.starttime)

select @.Starttime, @.endtime from site

Error Message:

TITLE: Microsoft Report Designer

An error occurred while executing the query.
The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.

ADDITIONAL INFORMATION:

The variable name '@.starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476

BUTTONS:

What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).

Any help would be greatly appreciated!

Add the word "my" as a prefix to your variables:

DECLARE @.myStartTime smalldate time

etc...

Adamus

|||

Hi,

Please note that expressions in SSRS are case sensitive. In the code above, the starttime is declared as @.starttime and while referring to it in the select clause, you have typed it as @.Starttime. This is causing the conflict. SSRS created a report parameter, Starttime. Since SQL is not case sensitive, while execution it got two declarations for @.starttime and hence the error that you are facing.

After changing the Select clause, delete the parameter from Reports --> Parameters menu option and also from the dataset --> parameters Tab and your query should work.

Also, there is no need for the from clause.

HTH.

Regards,

Ashish

|||They are in fact case-sensitive and I've corrected it and the query now runs is RS. Thank you so much!!!

Monday, February 20, 2012