Hi,
Can anyone tell me the best way to configure sql server to allow
publication of data from a pre to a production environment.
I'm thinking along the lines of a typical content mgmt app where users
can work in pre and publish to prod when ready, how would this normally
be configured?
Thanks
Kieran
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Probably by creating a publication that filters the articles by a column
which has a status value in it indicating whether the content is ready for
publication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kieran" <anonymous@.devdex.com> wrote in message
news:OP3KYH6qEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can anyone tell me the best way to configure sql server to allow
> publication of data from a pre to a production environment.
> I'm thinking along the lines of a typical content mgmt app where users
> can work in pre and publish to prod when ready, how would this normally
> be configured?
> Thanks
> Kieran
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Thanks Hilary, is a replication something that can be set up on the sql
server?
Thanks
Kieran
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||it sure is, go to tools, replication and go from there.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kieran" <anonymous@.devdex.com> wrote in message
news:%23kQdFl6qEHA.2724@.TK2MSFTNGP14.phx.gbl...
> Thanks Hilary, is a replication something that can be set up on the sql
> server?
> Thanks
> Kieran
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
sql
Showing posts with label environment. Show all posts
Showing posts with label environment. Show all posts
Tuesday, March 20, 2012
Thursday, March 8, 2012
A simple INSERT Problem
Dev Tool: VB6
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(??)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
Rick
Try stepping through the debugger and check to see if the connection is open.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(??)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick
|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
Rick
|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(??)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
Rick
Try stepping through the debugger and check to see if the connection is open.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(??)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick
|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
Rick
|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
A simple INSERT Problem
Dev Tool: VB6
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments
.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(??)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
RickTry stepping through the debugger and check to see if the connection is open
.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT stamen
ts.
> Having the same connection and user id. When I execute the stored procedur
e
> in the Query Analyzer console I have no problem, but when I execute it in
a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(??
)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" f
rom your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from t
he INSERT, but I don't
recommend that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments
.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(??)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
RickTry stepping through the debugger and check to see if the connection is open
.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT stamen
ts.
> Having the same connection and user id. When I execute the stored procedur
e
> in the Query Analyzer console I have no problem, but when I execute it in
a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(??
)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" f
rom your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from t
he INSERT, but I don't
recommend that.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
A simple INSERT Problem
Dev Tool: VB6
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(¡?)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
--
RickTry stepping through the debugger and check to see if the connection is open.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(¡?)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
Server: SQL Server 2000.
Environment: Windows 2000/Windows XP/Windows 2000 Server
I have changed a user connectivity from WINDOWS NT trusted connection to
SQL Server Authentication.
I have granted the same permissions to the new user.
The problem is as follows:
The new user cannot execute stored procedures which contains INSERT staments.
Having the same connection and user id. When I execute the stored procedure
in the Query Analyzer console I have no problem, but when I execute it in a
Visual Basic 6.0 Application, using ADO, I get the following message:
"Operation is not Allowed when the Object is closed."
Note: I created the table, so I'm the owner, I should have no problems(¡?)
What are the permission differences between Windows NT Trusted Connection
and SQL Server Authentication?
--
RickTry stepping through the debugger and check to see if the connection is open.
"Rick" wrote:
> Dev Tool: VB6
> Server: SQL Server 2000.
> Environment: Windows 2000/Windows XP/Windows 2000 Server
> I have changed a user connectivity from WINDOWS NT trusted connection to
> SQL Server Authentication.
> I have granted the same permissions to the new user.
> The problem is as follows:
> The new user cannot execute stored procedures which contains INSERT staments.
> Having the same connection and user id. When I execute the stored procedure
> in the Query Analyzer console I have no problem, but when I execute it in a
> Visual Basic 6.0 Application, using ADO, I get the following message:
> "Operation is not Allowed when the Object is closed."
> Note: I created the table, so I'm the owner, I should have no problems(¡?)
> What are the permission differences between Windows NT Trusted Connection
> and SQL Server Authentication?
>
> --
> Rick|||Actually the stored proc is excuted and INSERT statement is done.
the problem is the message:
"Operation is not Allowed when the Object is closed."
This is the store proc:
CREATE PROCEDURE PROC_TEST
AS
INSERT INTO TEST
(TEST, DATE )
VALUES
('Value', GETDATE())
select * from TEST
RETURN
This is the call in Visual Basic 6.0:
strSQL = "EXECUTE PROC_TEST "
strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
ctlADO.CommandType = adCmdText
ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
ctlADO.CursorLocation = adUseClient
ctlADO.ConnectionString = strCnn
ctlADO.RecordSource = strSQL
ctlADO.Refresh
Notice that ctlADO is an ADODC control.
--
Rick|||Add SET NOCOUNT ON in the beginning of your proc code. The "rows affected" from your INSERT message
is treaded as a recordset by classic ADO.
You can also do a .NextRecordset to navigate past the dummy recordset from the INSERT, but I don't
recommend that.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick|||Using ADODC control (and Data Environment) is a very bad choice. Very few
exerienced VB programmer uses it, although most VB books for newbies have an
example to show how easy in VB to deal with database. Avoid it whenever
possible, especially in your situation of simply sending ADODB Command to
SQL Server to execute SPs.
It is very simple do use an ADO Command object the execute SPs in SQL
Server.
Dim cn AS ADODB.Connection
Dim cmd AS ADODB.Command
Set cn=New ADODB.Connection
cn.Open myConnectionString
Set cmd=New ADODB.Command
cmd.CommandType=adStoredProc
cmd.CommandText="theStoredProcedureName"
Set cmd.ActiveConnection=cn
''Add ADODB Parameters here if the SP expects parameter
cmd.Execute 'You are done
cn.Close 'Close the connection
Note, you need add some error handling code, of course.
"Rick" <Rick@.discussions.microsoft.com> wrote in message
news:31547951-D174-49FB-B21A-50F44E65DB61@.microsoft.com...
> Actually the stored proc is excuted and INSERT statement is done.
> the problem is the message:
> "Operation is not Allowed when the Object is closed."
> This is the store proc:
> CREATE PROCEDURE PROC_TEST
> AS
> INSERT INTO TEST
> (TEST, DATE )
> VALUES
> ('Value', GETDATE())
> select * from TEST
> RETURN
> This is the call in Visual Basic 6.0:
> strSQL = "EXECUTE PROC_TEST "
> strCnn = "Provider=SQLOLEDB;Persist Security Info=False;Initial
> Catalog=MyTable;Data Source=MyServe;User Id=MyName;Password=MyPassword;"
> ctlADO.CommandType = adCmdText
> ctlADO.ConnectionTimeout = cnConexionADO_p.ConnectionTimeout
> ctlADO.CommandTimeout = cnConexionADO_p.CommandTimeout
> ctlADO.CursorLocation = adUseClient
> ctlADO.ConnectionString = strCnn
> ctlADO.RecordSource = strSQL
> ctlADO.Refresh
>
> Notice that ctlADO is an ADODC control.
> --
> Rick
Tuesday, March 6, 2012
a server hardware/environment question..
my network admin gives me two servers to set up for sql2k.
one will be for dev/testing qa, and one will be for production (mostly
reporting , not business operation type of stuff)
one server is good (raid 5), and the other is very good (raid 10).
the type of development will be in VB and data warehousing areas with
about 5 developers. the audience for the production reports can range
from a few people to a couple of hundreds of employees.
what would make the most sense in terms of server allocation?
should i give the best server to dev/testing? or to production (mainly
for reports)?
thank you.I tend to always give production the edge when building a dev server
(if I ca't have identical boxes); it will be more likely that
performance issues will show up on the weakest server, and I would
prefer to deal with them during development rather than production.
What would make this question interesting would be the processor and
memory specs as opposed to just the hard drive allocations...
HTH,
Stu|||On the development server when designing queries and report design
templates, you will probably want to work with more manageable subsets of
data, so the lessor equipped server should suffice. My development server is
typically just my PC with a local intall of SQL Server Developer edition.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1121891806.861200.159450@.g49g2000cwa.googlegroups.com...
> my network admin gives me two servers to set up for sql2k.
> one will be for dev/testing qa, and one will be for production (mostly
> reporting , not business operation type of stuff)
> one server is good (raid 5), and the other is very good (raid 10).
> the type of development will be in VB and data warehousing areas with
> about 5 developers. the audience for the production reports can range
> from a few people to a couple of hundreds of employees.
> what would make the most sense in terms of server allocation?
> should i give the best server to dev/testing? or to production (mainly
> for reports)?
> thank you.
>|||> What would make this question interesting would be the processor and
> memory specs as opposed to just the hard drive allocations...
Yes, definitely if you can, have more than one processor in dev if your
production machines have > 1.
(Often problems due to parallel processing / plans can be missed because the
first time the queries have a chance to utilize that hardware is in
production.)
Also, try to have the same amount of memory, e.g. if production uses AWE
then you should have the same settings in dev (for the same reasons as
above).
one will be for dev/testing qa, and one will be for production (mostly
reporting , not business operation type of stuff)
one server is good (raid 5), and the other is very good (raid 10).
the type of development will be in VB and data warehousing areas with
about 5 developers. the audience for the production reports can range
from a few people to a couple of hundreds of employees.
what would make the most sense in terms of server allocation?
should i give the best server to dev/testing? or to production (mainly
for reports)?
thank you.I tend to always give production the edge when building a dev server
(if I ca't have identical boxes); it will be more likely that
performance issues will show up on the weakest server, and I would
prefer to deal with them during development rather than production.
What would make this question interesting would be the processor and
memory specs as opposed to just the hard drive allocations...
HTH,
Stu|||On the development server when designing queries and report design
templates, you will probably want to work with more manageable subsets of
data, so the lessor equipped server should suffice. My development server is
typically just my PC with a local intall of SQL Server Developer edition.
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1121891806.861200.159450@.g49g2000cwa.googlegroups.com...
> my network admin gives me two servers to set up for sql2k.
> one will be for dev/testing qa, and one will be for production (mostly
> reporting , not business operation type of stuff)
> one server is good (raid 5), and the other is very good (raid 10).
> the type of development will be in VB and data warehousing areas with
> about 5 developers. the audience for the production reports can range
> from a few people to a couple of hundreds of employees.
> what would make the most sense in terms of server allocation?
> should i give the best server to dev/testing? or to production (mainly
> for reports)?
> thank you.
>|||> What would make this question interesting would be the processor and
> memory specs as opposed to just the hard drive allocations...
Yes, definitely if you can, have more than one processor in dev if your
production machines have > 1.
(Often problems due to parallel processing / plans can be missed because the
first time the queries have a chance to utilize that hardware is in
production.)
Also, try to have the same amount of memory, e.g. if production uses AWE
then you should have the same settings in dev (for the same reasons as
above).
Subscribe to:
Posts (Atom)