|
- Using the SQL Server Configuration Manager, create an alias[^] pointing to the correct Server2 for the current environment. The alias name should be the same in all environments.
- In SQL Server Management Studio, create a linked server[^] pointing to the alias you just set up.
- In your stored procedure, load the data from the linked server.
With this approach, your code doesn't need to change. It will always be querying a linked server with a fixed name, which is pointing to an alias with a fixed name. The only thing that changes between environments is where the alias is pointing.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi,
I have created the Alias for the server, then I looked for the linked server, it was already created in my Management Studio. But when I am trying to query using the Alias name, I am getting error message saying that the Server doesnt exist.
Can you please help me how to query using Alias name in SQL Server 2008 R2.
I am doing it in the following way.
select distinct xxid FROM CedarsLinkServer.CEDARS.dbo.SomeTestDemographic where recEndDate is null
I am getting the following error.
Msg 7202, Level 11, State 2, Line 1
Could not find server 'CedarsLinkServer' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Please help me in resolving it.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
The linked server needs to point to the alias, and your query needs to reference the linked server by name. Judging by the error message, you don't have a linked server called CedarsLinkServer set up.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Ok I got you where I am missing. I will try with that, if I get any problems I will send you a message. Thanks for the help Richard.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
As a prezzie here's a stored procedure to connect to and disconnect from a linked server:
CREATE PROC dbo.LinkedServerConnect_SP
(@connect as int)
AS
BEGIN
if @connect = 1
begin
if not exists(select name from sys.servers where name = 'yadayada')
begin
EXEC sp_addlinkedserver 'yadayada', 'MySQL', 'MSDASQL', Null, Null, 'Driver={MySQL ODBC 3.51 Driver};DB=yadayada;SERVER=mysql.yadayada.com;option=512;uid=yadayada;pwd=yadayada'
end
end
if @connect = 0
begin
if exists(select name from sys.servers where name = 'yadayada')
begin
EXEC sys.sp_dropserver @server = 'yadayada', @droplogins = 'droplogins';
end
end
end
GO
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
Hi,
This is my first attempt creating a SSIS package.
I am trying to imnport an excel spreadsheet into sql server table.
I am running BIDS, sql server 2008.
I specify an excel spreadsheet which is 2010 saved format, meaning a xlsx format. It keeps coming back with missing provider microsoft.ace.oledb.12.0
I have a 64 bit machine laptop from which I am creating this package. Do I still need to install the above?
Thanks!!
|
|
|
|
|
|
|
Chaps, I can't seem to find a definitive answer so maybe you can advise. Here's the scenario.
Let's say I have an update trigger on a table. If I have statement like (rough syntax) update mytable set x = 0 where somecolumn > 293 and it updates, say, 300 records. Would an update trigger occur on every row updated or just once for the batch of updates, as it were?
A long time back something I read/misread gave me the impression that some sql statements call the trigger for each row affected and another only does it once but I'm not sure what's fact and what's fiction.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
The trigger will file once for each statement. You should always write your triggers with the assumption that the inserted and deleted virtual tables will contain multiple rows.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard, thanks for that. I need to schedule some time to look into them. I know they're basically simple concepts but it's in the doing that you understand them.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
Triggers spit are a support nightmare, especially for a new developer. If you break a trigger (changing/removing a field) it can be extremely difficult for support to identify the problem (it can be bloody difficult for the silly bastard who wrote the trigger).
We do use the horrible things but only for audit purposes NEVER to implement business logic, that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: that lives in a stored proc that does what it is told when it is told and everyone knows where to find it.
I wish our database at work was so obliging and obedient.
In a previous job I remember problems with a trigger when looking for differences in the before and after images. I never worked on a more convuluted POS in my life. It was the kind of trigger that had an unwritten rota as to which mug of the month got as a prize.
If there is one thing more dangerous than getting between a bear and her cubs it's getting between my wife and her chocolate.
|
|
|
|
|
I dictate the standards on our DBs (mostly) so triggers and underscores struggle to survive and are hunted down and eliminated whenever I get really bored. This does tend to piss off the offending Dev who ignored the conventions.
So today they ganged up on me and we will move to MVC instead of WPF for next years crop of apps- bastards!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Richard Deeming wrote: inserted
Richard Deeming wrote: deleted
Brings back old memories. A stupid bugger rejected me in an interview when I could't answer the question "what are magic tables?". I knew these tables and how to use them, it's just that he expected me to know that they're called magic tables (even after a decade, I haven't heard anyone else calling them by that name).
|
|
|
|
|
Well, by the highly scientific method of Google-fighting:
your interviewer was an idiot.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Without going into gruesome detail, I would like to get some ideas on how I might approach this problem.
Given that I have a classic master-detail relationship in my database, lets call the tables Master and Detail. There is a master ID, and a detail ID for every row and every detail record has a reference to the master ID which it belongs.
If master record A has 3 detail lines (Part 1, Part 2, Part 3) and master record B also has those same detail records (Part 1, Part 2, Part 3) then I want to indicate that master record A and B are identical.
Can anyone suggest a method to do this ?
My initial idea is to use 2 SQL cursors where I would be looping through the data with Cursor1 from top to bottom, then use Cursor2 to scan the same data for each occurence of Cursor1.
Jeeze, sounds kind of lame.
There must be a better way.
Any suggestions would be greatly appreciated.
|
|
|
|
|
Something along these lines might get you started:
SELECT
A.ID As OriginalID,
B.ID As DuplicateID
FROM
MasterTable As A
CROSS JOIN
MasterTable As B
WHERE
A.ID < B.ID
And
Not Exists
(
SELECT
(relevant columns)
FROM
DetailTable
WHERE
MasterID = A.ID
EXCEPT
SELECT
(relevant columns)
FROM
DetailTable
WHERE
MasterID = B.ID
)
And
Not Exists
(
SELECT
(relevant columns)
FROM
DetailTable
WHERE
MasterID = B.ID
EXCEPT
SELECT
(relevant columns)
FROM
DetailTable
WHERE
MasterID = A.ID
)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Wow, that looks very interesting.
I will see if I can get it to work.
|
|
|
|
|
Normal way to acknowledge thanks is to up vote the answer - a message is appreciated as well.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
An up-vote before you know whether the answer works might be a bit premature.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
You can use the GROUP BY clause on the Detail table with Part1, 2 and 3 in the group clause and take the distinct DetailID and MasterID from the table. But Group By requires an aggregate function and I'm not sure how to take distinct values using it. If you are sure you need only 2 matching rows, you can use MIN and MAX functions of course.
|
|
|
|
|
You could use something like the except or minus (depending on your flavour of sql) clause to find differences between queries for rows referencing the same master ID.
This would return differences, those rows not returned would be equal.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
hi
I can i use "rank() over" OR "row_number" in sqlite ?
Thanks in advanced!
|
|
|
|
|
Did it even occur to you to type that into google[^], it seems to have been answered many time in great detail and 1 looks like it has a work around!
Never underestimate the power of human stupidity
RAH
|
|
|
|