SQL Syntax Trouble (Splitting A Field In Two)

Apr 12, 2007

I am able to use the following SQL Statement in Access, but when I try to use it in a View or Stored Procedure for SQL Server 2000, it says the function InStr is not a valid function.  Would someone be able to assist me with modifying this query to work in SQL Server 2000.  This is Spliting a field called EName into two fields called FName and LName.
 
SELECT  EmployeeID, CountryCode, Branch, SSN, Title, HireDate, Status,
 
RIGHT(EName, LEN(EName) - InStr(EName, [, ]) - 1) AS FName, LEFT(EName, InStr(EName, [, ]) - 1)
               AS LName
 
FROM     dbo.Staging_Employees
 
Thank You,
Wayne

View 5 Replies


ADVERTISEMENT

Having Trouble Splitting A List To A Resultset?

May 29, 2006

Ok, run this...

SELECT * FROM dbo.fnListSplit('a,b,c,d,e,f', ',')

-------------------------------------------------------------
CREATE FUNCTION dbo.fnListSplit
(
@List VARCHAR(8000),
@Delimiter VARCHAR(2)
)
RETURNS @Resultset TABLE (i SMALLINT IDENTITY(0, 1), x VARCHAR(8000))
AS

BEGIN
INSERT@Resultset
(
x
)
SELECTSUBSTRING(@Delimiter + @List + @Delimiter, w.i + 1, CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.i + 1) - w.i - 1)
FROM(
SELECTv0.n + v1.n + v2.n + v3.n i
FROM(
SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
)v0,
(
SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112 UNION SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
) v1,
(
SELECT 0 n UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768 UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536 UNION ALL SELECT 1792 UNION SELECT 2048 UNION ALL SELECT 2304 UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072 UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840
) v2,
(
SELECT 0 n UNION ALL SELECT 4096
) v3
) w
WHEREw.i = CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.i) AND w.i < LEN(@Delimiter + @List)
ORDER BYw.i

RETURN
END

View 5 Replies View Related

Splitting A Field Into Two

Jul 20, 2005

I have a field that contains codes likefhj#asdskjjljlj#12And so on.What I want to do is create two new fields (field1 and filed2) thatsplit the original filed at '#'If a field does not contain '#' I would like its entire contents to besaved in field1.Also how do I ensure that I save these changes?Thanks fo any help in advance.Regards,Ciarán

View 2 Replies View Related

Need Help Splitting Contents Of A Field In T-Sql

May 22, 2006

I have a table with about 2 million records.
One of the fields has data seperated by a comma. I need to be able to grap the data for each record and split those items into their own table in seperate rows. This is easy in Asp but the page will timeout before it can process all the records.
Any Ideas?
Thanks,Rick

View 4 Replies View Related

Splitting A Text Field

Feb 1, 2001

hi,
I am trying to split a text field .
what I want to do is -
split a text field and insert chars 1 to 8 in one field and then from 9 to 20 in another and so on.
Can someone help me in solving this.
TIA.
PD

View 3 Replies View Related

Problems Splitting Name Field

Sep 1, 2006

I am reading in data from a legacy system where name data is in one field. The single field includes last name, first name, middle initial(sometimes), and suffix(sometimes).

Due to the optional components it is a bear. I am using a fuzzy lookup on the suffix and derived columns for the first, last, and middle initial. The first name and suffix work, but I'm having trouble with the last and middle initial. The following is the formula I'm using for both. The first name formula is causing an error, and the middle initial is only populating if the suffix exists. If anyone has insight, it is appreciated.

FirstName:
TRIM(SUBSTRING([ARPNAME-T],FINDSTRING([ARPNAME-T]," ",1) + 1,FINDSTRING([ARPNAME-T]," ",2) - FINDSTRING([ARPNAME-T]," ",1) + 1))

MiddleInitial:
SUBSTRING(TRIM([ARPNAME-T]),FINDSTRING([ARPNAME-T]," ",2) + 1,(LEN(TRIM([ARPNAME-T])) - FINDSTRING([ARPNAME-T]," ",1) - LEN(PSUFFIX)))

