If that's so, do I simply concatenate them in the final file? Or do I have three separate files? And do I label them uniquely such as Poll1Main, Poll1Questions and Poll1Answers so that I can export several polls side-by-side without overwriting each other?
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
I am creating an application which uses logic similar to J.D.Edwards (for those of you are familiar with its wildcarding data structure).
Basically, a customer purchases a particular house, with a particular elevation, in a particular community. This data is stored in a Customer table. From the customer’s selection (community, plan, elevation) criteria, I need to create a unique list of options from data stored in an optionmaster table.
A customer’s data looks similar to this in the customer table:
CustomerID
CommunityID
Community Name
PlanID
Plan Name
ElevationID
ElevationName
1234567
7
Hickory Hills
25
Allen
3
C
The Optionmaster table is structured like this (there are actually about 1000 records):
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
0
+
0
+
0
+
100
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optional Dormer
0
+
25
Allen
2
B
50
9125
Optional Tub
8
Smithville
0
+
0
+
800
9125
Optional Kitchen
0
+
0
Lori
0
+
2500
First. Based on the customer’s table I need to first select all options = to their community, as well as those available to communities everywhere (+). In some cases the same option is maintained for both at both levels. In that case the community specific option must be selected.
To do this I think I need an if else statement to select those records:
In this case, if CommID =7, select record, else select 0. This eliminates the other Smithville community and pulls the correct option # 1234.
The result would look like this:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
9125
Optinal Kitchen
0
+
0
Lori
0
+
2500
Second, from this dataset, I need to select all options equal to the plan ID, and those which apply to all plans (+). So, the Lori Plan data goes away.
Something like, If the plan ID = 25, select record, else 0. the result would be:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
9101
Optinal Dormer
0
+
25
Allen
2
B
50
Third and finally, based on that dataset, I would have to select any elevation specific options for that plan or options for all elevations of that plan (+).
Something like this: If the Elevation ID = 2, select record, else. 0. Note the elevation B data goes away.
The final result would be for this customer is:
Option #
Option Name
CommID
Community Name
PlanID
Plan Name
ElevID
Elevation Name
Price
4567
Optional Window
0
+
0
+
0
+
250
1234
Optional Door
7
Hickory Hills
0
+
0
+
0
4567
Optional Brick
0
+
25
Allen
0
+
250
Any assistance anyone can provide in coming up with a sql statement to do this would be appreciated.
I could really use some suggestions on the best way to get the results of a pretty complex aspx form. There are a whole bunch of search criteria. Most of the criteria are ranges of numbers such as a starting number and ending number but other criteria need to search text fields for keywords. Most fields are optional which means that many values will not be submitted. Not that there is a right and wrong way to do it, but what typically works out better for these things, an aspx procedure that puts together a highly complicated select statement or writing a highly complicated stored procedure? Either way I see a ton of if statements, or am I making this a lot harder then it needs it needs to be?
I've just read Jeff's query regarding passing an array to a stored procedure, and it seems to be a question that hits the message board quite frequently. There often seems to be the case where you have some block of data in one location, and want to process it somewhere else, and there just isn't an easy way to do it (or is there?). Passing a cursor is possible, but getting data back out of a cursor is painful.
The ways I have used in the past are:
Using a cursor Using a global temporary table Using a "permanent" table, but declared with a unique name (using a timestamp) and passing the tablename as a parameter.
Has anybody any suggestions for a future SQL release which we could suggest to Microsoft?
Suggestions from myself: -Having a single TSQL command to copy a cursor back into a table, to make cursor parameters less cumbersome -Being able to pass a pointer to a temporary table as an SP parameter -Allowing variables to be declared with more complex structures (such as arrays, or classes)
I have a sql server 2008 backend with an Access 2007 frontend database. Each time I export a query I get the following error:
Code: Microsoft Access was unable to append all the data to the table.
The contents of fields in 0 record(s) were deleted, and 1 record(s) were lost due to key violations.
*If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table. *If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables. Do you want to proceed anyway?
I don't know what if anything is actually missing because of the amount of data is more thant 6000 records. It seems everything exported but I would have to comb through the data to be sure.
Does anyone know if it is possible to point data that underwent the "merge join" transformation (in one data flow) to the following data flow? I don't want to recreate all that merging, sorting and calling the same sources again in the following data flow if the data that I am using exists in the previous data flow. The merged data is simply too big to export to an excel file, so does anyone have any ideas? Thanks!
I'm a SQL noob still and need some help figuring out a weird problem. I have directory information I am trying to pump out in the correct order, no big deal except some entries need to be categorized based on a simple hierachy. So for example, there is an entry for a company division, and then there are all the subdivisions, and then a few offices. So it goes like this:
In other words, Desktop is an office in Support which is a part of Computing.
Now what I want is to basically pump all that data out via SQL in that exact order. So I have 3 fields that I am using and then trying to arrange it using Order By - except it doesn't come out correctly.
Code:
SELECT Division, Subdivision, Office FROM directory ORDER BY Division, Subdivision, Office
What happens with this is that I end up with all my Divisions in order only. So for example, the main entry for Computing has "Computing" as it's Division and Office, but I want it to appear first in the list so I set Subdivision to be "A" - Instead of it appearing first it appears second below an entry for a different Division that also has it's Subdivision set as "A". I end up with Divisions mixed into the wrong cluster of subdivisions and offices.
Anyone have any ideas? It seems like this should be fairly simple and yet somehow it isn't. Thanks in advance for any help!
I have three tables and all three are linked. Looking for query so that I can get the desired result. Notes for Table_A and Table_B:
ROW Data are given in the variables to insert the Row data. If these Row data are already exist with the exactly same sequence in the row of table then don't need to INSERT data again.If these variable date doen't exist then need to add this row.
Notes for Table_C:
Seq_id_Table_A is a Seq_id of #table_A. Seq_id_Table_B is a Seq_id of #table_B.
--Table_A----------------------Variables for Table_A-------------------- Declare @table_A_Y char(4)='TRC' Declare @table_A_Y1 char(2)='1'
I am building a report that takes a web service as data source. This web service does not take primitive types as parameters. The signature is something like this:
How do I get columns to output when I have a data reader source? My connection is an ODBC and does complex sql. I am connection to a Netezza database and I would like to execute a very complex query, but in essence does
Create newtable as
(select day, sessionId)
from source
// lots of other joins and unions
select day, sessionId from newtable
drop newtable
I have an ODBC connection and I have a Datareader source, I cannot connect this source to my SQL Server destination because no output columns are available. What am I missing here?
Are there any good examples of this, taking data from a ODBC source into SQL server?
Need a guidance on consuming the WCF service with complex type in the SSIS package. I have a WCF service with complex type inside the complex type (Nested complex type) in the web method as an argument. When I try to use this WCF service in the SSIS web service task, I get an error "The web method has unsupported arguments".
I am able to consume the WCF service with the web method having Complex type and simple/prmitive type in side that as argument.
For example:
The web method in WCF service which accepts the argument as comlex type say "Employee" whose structure is:
Employee { FName String; LName String; Age int }
It is possible to consume this WCF service and pass the arguments.
But when the Employee complex type is changed to have one more complex type in side it it give the above mentioned error. The Employee type is modified as:
Employee { FName String; LName String; Age String Type EmployeeType; }
EmployeeType { type string; }
Now I get the error Web Method has unsupported arguments.
I'm trying to replicate a SQL join across two seperate data sources in SSIS. If I were to write SQL to do this, it would be as follows:
SELECT Costs.CostRateEntryId,
Costs.UserId,
Costs.HourlyRate * 8 AS DailyCostRate,
Dates.DateKey,
Dates.ActualDate,
FROM Costs
INNER JOIN Dates ON Dates.ActualDate >= Costs.EffectiveDate AND Dates.ActualDate <= Costs.EndDate
Unfortunately, as the tables 'Dates' and 'Costs' are in two seperate SQL2005 systems, I can't really do this. I was hoping that it could be achieved in SSIS, but I cant seem to find any way that I can do a join that's <= or >=.
hi all, I am getting problem while importing data from excel file. I am bale to do the same with flat files. But when i do with excel files its throwing error : format error. pls help me in this regard. and How to export data into export files from query analyser..
I was wondering if there is a way to export and recode data at the same time with SQL.
For example I have gender information coded as 1 or 2 in my table and I need to upload the information to a different application that needs M or F. Is there a way to export to a new table and recode at the same time ?
I would like to export data to a excel file using a stored procedure. I'm not sure how to go about this or if it is even possible. Can someone point me to a link to show me how to do this?
I recently found out that there is no easy way to distribute a MSSQL 2005 database to MSSQL Server 2000. Most forums that I have read say to use the DTS facility to import the data into the SQL 2000, however this is not an option (for a variety of reason I won't go into).
The next best option in my mind is to script the entire database, including the data into one or a few script files then run them on the SQL 2000 server to recreate the database. Unfortunately, exporting the data, what I thought would be a fundamental feature, isn't part of SQL 2005.
So does anybody know of a good (free) scripting program that will allow me to export the entire database from a server? I've tried: - Free program from the CodeProject.com (program dies when there's more than 5000 lines) http://www.codeproject.com/dotnet/ScriptDatabase.asp -SQL Scripter www.sqlscripter.com (Doesn't script table which don't have primary keys, and produces a script for each object instead of just a single file)
I'd probably like a program which only creates a single script, as the database has over 200 tables and I don't want to have to go through this process everytime I need to distribute the DB (which will be often).
The only other option I can think of is a program which converts an SQL 2005 backup to a 2000 version.
I'm trying to export data from SQL. Can anyone help. I have commented my script below and have managed to create a table, insert using 'bulk insert' - now I want to export the data out. I'm getting an error message saying 'cannot use the output option when passing to a stored procedure'.
comment ----------- drop table for re-runs ! -----------
drop table JET go
comment ------------ create a table to match the .txt file importing ----------
I want export data from Excel to new SQL Server table,
select * into mytable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:sample1.xls;HDR=YES', 'SELECT * FROM [Sheet1$]')
But i am getting following error message
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.". Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
1. I need to be able to export data from a database to an excel sheet (I have written the query and tested it works, so I don't need to know this stage :^)). What is the best way of doing this? Could you send me a link of a howto doc?
2. Once the data is export to excel, it then needs to be manipulated so that a final sheet is created. During the manipulation I need to add the values of certain columns to give me an end result.
Should I use excel to manipulate the data or can sql add the values of certain columns and then export to an excel sheet?
Hi I am trying to export data from SQL which is ok and I can do that part. However from the result of the query I need to add the values of certain columns. Because I'm a newbie to SQL I'll explain and try and show images as best as I can as we go.... So far I have written a query select Stockitem.code, Warehouseitem.ConfirmedQtyInStock, Warehouseitem.UnConfirmedQtyInStock, Warehouseitem.QuantityAllocatedStock, Warehouseitem.QuantityAllocatedBOM, Warehouseitem.QuantityAllocatedSOP, WarehouseItem.WarehouseIDfrom WarehouseItem INNER JOIN StockItem ONWarehouseItem.ItemID = Stockitem.itemidINNER JOIN BinItem ONBinitem.itemid = stockitem.itemidwhere WarehouseItem.WarehouseID = '3403' AND BinItem.BinName LIKE 'S%' AND BinItem.BinName <> 'S' OR BinItem.BinName LIKE 'T%' AND BinItem.BinName <> 'T'order by stockitem.code This returns the following results.... Code ConfirmedQtyInStock UnConfirmedQtyInStock QuantityAllocatedStock QuantityAllocatedBOM QuantityAllocatedSOP WarehouseID 12345 96.00000 .00000 .00000 3.00000 13.00000 3403 Now I am trying to find out the amount of freestock available for everything with the WarehouseID 3403. Therefore in the example above to be able to workout how many '12345' (Code) we have in stock, the equation used to find out the free stock available is as follows.... Free Stock = (ConfirmedQtyInStock + UnConfirmedQtyInStock) – (QuantityAllocatedStock+ QuantityAllocatedBOM + QuantityAllocatedSOP) Therefore if I manually use the equation above the amount of freestock available = Free Stock = (96+0) - (0+3+13) = 80 (free stock). Then once I have the equation to be able to output the 'freestock' quantity, I need to export the results into an excel spreadsheet with the following layout... ColumnA Code,FreeStock 12345,80 Hope I've explained this in a good manner, any help would be gratefully received. Simba
I need to export data from one database to another and I want to automate this process. The problem is I only have to select specific tables of data to be exported. Any help will be greatly appreciated Thank you!!
may i know where to find more detail like how to importing and exporting data in ms sql server.? Hope can get the more detail about it and also teach me step by step. (hope can include the photo).
I am trying to export data to excel and it is giving an error. Here is the query and error:
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:SQL ServerEmp_Personal_Data.xls;', 'SELECT * FROM [Sheet1$]') SELECT * FROM Emp_Info
error is: Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
Can anybody please help me what is this error about & how to fix this.
Hi all,I've seen this noted in many posts, but nothing I've checked out givesme any clue on how to do this.Basically as my topic says, I have a DTS and I simply need to exportsome data from a table in MS SQL 2000 to an Excel spreadsheet. I alsoneed to automate this process so it can run nightly and each new day anew spreadsheet will be on a network share for us to pick-up.Can someone point me to the right direction? This needs to be donetotally through the DTS script, so no ImportExport wizard or anythingmanual.Thanks --Sam
When I export data from database A tot database B and I look at the copied tables in database B, all the primary keys are gone and the field is now a "normal" field ?
How can I keep the primary key in the copied tables ?
