Hi all!
I have a SQL Server database with a secondary datafile (*.ndf). I would like to be able to delete that file and only use the *.mdf to store data. Of cource I also have a transaction log file.
Is it possible to move data from the ndf to the mdf and the delete the ndf'
Regards
/FredrikHi,
SQL Server spans data across all data files within a filegroup.So all files
fill will be used for all DMLs. to remove a file , you must first have the
data moved off of the file onto the other members in the data set.
To do this, use the EMPTY FILE parameter in DBCC SHEINKFILE command.
This will empty the file and mark it as unavailable. After that execute
REMOVE FILE in ALTER DATABASE command to drop the NDF file.
See DBCC SHRINKFILE and ALTER database in books online for syntax.
--
Thanks
Hari
MCDBA
"Fredrik" <Fredrik@.discussions.microsoft.com> wrote in message
news:5DCC35E5-43A0-45B7-A3F6-81353A62F09E@.microsoft.com...
> Hi all!
> I have a SQL Server database with a secondary datafile (*.ndf). I would
like to be able to delete that file and only use the *.mdf to store data. Of
cource I also have a transaction log file.
> Is it possible to move data from the ndf to the mdf and the delete the
ndf'
> Regards
> /Fredrik
Showing posts with label delete. Show all posts
Showing posts with label delete. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Remove cursor from trigger - multi row updates
Remove cursor from trigger - multi row updates
I need to remove to following cursor for the delete trigger (I didn't
design it) as it is causing too much of a bottle neck on the server.
What would be the best way to impletement this, bearing in mind that it
would have to take in account multi row functionality as the system
very rarely has single row deletes. I have SQL2K Enterprise on
Win2KServer.
CREATE TRIGGER [tri_DelRecs] ON MyTable
FOR DELETE
AS
SET NOCOUNT ON
DECLARE @.MasterID int
DECLARE dc2 CURSOR FOR
SELECT [Master ID] FROM Deleted
OPEN dc2
FETCH NEXT FROM dc2 INTO @.MasterID
WHILE @.@.FETCH_STATUS = 0
BEGIN
delete from [tb1] where [master id] = @.masterid
delete from [tb2] where [master id] = @.masterid
delete from [tb3] where [master id] = @.masterid
delete from [tb4] where [master id] = @.masterid
delete from [tb5] where [master id] = @.masterid
.. .. .. .. .. .. ..
.. .. .. .. .. .. ..
delete from [tbn] where [master id] = @.masterid
FETCH NEXT FROM dc2 INTO @.Masterid
END
CLOSE dc2
DEALLOCATE dc2
Would I able to accomplish this using a series of JOINs on master id?
Thanks
qhdelete from [tb1] where [master id] in (Select [Master ID] from Deleted)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Scott" <quackhandle1975@.yahoo.co.uk> schrieb im Newsbeitrag
news:1113569793.664500.141850@.g14g2000cwa.googlegroups.com...
> Remove cursor from trigger - multi row updates
> I need to remove to following cursor for the delete trigger (I didn't
> design it) as it is causing too much of a bottle neck on the server.
> What would be the best way to impletement this, bearing in mind that it
> would have to take in account multi row functionality as the system
> very rarely has single row deletes. I have SQL2K Enterprise on
> Win2KServer.
>
> CREATE TRIGGER [tri_DelRecs] ON MyTable
> FOR DELETE
> AS
> SET NOCOUNT ON
> DECLARE @.MasterID int
> DECLARE dc2 CURSOR FOR
> SELECT [Master ID] FROM Deleted
> OPEN dc2
> FETCH NEXT FROM dc2 INTO @.MasterID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from [tb1] where [master id] = @.masterid
> delete from [tb2] where [master id] = @.masterid
> delete from [tb3] where [master id] = @.masterid
> delete from [tb4] where [master id] = @.masterid
> delete from [tb5] where [master id] = @.masterid
> .. .. .. .. .. .. ..
> .. .. .. .. .. .. ..
> delete from [tbn] where [master id] = @.masterid
>
> FETCH NEXT FROM dc2 INTO @.Masterid
> END
> CLOSE dc2
> DEALLOCATE dc2
>
> Would I able to accomplish this using a series of JOINs on master id?
>
> Thanks
> qh
>|||delete [tb1]
where exists (select * from deleted as d where d.[master id] = tb1.[master
id])
...
AMB
"Scott" wrote:
> Remove cursor from trigger - multi row updates
> I need to remove to following cursor for the delete trigger (I didn't
> design it) as it is causing too much of a bottle neck on the server.
> What would be the best way to impletement this, bearing in mind that it
> would have to take in account multi row functionality as the system
> very rarely has single row deletes. I have SQL2K Enterprise on
> Win2KServer.
>
> CREATE TRIGGER [tri_DelRecs] ON MyTable
> FOR DELETE
> AS
> SET NOCOUNT ON
> DECLARE @.MasterID int
> DECLARE dc2 CURSOR FOR
> SELECT [Master ID] FROM Deleted
> OPEN dc2
> FETCH NEXT FROM dc2 INTO @.MasterID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from [tb1] where [master id] = @.masterid
> delete from [tb2] where [master id] = @.masterid
> delete from [tb3] where [master id] = @.masterid
> delete from [tb4] where [master id] = @.masterid
> delete from [tb5] where [master id] = @.masterid
> ... .. .. .. .. .. ..
> ... .. .. .. .. .. ..
> delete from [tbn] where [master id] = @.masterid
>
> FETCH NEXT FROM dc2 INTO @.Masterid
> END
> CLOSE dc2
> DEALLOCATE dc2
>
> Would I able to accomplish this using a series of JOINs on master id?
>
> Thanks
> qh
>|||Have you considered using ON DELETE CASCADE instead of a trigger?
Never use cursors in triggers, for one thing the set-based code is
usually much simpler:
CREATE TRIGGER [tri_DelRecs] ON mytable FOR DELETE
AS
DELETE FROM tb1
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE [master id] = tb1.[master id])
DELETE FROM tb2
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE [master id] = tb2.[master id])
... etc
David Portas
SQL Server MVP
--|||Change your DDL on the referencing tables:
CREATE TABLE subord_n
(...
master_id INTEGER NOT NULL
REFERENCES Master (master_id)
ON DELETE CASCADE,
.);
Using DRI actions also gives the optimizer information about the
relationships, so all your code gets better.|||Thanks for all your replies guys, much appreciated!
I have used been testing and I think I have what I'm after using the
following trigger, although I will try the ON DELETE CASCADE.
Cheers
qh|||Doh!
CREATE TRIGGER [tri_MyTrigger] ON [dbo].[tbl1]
FOR INSERT, UPDATE, DELETE
AS
IF @.@.ROWCOUNT = 0
BEGIN
PRINT 'There are no records to delete!'
RETURN
END
IF NOT EXISTS(SELECT * FROM DELETED)
--sql block for insert
INSERT INTO tbl2([ID], [Name], [Team])
SELECT [ID], [name], [Team] FROM Inserted
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED)
--sql block for delete
DELETE FROM tbl2
WHERE tbl2.[ID] IN (SELECT [ID] FROM Deleted)
ELSE
--sql block for update
UPDATE tbl2
SET [Team] = Inserted.[Team], [Name] = Inserted.[Name]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
qh
I need to remove to following cursor for the delete trigger (I didn't
design it) as it is causing too much of a bottle neck on the server.
What would be the best way to impletement this, bearing in mind that it
would have to take in account multi row functionality as the system
very rarely has single row deletes. I have SQL2K Enterprise on
Win2KServer.
CREATE TRIGGER [tri_DelRecs] ON MyTable
FOR DELETE
AS
SET NOCOUNT ON
DECLARE @.MasterID int
DECLARE dc2 CURSOR FOR
SELECT [Master ID] FROM Deleted
OPEN dc2
FETCH NEXT FROM dc2 INTO @.MasterID
WHILE @.@.FETCH_STATUS = 0
BEGIN
delete from [tb1] where [master id] = @.masterid
delete from [tb2] where [master id] = @.masterid
delete from [tb3] where [master id] = @.masterid
delete from [tb4] where [master id] = @.masterid
delete from [tb5] where [master id] = @.masterid
.. .. .. .. .. .. ..
.. .. .. .. .. .. ..
delete from [tbn] where [master id] = @.masterid
FETCH NEXT FROM dc2 INTO @.Masterid
END
CLOSE dc2
DEALLOCATE dc2
Would I able to accomplish this using a series of JOINs on master id?
Thanks
qhdelete from [tb1] where [master id] in (Select [Master ID] from Deleted)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Scott" <quackhandle1975@.yahoo.co.uk> schrieb im Newsbeitrag
news:1113569793.664500.141850@.g14g2000cwa.googlegroups.com...
> Remove cursor from trigger - multi row updates
> I need to remove to following cursor for the delete trigger (I didn't
> design it) as it is causing too much of a bottle neck on the server.
> What would be the best way to impletement this, bearing in mind that it
> would have to take in account multi row functionality as the system
> very rarely has single row deletes. I have SQL2K Enterprise on
> Win2KServer.
>
> CREATE TRIGGER [tri_DelRecs] ON MyTable
> FOR DELETE
> AS
> SET NOCOUNT ON
> DECLARE @.MasterID int
> DECLARE dc2 CURSOR FOR
> SELECT [Master ID] FROM Deleted
> OPEN dc2
> FETCH NEXT FROM dc2 INTO @.MasterID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from [tb1] where [master id] = @.masterid
> delete from [tb2] where [master id] = @.masterid
> delete from [tb3] where [master id] = @.masterid
> delete from [tb4] where [master id] = @.masterid
> delete from [tb5] where [master id] = @.masterid
> .. .. .. .. .. .. ..
> .. .. .. .. .. .. ..
> delete from [tbn] where [master id] = @.masterid
>
> FETCH NEXT FROM dc2 INTO @.Masterid
> END
> CLOSE dc2
> DEALLOCATE dc2
>
> Would I able to accomplish this using a series of JOINs on master id?
>
> Thanks
> qh
>|||delete [tb1]
where exists (select * from deleted as d where d.[master id] = tb1.[master
id])
...
AMB
"Scott" wrote:
> Remove cursor from trigger - multi row updates
> I need to remove to following cursor for the delete trigger (I didn't
> design it) as it is causing too much of a bottle neck on the server.
> What would be the best way to impletement this, bearing in mind that it
> would have to take in account multi row functionality as the system
> very rarely has single row deletes. I have SQL2K Enterprise on
> Win2KServer.
>
> CREATE TRIGGER [tri_DelRecs] ON MyTable
> FOR DELETE
> AS
> SET NOCOUNT ON
> DECLARE @.MasterID int
> DECLARE dc2 CURSOR FOR
> SELECT [Master ID] FROM Deleted
> OPEN dc2
> FETCH NEXT FROM dc2 INTO @.MasterID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from [tb1] where [master id] = @.masterid
> delete from [tb2] where [master id] = @.masterid
> delete from [tb3] where [master id] = @.masterid
> delete from [tb4] where [master id] = @.masterid
> delete from [tb5] where [master id] = @.masterid
> ... .. .. .. .. .. ..
> ... .. .. .. .. .. ..
> delete from [tbn] where [master id] = @.masterid
>
> FETCH NEXT FROM dc2 INTO @.Masterid
> END
> CLOSE dc2
> DEALLOCATE dc2
>
> Would I able to accomplish this using a series of JOINs on master id?
>
> Thanks
> qh
>|||Have you considered using ON DELETE CASCADE instead of a trigger?
Never use cursors in triggers, for one thing the set-based code is
usually much simpler:
CREATE TRIGGER [tri_DelRecs] ON mytable FOR DELETE
AS
DELETE FROM tb1
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE [master id] = tb1.[master id])
DELETE FROM tb2
WHERE EXISTS
(SELECT *
FROM Deleted
WHERE [master id] = tb2.[master id])
... etc
David Portas
SQL Server MVP
--|||Change your DDL on the referencing tables:
CREATE TABLE subord_n
(...
master_id INTEGER NOT NULL
REFERENCES Master (master_id)
ON DELETE CASCADE,
.);
Using DRI actions also gives the optimizer information about the
relationships, so all your code gets better.|||Thanks for all your replies guys, much appreciated!
I have used been testing and I think I have what I'm after using the
following trigger, although I will try the ON DELETE CASCADE.
Cheers
qh|||Doh!
CREATE TRIGGER [tri_MyTrigger] ON [dbo].[tbl1]
FOR INSERT, UPDATE, DELETE
AS
IF @.@.ROWCOUNT = 0
BEGIN
PRINT 'There are no records to delete!'
RETURN
END
IF NOT EXISTS(SELECT * FROM DELETED)
--sql block for insert
INSERT INTO tbl2([ID], [Name], [Team])
SELECT [ID], [name], [Team] FROM Inserted
ELSE
IF NOT EXISTS(SELECT * FROM INSERTED)
--sql block for delete
DELETE FROM tbl2
WHERE tbl2.[ID] IN (SELECT [ID] FROM Deleted)
ELSE
--sql block for update
UPDATE tbl2
SET [Team] = Inserted.[Team], [Name] = Inserted.[Name]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
qh
remove cascade update and delete from table
hello, once upon a time when i created my db (originally in access then used the conversion tool, which i now know is wrong!) i thought it would be an amazing idea to have cascading updates and deletes, however it turns out now this is exactly not what i want! if i leave them in then it throws errors when i delete records out of my stock table as related records are in the order_line table here is the code (well i think so, im not the best at sqlserver as you probably can tell already) that im using if anyone can help or point me in the right direction that would be great, thanks
USE [nashdfDB1]GO/****** Object: Table [dbo].[tbl_stock] Script Date: 07/13/2007 02:52:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[tbl_stock]( [Stock_ID] [int] IDENTITY(1,1) NOT NULL, [cat_id] [int] NOT NULL CONSTRAINT [DF__tbl_stock__cat_i__15502E78] DEFAULT ((0)), [sub_cat_id] [int] NULL CONSTRAINT [DF__tbl_stock__sub_c__164452B1] DEFAULT ((0)), [location] [int] NULL CONSTRAINT [DF__tbl_stock__locat__173876EA] DEFAULT ((0)), [n_or_sh] [varchar](50) NULL, [title] [varchar](255) NULL, [description] [varchar](255) NULL, [size] [varchar](50) NULL, [colour] [varchar](50) NULL, [cost_price] [decimal](9, 2) NULL CONSTRAINT [DF__tbl_stock__cost___182C9B23] DEFAULT ((0)), [selling_price] [decimal](9, 2) NULL CONSTRAINT [DF__tbl_stock__selli__1920BF5C] DEFAULT ((0)), [qty] [varchar](50) NULL, [date] [datetime] NULL CONSTRAINT [DF__tbl_stock__date__1A14E395] DEFAULT (getdate()), [condition] [varchar](255) NULL, [notes] [varchar](255) NULL, [visible] [bit] NULL CONSTRAINT [DF__tbl_stock__visib__1B0907CE] DEFAULT ((1)), [picture1] [varchar](50) NULL, [picture1_thumb] [varchar](50) NULL, [picture2] [varchar](50) NULL, [picture2_thumb] [varchar](50) NULL, [picture3] [varchar](50) NULL, [picture3_thumb] [varchar](50) NULL, [picture4] [varchar](50) NULL, [picture4_thumb] [varchar](50) NULL, [display_price] [varchar](50) NULL, [created_by] [varchar](50) NULL, [buying_in_recipt] [varchar](255) NULL, CONSTRAINT [tbl_stock$PrimaryKey] PRIMARY KEY CLUSTERED( [Stock_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] GOSET ANSI_PADDING OFF
Regards
Jez
It's hard to read all that DDL but here's the syntax:
ALTER TABLE <table_name>
DROP FOREIGN KEY <foreignkey_name>
See eghttp://www.infogoal.com/sql/sql-drop-foreignkey.htm
You can read more about constraints athttp://www.mssqlcity.com/Articles/General/using_constraints.htm and you can get the full alter table syntax athttp://msdn2.microsoft.com/en-us/library/aa275462(SQL.80).aspx
Tuesday, March 20, 2012
Remote Server issue...
Did you ever figure this out ? I have the same problem.
I think you have to delete all subscribers and then delete the remote
server. Then create a linked server and then add back the subscribers.
Becuase, the sp_serveroption for data access will not work with a
remote server
Brett wrote:
dannr09
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message110773.htmlIf you're using SQL Replication, we setup Remote Servers as opposed to
Linked Servers already. What functionality are you trying to acheive?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
I think you have to delete all subscribers and then delete the remote
server. Then create a linked server and then add back the subscribers.
Becuase, the sp_serveroption for data access will not work with a
remote server
Brett wrote:
quote:
> *I am attmepting to setup up a linked server from my
> production server (sql1) to my reporting server (sql2).
> I have replication from sql1 to sql2.
> I noticed that there is already a remote server setup on
> sql1 for sql2. It will not allow me to setup a linked
> server for sql2. Plus, I am unable to run a distributed
> query... I get the error message that the remote server
> is not configured from data access.
> How do I setup a linked server from sql1 to sql2 so that
> I have data access?
> Please advise..
> Thank you,
> Brett
> I am using SQL Server 2000 for my production and
> reporting servers. *
dannr09
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message110773.htmlIf you're using SQL Replication, we setup Remote Servers as opposed to
Linked Servers already. What functionality are you trying to acheive?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Remote server delete
Hi,
Can any please shed some light on the following problem or direct me to an
information source ?
DELETE remoteserver.database.schema.table
returns the following error message.
iServer: Msg 7345, Level 16, State 1, Line 2
OLE DB provider 'MSDASQL' could not delete from table '"S1031118"."CISDTA6".
"DXDV36001"'. There was a recoverable, provider-specific error, such as an
RPC failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 106 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Multiple-step operation generated errors.
Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_FAIL].
Any help would really be appreciated.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1What kind of Linked server is this ? (product etc.)
Jens Suessmeyer.|||Sorry about the delay.
The linked server is configured as server type: other data source
provider name:
Microsoft OLE DB provider for ODBC drivers
Jens wrote:
>What kind of Linked server is this ? (product etc.)
>Jens Suessmeyer.
Message posted via http://www.webservertalk.com
Can any please shed some light on the following problem or direct me to an
information source ?
DELETE remoteserver.database.schema.table
returns the following error message.
iServer: Msg 7345, Level 16, State 1, Line 2
OLE DB provider 'MSDASQL' could not delete from table '"S1031118"."CISDTA6".
"DXDV36001"'. There was a recoverable, provider-specific error, such as an
RPC failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 106 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Multiple-step operation generated errors.
Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_FAIL].
Any help would really be appreciated.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1What kind of Linked server is this ? (product etc.)
Jens Suessmeyer.|||Sorry about the delay.
The linked server is configured as server type: other data source
provider name:
Microsoft OLE DB provider for ODBC drivers
Jens wrote:
>What kind of Linked server is this ? (product etc.)
>Jens Suessmeyer.
Message posted via http://www.webservertalk.com
Remote Server / Linked Server
I have transactional replication working and it created a remote server
to be used for replication. I am not able to insert/delete/update
between the two servers through the "remote server" connection. I need
to use a linked server, do I need to set up this linked server prior to
configuring any replication so a remote server isn't created during
configuration? Is there something else I can do instead of this.
Thanks
sp_serveroption 'remoteServerName','data access',true
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142538888.208897.59030@.e56g2000cwe.googlegro ups.com...
>I have transactional replication working and it created a remote server
> to be used for replication. I am not able to insert/delete/update
> between the two servers through the "remote server" connection. I need
> to use a linked server, do I need to set up this linked server prior to
> configuring any replication so a remote server isn't created during
> configuration? Is there something else I can do instead of this.
> Thanks
>
|||I changed the data access to true, but now I get the following error
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
The security properties are set to: "Be made using the login's current
security context"
I have sysadmin priviledges so I shouldn't be restricted by that. I
can get it to work and pull data, if I change the security properties
to use the sa login and password, but that isn't secure enough. What
options do I have. Thanks for your time.
|||Enter the sa account and password on the linked server.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142872805.003828.93830@.t31g2000cwb.googlegro ups.com...
>I changed the data access to true, but now I get the following error
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> The security properties are set to: "Be made using the login's current
> security context"
> I have sysadmin priviledges so I shouldn't be restricted by that. I
> can get it to work and pull data, if I change the security properties
> to use the sa login and password, but that isn't secure enough. What
> options do I have. Thanks for your time.
>
|||Maybe I didn't state my question correctly. I am wanting to use the
security property of: "Be made using the login's current
security context" so it can limit the users priviledges to the other
server. When I do this I get the following error.
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection
I was able to use the sa login and password, but that allowed everyone
to have unlimited access to the linked server.
How do I get the security property of: "Be made using the login's
current security context" to work without getting the error. Thanks
|||I don't believe you can as you are logging on using Windows Authentication,
and it appears that the remote server is not part of the domain. Perhaps try
pass through authentication. By any chance is the remote server across the
internet?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142879939.773655.304440@.z34g2000cwc.googlegr oups.com...
> Maybe I didn't state my question correctly. I am wanting to use the
> security property of: "Be made using the login's current
> security context" so it can limit the users priviledges to the other
> server. When I do this I get the following error.
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection
>
> I was able to use the sa login and password, but that allowed everyone
> to have unlimited access to the linked server.
>
> How do I get the security property of: "Be made using the login's
> current security context" to work without getting the error. Thanks
>
to be used for replication. I am not able to insert/delete/update
between the two servers through the "remote server" connection. I need
to use a linked server, do I need to set up this linked server prior to
configuring any replication so a remote server isn't created during
configuration? Is there something else I can do instead of this.
Thanks
sp_serveroption 'remoteServerName','data access',true
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142538888.208897.59030@.e56g2000cwe.googlegro ups.com...
>I have transactional replication working and it created a remote server
> to be used for replication. I am not able to insert/delete/update
> between the two servers through the "remote server" connection. I need
> to use a linked server, do I need to set up this linked server prior to
> configuring any replication so a remote server isn't created during
> configuration? Is there something else I can do instead of this.
> Thanks
>
|||I changed the data access to true, but now I get the following error
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection.
The security properties are set to: "Be made using the login's current
security context"
I have sysadmin priviledges so I shouldn't be restricted by that. I
can get it to work and pull data, if I change the security properties
to use the sa login and password, but that isn't secure enough. What
options do I have. Thanks for your time.
|||Enter the sa account and password on the linked server.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142872805.003828.93830@.t31g2000cwb.googlegro ups.com...
>I changed the data access to true, but now I get the following error
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection.
> The security properties are set to: "Be made using the login's current
> security context"
> I have sysadmin priviledges so I shouldn't be restricted by that. I
> can get it to work and pull data, if I change the security properties
> to use the sa login and password, but that isn't secure enough. What
> options do I have. Thanks for your time.
>
|||Maybe I didn't state my question correctly. I am wanting to use the
security property of: "Be made using the login's current
security context" so it can limit the users priviledges to the other
server. When I do this I get the following error.
Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted
SQL Server connection
I was able to use the sa login and password, but that allowed everyone
to have unlimited access to the linked server.
How do I get the security property of: "Be made using the login's
current security context" to work without getting the error. Thanks
|||I don't believe you can as you are logging on using Windows Authentication,
and it appears that the remote server is not part of the domain. Perhaps try
pass through authentication. By any chance is the remote server across the
internet?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"billy_karnes" <billy_karnes@.corphealth.com> wrote in message
news:1142879939.773655.304440@.z34g2000cwc.googlegr oups.com...
> Maybe I didn't state my question correctly. I am wanting to use the
> security property of: "Be made using the login's current
> security context" so it can limit the users priviledges to the other
> server. When I do this I get the following error.
> Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted
> SQL Server connection
>
> I was able to use the sa login and password, but that allowed everyone
> to have unlimited access to the linked server.
>
> How do I get the security property of: "Be made using the login's
> current security context" to work without getting the error. Thanks
>
Wednesday, March 7, 2012
Remote Database Management through LDAP Tools
Hai All
How to insert and delete records from a table in the remote database?"
I want it to be done through a tool that used LDAP Servers.
I want tools for the LDAP .
Thank u Advance
Regards
Sunil
aboutknowledge@.gmail.comWithout knowing a lot more about what you're trying to do, it is hard to recommend any specific tool or tools. Are you looking for somethng like MIIS?
-PatP|||I should be able to add / delete / modify the records of a table in a database from a remote location:
1. either just by using browser only.
2. Or a tool (preferably that utilizes LDAP servers in it's implementation)
I think this clearly explain the situation.
How to insert and delete records from a table in the remote database?"
I want it to be done through a tool that used LDAP Servers.
I want tools for the LDAP .
Thank u Advance
Regards
Sunil
aboutknowledge@.gmail.comWithout knowing a lot more about what you're trying to do, it is hard to recommend any specific tool or tools. Are you looking for somethng like MIIS?
-PatP|||I should be able to add / delete / modify the records of a table in a database from a remote location:
1. either just by using browser only.
2. Or a tool (preferably that utilizes LDAP servers in it's implementation)
I think this clearly explain the situation.
Subscribe to:
Posts (Atom)