problem with linked servers and INSERT

Hello

I have 2 linked SQL servers, trying to communicate with each other. An SP on the one server calls a function on the other to insert the data into a temporary table, but this makes the whole SP freeze. If I just call the function to view the data, it works the fine.

Here's the code:

-------

create table #b (type int,label varchar(100),x int,y int,so int)

declare @.arg nvarchar(100)
set @.arg = 'BLANKET.MaalGrupper.ID38'

declare @.tsql varchar(1000)
select @.tsql = 'select * from openquery( [erinyes.resultmaker.com], ''select * from blanketter.dbo.fnSelect1( '' + @.arg + '' )'' )'
insert #b exec (@.tsql)

drop table #b

--------

I use the openquery function so I can provide the fnSelect1 function with a dynamically generated argument (@.arg). If I remove the 'insert #b' part of the next to last line, it works fine. I get the same behaviour if I use an SP instead of the fnSelect1 function.

Can anybody help with this very irritating problem?

Thanks
MNJdoesn't anybody have an idea of might be wrong?|||Below is working for me (SQL2000)

-- on linked server
CREATE FUNCTION getit (@.id int=null)
RETURNS TABLE
AS
RETURN (SELECT *
FROM sysobjects WHERE id = coalesce(@.id,id))
go
select * from getit(null)
-------------
create table #b (label varchar(100))
insert #b
select * from openquery(linked,'select name from testDB.dbo.getit(null)')

declare @.tsql varchar(1000)
select @.tsql = 'select * from openquery(linked,''select name from testDB.dbo.getit(null)'')'
insert #b exec (@.tsql)|||Do you have Distributed Transaction Coordinator running on both machines?|||Hm, the first select works for me, the second doesn't. And I need to be able to specify a parameter.

DTC is running on both machines.

MNJ|||Have you set any special environment variables or anything like that?

MNJ|||Have you set any special environment variables or anything like that?

MNJ
Nothing special...sql

Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?
Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:

>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>
|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribi en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:
>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAULT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribió en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
>>Hi all:
>>I have following problem.
>>When I try to run an INSERT from Query Analyser to a table of a linked
>>server I get
>>a bug about a TEXT field of table:
>>INSERT is like this one:
>>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
>> values ('090','Fam1','Familia 1')
>>Bug is like this one:
>>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>>OLE DB [OLE/DB Provider 'SQLOLEDB'
>>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
>>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
>>STATUS=DBSTATUS_S_DEFAULT],
>>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>>value.
>>Running same instruction locally there isn't any problem.
>>How can I solve this problem ?
>>
>>|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribió en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
>> When I try to run an INSERT from Query Analyser to a table of a linked
>> server I get
>> a bug about a TEXT field of table:
>> INSERT is like this one:
>> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion)
>> values ('090','Fam1','Familia 1')
>> Bug is like this one:
>> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observaciones'.
>> OLE DB [OLE/DB Provider 'SQLOLEDB'
>> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK],
>> [COLUMN_NAME=familia
>> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
>> STATUS=DBSTATUS_S_DEFAULT],
>> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaciones'.
>> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>> value.
>> Running same instruction locally there isn't any problem.
>> How can I solve this problem ?
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Problem with Linked Server and Text fields

