I am currently having a problem where my SQL server seems to lock any variables to 1000 characters (ie. varchar(8000) can only hold 1000)
I have read in numerous sources it was possible to change that limit so the varchar can truly hold the 8000 characters and not stop at 1000, but there was no info on how to do this.
I am looking for a "How to" to put this limit to 8000.
Thank you!
Juste have fun with the following, if you can manage this out your my savior, been wasting far too much time on it, tried a couple of method and none ever worked. Conversion from type 'DBNull' to type 'String' is not valid.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code. Exception Details: System.InvalidCastException: Conversion from type 'DBNull' to type 'String' is not valid.Source Error:
Line 105:Line 106: Protected Sub sdsProprActionAJO_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sdsProprActionAJO.InsertedLine 107: Dim MessageSysteme As String = CType((CType(e.Command.Parameters("SQL_MSG"), IDbDataParameter)).Value, String)Line 108: End SubLine 109: With sdsProprActionAJO .ConnectionString = ConfigurationManager.AppSettings("DB_CS_MSSQL") .InsertCommandType = SqlDataSourceCommandType.StoredProcedure .InsertCommand = "dbo.sp_adm_cartListe" .InsertParameters.Clear() .InsertParameters.Add("Mode", TypeCode.String, "SEL") .InsertParameters.Add("LPR_Groupe", TypeCode.String, CBool(CType(Me.pnlProprActionAJO.FindControl("rblProprActionAJOGroupe"), RadioButtonList).SelectedValue)) .InsertParameters.Add("LPR_Etiquette", TypeCode.String, CType(Me.pnlProprActionAJO.FindControl("tbProprActionAJOEtiquette"), TextBox).Text) .Insert() End With End Sub Protected Sub sdsProprActionAJO_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles sdsProprActionAJO.Inserted Dim MessageSysteme As String = CType((CType(e.Command.Parameters("SQL_MSG"), IDbDataParameter)).Value, String) End Sub Protected Sub sdsProprActionAJO_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles sdsProprActionAJO.Inserting Dim Param As System.Data.SqlClient.SqlParameter = New System.Data.SqlClient.SqlParameter() param.DbType = DbType.String param.Direction = ParameterDirection.Output param.ParameterName = "SQL_MSG" e.Command.Parameters.Add(Param) End Sub CREATE PROCEDURE [dbo].[sp_Adm_Proprietes_AJO](@MODE VARCHAR(3),@LPR_Groupe BIT = NULL,@LPR_Etiquette NVARCHAR(32) = NULL,@RPP_LPR_IdParent [INT] = NULL,@RPP_LPR_IdEnfant [INT] = NULL,@SQL_MSG NVARCHAR(8) OUTPUT)AS--DECLARE @SQL_TEMP NVARCHAR(4000)DECLARE @INT_TEMP [INT]--IF @MODE = 'RRP'BEGIN INSERT INTO t__REL__PropParent_PropEnfant ( RPP_LPR_IdParent, RPP_LPR_IdEnfant ) VALUES ( @RPP_LPR_IdParent, @RPP_LPR_IdEnfant )END--IF @MODE = 'LPR'BEGIN IF NOT @LPR_Etiquette = NULL BEGIN SELECT * FROM t_Lib_Proprietes WHERE (LPR_Groupe = @LPR_Groupe ) AND (LPR_Etiquette = @LPR_Etiquette ) IF @@ROWCOUNT <= 0 BEGIN INSERT INTO t_Lib_Proprietes ( LPR_Groupe, LPR_Etiquette ) VALUES ( @LPR_Groupe, @LPR_Etiquette ) SET @SQL_MSG = 'CON_000A' END ELSE BEGIN SET @SQL_MSG = 'ERR_000A' END END ELSE BEGIN SET @SQL_MSG = 'ERR_003A' ENDENDRETURN @SQL_MSGGO
Hi,
I€™m having a problem with a storedprocedure in SQL Server 2005 Developer Edition: I have declared a varchar(max) variable, but when a try to assign a large string it gets truncated to 255 characters, I get the same result if a declare the variable with varchar(8000), ¿can someone help me fix this?, I don€™t need the full 8000 characters, but I need more that just 255.
Thanks.
Hi,
I have created a report with the report server project template.
the report is created from stoked procedure having defaut input parameters.
With visual studio, i publish my report on reportserver. whenever i access to my report on this url :
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render. the created report is with the default parameters.
I would like to know if i can transmet parameters for the stocked procedure to build the report with the request i want.
I tried to put parameter directly in the url in this way
http://localhost/ReportServer/Pages/ReportViewer.aspx?%2fReport+Project4%2fReport4&rs:Command=Render&@region='toto'
but without success.
my error message is that one
An attempt was made to set a report parameter '@region' that is not defined in this report. (rsUnknownReportParameter)
My stocked procedure is :
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[akli] @region varchar ='m'
as
select * from dbo.Report2 where region=@region
The request used to buid the report ?
DECLARE @region varchar
EXECUTE dbo.akli @region
What is wrong in that ?
Thanks for your help.
Arioule
I am using VB.NET / SQL to extract data from an Excel spreadsheet and insert it (via a dataset) into an SQL database.
My SQL select to form the dataset is:
Select 0, '01/01/1905', '00:00' , 'Description: ' + [DESCRIPTION] + '<br/><br/>Type: ' + [Field5] + '<br/> Thankyou' As Description From [Primary Schools$]
I am using Jet for the connection, the connection string is:
sExcelConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Path ofile.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
My problem is that, although Description and Field5 themselves are not above 255 characters, the combination of the two, plus my own text do come out as more than 255 characters and as such get truncated.
I have tried using cast and nested selects, but to no avail.
Any body got any ideas ?
I am trying to store long text so I gave the field in question the TEXT datatype. But it only lets me store 1023 characters. I was expecting (but not quite needing) 2,147,483,647 characters when using the TEXT datatype. Can some one point out what I am missing.
Great forum, my first post, ahhh....
I will continue to search the forum for my answer. Thank you for taking the time to read this.
John
...uh, something like that...
I am building rather large SQL statements using expressions and I am about to hit the 4000 character limit. First, what were the reasons for setting such a low limit? With it in place you are forced to come up with other solutions, but perhaps that was the intention.
Speaking of limits, what are the limits for storing a value in a string that is defined in a package configuration file? What are the limits when creating a string within a script task and then use it to set a string variable in the package? What are the limits when using a string value as an input parameter to an Execute SQL Task using OLE DB? Do these differ depending on the connection type?
I guess I'm getting a bit paranoid about limits, but I don't want to hit any others late in the design.
Regards,
Lars
What is the maximum character limit for table check constraint . For example
ADD CONSTRAINT [xyz]
CHECK (<how many characters are allowed??>)
Do MS SQL Express support Asian character set?
If I want to input Traditional Chinese character, what collation I should use?
Is there any limitation on the no of connect to MS SQL Express?
CHALASANI writes "I have a name in my table which is "ra_ja" I want to find the name with the help of the'_' character.
Can you please help with the querry
Sorry with my english
Thank you"
Hi, I need to replace a character from a column of a txt file. I have defined the column and the values (datetime type for sql server) that I receive are like this: 2008-02-15-20.07.19
So, I need to replace the "." with a ":" beacuse those are minutes and sql server uses ":" for minutes
How can I do this? Any help?
Thanks
I've written a managed (C#) stored procedure with the following signature:
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Sproc(string startDate, string endDate, string idList)...
Sometimes when I call this sproc, my comma-separated list of IDs exceeds 4000 characters. How can I get around this problem?
I guess I need something equivalent to NVarchar(MAX), but for CLR sprocs instead of TSQL.
Any thoughts?
I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:
NNNNNRJKSURNNNEJNNNN
Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?
Hi all,
Am searching for a built-in function that retrives me the occurrence (counts) of a particular character inside a string.
For e.g. In the String 'SqlServer'. I want to find the no of the character 'e', which shud retrive the value as 2.
Can this be acheived through any function.
Cheers !
ash
Hi - I am changing a field from type nvarchar to type text, given thatI need to store strings longer than 255 characters. To do this Ichange the data type in SQL Server, then I change the parameter code inthe calling procedure, as per below:cmd.Parameters.Append(cmd.CreateParameter("@title", adVarWChar,adParamInput, 255, title));becomes:cmd.Parameters.Append(cmd.CreateParameter("@title", adLongVarWChar,adParamInput, 1073741823, title));However, when I do this, for some reason, the field is still limited to255 characters - when I try to update the field with 256 characters,the error 'Application uses a value of the wrong type for the currentoperation.' occurs.Why is this? I've checked that the correct data is contained in theparameter. When I look at the data in the database, the column inquestion shows the content, whereas the next column, which has alwaysbeen of type text, shows '<LongText>' - does this mean anything? Do Ineed to do something special to convert the column from nvarchar totext?Many thanks,Iain
View 1 Replies View RelatedHi all,
I use a "Exec DTS 2000" task.
I have a variable user::MYVAR in my SSIS package, wich value is "PARENT".
I pass this variable as an outer variable to my dts 2000 package, wich receive it correctly, and then update it to "UPDATED".
After this task finishes, when I come back to SSIS, MYVAR is still at "PARENT".
Is it possible to see the update from the parent package ?
Thanks
I've been looking around but haven't yet found the syntax for usage of global variables in an SQL Task.
I've set the global variable Id (see code below):
if (select field from table where id = @[User::id]) is null
select top 1 1 as response from table
else select top 1 0 as response from table
My objective with it is to set another global variable (@isNull). Supposably, when the selection returns null, I should set the variable to null, I did it by using the selections and mapping the response to that variable (is ther a better way to do so?).
When I try to execute this, it says the variable has not been defined.
Here is the error:
Error: Must declare the variable '@'.
I've also tryed it withou the brackets and the User:: thing in the beggining, (@id directly) and here is the response:
Error: Must declare the variable '@id'.
How should I access the global variables in the SQL code?
(BTW, I've checked the field in execution time and it is set to 23, the correct Id, so the block that preceedes this one is working properly)
Thanks,
I have a test page where I'm using SqlConnection and SqlCommand to update a simple database table (decrease a number).
I'm trying to figure out how to make a number in the database table to decrease by 1 each time a button is being pressed. I know how to update the number by whatever I want to, but I have no idea what the correct syntax is for putting variables inside the query etc. Like "count -1" for instance.
The database table is called "friday" and the one and only column is called "Tickets".
Here's the code behind the button:protected void Button1_Click(object sender, EventArgs e)
{SqlConnection conn;
conn = new SqlConnection("Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***");
conn.Open();int count = -1;
SqlCommand cmd = new SqlCommand("select Tickets from friday", conn);
count = (int)cmd.ExecuteScalar();if (count > 0)
{
string updateString = @"
update friday
set Tickets = 500" ; <------ Here I want to set Tickets like "current count -1"SqlCommand cmd2 = new SqlCommand(updateString);
cmd2.Connection = conn;
cmd2.ExecuteNonQuery();
}
else
{
}
conn.Close();
}
Hi
I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?
Thanks
Mani
Just wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
Awesome! I don't alway get the email notification of whoever reply to the posting. I think it only work after I log off of the forum.
Scott
There is a table called "tblvZipCodes" that contain a zipcode of all cities, area code that are located in that zip code.
The problem I have with the inner join is that there are more than 1 cities in one zipcode code. Is there a way to just return only the 1st row and not return the rest of the rows from the tblvZipCodes in the INNER JOIN query?
Thanks..
Code:
SELECT TOP 100 PERCENT dbo.tblPurchaseRaw.Year, dbo.tblPurchaseRaw.Make, dbo.tblPurchaseRaw.Model, dbo.tblPurchaseRaw.ModelType,
dbo.tblPurchaseRaw.Color, dbo.tblvZipCodes.ZIPCode, dbo.tblvZipCodes.City, dbo.tblvZipCodes.County, dbo.tblvZipCodes.State,
dbo.tblvZipCodes.AreaCode, dbo.tblvZipCodes.Region, dbo.tblaAccounts.Name, dbo.tblaAccounts.PhoneOne, dbo.tblaAccounts.AccountID,
dbo.tblPurchaseRaw.AcceptedID, dbo.tblPurchaseRaw.Series, dbo.tblPurchaseRaw.BodyStyle, dbo.tblaAccounts.WebSite,
dbo.tblaAccounts.SalesEmail, dbo.tblPurchaseRaw.EmailTo, dbo.tblPurchaseRaw.PhotoURL, dbo.tblPurchaseRaw.Mileage,
dbo.tblPurchaseRaw.RawID, dbo.tblvRegions.Name AS RegionName, dbo.tblPurchaseRaw.VIN, dbo.tblPurchaseRaw.Style,
dbo.tblPurchaseRaw.StockDate
FROM dbo.tblPurchaseRaw INNER JOIN
dbo.tblaAccounts ON dbo.tblPurchaseRaw.AccountID = dbo.tblaAccounts.AccountID INNER JOIN
dbo.tblvZipCodes ON dbo.tblPurchaseRaw.ZipCode = dbo.tblvZipCodes.ZIPCode INNER JOIN
dbo.tblvRegions ON dbo.tblvZipCodes.Region = dbo.tblvRegions.RegionID
WHERE (CONVERT(char, dbo.tblPurchaseRaw.StockDate, 101) <> '01/01/1900') AND (dbo.tblPurchaseRaw.SoldRawID IS NULL) AND
(dbo.tblPurchaseRaw.AcceptedID <> - 10) AND (dbo.tblPurchaseRaw.AcceptedID <> - 1)
ORDER BY dbo.tblvZipCodes.ZIPCode
i have created the table empid,empname,dob,salary for the insert i need to create procedure of insert empid,empname,dob,salary i got the function for autogenerated number for empid
mmy question how to put function inside the procedure?
my function below,pls guide me its very urgent.
create function NextCustomerNumber()
returns char(5)
as
begin
declare @lastval char(5)
set @lastval = (select max(customerNumber) from Customers)
if @lastval is null set @lastval = 'C0001'
declare @i int
set @i = right(@lastval,4) + 1
return 'C' + right('000' + convert(varchar(10),@i),4)
end
Desikankannan
Hi,I have a .NET application that I want to save the Config.EXE contentsto my SQL database for remote review/testing. This config file is3700+ bytes long. I created a field in one of my tables with a VARCHAR4800 and then created a stored procedure that receives a parameter(also VARCHAR(4800).However it fails to write anything if the length of the value that Ipass is anything greater than 900. If I pass exactly 900 characters orless - the data is written to the field. If I pass 901 characters Iget nothing.I'm suspicious since it is exactly 900. I seriously doubt it's somelimitation of MS-SQL so I need a nudge in the right direction.Thanks
View 8 Replies View RelatedCan i get a login name of the user that execute the store procedure that have open the connection to the DB?
thanks, Paolo giusti
hi all,
i'm wondering if i can use one stored procedure in too cases, this is the senario:
i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user
if there a way to use if and else to make this happen
this is what i did so far:
CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as
if @indexType = 1
begin
SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin)
end
else
begin
SELECT * FROM table WHERE user = @user
end
GO
any suggestion will be very helpful
thanks
Hi,
I'm creating a generic procedure in the master db and I'm getting the database name as a input parameter.
How do I set the database inside the procedure , according to this parameter?
T-Sql does not accept "USE" inside procedures .
I tried "sp_defaultdb" but when I select from a specific table in the procedure it still does not identify the table as belonging to that database, it still looks for the table in the master db.
Hi,
we have the senario we want to call oracle table inside the sql stored procedure, it is possible to call oracle table inside the sql stored procedures.
Regards
Kannan.D
Desikankannan
Hello,I need to have a stored procedure, which performs sorting. Something likethis:CREATE PROCEDURE procname@sortby varchar(30)ASBEGINSELECT some, columnsFROM some_tableORDER BY @sortbyEND(of course, i know this won't work, but it gives the idea of what i mean)Is there a possibility to write a procedure which behaves like that? It isimportant for me not to have multiple procedures just for different sortingcriteria...Thanks,Mike
View 3 Replies View RelatedI want to execute some insert statements within a stored procedure and commit those changes regardless of any raiseerror that occurs later in the stored procedure. My difficulty is that I am forced to use raiseerror with severity 16 in order to send a message through the powerbuilder application interface (compiled vendor code). I have tried save points but can't get that to save my insert and still present an error to the user about something else that happens later.
here is an example
BEGIN procedure
Insert something and save it even if error is raised below
RaiseError('you made a mistake and need to do this.',16,-1)
END procedure
Hi folks
I nead to call a stored procedure in a where statemene, but MSSQL dont like that.
My problem is that the StoredProcedure is calling itself recursive and therfore its impossible to add the code as a standard SELECT statement. Here is the code
ALTER PROCEDURE dbo.advsp_FilterRecordRights
@RequesterGUID Char(20),
@EntityGUID Char(20)
AS
BEGIN
if not Exists(Select *
from GUIDRightsH
where GUIDRightsH.EntityGUID = @EntityGUID and
GUIDRightsH.RequesterGUID = @RequesterGUID and
GUIDRightsH.RecProp <> 0)
Begin
Return 1
end
if not Exists(Select *
from UsergroupMembers
where UserGroupMembers.UsergroupGUID = @RequesterGUID and
dbo.advsp_FilterRecordRights(UserGroupMembers.UserGUID,@EntityGUID) = 1)
Begin
Return 1
end
Return 0
END
In my ASP page, when I select an option from the drop down list, it has to get the records from the database stored procedure. There are around 60,000 records to be fetched. It throws an exception when I select this option. I think the application times out due to the large number of records. Could some tell me how to limit the number of rows to be returned to avoid this problem. Thanks.
Query
SELECT @SQLTier1Select = 'SELECT * FROM dbo.UDV_Tier1Accounts WHERE CUSTOMER IN (SELECT CUSTOMERNUMBER FROM dbo.UDF_GetUsersCustomers(' + CAST(@UserID AS VARCHAR(4)) + '))' + @Criteria + ' AND (number IN (SELECT DISTINCT ph1.number FROM Collect2000.dbo.payhistory ph1 LEFT JOIN Collect2000.dbo.payhistory ph2 ON ph1.UID = ph2.ReverseOfUID WHERE (((ph1.batchtype = ''PU'') OR (ph1.batchtype = ''PC'')) AND ph2.ReverseOfUID IS NULL)) OR code IN (SELECT DISTINCT StatusID FROM tbl_APR_Statuses WHERE SearchCategoryPaidPaymentsT1 = 1))'
Basically I have two strings. Both strings will contain similar data because the 2nd string is the first string after an update of the first string takes place. Both strings are returned in my Stored Procedure
For example:String1 = "Here is some data. lets type some more data"String2 = "Here's some data. Lets type some data here"I would want to change string2 (inside my Stored Procedure) to show the changed/added text highlighted and the deleted text with a strike though.
So I would want string2 to look like thisstring2 = "Here<font color = "#00FF00">'s</font> <strike>is</strike> some data. <font color = "#00FF00">L</font>ets type some <strike>more</strike> data <font color = "#00FF00">here</font>"
Is there an way to accomplish this inside a stored procedure?