Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
I need to parse an regularly outputted rtf file and was wondering if it is possible in SSIS. I am trying to use the flat file connection manager to do this.
Now, I can't treat tab stops in an rtf like tab stops in a csv, since when you treat an rtf as a text file, you see the format code of the rtf. If I open the rtf in a text editor, the entire file is one line, with lines breaking with:
par}
Columns are tab delimited in the rtf, and they look like this when you treat the rtf as a text file.
plain abfs16f4cf0cb1
(or something like that, the word "tab" is the important part.)
So I use the "plain ab" part to delimit in SSIS, since that is consistent (planning to parse out all the garbage later on). The problem is, sometimes lines don't have a "city" and "state", so it "tabs" right over to the next field. So like this (looking in MS Word):
Phone <tab> City <tab> State <tab> Date <tab> Other fields..... 847-111-2222 <tab> Omaha <tab> NB <tab> 9/14/2007 <tab> 222-222-3333 <tab> 9/14/2007 <tab> 555-121-1212 <tab> Houston <tab> TX <tab> 9/14/2007 <tab>
Now, if you treat an RTF as a text file, it has only one "plain abfs16f4cf0cb1" after the phone number, so even for the missing line there is only one tab, not 3. This is because in the beginning of the row tabs for each row are defined like this:
tql x90 ql x840 ql....etc...
with "tql" and "tx" tags basically saying where all the tab stops are for that row. So for the row above with missing info, it lists fewer tab stops. So the "date" (and associated garbage) ends up under "City" for this row. All of the "Houston" row's data starts appearing in the sql server output table's 2nd last field, as you might expect.
Any suggestions how to pull this in in SSIS during the transformation? I could deal with it after I pull it in, I still have all the data. I'm thinking the logic to do this could be complicated though. I take the data out of the last two fields of the missing row into some other table, use UPDATES to shift the values 2 fields to the right, and then figure out a way to take the data I just put in a temp table back in, but it all sounds a bit complicated.
Let me know if this makes sense--I've almost got it going, I just need to sort this last bit out.
i am trying to read a qfx file from quicken. it looks like xml, but its not, but i cannot figure out how to grab what ive got to parse the line. i put this into a derived column, but its not getting it
because inside the data, it lools like that's what brackets a tranasction; the data looks like this and varies by trntype, but the columns are tagged like so
I have a tab delimited file with 122 columns. Can any one let me know if there is a better way of parsing/extracting few columns (say about 15) from the file and loading it into a table using SSIS.
Hello all, I have a question regarding importing text file data into SQL Server. I'm hoping someone can point me in the right direction, as my searches haven't turned up anything specific enough. I'm trying to parse a large (24MB) text file. It's a fixed-width file, with multiple columns. I need to parse this file, check if a record already exists, and then import the data into the database. But I don't need to insert every column. There's only a few columns from the file I need to insert. This parsing also needs to occur at regular intervals (daily). I looked at BULK INSERT, but I can't find an example that uses only some of the columns. Every example uses all columns, and the file is delimited, not fixed-width. Is there anything within SQL Server that can accomplish this? I haven't turned up anything that will solve my problem. The only other solution I can think of is an application that parses the file for me and inserts the data into the database. But can I schedule that application to run every night at midnight (for example) through SQL Server? I'm not too familiar with SQL Server, so I appreciate any help offered. Thanks,Jay
Summary A ABCD A Category MarketValue Margin A category1 1.0000000 1.000000 A category2 2.0000000 2.000000
H Totals Total Cash Net H 2.00000 200000 2000000
Another Summary B BCDE B Activity MarketValue Margin B activity1 3.00000 3.000000 B activity2 4.00000 4.000000
The items in blue are headers. I don't want to capture those. However, I want to capture all the data in black, and put it into 3 separate tables (or maybe the same table, under the appropriate column names)
This situation differs from anything I've done before in that you can't identify what row contains what data by what's in the row itself. That is, what's in the data rows is random and subject to change. So you can't search the row itself to determine which table it goes to.
However, if there's a way to capture all the rows after a certain header before the header changes again, that might work.
That is, get all rows between A Category MarketValue Margin and H Totals Total Cash Net and get all rows between H Totals Total Cash Net and Another Summary and get all rows after B Activity MarketValue Margin
The suggestion to do this is buried deep in one of my posts, however I still do not have a clear idea of how to do this.
I have a flat file which has several "bad rows" in it. Because file error redirection is buggy, I need a manual approach to get rid of these incomplete rows in my data file.
Phil, you suggested I read the file as one long string, then parse out the bad rows (using a script?).... however I have no idea as to how to actually do this.
I was wondering if it's possible to clarify the steps involved in doing this, or perhaps point me to an example I can look at, as I cannot seem to get around this problem on my own.
I know this has come up before and I have tried several of the solutions found within the forum but I just can't seem to import my file correctly and could use some input, please.
Sample file (less fields than actual file):
Name (str), Phone# (str), Description(str), Resolved(bool), Met(bool)
"Kay, Mary","123-4567","Used a "."not a"," in text", "1", "1"
The text is qualified with " and columns delimited with commas but the description field has embedded quotes and commas. Normally it works except if there embedded quotes and commas.
I have tried unqualified data and undouble, but that does not work either because of the embedded commas in quotes.
Do I need to do something before the data flow? Do I need to do custom code similar to undouble (I tried modifying undouble but using unqualified fields caused the source file to not like the data and go red)? Should the row be read as one field and parsed?
I have a website, where people upload tab delimited text files of their product inventories, which the site parses and inserts into a database table. Here's the catch: Instead of insisting that each user use a standardized format, each user can upload the file in whatever column order they want, they just have to let the site know through a GUI which column is in which order. And, they may upload columns that if not mapped, will be ignored. Right now, I am doing all of this in code and it runs slow, I was thinking of offloading this to either a stored procedure, ssis, or bulk upload. But, with the varying format of the uploaded text file, I am not sure how I could do that. Any suggestions? Thanks!
I have a CSV file with roughly 6 million rows. The file is unstructured; that is, some rows have 5 fields, others have 15, and there are as many 50 fields in one row.
I am using bulk insert to read the entire file into a table in database, with each row being a database record. With that, I have one column that contains a row of comma delimited fields. All fields are character string and I want to find a quick way of parsing each row and placing each comma-delimited value in a column. For example:
Column CSVString contains the a CSV row (I don't know how many filelds (no. of commas + 1) in the row, but if the row contains 10 fields, I need to populate columns C1-C10. If the row has 15 fields, I populate columns C1-C15.
How can I do this in a very efficient way? I tried CTE but performance was not very good.
I have a table with rate histories that we recieve three times a day. I want to write a query that returns all fields for the row with the latest receipt time. ie - we get rates at 10 AM, 1 PM and 4 PM and I want to return the row from 4 PM. The arrival time is stored down to the seconds so I can't set an exact critera. I'm trying to so something like this...
Select * from ratehistory where ratehistoryid in (Select ratehistoryid, max(reciept) from ratehistory group by Cusip, Date(reciept))
I want to select from a table based of a variable, the variable is either 1 or 2, however is it possible to ask for BOTH 1 and 2 when calling from a variable?I tried this, but it seems to only look for values matching 1 and then stop... SET @FuelType = '1 OR FuelType = 2'SELECT * FROM Engine WHERE FuelType = @FuelType
Hi there,I am about to deploy an application and it works fine when launched from vwd... but typing it as locahost/application/example.aspx produces.....Server Error in '/WiseSystem' Application.
Unable to open the physical file "C:InetpubwwwrootWiseSystemApp_DataDatabase.mdf". Operating system error 32: "32(error not found)".An attempt to attach an auto-named database for file C:InetpubwwwrootWiseSystemApp_DataDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
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: Unable to open the physical file "C:InetpubwwwrootWiseSystemApp_DataDatabase.mdf". Operating system error 32: "32(error not found)".An attempt to attach an auto-named database for file C:InetpubwwwrootWiseSystemApp_DataDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.Does anyone know why this is?? I havent used SQL servers before and this is the first real application i have designed.Please help!!
I am trying to query multiple tables that have the same column. They are a supposed to be a unique ID to the product, but not to the database. I have a primary key already set to each othe tables. The tables do not share anything between them, but i would like to search for these IDs in other tables before my insert command is executed. I am writing my code in VB. example of what i am trying to accomplish. Table1 = TVs ID/SKU_ID/Manufacturer 1 87654 Samsung
I want to search for SKU_ID in multiple tables( I think a total of 9 tables). I want to search between tables to catch for the same SKU_IDs. and if it finds it, raise error. I will be able to figure the vb.net portion. I just dont know how to go about the sql query portion. Thanks in advance and appreciate any help. Thanks!
I have a package that extracts data from a Flat File. If any errors or truncation occur during the extraction of the input data, the package should fail. All fields that have erroneous values should be reported in the log file.
My Solution: - I have created a Data Flow Task that contains a Flat File Source Adapter and a dummy destination.
- I have left the default "Error Output" configuration of the Flat File Source adapter, namely if a truncation or an error occur for a certain column, then the reaction is "Fail Component".
Problem: This configuration gives me only the first erroneous column in the row being processed.
Question: Is it possible to make the Flat File Source adapter continue parsing the current row before it fails? This way, I would be able to get all the erroneous columns in the row in one shot.
The low down: Local Web Server on Windows 2003 Local SQL Server on Windows 2003 Hosting dynamic website tied to inhouse Access Application
Ok, basically, how it is set up, people can login to our website and enter data (insert record), on our end, we have an Access application where we can play with the data that was entered via the website. Currently, we do not have either server set up as a Mail server.
What we need to be able to do: When a customer enters data on our website, their supervisor, and about 2-3 other people related to the transaction need to be emailed to be notified that an order was submitted. So how do I code that? On the page with the Insert Record? OR after Insert Record redirect them to another page that sends the mail out?
Which server do I enable the mail?
I was reading about SQL Mail etc, which would be good since we do mass emails to clients weekly, but I have no idea how to set that up and I look crossed eyed at any tutorial.
Do I want to set up theSQL server to also be a Mail Server that way we can use the SQL database to email as well as data entered from the website? But then again, the website points to the Web Server which pulls data off the SQL server (so unless the Web Server is a mail server, nothing will be sent, am I right?)
*sigh*
I know very little about SQL and I'm being asked to impliment this and I am 100% confused. I'm a graphics artist not a programmer! LOL
All I am trying to do is use a SqlDataSource, Read that Data from the Session Variable, Do a RowFilter off the Data and then return the Data (Currently a DataSet but doesnt matter). But Since I dont use SqlDataSource that much not sure all the code I have is neccasry: protected DataSet GetSearchAppData(string strWhere) { DataSet dsSearchAppTable = new DataSet("SearchAppTable"); SqlDataSource ss = new SqlDataSource(); DataView dv = new DataView(); DataSourceSelectArguments dsArgg = new DataSourceSelectArguments();
dsArgg = DataSourceSelectArguments.Empty; ss = ((SqlDataSource)Session["SearchAppTable"]); dv = (DataView)ss.Select(dsArgg); dv.RowFilter = strWhere; dsSearchAppTable.Tables.Add(dv.Table.Clone());
return dsSearchAppTable; } And to mention it, dsSearchAppTable returns a Table with 0 rows. Not sure what the heck I'm doing wrong.
I have a table with 4 columns, ID, Key, Value and SiteID.
SiteID can be Null or an int.
As an example I could have these four rows, ID=1, key=colour, value=red, siteID=Null ID=2, key=colour, value=green, siteID=2 ID=3, key=font, value=arial, siteID=2 ID=4, key=size, value=6,siteID=Null
My query will pass in a siteID and I need to bring back all rows with that site ID plus any rows whos key I haven't already got who's siteID =Null.
The Null site ID is default, so it always needs to bring back the null if a key doesn't exist with a siteID.
Does anyone know if that is even possible with just a straight SQL query?
Hi I have been tearing my hair out trying to resolve this problem. A form is submitted with a number in to checkthe database for the items with that number of items in the quantity field(s) and display them.
There are 2 relevant Tables in the 2005 server SQL Database, One called "Products" the other called "ProductDetails"
In both tables is a field called Quantity, this hold a numerical value for the quantity of the product.
Some products have additional attributes such as sizes or colours and these are stored in the ProductDetails table.
Every product is stored in the "Products" table and has a unique ProductID, where a product has additional attributes, these along with the ProductID and the quantity for that particular attribute are stored in the ProductDetails table.
I am trying to loop through the database and produce a list of all the items and if they have any additional size or colour attributes then display these too, and if they dont have additional attributes then just display the product details and quantity stored in the Products table.
What I have actually done instead is produce a list of every item and every attribute in the database, but for example Where product 1 doesnt have any attributes, and neither does product 2, but product 3 and product 3 do, when the details are displayed on the page (Using ASP),
Rather than displaying product 1 details and the quantity in the "Products" table, product 2 and the details & quantity in the Products table, then product 3 and each of the attributes & Quanity pertaining to that item in the "ProductDetails" table, and so on, it shows every item as having every combination of attributes,
so although products 1 and 2 have no attributes, it will show product 1 with the first attribute of the other products and then show another product 1 with the next attribute from another product and keep going untill all the attributes in the whole of the Product details table is shown , then do the same thing with product 2 and then product 3 and so on.....
Also the products that have no attributes are not only shown with the attributes that they dont have, they are not shown without attribues and with the correct details in the list.
Here is my string, which has changed a million times, I am just learning as I go along so if it is obvious please be pateient with me and help me out...
rsResults.Source = "SELECT DISTINCT Products.ProductID, Products.ProductName, Products.ProductPict, Products.Quantity, ProductDetails.ProductDetailID, ProductDetails.P_Size, ProductDetails.P_Color, ProductDetails.Quantity FROM Products, ProductDetails WHERE Products.ProductID = Products.ProductID AND Products.Quantity <= "& +rsResults__var1 & " OR Products.ProductID = ProductDetails.ProductID AND ProductDetails.Quantity <= "& +rsResults__var1 & " Order by Products.ProductID asc "
Can anyone see why it is doing this instead of only showing the attributes next to the items that have attribues?
I have two tables: persons (id,age,roleid) roles (roleid,description)
I want to build a sql query to produce the following rows (example): range(age) role1 role2 role3 .... rolen 0 to 4 11 24 5 7 5 to 9 42 7 1 0 10 to 14 14 21 9 8 15 to 20 7 0 7 19
I was reading an information concerning to ROLLUP and CUBE but I have no idea how to do a query like this.
Hello guys, I am askng for any help ...am trying to get this SQL language.
I want to provide an SQL query to set all the priority to 1 for all customers that have all their orders being for a product with importance of 100. -------------------------------------------------------------------- There are three tables in the database: Customer, Product and Orders.
The Customer table has three column: Customer_id (PK), priority, Address.
The Orders table has three colums as well: Order_id (PK), Customer_id (FK), Product_id (FK)
The Product table has three columns as well: Product_id(PK), Product_name, Importance.
So the order table is connected to both the product and the customers table by respective foreign key.
I am trying to use ssis (sql query or .net-script task) to transfer data from one table to another. I have difficulties to make the query:
i have one table tbl_games which fields are : type ,startDateTime, EndDateTime, playerId. I need to check every 1/2 hour according to the startDateTime, How many times a person is playing and to show it in a new table tbl_collectData like this: TimeplayerId Games_0-30_secondsGames_30-50_seconds
8-8:30 A 3 2 8:30-9B 2 10 9-:30C 20 7
The length of the game is measured by the StartDateTime -EndDateTime, and only games type # 2 is being collected. The number 3 example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.
I tryed to start by doing something like this , but it is not a complete query: INSERT INTO tbl_collectData (Time, playerId, Games_0-30_seconds) SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS numberOfCalls_0_30 FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND (StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM') GROUP BY playerId
Our office uses a program to keep demographic and private customer information. This program is installed in a Dell PowerEdge Windows 2000 Server with 8 desktops spread throughout the office for our employees to use. We back the SQL database up every night on 2 WD external HD that we rotate and the server does a nightly backup too. Our software vendor is changing to a different software program that we don't care for since our current software works so well. As a result, we want to keep using the original software. Here's my question:
If we choose to stay with the original software, do we need this software to read and print out the SQL database for individual customers forever? In other words, if this software fails, is there a way to read and print the existing records without using the original software? Our software vendor tells us that our version will be discontinued and no support will ever be available again. But since we are required to keep our records for many years down the road, we need a way to print this information out if the customer wants to have a copy say 5 years from now. Is there any way to do this without the original software? Thank you very much for your help and opinions.
Hello,can anyone help me on this: I have a table with 3 columns:id,time,description let's say that I have this set of records: ID TIME DESC 1 8:04 aa 1 8:05 aa 1 8:06 aa 2 8:07 ab 2 8:08 -- 1 8:09 bb
I need a query that return 1 8:04 aa 2 8:07 ab 1 8:09 bb means that from every set of same ID a need the distinct one,coz the first set of "1" refere to same person,the 4th "1" refere to different person.
Hi,I have two tables Trade table and Cons table. Records are inserted inboth the tables independent of each other. There are fields likeExc_Ref, Qty, Date in both the tables.I need to write a query which should give me records :1. Where there is missing Exc_Ref value in either of the table. i.e.If Trade table has a Exc_Ref value but missing in Cons table then thatrecord should be displayed. Similarly if Cons has a Exc_Ref valuewhich is not found in Trade table then that too should be displayed.2. In case where both the tables have matching Exc_Ref data then itshould display the record only when the remaining column does notmatch like Qty or Date.Please help me to resolve this complicated query.ThanksNick
The best way to explain this is by example.I have a source table with many columns.SourceSYMBOLEXCHANGE_NAMECUSIPTYPEISSUE_NAMEand so onThen I have 3 other destination tables.ExchangesEXCHANGE_ID IDENTITYEXCHANGE_NAME UNIQUESecurityMasterSECURITY_MASTER_ID IDENTITYSYMBOL UNIQUECUSIPTYPEISSUE_NAMEand so onExchange_mm_SecurityMasterEXCHANGE_IDSECURITY_MASTER_ID-- The Source table has multiple rows of the same symbol.-- The Exchanges table is already populated with all the exchanges.-- A single security (in the SecurityMaster table) can belong to manyExchanges, hence the Exchange_mm_SecurityMaster table.Now. If I just wanted to insert into the SecurityMaster table withouttouching the Exchange_mm_SecurityMaster table I could just execute:INSERT INTO SecurityMaster ([SYMBOL], [CUSIP], [TYPE], [ISSUE_NAME])SELECT DISTINCT[SYMBOL], [CUSIP], [TYPE], [ISSUE_NAME]FROM SourceWHERE NOT EXISTS (SELECT * FROM SecurityMaster SM WHERE SM.SYMBOL =Source.SYMBOL)Now to the Exchange_mm_SecurityMaster. I need the individual identityvalues for each row inserted into SecurityMaster so I can then turnaround and insert into Exchange_mm_SecurityMaster. Here are theissues/possibilities as I see it.- @@IDENTITY will not work since I am not inserting a single row at atime- I guess I could INSERT INTO SecurityMaster first, THEN do anotherINSERT INTO Exchange_mm_SecurityMaster with different where clause.- I could create a stored procedure that does a single insert intoSecurityMaster and Exchange_mm_SecurityMaster. Then call thatprocedure for each row in the SELECT DISTRICT from the Source table.My main worry is the number of arguments passed in. My example onlyshows a few but a regular SecurityMster table could have 30-50columns.- Maybe do something with a trigger but I am not sure if I can passthe EXCHANGE_NAME value to the SecurityMaster trigger when that tabledoes not need it.Hope I explained it clearly. Any help would be appreciated.
I Have not been able to solve this problem from quiete a while now.
I am using sql server 2005.
I have got a table which contains these columns - start date, end date and volumes if the month in the start date is same as that of end date, the volume remains same, else if the months in the two dates are different, then i have to distribute the volume in such a way that some part will go in the first month and the rest in the other month.. i have to somehow calculate (or prorate) the volume according to the no of days in each month
I have to perform a query on this table so that I can group the volumes for different months and different years.
Here is the sample data...
Service Start Service End FMIS Code No of Units Year Month Volumes
01-Oct-00 15-Aug-01 6440 32 ? ? ?
01-Oct-00 30-Sep-02 6441 40 ? ? ?
01-Oct-02 22-May-03 6440 78 ? ? ?
01-Oct-02 23-May-03 6990 87 ? ? ?
06-Mar-03 31-Jul-03 6997 102 ? ? ?
07-Mar-03 31-Jul-03 6744 3 ? ? ?
01-May-03 31-May-03 6440 789 ? ? ?
23-Jun-03 31-Aug-03 6447 1000 ? ? ?
29-Jun-03 30-Jun-03 6440 981 ? ? ?
30-Jun-03 31-Jul-03 6000 50 ? ? ?
01-Jul-03 08-Jul-03 6002 54 ? ? ?
01-Jul-03 13-Jul-03 6000 562 ? ? ?
I have to calculate the columns coloured in blue..
I am trying to use ssis (sql query or .net-script task) to transfer data from one table to another. I have difficulties to make the query:
i have one table tbl_games which fields are : type ,startDateTime, EndDateTime, playerId. I need to check every 1/2 hour according to the startDateTime, How many times a person is playing and to show it in a new table tbl_collectData like this: Time playerId Games_0-30_seconds Games_30-50_seconds
8-8:30 A 3 5 8:30-9 B 2 10 9-:30 C 20 7
The length of the game is measured by the StartDateTime -EndDateTime, and only games type # 2 is being collected. The number 3 in the example is: 3 games that lasted between 0-30 seconds and were played between 8-8:30 by player A, and the game type is 2.
I tryed to start by doing something like this , but it is not a complete query: INSERT INTO tbl_collectData (Time, playerId, Games_0-30_seconds) SELECT '12 / 26 / 2007 4 : 53 : 03 PM' AS Expr1, playerId, COUNT(DATEDIFF(ss, StartDateTime, EndDateTime)) AS Games_0-30_seconds FROM tbl_games WHERE (Type = 2) AND (DATEDIFF(ss, StartDateTime, EndDateTime) < 31) AND (StartDateTime > '12 / 26 / 2007 4 : 53 : 03 PM') AND (StartDateTime < '12 / 26 / 2007 5 : 23 : 03 PM') GROUP BY playerId
Hi, My users table contains a field called researchInterestId which looks like this: 1, 5, 10 This is because users where allows to select multiple options when choosing their research interests. I have another table which contains the names of those research interests, which looks like this: researchInterestId researchInterestName 1 Biology 2 Cancer
My question is, when selecting my list of users, i wish to also display the names of their research interests. I know how to inner join but im not sure in this case as there are multiple values (1, 5, 10) Hope that makes sense and that someone can point me in the right direction or let me know what this type of query is called? Thanks Sam
