|
I don't have any experience working with linked servers, but this this page[^] seems to compare the issues you are contemplating pretty well.
Jack of all trades ~ Master of none.
|
|
|
|
|
Thank you very much for your assistance. The link contained much useful information, but to no avail. I'll be posting a follow up shortly.
"I need build Skynet. Plz send code"
|
|
|
|
|
Su-weet! Hurdle jumped. Below is a mock-up of the code that works for me
Manifest:
1) Table-Valued function created in Reporting that will encapsulate "campaign operation" specific querying logic.
2) Row inserted into CampaignOperations table with a pointer to (1)
3) Broker/Adapter procedure created that accepts a CampaignName and OperationCode
...found out along the way that an INSERT-EXEC stored proc cannot call an INSERT-EXEC stored proc and that you cannot execute remote table-valued functions.
Note that [RemoteSchema] is a schema located in a remote database called "Reporting." [LocalSchema] is the local schema that my application has access to. "Reporting" is a linked server of my local database.
1)
--------------------
USE [REPORTING]
GO
10:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [RemoteSchema].[TakeAllTheMoneyAndWomens]()
RETURNS @Solicitations TABLE
(
AccountId varchar(20) PRIMARY KEY NOT NULL,
AccountNumber varchar(20) NOT NULL,
HomePhone varchar(12) NULL,
BusinessPhone varchar(12) NULL,
FirstName varchar(70) NULL,
LastName varchar(70) NULL,
AddressStuffs varchar(max) NULL
)
AS
BEGIN
INSERT @Solicitations
SELECT '5','666321234','111-456-7890', '800-123-4567','Captain','Kirk','123 Menagerie Lane';
RETURN;
END;
GO
2)
------------------------------
insert into LocalSchema.CampaignOperations values('StarTrek','ShootToKill','REPORTING.RemoteSchema.TakeAllTheMoneyAndWomens()')
3)
------------------------------
USE [AppData]
GO
if(EXISTS(SELECT * FROM Information_Schema.ROUTINES where ROUTINE_SCHEMA = 'LocalSchema' and ROUTINE_TYPE = 'PROCEDURE' and ROUTINE_NAME = 'ExecuteCampaignOperation'))
drop PROCEDURE [LocalSchema].[ExecuteCampaignOperation]
10:11:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [LocalSchema].[ExecuteCampaignOperation]
(
@CampaignName varchar(50),
@OperationCode varchar(10)
)
AS
BEGIN
DECLARE @PROCEDURE nvarchar(100);
DECLARE @SQL nvarchar(max);
SELECT @PROCEDURE = Operations.[Algorithm]
FROM [LocalSchema].[CampaignOperations] [Operations]
WHERE CampaignName = @CampaignName
and OperationCode = @OperationCode;
SET @SQL = N'SELECT AccountNumber, AccountId, HomePhone, BusinessPhone, FirstName, LastName, AddressStuffs
FROM OpenQuery(REPORTING, ''SELECT * FROM ' + @PROCEDURE + ''')';
EXECUTE sp_executesql @SQL;
RETURN;
END
GO
EXEC LocalSchema.ExecuteCampaignOperation 'StarTrek', 'ShootToKill'
AccountNumber|AccountId|HomePhone|BusinessPhone|FirstName|LastName|AddressStuffs
1234567890 4 800-123-4567 800-231-4325 Captain Kirk 123 Menagerie Lane.
Yaaaaaay!
"I need build Skynet. Plz send code"
modified formatting on Friday, July 1, 2011 2:59 PM
|
|
|
|
|
Congratulations! 
Jack of all trades ~ Master of none.
|
|
|
|
|
hi all
i want to use some thing like that
set @a=@AnimalName == "Elephant"? "savannah": "unknown"
but it has an error in ==
how can i fix it?
besides i want to use conditional ?: operation to concat some strings like bellow how i should so;ve this problem?
N'insert into TBL Values(@Val1,'+ @Condition = 1? N'val2':N'val3')+',...'
|
|
|
|
|
Conditionals in Transact-SQL are expressed using the CASE statement[^]. Examples in the documentation should help you construct what you need.
|
|
|
|
|
dear friend
1- thanks to your reply it works for part of my storedprocedure
2- i heard that CASE is not high performance statement
3- what should i do if i want to shorten this block of code?
if(@flag=1) then
select * from TBL1
ELSE
select * from TBL2
i think this is not applicable to using CASE
am i right?
|
|
|
|
|
1. No problem
2. This does not make any more sense than "for loops are not high performance statements".
3. Assuming TBL1 and TBL2 have identical structure, you can do it like this:
select * from TBL1 where @flag=1
union all
select * from TBL2 where @flag<>1
<pre>
|
|
|
|
|
|
Hi all,
I'm using POCO[^] ODBC libraries to access SQL Server 2005, and have hit a problem.
If I enter a plain text statement:
session << "INSERT INTO TableName VALUES(44)", now;
it works fine. If I use a parameterised query:
int size=44;
session << "INSERT INTO TableName VALUES(:size)", use(size), now;
it throws an exception. The table contains a single int column.
Any suggestions gratefully received!
|
|
|
|
|
viaducting wrote: session << "INSERT INTO TableName VALUES(:size)", use(size), now;
Read up on parameterised queries, I don't recognise this syntax, it look like you are trying to insert the text "size" into the int field.
Sorry, just realised the POCO thingy may support that syntax. I suggest you chase this down using their support, this is not a problem with sql server but the POCO library.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That was it - it supports the ? syntax but fell over on the :name syntax, but only the :name syntax is in their documentation. 
|
|
|
|
|
Could you give more details on the exception that you are getting? Is it coming from POCO or from the DB?
The code appears lifted straight from the POCO example book - the only difference is that you use an int, while they use a string. Assuming that their example works, and that you also use SQLite, there's only a small number of places where you could get an error. First thing I would try is specifying the list of column names: INSERT INTO TableName (myIntColumn) VALUES(:size): the single-column syntax looks suspicious. Then I'd look at the error coming back, and try to decipher it.
|
|
|
|
|
I want to create the existing sql2000 DTS in sql 2008, i installed sql2000 backward compatibility. i can open the DTS in sql2008 and save as the location sql server. It is saving in sql2008 and when i open the dts in the popup window asking to select the version, but i can't see any version in the popup window.how can i sort this issue.
|
|
|
|
|
DTS is sort of gone in 2005 and up. Look at SSIS.
|
|
|
|
|
Rewrite the bloody thing, you are relying on a conversion utility to work perfectly, they almost never do, especially with anything that is even mildly complex.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
So I'm trying to get rid of the DAO classes and references in my MFC app, and there seem to be some good drop in replacement classes here.
But does anyone know of an easy way to re-do "DoFieldExchange" in ADO?
|
|
|
|
|
|
let suppose i have table like this:
id name key
1 sam sam1
2 joy joy2
name and key are inserted by user. Now whenever i am inserting new value, I need to check whelther I have that key exists or not. If yes, do nothing and if no, insert it. I know how to do it in dot net page but can I do this in sql query itself ? If yes, can anyone please help me..
suchita
modified on Wednesday, June 29, 2011 9:32 AM
|
|
|
|
|
Yes. You could try:
IF NOT EXISTS (SELECT DISTINCT keyname FROM <table_name>)
BEGIN
INSERT INTO.....
END
As an aside: this may not be the case because I can only see two rows of sample data, but if the [keyname] value is a concatenation of [name] + [id] and [id] is an identity column, then you should always have a unique value in [keyname].
Hope that helps.
|
|
|
|
|
If uncrush is right then one has to ask why you are creating the KeyName field, it is redundant.
When I have to check for existing keys, I return the record instead of using exists, then test if I got a record, that way I have all the information about the record. I always put this type of work into a stored procedure that returns the record, the UI may not even know if the records was inserted ot not.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Not knowing what database you are using, but you could also put a UNIQUE CONSTRAINT on the keyname column. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
I dislike using these, they tempt the developer into programming by error, try and add the dupe and trap the error. Whereas checking first makes the constraint redundant an does not generate an error.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I disagree, on the same terms that I do not check whether a file is readonly/accesible/existing when I delete it.
Bastard Programmer from Hell 
|
|
|
|
|
Eddy Vluggen wrote: on the same terms that I do not check whether a file is
readonly/accesible/existing when I delete it
If I am coding file I/O I certainly do!
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
