I have a database that has a table with a lot of delimited data. I need to extract some of this data, preferably into a sql view, that I can name the fields. The data looks like this:
|7700|6|0|work center|24|8|XLT
What I need is:
field name 1= 7700  field name 2= 6  field name 3= 0 field name 4= work center  field name 5=24, ETC
I'm not sure how to count the delimiter | and get the data between | & |. Or maybe that isn't the way to do this.
Here is a sample of what I did in Crystal Reports but I need a view to make this usable.Â
Split ({usrc1.application_data}, "|") [22] Â This gives me the data between delimiters 22 & 23.
Hi, I am new to the whole concept of ASP.NET and codebehind (i am using c# and i am also new to that) i currently trying to get my codebehind to connect to a SQL Server express 2005 database on page load however i am getting this error thrown back at me when i run the aspx page on my localhost, Cannot open database "db_revival" requested by the login. The login failed.Login failed for user '12042HOPEASPNET'. 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: Cannot open database "db_revival" requested by the login. The login failed.Login failed for user '12042HOPEASPNET'.Source Error:
Line 18: { Line 19: SqlConnection conn = new SqlConnection(@"Data Source=12042HOPESQLEXPRESS;Initial Catalog=db_revival;Integrated Security=True"); Line 20: conn.Open(); Line 21: Line 22: } Source File: g:Level HRevivalDefault.aspx.cs Line: 20 i believe it has something to do with the conn.Open() funtion or the connection string it's self the acutal full code is here, using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Data.Sql;public partial class _Default : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) {SqlConnection conn = new SqlConnection(@"Data Source=12042HOPESQLEXPRESS;Initial Catalog=db_revival;Integrated Security=True"); conn.Open();
} secondly once i get the connection working i want t be able to check a user email and password against those in a table to allow the log in, through my experience with PHP i would of done this by putting what the user entered into a variable and then checking the variables against an sql query, how do i go about this with aspx and C# my textfield is called uname and my passsword is call pword. Any help of example code would be greatly appreciated.
I have a parameter called Id in my SP which will be of nvarchar data type and i'm going to get the multiple ids at a time seperated by commas in that parameter from the application. Now my requirement is to update a table with these comma seperated ids in seperate rows.
For example, if i have 2 parameters called Id1 and Id2. Id1 will contain only one value and will be of int data type and Id2 will be of nvarchar data type as i can get multiple ids delimited by a comma from the application.
Suppose Id1 = 1 and Id2 = '1,2,3,4'. Then I have to update id2 in the tables seperately like wherever Id1 is '1' i need to update Id2 column for 4 rows with the value 1, 2, 3, 4 respectively in different rows.  how can i do this in T-SQL? How can i split the data of parameter Id2 in 4 different rows?
So these are a couple different interpretations I've made of a statement that needs to return students name's with the OSOS field in the attendcode code column of the attendance table on todays date with an attendance.aperiod column equal to 4.
This first one returns no results at all when I should get about 7 rows or so.....
Select Student.name, attendance.studentid, attendance.attenddate, attendance.aperiod, attendance.attendcode from Attendance, Student where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1 And aperiod = '4' And AttendCode = 'OSOS' AND student.name = Attendance.studentid
This one is returning all of the names in the database 6 times each.
Select Student.name, attendance.studentid, attendance.attenddate, attendance.aperiod, attendance.attendcode from Attendance, Student where AttendDate >= DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) And AttendDate < DateAdd(Day, DateDiff(Day, 0, GetDate()), 0) + 1 And aperiod = '4' And AttendCode = 'OSOS' order by name
I think something is messed up with my WHERE control but I cant figure it out... Any help is greatly appreciated!
Could you please help me to find easiest way to schedule daily auto data extraction time in SQL Server Express 2005 Edition (I need to put time to load data every day).
Hi all, I am really new to SSIS, so may be this is a really simple question, but I couldnt find an answer yet. I need to build a package that 1) counts the rows from a view 2) if rowcount >0 extracts the data into a file
I tryed to do this using a Row Count Transformation in the data flow, but after putting the count in a variable I am not able to perform the "conditional" phase two. I mean that I want to check the value of the variable, but cannot figure out how to conditionally execute the flat file extraction.
Using Row Count, I have to build 2 data flow tasks. Is there a way to do this in a single data flow? May be using an Execute SQL Task instead of row count?
Any suggestions will ge greately appreciated IgorB
We are using a package to extract data from a source build on some image using SOLID ODBC 5.2 driver. When we tried to extract data through MS SSIS(SQL Server Integration Services) package using SOLID 5.2 ODBC driver connection, it throws an error as mentioned below: ERROR DESCRIPTION:
Error: 0xC02090F5 at Data Flow Task, DataReader Source [1]: The component "DataReader Source" (1) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "DataReader Source" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
Also, we tried the same in a Excel sheet using the SOLID 5.2 ODBC driver it is working fine. Does the SOLID ODBC driver support .Net framework or do we need to install any other components??
I've to write a function to return a comma delimited values from a table columns
If a table has Tab1 ( Col1,Col2,Col3).
E.g. as below ( the columnName content I want to use as columns for my pivot table
CREATE FUNCTION [RPT].[GetListOfCol] ( @vCat NVARCHAR(4000) ) RETURNS @PList AS BEGIN SELECT @PList += N', [' + [ColumnName] +']' FROM [ETL].[TableDef] WHERE [IsActive] = 1 AND [Category] = @vCat RETURN; END;
I want out put to be as below, I am getting this output from the select query independently by declaring @Plist variable and passing @vcat value, now I want it to be returned from a function when called from a select query output ,Colum1,column2,....
I want to build a data import process with SSIS, sourcing Hyperion Financial Management. Accoring to my knowldge there were a Star Integration Server (Star Analytics acquired by IBM in Feb 2013) doing the extraction job and which could be used in SSIS.
As this product is not available now, how to do this.
I posted this originally in the incorrect forum I believe so I am reposting this in here which I believe is the proper place.
I need to move essentially a flat file from one server to another one and export it into a database on the second server. Does anyone have an easy process to accomplish this? I am currently at a loss. Any advice would be much appreciated.
I'd need to have a function that allows me to extract 'fields' fromwithin the stringI.E. (kinda pseudo code)declare @foo as varchar(100)set @foo = "Robert*Camarda*123 Main Street"select EXTRACT(@foo, '*', 2) ; -- would return 'Camarda'select EXTRACT(@foo, '*', 3) ;-- returns '123 Main Street'select EXTRACT(@foo, '*', 0) ;-- would return entire stringselect EXTRACT(@foo,'*' , 9) ;-- would return nullExtract( string, text delimiter, occurance)Anyone have something like this as a user defined function in SQL?TIARob
First of all I am a novice here. I am working on a table with a column of URL. I want to seperate the data in the URL delimited by '/'. Eg: http://www.simpletech.com/upgrades/aopen/s661fxm/s661fxmintelp4/
Here I want aopen as manufacturer, s661fxm as model_number and intelp4 as submodel_number. I solved this problem in Oracle using substring and instring. But I have no ides how to achieve this in SQL server. Please..advice me. Thanks in advance.
I'm trying to figure out if it's good design practice to have several pieces of data in a one column. I explain by example. Let's say you build a movie site. Each movie can belong to several categories. A movie can be Action, Adventure, Fantasy and Drama all at once. Assume a database table with all the movies and another table with all the categories. Now how would I associate one movie with several categories? Would it be OK if I add a Category field in the movie table and then add several categories in that, delimited by commas? Sort of like below:
movie_title | movie_rating | category_name--------------------------------------------------------------------------------------------------Pirates of the Carribean | PG-13 | Action,Adventure,FantasyEvil Dead | Unrated | Horrorand so on ...I can then query the database with a LIKE query if I want to select all movies of a certain category. Personally, I don't like this approach to much, but I can't think of another way to achieve this. Well, there is one other, but I like that one even less. I could create another table that links each movie to a category, but his way each movie with several categories would get a new row. Using the table above, Pirates would get three rows in that table. One with Action, one with Adventure and one with Fantasy. Get my drift? It all seems counter-intuitive. Thoughts? Thanks :o)
Hi, I need to export data from SQL server 2000 database into text file uisng ç Delimited. Because my destination database will be teradata. Could you let me know if you have any method for this. Thanks
I was wondering if anyone might be able to say how I could export data captured via a view into a comma delimited csv file.
So far I have tried using BCP to access my view and export to a CSV file, but the CSV file isn't comma delimited. I tried finding examples but couldn't see what I should do to have a comma delimited file. (I'm getting a bit tired now, so I might be missing something!)
I have created a bat file containing the following code:
I'm working on a sales commission report that will show commissions for up to 5 sales reps for each invoice. The invoice detail table contains separate columns for the commission rates payable to each rep, but for some reason the sale srep IDs are combined into one column. The salesrep column may contain null, a single sales rep id, or up to five slaes rep IDs separated by the '~' character.
So I'd like to parse the rep IDs from a single column (salesreplist) in my invoice detail table (below) to multiple columns (RepID1, RepID2, RepID3, RepID4,RepID5) in a temp table so I can more easily calculate the commission amounts for each invoice and sales rep.
Here is my table:
CREATE TABLE invcdtl( invoicenum int, salesreplist [text] NULL, reprate1 int NULL, reprate2 int NULL, reprate3 int NULL, reprate4 int NULL, reprate5 int NULL, )
As you can see, some records have trailing delimiters but some don't. This may be a result of the application's behavior when multiple reps are entered then removed from an invoice. One thing for sure is that when there are multiple reps, the IDs are always separated by '~'
As part of xml parsing, I use multicast to direct output of nodes to their corresponding relational tables and I do have a comma-delimited list for some nodes which basically needs to be converted into rows as illustrated below
I have a problem with a PIPE "|" delimited flat file. I have a column "Description" in which we get a string in which we have PIPE "|" as data. How we can skip this and load it as a data into the column Description.
Hi. Im new to SQL and I need to export a SQL table as a comma delimited text file which is straight forward. However two of the fields are integers and I need these to be right justified with zero's. In Access I would use something like format(columnname, "00000000") to get it to work, but SQL Server doesn't like this. How can I do this?
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?
