Split Command In String
hi all, i am still a newbie in sql
i got a problem with my query
for example:
-------------------------------
DECLARE @LongString VARCHAR(10)
@LongString = "Hello-Word"
------------------------
i want to change the LongString value by removing the "-" however i i need to identify if the @longString got "-" and delete it if it found how do i do that in sql
Thanks a lot guys
arifliminto86
View Complete Forum Thread with Replies
Related Forum Messages:
Hello See This Command For Name Split How To Get MIDDLE NAME
Hello The following command is for Name Split as First Name and Second Name. Please let me know based on the following only how to get Middle name. SELECT name, CASE WHEN CHARINDEX(',',Name) > 0 THEN SUBSTRING(Name,CHARINDEX(',',Name)+1,LEN(Name)-CHARINDEX(',',Name)) ELSE '' END AS Fname, CASE WHEN CHARINDEX(',',Name) > 0 THEN LEFT(Name,CHARINDEX(',',Name)-1) ELSE Name END AS Lname FROM YourTable
View Replies !
How To Split A String Using Sql
I have 5 dynamic rows each row consisting of 5 checkboxes & 5 dropdowns.I am concatenating the values of each controls in a row using a wildcard charater "~" and each row i am concatenating using "|".The complete string is then assigned to one hidden field and passed as sql parameter to the backend. Please help in writing the split function to get the values of each checkboxes and dropdowns from the string in order to save them in separate columns. Thanks
View Replies !
Split A String
Hi I have this string which might have a hyphen in it "-" What I want to do is if I get a hyphen then take all characters after hyphen else take only all the characters starting from the 5th position of the string How can this be achieved?
View Replies !
Split A String In Sql
Hi I need a stored procedure in SQL that will split a comma separated variable passed to it select a name for each value and return a recordset. Any pointers greatfully received. First attempt is dreadfully slow as I am opening recordsets each time Function func_getFood() Dim rsfoodsql Dim foodoutput for x=1 to ubound(masterfoodarray)-2 set rsfoodsql= objconn.execute ("select foodname from tbl"&language&"food where foodID='"& masterfoodarray(x) &"'") if not rsfoodsql.eof then foodoutput=rsfoodsql("foodname") if not foodoutput="" then response.write foodoutput&"<BR>" end if end if next End Function Hope someone can help, cheers
View Replies !
How To Split The String
I am having City, State i need to split these two string with comma separated and to search based on the city and state how to write the select query any one know help me Regards, Prabu R
View Replies !
How To Split A Delimeted String...
hi, i have a simple question, i have a sql server table named "Restaurant" and a field "Cuisines", the values in the field Cuisines contains delimeted string. (e.g. "C1,C2,C3...C10" ,maximum to 10 items or less) now, i want to split the values from Cuisines ("C1,C2,C3...C10") and passed all of them to a SP parameter. e.g @item0 nvarchar = "C1",@item1 nvarchar = "C2",@item2 nvarchar = "C3",@item3 nvarchar = "C4",@item4 nvarchar = "C5",@item5 nvarchar = "C6",@item6 nvarchar = "C7",@item7 nvarchar = "C8",@item8 nvarchar = "C9",@item9 nvarchar = "C10" if the field "Cuisines" contains less than 10 items, the value of the remaining parameters is null. any response will be appreciated. thanks.
View Replies !
SQL String Split With | Pipe
I've got a dilemma here. I'm currently reading records into EasyListBox (ELB) dropdownlist. The criteria in the dropdownlist is based on the select from another ELB dropdownlist. The problem is that the data contains rows with pipes that serve as separters. For example, one of the options that comes back now might be 123 | 456 | 789. I need to be able to make each of them its own row in the dropdownlist. Can anyone advice? Most records don't have any pipes and come throught fine, but some dont. My code below: Dim sAT As String = Replace(elbAttribute.SelectedValue, "'", "''") Dim adapter As New SQLDataAdapter("Select Feature_Type As Attribute_Name, FEATURE_TEXT_VALUE as Attribute_Value, (select distinct FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID from FILTER_PROFILES, FILTER_FAMILY_FEATURE_EXCLUDED, FAMILY_ALLOWED_FEATURE_TYPES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FILTER_PROFILE_ID And FAMILY_ALLOWED_FEATURE_TYPES.FAMILY_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FAMILY_ID And FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID = SHARED_FEATURE_TYPES.FEATURE_TYPE_ID And (SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "')) As ExclusionFlag From SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES, PRODUCT_FEATURE_VALUES Where PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = SHARED_FEATURE_VALUES.Feature_Value_ID And SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "' UNION Select distinct Column_Name As Attribute_Name, SMALL_TEXT_VALUE As Attribute_Value, (select FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID from FILTER_PROFILES, FILTER_ATTRIBUTES_EXCLUDED where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_ATTRIBUTES_EXCLUDED.FILTER_PROFILE_ID And FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID) As ExclusionFlag From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES, PRODUCTS Where PRODUCTS.PRODUCT_ID = EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID And not SMALL_TEXT_VALUE is null And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID > 1499 And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID And Column_Name = '" & sAT & "'", myConnection) Dim dt As New DataTable() adapter.Fill(dt) elbValue.DataSource = dt elbValue.DataBind()
View Replies !
Sql Charindex Split String
HelloI am quite hopeless and of course a newbe.The situation: Sql2k / queryI would like it ot break down the following string:2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094Whereby:Year = '2004 Inventory'plant= 'Ex.Plant Farm1'cut = '1st Cut'grade = 'Premium'lot# = '0094'It is always seperate by ':', but it can be 5 fields or 4 or 3 and sooncode to create the view:CREATE VIEW dbo.TESTASSELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))[color=blue]> 0 THEN LTRIM(SUBSTRING(FullName,[/color]CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',FullName) - 1)) ELSE NULL END AS Plant, CASEWHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THENLTRIM(SUBSTRING(FullName,CHARINDEX(':', FullName + ':', CHARINDEX(':',FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',Fullname) + 1) - CHARINDEX(':',FullName+':') - 1)) ELSE NULL END AS [Cut]FROM dbo.ItemInventoryCan anyone help me with this? I am stuck half the way and get for cutthe rest of the string: '1st Cut:Premium:0094'Thanks!
View Replies !
Split The String Into Columns
I have a table called products with the values like ProductId ProductName 10 A 20 D,E,F,G 30 B,C 40 H,I,J I need to display each productid's with ProductId ProductName 10 A 20 D 20 E 20 F 20 G 30 B 30 C 40 H 40 I 40 J I will be appreciated if you can send me the code. Thanks, Mears
View Replies !
Split String Using Delimiter
Hi, I get a string whihc looks like 'Q306/Q406 Version1/Current/Q108 Version2' I need to split the above string and get each of those values... ' / ' delimiter Can some one please help on this. Thanks
View Replies !
SPLIT A String Inside SSIS/T-SQL Code?
I have a RPC which gives me multiple - single record rows like Robert|K|Half|TX|1123823|1423904 -- This is one such record that i get in 1 Column Now I need to split the above record into 6 Fields and populate in my local DB I know i could use any .NET Language and accomplish this. But Iam limited to using SSIS/T-SQL Proc's Any ideas / directions?
View Replies !
Using Variable String In Sql Command
Using SQL Server 7.0, I am trying to use a variable string called from a form to perform a simple query. Here's what it looks like: [after submitting a form on the previous page with a drop down list box titled "fiscal"] dim strYear strYear = Request.Form("fiscal") sql = "SELECT * FROM VENDOR WHERE STATUS = 'P' AND '"& strYear &"';" [where strYear is the following: PYMNT_DT > ''12/31/98''] Records exist that meet this criteria. If the PYMNT_DT > '12/31/98' is hardcoded into the sql statement, 12 records are returned. However, when the variable string is used instead, no records are found. Any ideas on how this problem might be fixed? Thanks in advance. Sincerely, Chad Massie
View Replies !
Conversion Error With SQL Command String
Hi, When I use the folowing code, the first (green) line is executed well. The composed SQL statement (red) gives a conversion error. declare @vchSQL_Cmd varchar(max), @intMaxNr int, @vchTabelNaam varchar(255), @vchKolomNaam varchar(255) set @vchTabelNaam = 'CI_Lei_Toestand_DetailLijnen' set @vchKolomNaam = 'CameraInspectieID' select @intMaxNr = max(CI_Lei_Toestand_DetailLijnen.CameraInspectieID) from CI_Lei_Toestand_DetailLijnen select @intMaxNr set @vchSQL_Cmd = 'select @intMaxNr = max(' + @vchTabelNaam + '.' + @vchKolomNaam + ') from ' + @vchTabelNaam select @vchSQL_Cmd exec (@vchSQL_Cmd) select @intMaxNr Thx
View Replies !
Can You Change Command Timeout Via The Connection String?
I know that connecttimeout and commandtimeout are separate entities. Is it possible to change the default command timeout value by changing the connection string? I need to increase the command timeout and want to know if I can do it without changing my code and rebuilding my ASP.NET 1.1 web app. Thanks in advance. -- ZLA
View Replies !
INSERT INTO Command (two Columns) With One Fixed Value, One String With Several Values
I have a string with values value1,value2, value3, value(n) which I would like to append to my table. In the second column of my table I have a parameter which stays the same, so I end up with (if the parameter value is "123456") Column 1 | Column 2 123456 | Value1 123456 | Value2 123456 | Value3 I would like to set up a single SQL statement which will process this regardless of the number of values (therefore rows) desired. Something like: INSERT INTO dbo_tblUserLevelApplicationRequests ( Column1, Column2) select EmployeeNumberParam as EmployeeNumber, ((stringofvalues) as valuestring) Is it possible to do this with a single SQL statement?
View Replies !
How Do I Use A @variable To Hold On Value Return From An Exec ('string Command') Statement.
How do I use a @variable to hold on value return from an exec ('string command') statement. Example for: declare @OldID int declare @cmd varchar(255) declare @db varchar(25) declare @OldOwner varchar(25) set @db = 'DBNAME' set @OldOwner = 'USERNAME' select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" exec (@cmd) How can I use @OldID to hold on UID return from this statement. When I try use: select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'" then I'm getting a error message: @OldID not declare. Thanks.
View Replies !
The Command Line Parameters Are Invalid For Source Connection String When Running As A SQL Job
Hi All, I created a SSIS package to import an excel spreadsheet into my data warehouse. When I run the package it runs fine. When I created a SQL Job to run the package I get the following error: Option "Source=D:HelpDeskImportBook2.xls;Extended" is not valid. The command line parameters are invalid. Now if I look at my source connection string in the job it looks like the following: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:HelpDeskImportBook2.xls;Extended Properties=HDR=YES;EXCEL 8.0;HDR=YES"; Does anyone know why it appears that my connection string looks like it is being cut off and does anyone know how I can correct the problem? Thanks, Scott
View Replies !
SIMPLE Command To Convert String To Number? Not CAST Or CONVERT.
Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks
View Replies !
Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
View Replies !
Using A Variable In SSIS - Error - &&"Command Text Was Not Set For The Command Object.&&".
Hi All, i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables, enterName - String packageLevel (will store the name I enter) myVar - String packageLevel. (to store the query) I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )" Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error. Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.". Can Someone guide me whr am going wrong? myVar variable, i have set the ExecuteAsExpression Property to true too. Please let me know where am going wrong? Thanks in advance.
View Replies !
&&"Command Text Was Not Set For The Command Object&&" Error
Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage: Description: An OLE DB Error has occured. Error code: 0x80040E0C. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object". ......... Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN". This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below: private bool BuildPackage() { // Create the package object oPackage = new Package(); // Create connections for the Foxpro and SQL Server data Connections oPkgConns = oPackage.Connections; // Foxpro Connection ConnectionManager oFoxConn = oPkgConns.Add("OLEDB"); oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere oFoxConn.Name = "SourceConnectionOLEDB"; oFoxConn.Description = "OLEDB Connection For Foxpro Database"; // SQL Server Connection ConnectionManager oSQLConn = oPkgConns.Add("OLEDB"); oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere oSQLConn.Name = "DestinationConnectionOLEDB"; oSQLConn.Description = "OLEDB Connection For SQL Server Database"; // Add Prepare SQL Task Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask"); TaskHost thSQLTask = exSQLTask as TaskHost; thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn"); thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true); thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None); thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql"); thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection); thSQLTask.FailPackageOnFailure = true; // Add Data Flow Tasks. Create a separate task for each table. // Get a list of tables from the source folder arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF"); for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++) { // Get the name of the file from the array sDataFile = Path.GetFileName(arFiles[iCount].ToString()); sDataFile = sDataFile.Substring(0, sDataFile.Length - 4); oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe; oDataFlow.AutoGenerateIDForNewObjects = true; // Create the source component IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New(); oSource.Name = (sDataFile + "Src"); oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1"; // Get the design time instance of the component and initialize the component CManagedComponentWrapper srcDesignTime = oSource.Instantiate(); srcDesignTime.ProvideComponentProperties(); // Add the connection manager if (oSource.RuntimeConnectionCollection.Count > 0) { oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID; oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn); } // Set Custom Properties srcDesignTime.SetComponentProperty("AccessMode", 0); srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true); srcDesignTime.SetComponentProperty("OpenRowset", sDataFile); // Re-initialize metadata srcDesignTime.AcquireConnections(null); srcDesignTime.ReinitializeMetaData(); srcDesignTime.ReleaseConnections(); // Create Destination component IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New(); oDestination.Name = (sDataFile + "Dest"); oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1"; // Get the design time instance of the component and initialize the component CManagedComponentWrapper destDesignTime = oDestination.Instantiate(); destDesignTime.ProvideComponentProperties(); // Add the connection manager if (oDestination.RuntimeConnectionCollection.Count > 0) { oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID; oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn); } // Set custom properties destDesignTime.SetComponentProperty("AccessMode", 2); destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false); destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]"); // Create the path to link the source and destination components of the dataflow IDTSPath90 dfPath = oDataFlow.PathCollection.New(); dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]); // Iterate through the inputs of the component. foreach (IDTSInput90 input in oDestination.InputCollection) { // Get the virtual input column collection IDTSVirtualInput90 vInput = input.GetVirtualInput(); // Iterate through the column collection foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection) { // Call the SetUsageType method of the design time instance of the component. destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE); } //Map external metadata to the inputcolumn foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection) { IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New(); externalColumn.Name = inputColumn.Name; externalColumn.Precision = inputColumn.Precision; externalColumn.Length = inputColumn.Length; externalColumn.DataType = inputColumn.DataType; externalColumn.Scale = inputColumn.Scale; // Map the external column to the input column. inputColumn.ExternalMetadataColumnID = externalColumn.ID; } } } // Add precedence constraints to the package executables PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]); pcTasks.Value = DTSExecResult.Success; for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++) { pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]); pcTasks.Value = DTSExecResult.Success; } // Validate the package DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null); // Check if the package was successfully executed if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure)) { string sErrorMessage = ""; foreach (DtsError pkgError in oPackage.Errors) { sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + ""; sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + ""; sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + ""; sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + ""; sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + ""; sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + ""; sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + ""; sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode; } MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } // return a successful result return true; }
View Replies !
String Or Binary Data Would Be Truncated. (only For 1700 Character String?)
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 Replies !
Name Split
Quick question. I've got a CHAR (70) field called NAME that has a first and last name separated by a space. I want to split it into two fields FIRST and LAST -- with all the characters to the left of the space a first name and all the characters to the right of the space as last name. I couldn't find a string function that would let me do this simply (it may be right in front of me and I missed it). Thanks in advance. Ray
View Replies !
Name Split
I need to split a column of Full Names into First name and Last name columns. Has someone come across this before and if so can you give me an idea of how to overcome it?
View Replies !
Split Value In Sql
Hi to all I have one problem regarding sp and pass value in sp I am gating a value like Abc,Def,Ghi, Now I want to split the whole pass value by “,� And fire one for loop to store value in database This things is done in asp.net web form but I want to do all process in sp So please guide me how I am write sp . The purpose is pass value one time so connection time is decrees and give fast perforce
View Replies !
To Split Or Not To Split
I have a database with a "large" table containing date based information Basically they're reservations. I've thought about creating a new table and adding any records from past years to this table. For the most part only current reservation need to be searchable, but in some circumstances it would be useful to be able to search through the archive too. so, my questions!!! Is 8,000 or so rows of data "large" and unwieldly in SQL terms? Would splitting this data into 2 tables - one small table for current and future reservations and one larger archive table then using a UNION SELECT query to make archive information seachable be a significant improvment on server resources/load or am I making the whole thing more complicated than it need be as 8,000 rows of data is nothing to worry about............. What did they say about a little bit of knowledge being a dangerous thing? Thanks in advance of any guidance to a neophyte!!?
View Replies !
SPLIT() UDF
SQL UDF split() The objective of this article is to help the SQL developers with an UDF that can be used within a stored procedures or Function to split a string (based on given delimiter) and extract the required portion of the string. Scripting languages like VB script and Java script have in-built split() functions but there is no such function available in SQL server. In my experience this function is really handy when you’re working on an ASP application with SQL server as backend, whereby you’ll need to pass the ASP page submitted values to the SQL stored procedure. To give a simple example, in a typical Monthly reporting ASP page – the users would select a range of months and extract the information pertaining to this date range. Classic implementation of this model is to have an ASP page to accept the input parameters and pass the values to the SQL stored procedure (SP). The SP would return a result set which is then formatted in the ASP page as results. If the date range is continuous ie. JAN07 to MAR07 then the SP can typically accept a ‘From’ and ‘To’ range variables. But I’ve encountered situations whereby the users select 3 months from the current year and 2 months from previous year (non-continuous date ranges). In such scenario the SP cannot have a date range as input parameters. Typically an ASP programmer would do is by having a single date input parameter in the SP and call the SP within a loop in the ASP page. This is an inefficient way of programming as contacting the database server within an ASP loop could cause performance overhead especially if the table being queried is an online transaction processing table. Here is how I handled the above situation. 1.Declared one string input parameter of type varchar(8000) (if you’re using SQL 2005 then it is advisable to use Varchar(Max)) 2.Pass the ASP submitted values as string, in this case the months selected by user would be supplied to the SP as a string 3.Within the Stored Procedure I’ll call the split() function to extract each month from the string and query the corresponding data The basic structure of the stored procedure is as pasted below:- CREATE PROCEDURE FETCH_SALES_DETAIL ( @MONTH VARCHAR(MAX) ) AS BEGIN DECLARE @MONTH_CNT INT,@MTH DATETIME SET @MONTH_CNT=1 WHILE DBO.SPLIT(@MONTH,',',@MONTH_CNT) <> '' BEGIN SET @MTH = CAST(DBO.SPLIT(@MONTH,',',@MONTH_CNT) AS DATETIME) --<<Application specific T-SQLs>>-- (BEGIN) SELECT [SALES_MONTH],[SALES_QTY],[PRODUCT_ID],[TRANSACTION_DATE] FROM SALES (NLOCK) WHERE [SALES_MONTH]= @MTH --<<Application specific T-SQLs>>--(END) SET @MONTH_CNT=@MONTH_CNT+1 END END Dbo.SPLIT() function takes 3 parameters 1)The main string with the values to be split 2)The delimiter 3)The Nth occurrence of the string to be returned The functionality of the UDF is as explained STEP by STEP: 1.Function Declaration CREATE FUNCTION [dbo].[SPLIT] ( @nstring VARCHAR(MAX), @deliminator nvarchar(10), @index int ) RETURNS VARCHAR(MAX) Function is declared with 3 input parameters:- @nstring of type VARCHAR(MAX) will hold the main string to be split @deliminator of type NVARCHAR(10) will hold the delimiter @index of type INT will hold the index of the string to be returned 2.Variable Declaration DECLARE @position int DECLARE @ustr VARCHAR(MAX) DECLARE @pcnt int Three variables are needed within the function. @position is an integer variable that will be used to traverse along the main string. @ustr will store the string to be returned and the @pcnt integer variable to check the index of the delimiter. 3.Variable initialization SET @position = 1 SET @pcnt = 1 SELECT @ustr = '' Initialize the variables 4.Main functionality WHILE @position <= DATALENGTH(@nstring) and @pcnt <= @index BEGIN IF SUBSTRING(@nstring, @position, 1) <> @deliminator BEGIN IF @pcnt = @index BEGIN SET @ustr = @ustr + CAST(SUBSTRING(@nstring, @position, 1) AS nvarchar) END SET @position = @position + 1 END ELSE BEGIN SET @position = @position + 1 SET @pcnt = @pcnt + 1 END END 4.1The main while loop is used to traverse through the main string until the word index is less than or equal to the index passed as input parameter. 4.2Within the while loop each character within the string is verified against the delimiter and if it does not match then local word count variable is checked against the input index parameter 4.3If the values are same ie., the input variable index and the word being processed in the while loop are the same then the word is stored in the @ustr variable. If the values does not match then the @position variable is incremented. 4.4If the character matches with the delimiter then the word count variable @pcnt is incremented along with the @position variable 5.Return the value RETURN @ustr I hope this article would benefit those who are looking for a handy function to deal with Strings. Feel free to send your feedback at dearhari@gmail.com
View Replies !
Adding String To Database, But Name Of String Is Added, Not Data
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 Replies !
Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?
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 Replies !
Capitalize A Text String/String Function Related
Hi Folks: How can I capitalize a string like 'JOHN DOE' into 'John Doe' with one SQL statement. SQL does provide string function like LOWER(char_expr) which would change the whole text string to 'john doe' and I don't want that. So far, in order to do that, I have to use a front-end development language like 'Omnis' which has a string function 'cap()' to capitalize the whole string, then update the back-end SQL with the new string. Thank you in advance for your time and advice. David Nguyen
View Replies !
Need Help With String Manipulation - Splitting 1 String Into Multiple Columns
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 Replies !
SQL Search Split
CREATE PROCEDURE [dbo].[ShowComboLocation]@Keyword varchar(50) ASSELECT TOP 100 PERCENT PropertyAreaID, PropertyAreaFROM dbo.iViewAllWHERE (PropertyArea LIKE '%' + @Keyword + '%')GOQuestion 1 isIf Keyword ="London WestEnd Harrods", I know my query will end up like this (PropertyArea LIKE 'London WestEnd Harrods')But I want to to individually search for 3 or 1-nth words therefore my query should end up like this(PropertyArea LIKE 'London')OR (PropertyArea LIKE 'WestEnd')OR (PropertyArea LIKE 'Harrods')i WANT TO perform this on my SQL STored Procedure,Can anybody provide code or links pls
View Replies !
Reg Split Funtion
Hi, Is it possible to split the following value in sql server ? I have the value like 25 Email Accounts,50 Email Accounts in my sqlserver database. Here i need only the numeric value .ie 25,50.Is it possible? can any one give me the solution .. I am using ASP.Net and C# backend is SQL Server 2000. Thanks and Regards Arul
View Replies !
Split A Field
Whats the best way to do the following?Field1 in Table1 contains numbers and characters seperated by #Examples: aaa#01, kjhkjhjh#21 and jlkjlkj#123How can I create two new fields in Table1, one containing what is tothe left of the # and the other what is to the right?Regards,Ciarán
View Replies !
Split A Column
Hi everybody Does any body know how to split a field in a table into two fields eg usermaster(table) userid(field) usermaster has 40 users with user id 1 to 40 i want to get data as userid userid 1 21 2 22 3 23 . . . . . . 20 40 Thanks you very much
View Replies !
Split Tables And Keep ID
I have a large table that I'm planning on splitting out into 5 smaller ones. What I need to do is maintain some central repository for auto-numbering new records to make sure that no 2 records in different tables have the same unique ID. Thanks in advance!
View Replies !
Should I Split The Procedure Or Not???
I have a procedure that is going to be called through asp pages. This procedure carries out instructions depending on whether customers wants to insert, update or delete their portfolios. Rules are as follows: 1. It should not allow duplicate portfolio name to insert. 2. If customer has reached their max limit of 20 portfolio they can't add. They may have to delete or update the existing portfolio first. 3. all the error handling is done and returned as output parameters. Now coming to the question at present I have one procedure that does all these things. Should I split up the procedure and have three procedures handling the events seperately: 1 Insert 2 Delete 3 update The reason I am concerned is 1 procedure being hit so many times by concurrent users with varying events. I am concerned about performance issue and slowing down of the page. I do not have exact numbers of users at this point. But they would be in thousands or more. Thanks for any suggestions or advice you all might have to share. Hiku
View Replies !
Split Relationship ?
What I have is a table with a primary key. Then I have 5 other tables with a relating key. No problems there. I need to create a relationship with the primary table (primary) key who's data field is 25 charachters. I need to parse that out and have 3 charachters go to one, 2 to the other and so on. I don't know how to do that, can you help?
View Replies !
Split Pipeline
This is probably obvious, but how do I split a pipeline. I.e. I've got a data source with 200 columns - I need to split this into 20 pipelines each containing 10 of the original columns.
View Replies !
Split The File
my ssis package downloades the text file from Ftp. iT downloades for ex 5 files. I want to split the file to smaller file after downloades. If the size of file is more then 600 mB then I want to split it into 6 files. please suggest if any task in SSIS can perform this or any other way.
View Replies !
|
TRACKER
