I need to recreate a SQL 2005 db in a SQL 2000 server. So far I was able to recreate database schema, but I don't know how to import the raw data. I tried copy-pasting table data to the new db using SQL Server Management Studio Express, but there's a problem - the identity columns do not retain the correct values. For example:
Old db table
ID Username
-------------
15 Peter
45 James
When pasting the same data to teh new db, I would get this:
ID Username
-------------
1 Peter
2 James
You can see the ID field values become different (since the ID attribute is an identity and the table in the new db is empty, the db picks the values 1 and 2 for the two records) and now when pasting data into related tables, I get the error because table relations (which are based on identities) do not match. How do I solve this problem and import all the data into the new db while maintaining correct relationships among tables?
Does anyone know how I can do this? I have 2 tables in an ODBC datasource (INV HEADER) and (INV DETAILS). The relationship on these 2 tables is (INVNUM). I want to import these tables into SQL 2005 on a nightly basis by date. The problem is the date field is on the (INV HEADER) table and not the (INV DETAILS) table. Basically I want to import all the (INV DETAILS) rows that have the same (INVNUM) as the (INVHEADER) but don't know how to do this. I could use a join on the source tables but how would I direct specific columns to 2 different destinations? Any help on this would be great and appreciated. Thanks
Can you describe the best (or your preferred) method for updating data held in a related table using Visual Studio 2005 and SQL Server. For example; if you had a stock control system with the product names and current stock levels in one table and all stock movements in and out held in another table, what is the best, fastest, safest and most reliable method of inserting a stock movement and then updating the current stock level? I have tried a couple of different methods but would really appreciate a wider range of opinions. Thanks
I have a relationship database that contains two related tables. Table1 has the fields Customer_Name, Customer_ID, and products_Purchased. Table2 has the fields Customer_Name and Cutomer_ID. I would like to add a new row to Table1for a new purchase by a cutomer. When I try to add a new value in Table1 I get an error message saying I can't add a value to table1 because it requires a corresponding value in Table2. I am using the following code: '********************************************** ("INSERT INTO [Table1]([products_Purchased], [Customer_ID]) VALUES('" & textBox..Text & "'," & session("ID") & ")" ' ***************************************** How do I write an INSERT STATEMENT that adds vaules to both tables, so that the code works? Any HELP would be appreciated. charlie
I want retrieve data from 3 table my tables structure is this
tblUsers
U_ID - Name
3 John
tblGroups
G_ID - Name
5 Admins 6 Moderators
Now I want join some of the users to different groups for example John maby is a member of two groups (Admins and Moderators)
in order to do this I created a new table names tblGroupsUsers
tblGroupsUsers
ID - User_ID - Group_ID
1 3 5 1 3 6
its ok, but Now I don't know how to retrive my users list from database I don't know how to write a wuery for this I have tried this :
strSQL = "SELECT tblUsers.name, tblUsers.U_ID, tblGroups.G_ID, tblGroupsUsers.Group_ID, tblGroupsUsers.User_ID FROM tblUsers INNER JOIN tblGroupsUsers ON tblGroupsUsers.User_ID = tblUsers.U_ID, tblGroups WHERE tblGroupsUsers.Group_ID = tblGroups.G_ID ORDER BY tblUsers.name ASC;"
Its working withut error but the problem is the results its like this
John
John
its will retrive the username twice , I think its reading based on tblGroupsUsers table because it has two rows , help please I need this how can I configure my query to get eache name once
I have two tables. When my user completes an insert of data in table (1), I would like the second "related" table (2) to be automatically populated with defaults. Is this possible? My logical approach to this is: 1. Build a handler for the OnInsert event of the first table 2. In the handler, call the Insert Command on the SQLDataSource for the second table with the defaults specified in the DataSource. What I'm not sure how to do is Step 2 or whats the best way. How do I call the Insertcommand programmatically for a DataSource? Or, is there a better way such as some kind of traditional hardwired SQL insert statement like in classical ASP? Or is there a way to programmatically call a stored procedure and if so is the 3rd approach the best way?
How exactly would someone do this best? It seems this would be a rather common thing someone might need to do.
Hi I have a question how to load data to tables linked by Foreign Keys in MSDE/SQL server. Example: If I have 2 tables linked (by Foreign Key): One table:
ITEM idITEM NAMEITEM CATEGORY (FK) 1cheese 2
And another:
Category IDCATEGORY NAME 1 household 2 food 3 general
How do I enter the load of data Do I have to enter it as 1cheese2 or is there some way of entering it as 1cheesefood
TDS wizard does not allow me to transfer to views/querries what I thought would be a normal way as I would enter data to view(relevant to Access's form) and it would update related tables . When I wrote sql to do it it said I can not update my view table as too many tables would be affected(I had lookup tables empty then though) I am doing it by number using TDS wizard to transfer it directly to the main table but there must be a better way
I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how.
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
Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is: 1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1 ------ tbl1_id tbl1_data1 tbl1_data2
table2 ------ tbl2_id tbl2_data1 tbl2_data2
table3 ------ tbl3_id tbl3_data1 tbl3_data2
table1 has a one-to-many relationship with table2 table3 has a one-to-one relationship with table2
Hello sirWe have a very huge database its around 6 lakh recordsare being stored in it.records are not being a sorted order so we checked allrecord field through clustering option in Sql server.when we usedclustering records are showing in sorted order but speed of database isvery slow .i want to know with clustering is there any implecation in databaseregading performance od data.Can any one give his views to solve thisproblem?and increase the performance of our database.thanks a lot
I have a query/report that I need to create that needs to look at the size of a company and based on that size apply different rules. I am sure that this is not the only query/report I'll need to do using this and I'm also not so sure that the size ranges won't be changed in the future. Given this, I'd like to store the size ranges in a lookup(global) table. That way, if the ranges ever change I can just alter them in that table and not in all of the queries/reports that use them. What I need to figure out is how to join the live table with the look up table.
Specifically, here is what I have. The look up table would be:
Code Block CREATE TABLE #gl_sizerange ( glsid int IDENTITY(1,1) NOT NULL, lowsize int, highsize int, sizecat varchar(10), milestone varchar(25), days int ) ON [PRIMARY] INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Approach', 14) INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Interview', 14) INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Demonstrate', 21) INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Negotiate', 14) INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Close', 7) INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Approach', 14) INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Interview', 21) INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Demonstrate', 21) INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Negotiate', 14) INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Close', 7) INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Approach', 14) INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Interview', 21) INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Demonstrate', 21) INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Negotiate', 14) INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Close', 7) INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Approach', 28) INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Interview', 35) INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Demonstrate', 28) INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Negotiate', 35) INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Close', 35)
Then what I have is two more tables, one that indicates the size of the company and another that has the milestone contained within it and I will also calculate how long that milestone has been open and if it is longer than what is in the lookup table for that milestone and size range I need it returned in the report. Here are some quick sample table to represent that data (I've condensed the size and number of tables for the example):
Code Block CREATE TABLE #en_entity ( enid int NOT NULL, orgsize int, ) ON [PRIMARY]
INSERT into #en_entity VALUES(1, 5) INSERT into #en_entity VALUES(2, 18) INSERT into #en_entity VALUES(3, 24) INSERT into #en_entity VALUES(4, 25) INSERT into #en_entity VALUES(5, 47) INSERT into #en_entity VALUES(6, 101) INSERT into #en_entity VALUES(7, 499) INSERT into #en_entity VALUES(8, 500) INSERT into #en_entity VALUES(9, 10000) INSERT into #en_entity VALUES(10, 567890) CREATE TABLE #op_opportunity ( opid int NOT NULL, enid int NOT NULL, milestone varchar(25), daysopen int ) ON [PRIMARY] INSERT into #op_opportunity VALUES(1, 1, 'Approach', 5) INSERT into #op_opportunity VALUES(2, 2, 'Interview', 18) INSERT into #op_opportunity VALUES(3, 4, 'Negotiate', 24) INSERT into #op_opportunity VALUES(4, 7, 'Demonstrate', 25) INSERT into #op_opportunity VALUES(5, 7, 'Approach', 7) INSERT into #op_opportunity VALUES(6, 9, 'Close', 35) INSERT into #op_opportunity VALUES(7, 8, 'Close', 36)
So, given the sample data, I would expect the results to return me the following opids from the #op_opportunity table because they don't comply with what is in the look up table based on milestone, size and days open: 2,3,4,7
I have a problem.. I have a report, with subreports, which does the job it was supposed to do. Unfortunately, subreports does not export to Excel very well, so I have to come up with a solution without the use of subreports.
The scenario (keeping it simple)
I have a list of departments. Each department can have multiple costs attached to it. The cost happens on a specific date. Fairly straightforward and simplified
I want to view each departments costs, sorted by month, like this:
Jan - Feb - March - April.... Dep SUM COST
Dep FOR EACH MONTH Dep Dep
... ...
I did this in the old report by passing the department ID and the date interval as parameters to a subreport and have the subreport return the sum of all the fields, which worked fine.
Now that the subreports can no longer be used, I'm struggling on how to sort my output on that date interval. I can group my result pr. department, but how to filter the result so that I get the right numbers in the right month... no luck yet.
As far as I know; I can't use different datasets in one table? I could have made a different dataset for each month...
Anyone know of a way to design this? I'm sure the problem isn't unique. Does one have to aggregate the sums inside the SQL query instead?
hi friends, I m new to asp.net... actually i have an application where the data for current week needs to be displayed from all the data present in different weeks.actually i want to retrieve the syllabus of current week from all weeks i used the below query but itz not working sqlString = "SELECT DATEPART(ww,sylWeeKID)AS Week FROM SylWeeks WHERE SyllabusIDFK= " & SylId & " GROUP BY DATEPART(ww,sylWeeKID) " ..can anyone suggest me how to retrieve data for current week , for a related item thanks in advance i need urgent help.
I have a table with one of the column of xml type. the column contains xml like given below. I want to read this xml from the table and show as below with T-sql query
"EmployeeID" "IndustryDome" "description "
Where Description value comes from the value of AllDome/ITEM/Dome /Description whose Dome equals to IndustryDome value
Purpose - relate Server to Software, and Server to App and display it in power view.Current State - I am able to display that Server/Software and Server/App combos separately in power view.  However when I add Server/Software/App it throws the warning as seen below.  This leads me to believe the relationships aren't transitive so to speak.
Supporting Info - v_gs_installedsoftware is the software table. Â It relates to v_r_system which is the server table through resource id. Â The software table can have many rows with the same resource id, however the server table only has unique resourceids (think of it as a server can have many software products installed). Â Table1 is the application table, it relates to the v_r_system table through the resourceid. Â Each server from the server table can have many applications that it actually plays a role in (think of a web server that hosts multiple websites for different business units).
Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value A | 123 | alpha B | 123 | beta C | 123 | delta A | 124 | some val B | 124 | some val 2 C | 124 | some val 3 etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like: insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha') insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta') insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta') etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.This is something similar to what we get when we execute the followingÂ
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Employees'
But instead of TABLE_NAME i would like to give a query like
SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID].
I came across a frustrating bug last week. Basically, whenever I tried to execute almost any sql query with unnamed parameters (i.e. using "?" instead of "@param_name" in the SQL text), an exception would be thrown.
After trying lots and lots of things and navigating my way through the internals of System.Data.SqlServerCe.dll, I discovered that the method System.Data.SqlServerCe.SqlCeCommand.CreateParameterAccessor(...) has a bug.
The bug is that the private arrays "parameters" and "metadata" are ordered differently, yet at one point in CreateParameterAccessor(...) they are compared using the same index. Here are the two lines: p = this.parameters[ i ]; and MetaData info = metadata[ i ] and then the column data types of "info" & "p" are incorrectly compared in a later method, ValidateDataConversion(...).
So take a step back... how are they ordered differently? From observation, I concluded the following: The "parameters" array is ordered exactly in the order that the DbParameter's were added to the DbCommand object. The "metadata" array is ordered according to the column order of the table in the database.
So what causes the exception? Well, CreateParameterAccessor(...) passes data types from two different columns (one type taken from parameters[ i ] and the other from metadata[ i ]) on to SqlCeType.ValidateDataConversion(...). And, of course, if they differ (e.g. one column is of type DateTime and the other is a SmallInt), an exception is thrown. I've found two workarounds, and both seem to work well. The first is to name the SqlCeParameters (e.g. "SELECT ... WHERE id=@id"). This causes the buggy branch of code to be completely bypassed.
The second is to add the SqlCeParameters in the exact same order as the columns exist in the table you are accessing. Note, I do *not* mean the order that you select the columns (e.g. "SELECT column1, column2, ..."). I mean the actual order of the columns in the database.
I've included my setup and a stack trace below to help if it can.
My setup is: .Net CF 3.5 SqlServer CE 3.5 Visual Studio 2008 Deployed to Pocket PC 2003
Here is the stack trace (note the variables passed to ValidateDataConversion):
I setup this package to import data from a Sharepoint list to a SQL Server data table. The primary key of my SQL table is mapped to the Title column of my Sharepoint list. There is a possibility that duplicate values will be entered in the Title field of the Sharepoint list. So when importing data into my table via SSIS, my package always error-out when there it comes across duplicate values. how you others have managed data integrity when importing from a Sharepoint list with the Title column being mapped to the primary key of a table.
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
We have a daily process, which copies millions of rows of data from one DB to another over Linked Server. Just checking on the best practise, are there more efficient ways than the Linked server to copy millions of rows of data from one DB to another? I checked bulk insert but that transfers the data from the file to DB not DB to DB.Â
I have created a simple package that uses a sql command to pull data from an oracle database and inserts the data into a sql 2005 table. Some of the data fields that i am pulling from contain two digits after the decimal point, however this data is lost when it gets into sql. I have even tried putting the data into a flat file, and still the data is lost.
In the package I have a ole db source connection which is the oracle database and when i do the preview i see all the data I need. I am very confused and tried a number of things to get the data into sql, but none work. Any ideas would be very helpful.
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can€™t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn€™t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea€™s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
when i m importing data from excel to Sql using DTS the column which has text content was not imported as same in excel sheet. whereas a special character is appearing in between the lines. the text field contains multiple lines but the conetent is imported in single line .
I'm wondering if SSIS will be the solution to the problem I'm working on.
Some of our customers give us an Excel sheet with data they want to insert or update in the database.
I've created a package that will take an Excel sheet, do some data conversion so the data types match up and after that I use a Slowly Changing Data component to create the insert/update commands.
This works great. If a customer adds a new row to the Excel sheet or updates an existing row changes are nicely reflected in the database.
But now I€™ve got the following problem. The column names and the order of the columns in the Excel sheet are not standard and in the future it could happen a customer doesn't even use an Excel sheet but something totally different.
Can I use SSIS for this? Is it possible to let the user set the mappings trough some sort of user interface? I€™ve looked at programmatically creating the package but I€™ve got to say that€™s quit hard to do€¦ It would be easier to write the whole thing myself than to create the package trough code ;)
If not I thought about transforming the data in code before I pass it on to the SSIS package in something like XML. That way I can use standard column names and data types.
So how should I solve this problem? Use SSIS or not?
I'm new to SQL and DTS packages. I am trying to import data from an excel spreadsheet to an SQL server table via DTS package. It seems that the excel task looks at the first few records in a column to determine the datatype for that column. If the first few records are text, the entire column is imported as text. If numeric, the entire column is imported as numeric. There are about 25,000 records. In one field, the most important one, about half of the records begin with letters and the rest are all numbers. It is the subscriber ID field, and some subscriber IDs are all numbers, some are letters and numbers. The entire column should be imported as text. However, when I run the transform data task from the excel connection, none of the records that are all numbers are imported. I end up correctly importing only 13,000 of the 25,000 records. The rest are imported with the subscriberID field as <NULL>. I tried using the CAST or CONVERT function in the SQL query, but get the error message "Undefined Function."
hello, I create a txt file with a bash script, and i need to use it in a DTS package. But, i don't know how i can specify the type of my column. So in the transformations task, i have an error due to an incompatible type. what can i do to fix this error ? thanks,
I am creating a DTS package that is combining several tables, converting one column of data to a new column removing all special characters, then exporting the unique data based on this column and another column, and the max of other duplicates to a new table.
Now that I have the data in this table, I want to import any data that is not in my main table.
This "CLEANED" table does not have a designated "key" column, but the table I want to import the unique items does have an ID column that is also a primary key column.
DTS seems to want me to have a Key column to reference when importing from the CLEANED table to the MAIN table.
How would I go about checking the MAIN table against the CLEANED table, having DTS import only the unique items from the CLEANED table that are not present in the MAIN table based on three columns? The rest of the columns I want to just extract the MAX data from the duplicates.
Now here is the query I use to extract the unique values from the "CLEANING" table to get the data to the "CLEANED" table, but do not know how to use this to import into the MAIN table using something similar.
Code:
select partno2, MAX (partno) as partno, alt, MAX (C_alt) as C_alt, Max (cmpycd) as cmpycd, MAX (type) as type, compFN, MAX (pndesc) as pndesc, MAX (equipment) as equipment
into tbl_CLEANED from tbl_CLEANING group by partno2, alt, compFN ORDER BY partno, compFN
The three main columns I need to check against are: partno2 alt compFN I have named the columns the same in both tables.
partno2 is the column that has been copied from partno with all special characters & spaces removed. This is the main column I am using as a reference for unique values, then if no match, I have it check against the alt column, then the comFN column. If there are no matches in any of these columns, then I want to extract the data to the MAIN table.
How can I compare these tables and import only unique info to the MAIN table?
In addition, how can I also check items that are the same in both tables and update the MAX info for the other columns (not the three I use for reference - these I need to leave alone) and update those if there is more data in the CLEANED table then in the MAIN table?
