Query your Stored Procs

Is there a way you can query stored procedures for a string in all of the
stored procs in a database? For example, if you were looking for "LETTERS"
in all of your stored procedures in your Account DB, how would you look for
the string? Please post example if you know.
-Toco-You can search the syscomments which has the source code for all your stored
procedures. A wrapper script can be found at:
http://vyaskn.tripod.com/sql_server...cedure_code.htm
Anith|||SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM AccountDB.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%LETTERS%'
http://www.aspfaq.com/
(Reverse address to reply.)
"Toco" <Toco@.discussions.microsoft.com> wrote in message
news:5A3A9543-40A4-460D-BFDC-09C021991BDD@.microsoft.com...
> Is there a way you can query stored procedures for a string in all of the
> stored procs in a database? For example, if you were looking for
"LETTERS"
> in all of your stored procedures in your Account DB, how would you look
for
> the string? Please post example if you know.
> -Toco-

Query word starting with....from string caught in web app

Hi all
This should be easy for one of you pros out there...
I take a single string(one letter) from a textbox and want to query my
database for a name that starts with that letter.
I know that the correct synthax when you know the letter is:

WHERE FirstName LIKE 'O%'

but when I have this:
*****************
Dim lastname As String
lastname = txtlastname.Text

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like ' txtlastname % ' "
**********************************************
It doesn't seem to work since my datagrid is empty afterwards. I also
tried:
***************
Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like '" & txtlastname & " % ' "
****************
without succes...
It's probably just a synthax error on my part but I don'T know were..
THanks guys!!
JMTI got it to work with my variable 'lastname'
Got the answer form another forum, here's how:

And Last_Name like '" & lastname & "%'"

If it can help someone!!
JMT|||It may just be a posting error, but it looks as if you have a couple of
spaces in your string construction; the second looks closer to correct:

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE
Last_Name
like '" & txtlastname & "%'

Assuming that PUBNAME is a table named TABLE, and txtLastName = A, then
the final SQL String should look like:

SELECT * FROM [TABLE] WHERE Last_Name LIKE 'A%',

Stu|||I got it to work with my variable 'lastname'
Got the answer form another forum, here's how:

And Last_Name like '" & lastname & "%'"

If it can help someone!!
JMT

Query word starting with....from string caught in web app

Hi all
This should be easy for one of you pros out there...
I take a single string(one letter) from a textbox and want to query my
database for a name that starts with that letter.
I know that the correct synthax when you know the letter is:

WHERE FirstName LIKE 'O%'

but when I have this:
*****************
Dim lastname As String
lastname = txtlastname.Text

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like ' txtlastname % ' "
**********************************************
It doesn't seem to work since my datagrid is empty afterwards. I also
tried:
***************
Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE Last_Name
like '" & txtlastname & " % ' "
****************
without succes...
It's probably just a synthax error on my part but I don'T know were..
THanks guys!!
JMTIt may just be a posting error, but it looks as if you have a couple of
spaces in your string construction; the second looks closer to correct:

Dim strSQL As String = "SELECT * FROM [" & PubName & "] WHERE
Last_Name
like '" & txtlastname & "%'

Assuming that PUBNAME is a table named TABLE, and txtLastName = A, then
the final SQL String should look like:

SELECT * FROM [TABLE] WHERE Last_Name LIKE 'A%',

Stu

Query with SELECT and SUBSTRING

Hello,
I'm a beginner in SQLServer and I'm trying to crite a query with a subst
ring but without success. I've got a field (String) in a table which con
tains a price formatted like that "AUD 2,000.10". I would like with a su
bstring (or something else) obtain something like "2000.10". Can somebod
y help me with that ?
Thanks a lot
Vincent
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Article poste via Voila News - http://www.news.voila.fr
Le : Tue Mar 30 02:55:05 2004 depuis l'IP : mail.ebycms.com.au [VIP 3500
978]Hi Vincent,
Is it always AUD ?
try
select convert(money,right(col1,len(col1)-3))
I hope this helps
--
Greg O
http://www.sql-scripts.com
"MOTTE" <liste@.france-dev.com> wrote in message
news:c4agh9$src$1@.news.x-echo.com...
> Hello,
> I'm a beginner in SQLServer and I'm trying to crite a query with a subst
> ring but without success. I've got a field (String) in a table which con
> tains a price formatted like that "AUD 2,000.10". I would like with a su
> bstring (or something else) obtain something like "2000.10". Can somebod
> y help me with that ?
> Thanks a lot
> Vincent
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Article poste via Voila News - http://www.news.voila.fr
> Le : Tue Mar 30 02:55:05 2004 depuis l'IP : mail.ebycms.com.au [VIP
3500978]

Query with quotation mark.

I have problem with sql query with aspnet.

In my web page, i have a text box for input name or string to search in database. I have problem if the search string consist of quotation mark =>' .

Any suggestions? Thanks.

Use a parameterized query instead of building a SQL String to execute. If you must continue building a SQL string, then you need to replace all the quotes in any string field with two quotes.

Example:

strSQL="SELECT * FROM table WHERE field1='" & textbox1.text & "'"

You can change that to:

strSQL="SELECT * FROM table WHERE field1='" & textbox1.text.replace("'","''") & "'"

or:

dim conn as new sqlconnection(configurationmanager.connectionstrings("{your key here").ConnectionnString)

conn.open

dim cmd as new sqlcommand("SELECT * FROM table WHEREfield1=@.field1",conn)

