Any syntax where i will copy an sql server table to another table using BCP.im using sql server 2000 on vb.net, DTS isn't working for me because i want to edit some conditions on the package and it just dont work well.thanks in advance!
View 5 RepliesI am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
Hi all,
I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.
How can it be done?
Thanks, Michael.
Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
Превед!Prompt me please some asp.net 1.1 pivote table component that can use both SQL Server database (SQL queries) and Analyse Services database (MDX queries).Thanks.
View 3 Replies View RelatedThis question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
I found that when I install MSDE, then I can create/edit database objects for MSDE database in Visual Studio 2003 Server Explorer, but when I try to create/edit database objects under the default database created by the SQL Server 2000 installation, I cannot do these tasks as no option for these tasks appear when I right-click on the database object in Server Explorer. Anyone knows why this is happening?
View 3 Replies View RelatedHi!
I just used the SSIS Import and Export Wizard to copy 50+ tables from SS05 to SS2K.
I found that the wizard created a package that I could not figure out how to edit, e.g., to change whether or not it had to CREATE a table, or just use an existing one. (I created some problems by manually editing the receiving table names to be ones that already existed -- but the original names it had did not exist, so it knew it had to create them. What I should have done, and eventually ended up doing, was scroll through my list of tables in the "receiving" box; I just figured editing the name would be faster, not realizing what problems I would create for myself.)
Anyhow, now that I see the complex package that the wizard creates, with a LOOP over the 50+ tables, I would like to know how/where in the package it is storing the information about the tables to copy.
Basically the wizard creates the following Control Flow tab entries (in processing sequence order):
an Execute SQL Task: NonTransactableSql
an Execute SQL Task: START TRANSACTION
a Sequence Container: Transaction Scoping Sequence, which contains
an Execute SQL Task: AllowedToFailPrologueSql
an Execute SQL Task: PrologueSql
a Foreach Loop Container, which contains
a Transfer Task with an icon I did not notice in the Toolbox
an Execute Package Task: Execute Inner Package
an Execute SQL Task: EpilogueSql
an "on success" arrow to
an Execute SQL Task: COMMIT TRANSACTION
an Execute SQL Task: PostTransaction Sql
an "on failure" arrow to
an Execute SQL Task: ROLLBACK TRANSACTION
an Execute SQL Task: CompensatingSql
Where, and how, can I look within this package to see the details about the tables I am transferring? I see that one of the Connection Managers is "TableSchema.XML" -- but it points to a temporary file on my hard drive, that I presume is populated by the package. Where does it get its information?
This is certainly much more complex than the package I would have written, based on my limited knowledge of SSIS. I would have been inclined to create 50+ Data Flow tasks, one for each table.
So now I'm trying to understand why the Wizard created this more-complex package.
Any help will be appreciated, including references to non-Microsoft books/websites/etc.
Thanks in advance.
Dan
i need a query to return the top 10 tables in each database on a server. have used EXEC sp_msforeachtable 'sp_spaceused ''?''' which returns what I need for one db but I need it to loop through and gather the info for all dbs on server. maybe need cursor not sure. for reporting reasons i would like to include the name of the server and name of database.
View 3 Replies View RelatedCan I access SQL Server 2000 tables via SSIS/SQL Server 2005?
View 1 Replies View RelatedWe have 20 -30 normalized tables in our dartabase . Also we have 4tables where we store the calculated data fron those normalised tables.The Reason we have these 4 denormalised tables is when we try to dothe calcultion on the fly, our site becomes very slow. So We haveprecalculated and stored it in 4 tables.The Process we use to do the precalcultion, will get do thecalculation and and store it in a temp table. It will compare the thetemp with denormalised tables and insert new rows , delte the old oneans update if any changes.This process take about 20 mins - 60mins. Ittakes long time because in this process we first do the calculationregardless of changes and then do a compare to see what are changed andremove if any rows are deleted, and insert new rowsand update thechanges.Now we like to capture the rows/columns changed in the normalisedtables and do only those chages to the denormalised table , which weare hoping will reduce the processing time by atleast 50%WE have upgraded to SQL SERVER 2005.So We like to use the newtechnology for this process.I have to design the a model to capture the changes and updated onlythose changes.I have the list of normalised tables and te columns which will affectthe end results.I thought of using Triggers or OUTPUT clause to capture the changes.Please help me with the any ideas how to design the new process
View 3 Replies View RelatedMy searches have come up blank, so I'm hoping someone else can point me in the right direction.
I'm done some development in SQL Server Express, working on tables and procedures. I've now got to move those tables/procedures into SQL Server and I don't know of an easy way to do this. Are there any tools I can take advantage of that would allow me to get all these objects moved? The only way I can see to do it is to move objects over one-by-one. This would be really time-consuming, and I'd like to avoid it if possible.
I'm positive I'm not the first one to try this, but what should I be searching for? Are there tools/procedures I should be looking for?
Appreciate any help I can get.
Thanks.
I have been giving the task of profiling databases and all the tables within it. Right now I'm collecting the following:
Null_Count
Null_Percentage
Total_Record_Count
I'm looking to capture Mean_value, Min_value, and Max_value and the unique_count.
I need to generate a script where it should check the tables of a database from one server to another server(If server 1 database XYZ has 10 tables then server 2 database XYZshould match 10 tables). If the tables matches then truncate tables data and insert the data from one server 1 database XYZ to server 2 database XYZ. If tables does not matches, then it should check and create that tables which are not matching.
View 8 Replies View RelatedHi everybody,
Please suggest me a guideline to upload tables on sql server
for internet based application.
Actually I want to know the exact procedure what are the files to
upload (of sql server) OR i have to export the whole database
to the domain server.
Thank you
Harish
I have VS and SQL server database engine installed in the my office PC (Both legal copies). I would like to see the tables and like to create the new tables / databases as well for learning. How to do that?
Please help me....
I have been reviewing the video series at this site and have found them very helpful but have a need...
I have two data tables one of which is quite large and another table that is small. They have at least one exact field in common, lets call it room number. How do I recognize both tables (in different databases) and join them so I can search a unique item in one table and select its associated data in the other table? I don't image there is a video on this topic for this VB.NET student?
Hello,
I currently am in charge of creating a Sharepoint 2003/Exchange 2003 ticketing system. We have a client database (MS Access 2003) that has a linked table to our Exchange store, retrieving information we enter in Exchange regarding specific clients. I would like to move this database to SQL Server for performance reasons. Soon there will be many people accessing this database and I don't want the bottle neck to be Access.
The only quirk I can think of is, can one link a table from SQL Server to Exchange. Or for that matter, can SQL Server have linked tables at all? One solution I can think of is to create a System Service that runs on the server, to periodically update the SQL table with the information in the Exchange store. But that could become costly over time with large amounts of network bandwidth being used for unnecessary updates.
Is there maybe another solution I'm not seeing? Should I just stick with the Access database?
Thanks
Josh
Hi,
I am working on IBUYSTORE's db. I have added a backoffice/admin to the site.
I have a page where I retrieve a customer's details based on the customerid such as the following:
Dim Ssql As String = "SELECT O.CUSTOMERID, C.CUSTOMERID, C.PASSWORD, C.FULLNAME, C.EMAILADDRESS, O.ORDERID, OD.ORDERID , OD.PRODUCTID FROM customers AS C , ORDERS AS O , ORDERDETAILS AS OD WHERE C.customerID=O.CUSTOMERID AND OD.ORDERID=OD.ORDERID AND C.customerID = @cust_ID"
CMD As New SqlCommand(Ssql, oConn)
CMD.Parameters.Add("@cust_ID", id)
I am also using a sqldatareader.
I have a delete button. If the site manager clicks on that button i want to delete all that customer's details including all his orders from both orders and orderdetails tables.
I am not sure how to accomplish it.
The tables are as follows:
Customers: Orders: OrderDetails
customerid customerid orderid
orderid
I tried the following delete:
strDelete = "DELETE FROM CUSTOMERS where CustomerID=@CUST_id; DELETE FROM ORDERS WHERE CUSTOMERID=@CUST_id; DELETE FROM ORDERDETAILS WHERE ORDERID=@orderID "
Dim objCMD As New SqlCommand(strDelete, oConn)
objCMD.Parameters.Add("@CUST_id", id)
objCMD.Parameters.Add("@orderID", LBLORDER.Text)
oConn.Open()
objCMD.ExecuteNonQuery()
oConn.Close()
I get the following error:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'. The conflict occurred in database 'Store', table 'Orders', column 'CustomerID'. DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_OrderDetails_Orders'. The conflict occurred in database 'Store', table 'OrderDetails', column 'OrderID'. The statement has been terminated. The statement has been terminated
Can anybody tell me how to use the query analyzer to
connect to 2 different servers in one query?
If I access a database on another server like that
[servername].[dbname].[dbo].[tablename] I recieve
an error that says: Server servername is not configured
for DATA ACCESS.
Thanks in advance
Gert
Is there a way to rotate db tables - i.e. to pivot them?
Thanks,
Bianca
I have a table that is used for reporting, the problem is that the data in the table is refreshed every 30 minutes with a bulk insert. I am trying to find a way to have two tables that are mirror images of each other and when the loading table is loaded, then the table assumes the identity of the reporting table. The basic prinicpal is I need to have the table be available almost all the time and when the bulk insert is happening, users cannot query. Any help would be greatly appreciated.
Thanks in advance
Marc Brown
I have a 100m row table that I need to come from one database to another database in SQL SERVER.
The bulkcopy feature in DTS is nice -- however is there a stored procedure or external software that will be able to do this outside of DTS.
Right now I am doing a
SELECT *
INTO
(table name)
FROM (table name)
and on a 100m row table it is taking around 52 hours. Not acceptable.
Hello Everybody,
Can anyone tell me how to create a pivot table in SQL Server?
I am attaching a picture of what the table should look like before and after the transformation. Thank you in advance.
I have two tables that share (supposedly) 2 fields (PartID and RaceID) and those two tables should be identical as far as those two fields are concerned. That is, there should be the same number of rows in both tables and if listed in the same sort order in reference to these two fields, they should be identical. The problem is, they are not. There are in excess of 3000 records in each field and I need to write a query that will allow me to compare them row-by-row.
I am using sql server 2000 and I am (kind of)familiar with the SQL Query Analyzer. What I really need to know is how to write the select statement that will allow me to compare the two tables line-by-line to find the discrepancies or, better yet, simply show the discrepancies so I can focus on them.
Thanks!
Hi all
I have 15 tables in my database and I want to create a pdf file with description about all the columns of these tables(Data Model)
Few questions
How can I get all the column names in the excel or pdf file?
Is there any tool which can give me the output template for these columns and then I just have to fill the description for each column?
Thanks
Hello All.
I need your advise on how I could access table from another server.
Let me explain what I am doing. Currently, I have 2 servers running parallel to one another other each loading from the same set of data into its own database in the night.
I would like to write a stored procedure to report the number of records from selected tables from the 2 servers to see if they match in records loaded. Thereafter, email me the results (I have already settle the sending of email part).
Have picked up some tips in this forum to come up with the below statements and it works fine for me: -
use AABBCC (database name)
select Server='Server 009', name, rows, SystemDate=getdate() from sysindexes
where name in ('invoice_line', 'invoice_line_import', 'sales_order_line', 'sales_delivery_line','Cust_dely_reliabity','sales _order_proc_time','AU1016','NZ_Data')
order by name
Result: -
Server Table Name Rows
Server 002AU1016 0
Server 002Cust_Dely_Reliabity 5017342
Server 002invoice_line 4397800
Server 002invoice_line_import 12120
Server 002NZ_Data 649745
Server 002Sales_Delivery_Line 5323875
Server 002Sales_Order_Line 5356989
Server 002Sales_order_proc_time 3651362
Instead of running the above scipt or stored procedure from 2 servers and received 2 email (one from server 002 & one from server 009), I would like to access the other server within the same stored procedure.
I have used sp_addlinkedserver (may not be the correct method) and <servername>.<databasename>.dbo.sysindexes (doesn't work)
Please help. Thanks a million.
The desired result could be:
Server 002 .....
Server 002 .....
.
.
Server 009 ..... (result from the 2nd server)
Server 009 .....
.
.
Best regards
Hi,
Is there any way to open .TPS(TopSpeed) tables via SQL Server 2000???
hi
how to get list of all tables that exist in my sql server instance ?
i'm using this scrpit :
select * from sys.tables
but this command return only tables that exist in Current Database.
thanks
Hi everyone
I have 2 severs. I want to copy 3 tables (data, table definition, inex..ect) from sever A to sever B.
What is the best way to do it?
Please help!
Hi,When we make a search for a table in SQL Server 2000sysindexes table is searched and indid field is checked,If it's 0 we go for full table scan (No Indexes Present)If it's 1 it means clustured index column is used for search.2-254 means non-clustred indexes used.Thank you very much for taking time and replying.Thank you,Miks
View 1 Replies View RelatedI am using MS SQL Server management studio.I treid to see some system tables which are sysobjects, syscolumns,systypes, etc.., but i don't see the list under the system tablesfolder. There is one table showing, sysdiagrams; however, I was ableto query sql stmts though.where are they located? or Do I need to contact a dba to release thosetables? thanks.
View 1 Replies View Related