Complex T-SQL To Parse Out 9900|5057|5002|5000|90010|5086|5003| Values
May 7, 2008
I have a [User] table with a field called Username and a field called AssignProject. The values in the AssignProject field look like this: 9900|5057|5002|5000|90010|5086|5003|5097|5038|5059|5043|5099|5096|90000|5084|9991|4180|5047|6064|9999|4160|5104|5110|5107|5109|5115|
Those values are foreign keys for values in another table called Project, where the primary key is ProjectID and the other field is ProjectName.
I am trying to write a report in a T-SQL query to tell me which projects are assigned to each user, but the query is not a typical join since the ids are in a string separated by the | character.
I have written a function as below to pass some value and get aNOTHER VALUE DURING THE RUNTIME ----------------------------------------------- Sub ExpiaryDate(ByVal Expr1 As String, ByVal Expr2 As String)
Dim strSQLDDL As String = "SELECT STATMENT "
Dim SQlCommand As New SQlCommand(strSQLDDL, mySqlConnection) SQlCommand.CommandType = CommandType.Text
Dim Colleague_CN As String = Int32.Parse(Expr1) Dim Course_ID As String = Int32.Parse(Expr2)
Dim parameterEventType_ID As SqlParameter = _ New SqlParameter("@Colleague_CN", SqlDbType.NVarChar, 50) parameterEventType_ID.Value = Colleague_CN SQlCommand.Parameters.Add(parameterEventType_ID)
Dim parameterCourse_ID As SqlParameter = _ New SqlParameter("@Course_ID", SqlDbType.NVarChar, 50) parameterCourse_ID.Value = Course_ID SQlCommand.Parameters.Add(parameterCourse_ID)
Dim myDataAdapter As SqlDataAdapter = New _ SqlDataAdapter(SQlCommand) myDataAdapter.Fill(DivisionDataSet, "ExpiaryDate") myDataAdapter.Update(DivisionDataSet, "ExpiaryDate") DivisionDataSet.AcceptChanges()
End Sub
Everything was fine as far as i am passing an integer. Now i need to pass a combination of numbers and letters( NVarChar)
HOW DO I GETAROUND THIS PROBLEM WHEN I USE THIS FUNCTION I GET ERRORS SOMETHING LIKE..... Input string was not in a correct format. INDICATES --Dim Colleague_CN As String = Int32.Parse(Expr1.ToString) AS AN ERROR
CAN ANYBODY PLEASE HELP HOW DO I PASS A NVarChar instead THANKS
I have a table where different types of values are stored in one field, but I need to seperate them into different fields based on a value in another field.
For (hypothetical) example:
There is an existing table with following info in three columns: userid record recordtag 1 joe 1 1 j 2 1 jr 3 2 bob 1 2 a 2 2 sr 3 where recordtag indicates (1 for first name, 2 for middle initial, 3 for suffix)
I need to query these records for a report so it the output is:
userID firstname middleinitial suffix 1 joe j jr 2 bob a sr
What's the most efficient approach to create a query that will give me desired results? I have managed to create a very complex query that derives tables for each column I want to create and queries off of that derived table for the 'record' value based on the 'recordtag' values for a given 'userid'. The query is extremely slow, so I know there's some better way out there to get the results I want. Any help would be greatly appreciated. Thanks.
I have a column with the following information in it. I need to split the column and parse the values. I have Parsed one single values from but, not multiple times.
Hi! I have table with complex index on 5 fields. One of them is string filed. I want to implement some sort of filtering, by setting SetRange() in my SQLCeCommand. But i need to fileter only by one string field and to get the values starting with the input string value. I tried to use such code: ... command.SetRange(DbRangeOptions.Prefix, new object[] {null, null, null, "Com", null}, null); resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable); .... But it doesn't work. As a result i've got an empty result set. Usage of simple index on one field and setting the correspondent range will solve problem, but i can't have such index due to project restrictions. Is there any way to set prefix range only by one value of complex index? If not, please, explain me how does Prefix Range works.
I have a table with 1.6 million records with a foreign key called "ums_id" (int). I would like to select the ums_id's in groups of 5000. is there a simpler way to break these id's up instead of
select count(*) from newsletter_opt_in where ums_id between 493525 and 510075 and opt_in_Id = 1 and opt_in_status = 'Y'
I would like to break up the whole 1.5 million in one sql statement if I can. Thanks, Mark
My bos wants me to create 5000 database on single sql server box. I was stuned by his stupility but could not stop it. What kind of box could hold these databases ? Each DB is about 6.5 MB starup with 4 MB for 6 months. Each DB will have 10 users. They might hit at same time. What kind of configurations for the server box ? What kind of maintainence nightmare ? How about recovry ? and so on. Any one got idea ? or any more question you would ask if you were me ? Thanks
We are performing a SQL 2000 to SQL 2005 upgrade on a Windows 2003 SP1 server.
We encountered error in the SQL 2005 upgrade with the following error :
===================================
Common Language Runtime detected an invalid program.
===================================
Common Language Runtime detected an invalid program. (System.Xml)
------------------------------ Program Location:
at System.Xml.Schema.SchemaInfo..ctor() at System.Xml.Schema.XmlSchemaSet..ctor(XmlNameTable nameTable) at System.Xml.XmlReaderSettings.get_Schemas() at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReportController.LoadAndValidateDataFile() at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAReport.ValidateDataFile() at Microsoft.SqlServer.UpgradeAdvisor.ReportViewer.UAIssueReport.Refresh() at Microsoft.SqlServer.UpgradeAdvisor.ReportPanel.OpenReport(String reportFile)
From the Event Viewer, under Application, we can see the following error :
I'm trying to build a association model in the Standard Edition based on an existing cube. I keep getting the error:
Error (Data mining): The 'Product Recommendations' mining model has 60385 attributes. This number of attributes exceeds the attribute limit of 5000 allowed by the current version of the algorithm associated with the mining model.
I created Cube Slice filters and those limit the Customer and Product dimensions (Product is Nested) to well under 5000. The error message also does not change. The number of attributes is equal to the number of rows in the Product dimension, but I expected the cube slice to reduce the number. I tested all the SQL used while it processes and with the MDXFilters the number of rows returned is well under 5000.
So, in short, the final questions is, is it possible to create a mining model in standard edition based on an existing cube where the nested dimension in the model has more than 5000 rows? Is there some other way to filter the query?
I guess my only choice on this if there isn't a way is to extract the data into relational table with only the rows I want to analyze....that's a huge pain and doesn't really make sense when the filters should limit the model size.
What is annoying on this is I can't find one reference anywhere on the microsoft site that this limit even exists within the product...
Hi, I want to delete from table when the record count is over 5000. That is, the table's record cannot over 5000 count. How? row_number() seem to be used...
I tried to port 10000 records using DTS. After porting of 9900 records I got an error and comes out without any result. But I want to keep the records which has been ported till the error occured. Plz help me.
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
I can't paste text (directly into table row via enterprise manager) into a varchar (5000) field, truncates after about 960 char. Length of string trying to paste is about 1400 characters including spaces. No special characters (one apostrophe). Error happens intermittently.
I only found http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=389835&SiteID=17 regarding this error message and it was of no help to me.
Background: 3rd party program that extracts users from a AD group and manage user creation i a MSSQL 2000 server. Also sends e-mails using the SMTP service. The funny thing is that the program manages everything correctly, but writes an error in the Event log as it exists (below). Job is scheduled with the SQL server agent and runs with a doamin user that has local admin rights. We have the same setup on a testserver where it runs without problems.
Windows server 2003, MDAC 2.8, SQL Server 2000 SP3, .NET framework 2 on both servers.
I'm not a developer, so I'm pretty lost why it behaves like this on one server and not the other. Checked Aspnet.config-file and they are the same. Found this
http://support.microsoft.com/?id=911816
that described that the exception handling was different between the .NET-versions, but since this works on one of the servers, it shouldn't be related, or could it? Tried changing the setting in method 2, but with the same behaviour. Any ideas?
I've got an INSERT that's selecting data from a linked server and attempting to push 10 million rows into the blank table. More or less, it looks like this:
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. There are no other active users. I ran it again and monitored the following DMO to watch the growth of locks for that spid:
SELECT request_session_id, COUNT (*) num_locks -- select * FROM sys.dm_tran_locks --where request_session_id = 77 GROUP BY request_session_id ORDER BY count (*) DESC
The number of locks started small and held for a while around 4-7 locks, but at about 5 minutes in the number of locks held by that spid grew dramatically to more than 8 million before finally erroring again with the same message. Researching, I can't figure out why it's not escalating from row locks to table locks at the appropriate threshold. The threshold in was set to 0 at first (Server Properties > Advanced > Parallelism > Locks). I set it to 5000, and it still didn't seem to work. Rewriting the INSERT to include a WITH (TABLOCK) allows it to finish successfully in testing. My problem is that it's coming out of an ETL with source code that I can't edit. I need to figure out how to force it to escalate to locking the entire table via table or server level settings.
A colleague suggested that installing service packs may take care of it (the client is running SQL Server 2008 R2 (RTM)), but I haven't found anything online to support that theory.
Hello,I have a table that has a name field with the following datajohn doejohn doe smithjohn d smithI need to separate the first, middle and last names into separatefields. ex. first varchar (20), middle varchar (20) and last varchar(20).I am testing the process that I have to follow by doing the followingcreate table names (name varchar (40),first varchar (20),middle varchar (20),last varchar (20))insert into names (name)values (john doe smith)I get an error message when I run the following query:update bset first = substring(name, 1, (charindex('', name)-1))The error message is:Server: Msg 536, Level 16, State 3, Line 1Invalid length parameter passed to the substring function.The statement has been terminated.Does anybody have any suggestions?TIAja
I am fairly new to SQL and am trying to write a function to parse the ip address into 4 sections. I have been searching through the forums to see if anyone has a posted example of parsing an ip address but could not find one.
I am wondering what would be the best method of doing this, or if anyone has an example.
I have a table, tblstudents that has the fields strfirstname and strlastname. The table was imported from MS Acess where they were storing the first name and the lastname in the same field. Sucks doesn't it? Anyway, I need to write a script to extract the first name from the lastname and insert it into the first name field which is blank. So far I figure I can use select into, but need to find a way to tell sql server to take the fart of the field after the comma (the first name) and to delete the comma becuase I won't need it anymore.
Help!
Select '%,' into tblclients as strfirstname from tblclients
I know this won't work, but I want to show you all I'm at least trying!
This script parses the @@VERSION global variable into individual columns.
I developed it because I wanted to be able gather standard info on all versions.
I know there are functions for a lot of this, but only starting with SQL 2000.
It seems to work with all versions of SQL Server from 7.0 through 2005.
I haven't tested with 6.5 and before or 2008, because I don't have either available.
Please report any problems you see.
Edit: 2007-7-31 1. Changed SQL_SERVER_MAJOR_VERSION to varchar(20) 2. Added code to create a view named V_SQL_SERVER_VERSION 3. Added four new columns to the view: SERVER_NAME, value from @@servername SQL_SERVER_MAJOR_VERSION_NUMBER, Example: 9 SQL_SERVER_VERSION_NUMBER, Example: 8.0020390000 WINDOWS_VERSION_NAME, Example: 'Windows 2000'
Edit: 2007-8-2 Changed SQL_SERVER_MAJOR_VERSION to varchar(40)
from ( select zz = stuff(yy,charindex(Char(10),yy),1,'#3#') from (
select yy = stuff(xx,charindex(Char(10),xx),1,'#2#') from ( select xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#') from ( select VERSION = @@VERSION ) a ) a1 ) a2 ) a3 ) a4 ) a4
Results:
SQL_SERVER_MAJOR_VERSION SQL_SERVER_VERSION SQL_SERVER_PLATFORM SQL_SERVER_EDITION WINDOWS_VERSION WINDOWS_BUILD WINDOWS_SERVICE_PACK ------------------------ -------------------- -------------------- ------------------------------ -------------------- -------------------- ------------------------------ 2000 8.00.2039 Intel X86 Standard Edition Windows NT 5.0 Build 2195 Service Pack 4
(1 row(s) affected)
drop view [dbo].[V_SQL_SERVER_VERSION] go create view [dbo].[V_SQL_SERVER_VERSION] as select SERVER_NAME = @@servername, SQL_SERVER_MAJOR_VERSION, SQL_SERVER_VERSION, SQL_SERVER_MAJOR_VERSION_NUMBER = convert(int,floor(convert(numeric(20,10),substring(SQL_SERVER_VERSION,1,4)))), SQL_SERVER_VERSION_NUMBER= convert(numeric(20,10),( convert(numeric(20,10),substring(SQL_SERVER_VERSION,1,4))*1000000+ convert(numeric(20,10),substring(SQL_SERVER_VERSION,6,30)))/1000000), SQL_SERVER_PLATFORM, SQL_SERVER_EDITION, WINDOWS_VERSION_NAME = convert(varchar(20), case when WINDOWS_VERSION = 'Windows NT 5.0' then 'Windows 2000' when WINDOWS_VERSION = 'Windows NT 5.1' then 'Windows XP' when WINDOWS_VERSION = 'Windows NT 5.2' then 'Windows 2003' else WINDOWS_VERSION end), WINDOWS_VERSION, WINDOWS_BUILD, WINDOWS_SERVICE_PACK from ( select SQL_SERVER_MAJOR_VERSION = convert(varchar(40),substring(L1,1,L1_BREAK_1-1)), SQL_SERVER_VERSION = convert(varchar(20),substring(L1,L1_BREAK_1+3,L1_BREAK_2-(L1_BREAK_1+3))), SQL_SERVER_PLATFORM = convert(varchar(20),substring(L1,L1_BREAK_2+2,L1_BREAK_3-(L1_BREAK_2+2))), SQL_SERVER_EDITION = convert(varchar(30),substring(L4,1,L4_BREAK_1-1)), WINDOWS_VERSION = convert(varchar(20),substring(L4,L4_BREAK_1+4,L4_BREAK_2-(L4_BREAK_1+4))), WINDOWS_BUILD = convert(varchar(20),substring(L4,L4_BREAK_2+2,L4_BREAK_3-(L4_BREAK_2+2))), WINDOWS_SERVICE_PACK = convert(varchar(30),substring(L4,L4_BREAK_3+2,L4_BREAK_4-(L4_BREAK_3+2))), VERSION = VERSION from ( select VERSION, L1_BREAK_1 = charindex(' - ',L1), L1_BREAK_2 = charindex(' (',L1), L1_BREAK_3 = charindex(')',L1), L4_BREAK_1 = charindex(' on Windows',L4), L4_BREAK_2 = charindex(' (',L4), L4_BREAK_3 = charindex(': ',L4), L4_BREAK_4 = charindex(')',L4), L1, L4 from ( select VERSION, L1 = convert(varchar(100), rtrim(ltrim(replace(substring(zz,1,charindex('#1#',zz)-1),'Microsoft SQL Server',''))) ) , L4 = rtrim(ltrim(substring(zz,charindex('#3#',zz)+4,100)))
from ( select VERSION, zz = stuff(yy,charindex(Char(10),yy),1,'#3#') from (
select VERSION, yy = stuff(xx,charindex(Char(10),xx),1,'#2#') from ( select VERSION, xx =stuff(VERSION ,charindex(Char(10),VERSION),1,'#1#') from ( select VERSION = @@version ) a ) a1 ) a2 ) a3 ) a4 ) a4 ) a5 go grant select on [dbo].[V_SQL_SERVER_VERSION] to public go
Hi there,I am re-organizing the database. We used to have field 'names' in ourtable for our first name and last name. However, I want to have thosenames in different field.FYI, I have two different tables in different databases. The Adatabase contains A table with 'names" field. The B database containsB table with 'fname' and 'lname' I want to copy data A table to Btable.How can I parse names field into first name and last name fields?Here are some examples which are in the database.(id names01 John Doe02 John & Jane Doe03 Mr & Mrs Doe) something like this. It might contain '&' and two names. If thereare two names, then use first name.Thanks guys,
In my stored procedure, I want to parse @ArrayOfDays into @d1 through@d5.@ArrayOfDays is a varchar input parameter containing,for example, "1.7.21.25.60." - five elements.Most active vars:@i - loop counter@char - current char in string@tempVal - contains the current element as it is being built@tempValExecString - contains SELECT stmt for EXEC()I'm using EXEC() to execute a dynamically built SELECT.The error I get when calling from vb.net is:Must declare the variable '@tempVal'.Two manual traces indicate the logic is ok.I suspect my assignment statement for @tempValExecString.Any help would be appreciated. - BobC----------------------------------------------------------DECLARE@d1 varchar(3),@d2 varchar(3),@d3 varchar(3),@d4 varchar(3),@d5 varchar(3),@i int,@char char(1),@tempVal varchar(3),@tempValExecString varchar(30)SELECT @tempVal = ''SELECT @i = 1WHILE @i < LEN(@ArrayOfDays)BEGINSELECT @char = SUBSTRING(@ArrayOfDays, @i, 1)WHILE @char <'.'BEGINSELECT @tempVal = @tempVal + @charSELECT @char = SUBSTRING(@ArrayOfDays, @i+1, 1)IF @char = '.'BEGIN/* the following should produce "SELECT @d1 = 1" when it reads thefirst period(.) */SELECT @tempValExecString = 'SELECT @d' + LTRIM(RTRIM(STR(@i))) + '= @tempVal'EXEC(@tempValExecString)SELECT @tempVal = ''SELECT @i = @i + 1ENDSELECT @i = @i + 1ENDEND----------------------------------------------------------
Is there a way to parse a string to int? this is my example code. quote_id is a string i want to be treated as an int here.
Code Snippet ALTER PROCEDURE [dbo].[GetNextQuoteID] AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT MAX(quote_id)+1 as id From Quote END
Does anyone come across this error message whenever u try to parse ANY SQL statements in Management Studio 2005 (even parsing SELECT GETDATE() fails)...
>>>
.Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. >>>
Actually, i need to trace out changes in my database.[either by insertion/updation/deletion of data or adding/modifiying object.] within my application.
I study SQL Profile. But it is limited for its run and also resource hunger or extra burden on server in case of large size database and busy server.
So please guide me. Also suggest be its appropriate solution.
i am executing an sql statement in sql server 2005 express edition. It says it is unable to parse the query then automatically re-arranges the sql text and then it is able to parse it and return me the result. my query is this: What am I doing wrong? IF NOT EXISTS (SELECT MemberID, HotelID FROM tblHotels_Reviews WHERE MemberID = 3 AND HotelID = 3) BEGIN INSERT INTO tblHotels_Reviews(MemberID, HotelID, ReviewTitle, StayMonth, StayYear, Satisfaction, PricePerNight, ValueForMoney, CheckIn, FoodDining, BusinessFacilities, SafetySecurity, Rooms, StaffService, HotelFacilities, CheckOut, SuitableFor, Atmosphere, DoYouRecommend, Review, Anonymous, HasPhotos, ReviewDate, ReviewLanguage, PublishStatus)VALUES (6, 3, 'hello', 'January', 2002, 5, 234, 5, 5, 5, 5, 5, 5, 5, 5, 5, 'Singles,Couples', 0, 1, 'helloo', 0, 0, '06/04/2006', 'EN', 0) SELECT 1 AS RESULT END ELSE BEGIN SELECT 0 AS RESULT END
I create a Execute SQL Task and Load a stored procedure into the "SQL Statement" box. This stored procedure has many 'go' words and many commented lines either with '--' mark or '/*' or '*/' mark.
When Parse Query, get Package Error "Deferred prepare could not be completed. Statements could not be prepared". But I could run the same proc under the isql/w with no problem. How could I deal with this error? Though it looks the execution of the package still run successful. The Parsing just create uncertainty.
Has anyone tried to parse a column containing sql syntax to obtain the actual column names used in the syntax field. So if the field had acctno =1001 then it would return acctno.
The data looks like the following --------------------------- | PBP 20070420 2:26pm | ---------------------------
Now the data in this field is not uniform it can be blank, a sentence or have a different pre fix, instead of PBP, but the date will be YYYYMMDD when it is supplied.
I need to find all the dates that are within the last 10 months. How do I perform this task?
If I use and Execute SQL Task like this ... ------------------------------------ DECLARE @VAR SET @VAR = ?
EXEC sp_Test @VAR ------------------------------------ and then click parse query I get an error "Syntax error or Access violation"
However; if I use it this way ------------------------------------ EXEC sp_Test ? ------------------------------------ it works.
Now here is the odd thing. If I put in a DECLARE statement and a global parameter "?" I get the syntax error.
But .... If I use a disconnected edit and use DECLARE and "?" then task will run properly. So the problem seems to be with the query parser.
The problem is I don't have the time to edit everything in a disconnected manner. Is there a way or update that will allow me to use DECLARE and "?". I have tried many hotfixes but none have worked.
