I am somewhat new to the world of programming with SQL Server and was wondering if this could be done. Well I know it can be done but was wondering how it might be done.
I have a DTS package created to import a table from and AS400 server. What I need to do is take one field and parse that field into 5 different values for 5 new fields.
Here is what I know needs to be done but not sure how to put into the procedure.
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.
This should be an easy enough answer to find if I just knew what to search on!
I am building a query within my stored procedure based on what parameters are passed in. For example, suppose I have a table with first name and last name. I can call the sp with either first name or last name or both, so I build a query accordingly that says: select * from tblNames where FirstName = 'Hannah' or select * from tblNames where LastName = 'Montana' or select * from tblNames where FirstName = 'Hannah' and LastName='Montana'
My problem is putting the single quotes around the variable value. SELECT @whereClause = @whereClause + ' AND tblNames.LastName=' @LastName-with-singlequotes-around-it
I need to write a storedproc that receives the name of a table (as a string) and inside the stored proc uses select count(*) from <tablename>. The problem is the passed in tablename is a string so it can't be used in the select statement. Any ideas how I can do what I want?
Hello, I have a number of multi-select parameters which I would like to send to a stored procedure within the dataset for use in the stored procedure's IN() statement which in turn is used to filter on or out particular rowsets.
I considered using a hidden string parameter set = " ' " + join(parameter.value, ',') + " ' " so that the hidden parameter would then contain a comma delimiated string of the values selected, which would then be sent on to the stored proc and used in the WHERE clause of one of the queries internal to the stored proc. But before I start dedicating time to do this I wanted to inquire if anyone here with far more expertise could think of a faster or less system heavy method of creating a single string of comma delimited parameter selections?
I am using the follwing query for extracting the country name and city in a COLUMN [Destination Name] in Destinations table. The Data in the table looks like:
CANADA - Toronto United States- ARIZONA France Argentina United States (USA)- ARIZONA ........ ........
The folowing query is producing the required results upto soem extent but without using -1 in subtracting the one value of CHARINDEX. The error is:
Server: Msg 536, Level 16, State 4, Line 1 Invalid length parameter passed to the substring function. The statement has been terminated.
QUERY ----- select
Left( Destinations.[Destination Name], charindex("-", Destinations.[Destination Name])-1 ) as test into temp2 from destinations
CAN ANYBODY HELP me in extracting the city an dcoutry name. I also want to delete the name in () like (USA).
I was given a script that was supposed to take a name field that was separated by commas and normalize it into last, first and middle name. My data looks like below in one fieldname called longname
I am trying to break the string that looks like this
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
into three separate strings to look like this
col1 col2 col3 2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.
I am trying to break the string that looks like this
2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
into three separate strings to look like this
col1 col2 col3 2007-05-06 07:36:21.28 server Copyright (C) 1988-2002 Microsoft Corporation. 2007-05-06 07:36:21.28 server All rights reserved. 2007-05-06 07:36:21.28 server Server Process ID is 292.
I was able to separate the above string into two columns, but can't figure out how to put the rest of the string into the third column.
How i m Manipulate String , I have (ABC,XYZ,EFG) this string know i want to break this string 1 by 1 and modify and then rearrange in same form ang Get
Hi I have a field which is a file path like 'C:avde8393948.txt'
I want to separate them into folder and filename now the filename is always the same length, so i can use RIGHT to get the filename, but i prefer a method that from the right detects the 1st occurance of and everything after is the filename,
We have some rows that we need to do some tricky string manipulationon.We have a UserID column which has userid entries in the formatfirstname.lastname and i need to change each entry tolastname.firstnameCan this be done by some script?Thanks so much for your help.Sid
I am having problem in string manipulation in SSIS - Derived Column Transformation. I am trying to extract the OU names from Active Directory objects into a SQL table.
Assume that a distinguish name (DN) of an object as below: CN=John, Doe,OU=Users,OU=SubOU,OU=ParentOU,DC=domain,DC=company,DC=com
How can I manipulate the above string so that I get: ParentOU/SubOU/Users
I have a sql query in which I need to isolate part of the columm valueand return only that isolated portion. I can only do this within theselect statement, and cannot add a function or anything like that. Iwould also like to keep this query within sql (I don't want to do thisin my programming environment)The string value would normally look like "segment1-segment2-segment3".I need to isolate segment2, but I have to be able to account forsituations in which either one or both dashes are missing (in whichcase returning "" or the whole string is OK. The best I have been ableto do reliably is to get "segment2-segment3".Anybody want to take a stab?
I'm trying to look up customer records by e-mail domain by using a text box on a Web form. So if I want to look for all my customers that have an aol e-mail domain, I would type aol.com in the text box and the sub routine would know to count 7 characters from the right and through those characters into maybe a parameter query. I'm having problems passing this in. I can count the characters properly by using: dim strText = MyTextBox.Textdim intLength = strText.Length but having problems starting here...... MyCommand.SelectCommand.Parameters("@email").Value = MyTextBox.Text .............. but how would I ultimately feed this into my sql satement? Select * from Customers Where email = right(@email,intLength) Help appreciated. Frank
I am building a string (basically XML) on the fly in stored procedure (SQL 7.0). I am using one local variable @str_xml varchar(8000) to build this string. Now my problem is I can store maximum upto 8000 characters in "varchar type". And I can't use text field as SQL Server doesn't allow "text type" to be used as local variable. I am thinking to use 2 or 3 local varchar variables and then return them separately to front end and will do contatination at the front end. But I think it would be ugly way to do as I have to check every time in stored procedure the length of string.
Any suggestions to do this in some other way would be greatly helpful to me.
I'm hoping someone can help! Im using sql2000, and I am attempting to capitalize every 1st letter of a word in a column.
For Example: "GOLF IS FUN,BOWLING IS GREAT"
What Id like to get as my results:
"Golf is fun, Bowling is great"
Trying to figure out the syntax to get the character after the comma to have a space then capital "B" Thought I could use a charindex but just cant seem to get it.
Please help me with the sql script to manipulate the string data:
I need to add <Text> at the beginning and end of the string. I also need to add <option> before the first and after last occurence of the <Option> string. The original string
FROMTable1 INNER JOIN Table2 ON Table1.UID = Table2.UID
WHERE(SET @Temp = Table2.Column1
--remove all 0's SET @k = patindex('%[^0 ]%', @Temp) WHILE @k> 0 BEGIN SET @Temp = replace(@Temp, substring(@Temp, @k, 1), '') SET @k= patindex('%[^0 ]%', @Temp) END SELECT @Temp ) = ''
But of course this isn't working so much. I am wondering if I have to use a cursor?
I have data in a trace file, and I need to extract some info such as phone number.The problem is the phone number could be varying lengths, and various positions in the row.
For example:
@City='New York', @Phone='2035551212' (10 characters, no dashes) or @City='San Francisco', @Phone='918-555-1212' (12 characters, with dashes) or @City+'Berlin', @Phone='55-123456-7890' (14 characters, with dashes)
I can use CHARINDEX to search & find @Phone=' so I know where the phone number starts, but stuck on a programatic way to find the data between the quotes since it can vary.
I'm used to DTS but new to SSIS. What's a good reference/tutorial that deals with transforming columns of data (from a flat file) from one format to another when uploading into SQL2005? Typically columns of data have "" around the values and spaces that I want to remove.
Presumably in SSIS I need the following:
A Data flow task containing:
Flat file source Derived or Copy Column? OLE DB Destination
I am having a field 'Flight Route that holds hyphen delimited character sequences.
E.g. : ABC-BCD-DEF-EFG.
My requirement is like this:
If the flight route is:
ABC-BCD-BCD-DEF make it ABC-BCD-DEF ABC-ABC-BCD-DEF make it ABC-BCD-DEF
i.e. 'whenever a sequence repeats,only one appearance of that sequence should be displayed.The field Flight Route has to be updated with this replaced string.
The recipe preparation instructions are stored in a table by RecipeID. Â The prep instructions are in a single VARCHAR(MAX) column and look something like this: Â
1.  Boil Water 2. Add noodles 3. Add cheese sauce 4. Stir well
Now they want this single VARCHAR(Max) column broken into 2 columns - Step and Prep Instruction like this: Boil WaterAdd noodlesAdd cheese sauceStir well.I figure I can use the appearance of a number followed by a period and a space to determine the existence of a new row. Â How would I accomplish this in T-SQL?
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code. So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message: Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages. (1 row(s) affected) (1 row(s) affected) I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution? Also, Is there a way to trace into a stored procedure through Query Analyzer? -------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised: SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.
Error from SQL Debug: --- Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65 [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type. ---
Error from page execution: --- Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.
Source Error:
Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip") Line 78: Line 79: cmd.ExecuteNonQuery() ---
Below is what I currently have for my stored procedure and the pertinent code from the page itself.
