about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
>>From sys.sysdatabases
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

abort insertcommand

hey.

I have a formview - inertitemtemplate. In here I have a button with CommandName="Insert". In code behind under FormView1.ItemInserting I want to be able to abort the inserting in some cases. Is this possible?

Simple. In the event, simply do: e.Cancel = true.

a way to export data from table to flat file

Hi there!
I know bulk insert for importing data into tables and bcp for both
importing, exporting data between tables..from flat files...ok..
But is there any way to export data using a SQL statement and a format
file (need to export in kind of csv) just like bcp ? a kind of *bulk
output*...:)
any idea'
thanks a lot
++
Vincelook at DTS...|||Vince
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=D:\FolderName;','select * from Text1.txt')
"Vince .>" <vincent@.<remove> wrote in message
news:ro7751hjpvc9l0lks58pds2dda1prok8lu@.
4ax.com...
> Hi there!
> I know bulk insert for importing data into tables and bcp for both
> importing, exporting data between tables..from flat files...ok..
> But is there any way to export data using a SQL statement and a format
> file (need to export in kind of csv) just like bcp ? a kind of *bulk
> output*...:)
> any idea'
> thanks a lot
> ++
> Vince
>

A trigger that fire every insert event?

This trigger fired once ONLY during an insert event. My web form inserted 4 news records so it should fire the trigger 4 times correct?

CREATE TRIGGER trInsertImplementationTaskP1
on dbo.ImplementationTasks
FOR INSERT
AS
DECLARE @.ITIDint

SELECT @.ITID = (SELECT ITid
from inserted)

EXEC TrigSendNewIMAlertP1 @.ITIDIf you insert 4 rows as part of a single insert statement, (INSERT INTO foo(id) SELECT ID FROM OtherTable) then the trigger will only be fired once. If you run 4 seperate insert statements, then the trigger will be called 4 times.|||I am using a datagrid with a checkbox per row... for example if the user check 4 out of 10 checkboxes then press submit, it will insert 4 new records. This should fire the trigger 4 times during the insert event correct?|||That depends how you implement the code. Use SQL Profiler to see how the commands are being sent, if you cannot figure out how it is implemented.|||

Hi,

I'm in a similar situation. I have to table and I insert records from TABLE_1 to TABLE_2 using INSERT TABLE_2 (ld_ID) FROM SELECT ID FROM TABLE_1 WHERE some criteria . I have a trigger that fires when a record is inserted to TABLE_2. I need this trigger to fire for each record inserted to the TABLE_2. How can i get this to work.

Many thanks.

-VJ

A strange problem with updatable partitioned view.