View 7 Replies View Related

Splitting Name Field To FName And Last Name In Package

Apr 2, 2007

Hi



I have a customer data in a flat file (*.csv). It has a field "Name" which has data as James Smith. I want to load the dat in the dimenson table as fields FName="James" and LName="Smith". What type of data transformation should i use and how should i make it happen?



thanks,

chamajid

View 7 Replies View Related

SQL Server 2008 :: Splitting A Field In The Middle?

Mar 19, 2015

I have a field which looks like "LastName, FirstName (DOB: 01/01/1900)"

How do I get the "01/01/1900" between ":" and ")"

View 6 Replies View Related

Trouble With Update Syntax

Jul 20, 2005

I'm new to SQL and can't figure out how to update my table(StoreItemStatus) that contains the current status for items in eachstore (STORE_KEY, ITEM_KEY, STATUS,...).I get updated status info in a table (I'll call it NewInfo) thathas similar fields. NewInfo may contain multiple records for eachStore/Item, but I will just use the latest status. I'm not sure howto update StoreItemStatus using each record of NewInfo. Any advice isgreatly appreciatedThanks,Paul

View 14 Replies View Related

Splitting One Data Field With Hyphens To Separate Records

Feb 16, 2004

I receive a file that will have hyphens between data items such as
123-aed-edr-45r-ui9
1-ed3-45r-rrr-98u
I need to split the values to load into a table that will hold the 5 separate data itens. The fields will always have the hyphens but could be different lengths. Any idea on a best approach to split this in tsql

View 2 Replies View Related

Splitting Out Names From One Field Delimted By Comma And A Space

Apr 28, 2004

have a field in a table that has combined lastname,firstname and middle name like

combs,albert mike
woods-athere,jane alice

The last and first name are separated by a comma, and the middle name by a space
I need in tsql to split them to last name,first name and middle name
I used below

select substring (longname, 1, patindex( '%,%' , longname) -1 ) 'firstname',
substring (longname, patindex( '%,%', longname) + 1, len(longname)) 'lastname',
substring (longname, patindex( '% %', longname) + 1, len(longname)) 'middlename'
FROM Demographic_staging

I get the names all split, but I get the middle name with the first name. How can I limit the first name to recoginze the comma, but stop at the space

View 3 Replies View Related

Syntax Trouble With Int In Stored Procedure

May 9, 2006

Dear Forum,
I am adding a new column name to my Stored Procedure called HeadlinerID.  It is an Int that is 4 characters long.  I seem to be putting this in incorrectly in my stored procedure.  I have tried it like: @HeadlinerID int(4), and @HeadlinerID int,  and both ways I get the error below:
Error 170: Line 16: Incorrect Syntax near ‘)’. Line 40: Incorrect syntax near ‘@Opener’.
Is there a trick to putting in integers in a stored procedure?
 
Thanks,
Jeff Wood
Boise, ID
CREATE PROCEDURE Item_Insert(   @Title varchar(50),   @_Date datetime,   @Venue varchar(50),   @HeadlinerID int(4),   @Opener varchar(150),   @Doorstime varchar(50),   @Showtime varchar(50),   @Price varchar(50),   @Onsaledate datetime,   @Ticketvendor varchar(50),   @TicketURL varchar(150),   @Description varchar(1000),
)AS
INSERT INTO shows(   Title,   _Date,   Venue,   HeadlinerID,   Opener,   Doorstime,   Showtime,   Price,   Onsaledate,   Ticketvendor,   TicketURL,   Description)VALUES(   @Title,   @_Date,   @Venue,   @HeadlinerID,   @Opener,   @Doorstime,   @Showtime,   @Price,   @Onsaledate,   @Ticketvendor,   @TicketURL,   @Description    )GO

View 3 Replies View Related

Trouble With An ASync Query To Insert A Record And Return The Auto Number Field.

Aug 31, 2007

I get this error when I look at the state of my SQLresults object. Have I coded something wrong?Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.  conn.Open()
Dim strSql As String

