Hey all...
We have several websites running SQL server back ends and we have
successful registrations for each in SQL Servers Enterprise Manager.
We also have a client who hosts their own SQL Server Database and we
used to have a good registration for their SQL Server.
Our client has moved their website and SQL Server to a new server as
the machine they were running on had an unknown fault, and since they
have moved everything we can no longer see the database or register
the server in Enterprise Manager.
We can still register servers from other clients and the client we are
having trouble with can register their server using standard dial up
connections and the admin guy did it from his cable modem account from
home. We have tried using the VPN tunnel they prepared for us as well
as trying to register after they put the server outside the firewall.
The only firewall changes that were made wer after our first being
unable to connect, then they allowed all traffic from our IP in.
To summerize, They can register SQL Server externally (from home etc)
and we cannot, but we can register all other SQL Servers except
theirs.
Any Ideas?What error do you get when you try to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The error dialog reads:
==========
SQL Server registration failed because of the connection failure
displayed below. Do you wish to register anyway?
SQL Server does not exist or access denied
ConnectionOpen(Connect())
==========
I think it may be some sort of firewall problem, but they assure us
that the server has been placed outside their firewall during some
attempts to connect and our firewall is definately working OK as we
can connect to other, ISP hosted SQL Servers.
rboyd@.onlinemicrosoft.com (Rand Boyd [MSFT]) wrote in message news:<NGHck8z7DHA.1812@.cpmsftngx
a07.phx.gbl>...
> What error do you get when you try to connect?
> Rand
> This posting is provided "as is" with no warranties and confers no rights.sql
Showing posts with label remote. Show all posts
Showing posts with label remote. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
Problem performing remote queries
I am trying to a simple insert statement from a remote application
against a sql server 2005 database. To fix the problem I was having,
I had to grant the Login I was using the role of sysadmin. However I
don't want this user to have that kind of control, what would be the
best role to allow the user full access(including remoting) to only
one particular database?rhaazy pisze:
First, tell us what the problem was (provide an error message or other
details).
Permissions are assigned to logins/users and there is no difference
between remote and local scenario.
Since you are on SQL 2005, I would suggest that you use CREATE LOGIN
and CREATE USER rather than sp_adduser and sp_addlogin.
against a sql server 2005 database. To fix the problem I was having,
I had to grant the Login I was using the role of sysadmin. However I
don't want this user to have that kind of control, what would be the
best role to allow the user full access(including remoting) to only
one particular database?rhaazy pisze:
Quote:
Originally Posted by
I am trying to a simple insert statement from a remote application
against a sql server 2005 database. To fix the problem I was having,
I had to grant the Login I was using the role of sysadmin. However I
don't want this user to have that kind of control, what would be the
best role to allow the user full access(including remoting) to only
one particular database?
First, tell us what the problem was (provide an error message or other
details).
You can configure detailed permissions in SQL Server 2005, granting
sysadmin server role is far too much. Grant the login only enumerated
permissions that are essential to perform certain tasks (simple insert
in your case). Here is an T-SQL statement to do it:
GRANT INSERT ON your_table TO some_user;
Of course there are alternate solutions - e.g. database role
(db_datawriter), but try that one I mentioned above.
--
Best regards,
Marcin Guzowski
http://guzowski.info|||The error message is simply that my user didn't have permission to
execute the statement.
I would like to be able to grant the user insert, update, delete,
select on all tables in a particular database.(remotely or local, both
situations are possible)
How would I do this?|||rhaazy pisze:
Quote:
Originally Posted by
The error message is simply that my user didn't have permission to
execute the statement.
>
I would like to be able to grant the user insert, update, delete,
select on all tables in a particular database.(remotely or local, both
situations are possible)
>
How would I do this?
Permissions are assigned to logins/users and there is no difference
between remote and local scenario.
If you want all DML operations granted on all tables in particular
database, simply grant two database roles to your database user:
USE your_database;
GO
EXEC sp_addrolemember N'db_datareader', N'database_user';
GO
EXEC sp_addrolemember N'db_datawriter', N'database_user';
GO
--
Best regards,
Marcin Guzowski
http://guzowski.info|||As it turns out the db_owner is a more likely canidate for the level
of power I wish to give the user.
So what I need to do is add to my database install script, after I add
the user to the database, i need to grant Database Role Membership
(db_owner) for the database ClientScan for the user CSAdmin
exec sp_addlogin 'CSAdmin', 'pwd'
USE ClientScan
exec sp_adduser 'CSAdmin'
exec sp_addrolemember db_owner, CSAdmin
If there is anything wrong with my syntax please correct it.|||rhaazy (rhaazy@.gmail.com) writes:
Quote:
Originally Posted by
As it turns out the db_owner is a more likely canidate for the level
of power I wish to give the user.
>
So what I need to do is add to my database install script, after I add
the user to the database, i need to grant Database Role Membership
(db_owner) for the database ClientScan for the user CSAdmin
>
exec sp_addlogin 'CSAdmin', 'pwd'
>
USE ClientScan
exec sp_adduser 'CSAdmin'
>
exec sp_addrolemember db_owner, CSAdmin
>
If there is anything wrong with my syntax please correct it.
Since you are on SQL 2005, I would suggest that you use CREATE LOGIN
and CREATE USER rather than sp_adduser and sp_addlogin.
Note that there is a difference between CREATE USER and sp_adduser: the
latter will create a schema called CSAdmin and make that the default
schema for CSAdmin. If you only use CREATE USER, CSAdmin's default schema
will be dbo, and no schema CSAdmin will be created.
--
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.mspxsql
Problem passing parameter into remote stored proc
I'm having a problem passing a parameter value into a stored procedure
that I am running on a remote (linked) server, and am receiving a DTC
error because of it.
I have a stored procedure that brings in a variable (@.CustID int). I
later pass that parameter to another stored procedure. The code looks
like this...
EXEC LinkedServer.dbname.dbo.spname @.CustID
When I run that, I get this error...
Server: Msg 7391, Level 16, State 1, Procedure spname, Line 394
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
However, if I hard-code the parameter, it works:
EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.
I can even do this:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
But if I accept the variable as an input parameter to my stored
procedure, I get the error listed above.
Any ideas?
Thanks in advance for your help...
Zev Steinhardtzev_steinhardt
what happen if you assign the parameter to a variable?
DECLARE @.var int
SET @.var = @.CustID
EXEC LinkedServer.dbname.dbo.spname @.var
...
AMB
"zev_steinhardt" wrote:
> I'm having a problem passing a parameter value into a stored procedure
> that I am running on a remote (linked) server, and am receiving a DTC
> error because of it.
> I have a stored procedure that brings in a variable (@.CustID int). I
> later pass that parameter to another stored procedure. The code looks
> like this...
> EXEC LinkedServer.dbname.dbo.spname @.CustID
> When I run that, I get this error...
> Server: Msg 7391, Level 16, State 1, Procedure spname, Line 394
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> However, if I hard-code the parameter, it works:
> EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.
> I can even do this:
> DECLARE @.var int
> SET @.var = 1234
> EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
> But if I accept the variable as an input parameter to my stored
> procedure, I get the error listed above.
> Any ideas?
> Thanks in advance for your help...
> Zev Steinhardt
>|||Thanks for the reply, Alejandro.
I tried that. It didn't work.
I even tried to trick it into thinking that it's another variable
altogether. I put the variable into a temp table, declared a new
variable, populated it with the value from the temp table and passed it
in. That didn't work either.
Zev Steinhardt|||zev_steinhardt,
Are you executing the remote sp inside a transaction?
AMB
"zev_steinhardt" wrote:
> Thanks for the reply, Alejandro.
> I tried that. It didn't work.
> I even tried to trick it into thinking that it's another variable
> altogether. I put the variable into a temp table, declared a new
> variable, populated it with the value from the temp table and passed it
> in. That didn't work either.
> Zev Steinhardt
>|||Yes. The remote sp is within a transaction.
Zev|||zev_steinhardt,
you are using a distributed one, correct?
begin distributed transaction
exec ...
AMB
"zev_steinhardt" wrote:
> Yes. The remote sp is within a transaction.
> Zev
>|||Alejandro...
Yes, it is a distributed transaction... and I have XACT_ABORT on
Zev|||zev_steinhardt,
When you execute the remote sp using:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
then you are not executing it using a distributed transaction, that is why
you do not get the error.
See if this helps.
You receive error 7391 when you run a distributed transaction against a
linked server
http://support.microsoft.com/kb/329332/en-us
AMB
"zev_steinhardt" wrote:
> Alejandro...
> Yes, it is a distributed transaction... and I have XACT_ABORT on
> Zev
>
that I am running on a remote (linked) server, and am receiving a DTC
error because of it.
I have a stored procedure that brings in a variable (@.CustID int). I
later pass that parameter to another stored procedure. The code looks
like this...
EXEC LinkedServer.dbname.dbo.spname @.CustID
When I run that, I get this error...
Server: Msg 7391, Level 16, State 1, Procedure spname, Line 394
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
However, if I hard-code the parameter, it works:
EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.
I can even do this:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
But if I accept the variable as an input parameter to my stored
procedure, I get the error listed above.
Any ideas?
Thanks in advance for your help...
Zev Steinhardtzev_steinhardt
what happen if you assign the parameter to a variable?
DECLARE @.var int
SET @.var = @.CustID
EXEC LinkedServer.dbname.dbo.spname @.var
...
AMB
"zev_steinhardt" wrote:
> I'm having a problem passing a parameter value into a stored procedure
> that I am running on a remote (linked) server, and am receiving a DTC
> error because of it.
> I have a stored procedure that brings in a variable (@.CustID int). I
> later pass that parameter to another stored procedure. The code looks
> like this...
> EXEC LinkedServer.dbname.dbo.spname @.CustID
> When I run that, I get this error...
> Server: Msg 7391, Level 16, State 1, Procedure spname, Line 394
> The operation could not be performed because the OLE DB provider
> 'SQLOLEDB' was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
> ITransactionJoin::JoinTransaction returned 0x8004d00a].
> [OLE/DB provider returned message: New transaction cannot enlist in the
> specified transaction coordinator. ]
> However, if I hard-code the parameter, it works:
> EXEC LinkedServer.dbname.dbo.spname 1234 -- this works.
> I can even do this:
> DECLARE @.var int
> SET @.var = 1234
> EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
> But if I accept the variable as an input parameter to my stored
> procedure, I get the error listed above.
> Any ideas?
> Thanks in advance for your help...
> Zev Steinhardt
>|||Thanks for the reply, Alejandro.
I tried that. It didn't work.
I even tried to trick it into thinking that it's another variable
altogether. I put the variable into a temp table, declared a new
variable, populated it with the value from the temp table and passed it
in. That didn't work either.
Zev Steinhardt|||zev_steinhardt,
Are you executing the remote sp inside a transaction?
AMB
"zev_steinhardt" wrote:
> Thanks for the reply, Alejandro.
> I tried that. It didn't work.
> I even tried to trick it into thinking that it's another variable
> altogether. I put the variable into a temp table, declared a new
> variable, populated it with the value from the temp table and passed it
> in. That didn't work either.
> Zev Steinhardt
>|||Yes. The remote sp is within a transaction.
Zev|||zev_steinhardt,
you are using a distributed one, correct?
begin distributed transaction
exec ...
AMB
"zev_steinhardt" wrote:
> Yes. The remote sp is within a transaction.
> Zev
>|||Alejandro...
Yes, it is a distributed transaction... and I have XACT_ABORT on
Zev|||zev_steinhardt,
When you execute the remote sp using:
DECLARE @.var int
SET @.var = 1234
EXEC LinkedServer.dbname.dbo.spname @.var -- this works too.
then you are not executing it using a distributed transaction, that is why
you do not get the error.
See if this helps.
You receive error 7391 when you run a distributed transaction against a
linked server
http://support.microsoft.com/kb/329332/en-us
AMB
"zev_steinhardt" wrote:
> Alejandro...
> Yes, it is a distributed transaction... and I have XACT_ABORT on
> Zev
>
Problem opening diagrams in enterprise manager
Hi,
since 2 days I cannot open any database diagram on any sql server using my local pc. Using a remote desktopn session, I can use the enterprise manager installed on the servers to open the disgrams, but using my local instance I simply get an empty white w
indow - that's all. There's no error message. from my pc it seems the diagrams are empty. Also I get no context menu from that empty window. I guess something with the MMC on my pc is wrong - but what can I do? I've already reinstalled the sql server clie
nt tools on my pc and I've also tried to install a development edition with SP3 - no success.
Sven
hi Sven, Bill,
"Sven" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:A59FC9F1-35DD-488D-9273-1A4AE8109E0A@.microsoft.com...
> Hi,
> since 2 days I cannot open any database diagram on any sql server using my
local pc. Using a remote desktopn session, I can use the enterprise manager
installed on the servers to open the disgrams, but using my local instance I
simply get an empty white window - that's all. There's no error message.
from my pc it seems the diagrams are empty. Also I get no context menu from
that empty window. I guess something with the MMC on my pc is wrong - but
what can I do? I've already reinstalled the sql server client tools on my pc
and I've also tried to install a development edition with SP3 - no success.
> Sven
please report these issues, if you think they are related to VB6 sp6 to
Microsoft VB MVP Randy Birch in the microsoft.public.vb.bugs newsgroup ,
thread "Please Report SP6 bugs! (was [BUG] Crash di vb.exe)" dated
04/10/2004
(http://tinyurl.com/23w8s)... he is collecting a bug list to be presented to
VB dev team in order to prepare a hotfix post service pack 6...
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
since 2 days I cannot open any database diagram on any sql server using my local pc. Using a remote desktopn session, I can use the enterprise manager installed on the servers to open the disgrams, but using my local instance I simply get an empty white w
indow - that's all. There's no error message. from my pc it seems the diagrams are empty. Also I get no context menu from that empty window. I guess something with the MMC on my pc is wrong - but what can I do? I've already reinstalled the sql server clie
nt tools on my pc and I've also tried to install a development edition with SP3 - no success.
Sven
hi Sven, Bill,
"Sven" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:A59FC9F1-35DD-488D-9273-1A4AE8109E0A@.microsoft.com...
> Hi,
> since 2 days I cannot open any database diagram on any sql server using my
local pc. Using a remote desktopn session, I can use the enterprise manager
installed on the servers to open the disgrams, but using my local instance I
simply get an empty white window - that's all. There's no error message.
from my pc it seems the diagrams are empty. Also I get no context menu from
that empty window. I guess something with the MMC on my pc is wrong - but
what can I do? I've already reinstalled the sql server client tools on my pc
and I've also tried to install a development edition with SP3 - no success.
> Sven
please report these issues, if you think they are related to VB6 sp6 to
Microsoft VB MVP Randy Birch in the microsoft.public.vb.bugs newsgroup ,
thread "Please Report SP6 bugs! (was [BUG] Crash di vb.exe)" dated
04/10/2004
(http://tinyurl.com/23w8s)... he is collecting a bug list to be presented to
VB dev team in order to prepare a hotfix post service pack 6...
thank you
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Tuesday, March 20, 2012
Problem on getting resultset from remote stored procedure
Hi!
I've a problem on getting resultset from remote stored procedure from the local server.
The stored procedure on the remote server:
create procedure sp_test
begin
create table #temp
( field_a char(10,
field_b char(15) )
insert into #temp
select * from abc
select * from #temp
end
on the local server, the command is
create table #temp
( field_a char(10,
field_b char(15) )
insert into #temp
select * from openquery(LINKEDSERVER,'LINKEDSERVER.DBNAME.dbo.sp_test')
/*****/
the server prompt that table #temp not found
if I simply run LINKEDSERVER.DBNAME.dbo.sp_test
it works.
Actually I need to prepare a stored procedure on the local server to execute sp_temp on remote server.
I tried to use other method to do the task, such as
insert into #temp
exec LINKEDSERVER.DBNAME.dbo.sp_test
but the server prompt that MSDTC service not found in the server.
as i'm not quite familer on MSDTC service, I tried to use other method.
for doing this task, is it a must to start the MSDTC service.
if I start it, what effect will be (performance, loading etc)
please help!! thanks!!
Yeah, for the most part just start DTC and let it go its merry way. It will not have a negative impact on performance. Can somebody check me on this please?|||
I've a problem on getting resultset from remote stored procedure from the local server.
The stored procedure on the remote server:
create procedure sp_test
begin
create table #temp
( field_a char(10,
field_b char(15) )
insert into #temp
select * from abc
select * from #temp
end
on the local server, the command is
create table #temp
( field_a char(10,
field_b char(15) )
insert into #temp
select * from openquery(LINKEDSERVER,'LINKEDSERVER.DBNAME.dbo.sp_test')
/*****/
the server prompt that table #temp not found
if I simply run LINKEDSERVER.DBNAME.dbo.sp_test
it works.
Actually I need to prepare a stored procedure on the local server to execute sp_temp on remote server.
I tried to use other method to do the task, such as
insert into #temp
exec LINKEDSERVER.DBNAME.dbo.sp_test
but the server prompt that MSDTC service not found in the server.
as i'm not quite familer on MSDTC service, I tried to use other method.
for doing this task, is it a must to start the MSDTC service.
if I start it, what effect will be (performance, loading etc)
please help!! thanks!!
Yeah, for the most part just start DTC and let it go its merry way. It will not have a negative impact on performance. Can somebody check me on this please?|||
Yes, I just afraid the loading and performance would be affected after starting MSDTC.
Saturday, February 25, 2012
Problem installing SQL 2000 on SBS2003
Hi,
I am getting an error right after it asks if the server is local or remote.
It says that a previous program installation created pending file operations
on the installation machine. And that i must restart the computer. I've
restarted...doesn't make a difference. The server has MSDE for sbs
installed. Not sure what to do.
Thanks,
Andrew OuellettePlease try the following:
1. Run Registry Editor
2. Go to HKEY_LOCAL_MACHINE folder
3. Go to SYSTEM folder
4. Go to CurrentControlSet folder
5. Go to Control folder
6. Go to Session Manager folder
7. Double-click on the PendingFileRenameOperations key
and remove everything from the Value Data section
This ought to do it, I hope!
Thanks,
Dee
>--Original Message--
>Hi,
>I am getting an error right after it asks if the server
is local or remote.
>It says that a previous program installation created
pending file operations
>on the installation machine. And that i must restart the
computer. I've
>restarted...doesn't make a difference. The server has
MSDE for sbs
>installed. Not sure what to do.
>Thanks,
>Andrew Ouellette
>.
>
I am getting an error right after it asks if the server is local or remote.
It says that a previous program installation created pending file operations
on the installation machine. And that i must restart the computer. I've
restarted...doesn't make a difference. The server has MSDE for sbs
installed. Not sure what to do.
Thanks,
Andrew OuellettePlease try the following:
1. Run Registry Editor
2. Go to HKEY_LOCAL_MACHINE folder
3. Go to SYSTEM folder
4. Go to CurrentControlSet folder
5. Go to Control folder
6. Go to Session Manager folder
7. Double-click on the PendingFileRenameOperations key
and remove everything from the Value Data section
This ought to do it, I hope!
Thanks,
Dee
>--Original Message--
>Hi,
>I am getting an error right after it asks if the server
is local or remote.
>It says that a previous program installation created
pending file operations
>on the installation machine. And that i must restart the
computer. I've
>restarted...doesn't make a difference. The server has
MSDE for sbs
>installed. Not sure what to do.
>Thanks,
>Andrew Ouellette
>.
>
Monday, February 20, 2012
Problem inserting data into remote server
Hello,
I'm working on an old database that has been converted to SQL server 2000 and I am having a problem executing the DML statement below. It looks like the problem is related to column file. File seems to be a reserved keyword for sql server. How do I get around this? I already tried brackets around the column name etc.
INSERT INTO [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE ([source], [session_id],session_index, [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time])
SELECT [source], [session_id],
[session_index], [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time] FROM T_LOG_STORAGE WHERE NOT EXISTS ( SELECT session_id FROM [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE derived WHERE T_LOG_STORAGE.source = derived.source AND T_LOG_STORAGE.session_id = derived.session_id AND T_LOG_STORAGE.session_index = derived.session_index) AND T_LOG_STORAGE.source = @.dSource
(1 row(s) affected)
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'file'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].Originally posted by hijinks
Hello,
I'm working on an old database that has been converted to SQL server 2000 and I am having a problem executing the DML statement below. It looks like the problem is related to column file. File seems to be a reserved keyword for sql server. How do I get around this? I already tried brackets around the column name etc.
INSERT INTO [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE ([source], [session_id],session_index, [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time])
SELECT [source], [session_id],
[session_index], [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time] FROM T_LOG_STORAGE WHERE NOT EXISTS ( SELECT session_id FROM [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE derived WHERE T_LOG_STORAGE.source = derived.source AND T_LOG_STORAGE.session_id = derived.session_id AND T_LOG_STORAGE.session_index = derived.session_index) AND T_LOG_STORAGE.source = @.dSource
(1 row(s) affected)
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'file'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].
What is it: (1 row(s) affected) - looks like insert was done? Do you have triggers on remote table?
Try to do simple insert and check is it work at all.|||Actually the (1 row(s) affected) is a by product of me printing the INSERT statement, so the insert does not happen.|||Actually the (1 row(s) affected) is a by product of me printing the INSERT statement, so the insert does not happen.
I'm working on an old database that has been converted to SQL server 2000 and I am having a problem executing the DML statement below. It looks like the problem is related to column file. File seems to be a reserved keyword for sql server. How do I get around this? I already tried brackets around the column name etc.
INSERT INTO [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE ([source], [session_id],session_index, [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time])
SELECT [source], [session_id],
[session_index], [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time] FROM T_LOG_STORAGE WHERE NOT EXISTS ( SELECT session_id FROM [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE derived WHERE T_LOG_STORAGE.source = derived.source AND T_LOG_STORAGE.session_id = derived.session_id AND T_LOG_STORAGE.session_index = derived.session_index) AND T_LOG_STORAGE.source = @.dSource
(1 row(s) affected)
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'file'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].Originally posted by hijinks
Hello,
I'm working on an old database that has been converted to SQL server 2000 and I am having a problem executing the DML statement below. It looks like the problem is related to column file. File seems to be a reserved keyword for sql server. How do I get around this? I already tried brackets around the column name etc.
INSERT INTO [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE ([source], [session_id],session_index, [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time])
SELECT [source], [session_id],
[session_index], [header_type],
[app_type],
[access],
[start] ,
[stop],
[computer],
[file],
[insert_time] FROM T_LOG_STORAGE WHERE NOT EXISTS ( SELECT session_id FROM [NW_Test_MM].[NW_35].[dbo].T_LOG_STORAGE derived WHERE T_LOG_STORAGE.source = derived.source AND T_LOG_STORAGE.session_id = derived.session_id AND T_LOG_STORAGE.session_index = derived.session_index) AND T_LOG_STORAGE.source = @.dSource
(1 row(s) affected)
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'file'.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80004005: ].
What is it: (1 row(s) affected) - looks like insert was done? Do you have triggers on remote table?
Try to do simple insert and check is it work at all.|||Actually the (1 row(s) affected) is a by product of me printing the INSERT statement, so the insert does not happen.|||Actually the (1 row(s) affected) is a by product of me printing the INSERT statement, so the insert does not happen.
Subscribe to:
Posts (Atom)