Hello,
I have a transformation in which the column of data at the flat file source is nine characters long, and typically contains a string of six or seven zeros with a non-zero number in the last two or three characters. If none of the records in that column were an empty string, I think I could get away with this:
The destination is a SQL Server 2000 table, and the column is of type Integer. What do I do when Col004 is an empty string? I've tried a couple of different IF statements, but they have not worked. Empty strings need to become zero values.
I am in the process of converting DB2 mainframe data to SQL2005. During the conversion I ran into an issue with the DB2OLEDB provider not handling strings with embedded nulls. With the help of the Microsoft Tech support folks I was able to get a fix for the x64 DB2OLEDB provider to handle strings with embedded nulls.
The problem now however is that it appears that when the Data is copied to the Pipeline buffer it is truncated at the first null regardless of the DT_STR length. I have read where .NET is supposed to handle embedded nulls in strings but I am not sure what I need to do to get SSIS packages to handle this situation.
I know when I preview the query in the OLEDB provider within SSIS the data is correct, but as soon as it is passed to the SQL connector, a scripting component or a data conversion component the string is truncated at the first occurence of the embedded null.
I also tried doing a straight copy from the Data provider to a flat file, but the strings are once again truncated.
Is anyone else experienced any other similiar problems or found any resolutions to this type of problem I am getting down to crunch time on this conversion project and any help would be most appreciated.
************* Edited by moderator Adec *************** Inserted missing < code></ code> tags. Always include such tags when including code in your postings. Don't force the moderators to do this for you. Many readers disregard postings without the code tags. **************************************************
Well met,
Let's say I have a web form that allows users to insert and update records in a SQL database. Is it better to set empty web controls (textbox, etc.) to DBNull or let it go as an empty string into the database?
I have always (or at least intended to) treat NULL and empty strings separately in my SQL querying history. Now I have run across something that mystifies me (but probably shouldn't) that I would like an explanation for.
Consider this bit o' code:
DECLARE @ORDER CHAR(10) SET @ORDER= ( SELECT NULL ) IF @ORDER <> '' PRINT 'Not an empty string'
IF @ORDER IS NULL PRINT 'It is NULL'
Run this and you will get:
It is NULL
I was expecting:
Not an empty string It is NULL
Why is NULL not passing the 'not an empty string' test? In other words, how does NULL = '' ? Is NULL cast to an empty string for this comparison?
I have a SQL database where I am attempting to perform a complicated query that I cannot seem to figure out. I am using SQL Server.
I have 4 tables (TableA, TableB, TableC, and TableD). TableA and TableB are guaranteed to have a relationship.
TableC and TableD are guaranteed to have a relationship.
The trick is, I need to link between TableA and TableC essentially using a LEFT JOIN. I need to retrieve all of the values from TableA regardless and the information from TableC and TableD if there is a link, if there isn't a link, then the values from TableC and TableD need to be empty strings.
Does anyone know how I can do this? I've been trying for the last 5 hours without any luck. I feel I'm close, but there is something I feel I'm overlooking.
Am I right in assuming that when I have a column where all fields contain NULL, this does not increase the total data storage size if my database? Also, what kind of impact would it have on performance?
And what if I inserted "" in varchar columns? I would think the increase in size would be marginal?
The reason I'm asking is that I want to use an existing table and stored procedures for another purpose, but only need half of the columns. But it would significantly simplify application development.
I am having data where there are empty string in the business keys which should be used for Slowly changing dimesnion type 2, how do i over come this as due to empty strings i am getting new rows even though the rows havent really changed.
example of data is name and salary are business keys
name salary age address dev 23 klddldldlk sdfg 24 34 kdlddlkd
when the same is given as input the row dev 23 klddldldlk is coming as anew row where it already exists how do i over come this
Will the fuzzy grouping task match a null value to an empty string (or spaces)? I've got 5 columns I'm matching on, and one of them may be null for certain rows but an empty string for others. Given the 4 other columns may match, will this difference stop similar columns being grouped together?
(Someone's modified my grouped data since it was deduped, which takes a while, and I'm hoping for a quick answer on this).
We have created SSIS package to load a text file into a table. Source system shares 10 text files and recently they stopped generating data for one of the text file (comping empty), after few months they will start generating the data for the empty file batch processing.
The Issue here is Data Flow task is getting failed while loading empty text file into table. How to handle this empty file load issue in SSIS package.
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
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.
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:
I am executing script like this. How to check for the errors if "master..xp_cmdshell @bcpCommand" fails. Is there any way to verify that BCP is completed successfully
SET @FileName = 'E:TestBCPOut.txt' SET @bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "' SET @bcpCommand = @bcpCommand + @FileName + '" -c -U -P'
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.....
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());
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
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?
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?????
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?
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?
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
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.
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.
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.
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?
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.
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.
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)
[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'.
