Well, then everyone who used the linked server will be
using the new account created. I want people to only use
what thier domain account has permissions to do.
Any way to do this?
>--Original Message--
>You'll need to create a domain account that has login
>permissions to SQL Server "A" and the appropriate
database
>permissions as well. Use this account for the user login
>credentials when you create the link.
>Steve
>.
>You can use delegation to accomplish this. You can find
information on this in books online under the topic:
Security Account Delegation
-Sue
On Mon, 1 Mar 2004 06:57:34 -0800, "Linda"
<anonymous@.discussions.microsoft.com> wrote:
>Well, then everyone who used the linked server will be
>using the new account created. I want people to only use
>what thier domain account has permissions to do.
>Any way to do this?
>database
Showing posts with label windows. Show all posts
Showing posts with label windows. Show all posts
Friday, March 30, 2012
Link to Oracle Server SLOOOOW
Hi All,
I am having some problems getting a link to an Oracle server working properly. Here is my environment:
MS SQL Server 7.0 on Windows 2000
Oracle 8.1.7 on HPUX
MDAC 2.5
The link is working. I can query data and update date but it is incredibly slow.
Anyone run into this before?
Thanks...That's pretty vague...
What are you doing?
SELECT * FROM myMillionRowTable a FULL JOIN myMillionRowTable b
ON a.NonkeyedColumn = b.NonkeyedColumn
???|||I have a stored procedure that uses a cursor that queries data on the SQL Server and on average does about 300 inserts into the Oracle server. Takes 18 minutes. I modified the procedure to insert into a local table on SQL Server with the exact same format and it took less than 5 seconds. I would expect it to be somewhat slower going across a link, but not that much slower.|||Post the Cursor...
I bet a set based solution will fly...
Are you joining tables across servers?
Remeber the network gets involved...
Let's see what you're doing...
The "Miracle" of linked servers has limits, regadless what MS says...|||The link is not in any involved with the cursor. I only use the cursor to gather data to insert into a table across the link. The cursor query executes quickly (few seconds).
Here is the procedure I am using:
Thanks...
CREATE PROC lp_Send_ASN_Data_To_Oracle
as
DECLARE
@.ASN_NoticeID INT,
@.ASN_SHPMNT_ID VARCHAR(15),
@.ASN_DTE_SHPD VARCHAR(6),
@.ASN_TME_SHPD VARCHAR(4),
@.ASN_GRSS_WGHT DECIMAL(9,1),
@.ASN_BOL_NBR VARCHAR(15),
@.ASN_PO_NBR VARCHAR(8),
@.ASN_SKU_CODE VARCHAR(16),
@.ASN_PCKG_LP_CDE VARCHAR(9),
@.ASN_SBPCK_LP_CDE VARCHAR(9),
@.ASN_DYELOT VARCHAR(10),
@.ASN_QTY DECIMAL(9,2),
@.ASN_OVRL_WDTH DECIMAL(18,2),
@.NEXT_SQNC_NBR_LDED INT
DECLARE ASN_CURSOR SCROLL CURSOR FOR
select
SEDI.NoticeID,
SHPMNT_ID = SBOL.BillOfLadingID,
DTE_SHPD = substring(convert(varchar,datepart(yy,SBOL.ActualS hipDate)),3,2) +
case
when datepart(mm,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mm,SBOL.ActualShipDate))
else convert(varchar,datepart(mm,SBOL.ActualShipDate))
end +
case
when datepart(dd,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(dd,SBOL.ActualShipDate))
else convert(varchar,datepart(dd,SBOL.ActualShipDate))
end,
TME_SHPD = case
when datepart(hh,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(hh,SBOL.ActualShipDate))
else convert(varchar,datepart(hh,SBOL.ActualShipDate))
end +
case
when datepart(mi,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mi,SBOL.ActualShipDate))
else convert(varchar,datepart(mi,SBOL.ActualShipDate))
end,
GRSS_WGHT = IM.GrossWgt,
-- GRSS_WGHT_UOM = 'LB',
BOL_NBR = SBOL.BillOfLadingID,
-- MTHD_OF_PYMNT = 'PP',
-- VNDR_UCC_NBR = '662082',
PO_NBR = substring(SPSH.CustomerPO,1,8),
SKU_CODE = OLD.CustomerStyle,
PCKG_LP_CDE = (select TalcNo from Inventory_Items II2 where (II2.CartonID = II.CartonID) and (LeftIndex = 1)),
SBPCK_LP_CDE = II.TalcNo,
DYE_LOT = OD.OldDyeOrder,
QTY = IID.NetYards,
-- QTY_UOM = 'YD',
OVRL_WDTH = INSM.InspectedWidth
-- OVRL_WDTH_UOM = 'IN'
from
Shipping_EDINotice SEDI,
Shipping_BillofLadings SBOL,
Shipping_PackingSlips_Header SPSH,
Inventory_Master IM,
Inventory_Items II,
Inventory_Items_Detail IID,
Orders_LibOrders_Detail OLD,
Inspections_Master INSM,
Inventory_Webs_Header IWH,
Orders_DyeOrders OD
where
(SEDI.BillofLadingID = SBOL.BillOfLadingID) and
(SBOL.BillofLadingID = SPSH.BillofLadingID) and
(SPSH.PackingSlipID = IM.PackingSlipID) and
(IM.CartonID = II.CartonID) and
((II.RightIndex - II.LeftIndex) = 1) and
(II.ItemID = IID.ItemID) and
(II.ItemID = INSM.ItemID) and
(IM.SoldToLibID = OLD.LibOrderID) and
(II.WebID = IWH.WebID) and
(SEDI.EDIStatus = 'CR') and
(IWH.DyeOrderID = OD.DyeORderID)
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET XACT_ABORT ON
SET REMOTE_PROC_TRANSACTIONS OFF
OPEN ASN_CURSOR
SET NOCOUNT ON
FETCH NEXT FROM
ASN_CURSOR
INTO
@.ASN_NoticeID,
@.ASN_SHPMNT_ID,
@.ASN_DTE_SHPD,
@.ASN_TME_SHPD,
@.ASN_GRSS_WGHT,
@.ASN_BOL_NBR,
@.ASN_PO_NBR,
@.ASN_SKU_CODE,
@.ASN_PCKG_LP_CDE,
@.ASN_SBPCK_LP_CDE,
@.ASN_DYELOT,
@.ASN_QTY,
@.ASN_OVRL_WDTH
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN DISTRIBUTED TRANSACTION
-- BEGIN TRANSACTION
SELECT
@.NEXT_SQNC_NBR_LDED = MAX(SQNC_NBR_LDED) + 1
FROM
-- PUR_EDI_ASN
ORACDV..SLI.FRANK_EDI_ASN
IF (@.NEXT_SQNC_NBR_LDED IS NULL)
BEGIN
SELECT @.NEXT_SQNC_NBR_LDED = 1
END
INSERT INTO
-- PUR_EDI_ASN
ORACDV..SLI.FRANK_EDI_ASN
(
DTE_ASN_LDED,
SQNC_NBR_LDED,
SHPMNT_ID,
DTE_SHPD,
TME_SHPD,
GRSS_WGHT,
GRSS_WGHT_UOM,
BOL_NBR,
MTHD_OF_PYMNT,
VNDR_UCC_NBR,
PO_NBR,
SKU_CODE,
PCKG_LP_CDE,
SBPCK_LP_CDE,
DYE_LOT,
QTY,
QTY_UOM,
OVRL_WDTH,
OVRL_WDTH_UOM
)
VALUES
(
GETDATE(),
@.NEXT_SQNC_NBR_LDED,
@.ASN_SHPMNT_ID,
@.ASN_DTE_SHPD,
@.ASN_TME_SHPD,
@.ASN_GRSS_WGHT,
'LB',
@.ASN_BOL_NBR,
'PP',
'662082',
@.ASN_PO_NBR,
@.ASN_SKU_CODE,
@.ASN_PCKG_LP_CDE,
@.ASN_SBPCK_LP_CDE,
@.ASN_DYELOT,
@.ASN_QTY,
'YD',
@.ASN_OVRL_WDTH,
'IN'
)
update
Shipping_EDINotice
set
DateSent = GETDATE(),
EDIStatus = 'DN'
where
NoticeID = @.ASN_NoticeID
COMMIT TRANSACTION
FETCH NEXT FROM
ASN_CURSOR
INTO
@.ASN_NoticeID,
@.ASN_SHPMNT_ID,
@.ASN_DTE_SHPD,
@.ASN_TME_SHPD,
@.ASN_GRSS_WGHT,
@.ASN_BOL_NBR,
@.ASN_PO_NBR,
@.ASN_SKU_CODE,
@.ASN_PCKG_LP_CDE,
@.ASN_SBPCK_LP_CDE,
@.ASN_DYELOT,
@.ASN_QTY,
@.ASN_OVRL_WDTH
END
CLOSE ASN_CURSOR
DEALLOCATE ASN_CURSOR
SET NOCOUNT OFF|||WOW...
A world of pain...brother (sister?)
How many rows are we talking about...
BEGIN DISTRIBUTED TRANSACTION...whoa
you got a lot of things going on...I don't think it's the linked server...
And what's to say an insert hasn't occured since you got the last id?|||A lot of things going on?
One cursor, traversing the cursor with a while loop with one select to get the next sequence number, an insert and an update.
The cursor on average will return 300 rows.
There will be an insert for each row from the cursor.
That's not really very much I don't think.|||But you're comitting on every row...what happens if you fail for a row?
You're not checking @.@.ERROR or @.@.ROWCOUNT...and even if you did, how would you know where you left off?
And I can't remeber (not that I use them much), but doesn't Oracle (which version btw) use some form of identity?
If just not sure about the remote transactions...my guess that's the problem (Sorry)...is the Next Id column nullabe? Is it unique..
Can you test it as a set based operation? Something like below?
(Columns not in the correct order):
INSERT INTO
ORACDV..SLI.FRANK_EDI_ASN
(
DTE_ASN_LDED,
SHPMNT_ID,
DTE_SHPD,
TME_SHPD,
GRSS_WGHT,
GRSS_WGHT_UOM,
BOL_NBR,
MTHD_OF_PYMNT,
VNDR_UCC_NBR,
PO_NBR,
SKU_CODE,
PCKG_LP_CDE,
SBPCK_LP_CDE,
DYE_LOT,
QTY,
QTY_UOM,
OVRL_WDTH,
OVRL_WDTH_UOM
)
select GetDate(),
SEDI.NoticeID,
SHPMNT_ID = SBOL.BillOfLadingID,
DTE_SHPD = substring(convert(varchar,datepart(yy,SBOL.ActualS hipDate)),3,2) +
case
when datepart(mm,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mm,SBOL.ActualShipDate))
else convert(varchar,datepart(mm,SBOL.ActualShipDate))
end +
case
when datepart(dd,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(dd,SBOL.ActualShipDate))
else convert(varchar,datepart(dd,SBOL.ActualShipDate))
end,
TME_SHPD = case
when datepart(hh,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(hh,SBOL.ActualShipDate))
else convert(varchar,datepart(hh,SBOL.ActualShipDate))
end +
case
when datepart(mi,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mi,SBOL.ActualShipDate))
else convert(varchar,datepart(mi,SBOL.ActualShipDate))
end,
GRSS_WGHT = IM.GrossWgt,
BOL_NBR = SBOL.BillOfLadingID,
PO_NBR = substring(SPSH.CustomerPO,1,8),
SKU_CODE = OLD.CustomerStyle,
PCKG_LP_CDE = (select TalcNo from Inventory_Items II2 where (II2.CartonID = II.CartonID) and (LeftIndex = 1)),
SBPCK_LP_CDE = II.TalcNo,
DYE_LOT = OD.OldDyeOrder,
QTY = IID.NetYards,
OVRL_WDTH = INSM.InspectedWidth
from
Shipping_EDINotice SEDI,
Shipping_BillofLadings SBOL,
Shipping_PackingSlips_Header SPSH,
Inventory_Master IM,
Inventory_Items II,
Inventory_Items_Detail IID,
Orders_LibOrders_Detail OLD,
Inspections_Master INSM,
Inventory_Webs_Header IWH,
Orders_DyeOrders OD
where
(SEDI.BillofLadingID = SBOL.BillOfLadingID) and
(SBOL.BillofLadingID = SPSH.BillofLadingID) and
(SPSH.PackingSlipID = IM.PackingSlipID) and
(IM.CartonID = II.CartonID) and
((II.RightIndex - II.LeftIndex) = 1) and
(II.ItemID = IID.ItemID) and
(II.ItemID = INSM.ItemID) and
(IM.SoldToLibID = OLD.LibOrderID) and
(II.WebID = IWH.WebID) and
(SEDI.EDIStatus = 'CR') and
(IWH.DyeOrderID = OD.DyeORderID)|||One addition I would make is it looks like there is a sequenced or identity column in the destination table. You will probably have to create a temp table with an identity column, then insert the results of the monster query in the temp table. The to get to the Oracle table, you will just need to add the max(id) from the oracle table to the identity value.
select @.next_id = max(SQNC_NBR_LDED)
from ORACDV..SLI.FRANK_EDI_ASN
insert into ORACDV..SLI.FRANK_EDI_ASN
select id + @.next_id, (other fields)
from #tempresult.
A bit abbreviated, but it would be worlds better than a (shudder) cursor.
I am having some problems getting a link to an Oracle server working properly. Here is my environment:
MS SQL Server 7.0 on Windows 2000
Oracle 8.1.7 on HPUX
MDAC 2.5
The link is working. I can query data and update date but it is incredibly slow.
Anyone run into this before?
Thanks...That's pretty vague...
What are you doing?
SELECT * FROM myMillionRowTable a FULL JOIN myMillionRowTable b
ON a.NonkeyedColumn = b.NonkeyedColumn
???|||I have a stored procedure that uses a cursor that queries data on the SQL Server and on average does about 300 inserts into the Oracle server. Takes 18 minutes. I modified the procedure to insert into a local table on SQL Server with the exact same format and it took less than 5 seconds. I would expect it to be somewhat slower going across a link, but not that much slower.|||Post the Cursor...
I bet a set based solution will fly...
Are you joining tables across servers?
Remeber the network gets involved...
Let's see what you're doing...
The "Miracle" of linked servers has limits, regadless what MS says...|||The link is not in any involved with the cursor. I only use the cursor to gather data to insert into a table across the link. The cursor query executes quickly (few seconds).
Here is the procedure I am using:
Thanks...
CREATE PROC lp_Send_ASN_Data_To_Oracle
as
DECLARE
@.ASN_NoticeID INT,
@.ASN_SHPMNT_ID VARCHAR(15),
@.ASN_DTE_SHPD VARCHAR(6),
@.ASN_TME_SHPD VARCHAR(4),
@.ASN_GRSS_WGHT DECIMAL(9,1),
@.ASN_BOL_NBR VARCHAR(15),
@.ASN_PO_NBR VARCHAR(8),
@.ASN_SKU_CODE VARCHAR(16),
@.ASN_PCKG_LP_CDE VARCHAR(9),
@.ASN_SBPCK_LP_CDE VARCHAR(9),
@.ASN_DYELOT VARCHAR(10),
@.ASN_QTY DECIMAL(9,2),
@.ASN_OVRL_WDTH DECIMAL(18,2),
@.NEXT_SQNC_NBR_LDED INT
DECLARE ASN_CURSOR SCROLL CURSOR FOR
select
SEDI.NoticeID,
SHPMNT_ID = SBOL.BillOfLadingID,
DTE_SHPD = substring(convert(varchar,datepart(yy,SBOL.ActualS hipDate)),3,2) +
case
when datepart(mm,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mm,SBOL.ActualShipDate))
else convert(varchar,datepart(mm,SBOL.ActualShipDate))
end +
case
when datepart(dd,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(dd,SBOL.ActualShipDate))
else convert(varchar,datepart(dd,SBOL.ActualShipDate))
end,
TME_SHPD = case
when datepart(hh,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(hh,SBOL.ActualShipDate))
else convert(varchar,datepart(hh,SBOL.ActualShipDate))
end +
case
when datepart(mi,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mi,SBOL.ActualShipDate))
else convert(varchar,datepart(mi,SBOL.ActualShipDate))
end,
GRSS_WGHT = IM.GrossWgt,
-- GRSS_WGHT_UOM = 'LB',
BOL_NBR = SBOL.BillOfLadingID,
-- MTHD_OF_PYMNT = 'PP',
-- VNDR_UCC_NBR = '662082',
PO_NBR = substring(SPSH.CustomerPO,1,8),
SKU_CODE = OLD.CustomerStyle,
PCKG_LP_CDE = (select TalcNo from Inventory_Items II2 where (II2.CartonID = II.CartonID) and (LeftIndex = 1)),
SBPCK_LP_CDE = II.TalcNo,
DYE_LOT = OD.OldDyeOrder,
QTY = IID.NetYards,
-- QTY_UOM = 'YD',
OVRL_WDTH = INSM.InspectedWidth
-- OVRL_WDTH_UOM = 'IN'
from
Shipping_EDINotice SEDI,
Shipping_BillofLadings SBOL,
Shipping_PackingSlips_Header SPSH,
Inventory_Master IM,
Inventory_Items II,
Inventory_Items_Detail IID,
Orders_LibOrders_Detail OLD,
Inspections_Master INSM,
Inventory_Webs_Header IWH,
Orders_DyeOrders OD
where
(SEDI.BillofLadingID = SBOL.BillOfLadingID) and
(SBOL.BillofLadingID = SPSH.BillofLadingID) and
(SPSH.PackingSlipID = IM.PackingSlipID) and
(IM.CartonID = II.CartonID) and
((II.RightIndex - II.LeftIndex) = 1) and
(II.ItemID = IID.ItemID) and
(II.ItemID = INSM.ItemID) and
(IM.SoldToLibID = OLD.LibOrderID) and
(II.WebID = IWH.WebID) and
(SEDI.EDIStatus = 'CR') and
(IWH.DyeOrderID = OD.DyeORderID)
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
SET XACT_ABORT ON
SET REMOTE_PROC_TRANSACTIONS OFF
OPEN ASN_CURSOR
SET NOCOUNT ON
FETCH NEXT FROM
ASN_CURSOR
INTO
@.ASN_NoticeID,
@.ASN_SHPMNT_ID,
@.ASN_DTE_SHPD,
@.ASN_TME_SHPD,
@.ASN_GRSS_WGHT,
@.ASN_BOL_NBR,
@.ASN_PO_NBR,
@.ASN_SKU_CODE,
@.ASN_PCKG_LP_CDE,
@.ASN_SBPCK_LP_CDE,
@.ASN_DYELOT,
@.ASN_QTY,
@.ASN_OVRL_WDTH
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN DISTRIBUTED TRANSACTION
-- BEGIN TRANSACTION
SELECT
@.NEXT_SQNC_NBR_LDED = MAX(SQNC_NBR_LDED) + 1
FROM
-- PUR_EDI_ASN
ORACDV..SLI.FRANK_EDI_ASN
IF (@.NEXT_SQNC_NBR_LDED IS NULL)
BEGIN
SELECT @.NEXT_SQNC_NBR_LDED = 1
END
INSERT INTO
-- PUR_EDI_ASN
ORACDV..SLI.FRANK_EDI_ASN
(
DTE_ASN_LDED,
SQNC_NBR_LDED,
SHPMNT_ID,
DTE_SHPD,
TME_SHPD,
GRSS_WGHT,
GRSS_WGHT_UOM,
BOL_NBR,
MTHD_OF_PYMNT,
VNDR_UCC_NBR,
PO_NBR,
SKU_CODE,
PCKG_LP_CDE,
SBPCK_LP_CDE,
DYE_LOT,
QTY,
QTY_UOM,
OVRL_WDTH,
OVRL_WDTH_UOM
)
VALUES
(
GETDATE(),
@.NEXT_SQNC_NBR_LDED,
@.ASN_SHPMNT_ID,
@.ASN_DTE_SHPD,
@.ASN_TME_SHPD,
@.ASN_GRSS_WGHT,
'LB',
@.ASN_BOL_NBR,
'PP',
'662082',
@.ASN_PO_NBR,
@.ASN_SKU_CODE,
@.ASN_PCKG_LP_CDE,
@.ASN_SBPCK_LP_CDE,
@.ASN_DYELOT,
@.ASN_QTY,
'YD',
@.ASN_OVRL_WDTH,
'IN'
)
update
Shipping_EDINotice
set
DateSent = GETDATE(),
EDIStatus = 'DN'
where
NoticeID = @.ASN_NoticeID
COMMIT TRANSACTION
FETCH NEXT FROM
ASN_CURSOR
INTO
@.ASN_NoticeID,
@.ASN_SHPMNT_ID,
@.ASN_DTE_SHPD,
@.ASN_TME_SHPD,
@.ASN_GRSS_WGHT,
@.ASN_BOL_NBR,
@.ASN_PO_NBR,
@.ASN_SKU_CODE,
@.ASN_PCKG_LP_CDE,
@.ASN_SBPCK_LP_CDE,
@.ASN_DYELOT,
@.ASN_QTY,
@.ASN_OVRL_WDTH
END
CLOSE ASN_CURSOR
DEALLOCATE ASN_CURSOR
SET NOCOUNT OFF|||WOW...
A world of pain...brother (sister?)
How many rows are we talking about...
BEGIN DISTRIBUTED TRANSACTION...whoa
you got a lot of things going on...I don't think it's the linked server...
And what's to say an insert hasn't occured since you got the last id?|||A lot of things going on?
One cursor, traversing the cursor with a while loop with one select to get the next sequence number, an insert and an update.
The cursor on average will return 300 rows.
There will be an insert for each row from the cursor.
That's not really very much I don't think.|||But you're comitting on every row...what happens if you fail for a row?
You're not checking @.@.ERROR or @.@.ROWCOUNT...and even if you did, how would you know where you left off?
And I can't remeber (not that I use them much), but doesn't Oracle (which version btw) use some form of identity?
If just not sure about the remote transactions...my guess that's the problem (Sorry)...is the Next Id column nullabe? Is it unique..
Can you test it as a set based operation? Something like below?
(Columns not in the correct order):
INSERT INTO
ORACDV..SLI.FRANK_EDI_ASN
(
DTE_ASN_LDED,
SHPMNT_ID,
DTE_SHPD,
TME_SHPD,
GRSS_WGHT,
GRSS_WGHT_UOM,
BOL_NBR,
MTHD_OF_PYMNT,
VNDR_UCC_NBR,
PO_NBR,
SKU_CODE,
PCKG_LP_CDE,
SBPCK_LP_CDE,
DYE_LOT,
QTY,
QTY_UOM,
OVRL_WDTH,
OVRL_WDTH_UOM
)
select GetDate(),
SEDI.NoticeID,
SHPMNT_ID = SBOL.BillOfLadingID,
DTE_SHPD = substring(convert(varchar,datepart(yy,SBOL.ActualS hipDate)),3,2) +
case
when datepart(mm,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mm,SBOL.ActualShipDate))
else convert(varchar,datepart(mm,SBOL.ActualShipDate))
end +
case
when datepart(dd,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(dd,SBOL.ActualShipDate))
else convert(varchar,datepart(dd,SBOL.ActualShipDate))
end,
TME_SHPD = case
when datepart(hh,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(hh,SBOL.ActualShipDate))
else convert(varchar,datepart(hh,SBOL.ActualShipDate))
end +
case
when datepart(mi,SBOL.ActualShipDate) < 10 then '0' + convert(varchar,datepart(mi,SBOL.ActualShipDate))
else convert(varchar,datepart(mi,SBOL.ActualShipDate))
end,
GRSS_WGHT = IM.GrossWgt,
BOL_NBR = SBOL.BillOfLadingID,
PO_NBR = substring(SPSH.CustomerPO,1,8),
SKU_CODE = OLD.CustomerStyle,
PCKG_LP_CDE = (select TalcNo from Inventory_Items II2 where (II2.CartonID = II.CartonID) and (LeftIndex = 1)),
SBPCK_LP_CDE = II.TalcNo,
DYE_LOT = OD.OldDyeOrder,
QTY = IID.NetYards,
OVRL_WDTH = INSM.InspectedWidth
from
Shipping_EDINotice SEDI,
Shipping_BillofLadings SBOL,
Shipping_PackingSlips_Header SPSH,
Inventory_Master IM,
Inventory_Items II,
Inventory_Items_Detail IID,
Orders_LibOrders_Detail OLD,
Inspections_Master INSM,
Inventory_Webs_Header IWH,
Orders_DyeOrders OD
where
(SEDI.BillofLadingID = SBOL.BillOfLadingID) and
(SBOL.BillofLadingID = SPSH.BillofLadingID) and
(SPSH.PackingSlipID = IM.PackingSlipID) and
(IM.CartonID = II.CartonID) and
((II.RightIndex - II.LeftIndex) = 1) and
(II.ItemID = IID.ItemID) and
(II.ItemID = INSM.ItemID) and
(IM.SoldToLibID = OLD.LibOrderID) and
(II.WebID = IWH.WebID) and
(SEDI.EDIStatus = 'CR') and
(IWH.DyeOrderID = OD.DyeORderID)|||One addition I would make is it looks like there is a sequenced or identity column in the destination table. You will probably have to create a temp table with an identity column, then insert the results of the monster query in the temp table. The to get to the Oracle table, you will just need to add the max(id) from the oracle table to the identity value.
select @.next_id = max(SQNC_NBR_LDED)
from ORACDV..SLI.FRANK_EDI_ASN
insert into ORACDV..SLI.FRANK_EDI_ASN
select id + @.next_id, (other fields)
from #tempresult.
A bit abbreviated, but it would be worlds better than a (shudder) cursor.
Monday, March 26, 2012
Link Server to MySQL Database
Hi
I ma trying to create a linked server to a MySQL database via ODBC. I am running SQL 2005 on Windows 2003 R2 (64-bit). Can any one point me in the right direction .
Any ideas|||MSDASQL (provider shipped with SS2005 which serves as OLEDB-ODBC bridge) is not supported in 64-bit. Check if there is an 64-bit OLEDB provider for MySQL that you can use directly.|||Thanks for that i will look into it.
|||
Hi James,
Were you able to do that? I am having the trouble doing the same. If you have done, could you please post the procedure.
Thanks,
Neelesh:)
|||Unfortunatly I have not had much time to look at it. I have not managed to find a free / cheap OLE MYSQL driver. Sorry I was not much help|||Can you guys post your email? I can send you a documentation for creating linked servers with MySQL|||My email address is jamesholmes@.greggs.co.uk
Thanks
|||Have you tried email me ?
Thanks
Link Server to MySQL Database
Hi
I ma trying to create a linked server to a MySQL database via ODBC. I am running SQL 2005 on Windows 2003 R2 (64-bit). Can any one point me in the right direction .
Any ideas|||MSDASQL (provider shipped with SS2005 which serves as OLEDB-ODBC bridge) is not supported in 64-bit. Check if there is an 64-bit OLEDB provider for MySQL that you can use directly.|||Thanks for that i will look into it.
|||
Hi James,
Were you able to do that? I am having the trouble doing the same. If you have done, could you please post the procedure.
Thanks,
Neelesh:)
|||Unfortunatly I have not had much time to look at it. I have not managed to find a free / cheap OLE MYSQL driver. Sorry I was not much help|||Can you guys post your email? I can send you a documentation for creating linked servers with MySQL|||My email address is jamesholmes@.greggs.co.uk
Thanks
|||Have you tried email me ?
Thanks
Link server not working thru Windows authentication - "Login failed
I've two SQL server 2005 servers running under the same domain and the
SQL server service is also running under domain adminstrator for both
the servers. The OS on both the servers is Win2K3.
All the developers were working on these servers as 'sa' but recently
we have changed it to run on windows authentications. In doing so I
added all the developer's domain logins to a local group "Power Users"
on both the servers and now every body can log on to these servers
using their domain logins(windows authentication). But the link server
from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
run a distributed querry from QA.
I've also tried adding individual logins on the Security tab on link
server gui and checked impersonate check box also but with no results.
On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
sysadmin as server role.
One strange thing is if I directly logon (using my login) to the serv1
and run a distributed querry using the same link server it works.
Any help will be appriciated
Thanks,
SGsaboo,
I believe that you have a delegation problem here. Is Kerberos running in
your domain and are both servers correctly set up to user Kerboros? Read
this for an explanation:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
When you directly login to serv1 you are now only one hop away from serv2
and delegation is not an issue. But, from a desktop to serv2 is two hops.
RLF
"saboo" <subodh97@.gmail.com> wrote in message
news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
> I've two SQL server 2005 servers running under the same domain and the
> SQL server service is also running under domain adminstrator for both
> the servers. The OS on both the servers is Win2K3.
> All the developers were working on these servers as 'sa' but recently
> we have changed it to run on windows authentications. In doing so I
> added all the developer's domain logins to a local group "Power Users"
> on both the servers and now every body can log on to these servers
> using their domain logins(windows authentication). But the link server
> from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> run a distributed querry from QA.
> I've also tried adding individual logins on the Security tab on link
> server gui and checked impersonate check box also but with no results.
> On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> sysadmin as server role.
> One strange thing is if I directly logon (using my login) to the serv1
> and run a distributed querry using the same link server it works.
> Any help will be appriciated
> Thanks,
> SG|||On Feb 13, 2:40=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> saboo,
> I believe that you have a delegation problem here. =A0Is Kerberos running =in
> your domain and are both servers correctly set up to user Kerboros? =A0Rea=d
> this for an explanation:http://blogs.msdn.com/sql_protocols/archive/2006/0=
8/10/694657.aspx
> When you directly login to serv1 you are now only one hop away from serv2
> and delegation is not an issue. =A0But, from a desktop to serv2 is two hop=s.
> RLF
> "saboo" <subod...@.gmail.com> wrote in message
> news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
>
> > I've two SQL server 2005 servers running under the same domain and the
> > SQL server service is also running under domain adminstrator for both
> > the servers. The OS on both the servers is Win2K3.
> > All the developers were working on these servers as 'sa' but recently
> > we have changed it to run on windows authentications. In doing so I
> > added all the developer's domain logins to a local group "Power Users"
> > on both the servers and now every body can log on to these servers
> > using their domain logins(windows authentication). But the link server
> > from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> > Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> > run a distributed querry from QA.
> > I've also tried adding individual logins on the Security tab on link
> > server gui and checked impersonate check box also but with no results.
> > On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> > sysadmin as server role.
> > One strange thing is if I directly logon (using my login) to the serv1
> > and run a distributed querry using the same link server it works.
> > Any help will be appriciated
> > Thanks,
> > SG- Hide quoted text -
> - Show quoted text -
Delegation isn't too bad to deal with. Most likely your SQL servers
are not using a Local Service account to run (good for you, this is
not good security) and the accounts it's using are not domain admins
(again, good job). Here's the quick down-low on what you need to do:
You need domain admin rights to create an SPN, if you don't have them
you need to get your DA to do this for you. Download the Windows
Server tools for your flavor of server(http://go.microsoft.com/fwlink/?
LinkId=3D100114). You want the SetSPN tool. You need to create an SPN
for each of the SQL servers, this can be done from your workstation as
it's set Domain wide, not at the server level.
setspn -a service/name hostname
i.e.
setspn -a MSSQLsvc/my.server.name:1433 mydomain\MySqlServiceAccount
Again, do this for BOTH sql servers...sql
SQL server service is also running under domain adminstrator for both
the servers. The OS on both the servers is Win2K3.
All the developers were working on these servers as 'sa' but recently
we have changed it to run on windows authentications. In doing so I
added all the developer's domain logins to a local group "Power Users"
on both the servers and now every body can log on to these servers
using their domain logins(windows authentication). But the link server
from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
run a distributed querry from QA.
I've also tried adding individual logins on the Security tab on link
server gui and checked impersonate check box also but with no results.
On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
sysadmin as server role.
One strange thing is if I directly logon (using my login) to the serv1
and run a distributed querry using the same link server it works.
Any help will be appriciated
Thanks,
SGsaboo,
I believe that you have a delegation problem here. Is Kerberos running in
your domain and are both servers correctly set up to user Kerboros? Read
this for an explanation:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
When you directly login to serv1 you are now only one hop away from serv2
and delegation is not an issue. But, from a desktop to serv2 is two hops.
RLF
"saboo" <subodh97@.gmail.com> wrote in message
news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
> I've two SQL server 2005 servers running under the same domain and the
> SQL server service is also running under domain adminstrator for both
> the servers. The OS on both the servers is Win2K3.
> All the developers were working on these servers as 'sa' but recently
> we have changed it to run on windows authentications. In doing so I
> added all the developer's domain logins to a local group "Power Users"
> on both the servers and now every body can log on to these servers
> using their domain logins(windows authentication). But the link server
> from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> run a distributed querry from QA.
> I've also tried adding individual logins on the Security tab on link
> server gui and checked impersonate check box also but with no results.
> On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> sysadmin as server role.
> One strange thing is if I directly logon (using my login) to the serv1
> and run a distributed querry using the same link server it works.
> Any help will be appriciated
> Thanks,
> SG|||On Feb 13, 2:40=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> saboo,
> I believe that you have a delegation problem here. =A0Is Kerberos running =in
> your domain and are both servers correctly set up to user Kerboros? =A0Rea=d
> this for an explanation:http://blogs.msdn.com/sql_protocols/archive/2006/0=
8/10/694657.aspx
> When you directly login to serv1 you are now only one hop away from serv2
> and delegation is not an issue. =A0But, from a desktop to serv2 is two hop=s.
> RLF
> "saboo" <subod...@.gmail.com> wrote in message
> news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
>
> > I've two SQL server 2005 servers running under the same domain and the
> > SQL server service is also running under domain adminstrator for both
> > the servers. The OS on both the servers is Win2K3.
> > All the developers were working on these servers as 'sa' but recently
> > we have changed it to run on windows authentications. In doing so I
> > added all the developer's domain logins to a local group "Power Users"
> > on both the servers and now every body can log on to these servers
> > using their domain logins(windows authentication). But the link server
> > from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> > Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> > run a distributed querry from QA.
> > I've also tried adding individual logins on the Security tab on link
> > server gui and checked impersonate check box also but with no results.
> > On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> > sysadmin as server role.
> > One strange thing is if I directly logon (using my login) to the serv1
> > and run a distributed querry using the same link server it works.
> > Any help will be appriciated
> > Thanks,
> > SG- Hide quoted text -
> - Show quoted text -
Delegation isn't too bad to deal with. Most likely your SQL servers
are not using a Local Service account to run (good for you, this is
not good security) and the accounts it's using are not domain admins
(again, good job). Here's the quick down-low on what you need to do:
You need domain admin rights to create an SPN, if you don't have them
you need to get your DA to do this for you. Download the Windows
Server tools for your flavor of server(http://go.microsoft.com/fwlink/?
LinkId=3D100114). You want the SetSPN tool. You need to create an SPN
for each of the SQL servers, this can be done from your workstation as
it's set Domain wide, not at the server level.
setspn -a service/name hostname
i.e.
setspn -a MSSQLsvc/my.server.name:1433 mydomain\MySqlServiceAccount
Again, do this for BOTH sql servers...sql
Link Server doesn't work except through query analyzer: MSSQL freezing / timing out
Environment:
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)
Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.
The SQL we need to run is the following:
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
Our problem:
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans
str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 > 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
When we make a Stored Procedure and run the following SQL, it freezes.
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
We've also tried the following with the same results;
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 > xxxx
The same thing happens when we try to run the "SELECT" by itself.
SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
ORDER BY A.column1
What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?
Hi
Try running SQL Server Profiler to see what is going on when you run it from
QA and by using DTS? Do you have trigger/s defined on the table?
How big is your load? Does the database set up iwth FULL recovery mode?
"SQL Server Questions" <farkerku@.gmail.com> wrote in message
news:1168149322.774824.251680@.51g2000cwl.googlegro ups.com...
> Environment:
> Server1 (Local)
> OS Windows 2000 Server
> SQL Server 2000
> Server2 (Remote)
> OS Windows 2003 Server
> SQL Server 2000
> (Both with most recent service packs)
> Using Enterprise Manager, we have set up the Link Server (LINK_A) in
> the Local Server 1 to connect to Server 2.
> The SQL we need to run is the following:
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx;
> When we run this from the Query Analyzer, it completes with no problems
> in a few seconds.
> Our problem:
> When we add the DTS Package as the ActiveX Script (VB Script) to the
> Local Package, it times out at "obj_Conn.Execute str_Sql"
> Dim Sql, obj_Conn
> Set obj_Conn = CreateObject("ADODB.Connection")
> obj_Conn.Open XXXX
> obj_Conn.BeginTrans
> str_Sql = "INSERT INTO table1("
> str_Sql = str_Sql & "column1"
> str_Sql = str_Sql & ", column2"
> str_Sql = str_Sql & ")"
> str_Sql = str_Sql & " SELECT Ａ.column1"
> str_Sql = str_Sql & ", A.column2"
> str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
> str_Sql = str_Sql & " WHERE A.column1 > 0"
> str_Sql = str_Sql & ";"
> obj_Conn.Execute str_Sql
> ----
> When we make a Stored Procedure and run the following SQL, it freezes.
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> We've also tried the following with the same results;
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
> WHERE A.column1 > xxxx
>
> The same thing happens when we try to run the "SELECT" by itself.
> SELECT TOP 1 @.test=A.column1
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> ORDER BY A.column1
> What is going wrong here, and how do we need to change this so that it
> runs without timing out or freezing?
>
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)
Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.
The SQL we need to run is the following:
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
Our problem:
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans
str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 > 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
When we make a Stored Procedure and run the following SQL, it freezes.
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
We've also tried the following with the same results;
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 > xxxx
The same thing happens when we try to run the "SELECT" by itself.
SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
ORDER BY A.column1
What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?
Hi
Try running SQL Server Profiler to see what is going on when you run it from
QA and by using DTS? Do you have trigger/s defined on the table?
How big is your load? Does the database set up iwth FULL recovery mode?
"SQL Server Questions" <farkerku@.gmail.com> wrote in message
news:1168149322.774824.251680@.51g2000cwl.googlegro ups.com...
> Environment:
> Server1 (Local)
> OS Windows 2000 Server
> SQL Server 2000
> Server2 (Remote)
> OS Windows 2003 Server
> SQL Server 2000
> (Both with most recent service packs)
> Using Enterprise Manager, we have set up the Link Server (LINK_A) in
> the Local Server 1 to connect to Server 2.
> The SQL we need to run is the following:
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx;
> When we run this from the Query Analyzer, it completes with no problems
> in a few seconds.
> Our problem:
> When we add the DTS Package as the ActiveX Script (VB Script) to the
> Local Package, it times out at "obj_Conn.Execute str_Sql"
> Dim Sql, obj_Conn
> Set obj_Conn = CreateObject("ADODB.Connection")
> obj_Conn.Open XXXX
> obj_Conn.BeginTrans
> str_Sql = "INSERT INTO table1("
> str_Sql = str_Sql & "column1"
> str_Sql = str_Sql & ", column2"
> str_Sql = str_Sql & ")"
> str_Sql = str_Sql & " SELECT Ａ.column1"
> str_Sql = str_Sql & ", A.column2"
> str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
> str_Sql = str_Sql & " WHERE A.column1 > 0"
> str_Sql = str_Sql & ";"
> obj_Conn.Execute str_Sql
> ----
> When we make a Stored Procedure and run the following SQL, it freezes.
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> We've also tried the following with the same results;
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
> WHERE A.column1 > xxxx
>
> The same thing happens when we try to run the "SELECT" by itself.
> SELECT TOP 1 @.test=A.column1
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> ORDER BY A.column1
> What is going wrong here, and how do we need to change this so that it
> runs without timing out or freezing?
>
Link Server doesn't work except through query analyzer: MSSQL freezing / timing out
Environment:
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)
Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.
The SQL we need to run is the following:
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
Our problem:
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans
str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 > 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
----
When we make a Stored Procedure and run the following SQL, it freezes.
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
We've also tried the following with the same results;
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 > xxxx
The same thing happens when we try to run the "SELECT" by itself.
SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
ORDER BY A.column1
What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?Hi
Try running SQL Server Profiler to see what is going on when you run it from
QA and by using DTS? Do you have trigger/s defined on the table?
How big is your load? Does the database set up iwth FULL recovery mode?
"SQL Server Questions" <farkerku@.gmail.com> wrote in message
news:1168149322.774824.251680@.51g2000cwl.googlegroups.com...
> Environment:
> Server1 (Local)
> OS Windows 2000 Server
> SQL Server 2000
> Server2 (Remote)
> OS Windows 2003 Server
> SQL Server 2000
> (Both with most recent service packs)
> Using Enterprise Manager, we have set up the Link Server (LINK_A) in
> the Local Server 1 to connect to Server 2.
> The SQL we need to run is the following:
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx;
> When we run this from the Query Analyzer, it completes with no problems
> in a few seconds.
> Our problem:
> When we add the DTS Package as the ActiveX Script (VB Script) to the
> Local Package, it times out at "obj_Conn.Execute str_Sql"
> Dim Sql, obj_Conn
> Set obj_Conn = CreateObject("ADODB.Connection")
> obj_Conn.Open XXXX
> obj_Conn.BeginTrans
> str_Sql = "INSERT INTO table1("
> str_Sql = str_Sql & "column1"
> str_Sql = str_Sql & ", column2"
> str_Sql = str_Sql & ")"
> str_Sql = str_Sql & " SELECT Ａ.column1"
> str_Sql = str_Sql & ", A.column2"
> str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
> str_Sql = str_Sql & " WHERE A.column1 > 0"
> str_Sql = str_Sql & ";"
> obj_Conn.Execute str_Sql
> ----
> When we make a Stored Procedure and run the following SQL, it freezes.
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> We've also tried the following with the same results;
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
> WHERE A.column1 > xxxx
>
> The same thing happens when we try to run the "SELECT" by itself.
> SELECT TOP 1 @.test=A.column1
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> ORDER BY A.column1
> What is going wrong here, and how do we need to change this so that it
> runs without timing out or freezing?
>
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)
Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.
The SQL we need to run is the following:
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
Our problem:
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans
str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 > 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
----
When we make a Stored Procedure and run the following SQL, it freezes.
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
We've also tried the following with the same results;
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 > xxxx
The same thing happens when we try to run the "SELECT" by itself.
SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
ORDER BY A.column1
What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?Hi
Try running SQL Server Profiler to see what is going on when you run it from
QA and by using DTS? Do you have trigger/s defined on the table?
How big is your load? Does the database set up iwth FULL recovery mode?
"SQL Server Questions" <farkerku@.gmail.com> wrote in message
news:1168149322.774824.251680@.51g2000cwl.googlegroups.com...
> Environment:
> Server1 (Local)
> OS Windows 2000 Server
> SQL Server 2000
> Server2 (Remote)
> OS Windows 2003 Server
> SQL Server 2000
> (Both with most recent service packs)
> Using Enterprise Manager, we have set up the Link Server (LINK_A) in
> the Local Server 1 to connect to Server 2.
> The SQL we need to run is the following:
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx;
> When we run this from the Query Analyzer, it completes with no problems
> in a few seconds.
> Our problem:
> When we add the DTS Package as the ActiveX Script (VB Script) to the
> Local Package, it times out at "obj_Conn.Execute str_Sql"
> Dim Sql, obj_Conn
> Set obj_Conn = CreateObject("ADODB.Connection")
> obj_Conn.Open XXXX
> obj_Conn.BeginTrans
> str_Sql = "INSERT INTO table1("
> str_Sql = str_Sql & "column1"
> str_Sql = str_Sql & ", column2"
> str_Sql = str_Sql & ")"
> str_Sql = str_Sql & " SELECT Ａ.column1"
> str_Sql = str_Sql & ", A.column2"
> str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
> str_Sql = str_Sql & " WHERE A.column1 > 0"
> str_Sql = str_Sql & ";"
> obj_Conn.Execute str_Sql
> ----
> When we make a Stored Procedure and run the following SQL, it freezes.
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> We've also tried the following with the same results;
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
> WHERE A.column1 > xxxx
>
> The same thing happens when we try to run the "SELECT" by itself.
> SELECT TOP 1 @.test=A.column1
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> ORDER BY A.column1
> What is going wrong here, and how do we need to change this so that it
> runs without timing out or freezing?
>
Link Server doesn't work except through query analyzer: MSSQL freezing / timing out
Environment:
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
And if you slap BEGIN/COMMIT TRANSACTION around it?
I believe this is the problem is the increased security with DTC in Windows
2003. We had a problem with this a while back. This was what the support
professional from MS told us:
>
And if you slap BEGIN/COMMIT TRANSACTION around it?
>
>
I believe this is the problem is the increased security with DTC in Windows
2003. We had a problem with this a while back. This was what the support
professional from MS told us:
>
Let starts with a few troubleshooting steps, first take security MSDTC
configurations at the minimum level. Component Services -My Computer
-Right click proprieties ->MSDTC -Select use local coordinator ->
Security Configuration -Enable Network DTC access, Enable remote
administration, Enable Allow Remote clients, Enable allow inbound and
outbound, Select no authentication required, Enable XA transactions and
verify that NT AUTHORITY\NetworkService is set in DTC Logon account.
>
Restart DTC service and SQL Server and test if issue persists.
>
If problem persist please follow this KB article:
http://support.microsoft.com/kb/306843 - How to troubleshoot
MS DTC firewall issues.
>
I think the step to restart SQL Server is unnecessary.
>
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)
Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.
The SQL we need to run is the following:
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
Our problem:
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans
str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
------------------
When we make a Stored Procedure and run the following SQL, it freezes.
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx
We've also tried the following with the same results;
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 xxxx
The same thing happens when we try to run the "SELECT" by itself.
SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx
ORDER BY A.column1
What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?SQL Server Questions (farkerku@.gmail.com) writes:
Quote:
Originally Posted by
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx;
>
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
And if you slap BEGIN/COMMIT TRANSACTION around it?
Quote:
Originally Posted by
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
I believe this is the problem is the increased security with DTC in Windows
2003. We had a problem with this a while back. This was what the support
professional from MS told us:
Let starts with a few troubleshooting steps, first take security MSDTC
configurations at the minimum level. Component Services -My Computer
-Right click proprieties ->MSDTC -Select use local coordinator ->
Security Configuration -Enable Network DTC access, Enable remote
administration, Enable Allow Remote clients, Enable allow inbound and
outbound, Select no authentication required, Enable XA transactions and
verify that NT AUTHORITY\NetworkService is set in DTC Logon account.
Restart DTC service and SQL Server and test if issue persists.
If problem persist please follow this KB article:
http://support.microsoft.com/kb/306843 - How to troubleshoot
MS DTC firewall issues.
I think the step to restart SQL Server is unnecessary.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks!
We've got to this point and are getting a "New transaction cannot
enlist in specified transaction coordinator" error.
Erland Sommarskog wrote:
Quote:
Originally Posted by
SQL Server Questions (farkerku@.gmail.com) writes:
Quote:
Originally Posted by
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
>
And if you slap BEGIN/COMMIT TRANSACTION around it?
>
Quote:
Originally Posted by
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
>
I believe this is the problem is the increased security with DTC in Windows
2003. We had a problem with this a while back. This was what the support
professional from MS told us:
>
Let starts with a few troubleshooting steps, first take security MSDTC
configurations at the minimum level. Component Services -My Computer
-Right click proprieties ->MSDTC -Select use local coordinator ->
Security Configuration -Enable Network DTC access, Enable remote
administration, Enable Allow Remote clients, Enable allow inbound and
outbound, Select no authentication required, Enable XA transactions and
verify that NT AUTHORITY\NetworkService is set in DTC Logon account.
>
Restart DTC service and SQL Server and test if issue persists.
>
If problem persist please follow this KB article:
http://support.microsoft.com/kb/306843 - How to troubleshoot
MS DTC firewall issues.
>
I think the step to restart SQL Server is unnecessary.
>
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Link Server doesn't work except through query analyzer: MSSQL freezing / timing out
Environment:
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)
Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.
The SQL we need to run is the following:
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
Our problem:
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans
str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 > 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
----
When we make a Stored Procedure and run the following SQL, it freezes.
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
We've also tried the following with the same results;
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 > xxxx
The same thing happens when we try to run the "SELECT" by itself.
SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
ORDER BY A.column1
What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?Hi
Try running SQL Server Profiler to see what is going on when you run it from
QA and by using DTS? Do you have trigger/s defined on the table?
How big is your load? Does the database set up iwth FULL recovery mode?
"SQL Server Questions" <farkerku@.gmail.com> wrote in message
news:1168149322.774824.251680@.51g2000cwl.googlegroups.com...
> Environment:
> Server1 (Local)
> OS Windows 2000 Server
> SQL Server 2000
> Server2 (Remote)
> OS Windows 2003 Server
> SQL Server 2000
> (Both with most recent service packs)
> Using Enterprise Manager, we have set up the Link Server (LINK_A) in
> the Local Server 1 to connect to Server 2.
> The SQL we need to run is the following:
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx;
> When we run this from the Query Analyzer, it completes with no problems
> in a few seconds.
> Our problem:
> When we add the DTS Package as the ActiveX Script (VB Script) to the
> Local Package, it times out at "obj_Conn.Execute str_Sql"
> Dim Sql, obj_Conn
> Set obj_Conn = CreateObject("ADODB.Connection")
> obj_Conn.Open XXXX
> obj_Conn.BeginTrans
> str_Sql = "INSERT INTO table1("
> str_Sql = str_Sql & "column1"
> str_Sql = str_Sql & ", column2"
> str_Sql = str_Sql & ")"
> str_Sql = str_Sql & " SELECT Ａ.column1"
> str_Sql = str_Sql & ", A.column2"
> str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
> str_Sql = str_Sql & " WHERE A.column1 > 0"
> str_Sql = str_Sql & ";"
> obj_Conn.Execute str_Sql
> ----
> When we make a Stored Procedure and run the following SQL, it freezes.
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> We've also tried the following with the same results;
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
> WHERE A.column1 > xxxx
>
> The same thing happens when we try to run the "SELECT" by itself.
> SELECT TOP 1 @.test=A.column1
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> ORDER BY A.column1
> What is going wrong here, and how do we need to change this so that it
> runs without timing out or freezing?
>sql
Server1 (Local)
OS Windows 2000 Server
SQL Server 2000
Server2 (Remote)
OS Windows 2003 Server
SQL Server 2000
(Both with most recent service packs)
Using Enterprise Manager, we have set up the Link Server (LINK_A) in
the Local Server 1 to connect to Server 2.
The SQL we need to run is the following:
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx;
When we run this from the Query Analyzer, it completes with no problems
in a few seconds.
Our problem:
When we add the DTS Package as the ActiveX Script (VB Script) to the
Local Package, it times out at "obj_Conn.Execute str_Sql"
Dim Sql, obj_Conn
Set obj_Conn = CreateObject("ADODB.Connection")
obj_Conn.Open XXXX
obj_Conn.BeginTrans
str_Sql = "INSERT INTO table1("
str_Sql = str_Sql & "column1"
str_Sql = str_Sql & ", column2"
str_Sql = str_Sql & ")"
str_Sql = str_Sql & " SELECT Ａ.column1"
str_Sql = str_Sql & ", A.column2"
str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
str_Sql = str_Sql & " WHERE A.column1 > 0"
str_Sql = str_Sql & ";"
obj_Conn.Execute str_Sql
----
When we make a Stored Procedure and run the following SQL, it freezes.
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
We've also tried the following with the same results;
INSERT INTO table1(column1,column2)
SELECT A.column1, A.column2
FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
WHERE A.column1 > xxxx
The same thing happens when we try to run the "SELECT" by itself.
SELECT TOP 1 @.test=A.column1
FROM LINK_A.catalog_name.dbo.table2 AS A
WHERE A.column1 > xxxx
ORDER BY A.column1
What is going wrong here, and how do we need to change this so that it
runs without timing out or freezing?Hi
Try running SQL Server Profiler to see what is going on when you run it from
QA and by using DTS? Do you have trigger/s defined on the table?
How big is your load? Does the database set up iwth FULL recovery mode?
"SQL Server Questions" <farkerku@.gmail.com> wrote in message
news:1168149322.774824.251680@.51g2000cwl.googlegroups.com...
> Environment:
> Server1 (Local)
> OS Windows 2000 Server
> SQL Server 2000
> Server2 (Remote)
> OS Windows 2003 Server
> SQL Server 2000
> (Both with most recent service packs)
> Using Enterprise Manager, we have set up the Link Server (LINK_A) in
> the Local Server 1 to connect to Server 2.
> The SQL we need to run is the following:
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx;
> When we run this from the Query Analyzer, it completes with no problems
> in a few seconds.
> Our problem:
> When we add the DTS Package as the ActiveX Script (VB Script) to the
> Local Package, it times out at "obj_Conn.Execute str_Sql"
> Dim Sql, obj_Conn
> Set obj_Conn = CreateObject("ADODB.Connection")
> obj_Conn.Open XXXX
> obj_Conn.BeginTrans
> str_Sql = "INSERT INTO table1("
> str_Sql = str_Sql & "column1"
> str_Sql = str_Sql & ", column2"
> str_Sql = str_Sql & ")"
> str_Sql = str_Sql & " SELECT Ａ.column1"
> str_Sql = str_Sql & ", A.column2"
> str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"
> str_Sql = str_Sql & " WHERE A.column1 > 0"
> str_Sql = str_Sql & ";"
> obj_Conn.Execute str_Sql
> ----
> When we make a Stored Procedure and run the following SQL, it freezes.
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> We've also tried the following with the same results;
> INSERT INTO table1(column1,column2)
> SELECT A.column1, A.column2
> FROM [LINK_A].[catalog_name].[dbo].[table2] AS A
> WHERE A.column1 > xxxx
>
> The same thing happens when we try to run the "SELECT" by itself.
> SELECT TOP 1 @.test=A.column1
> FROM LINK_A.catalog_name.dbo.table2 AS A
> WHERE A.column1 > xxxx
> ORDER BY A.column1
> What is going wrong here, and how do we need to change this so that it
> runs without timing out or freezing?
>sql
Monday, March 12, 2012
Limiting users to user sql server
Hello there
When i started using sql server i choose Windows authentication.
Now i need to move to sql server authentication so that not anyone can have
full access to Enterprice manager or the query anlyser
In fact i don't want that the administrator on the Windows 2000 server would
be the administrator on the sql server
What i need to do for this?
any help would be usefulYou can remove the administrators group from being able to log into SQL
Server. There is no changing to SQL authentication, you would change to
mixed mode which allows SQL and Windows authentication. But it sounds like
you are just wanting to restrict people in the admin group from accessing
and that is done by removing the admin groups from SQL.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:Og$rJBWxDHA.3224@.tk2msftngp13.phx.gbl...
have
would
you do this make sure you know the sa password or have another NT account
that has sysadmin priviledge.
Rand
This posting is provided "as is" with no warranties and confers no rights.
When i started using sql server i choose Windows authentication.
Now i need to move to sql server authentication so that not anyone can have
full access to Enterprice manager or the query anlyser
In fact i don't want that the administrator on the Windows 2000 server would
be the administrator on the sql server
What i need to do for this?
any help would be usefulYou can remove the administrators group from being able to log into SQL
Server. There is no changing to SQL authentication, you would change to
mixed mode which allows SQL and Windows authentication. But it sounds like
you are just wanting to restrict people in the admin group from accessing
and that is done by removing the admin groups from SQL.
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:Og$rJBWxDHA.3224@.tk2msftngp13.phx.gbl...
quote:
> Hello there
> When i started using sql server i choose Windows authentication.
> Now i need to move to sql server authentication so that not anyone can
have
quote:
> full access to Enterprice manager or the query anlyser
> In fact i don't want that the administrator on the Windows 2000 server
would
quote:|||Just remove the BUILTIN\Administrators login from SQL Server. But before
> be the administrator on the sql server
> What i need to do for this?
> any help would be useful
>
you do this make sure you know the sa password or have another NT account
that has sysadmin priviledge.
Rand
This posting is provided "as is" with no warranties and confers no rights.
Limiting tables displayed in new window for SQL 2000 Enterprise Manager
Can anyone tell me if there's a way to create new windows from the
tables section of a database in Enterprise Manager such that I can
limit the tables included in that window? Ultimately, I'm looking for
a solution whereby I can group tables, stored procedures, etc. into
logical groups that can then be displayed using different MMC
consoles. I'm looking for either a GUI-level or programatic approach.
tables section of a database in Enterprise Manager such that I can
limit the tables included in that window? Ultimately, I'm looking for
a solution whereby I can group tables, stored procedures, etc. into
logical groups that can then be displayed using different MMC
consoles. I'm looking for either a GUI-level or programatic approach.
Thank you,
Dax WestermanUsing EM as is no. You can build your own version of EM though and display
whatever you want using SQL-DMO.
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Dax Westerman" <daxw@.bellsouth.net> wrote in message
news:273bcb55.0404300435.7ac4ba6b@.posting.google.c om...
> Can anyone tell me if there's a way to create new windows from the
> tables section of a database in Enterprise Manager such that I can
> limit the tables included in that window? Ultimately, I'm looking for
> a solution whereby I can group tables, stored procedures, etc. into
> logical groups that can then be displayed using different MMC
> consoles. I'm looking for either a GUI-level or programatic approach.
> Thank you,
> Dax Westerman
Monday, February 20, 2012
Limit remote connections
Hi,
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I be
concerned? How other people deal with remote connections?
Thanks in advance,
Bing
Hello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan
|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I be
concerned? How other people deal with remote connections?
Thanks in advance,
Bing
Hello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan
|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan
Limit remote connections
Hi,
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I b
e
concerned? How other people deal with remote connections?
Thanks in advance,
BingHello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I b
e
concerned? How other people deal with remote connections?
Thanks in advance,
BingHello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan
Limit remote connections
Hi,
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I be
concerned? How other people deal with remote connections?
Thanks in advance,
BingHello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan
We use SQL server 2000 on windows 2003. We usually use remote desktop to
connect to the SQL server. For security reasons, only 2 concurrent remote
desktop connections are allowed. I just noticed I'm able to use 'osql'
command to open as many connections to the SQL server as I want. Should I be
concerned? How other people deal with remote connections?
Thanks in advance,
BingHello, Bing
In my opinion, the limit of 2 concurrent remote desktop connections on
Windows 2003 is for licensing reasons, not for security reasons.
Having an unlimited number of connections to SQL Server is no cause of
concern for security reasons, as long as each connection is properly
authentificated and comes from an authorised user and/or computer.
Razvan|||On Jun 2, 1:33 pm, Razvan Socol <rso...@.gmail.com> wrote:
> Hello, Bing
> In my opinion, the limit of 2 concurrent remote desktop connections on
> Windows 2003 is for licensing reasons, not for security reasons.
> Having an unlimited number of connections to SQL Server is no cause of
> concern for security reasons, as long as each connection is properly
> authentificated and comes from an authorised user and/or computer.
> Razvan
However, you can limit the number of connections on an instance of SQL
Server, by using the "user connections" option. By default, SQL Server
allows a maximum of 32,767 user connections, but the actual number of
user connections allowed also depends on the limits of your
applications and hardware. Using the "user connections" option, you
can limit the number of connections to any desired number (less than
32,767), but this is done for performance reasons, not for security
reasons.
Razvan
Subscribe to:
Posts (Atom)