Hi all:
I have following problem.
When I try to run an INSERT from Query Analyser to a table of a linked
server I get
a bug about a TEXT field of table:
INSERT is like this one:
INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcion
)
values ('090','Fam1','Familia 1')
Bug is like this one:
The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observa
ciones'.
OLE DB [OLE/DB Provider 'SQLOLEDB'
IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=f
amilia
STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAUL
T],
[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observacio
nes'.
INSERT hasn't field "Observaciones" (TEXT type) because it has a default
value.
Running same instruction locally there isn't any problem.
How can I solve this problem ?Luis,
I have reproduced this error. I have never seen this before,
and I'll ask for some help and get back to you.
Steve Kass
Drew University
Luis Soler wrote:

>Hi all:
>I have following problem.
>When I try to run an INSERT from Query Analyser to a table of a linked
>server I get
>a bug about a TEXT field of table:
>INSERT is like this one:
>INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripcio
n)
> values ('090','Fam1','Familia 1')
>Bug is like this one:
>The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
>'[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'observ
aciones'.
>OLE DB [OLE/DB Provider 'SQLOLEDB'
>IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
>provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME=
familia
>STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion STATUS=DBSTATUS_S_DEFAU
LT],
>[COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
>Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
>[SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observaci
ones'.
>INSERT hasn't field "Observaciones" (TEXT type) because it has a default
>value.
>Running same instruction locally there isn't any problem.
>How can I solve this problem ?
>
>
>|||Hi Steve:
Table has one TEXT type field and a default value of ''.
I get error only in TEXT fields.
Boths servers have SQL Server 2000 with SP3.
Do you need anything more ?
"Steve Kass" <skass@.drew.edu> escribi en el mensaje
news:uvDbD8DFFHA.2156@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Luis,
> I have reproduced this error. I have never seen this before,
> and I'll ask for some help and get back to you.
> Steve Kass
> Drew University
> Luis Soler wrote:
>|||Luis Soler (none@.none) writes:
> When I try to run an INSERT from Query Analyser to a table of a linked
> server I get
> a bug about a TEXT field of table:
> INSERT is like this one:
> INSERT INTO [SERVIDOR].basedatos.dbo.fmafam (empresa,familia,descripci
on)
> values ('090','Fam1','Familia 1')
> Bug is like this one:
> The provider OLE DB 'SQLOLEDB' can`t INSERT INTO table
> '[SERVIDOR].[basedatos].[dbo].[fmafam]', by column 'obser
vaciones'.
> OLE DB [OLE/DB Provider 'SQLOLEDB'
> IRowsetChange::InsertRow returned 0x80040e21: Data status sent to the
> provider: [COLUMN_NAME=empresa STATUS=DBSTATUS_S_OK], [COLUMN_NAME
=familia
> STATUS=DBSTATUS_S_OK], [COLUMN_NAME=descripcion
> STATUS=DBSTATUS_S_DEFAULT],
> [COLUMN_NAME=observaciones STATUS=DBSTATUS_S_ISNULL],...
> Provider OLE DB 'SQLOLEDB' can't INSERT INTO table
> [SERVIDOR].[basedatos].[dbo].[fmafam], by column 'observac
iones'.
> INSERT hasn't field "Observaciones" (TEXT type) because it has a default
> value.
> Running same instruction locally there isn't any problem.
> How can I solve this problem ?
You will have to find a workaround of some sort. It appears that the
SQLOLEDB provider does not support default values with text columns.
Or maybe I should say do not expect them to have a default value. So
when the the data is entered into the rowset, SQLOLEDB things have
gone sour.
I also tested this on the latest (semi-)public build of SQL 2005, and
when the local server is SQL 2005, the INSERT works. This indicates
that the problem is fixed in SQL Native Client, an SQL Server OLE DB
provider that comes with SQL 2005.
What the best workaround is for you depends on your application, but
presumably the easiest way out is to make the column nullable.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK, already I have thinking about the option to make the field nullable
Thanks Erland
"Erland Sommarskog" <esquel@.sommarskog.se> escribi en el mensaje
news:Xns9600E1BB44115Yazorman@.127.0.0.1...
> Luis Soler (none@.none) writes:
> You will have to find a workaround of some sort. It appears that the
> SQLOLEDB provider does not support default values with text columns.
> Or maybe I should say do not expect them to have a default value. So
> when the the data is entered into the rowset, SQLOLEDB things have
> gone sour.
> I also tested this on the latest (semi-)public build of SQL 2005, and
> when the local server is SQL 2005, the INSERT works. This indicates
> that the problem is fixed in SQL Native Client, an SQL Server OLE DB
> provider that comes with SQL 2005.
> What the best workaround is for you depends on your application, but
> presumably the easiest way out is to make the column nullable.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

problem with like [^]

SET NOCOUNT ON
declare @.tab table(vc varchar(2000))
insert into @.tab select '10.1'
insert into @.tab select '10.1.1'
insert into @.tab select '10.1.2'
insert into @.tab select '10.1.1.1'
insert into @.tab select '10.1.1.1.1'
The problem is that i need to select only 10.1.1 and 10.1.2 when i put input
as 10.1
If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
I tried this but not working
SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'try this
SELECT * FROM @.Tab WHERE vc like '10.1.[^.%]'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"aneeshattingal" wrote:

> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put inp
ut
> as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
>
>|||aneeshattingal wrote:
> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put inp
ut
> as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
Drop the '%' from your query:
SELECT * FROM @.Tab WHERE vc like '10.1.[^.]'|||Sorry.. that won't work for 10.1.12.. Wrong solution.
You will have to do it the long way :)
SELECT * FROM @.Tab WHERE
vc not like '10.1.%[.]%'
and vc like '10.1.%'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Omnibuzz" wrote:
> try this
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.%]'
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "aneeshattingal" wrote:
>|||Thank guys , both worked ..
"aneeshattingal" <aneeshattingal@.hotmail.com> wrote in message
news:u0zMeYkjGHA.3408@.TK2MSFTNGP05.phx.gbl...
> SET NOCOUNT ON
> declare @.tab table(vc varchar(2000))
> insert into @.tab select '10.1'
> insert into @.tab select '10.1.1'
> insert into @.tab select '10.1.2'
> insert into @.tab select '10.1.1.1'
> insert into @.tab select '10.1.1.1.1'
> The problem is that i need to select only 10.1.1 and 10.1.2 when i put
> input as 10.1
> If I give input as 10.1 , it should select only records 10.1.1 and 10.1.2
> I tried this but not working
> SELECT * FROM @.Tab WHERE vc like '10.1.[^.]%'
>

problem with large number of inserts

We have an application that is connecting to our SQL 2k server remotely via
ODBC and sending a large number of individual insert statements. Apparently
this is causing problems with the transaction filling up on the sql database
the inserts are being sent to. To solve the problem, we set the transaction
log to be able to grow to 2GB. It quickly filled that space. Then we set it
to unlimited growth. Over time it grew to 12GB which began to cause other
problems. We backed up and ran DBCC Shrinkfile to bring it back under 1GB but
the remote app is not able to complete the inserts at that size. Truncate log
on checkpoint is selected in the database properties.
I found some KB articles which referred to problems doing large updates but
they had to do with using the 'where' statement and we are doing inserts with
no 'where' statement so it didn't seem to apply. Any ideas on how to solve
this problem?
Neither the Event Viewer nor the SQL Logs are offering much insight. No
errors are logged when the remote insert fails. However, the same database
appears to be failing to backup. During the weekly backup routine, we get
this in the Event Viewer:
"Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 3617.
A subsequent backup operation may be slower/larger than normal."
We also get several variations of this message for both the Log and Database:
3041 :
BACKUP failed to complete the command BACKUP LOG [specialneeds] TO DISK =
N'E:\SQL Backup\specialneeds\specialneeds_db_200506190202.B AK', DISK =
N'E:\SQL Backup\specialneeds\specialneeds_Tlog.bak' WITH INIT , NOUNLOAD ,
NAME = N'specialneeds backup', NOSKIP , STATS = 10, NOFORMAT
We have 30 or so other databases, some larger some smaller, running on the
same SQL Server and none of them are having problems backing up. Thanks for
your help!
Hi
Run a DBCC CHECKDB on this database and see what it reports.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"myaffee" wrote:

> We have an application that is connecting to our SQL 2k server remotely via
> ODBC and sending a large number of individual insert statements. Apparently
> this is causing problems with the transaction filling up on the sql database
> the inserts are being sent to. To solve the problem, we set the transaction
> log to be able to grow to 2GB. It quickly filled that space. Then we set it
> to unlimited growth. Over time it grew to 12GB which began to cause other
> problems. We backed up and ran DBCC Shrinkfile to bring it back under 1GB but
> the remote app is not able to complete the inserts at that size. Truncate log
> on checkpoint is selected in the database properties.
> I found some KB articles which referred to problems doing large updates but
> they had to do with using the 'where' statement and we are doing inserts with
> no 'where' statement so it didn't seem to apply. Any ideas on how to solve
> this problem?
> Neither the Event Viewer nor the SQL Logs are offering much insight. No
> errors are logged when the remote insert fails. However, the same database
> appears to be failing to backup. During the weekly backup routine, we get
> this in the Event Viewer:
> "Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 3617.
> A subsequent backup operation may be slower/larger than normal."
> We also get several variations of this message for both the Log and Database:
> 3041 :
> BACKUP failed to complete the command BACKUP LOG [specialneeds] TO DISK =
> N'E:\SQL Backup\specialneeds\specialneeds_db_200506190202.B AK', DISK =
> N'E:\SQL Backup\specialneeds\specialneeds_Tlog.bak' WITH INIT , NOUNLOAD ,
> NAME = N'specialneeds backup', NOSKIP , STATS = 10, NOFORMAT
> We have 30 or so other databases, some larger some smaller, running on the
> same SQL Server and none of them are having problems backing up. Thanks for
> your help!
>
|||Mike,
Here's what it reported:
DBCC results for 'specialneeds'.
DBCC results for 'sysobjects'.
There are 245 rows in 6 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 283 rows in 12 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 1167 rows in 25 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 220 rows in 19 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 18 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 13 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 571 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 2 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'leAuditCollectConfigVars'.
There are 3 rows in 1 pages for object 'leAuditCollectConfigVars'.
DBCC results for 'leAuditCollectDatabases'.
There are 59 rows in 1 pages for object 'leAuditCollectDatabases'.
DBCC results for 'leAuditCollectNotification'.
There are 1 rows in 1 pages for object 'leAuditCollectNotification'.
DBCC results for 'leAuditCollectAlerts'.
There are 5 rows in 1 pages for object 'leAuditCollectAlerts'.
DBCC results for 'affiliatelog'.
There are 0 rows in 1 pages for object 'affiliatelog'.
DBCC results for 'billing'.
There are 0 rows in 1 pages for object 'billing'.
DBCC results for 'configuration'.
There are 355 rows in 3 pages for object 'configuration'.
DBCC results for 'configuration1'.
There are 0 rows in 1 pages for object 'configuration1'.
DBCC results for 'coupons'.
There are 9 rows in 1 pages for object 'coupons'.
DBCC results for 'customerprices'.
There are 0 rows in 1 pages for object 'customerprices'.
DBCC results for 'customers'.
There are 1083 rows in 34 pages for object 'customers'.
DBCC results for 'downloads'.
There are 0 rows in 1 pages for object 'downloads'.
DBCC results for 'gifts'.
There are 0 rows in 1 pages for object 'gifts'.
DBCC results for 'hackers'.
There are 1 rows in 1 pages for object 'hackers'.
DBCC results for 'languages'.
There are 671 rows in 6 pages for object 'languages'.
DBCC results for 'mycompany'.
There are 0 rows in 1 pages for object 'mycompany'.
DBCC results for 'ordertracking'.
There are 0 rows in 1 pages for object 'ordertracking'.
DBCC results for 'pinnumbers'.
There are 0 rows in 1 pages for object 'pinnumbers'.
DBCC results for 'prodcategories'.
There are 2891 rows in 8 pages for object 'prodcategories'.
DBCC results for 'dtproperties'.
There are 0 rows in 1 pages for object 'dtproperties'.
DBCC results for 'prodfeatures'.
There are 0 rows in 1 pages for object 'prodfeatures'.
DBCC results for 'projects'.
There are 0 rows in 1 pages for object 'projects'.
DBCC results for 'quantitydiscounts'.
There are 0 rows in 1 pages for object 'quantitydiscounts'.
DBCC results for 'registrant'.
There are 0 rows in 1 pages for object 'registrant'.
DBCC results for 'registryitems'.
There are 0 rows in 1 pages for object 'registryitems'.
DBCC results for 'reviews'.
There are 0 rows in 1 pages for object 'reviews'.
DBCC results for 'savedcarts'.
There are 3 rows in 1 pages for object 'savedcarts'.
DBCC results for 'searchresults'.
There are 0 rows in 1 pages for object 'searchresults'.
DBCC results for 'shipmethods'.
There are 1 rows in 1 pages for object 'shipmethods'.
DBCC results for 'suppliers'.
There are 0 rows in 1 pages for object 'suppliers'.
DBCC results for 'tblaccess'.
There are 40 rows in 2 pages for object 'tblaccess'.
DBCC results for 'tbllog'.
There are 682 rows in 11 pages for object 'tbllog'.
DBCC results for 'orders'.
There are 997 rows in 71 pages for object 'orders'.
DBCC results for 'tbluser'.
There are 2 rows in 1 pages for object 'tbluser'.
DBCC results for 'templates'.
There are 0 rows in 1 pages for object 'templates'.
DBCC results for 'oitems'.
There are 2093 rows in 42 pages for object 'oitems'.
DBCC results for 'ipaddress'.
There are 8 rows in 1 pages for object 'ipaddress'.
DBCC results for 'privlabelproducts'.
There are 0 rows in 1 pages for object 'privlabelproducts'.
DBCC results for 'privatelabels'.
There are 1 rows in 1 pages for object 'privatelabels'.
DBCC results for 'privlabelproducts1'.
There are 32 rows in 3 pages for object 'privlabelproducts1'.
DBCC results for 'privatelabels1'.
There are 1 rows in 1 pages for object 'privatelabels1'.
DBCC results for 'privlabelproducts3'.
There are 0 rows in 1 pages for object 'privlabelproducts3'.
DBCC results for 'privatelabels3'.
There are 1 rows in 1 pages for object 'privatelabels3'.
DBCC results for 'categories'.
There are 39 rows in 1 pages for object 'categories'.
DBCC results for 'specialneeds.DIY_TEMPCOMMAND_TABLE'.
There are 0 rows in 0 pages for object 'specialneeds.DIY_TEMPCOMMAND_TABLE'.
DBCC results for 'spneeds_calendar'.
There are 10 rows in 1 pages for object 'spneeds_calendar'.
DBCC results for 'calendar'.
There are 13 rows in 1 pages for object 'calendar'.
DBCC results for 'spneeds_level'.
There are 12 rows in 1 pages for object 'spneeds_level'.
DBCC results for 'spneeds_author'.
There are 2892 rows in 18 pages for object 'spneeds_author'.
DBCC results for 'spneeds_subject'.
There are 39 rows in 1 pages for object 'spneeds_subject'.
DBCC results for 'spneeds_product'.
There are 2890 rows in 69 pages for object 'spneeds_product'.
DBCC results for 'disability_link_categories'.
There are 6 rows in 1 pages for object 'disability_link_categories'.
DBCC results for 'disability_link_images'.
There are 3 rows in 1 pages for object 'disability_link_images'.
DBCC results for 'disability_links'.
There are 16 rows in 1 pages for object 'disability_links'.
DBCC results for 'discount_codes'.
There are 1 rows in 1 pages for object 'discount_codes'.
DBCC results for 'spneeds_messages'.
There are 1 rows in 1 pages for object 'spneeds_messages'.
DBCC results for 'EmailContest'.
There are 434 rows in 8 pages for object 'EmailContest'.
DBCC results for 'products3'.
There are 3251 rows in 144 pages for object 'products3'.
DBCC results for 'products1'.
There are 3258 rows in 145 pages for object 'products1'.
DBCC results for 'products'.
There are 3258 rows in 151 pages for object 'products'.
DBCC results for 'affiliates'.
There are 1 rows in 1 pages for object 'affiliates'.
CHECKDB found 0 allocation errors and 0 consistency errors in database
'specialneeds'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Run a DBCC CHECKDB on this database and see what it reports.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "myaffee" wrote:

problem with large number of inserts

We have an application that is connecting to our SQL 2k server remotely via
ODBC and sending a large number of individual insert statements. Apparently
this is causing problems with the transaction filling up on the sql database
the inserts are being sent to. To solve the problem, we set the transaction
log to be able to grow to 2GB. It quickly filled that space. Then we set it
to unlimited growth. Over time it grew to 12GB which began to cause other
problems. We backed up and ran DBCC Shrinkfile to bring it back under 1GB bu
t
the remote app is not able to complete the inserts at that size. Truncate lo
g
on checkpoint is selected in the database properties.
I found some KB articles which referred to problems doing large updates but
they had to do with using the 'where' statement and we are doing inserts wit
h
no 'where' statement so it didn't seem to apply. Any ideas on how to solve
this problem?
Neither the Event Viewer nor the SQL Logs are offering much insight. No
errors are logged when the remote insert fails. However, the same database
appears to be failing to backup. During the weekly backup routine, we get
this in the Event Viewer:
"Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 3617.
A subsequent backup operation may be slower/larger than normal."
We also get several variations of this message for both the Log and Database
:
3041 :
BACKUP failed to complete the command BACKUP LOG [specialneeds] TO DISK
=
N'E:\SQL Backup\specialneeds\specialneeds_db_2005
06190202.BAK', DISK =
N'E:\SQL Backup\specialneeds\specialneeds_Tlog.bak' WITH INIT , NOUNLOAD ,
NAME = N'specialneeds backup', NOSKIP , STATS = 10, NOFORMAT
We have 30 or so other databases, some larger some smaller, running on the
same SQL Server and none of them are having problems backing up. Thanks for
your help!Hi
Run a DBCC CHECKDB on this database and see what it reports.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"myaffee" wrote:

> We have an application that is connecting to our SQL 2k server remotely vi
a
> ODBC and sending a large number of individual insert statements. Apparentl
y
> this is causing problems with the transaction filling up on the sql databa
se
> the inserts are being sent to. To solve the problem, we set the transactio
n
> log to be able to grow to 2GB. It quickly filled that space. Then we set i
t
> to unlimited growth. Over time it grew to 12GB which began to cause other
> problems. We backed up and ran DBCC Shrinkfile to bring it back under 1GB
but
> the remote app is not able to complete the inserts at that size. Truncate
log
> on checkpoint is selected in the database properties.
> I found some KB articles which referred to problems doing large updates bu
t
> they had to do with using the 'where' statement and we are doing inserts w
ith
> no 'where' statement so it didn't seem to apply. Any ideas on how to solve
> this problem?
> Neither the Event Viewer nor the SQL Logs are offering much insight. No
> errors are logged when the remote insert fails. However, the same database
> appears to be failing to backup. During the weekly backup routine, we get
> this in the Event Viewer:
> "Could not clear 'DIFF' bitmap in database 'specialneeds' due to error 361
7.
> A subsequent backup operation may be slower/larger than normal."
> We also get several variations of this message for both the Log and Databa
se:
> 3041 :
> BACKUP failed to complete the command BACKUP LOG [specialneeds] TO DI
SK =
> N'E:\SQL Backup\specialneeds\specialneeds_db_2005
06190202.BAK', DISK =
> N'E:\SQL Backup\specialneeds\specialneeds_Tlog.bak' WITH INIT , NOUNLOAD
,
> NAME = N'specialneeds backup', NOSKIP , STATS = 10, NOFORMAT
> We have 30 or so other databases, some larger some smaller, running on the
> same SQL Server and none of them are having problems backing up. Thanks fo
r
> your help!
>|||Mike,
Here's what it reported:
DBCC results for 'specialneeds'.
DBCC results for 'sysobjects'.
There are 245 rows in 6 pages for object 'sysobjects'.
DBCC results for 'sysindexes'.
There are 283 rows in 12 pages for object 'sysindexes'.
DBCC results for 'syscolumns'.
There are 1167 rows in 25 pages for object 'syscolumns'.
DBCC results for 'systypes'.
There are 26 rows in 1 pages for object 'systypes'.
DBCC results for 'syscomments'.
There are 220 rows in 19 pages for object 'syscomments'.
DBCC results for 'sysfiles1'.
There are 2 rows in 1 pages for object 'sysfiles1'.
DBCC results for 'syspermissions'.
There are 18 rows in 1 pages for object 'syspermissions'.
DBCC results for 'sysusers'.
There are 13 rows in 1 pages for object 'sysusers'.
DBCC results for 'sysproperties'.
There are 0 rows in 0 pages for object 'sysproperties'.
DBCC results for 'sysdepends'.
There are 571 rows in 4 pages for object 'sysdepends'.
DBCC results for 'sysreferences'.
There are 2 rows in 1 pages for object 'sysreferences'.
DBCC results for 'sysfulltextcatalogs'.
There are 0 rows in 1 pages for object 'sysfulltextcatalogs'.
DBCC results for 'sysfulltextnotify'.
There are 0 rows in 0 pages for object 'sysfulltextnotify'.
DBCC results for 'sysfilegroups'.
There are 1 rows in 1 pages for object 'sysfilegroups'.
DBCC results for 'leAuditCollectConfigVars'.
There are 3 rows in 1 pages for object 'leAuditCollectConfigVars'.
DBCC results for 'leAuditCollectDatabases'.
There are 59 rows in 1 pages for object 'leAuditCollectDatabases'.
DBCC results for 'leAuditCollectNotification'.
There are 1 rows in 1 pages for object 'leAuditCollectNotification'.
DBCC results for 'leAuditCollectAlerts'.
There are 5 rows in 1 pages for object 'leAuditCollectAlerts'.
DBCC results for 'affiliatelog'.
There are 0 rows in 1 pages for object 'affiliatelog'.
DBCC results for 'billing'.
There are 0 rows in 1 pages for object 'billing'.
DBCC results for 'configuration'.
There are 355 rows in 3 pages for object 'configuration'.
DBCC results for 'configuration1'.
There are 0 rows in 1 pages for object 'configuration1'.
DBCC results for 'coupons'.
There are 9 rows in 1 pages for object 'coupons'.
DBCC results for 'customerprices'.
There are 0 rows in 1 pages for object 'customerprices'.
DBCC results for 'customers'.
There are 1083 rows in 34 pages for object 'customers'.
DBCC results for 'downloads'.
There are 0 rows in 1 pages for object 'downloads'.
DBCC results for 'gifts'.
There are 0 rows in 1 pages for object 'gifts'.
DBCC results for 'hackers'.
There are 1 rows in 1 pages for object 'hackers'.
DBCC results for 'languages'.
There are 671 rows in 6 pages for object 'languages'.
DBCC results for 'mycompany'.
There are 0 rows in 1 pages for object 'mycompany'.
DBCC results for 'ordertracking'.
There are 0 rows in 1 pages for object 'ordertracking'.
DBCC results for 'pinnumbers'.
There are 0 rows in 1 pages for object 'pinnumbers'.
DBCC results for 'prodcategories'.
There are 2891 rows in 8 pages for object 'prodcategories'.
DBCC results for 'dtproperties'.
There are 0 rows in 1 pages for object 'dtproperties'.
DBCC results for 'prodfeatures'.
There are 0 rows in 1 pages for object 'prodfeatures'.
DBCC results for 'projects'.
There are 0 rows in 1 pages for object 'projects'.
DBCC results for 'quantitydiscounts'.
There are 0 rows in 1 pages for object 'quantitydiscounts'.
DBCC results for 'registrant'.
There are 0 rows in 1 pages for object 'registrant'.
DBCC results for 'registryitems'.
There are 0 rows in 1 pages for object 'registryitems'.
DBCC results for 'reviews'.
There are 0 rows in 1 pages for object 'reviews'.
DBCC results for 'savedcarts'.
There are 3 rows in 1 pages for object 'savedcarts'.
DBCC results for 'searchresults'.
There are 0 rows in 1 pages for object 'searchresults'.
DBCC results for 'shipmethods'.
There are 1 rows in 1 pages for object 'shipmethods'.
DBCC results for 'suppliers'.
There are 0 rows in 1 pages for object 'suppliers'.
DBCC results for 'tblaccess'.
There are 40 rows in 2 pages for object 'tblaccess'.
DBCC results for 'tbllog'.
There are 682 rows in 11 pages for object 'tbllog'.
DBCC results for 'orders'.
There are 997 rows in 71 pages for object 'orders'.
DBCC results for 'tbluser'.
There are 2 rows in 1 pages for object 'tbluser'.
DBCC results for 'templates'.
There are 0 rows in 1 pages for object 'templates'.
DBCC results for 'oitems'.
There are 2093 rows in 42 pages for object 'oitems'.
DBCC results for 'ipaddress'.
There are 8 rows in 1 pages for object 'ipaddress'.
DBCC results for 'privlabelproducts'.
There are 0 rows in 1 pages for object 'privlabelproducts'.
DBCC results for 'privatelabels'.
There are 1 rows in 1 pages for object 'privatelabels'.
DBCC results for 'privlabelproducts1'.
There are 32 rows in 3 pages for object 'privlabelproducts1'.
DBCC results for 'privatelabels1'.
There are 1 rows in 1 pages for object 'privatelabels1'.
DBCC results for 'privlabelproducts3'.
There are 0 rows in 1 pages for object 'privlabelproducts3'.
DBCC results for 'privatelabels3'.
There are 1 rows in 1 pages for object 'privatelabels3'.
DBCC results for 'categories'.
There are 39 rows in 1 pages for object 'categories'.
DBCC results for 'specialneeds.DIY_TEMPCOMMAND_TABLE'.
There are 0 rows in 0 pages for object 'specialneeds.DIY_TEMPCOMMAND_TABLE'.
DBCC results for 'spneeds_calendar'.
There are 10 rows in 1 pages for object 'spneeds_calendar'.
DBCC results for 'calendar'.
There are 13 rows in 1 pages for object 'calendar'.
DBCC results for 'spneeds_level'.
There are 12 rows in 1 pages for object 'spneeds_level'.
DBCC results for 'spneeds_author'.
There are 2892 rows in 18 pages for object 'spneeds_author'.
DBCC results for 'spneeds_subject'.
There are 39 rows in 1 pages for object 'spneeds_subject'.
DBCC results for 'spneeds_product'.
There are 2890 rows in 69 pages for object 'spneeds_product'.
DBCC results for 'disability_link_categories'.
There are 6 rows in 1 pages for object 'disability_link_categories'.
DBCC results for 'disability_link_images'.
There are 3 rows in 1 pages for object 'disability_link_images'.
DBCC results for 'disability_links'.
There are 16 rows in 1 pages for object 'disability_links'.
DBCC results for 'discount_codes'.
There are 1 rows in 1 pages for object 'discount_codes'.
DBCC results for 'spneeds_messages'.
There are 1 rows in 1 pages for object 'spneeds_messages'.
DBCC results for 'EmailContest'.
There are 434 rows in 8 pages for object 'EmailContest'.
DBCC results for 'products3'.
There are 3251 rows in 144 pages for object 'products3'.
DBCC results for 'products1'.
There are 3258 rows in 145 pages for object 'products1'.
DBCC results for 'products'.
There are 3258 rows in 151 pages for object 'products'.
DBCC results for 'affiliates'.
There are 1 rows in 1 pages for object 'affiliates'.
CHECKDB found 0 allocation errors and 0 consistency errors in database
'specialneeds'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Run a DBCC CHECKDB on this database and see what it reports.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "myaffee" wrote:
>

Problem with Intead of Insert Trigger

I'm having a rather weird problem with an Instead Of Insert trigger.
Its supposed to check to see if the record exists in the destination table,
and if so update it, otherwise insert the row in the table. All pretty
straightforward, but I'm getting weird behavior.
If the destination table is empty, each row gets properly inserted. If there
are records in the destination table, any that are found get properly
updated, but no new records get inserted.
Here is my table layout:
CREATE TABLE [dbo].[TestProspect] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[ProspectNumber] [int] NULL ,
[iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
And here is my trigger:
CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
INSTEAD OF INSERT
AS
--try to update the inserted record to an existing record
UPDATE p
SET
pLastName = ISNULL(i.LastName,''),
pFirstName = ISNULL(i.FirstName,''),
pPhone = ISNULL(i.Phone1,''),
Type = 'UPDATE'
FROM TestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName
WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
--if it isn't in the table, insert it
INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
'INSERT'
FROM INSERTED i
WHERE NOT EXISTS (
SELECT p.ProspectNumber
FROM TestProspect p INNER JOIN INSERTED i ON
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName)
I've tried all kinds of things, like wrapping it in an if-else clause or
rearranging the insert/update order, to no avail. Anybody see something that
I'm missing?
Thanks in advance.Verify that ANSI_NULLS was set ON when the trigger was created. Also, if
{iFirstName, iLastName, iPhone} is a key, then they shouldn't be nullable,
and you should create a unique constraint to ensure that you don't get any
duplicates.
"Brian Levine" <Brian Levine@.discussions.microsoft.com> wrote in message
news:462FFE90-CF5E-4575-B357-2B7D64E3B382@.microsoft.com...
> I'm having a rather weird problem with an Instead Of Insert trigger.
> Its supposed to check to see if the record exists in the destination
> table,
> and if so update it, otherwise insert the row in the table. All pretty
> straightforward, but I'm getting weird behavior.
> If the destination table is empty, each row gets properly inserted. If
> there
> are records in the destination table, any that are found get properly
> updated, but no new records get inserted.
> Here is my table layout:
> CREATE TABLE [dbo].[TestProspect] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProspectNumber] [int] NULL ,
> [iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> And here is my trigger:
> CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
> INSTEAD OF INSERT
> AS
> --try to update the inserted record to an existing record
> UPDATE p
> SET
> pLastName = ISNULL(i.LastName,''),
> pFirstName = ISNULL(i.FirstName,''),
> pPhone = ISNULL(i.Phone1,''),
> Type = 'UPDATE'
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName
> WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
> --if it isn't in the table, insert it
> INSERT INTO TestProspect
> SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
> 'INSERT'
> FROM INSERTED i
> WHERE NOT EXISTS (
> SELECT p.ProspectNumber
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName)
> I've tried all kinds of things, like wrapping it in an if-else clause or
> rearranging the insert/update order, to no avail. Anybody see something
> that
> I'm missing?
> Thanks in advance.|||Try this for the insert portion:
INSERT INTO TestProspect
SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
'INSERT'
FROM INSERTED i
WHERE NOT EXISTS (
SELECT 1
FROM TestProspect p WHERE
p.iPhone = i.Phone1 AND
p.iLastName = i.LastName AND
p.iFirstName = i.FirstName)
"Brian Levine" wrote:

> I'm having a rather weird problem with an Instead Of Insert trigger.
> Its supposed to check to see if the record exists in the destination table
,
> and if so update it, otherwise insert the row in the table. All pretty
> straightforward, but I'm getting weird behavior.
> If the destination table is empty, each row gets properly inserted. If the
re
> are records in the destination table, any that are found get properly
> updated, but no new records get inserted.
> Here is my table layout:
> CREATE TABLE [dbo].[TestProspect] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [ProspectNumber] [int] NULL ,
> [iFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iLastname] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [iPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pFirstName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pLastName] [char] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [pPhone] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> And here is my trigger:
> CREATE TRIGGER [tr_IMPRMSProspect] ON [dbo].[IMP_Prospect]
> INSTEAD OF INSERT
> AS
> --try to update the inserted record to an existing record
> UPDATE p
> SET
> pLastName = ISNULL(i.LastName,''),
> pFirstName = ISNULL(i.FirstName,''),
> pPhone = ISNULL(i.Phone1,''),
> Type = 'UPDATE'
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName
> WHERE p.ProspectNumber < 4000000 or p.ProspectNumber >= 4999999
> --if it isn't in the table, insert it
> INSERT INTO TestProspect
> SELECT i.ProspectNumber, i.FirstName, i.LastName, i.Phone1, '', '', '',
> 'INSERT'
> FROM INSERTED i
> WHERE NOT EXISTS (
> SELECT p.ProspectNumber
> FROM TestProspect p INNER JOIN INSERTED i ON
> p.iPhone = i.Phone1 AND
> p.iLastName = i.LastName AND
> p.iFirstName = i.FirstName)
> I've tried all kinds of things, like wrapping it in an if-else clause or
> rearranging the insert/update order, to no avail. Anybody see something th
at
> I'm missing?
> Thanks in advance.

problem with inserting or updating dates in mssql 2000

Hello.I've read many topics about this problem but i couldn't figure it out.
I use form where user must insert 2 dates using texboxes.-One is required and other is optional.
Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report.
Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?
question 2. What to do if user left optional texbox date empty.

I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)
and parameters @.date1, sqldbtype date =texbox1.textYou can get the date as;

DateTime myDate = DateTime.ParseExact(txtDate.Text, "dd.M.yyyy", System.Globalization.CultureInfo.InvariantCulture);

Now that you have your text box's date as a DateTime variable you can supply that to a SQL parameter query in any format you want, such as dd MMM yyyy

|||I understand that.
I wanna know how to change format of date wich is inserted as d.M.yyyy in texbox to yyyy.M.d because it is passed to sp parameter and it must be yyyy.M.d to do successful insert/update?

problem with inserting date in a datetime field

Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer.
That's my problem:
if the server is in english version, I have to insert date with this code:

DateTime.Today.ToString("MM/dd/yyyy")

instead if the server is in italian version, I have to insert date with this code:

DateTime.Today.ToString("dd/MM/yyyy")

Is there a way to insert a date in standard way, without knowing the server version?

bye and thanks in advanceI usually find that yyyy-mm-dd does the trick. But I can't swear it will always work. Actually what am I talking about, you should be using params and this won't be a problem.|||What if you insert the date in datetime format instead of converting it to string? Then maybe, just maybe sql server will recognize the format.|||>> What if you insert the date in datetime format instead of converting it to string? Then maybe, just maybe sql server will recognize the format.

You're on the road to SQL hell if you do that. What culture is your client, what culture is the database? Use params, there is hardly *ever* a reason not too almost always a problem with an alternative - unless you've written very well thought out DB classes.

Problem With Insert Using SQLDataSource

I have a table with 2 columns - "memberid" is a guid and "Interest" is a string. In an aspx page I want to read in all values of "Interest" for a given "memberid" and display approprite checkboxes as checked. The user can then change which boxes are checked and I want to record the new list of selected items in the table. I created a SQLDataSource (see below) but when I run it, I get this error:

========== Error ===========

Disallowed implicit conversion from data type sql_variant to data type uniqueidentifier, table 'DB_136571.dbo.gs_MemberInterests', column 'memberid'. Use the CONVERT function to run this query.

============================

The only use of this SQLDataSource is to delete all entries for a given user and then insert an entry for each checked checkbox. The code to set the session variables is as follows:

======== Code ============

Dim guidMemberidAs Guid =CType(user.ProviderUserKey, Guid)

Session("currmember") = guidMemberid

....

Session("currinterest") =CType(item.FindControl("CheckBox1"), CheckBox).Text

==========================

Any ideas of what I am doing wrong here?

========= SQLDataSource ==========

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:GoodSamSiteDB %>"

DeleteCommand="DELETE FROM [gs_MemberInterests] WHERE [memberid] = @.memberid"

InsertCommand="INSERT INTO [gs_MemberInterests] ([memberid], [Interest]) VALUES (@.memberid, @.Interest)"

SelectCommand="SELECT memberid, Interest FROM gs_MemberInterests WHERE (memberid = @.memberid) AND (Interest = @.interest)">

<DeleteParameters>

<asp:ParameterName="memberid"Type="Object"/>

</DeleteParameters>

<SelectParameters>

<asp:SessionParameterName="memberid"SessionField="currmember"Type="Object"/>

<asp:SessionParameterName="interest"SessionField="currinterest"/>

</SelectParameters>

<InsertParameters>

<asp:ParameterName="memberid"Type="Object"/>

<asp:ParameterName="Interest"Type="String"/>

</InsertParameters>

</asp:SqlDataSource>

=============================

Type="Object"|||

Motley wrote:

Type="Object"

Thanks. I assume you are referring to the fact that the "Type='Object'" has to be removed. I just found a reference to that bug. That cleared the problem.

sql

Problem with Insert trigger

I have an insert trigger that works 99.999% of the time. The trigger is
fired via a dts process that runs every 10 minutes which inserts data into a
ForecastTonnageChanges table. Based on changes to this
ForecastTonnageChanges, I want to update the live Forecast table. The
trigger fires this event. My basic logic in the trigger is:
Delete from Forecast
where exists (select *
from inserted
where <joining key columns> )
Insert into Forecast
select ...
from inserted
When this doesn't work, I am ending up with extra records in my forecast
table. I'm wondering if another insert has happened and between the delete
and the insert in the trigger, an additional row is in the inserted table.
Is this possible? I would think implied locking via the trigger would
prevent this. But, I'm grasping for straws here.
If you have ideas on a more reliable way to implement this... please share.Hi
A trigger is fired for each statement, therefore you will not get extra rows
in the inserted/deleted tables.
As you don't give DDL and all the trigger code it is hard to say where you
are going wrong, but you may want to use profiler to see what is happening.
You should also implement error handling to make sure that the statement
succeeds and all the relivent statements in the transaction are rolled back
if a failure occurs.
John
"Erin" wrote:
> I have an insert trigger that works 99.999% of the time. The trigger is
> fired via a dts process that runs every 10 minutes which inserts data into
a
> ForecastTonnageChanges table. Based on changes to this
> ForecastTonnageChanges, I want to update the live Forecast table. The
> trigger fires this event. My basic logic in the trigger is:
> Delete from Forecast
> where exists (select *
> from inserted
> where <joining key columns> )
> Insert into Forecast
> select ...
> from inserted
> When this doesn't work, I am ending up with extra records in my forecast
> table. I'm wondering if another insert has happened and between the delet
e
> and the insert in the trigger, an additional row is in the inserted table.
> Is this possible? I would think implied locking via the trigger would
> prevent this. But, I'm grasping for straws here.
> If you have ideas on a more reliable way to implement this... please share.[/color
]|||Without any more information, could you have a forecast VIEW of the
most current rows in the ForecastTonnageChanges table instead of
physically shuffling all this data around? You obviously have a time
stamp on the new data, so that should be easy enough.

Problem with INSERT Trigger

It's not so much a problem, as I don't know how to get around this
issue. We have applications that rely on the return value of a stored
procedure. This stored procedure inserts a record into a table that has
a trigger. The trigger calls a couple of stored procedures itself.

Due to the trigger, we have a return value of 0 coming in ahead of the
return value for the stored procedure we call directly. I've tested
this in Query Analyzer as well, and the same behavior applies.

In case I didn't describe it clearly:

Call storedproc1

storedproc1 inserts a record into table1

table1 has an INSERT trigger

expected behavior is: storedproc1 returns value1 and value2

actual behavior is: storedproc1 returns 0, then returns value 1 and
value 2(timothy.alvis@.gmail.com) writes:

Quote:

Originally Posted by

It's not so much a problem, as I don't know how to get around this
issue. We have applications that rely on the return value of a stored
procedure. This stored procedure inserts a record into a table that has
a trigger. The trigger calls a couple of stored procedures itself.
>
Due to the trigger, we have a return value of 0 coming in ahead of the
return value for the stored procedure we call directly. I've tested
this in Query Analyzer as well, and the same behavior applies.
>
In case I didn't describe it clearly:
>
Call storedproc1
>
storedproc1 inserts a record into table1
>
table1 has an INSERT trigger
>
expected behavior is: storedproc1 returns value1 and value2
>
actual behavior is: storedproc1 returns 0, then returns value 1 and
value 2

Returns? This needs some clarification. A stored procedure can return
data in three different ways:
o Result set
o Output parameters
o Return value. as in EXEC @.ret = some_sp

Which do you mean?

Overall, it would help if you posted the code of the procedure, so we know
what you are talking about. Please also include the output when run the
procedure in Query Analyzer.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Problem with insert Time to database

Dear all,
I have insert the time to my database but it appear also the date by default.
This is my code in C# :
DateTime date = DateTime.Now;
int hour = date.Hour;
int minute = date.Minute;
int second = date.Second;
string requestedTime = hour+":"+minute+":"+second;
string query = "INSERT INTO workorder([timeRequest]) VALUES("'"+requestTime+"'");
in my database , the column timeRequest appear :1/1/1900 11:59:05 AM
I dont want the date by default to appear, i want only the time like : 11:59:05 AM in my database,
Anyone can help me?
Best Regards,
Moniphal

Use parameterized command to insert data to database. By example:

SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO workorder(timeRequest) VALUES (@.TimeRequest)";
cmd.CommandType = CommandType.Text
cmd.Parameters.Add(new SqlParameter("@.TimeRequest",myDateTime));
cmd.ExecuteNonQuery();

Problem with INSERT Statement.

I have an insert statement thats causing me some issues.
Dim strname As String
Dim myname As String
myname = My.User.Name
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES(" & myname & ")"
How do I do the syntax correctly for it to be inserted into my SQL Server correctly?

Thanks,
Tom

I'm not going to answer your question directly because what you are doing is a great security risk. You risk your database coming under a SQL Injection attack so I am not going to give you the quick fix. I am going to tell you how to fix your code AND plug the gaping security hole that you have.
Your code injects the string myname directly into the SQL statement. This should be replaced with a parameter so that the attack surface of the application is reduced.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES(@.myname)"
Dim cmd as SqlCommand
cmd = New SqlCommand(strname, connection)
cmd.Parameters.Add("@.myname", myname)
cmd.ExecuteNonQuery()
I have replaced your injection with a parameter name (@.myname). Then in the command object I add a parameter with the same name and give it the value it needs.
Finally, here is an article aboutSQL Injection attacks and how to prevent them.

|||Om Sri Sai Ram
Forgot the single '. Use following statement.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES('" & myname & "')"
Thanks,
Ram|||

potturi_rp wrote:

Om Sri Sai Ram
Forgot the single '. Use following statement.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES('" & myname & "')"
Thanks,
Ram

Thats the same thing I had.

But I follow on not using the pure injection method.

Thanks,

Tom

Problem with Insert statement - Arithmetic overflow

Hi
I'm trying to do a script that can combine data from different databases and
then present them in a list to for some payment purposes. I have little
problem though with a small bit of the script.
The following small part of the script works fine :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR , belb
This is part works fine - I get 1731 records inserted into my temp table.
I'd like to get the records grouped a little bit further though, so I've
tried with the modification below :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
Here I just remove the "belb" column in the GROUP BY clause and now I get
the following error :
"Arithmetic overflow error converting float to data type numeric.
The statement has been terminated."
I don't quite understand why I get this error message since it's the same
values I'm trying to insert in both cases.
If I just do a -
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
- I get 129 records which looks fine and looks like what I want.
The spe_temp..LonSum table is created as -
CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
I hope that some of you can shed some light on this?
Regards
Steen
The greatest amount you can store in a decimal(9,2) column is 9,999,999.99.
Is the sum for one of the CVR's you group by greater than that?
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
> Hi
> I'm trying to do a script that can combine data from different databases
> and
> then present them in a list to for some payment purposes. I have little
> problem though with a small bit of the script.
> The following small part of the script works fine :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR , belb
> This is part works fine - I get 1731 records inserted into my temp table.
> I'd like to get the records grouped a little bit further though, so I've
> tried with the modification below :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR
> Here I just remove the "belb" column in the GROUP BY clause and now I get
> the following error :
> "Arithmetic overflow error converting float to data type numeric.
> The statement has been terminated."
> I don't quite understand why I get this error message since it's the same
> values I'm trying to insert in both cases.
> If I just do a -
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR
> - I get 129 records which looks fine and looks like what I want.
> The spe_temp..LonSum table is created as -
> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
> CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
> I hope that some of you can shed some light on this?
> Regards
> Steen
>
|||Hi Jacco
You're right that there could be an issue here. The biggest value seems to
be 33.213.057,179999199 but even though I change the [Amount] and [Belb]
definition for my temp table to e.g. (Decimal 25,12) it still gives me the
error.
Regards
Steen
Jacco Schalkwijk wrote:[vbcol=seagreen]
> The greatest amount you can store in a decimal(9,2) column is
> 9,999,999.99. Is the sum for one of the CVR's you group by greater
> than that?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
|||Can you post the result of:
SELECT CVR, SUM([Amount]), SUM([belb])
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
?
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi Jacco
> You're right that there could be an issue here. The biggest value seems to
> be 33.213.057,179999199 but even though I change the [Amount] and [Belb]
> definition for my temp table to e.g. (Decimal 25,12) it still gives me the
> error.
> Regards
> Steen
>
> Jacco Schalkwijk wrote:
>
|||Hi Jacco
I got the problem solved - and it was the definition of the Decimal column
that wasn't big enough. First time I changed the definition I just changed
the syntax for CREATE TABLE.... - but I actually missed to re-create the
table.....doohhhh.....;-).
Thanks for you help.....it was spot on....
Regards
Steen
Jacco Schalkwijk wrote:[vbcol=seagreen]
> Can you post the result of:
> SELECT CVR, SUM([Amount]), SUM([belb])
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
> AND Belb <>0
> GROUP BY CVR
> ?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
sql

Problem with Insert statement - Arithmetic overflow

Hi
I'm trying to do a script that can combine data from different databases and
then present them in a list to for some payment purposes. I have little
problem though with a small bit of the script.
The following small part of the script works fine :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR , belb
This is part works fine - I get 1731 records inserted into my temp table.
I'd like to get the records grouped a little bit further though, so I've
tried with the modification below :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
Here I just remove the "belb" column in the GROUP BY clause and now I get
the following error :
"Arithmetic overflow error converting float to data type numeric.
The statement has been terminated."
I don't quite understand why I get this error message since it's the same
values I'm trying to insert in both cases.
If I just do a -
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
- I get 129 records which looks fine and looks like what I want.
The spe_temp..LonSum table is created as -
CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
I hope that some of you can shed some light on this?
Regards
SteenThe greatest amount you can store in a decimal(9,2) column is 9,999,999.99.
Is the sum for one of the CVR's you group by greater than that?
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
> Hi
> I'm trying to do a script that can combine data from different databases
> and
> then present them in a list to for some payment purposes. I have little
> problem though with a small bit of the script.
> The following small part of the script works fine :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR , belb
> This is part works fine - I get 1731 records inserted into my temp table.
> I'd like to get the records grouped a little bit further though, so I've
> tried with the modification below :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR
> Here I just remove the "belb" column in the GROUP BY clause and now I get
> the following error :
> "Arithmetic overflow error converting float to data type numeric.
> The statement has been terminated."
> I don't quite understand why I get this error message since it's the same
> values I'm trying to insert in both cases.
> If I just do a -
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR
> - I get 129 records which looks fine and looks like what I want.
> The spe_temp..LonSum table is created as -
> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
> CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
> I hope that some of you can shed some light on this?
> Regards
> Steen
>|||Hi Jacco
You're right that there could be an issue here. The biggest value seems to
be 33.213.057,179999199 but even though I change the [Amount] and [B
elb]
definition for my temp table to e.g. (Decimal 25,12) it still gives me the
error.
Regards
Steen
Jacco Schalkwijk wrote:[vbcol=seagreen]
> The greatest amount you can store in a decimal(9,2) column is
> 9,999,999.99. Is the sum for one of the CVR's you group by greater
> than that?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...|||Can you post the result of:
SELECT CVR, SUM([Amount]), SUM([belb])
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
?
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi Jacco
> You're right that there could be an issue here. The biggest value seems to
> be 33.213.057,179999199 but even though I change the [Amount] and [
;Belb]
> definition for my temp table to e.g. (Decimal 25,12) it still gives me the
> error.
> Regards
> Steen
>
> Jacco Schalkwijk wrote:
>|||Hi Jacco
I got the problem solved - and it was the definition of the Decimal column
that wasn't big enough. First time I changed the definition I just changed
the syntax for CREATE TABLE.... - but I actually missed to re-create the
table.....doohhhh.....;-).
Thanks for you help.....it was spot on....
Regards
Steen
Jacco Schalkwijk wrote:[vbcol=seagreen]
> Can you post the result of:
> SELECT CVR, SUM([Amount]), SUM([belb])
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
> AND Belb <>0
> GROUP BY CVR
> ?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...

Problem with Insert statement - Arithmetic overflow

Hi
I'm trying to do a script that can combine data from different databases and
then present them in a list to for some payment purposes. I have little
problem though with a small bit of the script.
The following small part of the script works fine :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([beløb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Beløb <>0
GROUP BY CVR , beløb
This is part works fine - I get 1731 records inserted into my temp table.
I'd like to get the records grouped a little bit further though, so I've
tried with the modification below :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([beløb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Beløb <>0
GROUP BY CVR
Here I just remove the "beløb" column in the GROUP BY clause and now I get
the following error :
"Arithmetic overflow error converting float to data type numeric.
The statement has been terminated."
I don't quite understand why I get this error message since it's the same
values I'm trying to insert in both cases.
If I just do a -
SELECT SUM([Amount]), SUM([beløb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Beløb <>0
GROUP BY CVR
- I get 129 records which looks fine and looks like what I want.
The spe_temp..LonSum table is created as -
CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
I hope that some of you can shed some light on this?
Regards
SteenThe greatest amount you can store in a decimal(9,2) column is 9,999,999.99.
Is the sum for one of the CVR's you group by greater than that?
--
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
> Hi
> I'm trying to do a script that can combine data from different databases
> and
> then present them in a list to for some payment purposes. I have little
> problem though with a small bit of the script.
> The following small part of the script works fine :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([beløb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Beløb <>0
> GROUP BY CVR , beløb
> This is part works fine - I get 1731 records inserted into my temp table.
> I'd like to get the records grouped a little bit further though, so I've
> tried with the modification below :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([beløb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Beløb <>0
> GROUP BY CVR
> Here I just remove the "beløb" column in the GROUP BY clause and now I get
> the following error :
> "Arithmetic overflow error converting float to data type numeric.
> The statement has been terminated."
> I don't quite understand why I get this error message since it's the same
> values I'm trying to insert in both cases.
> If I just do a -
> SELECT SUM([Amount]), SUM([beløb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Beløb <>0
> GROUP BY CVR
> - I get 129 records which looks fine and looks like what I want.
> The spe_temp..LonSum table is created as -
> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
> CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
> I hope that some of you can shed some light on this?
> Regards
> Steen
>|||Hi Jacco
You're right that there could be an issue here. The biggest value seems to
be 33.213.057,179999199 but even though I change the [Amount] and [Beløb]
definition for my temp table to e.g. (Decimal 25,12) it still gives me the
error.
Regards
Steen
Jacco Schalkwijk wrote:
> The greatest amount you can store in a decimal(9,2) column is
> 9,999,999.99. Is the sum for one of the CVR's you group by greater
> than that?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
>> Hi
>> I'm trying to do a script that can combine data from different
>> databases and
>> then present them in a list to for some payment purposes. I have
>> little problem though with a small bit of the script.
>> The following small part of the script works fine :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR , beløb
>> This is part works fine - I get 1731 records inserted into my temp
>> table. I'd like to get the records grouped a little bit further
>> though, so I've tried with the modification below :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> Here I just remove the "beløb" column in the GROUP BY clause and now
>> I get the following error :
>> "Arithmetic overflow error converting float to data type numeric.
>> The statement has been terminated."
>> I don't quite understand why I get this error message since it's the
>> same values I'm trying to insert in both cases.
>> If I just do a -
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> - I get 129 records which looks fine and looks like what I want.
>> The spe_temp..LonSum table is created as -
>> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb
>> Decimal(9,2), CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
>> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
>> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
>> I hope that some of you can shed some light on this?
>> Regards
>> Steen|||Can you post the result of:
SELECT CVR, SUM([Amount]), SUM([beløb])
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Beløb <>0
GROUP BY CVR
?
--
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi Jacco
> You're right that there could be an issue here. The biggest value seems to
> be 33.213.057,179999199 but even though I change the [Amount] and [Beløb]
> definition for my temp table to e.g. (Decimal 25,12) it still gives me the
> error.
> Regards
> Steen
>
> Jacco Schalkwijk wrote:
>> The greatest amount you can store in a decimal(9,2) column is
>> 9,999,999.99. Is the sum for one of the CVR's you group by greater
>> than that?
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
>> Hi
>> I'm trying to do a script that can combine data from different
>> databases and
>> then present them in a list to for some payment purposes. I have
>> little problem though with a small bit of the script.
>> The following small part of the script works fine :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR , beløb
>> This is part works fine - I get 1731 records inserted into my temp
>> table. I'd like to get the records grouped a little bit further
>> though, so I've tried with the modification below :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> Here I just remove the "beløb" column in the GROUP BY clause and now
>> I get the following error :
>> "Arithmetic overflow error converting float to data type numeric.
>> The statement has been terminated."
>> I don't quite understand why I get this error message since it's the
>> same values I'm trying to insert in both cases.
>> If I just do a -
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> - I get 129 records which looks fine and looks like what I want.
>> The spe_temp..LonSum table is created as -
>> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb
>> Decimal(9,2), CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
>> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
>> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
>> I hope that some of you can shed some light on this?
>> Regards
>> Steen
>|||Hi Jacco
I got the problem solved - and it was the definition of the Decimal column
that wasn't big enough. First time I changed the definition I just changed
the syntax for CREATE TABLE.... - but I actually missed to re-create the
table.....doohhhh.....;-).
Thanks for you help.....it was spot on....
Regards
Steen
Jacco Schalkwijk wrote:
> Can you post the result of:
> SELECT CVR, SUM([Amount]), SUM([beløb])
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
> AND Beløb <>0
> GROUP BY CVR
> ?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> Hi Jacco
>> You're right that there could be an issue here. The biggest value
>> seems to be 33.213.057,179999199 but even though I change the
>> [Amount] and [Beløb] definition for my temp table to e.g. (Decimal
>> 25,12) it still gives me the error.
>> Regards
>> Steen
>>
>> Jacco Schalkwijk wrote:
>> The greatest amount you can store in a decimal(9,2) column is
>> 9,999,999.99. Is the sum for one of the CVR's you group by greater
>> than that?
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
>> Hi
>> I'm trying to do a script that can combine data from different
>> databases and
>> then present them in a list to for some payment purposes. I have
>> little problem though with a small bit of the script.
>> The following small part of the script works fine :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR , beløb
>> This is part works fine - I get 1731 records inserted into my temp
>> table. I'd like to get the records grouped a little bit further
>> though, so I've tried with the modification below :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> Here I just remove the "beløb" column in the GROUP BY clause and
>> now I get the following error :
>> "Arithmetic overflow error converting float to data type numeric.
>> The statement has been terminated."
>> I don't quite understand why I get this error message since it's
>> the same values I'm trying to insert in both cases.
>> If I just do a -
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> - I get 129 records which looks fine and looks like what I want.
>> The spe_temp..LonSum table is created as -
>> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb
>> Decimal(9,2), CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
>> Periode Int, Sats int, Areal_total numeric(9,2),
>> Areal_andelsbolig numeric(9,2), Procent numeric(9,2),
>> LoenSumBeloeb decimal(9,2) )
>> I hope that some of you can shed some light on this?
>> Regards
>> Steen