Sql Server For Oracle Developer And DBA - References
Jan 28, 2007
Hi all,
Would anyone know of any references (online or books) that make it easier for experienced Oracle people to Learn SQL Server. The type of things I'd like to know for example are
The environment for SQL server on a PC (e.g. where the
datafiles are, what's the replacement for tnslistener - general architecture info)
Any significant differences that I'd need to know for creating tables and applications, like what do I use instead of varchar2 for example.
I have a customer that wants to start using SQL Server 7.0 instead of Oracle, but they have a lot of Oracle applications (made in Developer 2000 and Forms). Due to the big effort needed to convert those applications (to Visual Basic), they want to be able somehow to migrate only data from Oracle to SQL Server, and then use some tool that will allow them to use the same applications with the SQL Server 7.0 engine.
I found on the "SQL Server Developer's Kit" a document describing the process of migrating data and applications from Oracle to SQL Server. There is a mention to an Oracle tool called "Oracle Gateway to SQL Server" that will translate calls received by the Oracle engine to the SQL Server engine, but this approach will still require the existence of Oracle servers (which is the thing the customer no longer wants).
The second option mentioned on that document, and the one that seems more attractive, is the use of Oracle "Open Client Adapter" (OCA) that will allow Developer 2000 applications to communicate directly to SQL Server via ODBC. However, I have been unable to find (on the Oracle site) any documentation regarding this tool.
Has anynoe heard or used such tool (OCA) ? Any comments, tips, drawbacks, experience you could share ? Any other idea on how a thing like that could be accomplished ? Perhaps a third-party application that can act as a gateway between the Forms app and ODBC/SQL Server, or that will be able to generate VB code from a Developer 2000/Forms app...
Any help will be appreciated.
Please respond directly to me, as I am not a member of the distribution list.
How to launch Oracle SQL Developer through "Run"Â window in windows as we can launch SQL Server Management studio through "Run" command line using command line "sqlwb -S localhost -d MyDB -U sa -P
There are conflicting postings everywhere about this. I built a small site using VWD and SQL Server Express, but need to convert to Sql Server 2005 Developer because the DB will exceed 4Gb. Tried to install and could not connect or use from VWD. Tried to uninstall, got into large mess with the byzantine uninstall process, and have now started with a clean machine.
I installed SQL Server 2005 Developer first, then installed VWD Express 2008 without installing SQL Server Express. VWD won't let me connect to an existing database, won't let me add a new database and doesn't even show the Database Tools option under the Tools menu.
Is there someone with specific knowledge that can provide any enlightenment? Would be much appreciated.
I need to join 2 tables but the join needs to account for 2 seperate columns.
for example:
select a. type a. prod_code a. prod_type b. division
from table1 a left join table2 b on a. prod_code = b. prod_code and a. prod_type = b. prod_type
The issue is that you may have only the prod_code or prod_type and null value for the other in table1.
Ideally I want it to check for both then if 1 isn't available then it draws the division of the available. having both or one or the other determines the division it falls under.
Does anyone know of any good references (books or web sites) that provide examples of querying AD from SQL Server? I have the database link setup and have done two very simple queries against AD but I would like to see more in-depth examples.
This is my problem: I have a dynamic web reference for a SQL Server UDF coded in C#, but I am unsure of where URL for the webservice is being read from.
I am working with SQL Server 2005 to create a User Defined Function, however I need to have a web reference to the SQLReportingService2005 web service. I will be distributing this function to my customers so I need this web reference to be dynamic.
Although the webservice says it is dynamic, I do not see a app.config file to place the changing URL. Does anyone know where a dynamic web service pulls the URL from in this case?
Is there any way to enforce table references in stored procedures? For Example, we have stored procedures with a ton of different formats, "dbo.table", "table", "db.dbo.table", etc. Can we make it so that for every stored procedure, the reference must be at least "dbo.table"?
Can someone here confirm or deny that Visual Web Developer 2005 Express Edition doesn't work with SQL Server Developer Edition 2005?
I was about to order SQL Server Developer Edition 2005 on Amazon, to use with Visual Web Developer 2005 Express, when I read a user comment that the two are incompatible with each other. Does anyone know for sure if that is true?
What I'm wanting to do is develop an ASP.NET web application on a web host using a SQL Server database. I use VS2003 and SQL Server Enterprise 2000 at work, but I'd like to learn the 2005 editions for this side project. I don't own any of the developer tools/IDEs personally, so I'm trying to find the most affordable way to do this for my home business.
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
Microsoft SQL Server Management Studio 9.00.2047.00 Microsoft Analysis Services Client Tools 2005.090.2047.00 Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447) Microsoft MSXML 2.6 3.0 4.0 6.0 Microsoft Internet Explorer 6.0.3790.1830 Microsoft .NET Framework 2.0.50727.42 Operating System 5.2.3790
I have the OraOLEDB.Oracle provider installed to the (C:oraclexe) directory.
I am having problems querying from linked oracle server. When i setup oracle as a linked server and purposely enter an incorrect password the query i run tells me i have an incorrect password. So it at least knows that. when i set the correct password and run a query I get this error:
(i replaced the real server name with "someServer".)
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "SomeServer".
Hello all. I'm giving serious thought on changing gears withing the IT world. For several years I have been a software developer who has been fortunate enough to have been involved in the needed databases. From just sketching out tables for the DBA to doing the database from the ground up and maintaining it during production. Along the way I've known that I didn't do as quality of a job as a knowledgeable DBA would do but given the situations I made do just fine.
I am considering switching gears to the database side only, coding as secondary. Since I am basically familier with most aspects of SQL Server in a general way, I am looking to take that knowledge and become a "Professional". What I am looking for is a few books to help out. Maybe a "Become a DBA for Software Developers" or something. I know that mining these and other forums are very valuble but I'd like some other training materials as well.
Have I been clear enough on what I am looking for? I don't quite feel like I have.
Basically, what would you recommend for a good software developer who is looking on switching to becoming a good database person?
I'd like to stay in SQL Server for now, since that is what they use here and it would give me an opportunity to practice.
Also, do you see value in the studying process for certifications in this area?
I can't tell if the SQL Developer install upgraded the service, but left the SqlExpress version information, or if I'm still really only using SqlExpress.
Also, my intended target is SQL Enterprise, so if I am using SQL Express am I asking for trouble when I go to move the database over? Are the database formats compatible?
I've poked around trying to search for this answer for a while and haven't turned anything up. Maybe I'm searching on the wrong terms...
Normally, you establish referential integrity so that foreign key in one table points to a primary in another. Here is a composite key:
(A_ref, B_ref) => (A, B)
Consider a situation where A is a primary key in table T1. It is refered by T2, which has A,B as it's prmary key. An example of this situation would be a table of printers and table of batches a printer has printed at specific date. The batches are identified by Printer, Date, BatchNo within the date. Now, we create a temporary table T3, which addresses "today" batches. The "today" reference is taken from a date is taken from some record in the DB and, combined with the Printer ID and BatchNo, must point to a record in T2. Is it possible to specify such a complex relationship to ensure the referential integrity?
The advantages of the integirty are:
-- the referred records are pinned down from removal -- it is not possible to refer unexisting object; thus, the referee is ensured to refer an existing one.
Hi All - I have come across the need to use a cube in AS 2005 for an application. Unfortunately this will be the first time writting an ASP.Net application which uses one. I was wondering if any of you had any good books or references that I could look at to get an idea of how I should approach this issue. Thanks in advance.
Hi, I'm doing a web application that will get some information from an ERP. At this moment I have 2 databases: 1) The aspnetdb, where I have the tables for Merbership and Role 2) The ERP database I need to put my web application tables on one of these two DB's. This tables will reference the users from the membership and some products from the ERP DB. I will store products requests that will store both UserID (from aspnetdb) and ProductID (from ERP DB). I'm thinking to put these tables on the aspnetdb, so that all web application tables stick together. But, I will loose tha ability to make joins with the ERP database, right? Do you think this will work? Can someone make some comments about this situation, and give me some tips? Thank you!
I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.
SELECT FldA = CASE WHEN .... THEN CurQty * 1.5 WHEN .... THEN CurQty * 1.75 ELSE 0 END), FldB = CASE ..... NewValue = CASE WHEN .... THEN FldA * CurValue WHEN .... THEN FldB * CurValue etc.
HelloSuppose a database Db1 with tables tl1 and tl2 and a second database db2with tables tl3 et tl4.Is it possible to make a join between tables of the two databases ?As for example, Select * from tl1 INNER JOIN tl3 where tl1.Field1 =tl3.Field3Thank for any helpThierry
I have a task to where I need to move a column from one table toanother. I want to be sure I update any view, stored procedure,trigger, etc. that references the column. I simply want a query thatwill report the related objects and then I will update them manuallybut before I go and try and figure out how to do this by querying thesys tables is there an sp_sproc that will do this?
I have a data list that will grow over time. The values are listed vertically in a column; most recent value at the bottom. I am trying to figure out how to setup a formula to figure out the standard deviation on the most recent 30 values automatically. For instance if the column contains 30 values and I add the 31st value, I'd like to have to have the standard deviation displayed in a cell and automatically shift from calculating on values 1-30 to values 2-31. Is this possible?
We installed SQL7 over the weekend. Everything was working peachy through yesterday. This morning SQL server is requiring all of the queries to require references to the username in the from clause...
For example
"Select * from mytable" used to work fine. Not it requires "select * from username.mytable"
I'm logged in as the same username, which is the DB Owner as well. Any idea why this is happening all the sudden?
I'm trying to create some tables in my database but I'm getting some errors... The one which is causing the most trouble is Msg 1767, Level 16, State 0, Line 38 Foreign key 'ten_fk' references invalid table 'Tenant'. I'm not sure why it's complaining... can anyone help me out here?
Cheers!
-- Mitch Curtis -- A2create.sql
-- Set the active database to KWEA. USE KWEA;
-- Drop existing tables (if any). DROP TABLE Ownership; DROP TABLE Tenant; DROP TABLE Staff; DROP TABLE Property; DROP TABLE Property_Status_Report; DROP TABLE Property_Owner; DROP TABLE Placement_Record; DROP TABLE Candidate_Tenant; DROP TABLE Waiting_List;
-- Create new tables. CREATE TABLE Waiting_List ( waiting# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, candidate_name VARCHAR(20) NOT NULL, anticipated_start_date SMALLDATETIME NULL, anticipated_end_date SMALLDATETIME NULL, max_affordable_rent SMALLMONEY NOT NULL );
CREATE TABLE Candidate_Tenant ( candidate# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, waiting# INT NULL, name VARCHAR(20) NOT NULL, phone_number INT NOT NULL, required_property_type VARCHAR(10) NOT NULL, CONSTRAINT w_fk FOREIGN KEY(waiting#) REFERENCES Waiting_List(waiting#) );
CREATE TABLE Placement_Record ( opening# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, tenant# INT NOT NULL, start_date SMALLDATETIME NOT NULL, end_date SMALLDATETIME NOT NULL, total_bonds SMALLMONEY NOT NULL, weekly_rent SMALLMONEY NOT NULL, CONSTRAINT ten_fk FOREIGN KEY(tenant#) REFERENCES Tenant(tenant#) );
CREATE TABLE Property_Owner ( owner# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, name VARCHAR(20) NOT NULL, phone_number INT NOT NULL );
CREATE TABLE Property_Status_Report ( address VARCHAR(30) NOT NULL, report_date SMALLDATETIME NOT NULL, weekly_rent SMALLMONEY NOT NULL, month_rent_start_date SMALLDATETIME NOT NULL, month_rent_end_date SMALLDATETIME NOT NULL, maintenance_fee SMALLMONEY NOT NULL, month_inspection_history VARCHAR(30) NULL, CONSTRAINT ar_pk PRIMARY KEY(address, report_date), FOREIGN KEY(address) REFERENCES Property(address) );
CREATE TABLE Property ( address VARCHAR(30) PRIMARY KEY NOT NULL, staff# INT IDENTITY(1,1) NOT NULL, type VARCHAR NOT NULL, occupant_limit INT NOT NULL, comments VARCHAR(30) NULL, FOREIGN KEY(staff#) REFERENCES Staff(staff#) );
CREATE TABLE Staff ( staff# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, manager# INT NOT NULL, name VARCHAR(20) NOT NULL, FOREIGN KEY(manager#) REFERENCES Staff(staff#) );
CREATE TABLE Tenant ( tenant# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, staff# INT NOT NULL, property_address VARCHAR(30) NOT NULL, name VARCHAR(20) NOT NULL, phone_number INT NOT NULL, street VARCHAR(20) NOT NULL, city VARCHAR(20) NOT NULL, postcode INT NOT NULL, category VARCHAR(10) NOT NULL, comments VARCHAR(30) NULL, FOREIGN KEY(staff#) REFERENCES Staff(staff#), FOREIGN KEY(property_address) REFERENCES Property(address) );
CREATE TABLE Ownership ( address VARCHAR(30) NOT NULL, owner# INT NOT NULL, CONSTRAINT ao_pk PRIMARY KEY(address, owner#), FOREIGN KEY(address) REFERENCES Property(address), FOREIGN KEY(owner#) REFERENCES Property_Owner(owner#) );
-- Display tables. SELECT * FROM Waiting_List; SELECT * FROM Candidate_Tenant; SELECT * FROM Placement_Record; SELECT * FROM Property_Owner; SELECT * FROM Property_Status_Report; SELECT * FROM Property; SELECT * FROM Staff; SELECT * FROM Tenant; SELECT * FROM Ownership; Errors: Msg 3701, Level 11, State 5, Line 8 Cannot drop the table 'Ownership', because it does not exist or you do not have permission. Msg 3701, Level 11, State 5, Line 9 Cannot drop the table 'Tenant', because it does not exist or you do not have permission. Msg 3701, Level 11, State 5, Line 10 Cannot drop the table 'Staff', because it does not exist or you do not have permission. Msg 3701, Level 11, State 5, Line 11 Cannot drop the table 'Property', because it does not exist or you do not have permission. Msg 3701, Level 11, State 5, Line 12 Cannot drop the table 'Property_Status_Report', because it does not exist or you do not have permission. Msg 3701, Level 11, State 5, Line 13 Cannot drop the table 'Property_Owner', because it does not exist or you do not have permission. Msg 3701, Level 11, State 5, Line 14 Cannot drop the table 'Placement_Record', because it does not exist or you do not have permission. Msg 1767, Level 16, State 0, Line 38 Foreign key 'ten_fk' references invalid table 'Tenant'. Msg 1750, Level 16, State 0, Line 38 Could not create constraint. See previous errors.
How do you add a reference if the assembly you want to reference does not live in C:WindowsMicrosoft.NETFrameworkv2.0.50727? It doesn't seem to make sense that you cannot access the connections the AquireConnection() method serves since the interfaces are defined in Microsoft.SQLServer.DTSRuntimeWrap.dll (in C:Program FilesMicrosoft SQL Server90SDKAssemblies).
For example the code below works fine, in order to make it work I had to copy DTSRuntimeWrap.dll to v2.0.50727, surely any assembly which is been added to the GAC should be available as a referece?
Public Sub Main()
Dim cmgr As Microsoft.SqlServer.Dts.Runtime.ConnectionManager = Dts.Connections("FTP Connection Manager")
Dim cn As Object = cmgr.AcquireConnection(Nothing)
Hi everyone, When you've got -for example- a derived column task linked with a Flat file and then you change any field and come back to derived column task again you have select that field with two possibilities: 1-€˜Leave as invalid column reference€™ / 2-€˜Delete invalid column reference€™
It€™s easy when just one is affected but when you have eight or ten is very tedious do the same one by one. Any way for to do same but selecting more than one?
I'm pretty new with SSIS. I have written some custom component which will read from flat file and write it into database. Between those processes, there are other processes which will analyse and transform the data being transfered. Everytime, I remove a column from upstream component, I have to go through each component to fix up column reference mapping using Invalid Column References Editor. There are lots of clicking involves just to remove a column from upstream component. My package has about 15 components. I am just wondering, is there anyway for us to prevent that editor to pop up, instead fixing invalid column reference programatically in ReInitializeMetadata?
I have a database of 900+ tables with around 3000 SPs, and views. Manually I reviewed few tables and found that tables are not referenced with FK and I applied few. There are lots of tables and SPs using them in join statement, Is there any way with which I can get each tables missing references, any DMV or other manual script which tells about this?
I know this has to be possible maybe I am just missing somthing.
I am creating a matrix report which will compare year by year quotes to orders The issue is quotes and orders each have their own dataset. I will be pivoting on JobType which is in both datasets and spelled the same. Is there a way to do this or will I have to figure out how to union the tables? If not possible why does it allow you to name the dataset in the expression?
I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