strSql = "INSERT INTO contacts (companyId, sourceId, firstName, lastName, middleName, birthday, dateCreated)" _
& "VALUES ('" & companyId & "', '" & sourceId & "', '" & firstName & "', '" & lastName & "', '" & middleName & "', '" & birthday & "', '" & now & "') SELECT @@IDENTITY AS 'contactId'"

Dim objCmd As SqlCommand
objCmd = New SqlCommand(strSql, conn)

Dim aSyncResult As IAsyncResult = objCmd.BeginExecuteReader()

If aSyncResult.AsyncWaitHandle.WaitOne() = True Then


Dim sqlResults As SqlClient.SqlDataReader

sqlResults = objCmd.EndExecuteReader(aSyncResult)

Dim cid As Integer



cid = sqlResults.Item("contactId")
Me.id = cid
conn.Close()
Return cid
Else
Return "failed"


End If  

View 3 Replies View Related

Syntax Error In Destination Field When Using Int

Nov 21, 2007

Hi,
I am using the Execute SQL Task in SSIS. I tried to create a table CREATE TABLE newtable(ID INT, NAME nvarchar(50)) in my ACCESS database.
However, it prompts me an error, saying that there is an syntax error and I need to change the INT. I do not know what is the correct data type.
Can anyone give advice? thks alot.

View 1 Replies View Related

COUNT Field Incorrect Or Syntax Error.

Sep 21, 2004

I'm stuck. This is in C#.
I am making the following query:
string query = INSERT INTO region_info(prefix, region, last_update) VALUES ('" + RegionInfo.Prefix + "', '" + region + "', ???TIMESTAMP???);

and then executing
query = query.Replace("???TIMESTAMP???", "'" + DateTime.Now.ToString("yyyyMMdd") + "'");

Thus, an example query is:
INSERT INTO region_info(prefix, region, last_update) VALUES ('907209', 'Alaska-Juneau', '20040921');
When I execute this query through my program(uses ADO.net), I get a "COUNT field incorrect or syntax error" exception, but if I run this same query through the query analyzer, it works fine.

Any ideas on what'z going wrong?
Thanks

View 5 Replies View Related

Transact SQL :: GROUP By Field Syntax Error

Oct 8, 2015

I have the following querry:

SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode
,PIT.Name,

[Code] ....

That query is suppose to add to calculation field OutStock and InStock based on the value of n

When executing this  query I get the following message :

Column 'Sales.ActionPlan_History.ReturnDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

View 3 Replies View Related

Seeking Correct Syntax To Pass A NULL Value For A Datetime Field From An Asp Page To SQL

Jan 13, 2006

Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!

View 7 Replies View Related

In Code Behind, What Is Proper Select Statement Syntax To Retrieve The @BName Field From A Table?

Apr 8, 2006

In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String        Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50)        parameterBName.Value = strName        myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'.
Source Error:
Line 59: Line 60: Line 61:         myCommand.ExecuteNonQuery()   'Execute the query

View 2 Replies View Related

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

May 20, 2008

Why does the following call to a stored procedure get me this error:


Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.




Code Snippet

EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'




The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.

I can't find anything wrong in the syntax for CONVERT or any nearby items.


Help me please. Thank you.

View 7 Replies View Related

Splitting Values

Apr 25, 2007

I have a sql2005 db. I'm trying to perform a query that takes a list of values in a parm, breaks them up and uses them in an IN clause as follows:
<code>
ALTER PROCEDURE [dbo].[SelectPartialOrderListByDate] @StoreID varchar(255)ASBEGIN SET NOCOUNT ON; SELECT DISTINCT O.OrderID, M.InitialSalesCode, M.DateOccurred, M.ErrorCode   FROM dbo.OrderInfo O WITH (NOLOCK)   INNER JOIN dbo.Message M WITH (NOLOCK) ON O.OrderID = M.OrderID   WHERE O.StoreID IN (@StoreID)END
</code>
The values coming into the procedure are obviously in a varchar string format. 
Notice the boldfacing above. The values within this IN clause should be something like this after the conversion: IN (87,108).
How can I accomplish this? I've seen examples of creating a UDF and returning a table, but am not sure how to apply that to my situation via a code sample.
Could someone help me out?

