Friday, March 30, 2012
Query Would Run For Ever - No Clue
I have a strange situation here. I am executing a SQL query which runs for ever till it fills up all the available temp space.
The same query runs within 1 minute in another database on another server. That database is a development database but with same records (and data).
I tried the following:
UPDATE STATISTICS
DBREINDEX
FIXED FRAGMENTATION BY RUNNING DBINDEXDEFRAG
Nothing helps... what should I do next?post the ddl + the query so we can see. Its common to have a rogue/running away query that would take the server down to its knee.
e.g.
select *
from master..syscolumns,master..syscolumns,master..sysc olumns,master..syscolumns
Monday, March 26, 2012
query with a table name that has a space...
ex: aim international
I had trouble just in the query analyzer with this..I had to place the
name in brackets [] for it to work. But now I'm in Visual Studio .Net
2003 and it gives me another problem. I get the table name from a drop
down list selection and send it to a query string. But is gives me this
error:
***************
Line 1: Incorrect syntax near 'AIM international'.
Exception Details: System.Data.SqlClient.SqlException: Line 1:
Incorrect syntax near 'AIM international'.
******************
Here is the string:
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &
"' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
**********************
And the variable PubName is the string AIM international .
I tried placing it in brackets like in the query analyzer :
****************
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &
"'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
*******************
and I get this:
*******************
Invalid object name ''AIIM international''.
Exception Details: System.Data.SqlClient.SqlException: Invalid object
name ''AIIM international''.
*******************
Any idea what I have to do for it to work ? Can I use table names
with spaces or it's just not a good idea?
Thanks for the help guys!!
JMTHi,
> I have a table name in SQL Server 2000 that has a space in it
> ex: aim international
> I had trouble just in the query analyzer with this..I had to place the
> name in brackets [] for it to work. But now I'm in Visual Studio .Net
> 2003 and it gives me another problem. I get the table name from a drop
> down list selection and send it to a query string. But is gives me this
> error:
> ***************
> Line 1: Incorrect syntax near 'AIM international'.
> Exception Details: System.Data.SqlClient.SqlException: Line 1:
> Incorrect syntax near 'AIM international'.
> ******************
> Here is the string:
> ****************
> Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM '" & PubName &
> "' WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
> ORDER BY Last_Name "
> **********************
> And the variable PubName is the string AIM international .
> I tried placing it in brackets like in the query analyzer :
> ****************
> Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM ['" & PubName &
There's an extra single quote there: [' ... & pubname ...
> "'] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
> ORDER BY Last_Name "
> *******************
> and I get this:
> *******************
> Invalid object name ''AIIM international''.
> Exception Details: System.Data.SqlClient.SqlException: Invalid object
> name ''AIIM international''.
> *******************
> Any idea what I have to do for it to work ? Can I use table names
> with spaces or it's just not a good idea?
Well, it's not an good idea either :-)
--
With regards,
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com|||Well thanks alot Martijn,
Thoses extra quotes really made a difference. Now it all works!!
here's the new string:
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
"] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
ORDER BY Last_Name "
Beautiful!! I love it when things end up working!!
Thanks again!
JMT|||vbnetrookie (bigjmt@.hotmail.com) writes:
> Well thanks alot Martijn,
> Thoses extra quotes really made a difference. Now it all works!!
> here's the new string:
> Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
> "] WHERE PostalCode ='" & postalcode & "' And Title='" & title & "'
> ORDER BY Last_Name "
And now for title enter the following string:
' DROP TABLE [AIM International] --
As a safety precaution, make sure that you have a backup of your database
available.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I'm not sure what you mean :
enter the following string for title ?
WHat will this do and why add that line if it all works now?
I'm alaways willing to learn new stuff so i'm all ears!!
JMT|||vbnetrookie (bigjmt@.hotmail.com) writes:
> I'm not sure what you mean :
> enter the following string for title ?
> WHat will this do and why add that line if it all works now?
> I'm alaways willing to learn new stuff so i'm all ears!!
Presumably title comes from an input field. And it is into this input
field you should enter this string and learn what happens. You may
think you are searching for some data, but in fact you will blow away
your table.
This is something which is called "SQL injection". By entering SQL commands
into an input field, an intruder might be able to do things your database
that you did not intend. This is particular a danger if that input field
in on a web site. The trick is simple: Use an ' to close the string and
also a -- at the end to kill the syntax that comes after the query.
The remedy for this problem is simple: rather than building the complete
SQL statement, you use a parameterised statement:
Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
"] WHERE PostalCode = @.postalcode And Title= @.title "
You then add the parameters with .AddParameter which I believe is on
the command object. (I'm not a very frequent ADO .Net programmer, so
I don't remember the details.) I encourage you to look it up. And I
cannot stress enough that this is essential stuff.
As you may note, I did not use a parameter for the table name; This is
because table names cannot be parameterized. Usually if you find the need
to determine the table name dynamically, this is a strong indiciation of
a poor database design.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> now for title enter the following string:
>' DROP TABLE [AIM International] --
Well, technically you'd need:
[AIM International]; DROP TABLE [AIM International] --
You have to finish that first SQL statement before you can in inject
new code.
I agree that if the table name is a parameter it indicates that the
database is not normalized, but if you *had* to do it that way then I'd
suggest putting the table names into an array and just passing as input
an index to the array (and of course doing bounds checking on it.)
Wednesday, March 21, 2012
query to see unused space in any data file
as in EM ? How can i do so using TSQL ?1. Use the taskpad view for the database
2. DBCC SHOWFILESTATS
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23j61al7gDHA.3144@.tk2msftngp13.phx.gbl...
> Id like to see how much of unused space is there for any database data
file
> as in EM ? How can i do so using TSQL ?
>|||Create following stored procedure to get the free space
left in the database.
Usage of the foll. procedure would be as
exec proc_free_space 'northwind' --pass db name as
parameter
--script of the procedure.
create proc proc_free_space @.db sysname
as
create table #temp2
(fileid int,filegroup int,totalextents int,usedextents
int,name sysname,filename sysname)
insert into #temp2 exec ('use ' + @.db + ';dbcc
showfilestats')
select 'Data Files :' [SPACE ALLOCATED] , '' [TOTAL
(M)], '' [USED (M)], '' [FREE M)]
union all
select reverse(substring(reverse(filename),1,
charindex('\', reverse(filename)) - 1)) [FILENAME],
str(cast([used (m)]as decimal(10,2)) + cast([free (m)] as
decimal(10,2)),10,2) 'Total (M)', [Used (M)] , [Free (M)]
from (select filename, str(usedextents * cast(64 as decimal
(8,2))/1024, 10,2) 'Used (M)',str((totalextents * cast(64
as decimal(8,2))/1024) - (usedextents * cast(64 as decimal
(8,2))/1024) , 10,2) 'Free (M)'
from
#temp2)a
go
--procedure ends here
To get the free space for each database use system stored
procedure sp_msforeachdb and pass the name of above stored
procedure.
Ex:
EXEC sp_Msforeachdb "proc_free_space '?'"
- Vishal|||Set up a profiler trace to capture what EM is doing and you will see the
TSQL. You might even see some undocumented commands.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#j61al7gDHA.3144@.tk2msftngp13.phx.gbl...
> Id like to see how much of unused space is there for any database data
file
> as in EM ? How can i do so using TSQL ?
>
Tuesday, March 20, 2012
Query to list disk drives and capacity
space.. similar to what i would see in disk management.
I tried xp_fixeddrives and it just gives me the drives and free space, but
not total disk capacity
Thank youDidn't you post this and get an answer already? I know I answered this same
question posted by someone a couple of weeks ago. Anyway, here it goes
again:
If you're using SQL 2000, do one of the following:
#1) Write an XP that retrieves this information for you,
#2) Get a Windows version of the "df.exe" program and use xp_cmdshell to
call it, and parse the results. There are freely available versions on the
Web and I previously posted a link to one the last time I answered this
question.
If you're using SQL 2005 write a SQLCLR table-valued function or SP to
retrieve the info using .NET.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:unYp%23lrOHHA.1252@.TK2MSFTNGP02.phx.gbl...
>I need a query to list all my disk drives, total disk capacity and free
>space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>|||It has always been one of my wishes for xp_fixeddrives to return more
complete disk info. I typically do this outside of SQL Server with tools such
as srvinfo.exe. I know this doesn't help if you want to insert the info into
a table.
Linchi
"Hassan" wrote:
> I need a query to list all my disk drives, total disk capacity and free
> space.. similar to what i would see in disk management.
> I tried xp_fixeddrives and it just gives me the drives and free space, but
> not total disk capacity
> Thank you
>
>
Monday, February 20, 2012
Query string space limitation
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.
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>