varchar(MAX) as
'MSG|John|J|Smith EVN|2008-02-01|A03 ADD|101 Highland St|Mount Vernon|WA|55231 OBS|Flu|Severe OBS|Mumps|Mild'
To explain this more thoroughly, the data is organized into segments: MSG, EVN, ADD, and OBS.
Each message may have 1+ of each of these segments.
What I'd like to do is pull the contents of all OBS segments and put them into a table.
I know I can identify them using the regex of ' OBS|' but I'm not clear on how to capture anything but the first one easily. Programmatically, it's simple. SQL I'm not so sure.
Can anyone give me a gentle nudge in the right direction?
I have a requirment to take data from a large set of tables where thetotal number of these tables may change on a regular baisis andoutput into another table. All the tables willl have the samecolumns. Frequency is being debated but it maybe as much as once perhour.Example1) I need to choose all the following tablesselect * from dbo.sysobjects where name like '_CPY%.2) then I need the followingfor each of the tables found above, I need the outfrom from each ofthose tables to be inputted into another table. basically, I wouldwant the following output from each of the tables found in step 1select machineid,name from _cpy_offermanager_6783) In the end I would have something like dbo.ALLCPY with recordscombined from all other _CPY tablesRon Sorrell
I am writing a package that will process delimited flat files that will come in one of a few different versions. Within each flat file, the number of delimited columns will be the same, but each version of the file has a different number of columns. I have tried configuring the flat file data source to expect the version with the largest number of columns, but it will then throw away rows that have less than this number of columns (warning: There is a partial row at the end of the file).
Is it possible to use a single flat file data source that will work with all of the different width files?
Im trying to determine the best way to store data gathered from a form that a user will fill out online. The form is dynamic and is customized at run time based on group-specific criteria. The end result is a form that might have 3 extra text boxes, 2 extra sets of radio buttons and a freeform textbox, whereas for another group, there might be a slightly different set of input fields. Now comes the issue of storing this data. Since the fields can be somewhat dynamic, it could get tricky to define table columns for each possible input field. So Im considering storing the data as xml. Has anyone else had to build custom forms and ended up storing the data as xml ?
I want to load flat files into a single table. But the flat files can have variable number of columns upto a maximum of 10 columns. The table in my database has 10 columns in it. So in case if I load a flat file having 6 columns then rest of the columns in the table will have nulls. I don't want to use script task for this as I am not good in writing C#code.
I am having a problem trying to pull data that has apostrophe in them. How can I do this? I get this as an error
Msg 105, Level 15, State 1, Line 14 Unclosed quotation mark after the character string ''.
Select Name From Table Where Name IN (CHILDREN'S ANES ASSOCS-CHOP,CHILDREN'S HEALTHCARE-CHOP,CHILDREN'S PSYCH ASSOC-CHOP,CHILDREN'S SURGICAL ASSOC-CHOP)
I'm trying to pull data from about 30 progress databases using DTS andscheduling the jobs to run monthly. I'd like to pull data betweenspecific dates, but for some reason, I can't figure out how to filterthe data on the progress side.I want to run a query that will pull all data fromprior-month/8/current-yearandcurrent-month/15/current-yearI'll also have to account for when it's january, make it december ofthe year before.Any ideas?thanks,M@
I got this piece of code from one of your articles:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=c: est.xls', 'Select * from [Book1$]')
Question is, how can I use a fully qualified path for the xls file name if the file is located on the network elsewhere?is that possible with this option?
Hi friends, I am new to MSAS world. I need help related to this. I want to pull data from MSAS cube programmatically. Only way I know is thru ODBO, but that won't help me in this case, cause I might have to drill down upto all possible intersections stored in MSAS (at least all of the stored members). Doing this thru MDX could be humongous thing, at least manipulating data taken out using ODBO. I might be missing something out here. Can anybody help. It would also help if somebody can tell me if any other approach is poosible.
Can any one tell me how to transfer bulk data from SQL Server6.5 to SQL Server2000.I have tried bcp out and bcp in to SQL Server2000 but it is taking very long time.Is there any way that I can pull the data into SQL Server2000 from SQL Server6.5.Can I use DTS feature of SQL Server2000 to pull the data from SQL Server6.5..
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS 2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype. 3)When I run this package on my machine against the production server, it works perfectly. 4)When I run this package on my test SQL Server against the production server,it works perfectly. 5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
eventually I started using DERIVED Tables and Sub queries within them if needed. I like choking down all the queries in the selects joining them and having your result set select there to choose from all the aliases, it also resolves while doing this. So much easier IMO than using CTE's or TEMP Tables. I was big on temp tables for a while...
I'm curious though if you want to count a type of criteria in a column do you use a standard case in your inner query choking it down just for those particular counts? Then do another case on the other criteria and END AS with the alias name?
E.G.
CASE WHEN COLUMN1 = PIZZA THEN COUNT(COLUMN1) END AS PIZZACOUNT CASE WHEN COLUMN1 = ROOTBEER THEN COUNT(COLUMN!) END AS ROOTBEERCOUNT ETC...........
I have a table, multiple columns, thousands of rows.
Six of the columns is the data that i need to work with...
col1, col2, col3, col4, col5, col6
col1 and col2 - go together - example. col1 = amount col2 = description col3 and col4 - go together col3 = amount col4 description col5 and col6 - go together col5 amount and 6 description
i need to pull search the table based on an auto number "id" and pull in the necessary two columns that correspond with a set value in the description.
example:
if col4 has "fee applied" in the description i need to pull the amount.
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS 2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype. 3)When I run this package on my machine against the production server, it works perfectly. 4)When I run this package on my test SQL Server against the production server,it works perfectly. 5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
Ok I wrote a SSIS package that will pull down data from my AS/400 and populate a SQL Server table with the data.
1)The data is being pulled from my China configured AS/400. It is configured to handle DBCS 2)The SQL Server tables are configured to handle DBCS by using the nvarchar datatype. 3)When I run this package on my machine against the production server, it works perfectly. 4)When I run this package on my test SQL Server against the production server,it works perfectly. 5)When I run this package on my production SQL Server it brings down all the records, but does not bring down all the fields. Most of the character fields are left blank.(not all)
I do not understand why this is doing this. Can anyone shed any light on this problem? Thank you.
Server1 = Previous "Test box", Windows Server 2003 Standard, SQL 2005 Standard SP1 with hotfixes, 2 processor 4 gig RAM Server2 = New "Box for production", Windows Server 2003 Enterprise, SQL 2005 Standard SP1 with hotfixes, 2 Dual Core Processor, 16 gig Ram
This is for a datawarehouse environment. I pull a lot of data every night from an Oracle database. On Server1 the process took about an hour to pull all my data accross, on Server2 it takes more than 3 hours. I use SSIS packages to pull the data accross. Even just running an OPENQUERY statement takes a lot longer. On Server1 returned about 150K record in 1.5 minutes and now on Server2 it only return 40K. Have I missed a setting on my re-install. It the same SQL build number, SP_Configure has exactly the same settings and everything seems the same. I ftp'ed a file from the oracle box to each of my boxes and had very similar results, so it doesn't seem to be a network issue.
Any help would be much appreciated, is there anything that would cause SQL to pull data 3 times slower from an external datasource?
Hey guys, I have created an asp.net page where users can select multiple items and then submit the form. I would like to return related items back. The catch is, I want to only return items that are related to all of the selected items. I've created a SQL Procedure that puts each of the inputted item's ItemId in to a temp table, I have a second table called RelatedItems which I use as my junction table that has ItemId, and ReleatedItemId, I then have my Item table that has the data I want to get to (I've excluded this because I have no trouble pulling out data once I have an ItemId) I can pull out all related ItemIds with a simple join, however I don't know where to start when it comes to pulling out only items related to all ItemIds in the @TempTable. Any help or suggestions would be great. Thanks, Matt
I need to do some advanced formatting in the text file, because the data in the text file is bit complex. I have some knowledge of using BCP, BULK INSERT and bit about FORMAT FILES but I want to do some advanced formatting(using IF condition and all) to pull the data from text file. Can anyone please tell me how can I perform bit advanced formatting using BULK INSERT?
In an approach of building an ETL tool, we are into a situation wherein, a table has to be loaded on an incremental basis. The first run all the records apporx 100 lacs has to be loaded. From the next run, only the records that got updated since the last run of the package or newly added are to be pulled from the source Database. One idea we had was to have two OLE DB Source components, in one get those records that got updated or was added newly, since we have upddate cols in the DB getting them is fairly simple, in the next OLEDB source load all the records form the Destination, pass it onto a Merge Join then have a Conditional Split down the piple line, and handle the updates cum insert.
Now the question is, how slow the show is gonna be ? Will there be a case that the Source DB returns records pretty fast and Merge Join fails in anticipation of all the records from the destination ?
What might be the ideal way to go about my scenario.. Please advice...
My goal is to show the most recent 12 months of data including the current month based on a patients discharge date (relative to the query execution time). There could be many years of data so I want to be sure I am pulling the most recent 12 monthsThis seems to work (on the surface anyway … not quite sure if this is the best logic to use).
Hello All, I have an application pulling data from SQL Server 2005 which is running on windows mobile 5 devices, and RDA is setup and working perfectly over http and this is in the production for long time, but we have decided to implement SSL for transportation recently and we have a certificate installed to the server and i am trying to pull data using https and getting "the request to send data to the computer running IIS has failed" message, only RDA stopped working over SSL, i can access scripts and other resources from the device using https (I made code to trust any certificate for any web requests) the link to the dll is accessible from outside, i have installed the root certicate on the server, the virtual directory has been changed to communicate via https on web synchronization wizard, then problem occurs, otherwise it will work ok, i am totally stuck at this point, is this a problem with port setup port 443 is opened, the issue is clearly a SSL issue. any help would be appreciated..what are the things to be done to implement SSL for RDA, i don't see anything more in msdn. thank you so much! sush
I have sql pulling back data from 2 tables (Ticket, Assignment) matching on an ID.
however the Assignment table can have more than 1 record for a matching ID in the Ticket table so is bringing back rows for each of these entries HOWEVER i only want 1 row returned matching on the FIRST record in Assignment table (on earliest DateAssigned field)
Here's my code
SELECT t.TicketID, CreatedByUserID, CreatedForUserID, DateCreated, a.DateAssigned FROM Ticket t INNER JOIN Assignment a ON (SELECT TOP 1 TicketID FROM Assignment WHERE [TicketID] = t.TicketID ORDER BY DateAssigned ASC) = t.TicketID
WHERE (CreatedByUserID NOT IN (SELECT u.UserID FROM Users u INNER JOIN Profiles p ON u.UserID = p.UserID
I know this is an easy one for most of you but this is new territory for me. I am trying to link a production application database to an existing SMS database. Both of these databases are SQL 2000. I have sa access to both of them. I have an accreditation application that deals with internal processes within our active directory network. We are using SMS database collections to provide the discovery for reporting of this application database and I want to compare the collection to the existing data between the 2 databases.
For simplicity I will define them both. Application Database we will call €œA-Database€? SMS Database we will call €œB-Database€?
I have gone into the A-Database and gone to linked Servers under the security node in enterprise manager. I created a new linked database and selected the SQL Server option in the general Tab. In the Security Tab I selected the €œBe made using the security context€? and used €œsa€? for the Remote login and the CORRECT sa password for the With password option. The server options tab remain the same with €?RPC, RPC Out, & Use Remote Collation€? selected.
It appears to connect but there is not any Table or Views showing up. What am I doing wrong here and what do I need to do to link these 2 database engines so I can move forward?
I have ssis package that pull data from SAP (Using ADO.net connection) to SQL server every night but i have noticed that all data from source is not getting pulled by package . package losing some amount of row.
I have SSIS package which is created to pull data from oracle server. The package is running fine when executing from BI studio. But failing when i execute as a job. I am getting teh below error message.
Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". End Error Error: 2007-10-15 16:04:15.60 Code: 0xC020801C Source: Data Flow Task O... The package execution fa... The step failed.
Appreciate if somebody can provide some inputs on this .
I am trying to pull data from an Oracle Db using SSIS. If I use the Table/View option in the Access Mode option on the OLE DB Source component, it works fine. But when I use the SQL Command option, the processing get stuck at Pre-Execution stage.... (for days).
HI All, I want to pull data from mysql dbms to sql servr 2005, i have wrote the following codes but it takes more than an hour and half which is not visible. Thus is there any consideration to consider and reduced time it takes. For your information i am going to use SSIS packags, there is no any tranformation, it is direct dump. Here is the code i am using,
SELECT * FROM OPENQUERY (Server_1,'
SELECT t3.Column11 as Column1, Column12 as Column2, Column13 as Column3, Column14 as Column4, Column15 as Column5, Column16 as Column6, Column17 as Column7, Column18/1000 as Column8 FROM table1 t1 INNER JOIN table2 t2 ON t1.ColumnId = t2.columnID INNER JOIN Table3 t3 ON t2.columnId = t3.columnID WHERE t1.Column4 > Sometime ')
I have a date that I get from my now() function that returns:2/10/2004 2:17:16 PMI would like to be able to break this value up. Is there a function thatallows me to pull whatever I want out of this?For example, if I just want the year 2004 pulled out (for a drop downlist I'm creating) what would the function be to do that?And If I just want the day, 10 pulled out, then how would I do that?part of the problem is that depending on the month, is the number ofdigits one counts from the front. 10,11,12 add one digit to the front ofthe string, making this difficult.I'm creating a search between dates so someone can select the day, monthand year as beginning value, and the end value. So how would I go aboutdisecting this string?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