View 4 Replies View Related

Splitting A Sql String

Oct 28, 2004

aaaaa,bbbb,ccccc,dddd,ffff,gggg,llll,kkkk,nnnnn

How can split the above string to the following individual strings
aaaaa
bbbb
ccccc
dddd
ffff
gggg
llll
kkkk
nnnnn

Thanks

View 2 Replies View Related

Column Splitting

Jun 22, 2007

Hi Everyone,I've been given the painstaking project of splitting a single column into multiple columns and rows. I have a solution set up in which I will be posting further down the post but I want to see if there is a much more efficient solution to this.sample data:create table tbl_list(pk_int_itmid int(5) Primary Key,vchar_desk vchar(300));create table tbl_test1(fk_int_itmid int(5) references tbl_list(pk_int_itmid),vchar_itm varchar(60));insert into tbl_list values(1, 'this item');insert into tbl_list values(2, 'that item');insert into tbl_list values(3, 'those items');insert into tbl_test1 values(1, 'A, B - C, D, E - F, G, H - I');insert into tbl_test1 values(2, 'J, K - L, M, N - O');insert into tbl_test1 values(3, 'P, Q - R');into this table:create table tbl_output(fk_int_itmid int(5) references tbl_list(pk_int_itmid),vchar_itmA varchar(60),vchar_itmB varchar(60),vchar_itmC varchar(60));Output in comma delimited form:'1', 'A', 'B', 'C''1', 'D', 'E', 'F''1', 'G', 'H', 'I''2', 'J', 'K', 'L''2', 'M', 'N', 'O''3', 'P', 'Q', 'R'my current solution:create view vw_itm_a as select fk_int_itmid, substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmCfrom tbl_test1where charindex(',',vchar_itm) >1Gocreate view vw_itm_b as select fk_int_itmid, substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmCfrom vw_itm_awhere charindex(',',vchar_itmC) >1;Gocreate view vw_itm_c as select fk_int_itmid, substring(vchar_itmC, 0, charindex('-',vchar_itmC)) as vchar_itmA,substring(vchar_itmC, charindex('-',vchar_itmC)+1 , charindex(',',vchar_itmC)-charindex('-',vchar_itmC)) as vchar_itmB,substring(vchar_itmC, charindex(',',vchar_itmC)+1) as vchar_itmCfrom vw_itm_bwhere charindex(',',vchar_itmC) >1;Go;create view vw_itm_d asselect fk_int_itmid, vchar_itmA, vchar_itmB,substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmCfrom vw_itm_a ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmidGo;create view vw_itm_e asselect fk_int_itmid, vchar_itmA, vchar_itmB,substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmCfrom vw_itm_c ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmidGo;create view vw_itm asselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_awhere fk_int_itmid not in (select fk_int_itmid from vw_itm_b)unionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_dunionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_bwhere fk_int_itmid not in (select fk_int_itmid from vw_itm_c)unionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_eunionselect fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC from vw_itm_cGo;select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC into tbl_outputfrom vw_itmIs there a much more efficient manner of handling this column splitting?ThanksDC

View 1 Replies View Related

Splitting Decimal Value

May 29, 2008

I need to send an extract to a vendor, and for some reason they want the following:

999.34 - StrikePrice
sent as
999 - StrikeDollar
34 - StrikeFract

Tried using various conversions, but at the moment can't solve.

Thanks.

View 4 Replies View Related

Splitting Datafiles

Jan 13, 2004

I founded couple thread about splitting but not find same situation what i have.
I have server with Raid10 with big storage massive, and Test server with two 30Gb HardDrives (not into a Raid or Somethink). I have situation when in real server database grown over 30Gb and now i can not restore database copy into a test server because i have one big Data File.

Can i split somehow 35Gb Data file when i restore to test server to 25Gb and 10Gb ???

Or can u recomend some solutions

At the moment cant to do hardware upgrate.

View 1 Replies View Related

Splitting Dates

May 3, 2004

I have a table that contains agrrements and contracts with dates. Now I need to calculate some things and I'd like the rows to only have one month per row.

I have rows like:

