Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MSSQL, Oracle, MySQL openquery... Expand / Collapse
Author
Message
Posted Friday, April 5, 2013 10:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 1:19 PM
Points: 5, Visits: 16
Hello,

My problem is that I do not know what to do from here. I am trying to find schedule conflicts. I used to have the data on a MySQL server and I was just using ASP.NET code to generate the conflicts list. Now one category of the data is coming from a different server, Oracle. I already own a MS SQL server that i am usually using for my apps.

What I did, is I created a stored procedure that uses a union all from 2 openquery select statements. That works and I can get all the data I need:

ALTER PROCEDURE [dbo].[getConflicts0]
@repsd varchar(50),
@reped varchar(50)
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_NULLS ON;
SET ANSI_WARNINGS ON;
declare @sqltext varchar(max)
select @sqltext='
SELECT * FROM OPENQUERY(MySQL)
UNION ALL
select * from openquery(ORACLE)
'
exec (@sqltext)
END

My next step would be to get this data in a table that has 2 more columns, to identify the conflicts and pair them.

I understand I cannot use this inside a function.

Would you guys have any suggestion on how to proceed with this?

Thank you
Post #1439362
Posted Friday, April 5, 2013 11:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:07 PM
Points: 12,910, Visits: 32,028
if you create a permanent or temp table that has the shape of the data, you can insert into it;


a rough example, but it works on my side, assuming the proper tables and databases and stuff:
 CREATE TABLE [dbo].[#tmp] ( 
[STATETBLKEY] INT NOT NULL,
[INDEXTBLKEY] INT NOT NULL,
[STATECODE] CHAR(2) NOT NULL,
[STATENAME] VARCHAR(50) NOT NULL,
[FIPS] CHAR(3) NULL)

declare @sqltext varchar(max)
select @sqltext='
SELECT * FROM OPENQUERY(My2005LinkedServer,''SELECT * FROM LHC.dbo.TBSTATE'')'
--insert into instead of just a select
INSERT INTO #tmp
exec (@sqltext)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1439387
Posted Friday, April 5, 2013 11:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 1:19 PM
Points: 5, Visits: 16
Thank you. It was exactly what i was trying right now. I have a question, how do I refer to the server I am on in the openquery? Is there such a thing as linked server local?
Post #1439390
Posted Friday, April 5, 2013 12:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:07 PM
Points: 12,910, Visits: 32,028
adinica (4/5/2013)
Thank you. It was exactly what i was trying right now. I have a question, how do I refer to the server I am on in the openquery? Is there such a thing as linked server local?


well it's possible, but i'm not sure why you'd want to do it.
if you are already connected to the sql server itself, there's no real need to create a "Loopback" linked server to itself, is there?
you can query data directly already.
I've tested this, and it does work, but i think we need to figure out WHY you think you want to use a linked server?
in this case, my developer machine is "DEV223", and here's my loopback linked server to itself:
/****** Object:  LinkedServer [MyLoopbackServer]    Script Date: 04/05/2013 13:57:06 ******/
EXEC master.dbo.sp_addlinkedserver
@server = N'MyLoopbackServer',
@provider=N'SQLNCLI',
@datasrc=N'DEV223',
@catalog=N'SandBox'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'MyLoopbackServer',
@useself=N'True',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1439397
Posted Friday, April 5, 2013 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 1:19 PM
Points: 5, Visits: 16
Oh, I see you were not suggesting I use the stored procedure but rather work in it further. I was thinking that the openquery in your example was using the stored procedure.

My idea was, ok, now I have the stored procedure, how do I use it in some other element.
Ok, thanks, I (think I) understand now. I will post later with my result or some other question. Thank you again.
Post #1439402
Posted Friday, April 5, 2013 12:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:07 PM
Points: 12,910, Visits: 32,028
i'm not clear ont he question, i may be reading it too fast.
if the question is "how do I execute a stored procedure via OPENQUERY,a dn capture the results, the answer is the same:
you need a pre-exisitng table that will capture the results of the openquery.
here's anotehr example, where i execute sp_WHO on a diffferent SQL server, is that close to what you are asking?
--drop table #MYRESULTS
CREATE TABLE #MYRESULTS (
[SPID] CHAR (5) NULL,
[STATUS] VARCHAR(30) NULL,
[LOGIN] VARCHAR(30) NULL,
[HOSTNAME] VARCHAR(30) NULL,
[BLKBY] VARCHAR(30) NULL,
[DBNAME] VARCHAR(30) NULL,
[COMMAND] VARCHAR(30) NULL,
[CPUTIME] INT NULL,
[DISKIO] INT NULL,
[LASTBATCH] VARCHAR(30) NULL,
[PROGRAMNAME] VARCHAR(200) NULL,
[SPIDINT] INT NULL,
[REQUESTID] INT NULL
)

declare @sqltext varchar(max)
select @sqltext='
SELECT * FROM OPENQUERY(My2005LinkedServer,''SET FMTONLY OFF; EXEC sp_WHO2'')'
INSERT INTO #MYRESULTS
exec (@sqltext)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1439415
Posted Friday, April 5, 2013 12:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 1:19 PM
Points: 5, Visits: 16
Hi.

No, you were plenty helpful. I misunderstood your answer. I had this stored procedure that has a union with 2 openquery in it. One openquery goes to a MySQL server and the other goes to an Oracle server. I am running it from a MSSQL server. When I first read your first response I understood that you wanted me to implement that code and do an openquery on the stored procedure that I had. Only, I was already on the MS SQL server, so I assumed you wanted me to somehow create another linked server to the server I was on (MS SQL). After you answered (#2), I understood what you answered in the first place.
Post #1439420
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse