i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...
One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.
But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.
I sent a long string of ID from front end to my stored procedure...till now I was using varchar(8000)...but if the string crossess that limit it is breaking.
If I try to use text datatype..It doesn't support rtrim, stuff functions etc...
So could any one suggest me a best way to save a long string without any restriction of size...
My front end is C#.Net and Back End is SQL SERVER 2000
I've got a stored proc used for order generation which runs long sometimes when called from within our app. A normal run will complete within 20s, a long run will get terminated by the app at the 6 minute mark.
When it runs long once, repeated attempts will also do so until I execute the same query the app did, but from within Query Analyzer. At which time the problem will disappear for a day or two. The app connects to the SQL Server 200 SP4 database using ADO.
I suspected statistics might be at fault here but have tried both "UPDATE STATISTICS table WITH FULLSCAN" and "DBCC DBREINDEX('table') to no avail. This issue has occurred and been worked around in this manner a few dozen times.
I'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though. I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this: set @FullQuery = 'Select ' + @FieldsinOrder + ' from tblExample'exec (@FullQuery) This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application. If I change the query to be a normal select it works. For example: select * from tblEmample That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?
I have a question that has a co-worker and myself confused. We are using the SQL Server Enterprise Manager. When I select return all rows in my tables, any content that is too long will appear as <Long Text> on my co-workers machine, but the column is blank on my machine. Is there a property or configuration that enables/disables <Long Text>? I am confused as to why it appears on other machines, but not mine.
String or binary data would be truncated.The statement has been terminated. (.Net SqlClient Data Provider) Hi i am getting the above error when i add a table to my Diagram, it happens inside VS05 (server explorer) as well as in SQL Express Server management studio CTP the thing that is weird is that both tables are fine before i add the realtionship, both the Primary Key and the Foreign Key has the same data type which is Int. i am able to add the relationships but when i go to save the relationship diagram i get that above error i believe that this error is caused when u try and insert data into a column that is larger that the column size, but in this case all am doing is creating a relation ship between the primary key in one table and a foreign key in the second table. ie i am not inserting data into either table I looked at the size property of both columns under questions and both reflect a size of 4, I think that is 4 bytes of size, so my question becomes is there any other place where the size of a column field is specified, stumped, thanks in advance,
My SQL statement: SQL Server seems to think my SQL Statement is too long........ I'm not sure why. "INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,DateInitiated) select 'Benjamin Short' ,'6' ,'2' ,'Printer' ,'40' ,'' ,'';" Error message: The identifier that starts with 'INSERT INTO tblChangeControls (Initiator,BPM,AddRemMod,ChangeType,SpecificChange,ChangeDescription,TechnicalDescriptionOfChange,' is too long. Maximum length is 128.
USE [Analytical] GO /****** Object: Table [dbo].[DailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:44:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DailyTickMinMaxAnalysis]( [symbol] [varchar](50) NOT NULL, [cDate] [varchar](50) NOT NULL, [part] [varchar](50) NOT NULL, [collection] [varchar](max) NULL, CONSTRAINT [remPat_Trades] PRIMARY KEY CLUSTERED ( [symbol] ASC, [cDate] ASC, [part] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
Stored procedure is:
USE [Analytical] GO /****** Object: StoredProcedure [dbo].[InsertDailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:45:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO
ALTER PROCEDURE [dbo].[InsertDailyTickMinMaxAnalysis] @symbol varchar(50), @cDate varchar(50), @part varchar(50), @collection varchar(50) = NULL AS BEGIN SET NOCOUNT ON; INSERT INTO dbo.DailyTickMinMaxAnalysis ( symbol, cDate, part, [collection]) Values ( @symbol, @cDate, @part, @collection) END
C# procedure: public void saveHistTickMinMaxAnalysis ( int tableIndex ) { if ( tableIndex != -1 ) { using ( SqlConnection conn_Analytical = new SqlConnection ( "server=ddmnvt61\sqlexpress;" + "database=Analytical;Trusted_Connection=Yes" ) ) { SqlCommand cmdm = new SqlCommand ( ); conn_Analytical.Open ( ); cmdm.Connection = conn_Analytical; cmdm.CommandType = CommandType.StoredProcedure; cmdm.CommandText = "InsertDailyTickMinMaxAnalysis"; cmdm.Parameters.Clear ( ); cmdm.Parameters.Add ( "@symbol", SqlDbType.VarChar, 50, "symbol" ); cmdm.Parameters.Add ( "@cDate", SqlDbType.VarChar, 50, "cDate" ); cmdm.Parameters.Add ( "@part", SqlDbType.VarChar, 50, "part" ); // 2 cmdm.Parameters.Add ( "@collection", SqlDbType.VarChar, 500, "collection" ); // 3 foreach ( DataRow row in Globals.dtSets.Tables[tableIndex].Rows ) { cmdm.Parameters[0].Value = ( string )row[0]; cmdm.Parameters[1].Value = ( string )row[1]; cmdm.Parameters[2].Value = ( string )row[2]; cmdm.Parameters[3].Value = ( string )row[3]; Console.WriteLine ( "Saving {0}", ( string )row[3] ); // <= where the print out occurs to confirm that the right string is being passed try { cmdm.ExecuteNonQuery ( ); } catch { } } } } } // saveHistTickMinMaxAnalysis
Table is defined with the last columns[3].Length = 500;
hi all, i'm retreiving user input using textboxes and saving to a gridview. i'm getting this error and i dont know whats causing it. <asp:SqlDataSource ID="SqlDataSource1" runat="server" InsertCommand="INSERT INTO test101(Surname,Names,Regno)VALUES (@Surname, @Names, @Regno)"ConnectionString="<%$ ConnectionStrings:engineeringConnectionString %>" ProviderName=System.Data.SqlClient ConflictDetection="CompareAllValues" >
Hello Everybody,I have a problem that I have an exception from a SQLDataSource when a user types in more characters to a TextBox then is allowed.First of all - is there ant built in ASP.NET way to handle that problem?Second - if not then I have to handle the Exception from the SQLDataSource myself. I can write a handler for that which will look like:1 protected void DetailsViewDS_OnUpdated(object sender, SqlDataSourceStatusEventArgs e) 2 { 3 if (e.Exception != null) 4 { 5 // do some stuff 6 } 7 } The problem is that I would like to show the user an error message which says how many characters he can insert. So the other question is: is there any way to get automatically from the SQL DB what is a maximum length of a varchar? If I use LEN/LENGTH it gives me the current length of given entry (which actually is the current length of the varchar). But I want to get the maximum length of this concrete varchar (NOT the varchar(max) - this shows maximum length of a varchar in general - like ~20000 ).I hope I did not make it too complex ;)All the best,Joseph A. Habdank
Hi there, I'm having a problem when I insert a string (from C#) which is 167 characters long. The field in SQL Server Express is a varchar(250), but the string gets cut of somehow at 150 characters.
Why does this happen?
The table and stored proc I use are defined like this:
USE [Test] GO /****** Object: Table [dbo].[tblImage] Script Date: 05/18/2006 10:42:27 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tblImage]( [imgID] [bigint] IDENTITY(1,1) NOT NULL, [persID] [bigint] NOT NULL, [imgPad] [varchar](250) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY]
Any way to parse out a text value (not varChar, using text data type) that is > than 8000 characters long? I'm looping through 1 big string passed to the DB that is pipe delimited, but I find myself needing the substring function to keep track of which segment I'm acting on (after an update, I then need to take that segment and remove it from the string)...but the subString function won't take anything larger than 8000 chars.
Say I have this string that is text data type...
'aaa|bbb|ccc|ddd|....'
..and so on, surpassing 8000 char length, how could you parse it out using the pipes as the delimter, then do an Update using that segment? Afterward, return to that string and find the next segment, then use it, and so on (in a loop). I tried using an update to set the string = replace(string, segmentJustUsed, '') to "erase" it, but replace can't take text as the datatype. Any help? Hope this isn't to confusing.
I have the following problem. My SQL Query that i wrote works but the result that is displayed in Query analyzer cuts most of my long text that I want in my result. The long text string is approx about 400 characters and the type is varchar of the field. Any ideas??
SELECT '510', PRODCLASSID , '1', COMPONENTID,'ENG'+SPACE(2),'#'+SPACE(254),'#'+SPAC E(254),'#'+SPACE(254),'#'+SPACE(99),externalid, 'Desc1' = CASE WHEN SUBSTRING(externalid,1,2) = 'MF' THEN 'Full machine warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. ' WHEN SUBSTRING(externalid,1,2) = 'MP' THEN 'Full machine warranty, parts only : parts covered at warranty rates applicable at the time of repair. ' WHEN SUBSTRING(externalid,1,2) = 'PF' THEN 'Power line warranty : parts, labour, mileage and others covered at warranty rates applicable at the time of repair. ' WHEN SUBSTRING(externalid,1,2) = 'PP' THEN 'Power line warranty, parts only : parts are covered at warranty rates applicable at the time of repair. ' END + CASE WHEN SUBSTRING(externalid,LEN(externalid)- 3,4) = '2018' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 18 month or 2000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '3024' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 24 month or 3000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '4030' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 30 month or 4000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '5036' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 36 month or 5000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '6042' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 42 month or 6000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '8054' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 54 month or 8000 HRS, whichever comes first. ' WHEN SUBSTRING(externalid,LEN(externalid)-3,4) = '1074' THEN 'Flexible warranty starts after the standard warranty period has expired and is covered up to 74 month or 10000 HRS, whichever comes first. ' END + 'Flexible warranty is handled according to the procedures described in ESPPM 3-10.' + CASE
WHEN prodclassid IN ('P1','P11','P8','P9') THEN ' (mileage limited to 300 km)' WHEN prodclassid IN ('P7') THEN ' (mileage limited to 200 km)' ELSE NULL END + SPACE(5000 - LEN('Desc1')) .......
FileName is coming as blank in db, while in debug mode i can see that it is being set with a proper filename. The other 2 params are inserting fine.
The FileName field is set as Text field with width length 255, and the incoming data is always shorter than 255 chars. The insertion happens without any errors/exceptions. I have also tried replacing the data for filename with a string like "Test", still it is inserting blank string.
Hi all, Recently i have installed (winxp sp2) and i have encountered with some problems, for example i always use the following Connection string for connecting to SqlServer 2000 and work with it:
I realise this'll probably seem like a very silly question, but is there any way of changing the font and font size in a query window in MS SQL Server Management Studio Express?
It would just make life a lot easier if I could see more of the text!
Hi all, We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case
select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"') even though there exist records that satisfy the condition in the table, the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"
If I remove the last 1 from the search string i get lot of records, Can anybody help me out.
When one executes a query in v7.0, the results in the result window are hard to look at one as one has to scroll sideways to see the entire rpt. Is there a better way (hgighlight) to view it so I can see the whole report without scrolling sideways?
Hi , I have got an SQL query which gives me some report against some data fed in .How can I set this query to run from a browser window .Meaning there will be some form where we input the data ( say date ) and run that query from the browser windows against that data .and the results are displayed in the browser window . Thanks for your help
I'm trying to write a query with named parameters/variables in the Management Studio - New Query pane. Where should I post a question about how to create parameters/variables?
Does anyone know why a backup database statement aborts a job in a stored procedure so that you can't handle the error with @@error, but when you run the same stored procedure in a query window the error handling does get run?
Executing the stored procedure took 45 seconds. But copying the code to a query window and setting up the variables (instead of parameters), it took 7 seconds.
In the query window, most of the processing cost (86%) is right up front in a "Distinct Sort." But in exec stored procedure, the cost for this step is 11% and the significant costs are in later "Table Scans."
I don't know why SQL Server would choose different execution plans when the code is identical in each.
Bit of a strange one here. We have a SQL Express instance running with one database which is accessed by a VB6 client application.
Performance between the application and the database isn't great, but bizarrely if you open a Query window from the Management Studio (against the database) the performance dramatically improves - to the extent that it is visually noticeable from within the application.
So, I'm thinking that the database connections being made by the application are taking longer because of instance name resolution or the like and with the Query window open this resolution is performed quicker.
Has anyone come across this situation? I can re-create it on several different computers each with their own Express instance and I've not found anything about this on the net.
Here's the connection string used by the application - I have tried various permutations of values to no avail:
I'm having some performance issues with a TSQL query. It's a complex statement but the main issue is the correlated query.
How can I convert this
SELECT TOP 5 (SELECT SUM(lt2.col3) FROM dbo.MyTable2 lt2 WHERElt2.col1 = lt.col1 AND lt2.col2 = lt.col2 AND lt2.id = lt.id ) AS Result FROM dbo.MyTable1 t1 ... to an inner join or a sql2012 window function?
By the way, I just added the TOP 5 myself while testing. It's not in the main query.
Can anyone tell me if you can display line numbers in the query windowof SQL 2005 Management Studio and if so how do I go about doing it?Thanks a bunch. TFD
Hotkey combination to be able to change the server connection from Server A to Server B in a query window in SQL Management Studio? This is so you do not have to Right click, chose "Connection" and then chose "Change Connection".
