I'm using sys.dm_fts_parser dynamic management function to tokenize a string of characters >4000. The function doesn't accept a query_string parameter >4000 characters. Is there a way around this? I've tried to execute the SELECT defined in the function but that doesn't work.
View 2 RepliesA trigger existed on the job steps table which captured any changes before and after. The insert table was limited changes to 4000 characters.
SQL Server 2012 SP2 Enterprise Edition (11.0.5058.0) on Windows Server 2008 R2
At some point a few months ago we encountered an issue where we hit a size limit of ~4000 characters on the amount of text we could enter into a Transact-SQL step of an Agent job. Attempting to create a job like this with sp_add_job will produce the error
Msg 50000, Level 16, State 10, Procedure sp_add_jobstep_internal, Line 255
String or binary data would be truncated.
Adding the job step via SSMS yields
Alter failed for JobStep 'xxx'. (Microsoft.SqlServer.Smo)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo)
String or binary data would be truncated.
The statement has been terminated. (Microsoft SQL Server, Error: 8152)
I've checked sp_add_jobstep_internal, sp_add_jobstep and the sysjobsteps table and all references to the command field are nvarchar(max). We can run the same job creation code without error on a SQL Server 2008 R2 Enterprise Edition machine and two SQL Server 2012 SP2 Developer Edition boxes. All our 2012 servers were fresh installs, not upgrades.
This is with SQLCe NET 3.5.0.0 running on Windows Server 2003 or Server 2008, not on a Windows mobile operating system.
The following code fails with ntext entries above 4000 characters:
Dim cn As New SqlCeConnection(ConnectString())
If cn.State = ConnectionState.Closed Then
cn.Open()
End If
Dim info as string
info = "This is lengthy text".PadLeft(4200)
Dim cmd As SqlCeCommand
strSQL = "create table testTable ("
strSQL &= "docType nvarchar (50) NULL, "
strSQL &= "docFlag nvarchar(10) NULL, "
strSQL &= "docData ntext NULL, "
strSQL &= " )"
cmd = New SqlCeCommand(strSQL, cn)
Try
cmd.ExecuteNonQuery()
Catch sqlexception As SqlCeException
MessageBox.Show(sqlexception.Message & vbNewLine & strSQL, "Table Error 7", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Catch ex As Exception
MessageBox.Show(ex.Message, "Table Error 8", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
cn.Close()
End Try
If cn.State = ConnectionState.Closed Then
cn.Open()
End If
'---- insert a row into the testTable
strSQL = "INSERT INTO testTable ("
strSQL &= "docType, "
strSQL &= "docFlag, "
strSQL &= "docData, "
strSQL &= ") "
strSQL &= "VALUES ("
strSQL &= "@docType, "
strSQL &= "@docFlag, "
strSQL &= "@docData, "
strSQL &= ")"
Try
cmd = New SqlCeCommand(strSQL, cn)
cmd.Parameters.AddWithValue("@docType", "a type")
cmd.Parameters.AddWithValue("@docFlag", "a flag")
cmd.Parameters.AddWithValue("@docData", info)
cmd.ExecuteNonQuery()
Catch sqlexception As SqlCeException
MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Catch ex As Exception
MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
cn.Close()
End Try
End If
Changing the cmd.Parameters as follows works:
Dim paramdocData As SqlCeParameter
Try
cmd = New SqlCeCommand(strSQL, cn)
cmd.Parameters.AddWithValue("@docType", "a type")
cmd.Parameters.AddWithValue("@docFlag", "a flag")
paramdocData = cmd.Parameters.Add("docData", SqlDbType.NText)
paramdocData.Value = info
cmd.ExecuteNonQuery()
Catch sqlexception As SqlCeException
MessageBox.Show(sqlexception.Message, "Table Error 9", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Catch ex As Exception
MessageBox.Show(ex.Message, "Table Error 10", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1)
Finally
cn.Close()
End Try
Thanks to the following Microsoft ReadMe for the above suggestion. See:
http://download.microsoft.com/download/f/7/2/f72ebbf8-4df1-4800-b4db-c2405c10d937/ReadmeSSC35.htm
Dear All
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
Here is my query sample for yours to understand.
SET NOCOUNT ON
DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)
Set @qstring = 'select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm'
PRINT '-------- ITEM Products Report --------'
DECLARE ITEM_cursor CURSOR FOR
execute (@qstring)
OPEN ITEM_cursor
FETCH NEXT FROM ITEM_cursor
INTO @ITEMCODE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Products From ITEM: ' +
@ITEMNAME
PRINT @message
-- Get the next ITEM.
FETCH NEXT FROM ITEM_cursor
INTO @ITEMcode
END
CLOSE ITEM_cursor
DEALLOCATE ITEM_cursor
Why i use @qstring? It is because the query will be changed by different critiera.
Regards
Edmund
Hi,
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
If I have a string with the following values, I’d like to replace the non-numeric characters with a space.
Input
01234-987
012345678
01234 ext 65656
Tel 0123456
012345 898989
Output
01234 987
012345678
01234 65656
0123456
012345 898989
Hi all,I'm using MSSQL 2005, using the nvarchar(MAX) but it doesn't seems to take more than 4000 characters... Any idea why?Thank you,Kenny.
View 6 Replies View RelatedIs it not possible for expressions to work with variables that contain more than 4000 characters? I've heard of a limitation of expressions and 4000 characters, but I thought this meant that the expression itself cannot be more than 4000 characters -- not that the expression cannot work with values of more than 4000 characters.
For example, I have a two variables:
Test1 (type = String):
Value = [Some really long string whose length is > 4000 characters]
Test2 (type = String, EvaluateAsExpression = True):
Expression = "-" + @[User::Test1] + "-"
Test2 throws the following error:
The variable User::Test1 contains a string that exceeds the maximum allowed length of 4000 characters.
Reading the variable "User::Test1" failed with error code 0xC0047100.
Why is there such a limitation when working with string values? This seems really restricting. Are there any possible workarounds?
Thanks in advance.
Jerad
Hi,
I have a clr stored procedure that takes in 2 parameters, input xml and a query name. The stored procedure transforms the xml with a the xslt for the given query name (stored in a database). I am currently using and output parameter that is of type NVarChar(4000) to retrieve the xml in .net.
This all works fine unless the xml that is being transformed is greater than 4000 characters which will happen. Are there any ways of returning a string/xml greater than 4000 characters (in the region of 60-70k characters).
Thanks for your help
N
SQL Server 2012 SP2 Enterprise Edition (11.0.5058.0) on Windows Server 2008 R2
At some point a few months ago we encountered an issue where we hit some size limit on the amount of text we could enter into a Transact-SQL step of an Agent job. Attempting to create a job like this with sp_add_job will produce the error
Msg 50000, Level 16, State 10, Procedure sp_add_jobstep_internal, Line 255
String or binary data would be truncated.
Adding the job step via SSMS yields
Alter failed for JobStep 'xxx'. (Microsoft.SqlServer.Smo)
Additional information:
An exception occurred while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo)
String or binary data would be truncated.
The statement has been terminated. (Microsoft SQL Server, Error: 8152)
I've checked sp_add_jobstep_internal, sp_add_jobstep and the sysjobsteps table and all references to the command field are nvarchar(max). We can run the same job creation code without error on a SQL Server 2008 R2 Enterprise Edition machine and two SQL Server 2012 SP2 Developer Edition boxes. All our 2012 servers were fresh installs, not upgrades.
SQL Query. What i need is to be able to extract specific characters from data.
Eg name
1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results
i.e. 1.2-KPIA1-App-00001 1.2 KPIA1
*I need this in part of a script as there is thousands of rows of data.
I'm putting a process together to run a DBCC CHECKCONSTRAINTS process against copies of client databases.The author application doesn't set the constraints as trusted, and therefore we need to check the integrity of the data.
The problem is that some of the Check constraints have a definition that is longer than 4,000 characters.When this is the case, DBCC CHECK CONSTRAINTS fails.One option is that I write a cursor to select the constraints that have a definition less than 4,000 characters and then call the DBCC command for those particular constraints. However, I'd prefer a more elegant approach - ideally a way to run DBCC CHECKCONSTRAINTS against all constraints regardless of the length of the definition
I want the reason for the above statement where I user nvarchar(4000)
to insert the japanese text it give the same error , why we cannot have
maximum size ? if we can have maximum size than 8060 what is the
setting
Please help me ..
Thanks in anticipation
Ashish Tahasildar
When using SQL CE 3.5 and I try to insert data to DataSet with NTEXT type column that has more than 4000 characters I get this error
"InvalidOperationException was unhandled
@p4 : String truncation: max=4000, len=8414
I had not problem with SQL Compact 3.1 I didn't even changed DataSet I just upgraded my database 3.1 -> 3.5 and I get this error. This is serious bug....
There are other people who have the same problem
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=812683&SiteID=17
is there any way i can make a field in a table accomodate more than 1023 charcters? i used the 'varchar' datatype and used a length of 2500, but still, I can't fill up a field with more than 1023 characters. Is there any way to change it?
Also, is there any way to used a symbol or special character in a field? Can SQL server identify such a character? like the alpha or beta symbol...
Thank you...
Consider the following: I have a table, say ORDERS, with these entries -
CustID
ProductID
1 CAN
2 2
3 1,2
4 4
5 1,2,3,4,5,CAN
6 10
7 CAN
8 1,CAN
I'd like to write a script to return only those rows WHERE ProductID = CAN along with other values in the same column. In this example, I'd like to return rows 5 & 8. How can I write this in T-SQL? So, say, check if ProductID has a comma ',' value plus the 'CAN' string. If yes, then return that row. If I use the LIKE operator, it'll return rows 1,5,7, and 8.
when I am trying to perform below query,
INSERT INTO EMPLOYEE
SELECT TOP 100 *
FROM EMPLOYEE_LANDING;
I am getting Invalid length parameter passed to the LEFT or SUBSTRING function.
I'm trying desparately to write a PadRight function in SQL Server 2005. I seem to be failing miserably because the trailing spaces disappear when the data is returned. First of all, why does SQL Server think I want my string trimmed? And second, how do I overcome this? Code below:
Code Snippet
CREATE FUNCTION [dbo].[PadRight]
(
@sourceString NVARCHAR(4000),
@length INT,
@padCharacter NCHAR(1) = ' ',
@trimBeforePadding BIT = 1
)
RETURNS NVARCHAR(4000) AS
BEGIN
DECLARE @returnStringLength AS INT, @toReturn AS NVARCHAR(4000)
SET @toReturn = LEFT(@sourceString, @length)
IF @trimBeforePadding = 1
SET @toReturn = RTRIM(LTRIM(@toReturn))
SET @returnStringLength = LEN(@toReturn)
IF @returnStringLength < @length
SET @toReturn = @toReturn + REPLICATE(@padCharacter, @length - @returnStringLength)
RETURN @toReturn
END
GO
Greetings,
Is there anything new on returning max of two values in SQL 05? There seems to be nothing I have searched everywhere. Only option might be to create my own UDF or do a CASE statement, but I could not believe my eyes there is no such thing?
Thanks
The length of my query string is greater than 255 characters. SQL Server is truncating the query string when I assign it to a Local variable.
What should i do....???
Hi, I have a datareader component of which i am dynamically setting its sqlcommand statement with expression (click the background of dataflow > properties > expressions). Now my sql select statement has about 600 fields so that makes my expression statment "select field1, field2, .....from table1 where field2 >=" + @[User::dateforfield2] but when i evalute the expresssion (which is right), i get the error: A string literal in the expression exceeds the maximum allowed length of 4000 character and i think its because of the fields in my select statment causing my string literal to grow more than 4000 characters. Is there any way to increase the max string literal for expressions. Please help.
View 1 Replies View RelatedHello,
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.
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
I created this unique codes and
I need all [FRMDAT] field set to ‘12/31/2014’ in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > ‘12/31/2014’
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
I have a created a table and entered data into the table as follows:
CREATE TABLE t ( id INT , txtcol varchar(1000) )
INSERT INTO t ( id , txtcol ) VALUES ( 1 , 'ATXR_SOURCE_ID,CDDL_AG_PRICE,CDDL_ALLOW,CDDL_ALTDP_EXCD_ID,CDDL_CAP_IND,CDDL_CHG_AMT,CDDL_COINS_AMT,CDDL_CONSIDER_CHG,CDDL_COPAY_AMT,CDDL_DED_AC_NO,CDDL_DED_AMT,CDDL_DIS_PA_LIAB,CDDL_DISALL_AMT,CDDL_DISALL_EXCD,CDDL_DISC_AMT,CDDL_DP_PRICE,CDDL_FROM_DT,CDDL_PAID_AMT,CDDL_PF_PRICE,CDDL_PR_PYMT_AMT,CDDL_PRICE_IND,CDDL_REF_IND,CDDL_RISK_WH_AMT,CDDL_SB_PYMT_AMT,CDDL_SURF,CDDL_TOOTH_BEG,CDDL_TOOTH_END,CDDL_TOOTH_NO,CDDL_TOT_PA_LIAB,CDDL_UNITS,CDDL_UNITS_ALLOW,CGCG_ID,CGCG_RULE,DPCG_DP_ID_ALT,DPDP_ID,DPTC_CD,PDVC_LOBD_PTR,PSDC_ID,UTUT_CD' )
Now if i select data using the query below the txtcol field displays only 255 characters :
SELECT * FROM t
Y is this happening?
What I want to see is how to show PO's who's due dates are > three
Select * from mytable
where myorderstatus = 'onorder'
This is my duedate format and what I want is any that past that date over 3 days
2014-08-11 00:00:00.000
I have a table with kind of data as below
PhNo Address DP AutoID Name
0123 RH 001 4577 J
0123 RH 004 4567 JD
0124 RH 010 4467 KK
0121 HN 007 4667 RK
012 SVN 009 4787 RKK
012 SVN 009 4787 RKR
011 SVN 009 4787 KKR
I need distinct phone numbers >1 for a address and if the same phNo has 2 different names I want to skip the second one.I want the output like below
PhNo Address DP AutoID Name
0123 RH 001 4577 J
0124 RH 010 4467 KK
0121 SVN 009 4787 RKK
012 SVN 009 4787 RKR
011 SVN 009 4787 KKR
I want to split the data every employeid wise based on fromdate and todate if totaldays>1.
sample output specified below ....but same output required for allthe empid's
create table attendence(EmployeeID nvarchar(20),[From] datetime,[To] datetime,TotalDays float)
insert into attendence values('1417','2015-11-02 22:48:49.450','2015-11-04 22:48:49.450',3)
insert into attendence values('1418','2015-11-04 22:48:49.450','2015-11-04 22:48:49.450',1)
insert into attendence values('1419','2015-11-03 22:48:49.450','2015-11-04 22:48:49.450',2)
insert into attendence values('1420','2015-11-04 22:48:49.450','2015-11-05 22:48:49.450',2)
insert into attendence values('1421','2015-11-01 22:48:49.450','2015-11-04 22:48:49.450',4)
OP
-------------------------
EmployeeID [From] [To] TotalDays
1417 2015-11-02 22:48:49.450 2015-11-02 22:48:49.450 3
1417 2015-11-03 22:48:49.450 2015-11-03 22:48:49.450 3
1417 2015-11-04 22:48:49.450 2015-11-04 22:48:49.450 3
For Below example when @x=1 to retrieve col>0 rows or all rows.
With out another if else blocks or Dynamic sql to solve only in where clause.
select 0 col into #x
union
select 1 col
union
select 2 col
declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END
--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter
I am using a table to store different size numbers for example:
Look at the picture below:
But I want this type of output look at the picture below:
How to sort the query to return greater than zero values to sort up and zeros go down.
I am using sql server 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
I have a string 'ACDIPFJZ'
In my table one of the column has data like
PFAG
ABCDEFHJMPUYZ
KML
JC
RPF
My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.
My output should be:
PFAG -- PFA (G Eliminated)
ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)
KML -- No data
JC -- JC
RPF -- PF (R Eliminated)
I’m getting ASCII characters in one column of my table. So I want to replace same column value in NON ASCII characters.
Note – values in column must be same