I am trying to set up a DTS Package to transfer data from a remote
MySQL server to a local MS SQL database. The source tables will remain
static as will the destination however I only want to copy down data
that does not already exist in the local database.
I have two different methods of which to identify the new rows, on some
tables it is by a unique ID thus in TSQL I would say WHERE ID x.
Other tables are by date where I would want to do something like WHERE
CreateDate >= GetDate()-1
How would I go about performing such clauses using DTS?
to create a code. Hi everyone, I have SQL SERVER 2005 on my desktop and i need to run queries from remote machines on my SQL SERVER. I thought it would be a good i idea to create a web page on my machine to where remote users can contact and activate SQL QUERIES. My question is very simple and i wonder i a simple answer is available: How do i run SQL queries whereby ASP code ? How do i make a connection between a web page (.asp code) and a SQL sever ? If the answer is complicated as i'm afraid it is, could any one provide me a link for a tutorial dealing with the above issues ? Thanks a lot !
Hi,I'm enclosed a snippet of test code which highlights my problem. The Storedprocedure insertValue should insert text into the parent, then insert othertext into the child table but the 2 tables should auto increment in sync(i.e. so that they both end up with the same id numbers). I've tried takingthe auto increment out of the child table but then I don't know how to getthe right parent id into the child table.Any advice appreciated - this is my first database, so I'm just in thelearning process really. Code follows:CREATE TABLE Parent(id INTEGER DEFAULT AUTOINCREMENT,parenttext VARCHAR(16),PRIMARY KEY (id))!CREATE TABLE Child(childID INTEGER INTEGER DEFAULT AUTOINCREMENT,childtext VARCHAR(16),FOREIGN KEY (childid) REFERENCES Parent(id),PRIMARY KEY (childID))!CREATE PROCEDURE insertValues(in p VARCHAR(16), in c VARCHAR(16))BEGINinsert into parent (parenttext) values (p);insert into child (childtext) values (c);END!call insertValues('from parent', 'from child')!select * from parent, child where parent.id = child.childid!
I'm attempting to use a JOIN for the first time and I'm getting about 94,000 rows returned when I should only be getting about 270. Something must not be unique (possible DSN) but I can't figure out why the join is not working. I know it must be difficult to determine the problem without actually seeing the tables but could somebody with a lot of SQL experience spot any glaring problems with this query? Thanks!
SELECT DS.DSNName AS DSN, S.ServerName AS WebServer, DBs.DBName, A.ServerName AS SQLServer FROM DSNs DS, Servers S, DBs, DSNs LEFT OUTER JOIN Servers A ON DSNs.SQLServerID = S.ServerID WHERE DBs.Status = 1 AND DS.WebserverID = S.ServerID AND DBs.DBID = DS.DBID ORDER BY DSN
This has all the earmarks of a procedural error on my part. I have it occuring on two computers with the same sequence and exceptions and with clean databases. Note: The code is from Doug Lowe's ASP.NET 2.0 Everyday Apps For Dummies 2006 loaded from the CD. The database is generate from a script provided by the author. I will paste it in at the end.
This Q is so easy I can't find the answer anywhere! Why is SQL Server called 'Server' ? I understand it is a lot more capable and robust than Access but where does 'server' come into it. I currently use ASP and Access for dynamic websites but it is time to move up a notch. Do I just buy SQL Server, make a database, upload it to the same place as before and hey presto? can I run lots of websites (on different domains and servers) from databases created with my single license standard edition? Thanks M
I'm hoping you all can provide me with a little advice? I just landed my first DBA position with a company that runs different versions of SQL Server (7.0, 2000, 2005) on many different boxes. The databases I will be managing are in the 10-30TB ranges. Mostly the databases are for OLAP access by the company’s customers. I will need to bring myself up to speed on everything because there is no DBA at the company to transfer information.
Can anyone provide any advise on how I should approach this task of learning my position and/or useful links to resources? Thank you in advance!
I'm working on a website remotely through a VPN and my client uses SQLServer. There's a requirement for a database and my client uses SQLServer 2000.Is it possible to administer this database remotely, just like I dowith MySQL/PHP MyAdmin?Please elaborate.Thanks,Marjorie
I imagine a data-retrieval scenario where data destined for several different tables are collected and initially placed into one 'collector' table (say, ALLDATA); a stored procedure then kicks in and passes the records to individual destinations. For example, I might want to have data for two persons, Ann and Betty, come in and be routed to table_ann and table_betty respectively, with the correspondence stored in table LOOKUP:
id output_table Ann table_ann Betty table_betty
How could this be implemented? Perhaps with some kind of loop over the rows of LOOKUP, with table name retrieved and entered into a (EXEC-written?) query
insert into <table_name> from alldata where id in (select distinct id from lookup where output_table = <table_name>)
But how could one iterate over the list of table names? (These could be placed in table, of course, and the question become 'how could one iterate over a table's rows?')
I hope that this is an easy question for SQL Server pros, and will be grateful for a brief pointer/code snippet. (I may have given more background then necessary, but perhaps there are better solutions available there as well?)
Im not sure how to explain this or if this is the correct forum but here it goes.
I have created a report from a view that gives me court hearing dates on a defendent. When I run the report/view I get two different dates for the person such as, 12/14/2006 and 12/15/2006. How can I get it to show only the later date (12/15/2006)? I pull information from a mainframe that almost always gives me 2 different dates, but we only need the most recent date.
Here is my query for the view
SELECT dbo.CASES.CaseID, dbo.CASENUMBERS.CaseNumber AS Arrest_Number, dbo.CASENUMBERTYPES.CaseNumTypeCode AS Arrest_NumType, CASENUMBERS_1.CaseNumber AS JN_Number, CASENUMBERTYPES_1.CaseNumTypeCode AS JN_NumType, dbo.ROLETYPES.RoleTypeDesc, dbo.ACTORS.SIDNum, dbo.ACTORLOCATIONS.ActorLocDesc, dbo.CHARGENUMBER.ChargeDescription, dbo.DEFCHARGES.OffenseDate, dbo.fnNameReverse(dbo.ACTORNAMES.LastName, dbo.ACTORNAMES.FirstName, dbo.ACTORNAMES.MiddleName, dbo.ACTORNAMES.Suffix) AS NameRev, dbo.EVENTS.ev_StartDate AS PreHearingSetDate, dbo.OFFENSELEVELS.OffenseLevelDesc, dbo.CASES.UnitID FROM dbo.CASES INNER JOIN dbo.CASENUMBERS ON dbo.CASES.CaseID = dbo.CASENUMBERS.CaseID INNER JOIN dbo.CASENUMBERTYPES ON dbo.CASENUMBERS.CaseNumTypeID = dbo.CASENUMBERTYPES.CaseNumTypeID INNER JOIN dbo.CASENUMBERS AS CASENUMBERS_1 ON dbo.CASES.CaseID = CASENUMBERS_1.CaseID INNER JOIN dbo.CASENUMBERTYPES AS CASENUMBERTYPES_1 ON CASENUMBERS_1.CaseNumTypeID = CASENUMBERTYPES_1.CaseNumTypeID INNER JOIN dbo.CASEPARTIES ON dbo.CASES.CaseID = dbo.CASEPARTIES.CaseID INNER JOIN dbo.ROLETYPES ON dbo.CASEPARTIES.RoleTypeID = dbo.ROLETYPES.RoleTypeID AND dbo.CASEPARTIES.RoleTypeID = dbo.ROLETYPES.RoleTypeID INNER JOIN dbo.ACTORNAMES ON dbo.CASEPARTIES.ActorNameID = dbo.ACTORNAMES.ActorNameID INNER JOIN dbo.ACTORS ON dbo.ACTORNAMES.ActorID = dbo.ACTORS.ActorID INNER JOIN dbo.ACTORLOCATIONS ON dbo.ACTORS.ActorLocationID = dbo.ACTORLOCATIONS.ActorLocationID INNER JOIN dbo.DEFCHARGESUMMARY ON dbo.CASES.CaseID = dbo.DEFCHARGESUMMARY.CasesCaseID INNER JOIN dbo.DEFCHARGES ON dbo.DEFCHARGESUMMARY.DefChargeID = dbo.DEFCHARGES.DefChargeID INNER JOIN dbo.CHARGENUMBER ON dbo.DEFCHARGES.ChargeNumberID = dbo.CHARGENUMBER.ChargeNumberID INNER JOIN dbo.COURTS ON dbo.CASES.CaseCourtID = dbo.COURTS.CourtID INNER JOIN dbo.EVENTS ON dbo.CASES.CaseID = dbo.EVENTS.CaseID INNER JOIN dbo.EVENTTYPES ON dbo.EVENTS.EventTypeID = dbo.EVENTTYPES.EventTypeID INNER JOIN dbo.OFFENSELEVELS ON dbo.CHARGENUMBER.OffenseLevelID = dbo.OFFENSELEVELS.OffenseLevelID WHERE (dbo.CASENUMBERTYPES.CaseNumTypeCode = 'NM' OR dbo.CASENUMBERTYPES.CaseNumTypeCode = 'MC' OR dbo.CASENUMBERTYPES.CaseNumTypeCode = 'CM' OR dbo.CASENUMBERTYPES.CaseNumTypeCode = 'JP') AND (CASENUMBERTYPES_1.CaseNumTypeCode = 'JN') AND (dbo.ROLETYPES.RoleTypeDesc = 'Defendant') AND (dbo.EVENTTYPES.EventTypeDesc = 'PRE-HEARING SET') AND (dbo.DEFCHARGES.OffenseDate > CONVERT(DATETIME, '2006-01-01 00:00:00', 102))
In the 'PRE-HEARING SET' table is where I receive the two dates. Is there a way to filter out the first date? I just want to show the most recent date.
Hi everyone, This is a beginner's question: I know that in order to run a package i need to press ctrl_f5 but i also know that there should be an icon (dimmed green arrow) to do that but i cant find it on the toolbar! I only see the "debug" green arrow. Can anyone help me find the lost "play" (or "run") green arrow ? on which toolbar is it situated ? Thanks
I need copy one database from one server into another server. Both servers have SQLSERVER 2000. One way is copy the LDF files directly but I need to stop the engine to do that, I€™m sure there are many other ways more efficiently. I do need an advice. Thanks in advance