Agreement, Start, End
ID001, 2004-01-01, 2004-04-30


If I could get these single rows that contains 4 months into a temptable like this:

Agreement, Start, End
ID001, 2004-01-01, 2004-01-31
ID001, 2004-02-01, 2004-02-29
ID001, 2004-03-01, 2004-03-31
ID001, 2004-04-01, 2004-04-30

It would simplify my calculations very much. How to do this with a query or sp?

View 14 Replies View Related

Splitting Strings

Nov 24, 2006

I everyone, I have been on the admin side of IT for the past 20 years and recently started to do some scripting (VBscript) and a little SQL.

I have developed a solution to meet the needs of some federal auditors, but not really met my needs yet.
What I have done is this.

I use MS Logparser to go out to 64 servers and copy the event logs into a DB on a SQL 2000 Ent. Server.

On the SQL server I have one StoredProcedure that parses out information from the security event log DB and put that info into a temp DB.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[stp_SecurityAuditReport]

AS

TRUNCATE TABLE SecurityEvents_Tmp

-- Parse Bank Number & UserName
INSERT INTO SecurityEvents_Tmp(DepartmentNumber, UserName, EventLog, RecordNumber, TimeGenerated, TimeWritten, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message, Data)
SELECT
DepartmentNumber = '001',
UserName = CASE
WHEN Strings LIKE '[0-9][0-9][0-9]%'
THEN SUBSTRING(Strings,1,charindex('|',Strings,1)-1)
WHEN Strings LIKE '-|[0-9][0-9][0-9]%'
THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE '-|[a-z]%'
THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE 'Account Unlocked. |%'
THEN SUBSTRING(Strings,21,charindex('|',Strings,21)-21)
ELSE SUBSTRING(Strings,1,charindex('|',Strings,1)-1)
END,
Events.*
FROM Events
JOIN EventsToLog on Events.EventID = EventsToLog.EventID
WHERE SID NOT LIKE 'S-%'


-- Update blank usernames
UPDATE SecurityEvents_Tmp SET UserName = 'NO USERNAME'
WHERE UserName = '' OR UserName = '-'

-- Update DepartmentNumbers with zeros
UPDATE SecurityEvents_Tmp SET
DepartmentNumber = CASE
WHEN UserName LIKE '[0-9][0-9][0-9][a-z]%' OR
UserName LIKE '[0-9][0-9][0-9]#%' OR
UserName LIKE '[0-9][0-9][0-9]$%'
THEN SUBSTRING(UserName,1,3)
ELSE '001' END


As you can see, we use 3 didgit numeric prefixes on all Departmental employee accounts. This is later used to produce departmenntal user audit reports.

I then have this script in a DTS that exports the report to an excel spreadsheet. (All works well for this purpose!)


DECLARE @TimeGenerated datetime
SELECT @TimeGenerated = TimeGenerated FROM SecurityEvents_TimeGenerated

DECLARE @TimeGeneratedEnd datetime
SELECT @TimeGeneratedEnd = TimeGeneratedEnd FROM SecurityEvents_TimeGenerated

SELECT DepartmentName = CASE WHEN b.DepartmentName IS NULL
THEN 'All Department' ELSE b.DepartmentName END,
a.EventID,d.EventDescription,a.UserName, a.TimeGenerated,c.Email1,c.Email2,c.Email3,c.Email4
FROM SecurityEvents_Tmp a
LEFT JOIN DepartmentList b on a.DepartmentNumber = b.DepartmentNumber
LEFT JOIN EmailToList c on b.DepartmentNumber = c.DepartmentNumber
JOIN EventsToLog d on a.EventID = d.EventID
WHERE b.Departmentnumber in (select Departmentnumber from Departmentlist)
AND a.TimeGenerated BETWEEN @TimeGenerated AND @TimeGeneratedEnd
ORDER BY b.DepartmentNumber,a.EventID,a.TimeGenerated


This combination of utils and scripts does very good for producing generic security reports for branch officers.

But now I am getting requests to justify/explain what is in these reports. The problem I have is that the information needed to delve further into the event logs is in a field called Strings. This field not only changes in length and the amount of fields within this string, but the information in this field changes depending on the type of event record it came from.