Hi all,
I am working on a distributed database. I defined linked
servers, partitioned views etc. I can delete/insert/update
data from the view. Now the problem is if I add more ID
ranges to the partitioning column in the check, sometimes
it worked or sometimes it didn't. See the following sample
code:
-- Create linked server SERVER0,SERVER1 on two SQL
servers.
-- SERVER0 one one machine
exec sp_addlinkedserver 'SERVER0', '',
N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
@.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
@.rmtpassword = ''
exec sp_serveroption @.Server='SERVER0', @.optname
='RPC', @.optvalue='TRUE'
exec sp_serveroption @.Server='SERVER0', @.optname
='RPC OUT', @.optvalue='TRUE'
-- SERVER1 on another machine
exec sp_addlinkedserver 'SERVER1', '',
N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
@.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
@.rmtpassword = ''
exec sp_serveroption @.Server='SERVER1', @.optname
='RPC', @.optvalue='TRUE'
exec sp_serveroption @.Server='SERVER1', @.optname
='RPC OUT', @.optvalue='TRUE'
-- Create database Test_DB on each server.
-- ON SERVER1:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TblZZ_Test]
GO
CREATE TABLE [dbo].[TblZZ_Test] (
[ObjectID] [int] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[Value] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
(
[ObjectID],
[StartTime]
) ON [PRIMARY]
GO
-- ObjectID will be the partitioning column
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
[ObjectID] <= 100)
GO
-- ON Server1:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[TblZZ_Test]
GO
CREATE TABLE [dbo].[TblZZ_Test] (
[ObjectID] [int] NOT NULL ,
[StartTime] [datetime] NOT NULL ,
[Value] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
(
[ObjectID],
[StartTime]
) ON [PRIMARY]
GO
-- ObjectID will be the partitioning column
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 )
GO
-- ON SERVER0: create federated view
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('vwTest'))
DROP view vwTest
GO
CREATE view vwTest (ObjectID,StartTime,Value)
AS
SELECT ObjectID,StartTime,Value FROM tblZZ_Test
UNION ALL
SELECT ObjectID,StartTime,Value
FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
GO
--ON SERVER1: create federated view
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('vwTest'))
DROP view vwTest
GO
CREATE view vwTest (ObjectID,StartTime,Value)
AS
SELECT ObjectID,StartTime,Value FROM tblZZ_Test
UNION ALL
SELECT ObjectID,StartTime,Value
FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (10,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (110,'2003-
01-01',1)
It succeeds
-- ON both server, drop the checks
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0, add more ObjectID ranges
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
<= 300 )
GO
-- On server1, add more ObjectID ranges
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
[ObjectID] <= 400 )
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (11,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (111,'2003-
01-01',1)
It succeeds
-- ON both server
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
<= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
GO
-- On Server1:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
[ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID] >= -
600)
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (13,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (113,'2003-
01-01',1)
It succeeds
-- On any server,
Delete vwtest
-- ON both server
IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
('CK_TblZZ_Test'))
ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
CK_TblZZ_Test
GO
-- ON server0:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074 or
[ObjectID] between 40075 and 50074)
GO
-- On server1:
ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074 OR
[ObjectID] BETWEEN 50075 and 60074)
GO
-- ON any server run the following query:
SET ANSI_NULLS ON
set xact_ABORT ON
insert vwTest (ObjectID,StartTime,Value) VALUES (17,'2003-
01-01',1)
insert vwTest (ObjectID,StartTime,Value) VALUES (117,'2003-
01-01',1)
It will fail. the error message is "UNION ALL view vwtest
is not updatable becuase a partitioning column is not
found."
I am totally lost. Anyone knows how SQL server decides one
column is a partitioning or not. Here I used the same rule
but the result is different.
Any ideas? Thanks in advance.Peter,
did not go through your detailed post. However, I bet that you did your
modification with EM. It is known that when you do such changes in EM to
updateable partitioned view the EM does not do it right. Try use QA. If it
does not work, try recreate the view in QA.
HTH
Quentin
"Peter" <phe@.Visualplant.com> wrote in message
news:058f01c34be6$214e5990$a101280a@.phx.gbl...
> Hi all,
> I am working on a distributed database. I defined linked
> servers, partitioned views etc. I can delete/insert/update
> data from the view. Now the problem is if I add more ID
> ranges to the partitioning column in the check, sometimes
> it worked or sometimes it didn't. See the following sample
> code:
> -- Create linked server SERVER0,SERVER1 on two SQL
> servers.
> -- SERVER0 one one machine
> exec sp_addlinkedserver 'SERVER0', '',
> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> @.rmtpassword = ''
> exec sp_serveroption @.Server='SERVER0', @.optname
> ='RPC', @.optvalue='TRUE'
> exec sp_serveroption @.Server='SERVER0', @.optname
> ='RPC OUT', @.optvalue='TRUE'
> -- SERVER1 on another machine
> exec sp_addlinkedserver 'SERVER1', '',
> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> @.rmtpassword = ''
> exec sp_serveroption @.Server='SERVER1', @.optname
> ='RPC', @.optvalue='TRUE'
> exec sp_serveroption @.Server='SERVER1', @.optname
> ='RPC OUT', @.optvalue='TRUE'
> -- Create database Test_DB on each server.
> -- ON SERVER1:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[TblZZ_Test]
> GO
> CREATE TABLE [dbo].[TblZZ_Test] (
> [ObjectID] [int] NOT NULL ,
> [StartTime] [datetime] NOT NULL ,
> [Value] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> (
> [ObjectID],
> [StartTime]
> ) ON [PRIMARY]
> GO
> -- ObjectID will be the partitioning column
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
> [ObjectID] <= 100)
> GO
> -- ON Server1:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[TblZZ_Test]
> GO
> CREATE TABLE [dbo].[TblZZ_Test] (
> [ObjectID] [int] NOT NULL ,
> [StartTime] [datetime] NOT NULL ,
> [Value] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> (
> [ObjectID],
> [StartTime]
> ) ON [PRIMARY]
> GO
> -- ObjectID will be the partitioning column
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 )
> GO
>
> -- ON SERVER0: create federated view
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('vwTest'))
> DROP view vwTest
> GO
> CREATE view vwTest (ObjectID,StartTime,Value)
> AS
> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> UNION ALL
> SELECT ObjectID,StartTime,Value
> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
> GO
> --ON SERVER1: create federated view
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('vwTest'))
> DROP view vwTest
> GO
> CREATE view vwTest (ObjectID,StartTime,Value)
> AS
> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> UNION ALL
> SELECT ObjectID,StartTime,Value
> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (10,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (110,'2003-
> 01-01',1)
> It succeeds
> -- ON both server, drop the checks
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0, add more ObjectID ranges
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
> <= 300 )
> GO
> -- On server1, add more ObjectID ranges
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> [ObjectID] <= 400 )
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (11,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (111,'2003-
> 01-01',1)
> It succeeds
> -- ON both server
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> and [ObjectID] <= 100 OR [ObjectID] >= 201 and [ObjectID]
> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
> GO
> -- On Server1:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID] >= -
> 600)
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (13,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (113,'2003-
> 01-01',1)
> It succeeds
> -- On any server,
> Delete vwtest
> -- ON both server
> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> ('CK_TblZZ_Test'))
> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> CK_TblZZ_Test
> GO
> -- ON server0:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074 or
> [ObjectID] between 40075 and 50074)
> GO
> -- On server1:
> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074 OR
> [ObjectID] BETWEEN 50075 and 60074)
> GO
> -- ON any server run the following query:
> SET ANSI_NULLS ON
> set xact_ABORT ON
> insert vwTest (ObjectID,StartTime,Value) VALUES (17,'2003-
> 01-01',1)
> insert vwTest (ObjectID,StartTime,Value) VALUES (117,'2003-
> 01-01',1)
> It will fail. the error message is "UNION ALL view vwtest
> is not updatable becuase a partitioning column is not
> found."
> I am totally lost. Anyone knows how SQL server decides one
> column is a partitioning or not. Here I used the same rule
> but the result is different.
>
> Any ideas? Thanks in advance.
>|||Thanks for your reply.
However, I didn't change the constraint from EM. What I
did is that drop the constraint for all servers, then
create the constraint for all servers from QA. It worked
in some cases. It seems if I have more ID ranges or I have
ID ranges with negative value, it will fail. I tried to
recreate the view, it didn't work too.
The code I posted is exactly what I ran in QA.
>--Original Message--
>Peter,
>did not go through your detailed post. However, I bet
that you did your
>modification with EM. It is known that when you do such
changes in EM to
>updateable partitioned view the EM does not do it right.
Try use QA. If it
>does not work, try recreate the view in QA.
>HTH
>Quentin
>"Peter" <phe@.Visualplant.com> wrote in message
>news:058f01c34be6$214e5990$a101280a@.phx.gbl...
>> Hi all,
>> I am working on a distributed database. I defined
linked
>> servers, partitioned views etc. I can
delete/insert/update
>> data from the view. Now the problem is if I add more ID
>> ranges to the partitioning column in the check,
sometimes
>> it worked or sometimes it didn't. See the following
sample
>> code:
>> -- Create linked server SERVER0,SERVER1 on two SQL
>> servers.
>> -- SERVER0 one one machine
>> exec sp_addlinkedserver 'SERVER0', '',
>> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
>> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
>> @.rmtpassword = ''
>> exec sp_serveroption @.Server='SERVER0', @.optname
>> ='RPC', @.optvalue='TRUE'
>> exec sp_serveroption @.Server='SERVER0', @.optname
>> ='RPC OUT', @.optvalue='TRUE'
>> -- SERVER1 on another machine
>> exec sp_addlinkedserver 'SERVER1', '',
>> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
>> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
>> @.rmtpassword = ''
>> exec sp_serveroption @.Server='SERVER1', @.optname
>> ='RPC', @.optvalue='TRUE'
>> exec sp_serveroption @.Server='SERVER1', @.optname
>> ='RPC OUT', @.optvalue='TRUE'
>> -- Create database Test_DB on each server.
>> -- ON SERVER1:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[TblZZ_Test]
>> GO
>> CREATE TABLE [dbo].[TblZZ_Test] (
>> [ObjectID] [int] NOT NULL ,
>> [StartTime] [datetime] NOT NULL ,
>> [Value] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> (
>> [ObjectID],
>> [StartTime]
>> ) ON [PRIMARY]
>> GO
>> -- ObjectID will be the partitioning column
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
>> [ObjectID] <= 100)
>> GO
>> -- ON Server1:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
>> N'IsUserTable') = 1)
>> drop table [dbo].[TblZZ_Test]
>> GO
>> CREATE TABLE [dbo].[TblZZ_Test] (
>> [ObjectID] [int] NOT NULL ,
>> [StartTime] [datetime] NOT NULL ,
>> [Value] [int] NOT NULL
>> ) ON [PRIMARY]
>> GO
>> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> (
>> [ObjectID],
>> [StartTime]
>> ) ON [PRIMARY]
>> GO
>> -- ObjectID will be the partitioning column
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 )
>> GO
>>
>> -- ON SERVER0: create federated view
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('vwTest'))
>> DROP view vwTest
>> GO
>> CREATE view vwTest (ObjectID,StartTime,Value)
>> AS
>> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> UNION ALL
>> SELECT ObjectID,StartTime,Value
>> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
>> GO
>> --ON SERVER1: create federated view
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('vwTest'))
>> DROP view vwTest
>> GO
>> CREATE view vwTest (ObjectID,StartTime,Value)
>> AS
>> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> UNION ALL
>> SELECT ObjectID,StartTime,Value
>> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(10,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(110,'2003-
>> 01-01',1)
>> It succeeds
>> -- ON both server, drop the checks
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0, add more ObjectID ranges
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
[ObjectID]
>> <= 300 )
>> GO
>> -- On server1, add more ObjectID ranges
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> [ObjectID] <= 400 )
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(11,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(111,'2003-
>> 01-01',1)
>> It succeeds
>> -- ON both server
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
[ObjectID]
>> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
>> GO
>> -- On Server1:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID]
>= -
>> 600)
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(13,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(113,'2003-
>> 01-01',1)
>> It succeeds
>> -- On any server,
>> Delete vwtest
>> -- ON both server
>> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> ('CK_TblZZ_Test'))
>> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> CK_TblZZ_Test
>> GO
>> -- ON server0:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
or
>> [ObjectID] between 40075 and 50074)
>> GO
>> -- On server1:
>> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074
OR
>> [ObjectID] BETWEEN 50075 and 60074)
>> GO
>> -- ON any server run the following query:
>> SET ANSI_NULLS ON
>> set xact_ABORT ON
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(17,'2003-
>> 01-01',1)
>> insert vwTest (ObjectID,StartTime,Value) VALUES
(117,'2003-
>> 01-01',1)
>> It will fail. the error message is "UNION ALL view
vwtest
>> is not updatable becuase a partitioning column is not
>> found."
>> I am totally lost. Anyone knows how SQL server decides
one
>> column is a partitioning or not. Here I used the same
rule
>> but the result is different.
>>
>> Any ideas? Thanks in advance.
>
>.
>|||Peter,
Oops.
I saw you used Alter Table to add the constraint. Did you try to create the
constraint together with the table creation? Try that.
Quentin
"peter" <phe@.VisualPlant.com> wrote in message
news:0c5701c34c72$5003b9b0$a301280a@.phx.gbl...
> Thanks for your reply.
> However, I didn't change the constraint from EM. What I
> did is that drop the constraint for all servers, then
> create the constraint for all servers from QA. It worked
> in some cases. It seems if I have more ID ranges or I have
> ID ranges with negative value, it will fail. I tried to
> recreate the view, it didn't work too.
> The code I posted is exactly what I ran in QA.
>
> >--Original Message--
> >Peter,
> >
> >did not go through your detailed post. However, I bet
> that you did your
> >modification with EM. It is known that when you do such
> changes in EM to
> >updateable partitioned view the EM does not do it right.
> Try use QA. If it
> >does not work, try recreate the view in QA.
> >
> >HTH
> >
> >Quentin
> >
> >"Peter" <phe@.Visualplant.com> wrote in message
> >news:058f01c34be6$214e5990$a101280a@.phx.gbl...
> >> Hi all,
> >>
> >> I am working on a distributed database. I defined
> linked
> >> servers, partitioned views etc. I can
> delete/insert/update
> >> data from the view. Now the problem is if I add more ID
> >> ranges to the partitioning column in the check,
> sometimes
> >> it worked or sometimes it didn't. See the following
> sample
> >> code:
> >>
> >> -- Create linked server SERVER0,SERVER1 on two SQL
> >> servers.
> >> -- SERVER0 one one machine
> >> exec sp_addlinkedserver 'SERVER0', '',
> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> >> @.rmtpassword = ''
> >> exec sp_serveroption @.Server='SERVER0', @.optname
> >> ='RPC', @.optvalue='TRUE'
> >> exec sp_serveroption @.Server='SERVER0', @.optname
> >> ='RPC OUT', @.optvalue='TRUE'
> >> -- SERVER1 on another machine
> >> exec sp_addlinkedserver 'SERVER1', '',
> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser ='sa',
> >> @.rmtpassword = ''
> >> exec sp_serveroption @.Server='SERVER1', @.optname
> >> ='RPC', @.optvalue='TRUE'
> >> exec sp_serveroption @.Server='SERVER1', @.optname
> >> ='RPC OUT', @.optvalue='TRUE'
> >>
> >> -- Create database Test_DB on each server.
> >> -- ON SERVER1:
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[TblZZ_Test]
> >> GO
> >>
> >> CREATE TABLE [dbo].[TblZZ_Test] (
> >> [ObjectID] [int] NOT NULL ,
> >> [StartTime] [datetime] NOT NULL ,
> >> [Value] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> >> (
> >> [ObjectID],
> >> [StartTime]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> -- ObjectID will be the partitioning column
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
> >> [ObjectID] <= 100)
> >> GO
> >>
> >> -- ON Server1:
> >> if exists (select * from dbo.sysobjects where id => >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY(id,
> >> N'IsUserTable') = 1)
> >> drop table [dbo].[TblZZ_Test]
> >> GO
> >>
> >> CREATE TABLE [dbo].[TblZZ_Test] (
> >> [ObjectID] [int] NOT NULL ,
> >> [StartTime] [datetime] NOT NULL ,
> >> [Value] [int] NOT NULL
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
> >> (
> >> [ObjectID],
> >> [StartTime]
> >> ) ON [PRIMARY]
> >> GO
> >>
> >> -- ObjectID will be the partitioning column
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 )
> >> GO
> >>
> >>
> >> -- ON SERVER0: create federated view
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('vwTest'))
> >> DROP view vwTest
> >> GO
> >> CREATE view vwTest (ObjectID,StartTime,Value)
> >> AS
> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> >> UNION ALL
> >> SELECT ObjectID,StartTime,Value
> >> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
> >> GO
> >>
> >> --ON SERVER1: create federated view
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('vwTest'))
> >> DROP view vwTest
> >> GO
> >> CREATE view vwTest (ObjectID,StartTime,Value)
> >> AS
> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
> >> UNION ALL
> >> SELECT ObjectID,StartTime,Value
> >> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
> >> GO
> >>
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (10,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (110,'2003-
> >> 01-01',1)
> >>
> >> It succeeds
> >>
> >> -- ON both server, drop the checks
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >>
> >> -- ON server0, add more ObjectID ranges
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
> [ObjectID]
> >> <= 300 )
> >> GO
> >> -- On server1, add more ObjectID ranges
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> >> [ObjectID] <= 400 )
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (11,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (111,'2003-
> >> 01-01',1)
> >>
> >> It succeeds
> >>
> >> -- ON both server
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >> -- ON server0:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
> [ObjectID]
> >> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
> >> GO
> >> -- On Server1:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
> >> [ObjectID] <= 400 OR [ObjectID] <= -501 and [ObjectID]
> >= -
> >> 600)
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (13,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (113,'2003-
> >> 01-01',1)
> >> It succeeds
> >>
> >> -- On any server,
> >> Delete vwtest
> >> -- ON both server
> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
> >> ('CK_TblZZ_Test'))
> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
> >> CK_TblZZ_Test
> >> GO
> >>
> >> -- ON server0:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> >> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
> or
> >> [ObjectID] between 40075 and 50074)
> >> GO
> >> -- On server1:
> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
> >> between 16 and 74 or [ObjectID] BETWEEN 20075 and 40074
> OR
> >> [ObjectID] BETWEEN 50075 and 60074)
> >> GO
> >> -- ON any server run the following query:
> >> SET ANSI_NULLS ON
> >> set xact_ABORT ON
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (17,'2003-
> >> 01-01',1)
> >> insert vwTest (ObjectID,StartTime,Value) VALUES
> (117,'2003-
> >> 01-01',1)
> >>
> >> It will fail. the error message is "UNION ALL view
> vwtest
> >> is not updatable becuase a partitioning column is not
> >> found."
> >>
> >> I am totally lost. Anyone knows how SQL server decides
> one
> >> column is a partitioning or not. Here I used the same
> rule
> >> but the result is different.
> >>
> >>
> >> Any ideas? Thanks in advance.
> >>
> >
> >
> >.
> >|||The result is the same. I have other aprtitioned tables
that work well. But the partitioned column of this table
has negative IDs. I think this is the reason.
>--Original Message--
>Peter,
>Oops.
>I saw you used Alter Table to add the constraint. Did
you try to create the
>constraint together with the table creation? Try that.
>Quentin
>
>"peter" <phe@.VisualPlant.com> wrote in message
>news:0c5701c34c72$5003b9b0$a301280a@.phx.gbl...
>> Thanks for your reply.
>> However, I didn't change the constraint from EM. What I
>> did is that drop the constraint for all servers, then
>> create the constraint for all servers from QA. It worked
>> in some cases. It seems if I have more ID ranges or I
have
>> ID ranges with negative value, it will fail. I tried to
>> recreate the view, it didn't work too.
>> The code I posted is exactly what I ran in QA.
>>
>> >--Original Message--
>> >Peter,
>> >
>> >did not go through your detailed post. However, I bet
>> that you did your
>> >modification with EM. It is known that when you do
such
>> changes in EM to
>> >updateable partitioned view the EM does not do it
right.
>> Try use QA. If it
>> >does not work, try recreate the view in QA.
>> >
>> >HTH
>> >
>> >Quentin
>> >
>> >"Peter" <phe@.Visualplant.com> wrote in message
>> >news:058f01c34be6$214e5990$a101280a@.phx.gbl...
>> >> Hi all,
>> >>
>> >> I am working on a distributed database. I defined
>> linked
>> >> servers, partitioned views etc. I can
>> delete/insert/update
>> >> data from the view. Now the problem is if I add more
ID
>> >> ranges to the partitioning column in the check,
>> sometimes
>> >> it worked or sometimes it didn't. See the following
>> sample
>> >> code:
>> >>
>> >> -- Create linked server SERVER0,SERVER1 on two SQL
>> >> servers.
>> >> -- SERVER0 one one machine
>> >> exec sp_addlinkedserver 'SERVER0', '',
>> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER0',
>> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser
='sa',
>> >> @.rmtpassword = ''
>> >> exec sp_serveroption @.Server='SERVER0', @.optname
>> >> ='RPC', @.optvalue='TRUE'
>> >> exec sp_serveroption @.Server='SERVER0', @.optname
>> >> ='RPC OUT', @.optvalue='TRUE'
>> >> -- SERVER1 on another machine
>> >> exec sp_addlinkedserver 'SERVER1', '',
>> >> N'SQLOLEDB', @.SHostName, '','',N'Test_DB'
>> >> exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER1',
>> >> @.useself = 'false', @.locallogin = NULL,@.rmtuser
='sa',
>> >> @.rmtpassword = ''
>> >> exec sp_serveroption @.Server='SERVER1', @.optname
>> >> ='RPC', @.optvalue='TRUE'
>> >> exec sp_serveroption @.Server='SERVER1', @.optname
>> >> ='RPC OUT', @.optvalue='TRUE'
>> >>
>> >> -- Create database Test_DB on each server.
>> >> -- ON SERVER1:
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY
(id,
>> >> N'IsUserTable') = 1)
>> >> drop table [dbo].[TblZZ_Test]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[TblZZ_Test] (
>> >> [ObjectID] [int] NOT NULL ,
>> >> [StartTime] [datetime] NOT NULL ,
>> >> [Value] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ObjectID],
>> >> [StartTime]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> -- ObjectID will be the partitioning column
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1 and
>> >> [ObjectID] <= 100)
>> >> GO
>> >>
>> >> -- ON Server1:
>> >> if exists (select * from dbo.sysobjects where id =>> >> object_id(N'[dbo].[TblZZ_Test]') and OBJECTPROPERTY
(id,
>> >> N'IsUserTable') = 1)
>> >> drop table [dbo].[TblZZ_Test]
>> >> GO
>> >>
>> >> CREATE TABLE [dbo].[TblZZ_Test] (
>> >> [ObjectID] [int] NOT NULL ,
>> >> [StartTime] [datetime] NOT NULL ,
>> >> [Value] [int] NOT NULL
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH NOCHECK ADD
>> >> CONSTRAINT [PK_TblZZ_Test] PRIMARY KEY CLUSTERED
>> >> (
>> >> [ObjectID],
>> >> [StartTime]
>> >> ) ON [PRIMARY]
>> >> GO
>> >>
>> >> -- ObjectID will be the partitioning column
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 )
>> >> GO
>> >>
>> >>
>> >> -- ON SERVER0: create federated view
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('vwTest'))
>> >> DROP view vwTest
>> >> GO
>> >> CREATE view vwTest (ObjectID,StartTime,Value)
>> >> AS
>> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> >> UNION ALL
>> >> SELECT ObjectID,StartTime,Value
>> >> FROM SERVER1.VisualPlant3DB.dbo.tblZZ_Test
>> >> GO
>> >>
>> >> --ON SERVER1: create federated view
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('vwTest'))
>> >> DROP view vwTest
>> >> GO
>> >> CREATE view vwTest (ObjectID,StartTime,Value)
>> >> AS
>> >> SELECT ObjectID,StartTime,Value FROM tblZZ_Test
>> >> UNION ALL
>> >> SELECT ObjectID,StartTime,Value
>> >> FROM SERVER0.VisualPlant3DB.dbo.tblZZ_Test
>> >> GO
>> >>
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (10,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (110,'2003-
>> >> 01-01',1)
>> >>
>> >> It succeeds
>> >>
>> >> -- ON both server, drop the checks
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >>
>> >> -- ON server0, add more ObjectID ranges
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
>> [ObjectID]
>> >> <= 300 )
>> >> GO
>> >> -- On server1, add more ObjectID ranges
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> >> [ObjectID] <= 400 )
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (11,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (111,'2003-
>> >> 01-01',1)
>> >>
>> >> It succeeds
>> >>
>> >> -- ON both server
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >> -- ON server0:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >= 1
>> >> and [ObjectID] <= 100 OR [ObjectID] >= 201 and
>> [ObjectID]
>> >> <= 300 OR [ObjectID] <= -401 and [ObjectID] >= -500 )
>> >> GO
>> >> -- On Server1:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID] >=>> >> 101 and [ObjectID] <= 200 OR [ObjectID] >= 301 and
>> >> [ObjectID] <= 400 OR [ObjectID] <= -501 and
[ObjectID]
>> >= -
>> >> 600)
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (13,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (113,'2003-
>> >> 01-01',1)
>> >> It succeeds
>> >>
>> >> -- On any server,
>> >> Delete vwtest
>> >> -- ON both server
>> >> IF EXISTS(SELECT * FROM sysobjects where ID=OBJECT_ID
>> >> ('CK_TblZZ_Test'))
>> >> ALTER TABLE [dbo].[TblZZ_Test] DROP CONSTRAINT
>> >> CK_TblZZ_Test
>> >> GO
>> >>
>> >> -- ON server0:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> >> BETWEEN 0 and 15 or [ObjectID] BETWEEN 75 and 20074
>> or
>> >> [ObjectID] between 40075 and 50074)
>> >> GO
>> >> -- On server1:
>> >> ALTER TABLE [dbo].[TblZZ_Test] WITH CHECK ADD
>> >> CONSTRAINT [CK_TblZZ_Test] CHECK ([ObjectID]
>> >> between 16 and 74 or [ObjectID] BETWEEN 20075 and
40074
>> OR
>> >> [ObjectID] BETWEEN 50075 and 60074)
>> >> GO
>> >> -- ON any server run the following query:
>> >> SET ANSI_NULLS ON
>> >> set xact_ABORT ON
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (17,'2003-
>> >> 01-01',1)
>> >> insert vwTest (ObjectID,StartTime,Value) VALUES
>> (117,'2003-
>> >> 01-01',1)
>> >>
>> >> It will fail. the error message is "UNION ALL view
>> vwtest
>> >> is not updatable becuase a partitioning column is not
>> >> found."
>> >>
>> >> I am totally lost. Anyone knows how SQL server
decides
>> one
>> >> column is a partitioning or not. Here I used the same
>> rule
>> >> but the result is different.
>> >>
>> >>
>> >> Any ideas? Thanks in advance.
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I have the same problem. My view is local and I can
insert using table names, but only read using view.

A simple sproc problem

I am trying to do something quite simple but i need some help. I am trying to insert some values into a table in my db. Lets say column country_id and city_id are required fields but state_id is not. I am using a stored proc that looks like
CREATE procedure spAdd_Countries_States_Cities

@.country_id int,
@.state_id int ,
@.city_id int

AS
insert Countries_States_Cities (country_id, state_id, city_id)
values (@.country_id, @.state_id, @.city_id)
GO
The problem is if I only pass two values into this proc I get an error saying that the sproc is looking for state_id. At the same time I need it there in case a state_id is being added. How do I work around this problem?
Thanks

I'd solve this problem by assigning a default value of NULL to the non-required parameter:

CREATE procedure spAdd_Countries_States_Cities

@.country_id int,
@.state_id int= NULL,
@.city_id int

AS
insert Countries_States_Cities (country_id, state_id, city_id)
values (@.country_id, @.state_id, @.city_id)
GO

a simple insert/update trigger

Hi I am looking to create a simple trigger for both UPDATES and INSERTS
where any change to tbl1 will be inserted/updated in tbl2.
The insert works okay, with values added to both tables, however when I
perform an update, it seems to add an extra row in tbl2.
Here's the code:
CREATE TABLE [dbo].[tbl1] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tbl2] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[Team] [varchar] (25) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [tri_DataTrans] ON [dbo].[tbl1]
FOR INSERT, UPDATE
AS
INSERT INTO tbl2([ID], [name], team)
SELECT ID, [name], team FROM inserted
UPDATE tbl2
SET [Name] = Inserted.[Name],
[Team] = Inserted.[Team]
FROM Inserted
WHERE [ID] = Inserted.[ID]
---
What am I doing wrong? do I need to use IF UPDATE()?
Thanks
qh75Why do you want to use a single trigger? You can certainly do that
(modify your INSERT to insert the row only if it doesn't already exist)
but since you want totally different actions in the case of UPDATE and
INSERT it will surely be more efficient to use two triggers instead of
one.
Secondly, your tables as posted have no keys at all. Apparently even
the ID isn't declared as unique (the IDENTITY property doesn't actually
guarantee uniqueness) and the other columns are nullable. Even if that
constrain exists on ID it is definitely not safe to assume that a row
would be assigned the same ID in both tables. On the other hand you
have included the ID in the INSERT, which will fail unless you turn
IDENTITY_INSERT ON, so I'm not clear if you intended to use IDENTITY in
the second table or not.
The solution is to declare natural keys on both tables (presumably Name
and/or Team) and make those column(s) NOT NULL. Then join the two
tables on that key rather than the IDENTITY.
David Portas
SQL Server MVP
--|||David Portas wrote:
> Why do you want to use a single trigger? You can certainly do that
> (modify your INSERT to insert the row only if it doesn't already
exist)
> but since you want totally different actions in the case of UPDATE
and
> INSERT it will surely be more efficient to use two triggers instead
of
> one.
I just thought it could be performed in one trigger, basically (without
getting into the identity stuff) I was looking for some logic I could
use in the one trigger to check for both inserts and both updates.

> Secondly, your tables as posted have no keys at all. Apparently even
> the ID isn't declared as unique (the IDENTITY property doesn't
actually
> guarantee uniqueness) and the other columns are nullable. Even if
that
> constrain exists on ID it is definitely not safe to assume that a row
> would be assigned the same ID in both tables. On the other hand you
> have included the ID in the INSERT, which will fail unless you turn
> IDENTITY_INSERT ON, so I'm not clear if you intended to use IDENTITY
in
> the second table or not.
I'll add a key to the tables and try that.
Thanks for the reply.
qh|||Anyhoo, this is what I came up with:
---
CREATE TRIGGER [tri_Update] ON [dbo].[tbl1]
FOR UPDATE
AS
IF UPDATE ([Team])
BEGIN
UPDATE tbl2
SET [Team] = Inserted.[Team]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
END
IF UPDATE ([Name])
BEGIN
UPDATE tbl2
SET [Name] = Inserted.[Name]
FROM tbl2, Inserted
WHERE tbl2.[ID] = Inserted.[ID]
END
----
CREATE TRIGGER [tri_DataTrans] ON [dbo].[tbl1]
FOR INSERT
AS
INSERT INTO tbl2([ID], [name], team)
SELECT ID, [name], team FROM inserted
----
If you know of a way of combining the two triggers into one (if indeed
it can be done) please let me know.
Cheers
qh

A Simple Insert statement in European version of SQL

I have a client that uses my utility program to insert a record into a
table. Once my program receives the values, creates an insert statement
with comma separated values. Ie:
Insert into T (a,b) values (1.578, 2)
I now understand that those numeric values could have ',' in place of
decimal point for European version. So, the above values would look like
1,578 and 2.
How does the insert statement would know ',' is not a separator in this
case ? Ie:
Insert into T (a,b) values (1,578, 2) --> resulting
into syntax error
Should I be using a different value separator character ?
TIA.
MacI am not the one producing the values with ',' in place of decimal points.
It is SQL Server of European version that returns the data I am collecting
with embedded comma. So, when I submit a query of "Select a from T"
( a is defined to be a real type number), it returns 2,476 instead of
2.476. So, my question is how do I take these returned value with
embedded comma and insert them back into say another field in a table ? I
do not have such SQL version in my site to see what is going on and how to
accomplish such inserts.
- Mac
""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> Hi Mac,
> Please do not use comma as decimal separator. It will cause problems. Use
> period as decimal point.
> Character expressions being converted to an exact numeric data type must
> consist of digits, a decimal point, and an optional plus (+) or minus (-).
> Leading blanks are ignored. Comma separators (such as the thousands
> separator in 123,456.00) are not allowed in the string.
>
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> | Newsgroups: microsoft.public.sqlserver.server
> | Subject: A Simple Insert statement in European version of SQL
> | Date: Mon, 25 Aug 2003 16:49:15 -0700
> | Organization: Unisys - Roseville, MN
> | Lines: 20
> | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> | NNTP-Posting-Host: 192.59.171.175
> | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> 2003 23:49:15 GMT)
> | X-Complaints-To: news@.rsvl.unisys.com
> | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> | Path:
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have a client that uses my utility program to insert a record into a
> | table. Once my program receives the values, creates an insert
statement
> | with comma separated values. Ie:
> | Insert into T (a,b) values (1.578, 2)
> |
> | I now understand that those numeric values could have ',' in place of
> | decimal point for European version. So, the above values would look
> like
> | 1,578 and 2.
> |
> | How does the insert statement would know ',' is not a separator in this
> | case ? Ie:
> | Insert into T (a,b) values (1,578, 2) --> resulting
> | into syntax error
> |
> | Should I be using a different value separator character ?
> |
> | TIA.
> | Mac
> |
> |
> |
>|||Are you using Visual basic ?
VB does use the client-settings to format numbers, dates,...
You'll have to use a format function to convert to a propre string.
e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
"###0.00") & ")"
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:big51p$5vu$1@.si05.rsvl.unisys.com...
> I am not the one producing the values with ',' in place of decimal points.
> It is SQL Server of European version that returns the data I am collecting
> with embedded comma. So, when I submit a query of "Select a from T"
> ( a is defined to be a real type number), it returns 2,476 instead of
> 2.476. So, my question is how do I take these returned value with
> embedded comma and insert them back into say another field in a table ?
I
> do not have such SQL version in my site to see what is going on and how to
> accomplish such inserts.
> - Mac
>
> ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > Hi Mac,
> >
> > Please do not use comma as decimal separator. It will cause problems.
Use
> > period as decimal point.
> >
> > Character expressions being converted to an exact numeric data type must
> > consist of digits, a decimal point, and an optional plus (+) or minus
(-).
> > Leading blanks are ignored. Comma separators (such as the thousands
> > separator in 123,456.00) are not allowed in the string.
> >
> >
> >
> > Bill Cheng
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> > --
> > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > | Newsgroups: microsoft.public.sqlserver.server
> > | Subject: A Simple Insert statement in European version of SQL
> > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > | Organization: Unisys - Roseville, MN
> > | Lines: 20
> > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > | NNTP-Posting-Host: 192.59.171.175
> > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > 2003 23:49:15 GMT)
> > | X-Complaints-To: news@.rsvl.unisys.com
> > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > | Path:
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > |
> > | I have a client that uses my utility program to insert a record into a
> > | table. Once my program receives the values, creates an insert
> statement
> > | with comma separated values. Ie:
> > | Insert into T (a,b) values (1.578, 2)
> > |
> > | I now understand that those numeric values could have ',' in place of
> > | decimal point for European version. So, the above values would look
> > like
> > | 1,578 and 2.
> > |
> > | How does the insert statement would know ',' is not a separator in
this
> > | case ? Ie:
> > | Insert into T (a,b) values (1,578, 2) -->
resulting
> > | into syntax error
> > |
> > | Should I be using a different value separator character ?
> > |
> > | TIA.
> > | Mac
> > |
> > |
> > |
> >
>|||As jobi point out: You have to differentiate between input and output. Just because a client tool
formats something that SQL Server outputs with a comma doesn't mean that SQL Server accepts that as
a valid input format.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"jobi" <jobi@.reply2.group> wrote in message news:bihkck$boq$1@.reader08.wxs.nl...
> Are you using Visual basic ?
> VB does use the client-settings to format numbers, dates,...
> You'll have to use a format function to convert to a propre string.
> e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> "###0.00") & ")"
> jobi
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > I am not the one producing the values with ',' in place of decimal points.
> > It is SQL Server of European version that returns the data I am collecting
> > with embedded comma. So, when I submit a query of "Select a from T"
> > ( a is defined to be a real type number), it returns 2,476 instead of
> > 2.476. So, my question is how do I take these returned value with
> > embedded comma and insert them back into say another field in a table ?
> I
> > do not have such SQL version in my site to see what is going on and how to
> > accomplish such inserts.
> >
> > - Mac
> >
> >
> > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > Hi Mac,
> > >
> > > Please do not use comma as decimal separator. It will cause problems.
> Use
> > > period as decimal point.
> > >
> > > Character expressions being converted to an exact numeric data type must
> > > consist of digits, a decimal point, and an optional plus (+) or minus
> (-).
> > > Leading blanks are ignored. Comma separators (such as the thousands
> > > separator in 123,456.00) are not allowed in the string.
> > >
> > >
> > >
> > > Bill Cheng
> > > Microsoft Online Partner Support
> > >
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > --
> > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > | Newsgroups: microsoft.public.sqlserver.server
> > > | Subject: A Simple Insert statement in European version of SQL
> > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > | Organization: Unisys - Roseville, MN
> > > | Lines: 20
> > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > | NNTP-Posting-Host: 192.59.171.175
> > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > > 2003 23:49:15 GMT)
> > > | X-Complaints-To: news@.rsvl.unisys.com
> > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > | X-Priority: 3
> > > | X-MSMail-Priority: Normal
> > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > | Path:
> > >
> >
> cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > >
> >
> m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > |
> > > | I have a client that uses my utility program to insert a record into a
> > > | table. Once my program receives the values, creates an insert
> > statement
> > > | with comma separated values. Ie:
> > > | Insert into T (a,b) values (1.578, 2)
> > > |
> > > | I now understand that those numeric values could have ',' in place of
> > > | decimal point for European version. So, the above values would look
> > > like
> > > | 1,578 and 2.
> > > |
> > > | How does the insert statement would know ',' is not a separator in
> this
> > > | case ? Ie:
> > > | Insert into T (a,b) values (1,578, 2) -->
> resulting
> > > | into syntax error
> > > |
> > > | Should I be using a different value separator character ?
> > > |
> > > | TIA.
> > > | Mac
> > > |
> > > |
> > > |
> > >
> >
> >
>|||Mac,
I've checked my vb-code again, and found this extra.
'Aparently the format still uses the client-setting for decimal point !!!
replace$(string, ",",".")
so you'll have to come up to this :
e.g. strsql = "insert into table1 (col1) values (" &
Replace$(Format(numcol, "###0.00"),",",".") & ")"
I guess you don't need the ###-part, in fact, you only need the format if
you want control of the format, else you can use the cstr-function.
Replace$(CStr(numcol), ",", ".")
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:bijdkf$2fsd$1@.si05.rsvl.unisys.com...
> Jobi,
> Yes. I am using VB. Also when I am collecting the data, I use Format
> function to make sure I only get 2 digits decimal point. The format
> sysntax I use is following:
> Format(numValue, "0.00").
> Is this not correct ? Do I need the ### in front of them ? More like
aVB
> question...
> By using the Format function I thought I am also forcing the decimal point
> to show up as decimal point despite the local setting of the computer.
This
> way then I can turn around and use the result in another insert statement
> without further formatting.
> Thanks for the input.
> Mac
> "jobi" <jobi@.reply2.group> wrote in message
> news:bihkck$boq$1@.reader08.wxs.nl...
> > Are you using Visual basic ?
> >
> > VB does use the client-settings to format numbers, dates,...
> > You'll have to use a format function to convert to a propre string.
> >
> > e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> > "###0.00") & ")"
> >
> > jobi
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > > I am not the one producing the values with ',' in place of decimal
> points.
> > > It is SQL Server of European version that returns the data I am
> collecting
> > > with embedded comma. So, when I submit a query of "Select a from
T"
> > > ( a is defined to be a real type number), it returns 2,476 instead
of
> > > 2.476. So, my question is how do I take these returned value with
> > > embedded comma and insert them back into say another field in a table
?
> > I
> > > do not have such SQL version in my site to see what is going on and
how
> to
> > > accomplish such inserts.
> > >
> > > - Mac
> > >
> > >
> > > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > > Hi Mac,
> > > >
> > > > Please do not use comma as decimal separator. It will cause
problems.
> > Use
> > > > period as decimal point.
> > > >
> > > > Character expressions being converted to an exact numeric data type
> must
> > > > consist of digits, a decimal point, and an optional plus (+) or
minus
> > (-).
> > > > Leading blanks are ignored. Comma separators (such as the thousands
> > > > separator in 123,456.00) are not allowed in the string.
> > > >
> > > >
> > > >
> > > > Bill Cheng
> > > > Microsoft Online Partner Support
> > > >
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > --
> > > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > > | Newsgroups: microsoft.public.sqlserver.server
> > > > | Subject: A Simple Insert statement in European version of SQL
> > > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > > | Organization: Unisys - Roseville, MN
> > > > | Lines: 20
> > > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > > | NNTP-Posting-Host: 192.59.171.175
> > > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25
> Aug
> > > > 2003 23:49:15 GMT)
> > > > | X-Complaints-To: news@.rsvl.unisys.com
> > > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > > | X-Priority: 3
> > > > | X-MSMail-Priority: Normal
> > > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > > | Path:
> > > >
> > >
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > > >
> > >
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > > | Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.server:303093
> > > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > > |
> > > > | I have a client that uses my utility program to insert a record
into
> a
> > > > | table. Once my program receives the values, creates an insert
> > > statement
> > > > | with comma separated values. Ie:
> > > > | Insert into T (a,b) values (1.578, 2)
> > > > |
> > > > | I now understand that those numeric values could have ',' in
place
> of
> > > > | decimal point for European version. So, the above values would
> look
> > > > like
> > > > | 1,578 and 2.
> > > > |
> > > > | How does the insert statement would know ',' is not a separator
in
> > this
> > > > | case ? Ie:
> > > > | Insert into T (a,b) values (1,578, 2) -->
> > resulting
> > > > | into syntax error
> > > > |
> > > > | Should I be using a different value separator character ?
> > > > |
> > > > | TIA.
> > > > | Mac
> > > > |
> > > > |
> > > > |
> > > >
> > >
> > >
> >
> >
>

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

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

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