Query written in CODE part is not working

Hi
I have a report in which the Dataset is filled using MDX query and for every row in the DataSet the Function in the CODE is called, which in turn Connects to the DB and Selects a particular value. The data set is getting filled but the CODE (which contains the SQL Select query) is not getting executed.
I have references System.data, System.data.SQLClient, System.data.Xml dlls for a RDL and have written a connection string with UID and Pwd and the userid has sufficient permissions.
When i Preview it(using visual studio), the value(O/P, the expected value) is shown in the report but i dont see any query executed in the SQL Profiler. Also when i preview this report through the report viewer control from an Window Application, i dont get the output. I dont seem to figure out what the problem is. Please HELP!!

Thanking you in advance.
Regards
Sai
Not sure if I undestand your scenario well. Do you have a VB.NET function embedded in the report? If so, most likely the function is erroring out or you are facing a security issue. What I'd suggest is moving the code to an external .NET assembly. Then, set your report as a startup item on the project properties. Put a breakpoint in the custom function and hit F5 to load the report in the Report Host. When the report is run, the breakpoint should be hit from the first dataset row and you should be able to troubleshoot what's wrong.sql

Query written in CODE part is not working

Hi
I have a report in which the Dataset is filled using MDX query and for every row in the DataSet the Function in the CODE is called, which in turn Connects to the DB and Selects a particular value. The data set is getting filled but the CODE (which contains the SQL Select query) is not getting executed.

I have references System.data, System.data.SQLClient, System.data.Xml

dlls for a RDL and have written a connection string with UID and Pwd

and the userid has sufficient permissions.
When i Preview it(using visual studio), the value(O/P, the expected value) is shown in the report but i dont see any query executed in the SQL Profiler. Also when i preview this report through the report viewer control from an Window Application, i dont get the output. I dont seem to figure out what the problem is. Please HELP!!

Thanking you in advance.
Regards
SaiNot sure if I undestand your scenario well. Do you have a VB.NET function embedded in the report? If so, most likely the function is erroring out or you are facing a security issue. What I'd suggest is moving the code to an external .NET assembly. Then, set your report as a startup item on the project properties. Put a breakpoint in the custom function and hit F5 to load the report in the Report Host. When the report is run, the breakpoint should be hit from the first dataset row and you should be able to troubleshoot what's wrong.

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
>

Query using DATEPART

Hello.

The purpose of the code below is to produce graphs in a report (thus the SELECT DATEPART so the month and year appear on the graphs using COUNT in the report.)I have two challenges:

1. How should the code be modified so that it returns the previous two months at the start of a new year?(I.e. In Jan. 2008, returning Nov. & Dec. 2007, and in Feb. 2008, returning Dec. 2007 & Jan 2008, etc.)

2. How should the code be modified so that the DatePart and DateName data are treated as dates yet still appear as the Month Name and Year (June 2007)? Currently it is treating them as alphanumeric so that on the chart, July is coming before June.

Thank you.

SELECT DISTINCT TBL_01.id, TBL_01.url, TBL_01.source, Tbl_02.type, TBL_01.time, Tbl_03.time, DATENAME(month, TBL_01.time) + ' ' + DATENAME(year,TBL_01.time) as Month_Year

FROMTbl_03 INNER JOIN

Tbl_04 ON Tbl_03.id = Tbl_04.endsTBL_01 LEFT OUTER JOIN

Tbl_02 ON Tbl_04.endsTBL_01 = Tbl_02.id LEFT OUTER JOIN

Tbl_05 ON Tbl_04.ursid = Tbl_05.SubId INNER JOIN

Tbl_03 AS TBL_01 ON TBL_01.id = Tbl_04. EFP

WHERE(Tbl_04. EFP IS NOT NULL) AND (Tbl_05.grade IS NULL OR

Tbl_05.grade = 'NC')

AND (DATEPART(month, TBL_01.time) < (SELECT DATEPART(month, getutcdate()))

AND DATEPART(month, TBL_01.time) > (SELECT DATEPART(month, getutcdate()))-3

AND DATEPART(year, TBL_01.time) = (SELECT DATEPART(year, getutcdate()))

AND (Tbl_02.type IS NULL OR Tbl_02.type = 'CC'

OR Tbl_02.type = 'MK'))

ORDER BY Tbl_02.Type, Tbl_03.time DESC

For Q2, you might be able to fix the ordering with a tiny change to the 'ORDER BY ' clause:

ORDER BY TBL_01.time desc, Tbl_02.Type, Tbl_03.time

You don't always have to have the <ORDER BY> field in the select list.

|||

For Q1, I would do something like this:

Code Snippet

declare @.dtStartOfThisMonth datetime, @.dtStartOfTimePeriod datetime

set @.dtStartOfThisMonth = ('01 ' + right(convert(varchar, getdate(),106),9))

set @.dtStartOfTimePeriod = Dateadd(m,-2, @.dtStartOfThisMonth)

THE REST OF THE QUERY HERE

Then, in the WHERE clause put something like:

Code Snippet

AND Tbl_01.Time >= @.dtStartOfTimePeriod and Tbl_01.Time < @.dtStartOfThisMonth

|||

This worked in putting things in the proper order. I just took out the "desc" and everything fell into place. The one field that was in the select list that didn't need to be was merely a leftover from when I was experimenting with an aggregate. I had forgotten to take it out.

Thank you so much.

|||

This worked beautifully! Thank you so much!

Query Timeout Setting

SQL Server 2005 is timing out when running loooong queries.

Is there a global place I can extend the timeout for queries? I do not mean in code but just in general use?

Thanks

Craig

Perhaps its better to fix the long running query?

|||

ndinakar:

Perhaps its better to fix the long running query?

I agree completely!

The number of reusable connections is limited. A few users of a single long-running query can bring an entire system down!

Think about driving on a road with ten lanes going your way. Just two slooooow drivers, each in different lanes, can cause traffic jams really fast in busy traffic.

|||

When you have 80 - 160 million records in 2 or more tables we need all options possible, so any answers to the original question?

|||

80-160 mill rows is nothing for SQL Server, if your query is properly optimized and have the right indexes. I work with tables over 500 mill rows to as high as 1.2 bill rows. So, fixing the query will be a better solution in the longer run. If you can show us the query that's timing out we can try to help with that.

Check this article on various ways to set timeout limits:http://vyaskn.tripod.com/watch_your_timeouts.htm

|||

Both the connection object and the command object have a property that deals with maximum times.

For legitimate, long-running queries that cannot be better tuned or designed, just up the time on those properties.

That way, you force your developers (assuming you are developing with test datasets of the appropriate size) to identify long-running queries before they hit production. It gives you an opportunity to improve the sql before the system goes live.

By searching for those property names, you can identify long-running queries (from the developer's point of view) and route them to your sql specialist for tuning.

Query that automatically deletes records that are older than 365 d

Hi all,
I'm trying to build a self running query that deletes all records that are
older than 365 days. the code i've used is -
DELETE RECadvertId
RECadRecruiterId
RECadreference
RECadjobtype
RECadLocation
RECadJobTitle
RECadSalary
RECadcategory
RECadduration
RECadstartdate
RECadDatePosted
RECadCompanyID
RECadDescription
RECadDescriptionshort
RECaddayspostedfor
RECadBenefits
FROM [dbo].[RECadvert]
WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
It isn't working, any ideas?
thanksLeave out the column names:
DELETE FROM [dbo].[RECadvert]
WHERE RECadDatePosted < DATEADD(dd,-365,getdate())
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:84131850-B81C-4878-84CD-BC8C0116AD56@.microsoft.com...
Hi all,
I'm trying to build a self running query that deletes all records that are
older than 365 days. the code i've used is -
DELETE RECadvertId
RECadRecruiterId
RECadreference
RECadjobtype
RECadLocation
RECadJobTitle
RECadSalary
RECadcategory
RECadduration
RECadstartdate
RECadDatePosted
RECadCompanyID
RECadDescription
RECadDescriptionshort
RECaddayspostedfor
RECadBenefits
FROM [dbo].[RECadvert]
WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
It isn't working, any ideas?
thanks|||On Sun, 29 Jan 2006 12:55:28 -0800, GTN170777 wrote:

>Hi all,
>I'm trying to build a self running query that deletes all records that are
>older than 365 days. the code i've used is -
>DELETE RECadvertId
> RECadRecruiterId
> RECadreference
> RECadjobtype
> RECadLocation
> RECadJobTitle
> RECadSalary
> RECadcategory
> RECadduration
> RECadstartdate
> RECadDatePosted
> RECadCompanyID
> RECadDescription
> RECadDescriptionshort
> RECaddayspostedfor
> RECadBenefits
> FROM [dbo].[RECadvert]
> WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
>It isn't working, any ideas?
>thanks
Hi GTN170777,
You've got the syntax of the DELETE statement wrong (you don't include a
column list - a DELETE will always rempve the entire row). And the WHERE
clause is incorrect too - this checks if RECadDatePosted is 365 days
older than itself (which it of course never is). You should compare
RECadDatePosted with the date that was 365 days before "now".
Something like this:
DELETE FROM dbo.RECadvert
WHERE RECadDatePosted < DATEADD(d, -365, CURRENT_TIMESTAMP)
(Note: this query is untested. Please test it on a test database first.
Also, enclose it in a transaction and check results before issuing a
COMMIT or a ROLLBACK command).
Hugo Kornelis, SQL Server MVP|||The WHERE clause is the problem. You cannot find one row where the value in
a column is not the same
as the value in that same column, i.e. the same value (take a long look at y
our WHERE clause and you
will understand). You probably want something like:
WHERE RECadDatePosted < DATEADD(d,-365,CURRENT_TIMESTAMP)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"GTN170777" <GTN170777@.discussions.microsoft.com> wrote in message
news:84131850-B81C-4878-84CD-BC8C0116AD56@.microsoft.com...
> Hi all,
> I'm trying to build a self running query that deletes all records that are
> older than 365 days. the code i've used is -
> DELETE RECadvertId
> RECadRecruiterId
> RECadreference
> RECadjobtype
> RECadLocation
> RECadJobTitle
> RECadSalary
> RECadcategory
> RECadduration
> RECadstartdate
> RECadDatePosted
> RECadCompanyID
> RECadDescription
> RECadDescriptionshort
> RECaddayspostedfor
> RECadBenefits
> FROM [dbo].[RECadvert]
> WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
> It isn't working, any ideas?
> thanks|||DELETE FROM [dbo].[RECadvert]
WHERE DATEDIFF(dd, RECadDatePosted, GETDATE()) > 365
You need the GETDATE() function to return the current date and time. The
DATEDIFF function, as used here, calculates the number of days that have
passed between a value for RECadDatePosted and today. If that number is more
than 365, that row will be deleted.
You don't need to specify column names when deleting.
"GTN170777" wrote:

> Hi all,
> I'm trying to build a self running query that deletes all records that are
> older than 365 days. the code i've used is -
> DELETE RECadvertId
> RECadRecruiterId
> RECadreference
> RECadjobtype
> RECadLocation
> RECadJobTitle
> RECadSalary
> RECadcategory
> RECadduration
> RECadstartdate
> RECadDatePosted
> RECadCompanyID
> RECadDescription
> RECadDescriptionshort
> RECaddayspostedfor
> RECadBenefits
> FROM [dbo].[RECadvert]
> WHERE RECadDatePosted > DATEADD(d,365,RECadDatePosted)
> It isn't working, any ideas?
> thanks|||>> t isn't working, any ideas? <<
You might try writing SQL instead of whatever this language was. The
unit of work in SQL is a row; but you do not know that row is nothing
whatsoever like a record!! Please read a book or your error messages
before you post again.|||What hospitality. This will learn him to be curious about a subject. I
will bet he ran out and bought an entire catalog of books :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138582546.129921.115400@.g49g2000cwa.googlegroups.com...
> You might try writing SQL instead of whatever this language was. The
> unit of work in SQL is a row; but you do not know that row is nothing
> whatsoever like a record!! Please read a book or your error messages
> before you post again.
>|||Why is it that 5 other people knew what he was talking about and gave the
correct solution and you didn't?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138582546.129921.115400@.g49g2000cwa.googlegroups.com...
> You might try writing SQL instead of whatever this language was. The
> unit of work in SQL is a row; but you do not know that row is nothing
> whatsoever like a record!! Please read a book or your error messages
> before you post again.
>

Query tables in 2 databases using JDBC.

I need to Query tables in 2 databases after getting a connection. I cannot make this piece of code to work. In db2, oracle this type of code works.

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String sys = jdbc:microsoft:sqlserver://192.168.0.152";
conn = DriverManager.getConnection(sys, "name", "pswd");
Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM pubs.jobs");

rs = stmt.executeQuery("SELECT * FROM mydb.myfile");

If I run this I get invalid object pubs.jobs error.

Any ideas?
Thanks.You are missing the dbo (object owner) in the table names. You want to use something more likeClass.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String sys = jdbc:microsoft:sqlserver://192.168.0.152";
conn = DriverManager.getConnection(sys, "name", "pswd");
Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM pubs.dbo.jobs");

rs = stmt.executeQuery("SELECT * FROM mydb.dbo.myfile"); I think that should get you rocking and rolling nicely.

-PatP|||Thanks Patp!
Now it works.
is dbo something that has to be included? I mean is it always the owner of tables?
Sorry I am a sql server newbie and know very little about it.|||dbo is "data base owner", and they normally "own" all of the objects in a production database. You can think of different owners in SQL Server much like different schemas in Oracle.

When using "one part names", such as a table name or a procedure name by itself, you don't need to use dbo. When using anything more than one part names (including the database), you should always use the owner name even though it can be allowed to default so that master.dbo.sysdatabases and master..sysdatabases are usually the same thing. Explicitly providing dbo allows object references to compile without the need for later lookup, so it also helps performance to always explicitly provide the dbo.

-PatP|||Thanks alot Pat!

query string is being truncated

Hi,
I have hit a brick wall with this. My code is as below

public void fillCustomer()
{
 string connectionString = "server='local'; trusted_connection= true; integrated security=sspi; database='Mrbob'";
System.Data.SqlClient.SqlConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
string queryString = "SELECT * FROM [Customer] WHERE ([CustomerID] = @.CustomerID)";
System.Data.SqlClient.SqlCommand dbCommand= new System.Data.SqlClient.SqlCommand();
dbCommand.CommandText = queryString;
dbCommand.Connection = dbConnection;
System.Data.IDataParameter param_CustomerID = new System.Data.SqlClient.SqlParameter();
param_CustomerID.ParameterName ="@.CustomerID";
param_CustomerID.Value = customerID;dbCommand.Parameters.Add("@.CustomerID", SqlDbType.Int);
dbCommand.Connection.Open();
System.Data.IDataReader dataReader = dbCommand.ExecuteReader();
dbCommand.Connection.Close();
while(dataReader.Read())
{
customerID = dataReader.GetInt32(0);
date = dataReader.GetDateTime(1);
eposCode = dataReader.GetInt32(2);
}
dataReader.Close();
}

The error I am getting is

Prepared statement '(@.CustomerID int)SELECT * FROM [Customer] WHERE ([CustomerID] = ' expects parameter @.CustomerID, which was not supplied.

As you can see from my queryString the @.CustomerID parameter is passed in. It seems as if the string is being truncated at 64 characters long. If I remove the paramter to pass the relevant infomration and pass in a customerID I know exists it works.

I am really stumped on this and would really appreciate any pointersfixed it, went away and came back to it a couple of hours later!!!! hope I haven't wasted anyones time

Cheers
Tom