This is the Strings field from a failed logon (529)
200jenil|DOMAIN|10|User32 |Negotiate|SERVER|SERVER$|DOMAIN|(0x0,0x3E7)|6920|-|10.190.12.10|48397

And this is from Event ID 642 which was an account being created.
-|381$cmiller|DOMAIN|%{S-1-5-21-3554868564-134719009-1577582102-7972}|Jmotta|DOMAIN|(0x0,0x58F635E)|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|%%1792|-|-

Now, my script does a good job of getting the first user name out but as in the 642 event the second users name would be useful as well. This is the person that created/modified the user account.

So what I was hoping was that I could use a function (or whatever) to automaticaly split the Strings value into it's individual components and put them into an auto-sizing temp table as something like Field1, Field2, Field3, and so on until the end of string.

I could then use a case to get the information needed.

HELP PLEASE!!



Thank You,

John Fuhrman
http://www.titangs.com

View 15 Replies View Related

Splitting A Database

Feb 21, 2008

I have a large db (sql 2005 express) that is, well, basically a mess. It is not normalized properly and contains masses amount of data (due to so much repeating data). To make a long story short - this db needs to be redesigned, but management said no, so that is not an option - so please, no one suggest that that's what I do.

My application creates reports based on this db - the problem is, the sp's are slow - and when a report needs to run several reports, it takes a long time to run. The sp's and db have been optimized as best I can (adding indexes etc.,)

I was wondering if there is a way to split the db - what I want to do is just retain, say 2 years of data in 1 db, and store the rest of the data in the other db, as 2 years worth of data is 95% of what will be queried. I did copy over 2 years worth for testing, and reports that took 30 minutes in the existing db, take less than 1 minute (sometimes even faster) in the new db - a huge improvement.

My problem is how to deal with the times that I need more than 2 years worth of data - how do I query both db's to get my application to read the data from both db's so that it seems that I am only running one db? The new db would be updated daily with new data, but not the old db - so if I had to query 10 years worth of data, I need 2 years from the first db, and then the 8 yeas from the second db.

If anyone can provide some feedback or point me in the right direction of what I should research in order to accomplish the above - I would appreciate it.

If anyone knows of a better solution - please don't be shy - speak up! :)

Thanks - ann

View 10 Replies View Related

Splitting Out A Fields Value

Mar 7, 2008

Hi
I have a field that has the following value 001, 002, 003, 004
I want to select the different value as
col col2 col3 col4
001 002 003 004

Sometimes there might only be one value, sometimes none sometimes two, three or four

Thanks

View 8 Replies View Related

Splitting Of Database

Jul 23, 2005

hiI am new to SQL.I have database of 30GB.I have just heard aboutspliting Databases which helps in Performance.So please can any oneguide me in what are the steps involved in it.Anxious to know How does it work if i split my database in twodifferent location.We are using SQl 2000.Operating System - Windows 2000 serverregardsTV

View 2 Replies View Related

Splitting A String

Nov 30, 2005

Hello,I have been placed in charge of migrating an old access based databaseover to sql server 7.0. So far, I have imported all the tables intosql server, but now I have come across the issue of needing to split astring variable. For instance, in the old database, the variable forname was such that it included both first and last names, whereas inthe new database there are seperate entities for first and last name.I know that there is a way to write a script that will separate out thetwo strings by using the "space" in between the name, but I'munfamiliar how to do this. Any suggestions? Thanks!Rick

View 1 Replies View Related

Splitting Data

Oct 10, 2006

I've read that we need to split data into a training and testing data set. How do I go about that?

View 7 Replies View Related

Splitting PDF Documents

Dec 14, 2006

This may be a stupid question but I'll throw it out here, is it possible to use sql 2005 to split up pdf files into individual files by a field on the form or an index?



Thanks.

View 1 Replies View Related

Splitting A Value In A Column

Dec 4, 2007



Hello

I have a column which contains a phone number followed by a name.

How can I split off the name.

Example 0898 500 400 Joe Bloggs

Thank you.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved