I have a table in mssql7 that has 7000000 record and I want to take
100000 records out of it and place them into the new machine with
mssql2000. The new machine will also have the same table name, so I
want to append the 100000 records into that table.
Thanks,
Royal344
--
Direct access to this group with http://web2news.com
http://web2news.com/?comp.databases.ms-sqlserver
Hi all,
I hv very little experience handling mssql7. Recently I was assigned a task to maintain a website that uses mssql 7. My system admin has already installed mssql7 server components on a Win2K box and I have only managed to install mssql7 client components on my winXP box.
Whenever I try to connect to the remote server using enterprise manager, I always encounter the same error message complaining that
Quote:
A connection could not be established to "Server Name" - Access denied
ConnectionOpen (CreateFile())..
Please Verify SQL Server is running and check you SQL Server registration properties (by right clicking on the GAMMA
node) and try again.
I have used the client network utility in my winxp box to set up an alias for the server using various network libraries such as TCP/IP and named pipe, but it keeps on returning the same access denial message.
I am running out of brain juice figuring about what's wrong. Could somebody please help me? Thanks in advanced.
Transfer tables from one server to the other on a regular basis. I'm doin this using the DTS import wizard (saved it as a package and scheduled a job). But i noticed that if the table structure changes on the soruce tables, the DTS package will fail.
I want to make a DTS package that imports 6 tables, now how do i make it such that if the structure of the tables changes it shud still work. ie, everytime the tables are tranferred, the old tables on the target server must be dropped and re-created with the source server table definition. I hope this is doable.
Plz help if ne one has done this b4.
Thx
Hi!
I have 2 tables (both have the same structure):
ID -> bigint (identity, not for replication, primary key)
Url -> nvarchar(1000)
MainUrl -> nvarchar(1000)
Tbl1 cantains about 0,5 mln records, and tbl2 - 1 mln.
What I need, is to copy records from tbl2 to tbl1. But records in tbl1 are unique, and it can't change. (Unique must be only "Url"; (and ID, but it's automatic)). How can I do this in fast way? Now I'm using SELECT for each record in tbl2 to see if it exist in tbl1. But it's a bit slow... Is there any faster method? (One thing: I'm beginner in databeses, so I'm wrote VB application to transfer records. How can I do it using only Microsoft Sql server?)
--------------
I'm forgot to write, I'm using MsSql 2005.
Hi,
I am have a two tables. I want to transfer data from Table1 to Table2 but depending on BRK_TITLE. For example, if BRK_TITLE is "Testing" then table1.BRK_QUANT would go to Table2.Qty1, if BRK_Title ="My Testing" then it would go to table2.qty2 and so on...
but problem is every time BRK_TITLE changes. these are not constant titles(e.g. title may be 'abc', '134' etc)
If data belongs to title3 then it would go to the specific quantity i.e. table2.qty3
Table1:
DB_CONTRACT
ITEM_NO
BRK_TITLE
BRK_QUANT
----
table2:
DB_CONTRACT
ITEM_NO
BRK_TITLE
qty1,
qty2,
qty3,
qty4,
qty5,
qty6,
qty7,
qty8
I hope i convey my point of view?
I'll really appreciate your help
Hi, I'm working on Sql Server 2005 Express and I'm trying to transfer over some of my tables with the primary and foreign keys relations as well as the data in them from one asp.net 2.0 website to another.But I can't seem to find the Import/Export option in Sql Server 2005 Express Edition, does anyone know how I can do this?Thanks in advanced.
View 1 Replies View RelatedHello:
I need to develop an off-line database that replicates/copies some key tables for reporting. The info is fairly static (codes. etc.)and does not need to be real-time.
Is it feasible that I can set up a scheduled task on SQL 6.5 using Transfer Manager to copy & append data to this off-line db on a schedule.
I may not be able to set-up replication on our production server.
So, I am considering alternatives. Appreciate any input. Thank you :)
Hi,
I have two tables named Tab1 and Tab2. Both are identical in structure. The only diff is Tab2 has two more additional fields (FromDate and ToDate).
The structure is like below :
Col1
Col2 (Date field)
Col3
Col4
Also Tab 2 have
Col5 (From Date)
Col6 (To Date)
Now I want to transfer some set of reocrds from Tab1 to Tab2. The additional Tab2 field (Col5 and Col6) values should be the minimum and maximum values of Tab1 date field for the current set.
How to accomplish this? Kindly help me in this regard.
Thanks
Somu
hi iam new to writting stored procedures
so anyone please help me out.
i have to transfer data from one table to three other new tables.
and if there are any duplicates in original table i have to send them to
duplicates table.the remaining data should be send to three other tables.
so can anyone help writting stored procedure for this.
thanks for your suggestions and answers
regards
ramya.
Hello, I like to know how to transfer current data displayed in my datagrid into a table in sql server. Any help is much appreciated.Thanks!
View 1 Replies View RelatedHello,
I am transferring data from one SQL table to another. The first table has a PK on the unique id only, the second table has PK on five fields (the idea being to reject duplicate records etc. etc.). I am using a DTS package to do this, but when run it will fail when it hits a PK violation. How do I getround this??????? What simple thing am i missing??
TIA DVNC
When executing an inline insert into command from isqw/w the command executes perfectly. Yet when I try to create a stored procedure with exactly the same insert into statement it will not create. It does not like the database.owner.table prefix on the selected rows. Any suggestions ??
Thanks.... Tom
How is it possible to set data to flow through development,test,and production environment???????
I have created one package.Right now i am transferring data to tables in the development environment.
I want to use the same package for several diffrent distinations two based on environment.
Is there a way that the destination componnent sets itself automatically by passing destination tables through registry or is there anything i could or do i need to change manually everytime which ever i want to..
Transfer tables with Data from one database to another one on a same server?
View 10 Replies View RelatedI have a SQL Server 7.0 installed on a Compaq PC running on Windows NT. This PC has 64 MB of RAM and it is a Pentium 166. There are 3 Clients connected to it. If there is only one PC running an apllication requesting data from the server, the performance is tolerable, but when there is more than one PC simultenously requesting data from the server, problems araise. Either all of the PCS running the application show zero records on the apllication or one of them show no records. Any mouse click action on the clients will bring the server to almost a halt. Can anyone tell me the reason to these problems?
View 1 Replies View RelatedWhile installing SQL 7.0 it turned out that the size of my primary patrtition where the Windows NT system files are {C: drive) does not have enough space to take the MSSQL7 system files it wanted to install there. Can the size of this partition be increased (am using FAT file system), can the MSSQL7 system files be installed somewhere else, or should I just do an f disk and start all over again with a new Windows NT installation? (No data on this server yet.) Thanks for your help!
View 2 Replies View RelatedWe've experienced a problem with one of our MSSQL7 servers where transactions are not being committed to the database. We've experienced this in Query Analyzer -- where we run several (simple) insert or update statements. Although the script runs successfully, the rows do not appear in the tables. The implicit transaction setting is off for the server.
We have also experienced this problem with other tools that run against this server via ODBC.
I thought the default setting for MSSQL7 is to auto-commit transactions and I've seen nothing in the error log that indicates that the updates were rolled back. I have compared our server, database and client configuration options to other servers that do not have this problem and have no idea where to look next.
Has anyone encountered this problem? Or does anyone have some ideas about where I could look for more information?
Thanks!
Hi all,
Currently, my (small) intranet site is storing it's data on a remote SQL server. The danger with this, as has happened several times now, is that the application is twice as vulnerable; if either the webserver or the dataserver malfunctions or is unreachable, the application won't work.
I only recently discovered the possibility to use local database files (MDF files), and this seems like a much better solution for my site. But now I want to transfer the tables that are residing on the dataserver, to the MDF file. The database only contains tables. How do I handle this? I do not have access to the dataserver, only to a few databases that are residing on it. Is this possible using Visual Studio 2008? I have read about a "Bulk Copy Program" (bcp) which is included with SQL Server, but I cannot find a download for just that application.
Or is this totally not the way to go? I've discovered MDF files are a bit more problematic with concurrent connections; having tables open in Visual Studio results in "Site offline" or "Cannot open database" error messages on the website. Problems I've never had to deal with using SQL Server, but they are only minor problems.
Thanks,
Peter
Hi,
I am new to SQL Server 2005 SSIS Packages. I want to transfer data from multiple tables from sql server to oracle database. I cannot use export wizard as it creates new tables in the destination (oracle) DB. I already have tables created in the destination DB. When I created an SSIS package, it allowed me create package to tranfer data for only for one table from source to destination. I have created DTS packages in 2000, where you have source db and destination db and just add links for multiple tables. Is there a way I can do it in SSIS. Please let me know.
I have few issues regarding the transfer of the tables from one file group to another file group in SQL 2008 and also How can we backup and restore the particular database based on file group level.
Let’s say I have a tables stored within the different FG. such as
Tables
File group Dimension tables Primary Fact tables
FG1 ...
FG2…
zzz_tables DEFAULT_FG
dim.table1 DEFAULT_FG
dim.table2 DEFAULT_FG
… ….
Here all I want to transfer the dim.table1 ,dim.table2 from DEFAULT_FG to the Primary File group .So is there simple methods for transfer the dim.table1,2 from one FG to another .I have tried somewhat but I couldn’t get the exact way.Secondly after moving those dim.table1 ,dim.table2 from DEFAULT_FG to Primary ,All I want to backup and restore the database only containing the Primary and FG1,FG2… not a DEFAULT_FG.Is it possible or not.?
Hello,
I created ssis package to transfer tables from one DB to another.
However, I don't find the option where I can make that the transfer with the indexes and pks of the tables. in sql2000 I had this option.
appriciate your help.
Kubyustus
Hi,
I am trying to figure out a way to link a MySQL database (running on Linux) to my MSSQL7 database. My ultimate goal is to synchronize inserted/updated/deleted data in the MSSQL7 DB to the MySQL DB.
I have tried a few things and have had a few ideas. They are:
1) Create table triggers in the MSSQL7 DB that will synchronize the data to the MySQL DB. I can't figure out how to run Transact/SQL statements (INSERT, UPDATE, DELETE, etc.) into an ODBC DSN pointing to the MySQL DB. I think this could be done with a linked server (see next item).
2) Add a linked server under Security->Linked Servers in the MSSQL7 Enterprise Manager. I have managed to get the remote MySQL server to link in here. I can even see the table names in the server browser, so that's encouraging! However, I cannot figure out the syntax of the fully-qualified table names in my SELECT statements. I have tried this:
SELECT * from LinuxBox..MyDB.MyTable
-and-
SELECT * from LinuxBox.MyDB.MyDB.MyTable
but I get:
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
This statement:
SELECT * from LinuxBox...MyTable;
gives me:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
Since I can see the table names in the linked server browser in Enterprise Manager, though, some kind of connection must be active. I just need to figure out how to refer to the remote tables.
A big problem is that when I supply a catalog name while defining the linked server, I get an error message from the Enterprise Manager:
Error 7399: OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
Therefore, I can't define a catalog with the linked server. I think a catalog is required for the "4-part name" to work in the SELECT statement above.
3) Write an extended stored procedure that uses the ODBC function library to make a connection to the MySQL DSN and have a table trigger call the ESP. Hoping to be able to do something simpler than this!
Any suggestions would be greatly appreciated!
Thanks,
Greg
Dear All,
could you please tell me if MSSQL7 is compliant with Windows 2000 ?
Which Service Pack is needed from both MSSQL and W2000 ?
many thanks in advance for your help ;)
Hello
I have a production database that i need to refresh to our test environment daily. The database size is 700 MB.
I do not need to transfer the stored procedures and triggers , users and logins.
Would a DTS package that runs every night be the best and the easiest solution to implement or should i look into log shipping and snapshot replication.
thanks
Hi,
I have a Users table in Oracle database and same table (Users) in SQL Server 2000 database. I want to create a DTS Package through which I can copy the data from Oracle database to SQL Server 2000 database. This package should run automatically at mid-night daily so that if there are some entries done in Oracle database then it get copied in SQL Server 2000 database. Also is there any way to copy only those entries from Oracle database which are not present in SQL Server 2000 database. Please help me in this regard as I am new to DTS.
Thanks
Rohit
I'm trying to bcp logins from 6.5 to 7.0 while maintaining the passwords. I tried doing a bcp in native format, but when I try to bcp in, I get a Dr. Watson. I figured out the issues with syslogins not be updatable on 7.0 because its a view. I'm also aware of the problems associated with the fact that sysxlogins on 7.0 has different column definitions than syslogins on 6.5. I just don't know how to get around these roadblocks. Anyone have any ideas??
Thanks in advance,
Darias
I was using MSSQL7 for a long period.
I upsized to MSSQL7 from Access some years ago. Without any particular reason when writing code in stored procedures, when I wanred to select some records having a bit column to true I used the syntax bitcolumn=-1 (and not bitcolumn=1). This behavior was used in Access. Everything worked fine. Then I moved to MSSQL2000 and by restoring the MSSQL7 database I had no problem.
However, in order to use some features of MSSQL2000 I had to run the sp
sp_dbcmptlevel <database>, 80
After that the condition bitcolumn=-1 didn't work.
Can anyone verify this behavior, since I have to make dozens of changes in my stored procedures and triggers???
Regards,
Manolis Perrakis
Hi there,
Does anyone know if it's possible to perform a MSSQL7 database backup on a network drive instead of the local drives ?
...and if yes, how to proceed ?
It seems to be possible under MSSQL2000.
Many thanks for your help in advance,
Matt
Sorry about the huge post, but I think this is the amount ofinformation necessary for someone to help me with a good answer.I'm writing a statistical analysis program in ASP.net and MSSQL7 thatanalyzes data that I've collected from my business's webpage and thehits it's collecting from the various pay-per-click (PPC) engines.I've arrived at problems writing a SQL call to generate certainstatistics.Whenever someone enters our site from one of the PPC search engines, Iwrite out a row to the Hits table. In that table are the followingcolumns:HitID - the Unique ID assigned to each hit that comes into the siteKeyword - the keyword the user searched on when he or she came to thesiteSearchEngine - the PPC engine the user came fromSource - this is pretty much always 'PPC'...if we were to do otherthings, like a newsletter, then this would be different.TimeArrived - the date and time the user arrived at the website. Ihave no idea why I didn't call it "datearrived," since I use "date"and not "time" pretty much everywhere else...(I don't think the rest are important, but they might be, so I'llinclude them for completeness's sake)Referring URL - the URL the user came fromReferring Website - the string between the 'http://' and the first '/'in the URL. I know it's redundant information, but when I designedthis part, I didn't know how to parse it out afterwards, so I justfigured I'd duplicate it.Page Visited - the page the user first arrived atWhen a person comes to the site, I also write out a session cookiecontaining the user's hitID. If the person fills out an enrollmentform (a process which we refer to as "responding"), I attach thatsession ID to the form. The response form (and thus the responsestable) is long; these are the important fields:id - a unique ID for each responsedate - the date and time of the responsestatus - a varchar field containing a status code. I would have madeit a number, but I wanted it to be viewable from looking at the rawdatabase.hitid - the HitID of the user, taken from the session cookie. If thereis no session cookie (for whatever reason), the HidID is written outas 0. While it wouldn't occur often, I can't guarantee that there willnever be more than one response record attached to a singular hitid.Later, some of the responses turn into "confirmations", which meansthat they've actually ordered from us, not just filled out the form.This usually happens about three or four days after the initialresponse. When this happens, the status of the response is changed toa phrase containing the word "confirm" in it (there are a few of them,but they all contain that word).So now that we've collected all this marketing intel., I need toanalyze it.I've written a parser that takes reports from various pay-per-clickcompanies and puts them into a table called PPC. Information in thiscolumn is written out as one record per search engine per keyword perday. The schema is as follows:id - a unique ID for the record in the tabledate - the date to which the information in the record appliessearchengine - the PPC engine to which the information applieskeyword - the keyword to which the information appliesclicks - the number of clicks on the applicable keyword on theapplicable search engine on the applicable day.impressions - same as clicks, but for impressionscpc - the cost per click on the applicable keyword ...avgpos - (I don't always have a value for this field) The averageposition that the keyword was shown in for the applicable keyword ...With this data in, the last step is actually analyzing the threetables for useful statistics on the various keywords, search engines,and time frames. That's the step I've been trying to complete.So what I need is a SQL call that I can run that generates a tablewith the following information:SearchEngineKeywordCost / Click - When calculating the CPC, I can't just take an averageof all the records. I need to calculate the total amount spent per day(clicks * cpc), add that up for every day, and then divide that by thenumber of total clicks. Just doing an average doesn't take intoaccount the fact that some days we'll get more clicks than others.Total Spent - # Clicks * CPC#Responses - counting the number of records in the responses table#Confirms - counting the number of records in the responses table with"confirm" in their statusTotal Spent / #ResponsesTotal Spent / #ConfirmsOh yeah, and I want to be able to order by any four of the fields inany order, narrow my selection to only those keywords that either areor contain a user-specified string, further narrow my selection toonly those records that fit other user-specified criteria for any ofthe columns in the table I'm generating, and select only the top xrecords (where x is a user-specified number). I already haveuser-controls that output the SQL for all of these things, but I needto have places in which I may put that SQL in my call.After many trials and tribulations, I've come up with the followingSQL call. Right now, its output for nearly every row is incorrect, Ithink in a large part due to the fact that the method that I'm usingto generate the number of clicks is yielding incorrect values.If you'd like to help me and you think that modifying the followingcall is easier than writing a whole new one, be my guest; if you'dprefer to write a new one, I'm game for that, too. I'm just concernedwith its working right now, and any help you can give me is greatlyappreciated.Anyway, here's the call:/*sp_dboption @dbname='NDP', @optname='Select Into', @optvalue=true;*//*Running the above might be necessary to get the "Select Into"s towork*/Drop table ResponsesPPCDrop table ConfirmPPCDrop table TempPPCSELECT Responses.[ID] as [ID], Responses.Status, PPC.SearchEngine,PPC.KeywordInto ResponsesPPCFROM Responses, PPCWHERE Responses.HitID IN(SELECT Hits.HitIDFROM HitsWHERE Hits.SearchEngine = PPC.SearchEngineAND Hits.Keyword = PPC.Keyword)SELECT ID, Status, SearchEngine, KeywordInto ConfirmPPCFROM ResponsesPPCWHERE Status LIKE "%confirm%"Order by SearchEngine, KeywordSELECT PPC.SearchEngine, PPC.Keyword,SUM(PPC.Clicks), /*I noticed that thiscolumn gives me incorrect values(I don't need it in my final report, but it's useful for debugging).For some keywords, it gives me huge numbers(e.g. 265 clicks on one word that got ~10 clicks /day over five days),and for others, it doesn't give me enough. I think this is a majorpartof what's throwing off the rest of the statistics*/Case SUM(PPC.Clicks) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / SUM(PPC.Clicks) END as CPC,SUM(PPC.clicks * PPC.cpc) AS TotalCost,count(ResponsesPPC.ID) As NumResponses,Count(ConfirmPPC.ID) As Confirms,(Case Count(ResponsesPPC.ID) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / count(ResponsesPPC.ID) END) ASCostPerResponse,(Case Count(ConfirmPPC.ID) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / count(ConfirmPPC.ID) END) AsCostPerConfirmFROM (PPC LEFT JOIN ResponsesPPC ON PPC.SearchEngine =ResponsesPPC.SearchEngineAND PPC.Keyword = ResponsesPPC.Keyword)LEFT JOIN ConfirmPPC ON PPC.SearchEngine = ConfirmPPC.SearchEngineAND PPC.Keyword = ConfirmPPC.KeywordGROUP BY PPC.SearchEngine, PPC.KeywordOrder by PPC.keyword desc/*Drop table ResponsesPPCDrop table ConfirmPPCDrop table TempPPC*//*I don't drop them right now so I can look at them,but normally, one would drop those tables.*/Thanks a lot for your help,-Starwiz
View 4 Replies View RelatedI'm using a Business Intelligence project to copy stored procedures and tables from one database to another across servers. I'm having trouble copying tables or stored procedures using the Management.SMO.Transfer class.
I tried copying stored procedure with the property transfer.CopyAllStoredProcedures = true. This didn't work. As a workaround, I used the StringCollection property and executed every string as sql.
Now I'm having trouble copying tables. I don't want to copy all the tables in the database. How do I go about selecting what tables to copy. I tried using ObjectList property and provided the names of the tables in an ArrayList. I get the error
"Transfer cannot process System.String. You need to pass an instance class object."
How can I pass an "instance class object" for something that's in the database? The ScriptTransfer method fails so I can't even see the script that is being generated. There is virtually no documentation for this class.
Any help will be appreciated.
Transfer transfer = new Transfer();
transfer.Database = sourceDB;
transfer.DestinationDatabase = DestinationDatabase;
transfer.DestinationServer = DestinationServer;
transfer.DropDestinationObjectsFirst = false;
transfer.CopyData = true;
transfer.CopySchema = true;
transfer.CopyAllObjects = false;
transfer.CopyAllTables = false;
transfer.CopyAllStoredProcedures = false;
transfer.Options.WithDependencies = false;
GetTablesToBeCopied();
transfer.ObjectList = tablesList;
transfer.Options.FileName = "C:\TransferScriptTables.sql";
StringCollection coll = transfer.ScriptTransfer();
anyone has tried? please advice possible issues, workarounds
tks
Greetings!
I recently rewrote an old ACCESS 97 application to work with MSSQL 7.
The program works fine until I use a form whiche opens several linked tables at the same time. When closing the Form I get (At unpredictable moments) an ODBC SQL SERVER DRIVER update failure error message.
All rights are set on the tables for Public and even then i keep getting this error at very odd moments. I also had a look in Access at the ODBC refresh rate which is set at 10sec , OLEDB refreshrate 10 sec, and refresh is set at 5sec.
I tried different combinations of timeout settings and even renewing the MDAC to version 2.7.
Nothing helps.
Can anyone give me a tip as to look for the possible source of the error and how to fix it?
Thanks!
Vincent JS
Hi everyone!
I start to get panic now after 10 hours of different ways of trying to
get my old database from my [Server1]* to my [Server2]*. When Im
trying with the "SQL Server Management Studio" that included in
the MSSQL 2005 but that just tells me it dosn't support any version
before MSSQL2000.. I've also tryed with some third part softwares
like EMS but no joy!... Its 5 databases from the old server that I MUST
get into the new one.. Anyone know howto do this?
[Server1]
Windows 2000
MSSQL7
[Server2]
Windows Small Business Server 2003 R2.
Includes MSSQL 2005 Premium edition.
Kind Regards
// Magnus Dahl