|
Paul Selormey wrote:
any vendor specific way
For SQL Server, you can use the sp_rename stored proc.
-- Rename the customers table to custs.
EXEC sp_rename 'customers', 'custs'
Without nipples, breasts would be pointless.
|
|
|
|
|
Wow, that is cool. Thanks.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
Dear CPians,
I am developing an ASP.NET app which has a lot of database work. The app has to support both SQL Server and Access and one of the requirements is be able to switch from SQL Server to Access by setting a parameter in a XML file(the XML file is read when the app starts).
I need to use the SQLClient classes. Will I be able to use the Access DB as well through the SQLClient classes?
If yes are there any drawbacks?
If no, how can I make my app capable of accessing both DBs?
What if in the future I need to support Oracle or/and DB2 as well?
Any help anyone?
Thank you!
|---------------|
| theJazzyBrain |
|---------------|
|
|
|
|
|
The best way to handle this kind of issue in the ADO.NET is to work with the interfaces, not the classes directly.
Provide a common point to create your connection object and return, IDbConnection interface. You can you this to access the data in a provider-independent way.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
And you do I work with the interfaces? Any sample code?
If I do that, will I still have the advantages that SQLClient provides when using SQL Server. Because, the app will initially be aimed for SQL server 2000, but we are planning for a Light version which works with Access in order to cut down costs for customers.
Do you think we should try and make a component to handle all the provider-independed access?
Thanx!
Jason
|---------------|
| theJazzyBrain |
|---------------|
|
|
|
|
|
theJazzyBrain wrote:
And you do I work with the interfaces? Any sample code?
IDbConnection connection = new OleDbConnection(str);
IDbConnection connection = new SqlConnection(str);
Should work for you. You can later do something like
IDbCommand atrCommand = connection.CreateCommand();
theJazzyBrain wrote:
If I do that, will I still have the advantages that SQLClient provides when using SQL Server. Because, the app will initially be aimed for SQL server 2000, but we are planning for a Light version which works with Access in order to cut down costs for customers.
If you wish to do SQL Server or for that matter any provider specific stuff, store the connection type somehow so that you can cast the pointer to the appropriate class later.
private IDbConnection conn = null;
private Type connType = null;
SqlConnection connection = new SqlConnection(str);
conn = (IDbConnection)connection;
connType = connection.GetType();
then later, you can do something like
if (connType == typeof(SqlConnection))
SqlConnection sqlConn = (SqlConnection)conn;
else if (...)
{}
theJazzyBrain wrote:
Do you think we should try and make a component to handle all the provider-independed access?
It depends on your needs. The central point here is the connection object, so provide a means of obtaining the interface where you wish to work in provider-independent manner.
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|
I understand now!
This is very helpfull!
Thank you very much Paul!
Jason
|---------------|
| theJazzyBrain |
|---------------|
|
|
|
|
|
Hello all. I have a stored procedure that updates a table based on it's own contents. The table contains web site hits and I have a C# function that later on resolves the IP addresses in the table to domain names and updates them in the table.
However before that step, the stored procedure in question sees if there are already matching IP addresses from past hits that had their domains resolved and then updates them accordingly to save time on the nslookup part of it.
Problem is that the stored procedure is getting very slow when all that has changed is the number of rows in the table has increased quite a bit, however the daily number of null domains hasn't really changed at all.
I'm wondering if anyone sees anything inherently wacky or wrong with this stored procedure:
ALTER PROCEDURE dbo.zspDomainSelfUpdate<br />
AS<br />
UPDATE dbo.hits SET dbo.hits.domain = hits_1.domain<br />
FROM dbo.hits INNER JOIN<br />
dbo.hits hits_1 ON dbo.hits.ip = hits_1.ip<br />
WHERE (dbo.hits.domain IS NULL) AND (hits_1.domain IS NOT NULL)
(there is an index on the ip field)
|
|
|
|
|
Hi J,
the problem I see is the WHERE-condition, you will (almost certain) get 2 full-table scans in resolving that. I would try this:
UPDATE dbo.hits SET dbo.hits.domain =
(SELECT dbo.hits.domain FROM dbo.hits hits1
WHERE dbo.hits.ip = hits1.ip
AND dbo.domain IS NOT NULL)
WHERE dbo.hits.domain IS NULL
I guess this leads to a somewhat shorter access-plan.
good luck,
dirk
|
|
|
|
|
Thank you! I'll give that a shot, it makes sense.
|
|
|
|
|
I was reading this article:
http://www.w3schools.com/sql/sql_join.asp[^]
And it shows the following for regular joins:
<br />
SELECT Employees.Name, Orders.Product<br />
FROM Employees, Orders<br />
WHERE Employees.Employee_ID=Orders.Employee_ID<br />
and then the following for INNER JOINS:
<br />
SELECT Employees.Name, Orders.Product<br />
FROM Employees<br />
INNER JOIN Orders<br />
ON Employees.Employee_ID=Orders.Employee_ID<br />
They seem to always return the same results. Is there a difference between the two types of queries above? Or are they the same? Is there any performance reasons or any other reasons when I should favor one type of query above the other?
Thanks!
|
|
|
|
|
I've wondered myself and the best info I could find implies that using an explicit JOIN rather than an implied JOIN helps out the optimization of the query for some types of database servers.
Personally, I think it's just a good habit to stick to explicit JOINS.
|
|
|
|
|
They will both produce the same result, and in most cases they will both optimize to the same execution plan.
Try them Query Analyzer (SQL Server) sometime, and choose to show the execution plan.
Without nipples, breasts would be pointless.
|
|
|
|
|
From SQL Books Online:
Specifying Joins in FROM or WHERE Clauses
The rows selected by a query are filtered first by the FROM clause join conditions, then the WHERE clause search conditions, and then the HAVING clause search conditions. Inner joins can be specified in either the FROM or WHERE clause without affecting the final result.
Outer join conditions, however, may interact differently with the WHERE clause search conditions, depending on whether the join conditions are in the FROM or WHERE clause. Therefore, the ability to specify Transact-SQL outer joins in the WHERE clause is not recommended, is no longer documented, and will be dropped in a future release.
For example, these queries both specify a left outer join to SELECT 23 rows that display the title identification number, title name, and the number of books sold:
-- Join in WHERE clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t, sales AS s
WHERE t.title_id *= s.title_id
-- Join in FROM clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
In this query, a search condition is also specified in the WHERE clause:
-- Join and search condition in WHERE clause.
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t, sales AS s
WHERE t.title_id *= s.title_id
AND s.stor_id = '7066'
The condition stor_id = '7066' is evaluated along with the join. The join only selects the rows for stor_id 7066 from the sales table, but because it is an outer join null values are supplied as the store information in all the other rows. This query returns 18 rows.
The join condition can be moved to the FROM clause, and the stor_id condition left in the WHERE clause:
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
WHERE s.stor_id = '7066'
This query returns only two rows because the restriction of stor_id = '7066' is applied after the left outer join has been performed. This eliminates all the rows from the outer join that have NULL for their stor_id. To return the same information with the join condition in the FROM clause, specify the stor_id = '7066' condition as part of the ON join_criteria section in the FROM clause and remove the WHERE clause:
USE pubs
SELECT t.title_id, t.title, s.qty
FROM titles AS t LEFT OUTER JOIN sales AS s
ON t.title_id = s.title_id
AND s.stor_id = '7066'
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Does anybody know how to start/stop MSDE from code (c#) in a machine running Windows 98/ME?
In NT, I use System.ServiceProcess.ServiceController.
With ME, I've tried with SQLDMO.SQLServer2Class (which I'm also using to attach a DB, without problems), using the Start method, but it doesn't seem to work.
Thanks!
|
|
|
|
|
I am doing a data conversion from our old DB design to a new one using a stored procedure. In order to avoid changing the name of the source DB in the sproc (we get different db names with identical schemas from our customers--don't ask me why), I would like to pass in the name of the source DB. Can this be done? I've tried to pass in like this:
CREATE PROCEDURE convert_Customer
@DbName [varchar](255)
AS
DECLARE C_OLD_Customer_Data CURSOR
LOCAL FORWARD_ONLY STATIC
FOR
SELECT @DbName.dbo.Customer.CustomerID,
@DbName.dbo.Customer.LastCompanyName,
As I figured it would, it fails miserably. I think it's more and interpolation problem than anything else. Anyone know if and how this can be done?
Thanks.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Friends, consider the following query:
select id, name, nick from blahtable where salary=2000
As you can see above i selected three fields from a table. Assume that the query returns only one record.
Now i want the values of three fields returned, to be assigned in variables and then i want to print the values of these variables. How can i do so.
|
|
|
|
|
Using which language(s) ?
|
|
|
|
|
|
Well, if you are doing this purely in SQL Server, then you can go into Query Analyser, run the SELECT query, and print the grid from there.
If you want to start assigning return values to variables and formatting printout (and perhaps changing the SELECT criteria), then you have to look at doing this from a client-side tool, such as VB.NET, or Crystal Reports.
Alternatively, you may be able to write a SQL Stored Procedure which takes criteria, and outputs the 3 fields you need (this gets complicated if you want to send the three fields directly to a printer from a stored procedure)
Is this part of a user application you're developing, or is it a once-off ad-hoc type query you're running?
John.
www.silveronion.com[^]
|
|
|
|
|
I believe this may be the solution
--Declare the Necessary Variables
DECLARE @ID AS int,
@Name AS nvarChar(15),
@Nick AS nvarChar(15)
--Incorporate those Variables
--Into the Select Statement
SELECT @ID = id,
@Name = name,
@Nick = nick
FROM blahtable
WHERE salary = 2000
PRINT @ID
PRINT @Name
PRINT @Nick
Buy the Book 'Advanced Transact-SQL for SQL Server 2000' by Itzak Bengan and Tom Moreau, If you plan on working with SQL, it will change your life
...just kidding but its a powerfull book
Gregory J Lynch
Hack
|
|
|
|
|
try this
declare @id int
declare @name varchar(30)
declare @nick varchar(30)
select @id = id, @name = name, @nick = nick
from blahtable where salary=2000
print @id
print @name
print @nick
Si 
|
|
|
|
|
I need to teach some 'users' a few basics of SQL. They are picking up most of the concepts fairly easily. That was until we came to JOINS. Because of the structure of the database, they'll often have to do some LEFT JOINS in their queries.
Does anybody know of a book / website that explains joins in a simple easy to understand way. (Pictures would be nice)
Michael
'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879
|
|
|
|
|
|
Hi!
:-OHow can I select from
InvId Company
------------------
1 a
23 b
56 c
12 a
21 c
this data set:
Company Invoices
-----------------------
a 1,12
b 23
c 56,21
Many thanks in advance!
_____________
stefan bornuz
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