cmd.parameters.add(new sqlparameter("@.field1",sqldbtype.varchar))

cmd.parameter("@.field1").value=textbox1.text

' Execute the following line if you want a datareader

dim dr as sqldatareader=cmd.executedatareader

' Execute the following 4 lines if you want a dataset

dim ds as new dataset

dim da as sqldataadapter=new sqldataadapter(cmd)

da.fill(ds)

conn.close

|||

a parameterized query is definately the way to go.

If you continue to use concatenated SQL statements, you are opening yourself up to sql injection attacks.

|||Another option is to use a Stored Procedure - this way you don't have to write an SQL statement at all in your web application. You also gain a performance benefit since the Procedure is already compiled in SQL Server.

dim objCmd as sqlcommand
dim objRdr as sqldatareader

objCmd = new sqlcommand(spNameHere, dbConnObjectHere)
objCmd.commandType = commandtype.storedprocedure
objCmd.parameters.Add("@.SearchField", textBox1.text)

objRdr = objCmd.executeReader

When you use the parameters collection in this way there is no need to worry about single quotes, percent signs, or other characters messing with your query. You can add as many parameters as you need to.|||

rich freeman wrote:

You also gain a performance benefit since the Procedure is already compiled in SQL Server.

Beginning with SQL Server 2000, the execution plan of a parameterized sql statement is also cached just like with a stored proc. Therefore performance differences between stored procs and parameterized sql statements should not be used as a significant factor when choosing one technique over the other.

|||

Thanks you everybody for the reply. All the replies are really informative (thumbs up!)! With the suggestions from you all, I have finally solved the problem. Thanks a lot!!!

Query with multiple arguments?

Hi, I'm a bit stumped as to how to do this.
I have a string[] with a list of users, and I want to query my database to select only the users in this array and bind the datasource to a GridView, but I don't know how to write an SQL query to search for multiple results from the same field.

E.g. Say I have two results in my string[], fred and bob.
How can I select data from the database for just those two users - "SELECT * FROM tblUsers WHERE UserName='bob' AND ??";

IF this is possible, I also need to bind it to a gridview. I tried the following, but it didn't work as I needed it to:

for(int a = 0; a < userArray.Length; a++)
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn);
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close()
}

That 'worked', but as I'm sure you can see, the data that was bound to the gridview was only the last result found, not the whole result set.

Any help is greatly appreciated.

schuminator:

for(int a = 0; a < userArray.Length; a++)
{
conn.Open();
SqlCommand command = new SqlCommand("SELECT * FROM tblUsers WHERE UserName='" + userArray[a] + "'", conn);
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close()
}

try out as below

 String strUsers = String.Empty;for (int a = 0; a < userArray.Length; a++) strUsers = strUsers + @."'" + userArray[a] + @."',"; strUsers = strUsers.Substring(0, strUsers.Length - 1); conn.Open(); SqlCommand command =new SqlCommand("SELECT * FROM tblUsers WHERE UserName in (" + strUsers +")", conn); SqlDataReader reader = command.ExecuteReader(); grid.DataSource = reader; grid.DataBind(); conn.Close();

Good Luck./.

|||

Sorry, I tried to delete the thread but it was too late.
I got it sorted...it was so simple, I feel like an idiot!

"SELECT * FROM tblUsers WHERE UserName='fred' OR UserName='bob'" etc

I also wrote a little for loop to add another OR... to the end of the string when necessary.

|||

you can use above method also...

so instead of UserName='fred' OR UserName='bob'"......

if will formulate query using IN keywork as..

UserName in ('fred','bob')

|||

ahh ok awesome, thanks :)

Query to Search all fields in simple table

I am trying to write a simple search page that will searchall the fields in a database to find all records that match a user input string. The string could happen anywhere in any of the fields. I have a dataset and can write a query but am unsure what the format is for this simple task. I figured it would look like this:

SELECT Table.*

FROM Table

WHERE * = @.USERINPUT

But thats not working. Can someone help.? Thanks..

Not a simple task, but this should get you started.

SELECT *

FROM Table

WHERE field1 LIKE '%' + @.UserInput + '%' OR field2 LIKE'%'+@.UserInput+'%' OR...

sql

Query to find record selection on between dates

Table has five fields fields
PrimaryKey - Integer type
InfoField1 - String Type
InfoField2 - String type
StartDate - DateTimeType
EndDate - DateTimeType
My where clause should be something like
Where InfoField2 = 'something' And Today's date between StartDate and
EndDate.
How do I express Today's date in that query? The data for the startdate and
end date is stored in the database in shortdate format, so in some cases it
can be mm/dd/yyyy and in others it could be dd/mm/yyyy or any other
shortdate format possible.
Thanks for any help
RDWhere InfoField2 = 'something' And getdate()between StartDate and
EndDate.
"RD" <nospam@.nospam.net> wrote in message
news:OvI$1hQRFHA.504@.TK2MSFTNGP12.phx.gbl...
> Table has five fields fields
> PrimaryKey - Integer type
> InfoField1 - String Type
> InfoField2 - String type
> StartDate - DateTimeType
> EndDate - DateTimeType
> My where clause should be something like
> Where InfoField2 = 'something' And Today's date between StartDate and
> EndDate.
> How do I express Today's date in that query? The data for the startdate
> and
> end date is stored in the database in shortdate format, so in some cases
> it
> can be mm/dd/yyyy and in others it could be dd/mm/yyyy or any other
> shortdate format possible.
> Thanks for any help
> RD
>
>

