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.
Receive this error msg when trying to parse a SQL Query
.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.
I have just finished going thru a long process getting SP2 CU3 patch installed.
The first file microsoft sent was corrupted and in turn corrupted my .NET Framework, had to run repair on the Framework before installing CU3. I am now on version 9.0.3186. I have two other boxes with the same version and do not have this problem
My understanding is that all of the patches/hotfixes since SP,1 that this problem was resolved
Any help will be greatly appreciated as I am in a very heavy development stage on this box.
Hello, I get the following error when I run my package interactively. From the logs written out by the driver, it appears that all is working well as far as connecting to the data source and pulling data. It seems as if this error occurs when the DataReader source tries to process the received data.
SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning. Error: 0xC0047062 at Data Flow Task, DataReader Source [1]: System.Data.Odbc.OdbcException: ERROR [42000] XML parse error at 162:1338: not well-formed (invalid token) at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute() at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper) Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source" (1) failed the pre-execute phase and returned error code 0x80131937. Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning. Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (691)" wrote 0 rows. Task failed: Data Flow Task SSIS package "MyPackage.dtsx" finished: Success.
I am not sure where to look next. Any help is much appreciated.
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
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.
Although I am able to connect to a SQL 2005 server (both through the SQL Management Studio and through the connection wizard in Visual Studio 2005), I am unable to parse a query if I use a connection set up as ADO.NET. When I use OLE-DB, it works properly. When I attempt to execute a SQL task (comprised of a single SELECT statement), I get "Failed to acquire connection 'servernamegoeshere'. Connection may not be configured correctly or you may not have the right permissions on this connection." How can this be if I'm able to parse data from this connection through other means? This happens both locally and remotely.
Executing Parse in Sql Server 2005 Management Studio I get the following error:
.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.
Reading SP1 fixes, I found a similar problem but despite I installed latest hotfix (sql version is now 9.0.2153) I can't able to execute Parse to check T-SQL statements...
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.
SELECT SID, SAmAccountName, DOMAIN, EmployeeID, CustAtr, DisplayName, UPN, Date, flag, Date_Mod, Date_Exp, IsActive, OU, Description, IDType FROM dbo.CORP_EMP_IDS as corp_emp_ids WHERE (EmployeeID BETWEEN 'A' AND 'Z') AND (IsActive = 1) AND (EmployeeID NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin')) AND (Date_Exp > GETDATE() OR Date_Exp = '') AND (SAmAccountName NOT LIKE '%$%')
UNION
SELECT SID, SAmAccountName, DOMAIN, EmployeeID, CustAtr, DisplayName, UPN, Date, flag, Date_Mod, Date_Exp, IsActive, OU, Description, IDType FROM dbo.CORP_EMP_IDS AS CORP_EMP_IDS_1 WHERE (CustAtr BETWEEN 'A' AND 'Z') AND (IsActive = 1) AND (CustAtr NOT IN ('gen', 'G', 'M', 'S', 'T', 'TT', 'AdminAcct', 'TestAcct', 'ConfRoom', 'AcctAdmin','AdminAccts')) AND (Date_Exp > GETDATE() OR Date_Exp = '') AND (SAmAccountName NOT LIKE '%$%')
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
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
When I am trying to read rows from any table from any database in the offsite production server with Pc Anywhere ( all other servers can be acess fine with pc anywhere
I get this error
An unexpected error happned during this operation {query} query designer error encountered a query error unexpected error
I checked the version of Enterprise manager Help About microsoft managment console 1.1version 4.0 bulid 1381 service pack 4
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.
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?
Please let me know if you come across any name strings this function cannot parse. CREATE function FormatName(@NameString varchar(100), @NameFormat varchar(20)) returns varchar(100) as begin --blindman, 11/04 --FormatName parses a NameString into its component parts and returns it in a requested format. -- --@NameString is the raw value to be parsed. --@NameFormat is a string that defines the output format. Each letter in the string represents --a component of the name in the order that it is to be returned. --[H] = Full honorific --[h] = Abbreviated honorific --[F] = First name --[f] = First initial --[M] = Middle name --[m] = Middle initial --[L] = Last name --[l] = Last initial --[S] = Full suffix --[s] = Abbreviated suffix --[.] = Period --[,] = Comma --[ ] = Space
--Example: select dbo.Formatname('Reverend Gregory Robert Von Finzer Junior', 'L, h. F m. s.') --Result: 'Von Finzer, Rev. Gregory R. Jr.'
--Test variables -- declare@NameString varchar(50) -- declare@NameFormat varchar(20) -- set@NameFormat = 'L, h. F m. s.' -- set@NameString = 'Reverend Gregory Robert Von Finzer Junior'
--Prepare the string --Make sure each period is followed by a space character. set@NameString = rtrim(ltrim(replace(@NameString, '.', '. '))) --Eliminate double-spaces. while charindex(' ', @NameString) > 0 set @NameString = replace(@NameString, ' ', ' ') --Eliminate periods while charindex('.', @NameString) > 0 set @NameString = replace(@NameString, '.', '')
--If the lastname is listed first, strip it off. set@TempString = rtrim(left(@NameString, charindex(' ', @NameString))) if@TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') set @TempString = rtrim(left(@NameString, charindex(' ', @NameString, len(@TempString)+2))) ifright(@TempString, 1) = ',' set @LastName = left(@TempString, len(@TempString)-1) iflen(@LastName) > 0 set@NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
--Get rid of any remaining commas while charindex(',', @NameString) > 0 set @NameString = replace(@NameString, ',', '')
--Get Honorific and strip it out of the string set@TempString = rtrim(left(@NameString, charindex(' ', @NameString + ' '))) if@TempString in ('MR', 'MRS', 'MS', 'DR', 'Doctor', 'REV', 'Reverend', 'SIR', 'HON', 'Honorable', 'CPL', 'Corporal', 'SGT', 'Sergeant', 'GEN', 'General', 'CMD', 'Commander', 'CPT', 'CAPT', 'Captain', 'MAJ', 'Major', 'PVT', 'Private', 'LT', 'Lieutenant', 'FATHER', 'SISTER') set @Honorific = @TempString iflen(@Honorific) > 0 set@NameString = ltrim(right(@NameString, len(@NameString) - len(@TempString)))
--Get Suffix and strip it out of the string set@TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) if@TempString in ('Jr', 'Sr', 'II', 'III', 'Esq', 'Junior', 'Senior') set @Suffix = @TempString iflen(@Suffix) > 0 set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString)))
if @LastName is null begin --Get LastName and strip it out of the string set@LastName = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) set@NameString = rtrim(left(@NameString, len(@NameString) - len(@LastName))) --Check to see if the last name has two parts set@TempString = ltrim(right(@NameString, charindex(' ', Reverse(@NameString) + ' '))) if@TempString in ('VAN', 'VON', 'MC', 'Mac', 'DE') begin set @LastName = @TempString + ' ' + @LastName set @NameString = rtrim(left(@NameString, len(@NameString) - len(@TempString))) end end
--Get FirstName and strip it out of the string set@FirstName = rtrim(left(@NameString, charindex(' ', @NameString + ' '))) set@NameString = ltrim(right(@NameString, len(@NameString) - len(@FirstName)))
--Anything remaining is MiddleName set@MiddleName = @NameString
--Create the output string set@TempString = '' while len(@NameFormat) > 0 begin if @IgnorePeriod = 'F' or left(@NameFormat, 1) <> '.' begin set @IgnorePeriod = 'F' set @TempString = @TempString + case ascii(left(@NameFormat, 1)) when '72' then case @Honorific when 'Dr' then 'Doctor' when 'Rev' then 'Reverend' when 'Hon' then 'Honorable' when 'Maj' then 'Major' when 'Pvt' then 'Private' when 'Lt' then 'Lieutenant' when 'Capt' then 'Captain' when 'Cpt' then 'Captain' when 'Cmd' then 'Commander' when 'Gen' then 'General' when 'Sgt' then 'Sergeant' when 'Cpl' then 'Corporal' else isnull(@Honorific, '') end when '70' then isnull(@FirstName, '') when '77' then isnull(@MiddleName, '') when '76' then isnull(@LastName, '') when '83' then case @Suffix when 'Jr' then 'Junior' when 'Sr' then 'Senior' when 'Esq' then 'Esquire' else isnull(@Suffix, '') end when '104' then case @Honorific when 'Doctor' then 'Dr' when 'Reverend' then 'Rev' when 'Honorable' then 'Hon' when 'Major' then 'Maj' when 'Private' then 'Pvt' when 'Lieutenant' then 'Lt' when 'Captain' then 'Capt' when 'Cpt' then 'Capt' when 'Commander' then 'Cmd' when 'General' then 'Gen' when 'Sergeant' then 'Sgt' when 'Corporal' then 'Cpl' else isnull(@Honorific, '') end when '102' then isnull(left(@FirstName, 1), '') when '109' then isnull(left(@MiddleName, 1), '') when '108' then isnull(left(@LastName, 1), '') when '115' then case @Suffix when 'Junior' then 'Jr' when 'Senior' then 'Sr' when 'Esquire' then 'Esq' else isnull(@Suffix, '') end when '46' then case right(@TempString, 1) when ' ' then '' else '.' end when '44' then case right(@TempString, 1) when ' ' then '' else ',' end when '32' then case right(@TempString, 1) when ' ' then '' else ' ' end else '' end if ((ascii(left(@NameFormat, 1)) = 72 and @Honorific in ('FATHER', 'SISTER')) or (ascii(left(@NameFormat, 1)) = 115 and @Suffix in ('II', 'III'))) set @IgnorePeriod = 'T' end set @NameFormat = right(@NameFormat, len(@NameFormat) - 1) end
In MS Access 2000 if I have a String such as:Column1Delta CC: 123Charley CC: 234Foxtrot CC: 890and I wanted to extact just the numbers in to a field called CCI could use this formula in a calculated field:CC: Mid([Column1],Instr(1,[Column1],"CC")+3,50)resulting in:CC123234890Any idea on what the code should be within a view in SQL Server?also -- what is a good reference that can help with these types ofproblems.Any help appreciated!RBollinger
DECLARE @UserName nvarchar(100)SELECT @UserName = SYSTEM_USERvalue returned is "DomainNTSignonName"What I want is only "NTSignonName"Is there a function to do this or an easy parse for this in SQL2000?lq
Hi all,I have a table of text and associated data. I want to break apart the textinto individual words, yet retain the data in other columns. For example:Sentence: Chapter:--------------------------I like cats. 1Joe likes dogs. 2Should become:Word: Chapter:--------------------------I 1like 1cats 1Joe 2likes 2dogs. 2Are there built-in SQL parsing functions, If not, what text handlingfeatures would be most useful for building them?Thanks!
