Need Workaround For 4000-character Limit On CLR Sproc Parameters
May 11, 2007
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?
View 3 Replies
ADVERTISEMENT
Apr 4, 2006
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
View 6 Replies
View Related
Mar 3, 2008
I'm using a function which splits a comma delimitted list of numbers which I call from a stored procedure. The list is defined as a NVarChar which has a maximum length of 4000 characters. I often need to pass more than 4000 characters. If I do so, I receive the error message below. Is there any way around this limitation? Thanks.
Msg 257, Level 16, State 2, Procedure up_ExportQuickSearchresults, Line 0
Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT function to run this query.
CREATE FUNCTION dbo.Split
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (IDDir NVARCHAR(4000))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @IDDir NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @IDDir = @tempItemList
ELSE
SET @IDDir = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(IDDir) VALUES(@IDDir)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
CREATE PROCEDURE up_ExportSelectedDirectors
@p_selectedDirectors NVARCHAR(4000)
AS
BEGIN
SELECT * FROM v_SearchResultsDirectors
WHERE IDDir IN (SELECT IDDir FROM split(@p_selectedDirectors, ','))
END
GO
View 5 Replies
View Related
Feb 6, 2007
I have a package with an input column that is varchar(8000).
I want to strip the first byte off of this column and put it in one result column and the remainder of the field I want to go to a second column. If the input column is an empty string, I want to return NULL.
Pulling the first byte off works fine, no issues, however putting the remainder of the input column into an output column is giving me a little trouble.
If I use this expression:
LEN(FLD1) == 0 ? NULL(DT_WSTR,1) : SUBSTRING(FLD1, 2, LEN(FLD1) - 1))
I get an error that says my expression evaluates to a string of greater than 4000 bytes.
If I do this instead:
LEN(FLD1) == 0 ? NULL(DT_WSTR,1) : RTRIM(SUBSTRING(FLD1, 2, 7999))
The expression passes muster but I get the warning that I will be truncating the column at 4000 bytes.
In actuality, I don't care if the result column is truncated after 4000 bytes. I find the second solution to be a bit clunky and I'm wondering if anyone can give me a reason why the first solution won't evaluate but the second will?
View 14 Replies
View Related
Feb 5, 2008
Hello,
I have 2 table, "table1" is the source one and the other one "table2" is the destination.
Columns in Table1 and in Table2 are nvarchar(max).
Data loaded from table1 is performed by SSIS OLEDB data flow source, I have found out that opening "Data flow Path Editor" in the Metadata, columns are as: DT_WSTR with lenght 4000.
First question:
Why SSIS limit the column to 4000.
Then I get some error for this issue, with the error:
input column "col1" (xxxx) and reference column named (coln) have incompatible data type.
As written before both columns are string, the problem is that SSIS limit the lenght of the string to 4000.
How can I solve this issue?
Thank
View 4 Replies
View Related
Oct 25, 2007
Hello,
I created a TVF that take as input a string and a delimiter (tipically a ',') and return a table.
Very briefly this my code:
<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillCharListRow", Name:="Split_CharList", TableDefinition:="Value nvarchar(255)")> _
Public Shared Function Split_CharList(ByVal strList As SqlString, ByVal delimiter As SqlString) As IEnumerable
Return strList.Value.Split(delimiter.Value.ToCharArray(0, 1))
End Function
Private Shared Sub FillCharListRow(ByVal obj As Object, <Out()> ByRef strList As String)
strList = CType(obj, String)
strList = strList.Trim
End Sub
This is what I see from the Managment studio, after I deploy the code:
ALTER FUNCTION [dbo].[Split_CharList](@strList [nvarchar](4000), @delimiter [nvarchar](4000))
RETURNS TABLE (
[Value] [nvarchar](255) NULL
) WITH EXECUTE AS CALLER
AS
It means that the string I give to the function has to be no longer than 4000.
There is the way to define a function that can accept a longer input?
Thankx very much
Marina B.
View 4 Replies
View Related
Mar 28, 2008
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.
View 5 Replies
View Related
Aug 8, 2006
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 ?
View 4 Replies
View Related
Feb 7, 2007
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...
View 11 Replies
View Related
Jul 16, 2014
What is the maximum character limit for table check constraint . For example
ADD CONSTRAINT [xyz]
CHECK (<how many characters are allowed??>)
View 4 Replies
View Related
Mar 15, 2006
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?
View 1 Replies
View Related
Feb 23, 2007
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!
View 10 Replies
View Related
May 20, 2008
Hi ALL,
wondering if anybody can help me, i've writen a stored procedure to query my database, i want to be able to added a wildcard into a parameter incase i don't want to use it.
(don't laugh at my efforts!!!!:))
i've tried
IF @<PARAM> = NULL
@<PARAM> = LIKE %
I sure this is a simple thing to add basically i need to replace a null with everything!!
can anybody help?
View 3 Replies
View Related
Jul 23, 2005
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 Related
Jan 10, 2008
HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub
View 6 Replies
View Related
May 2, 2005
Okay, here's the scenario....
I have a bunch of un-related tables that I need to make relational.
I have a bunch of SProcs set up to help with the relating of the tables but I need the quickest way to get the values from the tables and into the SProcs.
Currently, I'm using .NET to cycle through the necessary tables, sending in their values into the SProcs, one at a time.
I'd like to have some SQL-ized way to do this so I can just make another step in my DTS package that already copies over the un-related tables from DB2.
What I'd imagine the code to look like would be something like...
EXEC SP_EMPLOYEES (SELECT FIRST_NAME,LAST_NAME FROM oldEmployees)
I know, now, that it's not as easy as that but I'm thinking there MUST be a way - even if it requires creating a new SProc.
TIA
View 1 Replies
View Related
Nov 21, 2007
Hi,I'm using c# with a tableadapter to call stored procedures. I'm running into a problem where if I have over a certain byte size or number of parameters being passed into my stored proc I get an exception that reads: "Cannot evaluate expression because a thread is stopped at a point where garbage collection is impossible, possibly because the code is optimized." If I remove one parameter, the problem goes away. Has anyone run into this before? Thanks,Mark
View 3 Replies
View Related
Dec 28, 2007
Hi,I'm new at asp .net and am having a problem. On a linkbutton click event, I want to insert into my db a row of data which includes two parameters. Param1 is the id of the logged in user, and Param2 is <%#DataBinder.Eval(Container.DataItem, 'UserId')%> which is the username of a user given through a datalist.When I execute the query: "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)"I get the error: Conversion failed when converting from a character string to uniqueidentifier.I am trying to insert these into my table aspnet_friendship into the columns userId and buddyId which are both uniqueidentifier fields. I tested the query using the same values (@Param1, @Param1) and (@Param2, @Param2) and found out that the problem is with Param2 because when I use Param1 the insert works perfectly. The value passed by Param2 is supposed to be the id of a user which I get from the datalist which selects * from aspnet_Users, so that <%#DataBinder.Eval(Container.DataItem, 'UserId')%> should also be an Id like Param1 is. Since both params are in the form of .toString(), I don't understand why one works but the other doesn't.As you can see in the code below, both of these parameters are dimmed as strings, so I don't understand why Param2 doesn't work. Can anyone please help? Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim MyConnection As SqlConnection MyConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True") Dim MyCommand2 As SqlCommand Dim CurrentUser As String CurrentUser = Membership.GetUser.ProviderUserKey.ToString() Dim add As String add = "<%#DataBinder.Eval(Container.DataItem, 'UserId')%>".ToString() Session("username") = User.Identity.Name Dim InsertCmd As String = "insert into aspnet_friendship (userId, buddyId) values (@Param1, @Param2)" MyCommand2 = New SqlCommand(InsertCmd, MyConnection) MyCommand2.Parameters.AddWithValue("@Param1", CurrentUser) MyCommand2.Parameters.AddWithValue("@Param2", add) MyCommand2.Connection.Open() MyCommand2.ExecuteNonQuery() MyCommand2.Connection.Close() End Sub Thank you.
View 3 Replies
View Related
Feb 13, 2007
I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
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.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
Thanks,
SMA49
View 3 Replies
View Related
Dec 14, 2004
The very simplified version of my problem is that these
Select DISTINCT Cast(KWID as NUMERIC)
FROM OV_MID
Select DISTINCT Convert(Numeric,KWID)
FROM OV_MID
should work, but don't because KWID is a varchar and somewhere in there is something that won't convert.
I get this error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
I would love to find out which rows are causing the error, but more importantly I would like to have a Null value where the conversion doesn't work and the numeric values where it does work.
I have already deleted all obvious non-numeric characters, but I believe there are some line terminators being read as carriage returns in this table. :confused:
Any workaround or way to determine which rows have KWID that cannot be converted to numeric would be most appreciated.
Thanks!
View 1 Replies
View Related
Jul 20, 2005
Hi All,I have read about deadlocks here on Google and I was surprised to readthat an update and a select on the same table could get into adeadlock because of the table's index. The update and the selectaccess the index in opposite orders, thereby causing the deadlock.This sounds to me as a bug in SQL Server!My question is: Could you avoid this by reading the table with a'select * from X(updlock)' before updating it? I mean: Would thisresult in the update transaction setting a lock on the index rowsbefore accessing the data rows?Merry Christmas!/Fredrik Möller
View 3 Replies
View Related
Nov 8, 2006
Hello everbody,
this query:
SELECT * FROM TBL_DEVICE_DRIVERS
WHERE (DD_CATEGORIES & 2147483648) > 0
bring the error message:
Invalid operator for data type.
The value 2147483648 is hex 0x80000000 for bitwise
joining defined.
I try to cast it to decimal, but it does not work.
Have anybody an idea?
Thanks for answers!
View 4 Replies
View Related
May 17, 2015
I have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last
View 5 Replies
View Related
Aug 6, 2015
I have the following scenario, The contents of main file are like :
ServerCentral|||||forum|||||||||||||||is||||||the||best
so||||||be|||||on||||||||||||||||||||||||||||||||||||||||||||it
And I need the output in the following form:
ServerCentral=forum=is=the=best
so=be=on=it
The logic being that multiple and consecutive occurrences of the special character, here - pipe , should be replaced by a single special character.
View 5 Replies
View Related
Aug 6, 2001
The function "DATEPART(wk, [valid_date])" appears to have the following bug:
DATEPART(wk, date) returns week 53 for the following dates (checked years 2000, 2001, 2002):
year 2000: 12-24-2000 through 12-30-2000 inclusive
year 2001: 12-30-2000 through 12-31-2001 inclusive
year 2002: 12-29-2002 through 12-31-2002 inclusive
DATEPART(wk, date) returns week 54 for the following dates:
year 2000: 12-31-2000
SQL2000 SP1.
Are there any known workarounds/fixes/patches for this (other than just hand-coding the function?)
Thanks!
David Schneider
Engineering Manager
iScribe, Inc.
DSchneider@iscribe.com
View 2 Replies
View Related
Apr 10, 2008
I'm converting crystal reports to SSRS reports right now and came across this function that I'm not familiar with.
It's a formula field in crystal that has this formula:
quote:whileprintingrecords;
NumberVar RTCurrent;
NumberVar RT31to60;
NumberVar RT61to90;
NumberVar RT91to120;
NumberVar RTOver120;
if {@AgedDays} < 31 then RTCurrent := RTCurrent + {@BalanceDue} else
if ({@AgedDays} > 30 and {@AgedDays} < 61) then RT31to60 := RT31to60 + {@BalanceDue} else
if ({@AgedDays} > 60 and {@AgedDays} < 91) then RT61to90 := RT61to90 + {@BalanceDue} else
if ({@AgedDays} > 90 and {@AgedDays} < 121) then RT91to120 := RT91to120 + {@BalanceDue} else
if {@AgedDays} > 120 then RTOver120 := RTOver120 + {@BalanceDue}
@Aged days is just an integer, but that shouldn't matter for this thread.
Is there just a While loop equivalent for this in SSRS?
View 2 Replies
View Related
Apr 10, 2008
At some point in time, when I release my code from developemnt to production, somebody will onvoke the SQL Script containg my certificates and symettric keys based on my master key. Unfortunately this seems a bit of a weekness as my SQL SCRIPT contains the CREATE MASTER KEY ENCRYPTION BY PASSWORD stement which has the password itself in clear. (script gets invoked from a command line in a batch script which is all under documeny mangement control). Obviously I would not like my password to be be in clear anywhere - i.e. not in document control nor viewable from whoever invokes the script. What is best pracrice to adopt on this? - encrypt the script file?
Thanks
View 5 Replies
View Related
Apr 28, 2006
I have written a UDF into which I pass a table name, field name, value of the field, whether alpha characters are valid, whether numerics are valid, and a string of alphanumerics that are valid. I return back a string with all invalid characters removed. Unfortunately when I use this on names and addresses in an 12000 row table, it takes forever to run. Can anyone think of an easy way to do this which isn't so labour intensive. Please see code below.
NB CHAR(32) is space, CHAR(45) is -,CHAR(39) is '
CREATE FUNCTION dbo.UDF_RemoveInvalidCharacters
( @sTableName varchar(50),-- e.g. 'Contact'
@sFieldname varchar(50),-- e.g. 'Lastname'
@sFieldValue varchar(500),-- e.g. 'Jeremi@h O''Grady84'
@sAlphaValid char(1),-- e.g. 'Y'
@sNumericValid char(1),--e.g. 'N'
@sAlphanumericsValid varchar(500))--'CHAR(32):CHAR(45):CHAR(39)'
RETURNS varchar(500)
AS
BEGIN
DECLARE @sReturnValue varchar(500),
@nTableID int,
@nFieldLength int,
@nCurrentPos int,
@sTestChar char(1),
@sValid char(1),
@nAlphanumericPos int,
@sAlphanumericTest varchar(8),
@sTempTestChar varchar(8),
@sAlphasFound char(1),
@sNumericsFound char(1),
@sAlphanumericsFound char(1)
--Get ID of table that the field is on
SELECT @nTableID = [id]
FROM SYSOBJECTS
WHERE [name] = @sTableName
--Get the length of the field
SELECT @nFieldLength = sc.length
FROM SYSOBJECTS so, SYSCOLUMNS sc
WHERE so.id = @nTableID
AND sc.id = @nTableID
AND sc.name = @sFieldName
--Initialise values
SET @sReturnValue = ''
SET @nCurrentPos = 1
SET @sValid = 'N'
SET @sAlphasFound = 'N'
SET @sNumericsFound = 'N'
SET @sAlphanumericsFound = 'N'
--Test each character to ensure it is valid before adding it to the return string, a string consisting solely of alphanumeric characters would be wrong
WHILE @nFieldLength >= @nCurrentPos
BEGIN
SET @sTestChar = substring(@sFieldValue,@nCurrentPos,1)
IF @sAlphaValid = 'Y' --alphas are valid
BEGIN
IF UPPER(@sTestChar) in ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z')
BEGIN
SET @sValid = 'Y'
SET @sAlphasFound = 'Y'
END
END
IF @sNumericValid = 'Y' AND @sValid <> 'Y'--numerics are valid
BEGIN
IF @sTestChar in ('0','1','2','3','4','5','6','7','8','9')
BEGIN
SET @sValid = 'Y'
SET @sNumericsFound = 'Y'
END
END
SET @nAlphanumericPos = 1
WHILE LEN(@sAlphanumericsValid) > @nAlphanumericPos AND @sValid <> 'Y' --alphanumerics that are valid
BEGIN
IF CHARINDEX(':',SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,LEN(@sAlphanumericsValid))) > 0
BEGIN
SET @sAlphanumericTest = SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,CHARINDEX(':',SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,LEN(@sAlphanumericsValid)))-1)
END ELSE
BEGIN
SET @sAlphanumericTest = SUBSTRING(@sAlphanumericsValid,@nAlphanumericPos,(LEN(@sAlphanumericsValid)-@nAlphanumericPos)+1)
END
SET @sTempTestChar = 'CHAR(' + RTRIM(LTRIM(STR(ASCII(@sTestChar)))) + ')'
IF @sTempTestChar = @sAlphanumericTest AND (@sAlphasFound = 'Y' OR @sNumericsFound = 'Y') --alphanumerics are only valid once we have alpha or numerics
BEGIN
SET @sValid = 'Y'
SET @sAlphanumericsFound = 'Y'
END
SET @nAlphanumericPos = @nAlphanumericPos + LEN(@sAlphanumericTest) + 1
END
IF @sValid = 'Y'
BEGIN
SELECT @sReturnValue = @sReturnValue + @sTestChar
END
SET @nCurrentPos = @nCurrentPos + 1
SELECT @sValid = 'N'
END
IF @sAlphanumericsFound = 'Y' AND @sNumericsFound = 'N' AND @sAlphasFound = 'N' --alphanumerics on their own are not valid
BEGIN
SELECT @sReturnValue = ''
END
RETURN @sReturnValue --in the example I would get Jeremih O'Grady
View 1 Replies
View Related
Apr 30, 2007
Hi,
Any insights to this issue would be great.
Following is my problem statement. I have write around 20Batch programs and in each batch i have atleast 7-8 data validations. If any of the data validation fails then i have to perform a log operation and exit.
Now I have each of my data validation as a Script Task (Control Flow) which inturn would call my SP and set the "Status" variable accordingly
I have written a "OnVariableValueChanged" Event with Raise Change Event for "Status" variable set to "True" Now in this event i check if Status = False, if it is false then i perform the log operation and throw a new DTSException to abort the control flow execution. The event gets fired but it continues to process the next control step(but i wanted it stop there). I could have acheived this by setting a precendence constraint (Status = True) for all of my control flow task but i feel the other approach to be very elegant.
Any suggestions to make this work??
Thanks & Regards
Arvind T N
View 2 Replies
View Related
Jun 21, 2007
I am trying to group on the number of distict field values. Basically:
=Ceiling(CountDistinct(Fields!ClientID.Value, Nothing)/10)
So for every 10 different clients, I get a group. I get an error with the above function saying aggregates are not allowed in a grouping expression. I tried creating a text box with the running value:
=RunningValue(Fields!ClientID.Value,CountDistinct,Nothing)
The value of the text box is correct, but I can't reference that text box in the grouping function. It says the textbox is undefined.
Any suggestions for a workaraound?
Thanks.
View 5 Replies
View Related
Apr 23, 2004
I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.
What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?
Thanks,
Peggy
Sproc that is called from ASP.NET:
ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int
EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID
UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended
WHERE
EntryLogID = @EntryLogID
EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID
Called Sprocs:
*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS
SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID
RETURN
*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS
UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID
UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID
View 2 Replies
View Related
Jan 20, 2004
create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID
GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??
Tks
View 2 Replies
View Related
Apr 15, 2004
I have data in a column that starts with 1-4 characters followed by a dash then followed by an number of characters (ex: EU-Surgery).
How do I select everything to the right of the dash when the number of characters to the left of the dash varies?
View 3 Replies
View Related
