Concatenate Strings After Assigning Text In Place Of Bit Strings
Feb 19, 2007
I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
View 2 Replies
ADVERTISEMENT
May 2, 2008
Hi,
Whats wrong with the following T-SQL?
DECLARE @SQL NCHAR(4000)
SELECT @SQL = 'xxxx'
SELECT @SQL = @SQL + ' yyyy'
PRINT @SQL
I'm only seeing 'xxxx' in the debug window so it appears that its not adding the 2 strings?
Weird?!?
View 8 Replies
View Related
Nov 10, 2006
I have a table within a table that contains descriptions of work done as strings. Some of these need to be edited after a report is generated so it needs to be exported to Excel.
As everyone knows Excel can't process a table within a table so I need a way to concatenate all the separate row entries in the nested table to one Field entry so the nested table is no longer necessary.
Is there a way to recursively concatenate strings from the report designer?
The number of rows that need to be concatenated is variable so it has to be flexible.
View 2 Replies
View Related
Jul 20, 2005
I am trying to export data from a SQLServer database into a text fileusing a stored procedure. I want to be able to read it and debug iteasily; therefore, I want all the columns to indent nicely. This meansI need to append trailing spaces to a text string (such as "Test1 ")or append leading space in front of a text string that contains anumber (such as " 12.00"). Now, the stored procedure works fine whenI run it in Query Analyzer. But it doesn't work correctly when I runit using ISQL - All the columns are not indented. I am wondering whyit doesn't work in ISQL.This is what I want, and this is also what I get when I run the storedprocedure using Query Analyzer:Test1 , 2,Test1.txt , 1.00, 1.00Test22 , 2,Test22.txt , ,Test333 , 2,Test333.txt , 30.00, 30.00This is what I get if I run the stored procedure using ISQL(isql -S myserver -E -w 556 -h-1 -n -d mydb -Q "exec MyTest"):Test1, 2,Test1.txt, 1.00, 1.00Test22, 2,Test22.txt, ,Test333, 2,Test333.txt, 30.00, 30.00You can see that the result from ISQL has the following differences:1. It puts a space in front of each row.2. It appends enough spaces at the end of each line to makethe line length to be exactly 61 characters.3. It gets rid of the trailing space from each column.4. It leaves only one blank space if the column has nothingbut a serie of spaces.The following is the stored procedure that I am testing:create procedure MyTestasset nocount oncreate table #Test(Field1 varchar(10) null,Field2 varchar( 5) null,Field3 varchar(20) null,Field4 varchar(10) null,Field5 varchar(10) null)insert into #Test values( "Test1 ", " 2","Test1.txt ", " 1.00", " 1.00" )insert into #Test values( "Test22 ", " 2","Test22.txt ", " ", " " )insert into #Test values( "Test333 ", " 2","Test333.txt ", " 30.00", " 30.00" )select Field1 + "," +Field2 + "," +Field3 + "," +Field4 + "," +Field5from #Testdrop table #TestgoStrangely, the differences #3 and #4 only show up when I use theSELECT statement on a table. They don't show up when I use SELECTstatements to show constant text strings or string variables, likethis:set nocount onselect "Test1 " + "," +" 2" + "," +"Test1.txt " + "," +" 1.00" + "," +" 1.00"select "Test22 " + "," +" 2" + "," +"Test22.txt " + "," +" " + "," +" "select "Test333 " + "," +" 2" + "," +"Test333.txt " + "," +" 30.00" + "," +" 30.00"The result is like the following if I use constant text strings orstring variables:Test1 , 2,Test1.txt , 1.00, 1.00Test22 , 2,Test22.txt , ,Test333 , 2,Test333.txt , 30.00, 30.00I need to run it from ISQL because that is how I run _all_ my otherstored procedures. I don't want to do anything differently justbecause I need to run this stored procedure.Thanks in advance for any suggestion.Jay Chan
View 4 Replies
View Related
Sep 12, 2007
Hi--
I am uncertain how to do this. I am thinking it could be done in a script component, but after a day of experimentation, I'm not getting any closer. I'd also like to know if there is another component I may be able to use for this.
I have data coming from an Excel Spreadsheet that looks like this:
CustNumb Invoice
1 a
1 b
2 c
3 d
3 e
3 f
I would like an output that looks like this:
CustNumb Invoice
1 a, b
2 c
3 d, e, f
I am not even sure if I should be trying to do this in the subroutine that looks at each row or the one that looks at the entire buffer.
Thanks for any help or ideas...
Hilary
View 10 Replies
View Related
Dec 17, 2003
I am trying to build a simple search engine using Sql Server 2000 to scan information about approximatelly 20.000 products.
Heres what I am doing:
I created a table called keywords that contains a reference for each product.
keyword -> varchar(100)
items -> Text
keyword data example:
[keyword] [items]
car 1, 3, 5, 7
blue 3, 5
compact 1,7
I am not using clustered index.
To search basically I run the "AND" or "OR" to select the keywords I want to target.
I need to run another select that would compare the data in the items field depending of the condition selected. If "AND" clause is used I would need to compare all the items that contains the same reference, for example:
looking for car compact using "AND" clause
result = 1
looking for car compact using "OR" clause
result = 1,3,5,7
There is no table that holds references. The items are stored in a text field in the keyword table. I can compare data using script like AsP spliting the items by comma or space, but that can be too slow and use up a lot of RAM. Another solution would be to use a table to hold the references but that would affect performance dramatically because of the large number of records created and storage space used. One example, if I have 60.000 keywords and each keyword has an avereage of 200 references, I would have to generate 12 milion records.
I want to know if there is a function or routine in SQL server to compare matched references on the fly in the server between two or more fields and how should I do it.
In addition, in this scenario, how should a clustered index help?
Thanks
Rodrigo
View 2 Replies
View Related
Nov 10, 2007
Hi everyone,
You have helped me resolved my previous problem with the LIKE statement, and now I'm running into this TEXT STRING problem that I desperately need your help and guidance again.
The following is the set of various descriptions in a PRODUCT_DESC field. I need to be able to calculate the Squared Meter of these products. As you can see, I need to be able to extract the part in the middle (like 2x60YD, etc.) for each record and perform some sort of calculation and conversion. The problem is that I can't find a way to do this effectively. Can someone please help me? Thanks.
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
CH PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
View 10 Replies
View Related
Jul 23, 2005
I am trying to build a query on a SQL2000 text field which contains XMLstring. The query is like "select requestnumber from history whererequestnumber is like '%re1%'". As you can see in the following samplerecords, the xml string has database structure and the requestnumber isa node of the XML. I wonder if it is possible to have SQL server parsethis field and allow me to do the query. If not, any suggestion wouldbe appreciated as to how to store XML data in SQL2000. I am not sureif I misused the SQL2000 XML feature correctly. So far I pass the rawquery result to ADO and manipulate it in XMLDOM.The table is to capture history of changes in any record in mydatabase. So I need to keep it simple so any record from any table canbe stored in here. The structure of the table is like this:sysObjectNumber(int, not null)recordKeyValues(char(30), not null)archiveTime(datetime, not null)history(text, null)A sample record would be like the following:sysObjectNumber recordKeyValues arvhiveTime History=============== =============== =========== =======1728725211 ABC 2005-03-25 8:09:56.700<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>null</threshhold><usedby>user1</usedby></history>1728725211 ABC 2005-03-28 11:01:14.407<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain></remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user2</usedby></history>1728725211 ABC 2005-03-28 11:46:12.723<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user1</usedby></history>1728725211 ABC 2005-03-28 11:46:35.273<history><partnumber>ABC</partnumber><type>2140461537</type><color>1</color><remain>4</remain><lastdatein></lastdatein><lastdateout></lastdateout><lastquantityin></lastquantityin><lastquantityout></lastquantityout><threshhold>4</threshhold><usedby>user4</usedby></history>
View 1 Replies
View Related
Aug 23, 2007
HI All. I'm trying to tweak the Transfer Logins task to exclude Windows Logins that are local to the Server (e.g. servernameusername) which obviously can't be transferred off the server. Annoying that we have a couple of local Logins on this system instead of all Domain Groups, but we're stuck with them due to firewall issues, and a policy excluding SQL Logins.
My idea is to create a text file as part of my Package that lists Logins to be Excluded From the Transfer - I think I then need to create a New File Connection to the Text File as a Connection Manager, then somehow get that data into a Variable, and then use an Expression to populate the 'LoginsList' Collection from syslogins where loginame not equal to logins in my textfilevariable?
Or maybe I'm over complicating this, and there's an easier solution? Lots of info in Books Online about Expressions and Variables, but having trouble finding examples that I can use. As a DBA, this is my first foray into SSIS, and as you can possibly tell, I'm floundering....
View 1 Replies
View Related
Aug 22, 2002
I have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description
1234 1 Talk to Mr. Cartwright about
1234 2 new issues with patent law. Conferece
1234 3 call to discuss payment of past bills.
I need to see
1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
View 4 Replies
View Related
Jul 18, 2007
Hi everyone.
Is it possible to put in a string value as one of the results? I'm trying to produce a string in the data table is the value is null so I want to do something like:
iif(somevalue is nothing, 'Other', somevalue)
Thank you in advance.
View 1 Replies
View Related
Nov 11, 2006
I am trying to develop a web site. I have a local ms sql database on my machine.
I am trying to connect to a ms Sql database on a goDaddy server from the application.
I am trying to understand the connection string and its total properties.
here is what I think should be in my web.config file
<
add name="Personal" connectionString="Server=whsql-v12.prod.mesa1.secureserver.net;
Database=DB_XX10;
User ID=myID;
Password=myypassword;
Trusted_Connection=False" providerName="System.Data.SqlClient"
/>
<remove name="LocalSqlServer"/>
can someone please tell me where I am going wrong, Thanks for your help.....
View 7 Replies
View Related
Feb 7, 2007
I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>"
SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @PROPERITY_ID)">
<SelectParameters>
<asp:QueryStringParameter Name="PROPERITY_ID" QueryStringField="key" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string
Dim y as string = "'" // " ' "
key = Request.QueryString("key").trim(y.tochararray)
But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up?
string y = "'";
key = Request.QueryString["key"].trim(y.tochararray());
View 3 Replies
View Related
Aug 28, 2007
How can i make a sum (concatenation) of strings of one column in a table.
for example i have a table like this
field1 field2
1 abc
1 bcd
2 sdf
2 sdd
I want to get these strings added group by field 1
Thanks
View 5 Replies
View Related
Nov 23, 2007
I am submitting a telephone number into a table. I have 3 boxes for the telephone number. Telephone1,Telephone2,Telephone3. I need to insert the values of the 3 text boxes into a column called phone in my table.
so like
InsertCommand="INSERT INTO customer_mod (phone) Values (@Telephone_1)
<asp:formparameter name="Telephone_1" formfield="Telephone1+Telephone2+Telephone3" />
I don't think that is gonna work, so can you please help me make that code work?
View 3 Replies
View Related
Nov 24, 2007
I developed an application and deployed it onto a hosted provider. I use SQL 2005 Express for development SQL 2000 for production. Both systems work.
I backed up my production database and wish to use it for my development testing. I restored it as a SQL 2000 database on my development machine and changed my connection strings to point to it. I added it as a new Data Connection in Database Explorer and can see all of the data through it, but I see that all the tables now have the owner name in parenthesis as part of the table name.
My application now fails trying to find any of the tables. It works if I include the owner name as part of the SELECT statement. So much for data separation.
I don't know where to start. Is this an issue between SQL 2005 Express and SQL 2000, or is it an issue between SQL and ASP.NET, or is it a hosting provider issue, or what?????
View 6 Replies
View Related
Jan 24, 2008
Hey guys, don't know if this is in the right place but here goes.
I'd like some advice on optimizing the way I write my code. Here's the scenario. I have a select string for 3 different tables. One for the main gridview, and two others that are binded to dropdownlists.
So I created a Sqldatasource1,2,3 and had to create the connection strings for each one.
ex. SqlDataSource1.ConnectionString = "blah", SqlDataSource2.ConnectionString = "blah", SqlDataSource3.ConnectionString = "blah"the thing is, the connection string itself is the same. Is there a way to consolidate that? So that when I use a select/update/insert command for each sqldatasource i dont have to have another connection string? Also, having multiple connectionstrings, does that slow down the performance?
View 3 Replies
View Related
Feb 28, 2008
In an ASP.NET app, where is the preferred place (or your preferred place, I should say) to store any SQL Statements (updates, selects, stored procedure calls, etc.) you have? I have been using a key/value pair in AppSettings in my web.config but just feel like that isn't the best way. What say you?
View 4 Replies
View Related
Mar 14, 2008
Hi,
Im struggling to work out the connection string to my ms sql database.
My website is being hosted with freezone.co.uk and I have a ms sql database called "aspnetdb". I usually log into the database with a username but no password. I don't no what other information is needed to create my connection string?
Thanks, Mark Lawton
View 2 Replies
View Related
Apr 4, 2006
I've got a GridView, a datasource, 2 textboxes and a button. If I choose to bind the GridView to the datasource the correct data is shown. But if I modify the datasource and chooses the TextBoxes as Parameter Controls it doesn't work out as I expected.
Can I please have a step-by-step response which will work? And expecially if one if the TextBoxes eguals nothing/null. Because this is very important, too, and that part doesn't work either.
View 1 Replies
View Related
Jan 9, 2001
This seems so simple but I can't find any examples to do it..
I have the following in a text file "Lastname, Firstname MI" and I want to select each part separetly.. What would be the SELECT statement to get the Lastname up to the comma, then the Firstname after the comma, then the MI after the last space..
Thanks for the help, and any reference to learn more.
View 2 Replies
View Related
Feb 27, 2001
I have a varchar field that contains answers to questions separated by commas. Say there are 4 questions for each user. Here is an example of what the table would look like:
User Answer
1 Good,Fair,Good,Bad
2 Bad,Good,Good,Good
3 Fair,Good,Bad,Fair
I need to write a stored procedure to report off of that separates the Answer field into 4 different columns. How can this be achieved? Any assistance would be greatly appreciated.
View 1 Replies
View Related
Oct 11, 2006
In MySQL I can represent a string as the following:
X'6162636465'
However, I can't seem to find an equivalent that is supported MS SQL from the last hour and a half of searching the net. Does anyone know of any way to do this?
View 2 Replies
View Related
May 13, 2004
I have a column called professional_degrees in my table
and it has the following data
professional_degrees
bs,ms,mba
bs,mba,
bs,ms,bs,ms
mba,mba
bs,mba
In the above data u can see some of the degrees are repeated.So how can i find them and delete them from the table.
Thanks.
View 11 Replies
View Related
Apr 1, 2008
I have a small problem that I can't figure out. Here's my table:
----------------------------------------
Members
----------------------------------------
ID | Name | Image
----------------------------------------
When a user signs up to become a member, they can upload an image, which will be stored under Image. It will also be stored in a folder. For example, if the user with the ID of 14 uploads an image, it will be stored in 'livefiles/Members/14/image.jpg'. If the user does not upload an image, NULL is stored under Image, and the user has a default image, which is stored under 'livefiles/MembersDefault/1/image.jpg'.
What I want to do is return the path of where the image is in the folders. Basically...
If Image is not null
SELECT 'livefiles/Members/' + ID + '/' + Image AS ImagePath
If Image is null
SELECT 'livefiles/MembersDefault/1/image.jpg'
I'd rather not do this with an IF statement, I'm doing a lot of other SELECTS, and I don't want to have to copy all that code. Here's what I tried...
COALESCE (NULLIF('livefiles/Members/' + CONVERT(varchar(20), ID) + '/' + Image, 'livefiles/Members/' + ID + '/'), 'livefiles/MembersDefault/1/image.jpg') AS ImagePath
but that gave me an error saying:
Conversion failed when converting the varchar value 'livefiles/Members/' to data type int.
View 4 Replies
View Related
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
Dec 11, 2006
Hello everyone, I have this SP and can't get it to work on my SQL2000 server. I just can't seem to figure out what syntax error I am making.
(This works on my test SQL2005 server)
Truncate Table [SecurityEvents_Temp]
Insert Into SecurityEvents_Temp (
[BankNumber],
[UserName],
[DomainName],
[EventLog],
[RecordNumber],
[TimeGenerated],
[TimeWritten],
[EventID],
[EventType],
[EventTypeName],
[EventCategory],
[EventCategoryName],
[SourceName],
[Strings],
[ComputerName],
[SID],
[Message],
[Data]
)
Select
[BankNumber] = '001',
[UserName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 3)
[DomainName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 4),
[SecurityEvents].*
FROM [SecurityEvents]
JOIN [EventsToLog] on [SecurityEvents].[EventID] = [EventsToLog].[EventID]
WHERE [SID] NOT LIKE 'S-%'
Query Output:
quote:
Server: Msg 170, Level 15, State 1, Line 28
Line 28: Incorrect syntax near 'Strings'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'Strings'.
Thank You,
John Fuhrman
http://www.titangs.com
View 16 Replies
View Related
Aug 23, 2007
I need to convert string data so that I can use it in a view.
example
xxx-xx-xx
xxx-xx-xx
xxxxxxx
xxxxxxx
xxxxxxx
xxx
xx
I need to be able to add the dashes in for the data that has 7 characters and disregard the data that is less then 7 characters
Jeff
View 8 Replies
View Related
Mar 10, 2008
Guys,
I have following table where I need to concatenate varchar column.
For example
IDCOMMENT
__________________
1JOHN SMITH
1 SURRENDER
1TO COPS
I want to be able to group by ID and concatenate COMMENT field to 'JOHN SMITH SURRENDER TO COPS' for ID 1
Is there any way to accomplish this?
Any suggestions and inputs would help
Thanks
View 5 Replies
View Related
Jan 27, 2008
I find myself between rock and hard place.
My requirement is to simply output a flat file from an OLE DB source containing three types of records.
The output records should look something like this:
Type 1 contains field1, field2, field3
Type 2 contains field1, field3, field6
Type3 contains field1, field4, field5
The source table contains all six fields.
If I simply map the input to output columns, I get all fields output in each record, which is not what I want.
So I run the source through a derived column transformation, inspecting the record type field and creating a new column containing the relevant fields concatenated together, e.g field1 + field 2 + field3 for the Type 1 record.
The problem is that the fields are now all scrunched together, i.e all the trailing spaces are being stripped out of each field during concatenation.
Is there any way to string these fields together while maintaining the original field length?
I'd like the output to look like
ABC FGH XYZ instead of
ABCFGHXYZ
View 7 Replies
View Related
May 7, 2008
Hi All
I've got a list of IDs seperated by commas and I want to get each indivdual ID and insert them into a table. Has anybody got any ideas how this can be done?
Thanks
View 6 Replies
View Related
Nov 6, 2006
I have created a SSIS package, and got it to perform as I want.
I am having a problem though with connecting to my data source (OLE DB Source) using SQL Server Authentication.
If I use Integrated security, I have no problems. If I use SQL Server Authentication, I get errors.
Here is the connection string that works:
Provider=SQLNCLI.1;Data Source=.SqlExpress;Integrated Security=SSPI;Initial Catalog=MyDataBase
Here is the connection string that fails:
Provider=SQLNCLI.1;Data Source=.SqlExpress;Persist Security Info=True;Password=**********;User ID=myUserid;Initial Catalog=MyDataBase
The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.
Here is the error I am getting:
[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.
I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.
It seems I am missing something really basic here, but am not seeing it.
Any thoughts?
View 12 Replies
View Related
Aug 20, 2007
Hi,
I have a strange problem I can't solve. I have a log table where a have for example a timecreated column where I store dates in the format 0704212112538707
What I want to to is to search for log posts that are in between two dates.
I have this SQL query: select * from log where timecreated >= '070421' and timecreated <= '070422'. With this question I only get hits from the date 070421. I would like to get both 070421 and 070422. I get no difference if I use < or <=.
How would I solve this
Best /M
View 4 Replies
View Related