query text file as linked server in v. 7.0?

Hi,
Can one query a text file as a linked server in sql 7.0? If so, anyone know which driver/provider string I would use?
thxOriginally posted by manster
Hi,

Can one query a text file as a linked server in sql 7.0? If so, anyone know which driver/provider string I would use?

thx

Did you check out sp_addlimked server? There's a section on text files...

BOL

H. Use the Microsoft OLE DB Provider for Jet to access a text file
This example creates a linked server for directly accessing text files, without linking the files as tables in an Access .mdb file. The provider is Microsoft.Jet.OLEDB.4.0 and the provider string is 'Text'.

The data source is the full pathname of the directory that contains the text files. A schema.ini file, which describes the structure of the text files, must exist in the same directory as the text files. For more information about creating a schema.ini file, refer to Jet Database Engine documentation.

--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\data\distqry',
NULL,
'Text'
GO

Query string with quote

I want to connect to table of AS400 with a parameter(THE NAME OF TABLE IS
AVP.M002)
The following sentence execute correctly.
SELECT * FROM LIB01."AVP.M002" WHERE CDTAB= '007'
How will be the string with the parameter: Parameters!ParmCDTAB.Value
I was trying with the string , but it doesn't run
="SELECT * FROM LIB01."AVP.M002" WHERE CDTAB= '" &
Parameters!ParmCDTAB.Value &"'"
The problem is ( "AVP.M002") . How I build the string to execute correctly?Just try this (duplicate the double quotes):
="SELECT * FROM LIB01.""AVP.M002"" WHERE CDTAB= '" &
Parameters!ParmCDTAB.Value &"'"
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Walter" <Walter@.discussions.microsoft.com> wrote in message
news:2E79D5D2-0169-4F16-9308-0F8C124203F8@.microsoft.com...
>I want to connect to table of AS400 with a parameter(THE NAME OF TABLE IS
> AVP.M002)
> The following sentence execute correctly.
> SELECT * FROM LIB01."AVP.M002" WHERE CDTAB= '007'
> How will be the string with the parameter: Parameters!ParmCDTAB.Value
> I was trying with the string , but it doesn't run
> ="SELECT * FROM LIB01."AVP.M002" WHERE CDTAB= '" &
> Parameters!ParmCDTAB.Value &"'"
>
> The problem is ( "AVP.M002") . How I build the string to execute
> correctly?
>

Query String using Web Matrix

Hi folks,

I have two tables in one database.

One table has an automatic numbering primary key named ID and is named rfi.

The other table has a field named rfinumber and is named discussion.

Both ID and rfinumber have a datatype of number.

Upon using the querybuilder with Web Matrix, I issue a select command to get all records from the discussion table that have a rfinumber field equal to the ID field in the rfi table.

Problem is that I am getting the entire discussion table when I use the following query:

"SELECT [discussion].* FROM [discussion], [rfi] WHERE ([discussion].[rfinumber] = [rfi].[ID])"

For example

DISCUSSION TABLE
rfinumber
1
1
1
2

RFI TABLE
rfi
1
2

Given the query, I should get a discussion table only listing the rfinumber = 1.

When I run the query from my database package it runs fine??

Any clues?

thanks,
glenn

gmeadows:

Problem is that I am getting the entire discussion table when I use the following query:

That's exactly what I would expect. You are returning a row from discussion matched with each row from rfi where the rfinumber from discussion is the same as the ID from rfi. Why would you expect it only to return those rows where rfinumber = 1? You need to use a parameter of some sort to make that happen.

Query string space limitation

Other posts indicate that Reporting Services has no limit to the size of the
query string defining the Data Set in Visual Studio. I am querying MySQL
(lots of calculation and conversion statements) and after building and
testing the query with another tool, I paste it into the Report Designer data
set query string box. I have successfully pasted large blocks of sql text
into the box (20,000+ characters/35,000+ with white spaces), but the latest
query got cut off during the copy/paste routine (23,405 characters/35,936
with white spaces). Not until I remove text in the MySQL statement does it
paste entirely into the query string box.
Since the larger query works fine against the database in my build tool that
rules out possible limitations with MySQL. So, is there a limitation in how
much text can be entered in the query string box -- character limit? line
limit?
Thanks.I could be having a memory problem, but I thought that the entire RDL must
be < 4000 characters or it can not be deployed.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
> Other posts indicate that Reporting Services has no limit to the size of
> the
> query string defining the Data Set in Visual Studio. I am querying MySQL
> (lots of calculation and conversion statements) and after building and
> testing the query with another tool, I paste it into the Report Designer
> data
> set query string box. I have successfully pasted large blocks of sql text
> into the box (20,000+ characters/35,000+ with white spaces), but the
> latest
> query got cut off during the copy/paste routine (23,405 characters/35,936
> with white spaces). Not until I remove text in the MySQL statement does
> it
> paste entirely into the query string box.
> Since the larger query works fine against the database in my build tool
> that
> rules out possible limitations with MySQL. So, is there a limitation in
> how
> much text can be entered in the query string box -- character limit? line
> limit?
> Thanks.|||There is no size limit on RDL files.
Note: if RS is installed on Windows 2003 with IIS 6 you may run into the
default security restriction of a 4 MB file upload/download limit (which can
be changed).
Regarding the large query command text in report designer - are you using
the text-based generic query designer (with 2 panes)?
BTW: you could start with a smaller query that returns all fields, design
the report and as last step replace the smaller query with the huge
commandtext directly in the RDL file.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
>I could be having a memory problem, but I thought that the entire RDL must
>be < 4000 characters or it can not be deployed.
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
>> Other posts indicate that Reporting Services has no limit to the size of
>> the
>> query string defining the Data Set in Visual Studio. I am querying MySQL
>> (lots of calculation and conversion statements) and after building and
>> testing the query with another tool, I paste it into the Report Designer
>> data
>> set query string box. I have successfully pasted large blocks of sql
>> text
>> into the box (20,000+ characters/35,000+ with white spaces), but the
>> latest
>> query got cut off during the copy/paste routine (23,405 characters/35,936
>> with white spaces). Not until I remove text in the MySQL statement does
>> it
>> paste entirely into the query string box.
>> Since the larger query works fine against the database in my build tool
>> that
>> rules out possible limitations with MySQL. So, is there a limitation in
>> how
>> much text can be entered in the query string box -- character limit? line
>> limit?
>> Thanks.
>|||My problem seems to be limited to the query string on the dataset. I have
already built the report and was modifying (i.e., adding limiters) to my
existing query when it wouldn't paste the query contents in the Query string
field. I used the ellipses next to my existing Dataset name to pull up the
Dataset properties. Then, I cut out the existing Query string, copied and
pasted the new query string (using Command Type: Text) and that's where I
found the problem. The only way to then get a valid dataset that I could use
in my report was to pare down the query length until I could get everything
in the Query String box.
I do not add query strings to Report Designer any other way. I've had
Visual Studio crash on my frequently enough when accessing more than two
tables in a query that I long since have been building queries outside Report
Designer and only then pasting in the final query when I'm ready to build the
reports. This is the first instance where the query did not paste completely
into the box.
I run two different reports off this query. One report is a few hundred K;
the other is about 1MB when exported to Excel. Once I paste in a valid query
into the Dataset properties, all the reports run fine.
Anything else that might illuminate a remedy?
Thanks.
"Robert Bruckner [MSFT]" wrote:
> There is no size limit on RDL files.
> Note: if RS is installed on Windows 2003 with IIS 6 you may run into the
> default security restriction of a 4 MB file upload/download limit (which can
> be changed).
> Regarding the large query command text in report designer - are you using
> the text-based generic query designer (with 2 panes)?
> BTW: you could start with a smaller query that returns all fields, design
> the report and as last step replace the smaller query with the huge
> commandtext directly in the RDL file.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
> >I could be having a memory problem, but I thought that the entire RDL must
> >be < 4000 characters or it can not be deployed.
> >
> >
> >
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> > (Please respond only to the newsgroup.)
> >
> > I support the Professional Association for SQL Server ( PASS) and it's
> > community of SQL Professionals.
> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
> > news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
> >> Other posts indicate that Reporting Services has no limit to the size of
> >> the
> >> query string defining the Data Set in Visual Studio. I am querying MySQL
> >> (lots of calculation and conversion statements) and after building and
> >> testing the query with another tool, I paste it into the Report Designer
> >> data
> >> set query string box. I have successfully pasted large blocks of sql
> >> text
> >> into the box (20,000+ characters/35,000+ with white spaces), but the
> >> latest
> >> query got cut off during the copy/paste routine (23,405 characters/35,936
> >> with white spaces). Not until I remove text in the MySQL statement does
> >> it
> >> paste entirely into the query string box.
> >>
> >> Since the larger query works fine against the database in my build tool
> >> that
> >> rules out possible limitations with MySQL. So, is there a limitation in
> >> how
> >> much text can be entered in the query string box -- character limit? line
> >> limit?
> >>
> >> Thanks.
> >
> >
>
>|||You can still do this cut and paste but don't do it the way you are doing
it. Use the generic query designer (2 pane) rather than the graphical (4
pane) designer. The button to switch to the generic query designer is to the
right of the ...
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:C6481152-C839-4A9B-BA6F-79A5D6626883@.microsoft.com...
> My problem seems to be limited to the query string on the dataset. I have
> already built the report and was modifying (i.e., adding limiters) to my
> existing query when it wouldn't paste the query contents in the Query
> string
> field. I used the ellipses next to my existing Dataset name to pull up
> the
> Dataset properties. Then, I cut out the existing Query string, copied and
> pasted the new query string (using Command Type: Text) and that's where I
> found the problem. The only way to then get a valid dataset that I could
> use
> in my report was to pare down the query length until I could get
> everything
> in the Query String box.
> I do not add query strings to Report Designer any other way. I've had
> Visual Studio crash on my frequently enough when accessing more than two
> tables in a query that I long since have been building queries outside
> Report
> Designer and only then pasting in the final query when I'm ready to build
> the
> reports. This is the first instance where the query did not paste
> completely
> into the box.
> I run two different reports off this query. One report is a few hundred
> K;
> the other is about 1MB when exported to Excel. Once I paste in a valid
> query
> into the Dataset properties, all the reports run fine.
> Anything else that might illuminate a remedy?
> Thanks.
> "Robert Bruckner [MSFT]" wrote:
>> There is no size limit on RDL files.
>> Note: if RS is installed on Windows 2003 with IIS 6 you may run into the
>> default security restriction of a 4 MB file upload/download limit (which
>> can
>> be changed).
>> Regarding the large query command text in report designer - are you using
>> the text-based generic query designer (with 2 panes)?
>> BTW: you could start with a smaller query that returns all fields, design
>> the report and as last step replace the smaller query with the huge
>> commandtext directly in the RDL file.
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
>> >I could be having a memory problem, but I thought that the entire RDL
>> >must
>> >be < 4000 characters or it can not be deployed.
>> >
>> >
>> >
>> > --
>> > Wayne Snyder MCDBA, SQL Server MVP
>> > Mariner, Charlotte, NC
>> > (Please respond only to the newsgroup.)
>> >
>> > I support the Professional Association for SQL Server ( PASS) and it's
>> > community of SQL Professionals.
>> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> > news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
>> >> Other posts indicate that Reporting Services has no limit to the size
>> >> of
>> >> the
>> >> query string defining the Data Set in Visual Studio. I am querying
>> >> MySQL
>> >> (lots of calculation and conversion statements) and after building and
>> >> testing the query with another tool, I paste it into the Report
>> >> Designer
>> >> data
>> >> set query string box. I have successfully pasted large blocks of sql
>> >> text
>> >> into the box (20,000+ characters/35,000+ with white spaces), but the
>> >> latest
>> >> query got cut off during the copy/paste routine (23,405
>> >> characters/35,936
>> >> with white spaces). Not until I remove text in the MySQL statement
>> >> does
>> >> it
>> >> paste entirely into the query string box.
>> >>
>> >> Since the larger query works fine against the database in my build
>> >> tool
>> >> that
>> >> rules out possible limitations with MySQL. So, is there a limitation
>> >> in
>> >> how
>> >> much text can be entered in the query string box -- character limit?
>> >> line
>> >> limit?
>> >>
>> >> Thanks.
>> >
>> >
>>|||I typically don't even use the graphical designer in Reporting Services; I
have them all toggled off and paste the query in the pop-up window. However,
I did try the 2-pane window as you suggest and the query still cuts off.
Pasting the cut-off query into Word gives me a count of 20,812 characters;
31,955 with white spaces. The only way I can get this to paste in its
entirety is to remove one of my fields (luckily, I don't need that).
However, I foresee adding more limiters in the WHERE clause which might bulk
up the query again.
Any other ideas on what is preventing my query from pasting into the designer?
Thanks.
"Bruce L-C [MVP]" wrote:
> You can still do this cut and paste but don't do it the way you are doing
> it. Use the generic query designer (2 pane) rather than the graphical (4
> pane) designer. The button to switch to the generic query designer is to the
> right of the ...
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:C6481152-C839-4A9B-BA6F-79A5D6626883@.microsoft.com...
> > My problem seems to be limited to the query string on the dataset. I have
> > already built the report and was modifying (i.e., adding limiters) to my
> > existing query when it wouldn't paste the query contents in the Query
> > string
> > field. I used the ellipses next to my existing Dataset name to pull up
> > the
> > Dataset properties. Then, I cut out the existing Query string, copied and
> > pasted the new query string (using Command Type: Text) and that's where I
> > found the problem. The only way to then get a valid dataset that I could
> > use
> > in my report was to pare down the query length until I could get
> > everything
> > in the Query String box.
> >
> > I do not add query strings to Report Designer any other way. I've had
> > Visual Studio crash on my frequently enough when accessing more than two
> > tables in a query that I long since have been building queries outside
> > Report
> > Designer and only then pasting in the final query when I'm ready to build
> > the
> > reports. This is the first instance where the query did not paste
> > completely
> > into the box.
> >
> > I run two different reports off this query. One report is a few hundred
> > K;
> > the other is about 1MB when exported to Excel. Once I paste in a valid
> > query
> > into the Dataset properties, all the reports run fine.
> >
> > Anything else that might illuminate a remedy?
> > Thanks.
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> >> There is no size limit on RDL files.
> >> Note: if RS is installed on Windows 2003 with IIS 6 you may run into the
> >> default security restriction of a 4 MB file upload/download limit (which
> >> can
> >> be changed).
> >>
> >> Regarding the large query command text in report designer - are you using
> >> the text-based generic query designer (with 2 panes)?
> >> BTW: you could start with a smaller query that returns all fields, design
> >> the report and as last step replace the smaller query with the huge
> >> commandtext directly in the RDL file.
> >>
> >> -- Robert
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >>
> >> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> >> news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
> >> >I could be having a memory problem, but I thought that the entire RDL
> >> >must
> >> >be < 4000 characters or it can not be deployed.
> >> >
> >> >
> >> >
> >> > --
> >> > Wayne Snyder MCDBA, SQL Server MVP
> >> > Mariner, Charlotte, NC
> >> > (Please respond only to the newsgroup.)
> >> >
> >> > I support the Professional Association for SQL Server ( PASS) and it's
> >> > community of SQL Professionals.
> >> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> > news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
> >> >> Other posts indicate that Reporting Services has no limit to the size
> >> >> of
> >> >> the
> >> >> query string defining the Data Set in Visual Studio. I am querying
> >> >> MySQL
> >> >> (lots of calculation and conversion statements) and after building and
> >> >> testing the query with another tool, I paste it into the Report
> >> >> Designer
> >> >> data
> >> >> set query string box. I have successfully pasted large blocks of sql
> >> >> text
> >> >> into the box (20,000+ characters/35,000+ with white spaces), but the
> >> >> latest
> >> >> query got cut off during the copy/paste routine (23,405
> >> >> characters/35,936
> >> >> with white spaces). Not until I remove text in the MySQL statement
> >> >> does
> >> >> it
> >> >> paste entirely into the query string box.
> >> >>
> >> >> Since the larger query works fine against the database in my build
> >> >> tool
> >> >> that
> >> >> rules out possible limitations with MySQL. So, is there a limitation
> >> >> in
> >> >> how
> >> >> much text can be entered in the query string box -- character limit?
> >> >> line
> >> >> limit?
> >> >>
> >> >> Thanks.
> >> >
> >> >
> >>
> >>
> >>
>
>|||This must just be an internal tool issue. This designer came from VS so it
is the same as VS (i.e. it is not specific to RS). My suggestion is to
create this as a stored procedure instead. Better for performance with
something this complicated so SQL Server already has created the query plan.
By the way are you aliasing your table names?
select a.somefield, b.someotherfield from table1 a inner join table2 b on
a.joinfield = b.joinfield
If not that should get your number of characters to be a lot less.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bhc" <bhc@.discussions.microsoft.com> wrote in message
news:EB7775A7-EDAB-44DD-9C4F-D693699A8268@.microsoft.com...
>I typically don't even use the graphical designer in Reporting Services; I
> have them all toggled off and paste the query in the pop-up window.
> However,
> I did try the 2-pane window as you suggest and the query still cuts off.
> Pasting the cut-off query into Word gives me a count of 20,812 characters;
> 31,955 with white spaces. The only way I can get this to paste in its
> entirety is to remove one of my fields (luckily, I don't need that).
> However, I foresee adding more limiters in the WHERE clause which might
> bulk
> up the query again.
> Any other ideas on what is preventing my query from pasting into the
> designer?
> Thanks.
> "Bruce L-C [MVP]" wrote:
>> You can still do this cut and paste but don't do it the way you are doing
>> it. Use the generic query designer (2 pane) rather than the graphical (4
>> pane) designer. The button to switch to the generic query designer is to
>> the
>> right of the ...
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> news:C6481152-C839-4A9B-BA6F-79A5D6626883@.microsoft.com...
>> > My problem seems to be limited to the query string on the dataset. I
>> > have
>> > already built the report and was modifying (i.e., adding limiters) to
>> > my
>> > existing query when it wouldn't paste the query contents in the Query
>> > string
>> > field. I used the ellipses next to my existing Dataset name to pull up
>> > the
>> > Dataset properties. Then, I cut out the existing Query string, copied
>> > and
>> > pasted the new query string (using Command Type: Text) and that's where
>> > I
>> > found the problem. The only way to then get a valid dataset that I
>> > could
>> > use
>> > in my report was to pare down the query length until I could get
>> > everything
>> > in the Query String box.
>> >
>> > I do not add query strings to Report Designer any other way. I've had
>> > Visual Studio crash on my frequently enough when accessing more than
>> > two
>> > tables in a query that I long since have been building queries outside
>> > Report
>> > Designer and only then pasting in the final query when I'm ready to
>> > build
>> > the
>> > reports. This is the first instance where the query did not paste
>> > completely
>> > into the box.
>> >
>> > I run two different reports off this query. One report is a few
>> > hundred
>> > K;
>> > the other is about 1MB when exported to Excel. Once I paste in a valid
>> > query
>> > into the Dataset properties, all the reports run fine.
>> >
>> > Anything else that might illuminate a remedy?
>> > Thanks.
>> >
>> > "Robert Bruckner [MSFT]" wrote:
>> >
>> >> There is no size limit on RDL files.
>> >> Note: if RS is installed on Windows 2003 with IIS 6 you may run into
>> >> the
>> >> default security restriction of a 4 MB file upload/download limit
>> >> (which
>> >> can
>> >> be changed).
>> >>
>> >> Regarding the large query command text in report designer - are you
>> >> using
>> >> the text-based generic query designer (with 2 panes)?
>> >> BTW: you could start with a smaller query that returns all fields,
>> >> design
>> >> the report and as last step replace the smaller query with the huge
>> >> commandtext directly in the RDL file.
>> >>
>> >> -- Robert
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >>
>> >> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
>> >> news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
>> >> >I could be having a memory problem, but I thought that the entire RDL
>> >> >must
>> >> >be < 4000 characters or it can not be deployed.
>> >> >
>> >> >
>> >> >
>> >> > --
>> >> > Wayne Snyder MCDBA, SQL Server MVP
>> >> > Mariner, Charlotte, NC
>> >> > (Please respond only to the newsgroup.)
>> >> >
>> >> > I support the Professional Association for SQL Server ( PASS) and
>> >> > it's
>> >> > community of SQL Professionals.
>> >> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
>> >> > news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
>> >> >> Other posts indicate that Reporting Services has no limit to the
>> >> >> size
>> >> >> of
>> >> >> the
>> >> >> query string defining the Data Set in Visual Studio. I am querying
>> >> >> MySQL
>> >> >> (lots of calculation and conversion statements) and after building
>> >> >> and
>> >> >> testing the query with another tool, I paste it into the Report
>> >> >> Designer
>> >> >> data
>> >> >> set query string box. I have successfully pasted large blocks of
>> >> >> sql
>> >> >> text
>> >> >> into the box (20,000+ characters/35,000+ with white spaces), but
>> >> >> the
>> >> >> latest
>> >> >> query got cut off during the copy/paste routine (23,405
>> >> >> characters/35,936
>> >> >> with white spaces). Not until I remove text in the MySQL statement
>> >> >> does
>> >> >> it
>> >> >> paste entirely into the query string box.
>> >> >>
>> >> >> Since the larger query works fine against the database in my build
>> >> >> tool
>> >> >> that
>> >> >> rules out possible limitations with MySQL. So, is there a
>> >> >> limitation
>> >> >> in
>> >> >> how
>> >> >> much text can be entered in the query string box -- character
>> >> >> limit?
>> >> >> line
>> >> >> limit?
>> >> >>
>> >> >> Thanks.
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||The database platform I'm querying is MySQL, not a native SQL Server
database. The database platform itself is version 4.0 and the most recent
MyODBC driver freely available is 3.51. I have not tried so I can't really
speak of the ability to create the query in 4.0 on the native platform and
successfully call it using a 3.51 driver.
And, yes (for sanity, not space reasons) I'm aliasing my tables with single
character references. The white space is predominantly indents (again for
sanity in reviewing the queries).
Since the query tool I'm using processes the large query string just fine I
know it's valid SQL and that it does pull the desired data from the 4.0 MySQL
database. I am puzzeld by what is causing the cut-offs in VS/RD. I'd like
to know what it is so I can find a better workaround.
Thanks.
"Bruce L-C [MVP]" wrote:
> This must just be an internal tool issue. This designer came from VS so it
> is the same as VS (i.e. it is not specific to RS). My suggestion is to
> create this as a stored procedure instead. Better for performance with
> something this complicated so SQL Server already has created the query plan.
> By the way are you aliasing your table names?
> select a.somefield, b.someotherfield from table1 a inner join table2 b on
> a.joinfield = b.joinfield
> If not that should get your number of characters to be a lot less.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> news:EB7775A7-EDAB-44DD-9C4F-D693699A8268@.microsoft.com...
> >I typically don't even use the graphical designer in Reporting Services; I
> > have them all toggled off and paste the query in the pop-up window.
> > However,
> > I did try the 2-pane window as you suggest and the query still cuts off.
> > Pasting the cut-off query into Word gives me a count of 20,812 characters;
> > 31,955 with white spaces. The only way I can get this to paste in its
> > entirety is to remove one of my fields (luckily, I don't need that).
> > However, I foresee adding more limiters in the WHERE clause which might
> > bulk
> > up the query again.
> >
> > Any other ideas on what is preventing my query from pasting into the
> > designer?
> >
> > Thanks.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You can still do this cut and paste but don't do it the way you are doing
> >> it. Use the generic query designer (2 pane) rather than the graphical (4
> >> pane) designer. The button to switch to the generic query designer is to
> >> the
> >> right of the ...
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> news:C6481152-C839-4A9B-BA6F-79A5D6626883@.microsoft.com...
> >> > My problem seems to be limited to the query string on the dataset. I
> >> > have
> >> > already built the report and was modifying (i.e., adding limiters) to
> >> > my
> >> > existing query when it wouldn't paste the query contents in the Query
> >> > string
> >> > field. I used the ellipses next to my existing Dataset name to pull up
> >> > the
> >> > Dataset properties. Then, I cut out the existing Query string, copied
> >> > and
> >> > pasted the new query string (using Command Type: Text) and that's where
> >> > I
> >> > found the problem. The only way to then get a valid dataset that I
> >> > could
> >> > use
> >> > in my report was to pare down the query length until I could get
> >> > everything
> >> > in the Query String box.
> >> >
> >> > I do not add query strings to Report Designer any other way. I've had
> >> > Visual Studio crash on my frequently enough when accessing more than
> >> > two
> >> > tables in a query that I long since have been building queries outside
> >> > Report
> >> > Designer and only then pasting in the final query when I'm ready to
> >> > build
> >> > the
> >> > reports. This is the first instance where the query did not paste
> >> > completely
> >> > into the box.
> >> >
> >> > I run two different reports off this query. One report is a few
> >> > hundred
> >> > K;
> >> > the other is about 1MB when exported to Excel. Once I paste in a valid
> >> > query
> >> > into the Dataset properties, all the reports run fine.
> >> >
> >> > Anything else that might illuminate a remedy?
> >> > Thanks.
> >> >
> >> > "Robert Bruckner [MSFT]" wrote:
> >> >
> >> >> There is no size limit on RDL files.
> >> >> Note: if RS is installed on Windows 2003 with IIS 6 you may run into
> >> >> the
> >> >> default security restriction of a 4 MB file upload/download limit
> >> >> (which
> >> >> can
> >> >> be changed).
> >> >>
> >> >> Regarding the large query command text in report designer - are you
> >> >> using
> >> >> the text-based generic query designer (with 2 panes)?
> >> >> BTW: you could start with a smaller query that returns all fields,
> >> >> design
> >> >> the report and as last step replace the smaller query with the huge
> >> >> commandtext directly in the RDL file.
> >> >>
> >> >> -- Robert
> >> >> This posting is provided "AS IS" with no warranties, and confers no
> >> >> rights.
> >> >>
> >> >>
> >> >>
> >> >> "Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
> >> >> news:%233AlNICpFHA.764@.TK2MSFTNGP14.phx.gbl...
> >> >> >I could be having a memory problem, but I thought that the entire RDL
> >> >> >must
> >> >> >be < 4000 characters or it can not be deployed.
> >> >> >
> >> >> >
> >> >> >
> >> >> > --
> >> >> > Wayne Snyder MCDBA, SQL Server MVP
> >> >> > Mariner, Charlotte, NC
> >> >> > (Please respond only to the newsgroup.)
> >> >> >
> >> >> > I support the Professional Association for SQL Server ( PASS) and
> >> >> > it's
> >> >> > community of SQL Professionals.
> >> >> > "bhc" <bhc@.discussions.microsoft.com> wrote in message
> >> >> > news:1BB42C13-89EB-42C1-8E81-478EC783429F@.microsoft.com...
> >> >> >> Other posts indicate that Reporting Services has no limit to the
> >> >> >> size
> >> >> >> of
> >> >> >> the
> >> >> >> query string defining the Data Set in Visual Studio. I am querying
> >> >> >> MySQL
> >> >> >> (lots of calculation and conversion statements) and after building
> >> >> >> and
> >> >> >> testing the query with another tool, I paste it into the Report
> >> >> >> Designer
> >> >> >> data
> >> >> >> set query string box. I have successfully pasted large blocks of
> >> >> >> sql
> >> >> >> text
> >> >> >> into the box (20,000+ characters/35,000+ with white spaces), but
> >> >> >> the
> >> >> >> latest
> >> >> >> query got cut off during the copy/paste routine (23,405
> >> >> >> characters/35,936
> >> >> >> with white spaces). Not until I remove text in the MySQL statement
> >> >> >> does
> >> >> >> it
> >> >> >> paste entirely into the query string box.
> >> >> >>
> >> >> >> Since the larger query works fine against the database in my build
> >> >> >> tool
> >> >> >> that
> >> >> >> rules out possible limitations with MySQL. So, is there a
> >> >> >> limitation
> >> >> >> in
> >> >> >> how
> >> >> >> much text can be entered in the query string box -- character
> >> >> >> limit?
> >> >> >> line
> >> >> >> limit?
> >> >> >>
> >> >> >> Thanks.
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Query String Programmatically

Is any way to Modified The Query String Programmatically for a Report ?
I want to be able to Change the WHERE Clause without using Parameter..
e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report I
want something like WHERE DeliveryDate < '2005-04-04'.
Thank. Any Idea I would appreciated.
--
Message posted via http://www.sqlmonster.comGot from BOL.
Passing a Report Parameter on a URL
You can pass report parameters to a report by including them in a URL. These
URL parameters are not prefixed, because they are passed directly to the
report processing engine. For more about report parameters, see Running a
Parameterized Report.
Example
The following example uses the report parameter EmployeeID to render the
specified report:
http://server/reportserver?/Sales/Northwest/Employee Sales
Report&rs:Command=Render&EmployeeID=1234See Also
The operator cant be changed unless you use dynamic SQL.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Edgar Mantilla via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:000edb152d5c494b8854daa6aaf009ff@.SQLMonster.com...
> Is any way to Modified The Query String Programmatically for a Report ?
> I want to be able to Change the WHERE Clause without using Parameter..
> e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report
> I
> want something like WHERE DeliveryDate < '2005-04-04'.
> Thank. Any Idea I would appreciated.
> --
> Message posted via http://www.sqlmonster.com|||Thank you,
I am working in a windows form that selects the report from the Report
Server and send it as .PDF to a specific location, Passing a Report
Parameter on a URL, it works well, but I want to have more flexibility in
building my query string, so I worked with a RDL Generator (Form BOL
Walkthrough ? Generating RDL Using the .NET Framework), which work fine
when you are creating a report from scratch.
Is any way to change the CommandTex or Query string of an existing report
programmatically?
--
Message posted via http://www.sqlmonster.com|||Edgar,
AFAIK you can't change the query text at report run time. There are
solutions to this type of complex querying, and let me say up front that I
recommend you put your query in a stored procedure instead of trying to put
this all into the report itself.
As Jens said, you can use dynamic SQL. This involves building your query as
a string and then calling EXEC (string). If you only have a limited number
of choices (e.g. only <, =, or >) then you can simply use IF statements and
hard code the queries for each branch.
Note: if you go the dynamic SQL route, beware of SQL injection attacks.
That's a whole subject, and too much to discuss here in one post.
Ted
"Edgar Mantilla via SQLMonster.com" wrote:
> Thank you,
> I am working in a windows form that selects the report from the Report
> Server and send it as .PDF to a specific location, Passing a Report
> Parameter on a URL, it works well, but I want to have more flexibility in
> building my query string, so I worked with a RDL Generator (Form BOL
> Walkthrough â' Generating RDL Using the .NET Framework), which work fine
> when you are creating a report from scratch.
> Is any way to change the CommandTex or Query string of an existing report
> programmatically?
> --
> Message posted via http://www.sqlmonster.com
>|||Hi, in the Generic Query Designer you can use some like this:
="SELECT " & Parameters!Field1.Value
" FROM " &
Parameters!Table.Value
"Edgar Mantilla via SQLMonster.com" wrote:
> Is any way to Modified The Query String Programmatically for a Report ?
> I want to be able to Change the WHERE Clause without using Parameter..
> e.i WHERE DeliveryDate = '2005-04-04' but the next time I run the report I
> want something like WHERE DeliveryDate < '2005-04-04'.
> Thank. Any Idea I would appreciated.
> --
> Message posted via http://www.sqlmonster.com
>

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