String Literal Problem
Aug 8, 2006
hi!
din't work. I need some thing in C# that can execute like this:
This works in SQl Query perfectly.
xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx"/Set package.Variables[User::connectst].Properties[Value];""Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;""'
How do we interpret in c# currently i have some thing like this which is not correct i need to fix this to work in C#
string conn = @"""Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;""""";
path = @"""D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx";
jobCommand = new SqlCommand(@"xp_cmdshell 'dtexec /f " + path + "" /Set \package.Variables[User::connectst].Properties[Value]; " + conn + ""'", cconn);
Thanks,
Ja
View 5 Replies
ADVERTISEMENT
Aug 7, 2006
when i execute in sql this works fine:
xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx" /Conn TahoeDB;"Provider=SQLNCLI.1;Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'
but when i execute in C# i get this value
string connect = @"TahoeDB;""Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;""";
xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn "TahoeDB;"Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"" ' -- this thorughs up error in sql
Option "Source=SE413695\AASQL2005;Initial" is not valid.This is basically after Data there is a space till Initial and then space catalog.
what should i do ? any help
thanks,
Jas
View 3 Replies
View Related
Aug 6, 2007
Hi,
I want to convert literal string to unicode before insert into the database. and after insertion i want to retrive this value from data base and convert back to literal string.
pls tell me how to incode and decode literal string to unicode and from unicode to literal string.
View 2 Replies
View Related
May 9, 2008
The proc below does two queries that are functionally identical. The only difference is that one LIKE 'foo%', and the other uses LIKE @searchText, where @searchText = 'foo%'.
But the first does an index seek, and the second does an index scan -- and it makes a big difference in performance. (Timing stats are below.)
How can I make the second query seek instead of scanning?
CREATE PROCEDURE test_like
@searchText nvarchar(64)
AS
-- Straight literal string search
SELECT companyId
FROM companies
WHERE searchbrand LIKE 'foo%'
-- With param
SELECT companyId
FROM companies
WHERE searchbrand LIKE @searchText
OPTION(OPTIMIZE FOR(@searchText = 'foo%'))
GO
EXEC test_like 'foo%'
-- Query 1:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
-- Query 2:
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 40 ms.
View 4 Replies
View Related
Apr 15, 2006
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 Related
Apr 14, 2015
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2))
Declare @S nvarchar(255)
Insert into @T
Values ('AR'), ('AT'), ('AW')
Set @S = 'Auto Repairs'
Select *
from @T T
where case @S when 'Auto Repairs' then 'AR'
when 'Auto Target' then 'AT'
when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
View 3 Replies
View Related
Apr 12, 2006
Howdy,Is it okay to use a literal value with the IN clause. E.g.SELECT somefield, anotherfield.....WHERE ...etc.AND 1234 IN (SELECT userid FROM tblUsers)I was told it wasn't valid, but I'm pretty sure it worked for me. Justseeking clarification.cheers,
View 2 Replies
View Related
Apr 1, 2008
Hi,
A really basic problem in psuedo code...
select * from mytable where email = 'test@go.com'
This throws back:
Unclosed quotation mark before the character string 'test@'.
Line 1: Incorrect syntax near 'test@'.
Unclosed quotation mark before the character string ''.
Because of the 'go'. Other addresses featuring 'go' suffer the same problem. How do I escape it?
Thanks,
Joe
View 4 Replies
View Related
Oct 5, 2007
I have a stored procedure that starts like this:
.
.
.
UPDATE Employees
set depth=0, hierarchy=NULL
UPDATE Employees
set depth=1, hierarchy=right(@MaxPadLength + CAST(Employees.Parent AS varchar(255)),@DisplayPadLength)
where Child = Parent
WHILE EXISTS
(
SELECT *
FROM Employees
WHERE Depth=0
)
.
.
.
I have many tables that have the same structure as the Employees table but have different names. I would like to pass the PS a paramater with the table name I want to process. My question is what is the correct syntax to use a parameter in place of the literals for the table name?
Thanks
View 4 Replies
View Related
Oct 25, 2007
I am trying to extract the month from a date field, and I am able to get the integer for the month, however, when I try to convert the integer to the literal, e.g. 3 to March, I am having issues.
I have a field called PROCES, which represents a date processed, which is numeric(8).... i.e. 20061231.
I am using:
SELECT datepart(mm,cast(convert(char(8),PROCES)as datetime))
which gives me the correct integer.
When I try:
SELECT datename(mm,datepart(mm,cast(convert(char(8),PROCES)as datetime)))
I get January for all records...?
Does anyone know why, and how to fix.
Thanks
View 1 Replies
View Related
Jul 23, 2005
What exactly is happening when a query is sent using the N in front ofthe string to be found?Under what conditions would someone use the N' in a query?I have been testing out some chinese text. I set up some fields ofnVarchar, nText and it works with an N. Without the N, it wont work.N also works with fields of varchar and text for english.Would this ever cause a problem to a query depending on how themachines regional settings are set? Why not just put N in all of thequeryies?If anyone has some ideas, I would be grateful for any and allinformation about the N.
View 1 Replies
View Related
Feb 24, 2008
Hello,
I am trying to insert some values retrieved from textboxes into an Access DB using ASP.NET. When I try to run this code I get an error reading "Too many characters in character literal". What does this mean? Also, how do I break new lines, does way this look ok?
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
string sQuery2 = INSERT INTO Member(FirstName, LastName, StreetAdress, PostalAdress, Telephone, Email),
VALUES ('strFirstName', 'strLastName', 'strStreetAddress', 'strPostalAddress', 'strTelephone', 'strEmail');
OleDbConnection oOleDbConnection = new OleDbConnection(connectionString);
oOleDbConnection.Open();
OleDbCommand command2 = new OleDbCommand(sQuery2, oOleDbConnection);
OleDbDataReader reader2 = command2.ExecuteNonQuery();
View 8 Replies
View Related
Mar 21, 2005
In MS Sql 2000 - is it possible to find the literal underscore character (_). Something like:
SELECT * FROM foo WHERE bar like 'a\_%'
where the query would return "a_abc", "a_cba", and "a_lksdfjlkdsjlksjdfl", but not "abc"?
I've tried:
SELECT * FROM foo WHERE bar like 'a\_%'
SELECT * FROM foo WHERE bar like ( 'a' + CHAR(95) + '%' )
Any other suggestions?
View 1 Replies
View Related
May 3, 2007
When a report is exported to Excel, the footer is set to a literal "page 1 of 1" even though it's specified as
="Page " + CStr(Globals!PageNumber) + " of " + CStr(Globals!TotalPages)
in the rldc report definition.
Hence, if you print from Excel every page has the 'page 1 of 1' footer. Not very useful.
Is there a way to either suppress the output of the footer (only when going to Excel cause you need it when exporting to PDF) -or- get it to generate the correct excel footer of 'page 1 of ?'????
Thanks,
View 1 Replies
View Related
Aug 14, 2015
I'm struggling with the syntax for qualifying an openquery's results with a where clause. I copied this from examples on the web but get the error  incorrect syntax near '32810'.
declare @sysid varchar(50) = '32810C534D01C920E7CB07EBC0A80122'
declare @sql varchar(500) =
'select * from OPENQUERY(WAREHOUSE,''select * from xxx.yyyy where sys_id = ''' + @sysid + ''''')'
exec(@sql)
selecting @sql it looks like
select * from OPENQUERY(WAREHOUSE,'select * from xxx.yyyy where sys_id = '32810C534D01C920E7CB07EBC0A80122'')
View 6 Replies
View Related
Apr 20, 2006
Hi,
I'm a newbee to SQL Server. I have a very simple question to you experts: How should I code a literal of the "datetime"-Datatype? For Example in the VALUES-clause of an SQL-statement. I have tested several "formats" ('20.04.2006 11:15:00' with an 4-digit year enclosed in single apostrophes) but all i earned is an exception!
Any help very appreciated!
Thanks in advance and best regards
Reiner
PS.: I'm using a german-localized database (thus the date-format dd.MM.yyyy).
View 9 Replies
View Related
Jan 18, 2008
Hello all,
From what I've read, SQL Server is supposed to do a phrase match when you do a full text search that contains quoted literals. So, for example, if I did a full text search on the phrase "time out" and I put it in quotes, it's supposed to search for the full phrase "time out" and not just look for rows that contain the words "time" or "out." However, this isn't working for me.
Here is the query that I'm using :
SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time out"') AS ft ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC
What's it's doing is this : it's returning a bunch of rows that have the words "time" or "out" in the column called hed. It's also returning rows that have the full phrase "time out", but it's giving those rows the same rank as rows that only contain the word "time." In this case, that rank is 180.
Is there anything else I should be doing in my query, or is there some configuration option I should have turned on?
Thanks.
View 1 Replies
View Related
May 6, 2008
The Web Service Task seems to support calling methods using parameters but not (as far as I can see) using the Document/Literal calling convention. Is this correct? Is this likely to change in the future?
Thanks
*** Campbell
View 5 Replies
View Related
Nov 9, 2015
I am working on SSRS report deployed on the sharepoint.
By default, it is displaying " Microsoft SQL Server Reporting Services " on the report
How can that be removed?
Secondly as I don't have the requirement to default the report parameters so I am getting
"Specify parameter values ............... button "
Any way to replace it with some other text etc...
View 2 Replies
View Related
Mar 10, 2015
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
View 3 Replies
View Related
Mar 20, 2014
We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.
View 9 Replies
View Related
Sep 8, 2015
I have following query which return me SP/Views and Functions script using:
select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function
I need its result as:
Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function
I used following
select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?
View 5 Replies
View Related
Nov 2, 2006
I am trying to insert a row into a table of Microsoft SQL Server 2000.
There are various columns.
[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-
StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset
at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?
Can anyone please tell me what's wrong?
View 6 Replies
View Related
May 22, 2007
Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships: IDMember Relationships .
3387 (2345, 2388,4567,....)
4567 (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
Best Regards
Pinsha
View 9 Replies
View Related
Mar 12, 2008
Hello, I am tring to add a string my database. Info is added, but it is the name of the string, not the data contained within. What am I doing wrong? The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string. All help is appreciated!
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
End Sub
End Class
View 3 Replies
View Related
Dec 17, 2014
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)
View 2 Replies
View Related
Jul 14, 2015
I have a text field which has entries of variable length of the form:
"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"
I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:
1234
123
12389
the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.
View 5 Replies
View Related
Sep 11, 2006
Hello All,
I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.
There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:
2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1
The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.
How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?
In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().
Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.
Thanks a bunch in advance,
Chutikorn
View 2 Replies
View Related
Sep 1, 2015
Is there way to search for the particular string and return the string after that searched string
SalesID
Rejection reason
21812
[code]....
The timeout period elapsed hence disqualified
View 3 Replies
View Related
Mar 21, 2007
I am trying to find a way to find a certian character in a string and then select everything after that character.
for example i would look for the position of the underscore and then need to return everthing after it so in this case
yes_no
i would return no
View 7 Replies
View Related
Jul 13, 2006
I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam
View 2 Replies
View Related
Jul 28, 2015
I have a string variable and following data.
Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'
when I assign values in @ServiceID Â in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.
View 8 Replies
View Related
Feb 13, 2006
We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
View 1 Replies
View Related
