I have a SQL query in Visual Studio (SSRS). I have a GL Account field that is formatted such as 100-400-123-1234. I wanted to use the substring function to pull out the second set of numbers which I can assign a location:
CASE WHEN substring(GlAccount,5,3)= '400' THEN 'Gainesville'
CASE WHEN substring(GlAccount,5,3)= '401' THEN 'Aledo'
I tried this and it comes back with syntax errors. Can anyone tell me how to approach this as I have a list of about 35 locations that I need to do like this. Thanks.
it's early in the morning (well it is here anyway) and i need a shot of logic...
in the data base i'm currently working with i have a varchar field that holds either a number or a collection of numbers in square brackets. I need to extract the number or the first number in square brackets if it's a list. i know it's gonna be a simple one but my head just won't do it?? i'm trying with substring and charindex to determine the position of the '[' but just not getting it this morning
quick sample of what the data in this column may look like...
declare @t table (col varchar(30)) insert into @t select '2' union all select '[5] [4]' union all select ' [12] [1]'
so i need to get...
col ------- 2 5 12
EDIT- OK, so i get this to work but only if there is actually square brackets
declare @t table (col varchar(30)) insert into @t --select '2'union all select '[5] [4]'union all select ' [12] [1]'
select col ,substring(col, charindex('[',col)+1, charindex(']',col)-charindex('[',col)-1) from @t
but when I use following - 3) select name, datalength(Name), charindex('_2_', Name), substring(name, 5, charindex('_2_', Name) - 3) from msdb.sysjobs.name
I get the result I want (last column): Job_4927_2_7Sun 30 9 4927_2 Job_250144_2_6Sat 34 11 250144_2 Job_30197_2_1Mon 32 10 30197_2
but also with an error: Server: Msg 536, Level 16, State 3, Line 1 Invalid length parameter passed to the substring function.
update vendor_invoice set de_ftpdownload= (select SUBSTRING(de_ftpdownload,1,CHARINDEX('.',de_ftpdow nload,0)-1)as de_ftpdownload from vendor_invoice) where vendor_invoice_id =931
This get me this error.... so what is the max allowable length of substring function.
Server: Msg 536, Level 16, State 3, Line 1 Invalid length parameter passed to the substring function. The statement has been terminated. Thanks Al
I have a problem in Substring function. There is a Text data type field in my Table and it has large no of characters. I want to get the whole text as the result. The problem is its just giving me 300 characters from a row, as the result.
Hi All, I am trying to achieve loading a fact table using my stage and dimesion data , I was planning to use a Lookup transformation to do this, however I am supposed to use a substring task to because the data in the stage table is in adifferent format from that in the dimensions, how do I incorporate this substring task within the data flow, any help is appreciated.
I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?
I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.
Example: 1225000.00
When I select this value using SSMS I get the correct value:
1225000
Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))
1.225e+006
I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.
Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?
I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS.
select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.
The Table1 containt approx. 6000 row and Table2 containt only 210 rows
The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field "PostalCode" and "Telephone".
When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes.
When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1 like in the following query : select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905'))) the query take an execution time of ~15 secondes!!!!
This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second.
It’s weird because, Sql Express 2005 is supposed to be more performant than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!!
I'm trying to create a function that splits up a column by spaces, andI thought creating a function that finds the spaces with CHARINDEX andthen SUBSTRING on those values would an approach. I get an errorsaying that the I have an Invalid column 'Course_Number'. Not surewhy but I am very new to User Defined Functions. Here is what I haveso far:CREATE FUNCTION CourseEvalBreakdown(@fskey int)RETURNS @CourseTable TABLE(Col CHAR(2),Area CHAR(4),Number CHAR(4),Section CHAR(4),Term CHAR(3))ASBEGINDECLARE@Ind1 tinyint,@Ind2 tinyint,@Rows intDECLARE @crstbl TABLE (FStaffKey int,Course_Number char(20),Term char(3),Col char(2),Area char(4),Number char(4),Section char(3))INSERT INTO @crstbl (FStaffKey, Course_Number, Term)SELECT FStaffKey, Course_Number, TermFROM EvalWHERE FStaffKey = @fskeySET @Rows = @@rowcountWHILE @Rows 0BEGINSET @Ind1 = CHARINDEX(' ', Course_Number, 4)SET @Ind2 = CHARINDEX(' ',Course_Number, (CHARINDEX(' ',Course_Number, 4)+1))UPDATE @crstblSET Col = SUBSTRING(Course_Number, 1, 2)WHERE FStaffKey = @fskeyUPDATE @crstblSET Area = UPPER(SUBSTRING(Course_Number, 4, @Ind1-4))WHERE FStaffKey = @fskeyUPDATE @crstblSET Number = UPPER(SUBSTRING(Course_Number, @Ind1+1, (@Ind2-@Ind1)-1))WHERE FStaffKey = @fskeyUPDATE @crstblSET Section = SUBSTRING(Course_Number, @Ind2+1, 3)WHERE FStaffKey = @fskeyENDINSERT @CourseTableSELECT Col, Area, Number, Section, Term FROM @crstblRETURNENDGO
select substring(ISNULL(CAST(FullAdress AS NVARCHAR(MAX)),''),1,charindex(',',ISNULL(CAST(FullAdress AS NVARCHAR(MAX)),''))-1) from tbl_lrf_company_details_with_codes
but i am getting the error as "Invalid length parameter passed to the SUBSTRING function." Please advice Thanks In advance
I am using a simple procedure to pivot results (found in another forum and adapted it). It is done on SQL Server 2005 server with all service packs. Procedure: ************** ALTER Procedure [dbo].[EthnicityPivot] @StDate as Datetime, @EndDate as Datetime as begin DECLARE @Teams varchar(2000)
truncate table ForEthnicPivot
INSERT INTO ForEthnicPivot SELECT DISTINCT COUNT(ID), Team, Ethnicity FROM dbo._EthnicityByTeamEpisode where Startdate between @StDate and @EndDate GROUP BY Ethnicity, Team
SET @Teams = '' --// Get a list of the pivot columns that are important to you.
SELECT @Teams = @Teams + '[' + Team + '],' FROM (SELECT Distinct Team FROM ForEthnicPivot) Team --// Remove the trailing comma
SET @Teams = LEFT(@Teams, LEN(@Teams)-1) --// Now execute the Select with the PIVOT and dynamically add the list --// of dates for the columns EXEC( 'SELECT * FROM ForEthnicPivot PIVOT (SUM(countID) FOR Team IN (' + @Teams + ')) AS X' ) end ************
I can call the function: exec EthnicityPivot '01/01/2007','09/09/2007'
and it works fine in SQL analyzer, but when I want to use it in Visual Studio in a new report I am getting this error message:
There is an error in the query. Invalid length parameter passed to the SUBSTRING function. Incorrect syntax near ')'.
Hi, I am new at sql 2000 and 2005, I have created a package in 2005 which I am trying to execute on a daily bases by creating a job. At first because of security issues the job would not execute. Hence, I had to create a credential and a proxy to run the job with sa account. Now it is giving me this error, €œSQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. €œ Through research I have no clue as what I need to do, or where to look. The package runs without error when I execute the package itself. Any help is greatly appreciated. Thanks, Lori
Hello, I have a query that returns a daily revenue figure. The query is as follows:
SELECT top 1000 ds.AcctCode, ds.TxnDate, SUM(isnull(ds.FuelFee,0)) + SUM(isnull(ds.CashFee,0)) + SUM(isnull(ds.ScFee,0)) AS TotalDailyRevenue, --"MTD" = ?, --"YDT" = ?, ps.TC, CASE WHEN ps.Proj = 100 THEN 'New Account' WHEN ps.Proj = 200 THEN 'Current Account' END AS ProjStatus, ps.FSR, ps.SubmitRep1
FROM TxnRptg.dbo.tbl_DailySummary ds INNER JOIN SalesData.dbo.tbl_CYProcessedSales ps ON ds.AcctCode = ps.Acct
WHERE MONTH(ds.TxnDate) = 1 AND Proj IN (100,200) AND TC = 'HV'
GROUP BY ds.AcctCode, ds.TxnDate, ps.TC, ps.Proj, ps.FSR, ps.SubmitRep1
ORDER BY ds.AcctCode, ds.TxnDate
--*********************************
TxnDate represents a single day of the month. How can I include MTD so that the dates for the revenue total are from DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) (beginning of current month) to TxnDate, and YTD so that the revenue totals are from DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) (beginning of the current year) to TxnDate?
Hi i got errro mess "Invalid length parameter passed to the substring function" from this below. Anyone how can give me a hint what cause this, and how i can solve it? if i remove whats whitin thoose [] it works, i dont use [] in the code :) colums: VLF_InfectionDestination is nvarchar 254
SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number FROM tb_AVVirusLog INNER JOIN __CustomerMachines002 ON tb_AVVirusLog.CLF_ComputerName = __CustomerMachines002.FalseName WHERE (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND (__CustomerMachines002.folder_id = @folderId) [OR (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND (tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@%')] GROUP BY tb_AVVirusLog.VLF_VirusName HAVING (NOT (tb_AVVirusLog.VLF_VirusName LIKE N'cookie')) ORDER BY COUNT(tb_AVVirusLog.VLF_VirusName) DESC
An application I developed normally works great, but it seems that when processing a certian record (and none of the others so far), SQL Server throws this error: "Invalid length parameter passed to the substring function."
Private Sub setControls(ByVal dr As SqlDataReader) If (dr.Read()) Then '<--*******problem line*******
The SqlDataReader (orderReader) doesn't blow up or anything until I call .Read() (and, as mentioned, this problem only occurs for one order). What could be happening here?
I'm in desperate need of help. I'm setting up an intranet portal using DNN. I added an event calendar module, but whenever I try to add events to it, the system rejects it with a nasty Sql exception saying the conversion from char to datetime produced an out of bounds result.
The string the table uses to convert to datetime is (I have not modified it, the module is exactly as it came when i downloaded)
(convert(varchar,getdate(),101))
The whole stack trace for the error is:
Stack Trace:
[SqlException: La conversión del tipo de datos char a datetime produjo un valor datetime fuera de intervalo.] System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +642 System.Data.SqlClient.SqlCommand.ExecuteReader() +11 DotNetNuke.AVCalendarDB.Save() +1067 DotNetNuke.AVCalendarEdit.updateButton_Click(Objec t sender, EventArgs e) +3367 System.Web.UI.WebControls.LinkButton.OnClick(Event Args e) +108 System.Web.UI.WebControls.LinkButton.System.Web.UI .IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +138 System.Web.UI.Page.ProcessRequestMain() +1263
At first I thought it could be a language issue (DNN and the module are in english and my system runs XP Pro in Spanish) but I discarded it since it didn't work when I installed XP Pro in english
Any ideas?? I would really appreciate your help Best regards from Chile Javier L.
Hi, I am somewhat new to t-sql and what I do know is from trial & error and help from this forum. What I need to do is add a column of counts for id numbers that are not unique.
I have a small database in which Employee's are linked to "Tags". These tags can be red or yellow. So, I have 3 tables. Tag_Colors : tag_color_ID , tag_colors Employee_Table: employeeID , employee_name Tag_Table: tagID, tag_color_ID, employeeID, tag_notes
I need to be able to perform a query, where I can list employee's by the number of tags they have. I don't even know how to get started on this -- could anybody point me in the right direction? After I can et a query working, I think I can take it from there -- and get it to display on a web page.
I've been trying to get a range of values out of my SQL Server 2000 db without sucess. The field in question has the data type of char(8) and looks like this:
House_numbr_pub (leading spaces in front of each value) 140A 140 141 142 143 144 145 146 147 148 149 150 151 . . . 14500 . . .
Does anyone know how write a sql statement that will return 140-150, but excluding the ' 14500' and 100-1000. I tried the following where clause to return a range between 100-1000.
WHERE (cook.STREET_PUB LIKE 'lincoln%') AND (LEN(LTRIM(cook.HOUSE_NUMBR_PUB)) BETWEEN 3 AND 4) AND ( (LTRIM(cook.HOUSE_NUMBR_PUB) >= '100') and (LTRIM(cook.HOUSE_NUMBR_PUB) <= '1000') )
This where clause only return two records (100 and 1000). I want it to return 100-1000.
I also tried the following where clause:
WHERE LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]' OR LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]'
However, building this on the fly with .net will take some effort if someone is trying to search range 1-10000000.
Some have the same value in the 'subkey' field. I want to select all the records from the table that have their highest MAINKEY.
So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)
Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.
So the tables looks sort of lLike this:
ID SK MK 1 10 2 2 10 3 3 25 2 4 25 3
I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.
Here is my code below. When I attempt to run the data flow task that calls this script, I get this error:
"Index was outside the bounds of the array."
I honestly do not know what the problem is here. There are definitely 6 columns in the file. In fact, even if comment out everything except the first line (myCol1), I still get the "Index was outside the bounds of the array." error.
Any ideas??? Need help.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim rowValues As String()
rowValues = Row.Line.Split(columnDelimiter) 'parse row by comma
If Row.Line Like "*OPENING BALANCE*" Then
Row.AccountNumber = Nothing 'set to null for conditional split
Row.OpeningBalance = CDec(rowValues(3)) 'get the 4th value
ElseIf Row.Line Like "*CLOSING BALANCE*" Then
Row.AccountNumber = Nothing 'set to null for conditional split
Row.ClosingBalance = CDec(rowValues(2)) 'get the 3rd value
Right now I'm just looking at websites how to create a database and putting it in the App_Data folder which is no problem. What I want to do is Programmatically do it. in the example code that I see it says: Dim ConnString as string = ConfigurationManager.ConnectionStrings(connStringName).ConnectionString or Dim MyConnString as new sqlconnection = New SqlConnection(ConfigurationManager.ConnectionStrings(connStringName).ConnectionString) now every time I put the connStringName in the parenthasis it tells me that its an error: Name connstringName is not declared. I figure the connstringName is the name in the connectionStrings in the web.config. So I am doing something wrong here, I would appreciate any help.
Can someone please look at my stored procedure? I am trying to create the following stored procedure, but get the following errormessages: Msg 102, Level 15, State 1, Procedure InsertWork, Line 3Incorrect syntax near '7'.Msg 102, Level 15, State 1, Procedure InsertWork, Line 25Incorrect syntax near '@7am8am'. USE [Work]GO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[InsertWork]( 7am8am nvarchar(500), 8am9am nvarchar(500), 9am10am nvarchar(500), 10am11am nvarchar(500), 11am12noon nvarchar(500), 12Noon1pm nvarchar(500), 1pm2pm nvarchar(500), 2pm3pm nvarchar(500), 3pm4pm nvarchar(500), 4pm5pm nvarchar(500), 5pm6pm nvarchar(500), 6pm7pm nvarchar(500), 7pm8pm nvarchar(500), 8pm9pm nvarchar(500), 9pm10pm nvarchar(500), 10pm11pm nvarchar(500), Notes nvarchar(500), Date nvarchar(15))AS BEGIN INSERT INTO WorkDay VALUES @7am8am, @8am9am, @9am10am, @10am11am, @11am12Noon, @12Noon1pm, @1pm2pm, @2pm3pm, @3pm4pm, @4pm5pm, @5pm6pm, @6pm7pm, @7pm8pm, @8pm9pm, @9pm10pm, @10pm11pm, @Notes, @DateEND
I'm new to sql and have come up with a problem and can't seem to find the answer. Most would probably find it simple but I cant get my head around it! :p I have the following table structure User_Table-----------User_ID (Key)FirstNameLastName Contacts_Table--------------User_ID (F key)Contact_User_ID User_Table stores all users details and assigns them a User_ID. Users can add other users to their contacts, and this will be stored in Contacts_Table on a one-to-many basis. *deep breath*... So User_ID in Contacts_Table, will store the User_ID from User_Table, and the Contact_User_ID in Contacts_Table will store the User_ID from User_Table. Does this seem ok? Sorry if I confused everyone! So my question is, how do I select a user and show all his contacts (names etc)? I thought I could use innerjoin but I dont think it would work here. Any ideas? Thanks!
