Field overflow and Log grow

Hi,
I am using SQL Server 2000.
One user database contains a table as following:
CREATE TABLE Events..Audit_Sub (
[RecID] [bigint] NOT NULL ,
[Name] [varchar] (100) NULL ,
[Value] [varchar] (1024) NULL
) ON [PRIMARY]
Sometimes the application writes to the table a record that overflows the
size of the Value field (actually because of an error in the new code, the
appilcation attempts to write about 5Kb to the Valaue field).
The fact is:
- No error is detected on SQL Server, data is written to tha table, it is
visible by select, it is just truncated to the field size (1Kb).
- In the meanwhile it appears that DB log begins growing: the things are not
directly dependent, just somewhat later the log begins growing, but no error
is found in SQL errorlog.
- Later on transaction log cannot be backup up, data are no more written to
DB, but then it is too late to understand the reason why.
The question is:
- In which way may the two things (overflow and log grow) be related?
- What really happens on SQL server when data overflow occurs? How does it
handle?
Thanks in advance,
MRMarco
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - In which way may the two things (overflow and log grow) be related?
It does not matter whether or not overflow occured. the log file grows up
and it is not truncated unless you have SIMPLE recovery mode the database
set
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
create table t (c1 tinyint, c2 varchar (5))
--owerflow on c1 column
insert into t values(4545745454545,'a')
--Server: Msg 8115, Level 16, State 2, Line 1
--Arithmetic overflow error converting expression to data type tinyint.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
--now insert much more characters than you defined for c2 columnn
insert into t values(1,'asgtbvtybvtg')
--Server: Msg 8152, Level 16, State 9, Line 1
--String or binary data would be truncated.
--The statement has been terminated.
select * from t
--(0 row(s) affected)
"Marco Roda" <mrtest@.amdosoft.com> wrote in message
news:e8t656$le6$1@.ss408.t-com.hr...
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are
> not
> directly dependent, just somewhat later the log begins growing, but no
> error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written
> to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||Hi
At a guess you have the ANSI_WARNINGS setting off as "When OFF, data is
truncated to the size of the column and the statement succeeds. " e.g
SET ANSI_WARNINGS ON
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated.
The statement has been terminated.
----
Transaction Rolled Back Error Status: 8152
(1 row(s) affected)
col1
--
(0 row(s) affected)
*/
SET ANSI_WARNINGS OFF
DECLARE @.error int
CREATE TABLE #tmp ( col1 char(1) NOT NULL )
BEGIN TRANSACTION
INSERT INTO #tmp ( col1 ) values ( 'AA' )
SET @.error = @.@.ERROR
IF @.error <> 0
BEGIN
SELECT 'Transaction Rolled Back Error Status: ' + CAST(@.error as varchar(30))
ROLLBACK TRANSACTIOn
END
ELSE
BEGIN
PRINT 'Transaction Comitted'
COMMIT TRANSACTION
END
GO
SELECT * from #tmp
GO
DROP TABLE #tmp
GO
/*
(1 row(s) affected)
Transaction Comitted
col1
--
A
(1 row(s) affected)
*/
although with your log file growing it may be that you have detected an
error and not rolled back the transaction, use DBCC OPENTRAN to view open
transactions.
John
"Marco Roda" wrote:
> Hi,
> I am using SQL Server 2000.
> One user database contains a table as following:
> CREATE TABLE Events..Audit_Sub (
> [RecID] [bigint] NOT NULL ,
> [Name] [varchar] (100) NULL ,
> [Value] [varchar] (1024) NULL
> ) ON [PRIMARY]
> Sometimes the application writes to the table a record that overflows the
> size of the Value field (actually because of an error in the new code, the
> appilcation attempts to write about 5Kb to the Valaue field).
> The fact is:
> - No error is detected on SQL Server, data is written to tha table, it is
> visible by select, it is just truncated to the field size (1Kb).
> - In the meanwhile it appears that DB log begins growing: the things are not
> directly dependent, just somewhat later the log begins growing, but no error
> is found in SQL errorlog.
> - Later on transaction log cannot be backup up, data are no more written to
> DB, but then it is too late to understand the reason why.
> The question is:
> - In which way may the two things (overflow and log grow) be related?
> - What really happens on SQL server when data overflow occurs? How does it
> handle?
> Thanks in advance,
> MR
>
>
>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uozopRApGHA.4996@.TK2MSFTNGP05.phx.gbl...
> Marco
> > - In the meanwhile it appears that DB log begins growing: the things are
> > not
> > directly dependent, just somewhat later the log begins growing, but no
> > error
> > is found in SQL errorlog.
> > - In which way may the two things (overflow and log grow) be related?
>
> It does not matter whether or not overflow occured. the log file grows up
> and it is not truncated unless you have SIMPLE recovery mode the database
> set
>
> > - What really happens on SQL server when data overflow occurs? How does
it
> > handle?
>
> create table t (c1 tinyint, c2 varchar (5))
> --owerflow on c1 column
> insert into t values(4545745454545,'a')
> --Server: Msg 8115, Level 16, State 2, Line 1
> --Arithmetic overflow error converting expression to data type tinyint.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
> --now insert much more characters than you defined for c2 columnn
> insert into t values(1,'asgtbvtybvtg')
> --Server: Msg 8152, Level 16, State 9, Line 1
> --String or binary data would be truncated.
> --The statement has been terminated.
> select * from t
> --(0 row(s) affected)
>
The fact is: when the application attempts writing more data, data is REALLY
WRITTEN (even if truncated), and NO ERROR is thrown.
- Why did not get error?
- May the overflow be a reason why the log is growing?

Field ntext in the table only stores 256 characters

declare @.mensagem varchar(8000)
CREATE TABLE #Mensagem (mensagem text)
set @.mensagem = 'Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa dsfsdfsdf sdfsdfsdf 111111111 00 '
insert into #Mensagem select @.mensagem
select * from #MensagemHi Frank.
I ran that under SQL2KEE & it produced a perfect result..
The Query Analyser truncates column output to 256 characters by default, so
try setting your "Maximum characters per column" to something higher (eg
8000) under Query Analyser's "Tools/Options" menu, "Results" tab.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:#ZTWHAgjDHA.2592@.TK2MSFTNGP10.phx.gbl...
>
>
> declare @.mensagem varchar(8000)
> CREATE TABLE #Mensagem (mensagem text)
> set @.mensagem =>
'Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
>
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa dsfsdfsdf sdfsdfsdf 111111111 00 '
> insert into #Mensagem select @.mensagem
> select * from #Mensagem
>

Field msrepl_tran_version added

Hello there
I've create transacional replication from two databases. At the publisher
new field added to all the tables i mark as replicated: msrepl_tran_version
It cause damage to my database. What i need to do to ged rid of them? and
what i need to do so they won't be created again?
' 03-5611606
' 050-7709399
: roy@.atidsm.co.il
You have to run a script to drop them. These columns are using by immediate
updating or queued updating publications. Have you need of these
publications?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23EzlsbLAGHA.3296@.TK2MSFTNGP12.phx.gbl...
> Hello there
> I've create transacional replication from two databases. At the publisher
> new field added to all the tables i mark as replicated:
> msrepl_tran_version
> It cause damage to my database. What i need to do to ged rid of them? and
> what i need to do so they won't be created again?
> --
>
> ' 03-5611606
> ' 050-7709399
> : roy@.atidsm.co.il
>
|||Whell Hilary:
When i tried to delete the field by this code?
ALTER TABLE Client
DROP COLUMN msrepl_tran_version
I got an error:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF__Client__msrepl_t__02FED618' is dependent on column
'msrepl_tran_version'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
objects access this column.
how can i delete these records?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
> You have to run a script to drop them. These columns are using by
> immediate updating or queued updating publications. Have you need of these
> publications?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%23EzlsbLAGHA.3296@.TK2MSFTNGP12.phx.gbl...
>
|||You will have to drop the constraints before dropping the column.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23ePeTNMAGHA.3864@.TK2MSFTNGP12.phx.gbl...
> Whell Hilary:
> When i tried to delete the field by this code?
> ALTER TABLE Client
> DROP COLUMN msrepl_tran_version
> I got an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__Client__msrepl_t__02FED618' is dependent on column
> 'msrepl_tran_version'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
> objects access this column.
> how can i delete these records?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
>
|||I'm sorry with my language. it's one constraint per msrepl_tran_version
column, not constraints...
Sorry.
|||There are default contraints on that field. You'll have to drop those
constraints bfore dropping the column itself. you should do a select on
sysobjects to find the default constraints like '%df_%_msrepl%'. i would
assume, replication is not in place(immediate updating or queued updating).
Once you drop the constraint, it lets u drop the column.
HTH
Tejas
"Roy Goldhammer" wrote:

> Whell Hilary:
> When i tried to delete the field by this code?
> ALTER TABLE Client
> DROP COLUMN msrepl_tran_version
> I got an error:
> Server: Msg 5074, Level 16, State 1, Line 1
> The object 'DF__Client__msrepl_t__02FED618' is dependent on column
> 'msrepl_tran_version'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN msrepl_tran_version failed because one or more
> objects access this column.
> how can i delete these records?
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23y6ZYGMAGHA.2656@.tk2msftngp13.phx.gbl...
>
>
|||Whell Tejas:
It gives me new error: ALTER TABLE DROP COLUMN failed because
'msrepl_tran_version' is currently replicated.
Now what i need to get rid of that, or what i need to cause it not be
created again?
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:32CD928E-5D0B-45EF-8A07-85C65A30BA65@.microsoft.com...[vbcol=seagreen]
> There are default contraints on that field. You'll have to drop those
> constraints bfore dropping the column itself. you should do a select on
> sysobjects to find the default constraints like '%df_%_msrepl%'. i would
> assume, replication is not in place(immediate updating or queued
> updating).
> Once you drop the constraint, it lets u drop the column.
> HTH
> Tejas
> "Roy Goldhammer" wrote:
|||You will have to drop your subscription before trying to make these changes.
Is this on the publisher or subscriber?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:essSUXVAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> Whell Tejas:
> It gives me new error: ALTER TABLE DROP COLUMN failed because
> 'msrepl_tran_version' is currently replicated.
> Now what i need to get rid of that, or what i need to cause it not be
> created again?
> "Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
> news:32CD928E-5D0B-45EF-8A07-85C65A30BA65@.microsoft.com...
>
|||Whell Hilary:
It is on the Publisher
And it also addes me new tables Conflict...
I've chosed to use Transactional replication, and it act like merge
replication.
This is realy bad.
What i need to do to use replication without changing the database
Publisher?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:#K1WEuYAGHA.3140@.TK2MSFTNGP14.phx.gbl...
> You will have to drop your subscription before trying to make these
changes.[vbcol=seagreen]
> Is this on the publisher or subscriber?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:essSUXVAGHA.2036@.TK2MSFTNGP14.phx.gbl...
would[vbcol=seagreen]
them?
>

field mapping

Hi all,
I have written many stored procedures in our database to import data from
another database. Now I need to create a report that lists the source table
and column names and their corresponding destination table and column names.
I think it will be difficult and time-consuming for me to open each
procedure and copy this information manually. Are there any procedures or
queries that can help me in generating this report.
Thanks in advance.Hi,
maybe this will help you ...
SELECT OBJECT_NAME(id) AS Source, OBJECT_NAME(depid) AS Uses
FROM dbo.sysDepends WHERE id = OBJECT_ID('proc_sys_InsertPropertyValue'
)
All dependencies of objects are stored in the relation dbo.sysdepends!
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htmsql

Field Definition report results no record

Hi guys, sorry my poor english,
I'm trying to create a simple example of a report that receive the recordset from my VB6 application. The recordset have only one field like the rpt and the ttx file.

My project use these references:
Microsoft Remote Data Object 2.0
Crystal Reports ActiveX Designer Run Time Library 10.0
Crystal Report Viewer Control

To reproduce the example in VB, create a new project, include the references, create a command button and a CRViewer and after paste the code inside the form.

--- Code ---
Option Explicit
Dim cnFin As RDO.rdoConnection
Dim Crystal As CRAXDRT.Application

Private Sub Command1_Click()
Dim rsFin As RDO.rdoResultset
Dim rpFin As Report

'Opening table
Set rsFin = cnFin.OpenResultset( _
"SELECT ALL FinCPSequencia " & _
"FROM FinMovtoPagar " & _
"WHERE CodEstab = 5", RDO.rdOpenKeyset, RDO.rdConcurRowVer)
If rsFin.RowCount > 0 Then
'Opens report
Set rpFin = Crystal.OpenReport(App.Path & "\Test.rpt")
'Defines parameters
rpFin.ParameterFields.Item(1).AddCurrentValue "Title"
'Set DataSoruce
rpFin.Database.SetDataSource rsFin
'Shows report
With CRViewer1
.ReportSource = rpFin
.ViewReport
End With
Else
MsgBox "No record"
End If
End Sub
Private Sub Form_Load()
'Connecting with database
Set cnFin = RDO.rdoEnvironments(0).OpenConnection("", , , _
"DRIVER=SQL Server;SERVER=127.0.0.1;UID=BNP;PWD=password;APP=BNPGest;WSID=NBNIETTO;DATABASE=BNPGEST_DESENV;LANGUAGE=us_english;Network=DBMSSOCN;Address=127.0.0.1,1433")
'Initiating Crystal
Set Crystal = New CRAXDRT.Application
End SubThe same example using ADO works fine, but I have to keep the project with RDO.
I found this article: http://support.businessobjects.com/library/kbase/articles/c2016624.asp?ref=devzone_xiresources_tipsandtricks , But the report yet results no records even using the batchclient. Does anyone can help me?

Few Design and Programming Questions

I have a few questions regarding reports that I haven't found answers after a
little searching. Thanks for any help!
1. I need to create a few reports with some repeating pages. For example, in
one of them I need to have a cover page, then couple content pages, then a
summary page. I need to double print the content pages. There must be a
better way to achive this than manually copy-and-paste to duplicate those
pages in the report design, is there?
2. Is there a way to embed a PDF file into the report somehow? Say, at the
end of each report, I need to include a few pages of static information. Can
I somehow have the report server to pull out a PDF file instead of putting
those text into the report design?
3. Can I include other reports in a report?
4. Is there a way to control printing of the reports after I exported them
into PDF for printing? Say, I want page 3 and 4 print on both side of the
paper. I don't think this is possible, but asking to make sure.
5. Can I control the size of the parameter input area of the report at the
top portion of the report server? I need to disply 8 input boxes. Users need
to scroll down in the parameter area to see the last 2.
Thanks!No reply yet : ]
Does anyone know something about my question #2?
Or any suggestions on how to deal with complexed text/table formatting with
VS .NET 2003 report designer? It is so hard to make things look right, that's
why I wanted to just "attach" a PDF or word file for those static text.
"Xonic" wrote:
> I have a few questions regarding reports that I haven't found answers after a
> little searching. Thanks for any help!
> 1. I need to create a few reports with some repeating pages. For example, in
> one of them I need to have a cover page, then couple content pages, then a
> summary page. I need to double print the content pages. There must be a
> better way to achive this than manually copy-and-paste to duplicate those
> pages in the report design, is there?
> 2. Is there a way to embed a PDF file into the report somehow? Say, at the
> end of each report, I need to include a few pages of static information. Can
> I somehow have the report server to pull out a PDF file instead of putting
> those text into the report design?
> 3. Can I include other reports in a report?
> 4. Is there a way to control printing of the reports after I exported them
> into PDF for printing? Say, I want page 3 and 4 print on both side of the
> paper. I don't think this is possible, but asking to make sure.
> 5. Can I control the size of the parameter input area of the report at the
> top portion of the report server? I need to disply 8 input boxes. Users need
> to scroll down in the parameter area to see the last 2.
> Thanks!

Fetching data from IBM DB2 to SQL Server 2005

Hi,

I am trying to fetch data from IBM DB2 to SQL Server 2005.

The problem I am facing is when I create the OLE DB Connection (I am using the "IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider") and see the "Preview", I get "System.Byte[]" in a couple of columns for all the rows, instead of the actual data.

The datatype of the original field is "Byte Stream".

I have tried all options, but, failed. I believe there is something in the "Force Translate" property of the OLE DB Connection. Right now it is set to "65535". I am not sure if that needs to be changed.

I was earlier using a DTS package, where I used ODBC for connecting to the same database. In ODBC, there is a "Translation" tab where there is a check box labelled: "Convert binary text (CCSID 65535) to text".

When I check this box, I am able to see the data correctly.

But, now I have moved to SSIS and I am facing the same problem as I am not using the ODBC connection.

Please help.

Thanks and Regards,

B@.ns

I don't knot if this would resolve your problem; but you can still use the ODBC connection in SSIS by using a Data Reader source component.|||

Hi,

Thank you for the reply.

I will try and use the same ODBC connection and get back.

Thanks and Regards,

B@.ns

|||

Thank you Rafael!

This has indeed solved my problem.

Thanks and Regards,

B@.ns

|||

If you still having problems.

1. You can try to create linkserver

2. Create a view of the target table

3. Access it like a SQL server table

Feedback wanted: Simulating @@Identitt behavior

Traditionally, implementing an alternative for the MS SQL Server
IDENTITY in Oracle is pretty straight forward:
e.g:
---
CREATE TABLE MyTable(
ID NUMBER(10) NOT NULL,
Description VARCHAR2(128)
)
/
CREATE SEQUENCE SeqMyTable
/
CREATE OR REPLACE TRIGGER TiMyTable BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT NVL(:new.ID, SeqMyTable.NEXTVAL) INTO :new.ID FROM DUAL;
END;
/
---
So far so good.
However, in my case I am dealing with a large application which is
running 'mixed', eg, originally designed for MS SQL Sever, and now
being adapted to run under Oracle as well. As the @.@.Identity is wildly
used, I thought of an alternative for returning the last identiry
value. As the queries themselves are already parsed, replacing all
occurrences of @.@.Identity with something else is a breeze.
So, my idea is to implement a global identity value with a package.
Can anybody tell me if the following is a good or a bad plan, and if
there are any caveats?
Thanks
Code:
---
CREATE OR REPLACE PACKAGE SomePackageName
IS
FUNCTION GetIdentity RETURN NUMBER;
PROCEDURE SetIdentity(Identity IN NUMBER);
END SomePackageName;
/
CREATE OR REPLACE PACKAGE BODY SomePackageName
IS
Identity NUMBER(10) := NULL;
FUNCTION GetIdentity
RETURN NUMBER
IS
BEGIN
RETURN Identity;
END;
PROCEDURE SetIdentity(Identity IN NUMBER)
IS
BEGIN
SomePackageName.Identity := Identity;
END;
END SomePackageName;
/
CREATE TABLE MyTable(
ID NUMBER(10) NOT NULL,
Description VARCHAR2(128)
)
/
CREATE SEQUENCE SeqMyTable
/
CREATE OR REPLACE TRIGGER TiMyTable BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT NVL(:new.ID, SeqMyTable.NEXTVAL) INTO :new.ID FROM DUAL;
SomePackageName.SetIdentity(:new.ID);
END;
/
Application example:
---
SET SERVEROUTPUT ON;
DECLARE
NewID NUMBER(10);
BEGIN
INSERT INTO MyTable(Description) VALUES('Hello world');
NewID := SomePackageName.GetIdentity;
dbms_output.put_line('New ID = ' || TO_CHAR(NewID));
END;
/Hmmm, actually, I'd better post this in the Oracle groups. Sorry for
spamming Oracle stuff here. :)sql

Federated server table partition

In a federated server, you create the same table on more than one server and
use a view to link them all together. I have two questions:
1) Can modulo division be used to determine which server a row goes on?
My reason is that modulo division will give the most even
distribution of the data.
2) Can I put a 2nd table on the same server, but in a different filegroup?
The DDL will get complicated and I want to start with 6 table
fragments on 2 servers, eventually moving to 3, then 6 servers, without
having to make wholesale changes to the DDL.
Oops: SQL Server 2000
"JayKon" wrote:

> In a federated server, you create the same table on more than one server and
> use a view to link them all together. I have two questions:
> 1) Can modulo division be used to determine which server a row goes on?
> My reason is that modulo division will give the most even
> distribution of the data.
> 2) Can I put a 2nd table on the same server, but in a different filegroup?
> The DDL will get complicated and I want to start with 6 table
> fragments on 2 servers, eventually moving to 3, then 6 servers, without
> having to make wholesale changes to the DDL.
|||Answers inline.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:A4D5D4C5-75C5-4B19-9BD9-E9B318787460@.microsoft.com...
> In a federated server, you create the same table on more than one server
> and
> use a view to link them all together. I have two questions:
> 1) Can modulo division be used to determine which server a row goes on?
> My reason is that modulo division will give the most even
> distribution of the data.
Yes, but then the query optimizer will not be able to take advantage of
Distributed Partitioned View, or even the indexes on table partitioning.
> 2) Can I put a 2nd table on the same server, but in a different filegroup?
> The DDL will get complicated and I want to start with 6 table
> fragments on 2 servers, eventually moving to 3, then 6 servers, without
> having to make wholesale changes to the DDL.
Absolutely! Some people run all the member tables on DPVs on the same
database. However you are not getting scale out performance this way. If you
have large data sets being returned you should use different servers, if the
results sets are small the network hop becomes the bottleneck and you should
move all tables locally.
|||Questions inline.

> Yes, but then the query optimizer will not be able to take advantage of
> Distributed Partitioned View, or even the indexes on table partitioning.
Even if the rows are accessed my the IDENTITY column (which is what I want
to do the modulo on)? I'm thinking of a title/item structure, or a customer
structure.
The lookup of the number would either be from a search engine, or a seperate
lookup table.

> Absolutely! Some people run all the member tables on DPVs on the same
> database. However you are not getting scale out performance this way. If you
> have large data sets being returned you should use different servers, if the
> results sets are small the network hop becomes the bottleneck and you should
> move all tables locally.
This is understood, however, I was thinking about this as more a way to keep
DDL changes to a minimum when expanding the number of servers in the cluster,
while at the same time reducing hard drive and I/O controller (and posibily
buss) activity.
Also, I intended seperate NIC's for the DB Servers to talk to each other.
One other question.
I saw a reference that said only the index for the key on a DPV was allowed.
Does that mean that if I wand another index, I need to build a seperate data
structure that will contain the PK for the table?
|||If your queries are by identity value, yes it should.
I would advise you to test this out with a representative load to see if it
will work.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:70BF14C6-7CC8-4098-BC5D-53B9A79EE0D1@.microsoft.com...
> Questions inline.
>
> Even if the rows are accessed my the IDENTITY column (which is what I want
> to do the modulo on)? I'm thinking of a title/item structure, or a
> customer
> structure.
> The lookup of the number would either be from a search engine, or a
> seperate
> lookup table.
>
> This is understood, however, I was thinking about this as more a way to
> keep
> DDL changes to a minimum when expanding the number of servers in the
> cluster,
> while at the same time reducing hard drive and I/O controller (and
> posibily
> buss) activity.
> Also, I intended seperate NIC's for the DB Servers to talk to each other.
> One other question.
> I saw a reference that said only the index for the key on a DPV was
> allowed.
> Does that mean that if I wand another index, I need to build a seperate
> data
> structure that will contain the PK for the table?
sql

Feature or Bug?

Service Broker will let you create two services with the same name (one with contract and another without)

CREATE SERVICE [Order Msg Recieve] AUTHORIZATION [dbo] ON QUEUE [dbo].[Order Return Msg Queue]

CREATE SERVICE [Order Msg Receive] AUTHORIZATION [dbo] ON QUEUE [ODS].[Order Return Msg Queue] ([OrderSubmission])

When you delete the service....

drop service [Order Msg Recieve]

It will only drop the first one. In the BOL there is no syntax for telling it to delete the second one, however you can drop it from SQL Management Studio.

I stumbled over this by accident. Just FYI

Gary

Neither feature nor a bug. Typo. One service is named 'Receive', one is named 'Recieve'. Note the 'cei' vs. 'cie'.

HTH,
~ Remus

sql

fastest way to deduplicate a list

Im trying to dedupe a table with only one field on it. The table has
40 million records in it. What is the fastest way?

1) create a table with a unque constraint on it insert into that
table?

2) create a table without a unique constraint on it and use insert
into table select distinct un from table2?

3) another way?

MichaelMichael Evanchik (mre224@.yahoo.com) writes:

Quote:

Originally Posted by

Im trying to dedupe a table with only one field on it. The table has
40 million records in it. What is the fastest way?
>
1) create a table with a unque constraint on it insert into that
table?

I assume that you would use the IGNORE_DUP_KEY option? Else the scheme
wouldn't work. That could very well be the fastest method.

--
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

Fastest way to build a database out of SQL scripts

What is the fastest way to generate an SQL 2000 database
out of SQL scripts.

The SQL scripts contain the create tables, views, stored
procedures, triggers, constraints, and the tables DATA
records.

What are my options? isql? osql? are there other ways?

Thank youHi

Using the command line utility unless you specify the :r option for the list
of files you are most likely to be creating a connection for each script.
Check out http://tinyurl.com/5299q

You can use a program that uses DMO (or ADO) to open each file and then
execute the commands within them, this can re-use a single connection, and
so be faster.

John

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:pJ%qe.22686$Es6.203309@.wagner.videotron.net.. .
> What is the fastest way to generate an SQL 2000 database
> out of SQL scripts.
> The SQL scripts contain the create tables, views, stored
> procedures, triggers, constraints, and the tables DATA
> records.
> What are my options? isql? osql? are there other ways?
> Thank you|||Hi

Using the command line utility unless you specify the :r option for the list
of files you are most likely to be creating a connection for each script.
Check out http://tinyurl.com/5299q

You can use a program that uses DMO (or ADO) to open each file and then
execute the commands within them, this can re-use a single connection, and
so be faster.

John

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:pJ%qe.22686$Es6.203309@.wagner.videotron.net.. .
> What is the fastest way to generate an SQL 2000 database
> out of SQL scripts.
> The SQL scripts contain the create tables, views, stored
> procedures, triggers, constraints, and the tables DATA
> records.
> What are my options? isql? osql? are there other ways?
> Thank you|||There is a tool called DB Ghost that builds databases from individual
drop/create scripts (schema) and static data insert scripts (data) and
it is, by far, the fastest method to build a database. It beats hand
coded scripts executed via QA or osql by a long way.

DB Ghost can also compare databases, produce a delta upgrade script and
it is possible to execute it via the command line which means that one
tool can give you a fully automated database change management
solution. When you keep the drop/create scripts in a source control
system you also get a full audit trail of changes made.

It's a very powerful way of making changes to SQL Server databases, I
highly recommend you check it out.

Malc|||There is a tool called DB Ghost that builds databases from individual
drop/create scripts (schema) and static data insert scripts (data) and
it is, by far, the fastest method to build a database. It beats hand
coded scripts executed via QA or osql by a long way.

DB Ghost can also compare databases, produce a delta upgrade script and
it is possible to execute it via the command line which means that one
tool can give you a fully automated database change management
solution. When you keep the drop/create scripts in a source control
system you also get a full audit trail of changes made.

It's a very powerful way of making changes to SQL Server databases, I
highly recommend you check it out.

Malc|||> Using the command line utility unless you specify the :r option for the
list
> of files you are most likely to be creating a connection for each script.
> Check out http://tinyurl.com/5299q
> You can use a program that uses DMO (or ADO) to open each file and then
> execute the commands within them, this can re-use a single connection, and
> so be faster.

Thank you John.|||> Using the command line utility unless you specify the :r option for the
list
> of files you are most likely to be creating a connection for each script.
> Check out http://tinyurl.com/5299q
> You can use a program that uses DMO (or ADO) to open each file and then
> execute the commands within them, this can re-use a single connection, and
> so be faster.

Thank you John.

Fast BCP

Can someone tell me the conditions that should be
fulfilled for a FAST BCP to happen
when i create a DTS package and that has Transform data
Task which inserts data into a table from select query is
that done via BCP
SanjayLook in BooksOnLine under BCP - Minimally logged load.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
> Can someone tell me the conditions that should be
> fulfilled for a FAST BCP to happen
> when i create a DTS package and that has Transform data
> Task which inserts data into a table from select query is
> that done via BCP
> Sanjay
>|||This is a bit confusing
It says that Target table should have 0 rows
Is this true
Also TABLOCK hint should be specified
Is this true too
Sanjay
>--Original Message--
>Look in BooksOnLine under BCP - Minimally logged load.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Sanjay" <sanjayg@.hotmail.com> wrote in message
>news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
>> Can someone tell me the conditions that should be
>> fulfilled for a FAST BCP to happen
>> when i create a DTS package and that has Transform data
>> Task which inserts data into a table from select query
is
>> that done via BCP
>> Sanjay
>
>.
>|||It only needs 0 rows if there are existing indexes on the table. If the
table has indexes it must be empty to do a minimally logged load. You can
drop the indexes and recreate them afterwards though. A table lock is
needed as well. This shouldn't be an issue if your loading that many rows
in you certainly don't want other users in there.
--
Andrew J. Kelly
SQL Server MVP
"Sanjay" <sanjayg@.hotmail.com> wrote in message
news:8be401c34577$ae5ab1f0$a401280a@.phx.gbl...
> This is a bit confusing
> It says that Target table should have 0 rows
> Is this true
> Also TABLOCK hint should be specified
> Is this true too
> Sanjay
> >--Original Message--
> >Look in BooksOnLine under BCP - Minimally logged load.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Sanjay" <sanjayg@.hotmail.com> wrote in message
> >news:024401c3456b$c1e25a40$a101280a@.phx.gbl...
> >> Can someone tell me the conditions that should be
> >> fulfilled for a FAST BCP to happen
> >>
> >> when i create a DTS package and that has Transform data
> >> Task which inserts data into a table from select query
> is
> >> that done via BCP
> >>
> >> Sanjay
> >>
> >
> >
> >.
> >

Failure to create first Replicated Database (SQL Mobile/SQL Server 2005) / Err 28627

Hi,

I'm trying to get my first replication going, and I have set up a database successfully, along with merge replication, however when I attempt to create the first subscriber, I get a permissions issue, regardless of what I do. In the SQLCESA30.LOG file, I get the following entries:

2006/01/07 16:59:36 Hr=00000000 SQLCESA30.DLL loaded 0
2006/01/07 16:59:58 Hr=80004005 ERR:OpenDB failed getting pub version 28627

I posted the initial thread under the "Replication" forum, but am including this 'pointer' post here due to a lack of replies there, and the relevance of this thread to this forum. Please see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=193693&SiteID=1 for more details.

Thanks.

I've managed to solve this. It was a Network Protcol issue. I ensured

that Named Pipes was the only protocol being used on the machine for

both client and server protocols, and created an Alias for my machine

with the same name as the computer. Refer to my post for details: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=194457&SiteID=1

Failure to create a Report Project within Visual Studio 2003

I'm having a problem creating a Report Project within Visual Studio. I have
VS.NET 2003 installed and I had then installed Sql Reporting Services with
the latest service pack. If I try and then create a reporting project within
a pre-existing solution, it gives me the following error.
Class already exists.
I looked at the knowledge base and it seems that this behaviour is caused by
a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
add-in manager and tested again. The problem still exists.
Any other ideas on what I should try?
Thanks,
Darren.On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
<dmpenner@.discussions.microsoft.com> wrote:
>I'm having a problem creating a Report Project within Visual Studio. I have
>VS.NET 2003 installed and I had then installed Sql Reporting Services with
>the latest service pack. If I try and then create a reporting project within
>a pre-existing solution, it gives me the following error.
>Class already exists.
>I looked at the knowledge base and it seems that this behaviour is caused by
>a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
>add-in manager and tested again. The problem still exists.
>Any other ideas on what I should try?
>Thanks,
>Darren.
Darren,
If you try to create a new project from the File menu (choose the
Wizard option for speed) does that create a working report or not?
Andrew Watt
MVP - InfoPath|||Andrew,
Just tried creating a new report project from the file menu and I still get
the same "Class already exists" message. If I look in my local directory
where I'm trying to create the new project, a folder is created there with a
virtually empty .rptproj file with the following contents.
<?xml version="1.0"?>
<Project xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
xmlns:xsd="">http://www.w3.org/2000/10/XMLSchema">
<DataSources />
<Reports />
</Project>
I can't however, see the project in the solution explorer and it doesn't
seem to be added to the solution file. Any other suggestions?
Thanks,
Darren.
"Andrew Watt [MVP - InfoPath]" wrote:
> On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
> <dmpenner@.discussions.microsoft.com> wrote:
> >I'm having a problem creating a Report Project within Visual Studio. I have
> >VS.NET 2003 installed and I had then installed Sql Reporting Services with
> >the latest service pack. If I try and then create a reporting project within
> >a pre-existing solution, it gives me the following error.
> >
> >Class already exists.
> >
> >I looked at the knowledge base and it seems that this behaviour is caused by
> >a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
> >add-in manager and tested again. The problem still exists.
> >
> >Any other ideas on what I should try?
> >
> >Thanks,
> >Darren.
> Darren,
> If you try to create a new project from the File menu (choose the
> Wizard option for speed) does that create a working report or not?
> Andrew Watt
> MVP - InfoPath
>|||Darren,
This isn't something I have seen before so the following questions are
simply trying to identify more clearly your specific
setup/circumstances.
Can you describe your setup in more detail? Which add-ins do you have?
When did you disable them?
Have you ever (I assume not, but want to be clear) created an RS
report successfully on that machine?
Apart from RS is Visual Studio working normally?
Andrew Watt
MVP - InfoPath
On Thu, 7 Apr 2005 08:55:03 -0700, "dmpenner"
<dmpenner@.discussions.microsoft.com> wrote:
>Andrew,
>Just tried creating a new report project from the file menu and I still get
>the same "Class already exists" message. If I look in my local directory
>where I'm trying to create the new project, a folder is created there with a
>virtually empty .rptproj file with the following contents.
><?xml version="1.0"?>
><Project xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"
>xmlns:xsd="">http://www.w3.org/2000/10/XMLSchema">
> <DataSources />
> <Reports />
></Project>
>I can't however, see the project in the solution explorer and it doesn't
>seem to be added to the solution file. Any other suggestions?
>Thanks,
>Darren.
>"Andrew Watt [MVP - InfoPath]" wrote:
>> On Thu, 7 Apr 2005 07:55:02 -0700, "dmpenner"
>> <dmpenner@.discussions.microsoft.com> wrote:
>> >I'm having a problem creating a Report Project within Visual Studio. I have
>> >VS.NET 2003 installed and I had then installed Sql Reporting Services with
>> >the latest service pack. If I try and then create a reporting project within
>> >a pre-existing solution, it gives me the following error.
>> >
>> >Class already exists.
>> >
>> >I looked at the knowledge base and it seems that this behaviour is caused by
>> >a mis-behaving add-in. Subsequently, I disabled all of my add-ins within the
>> >add-in manager and tested again. The problem still exists.
>> >
>> >Any other ideas on what I should try?
>> >
>> >Thanks,
>> >Darren.
>> Darren,
>> If you try to create a new project from the File menu (choose the
>> Wizard option for speed) does that create a working report or not?
>> Andrew Watt
>> MVP - InfoPath

Failure sending mail: The transport lost its connection to the ser

When I create e-mail subscriptions they have an error with the status:
"Failure sending mail: The transport lost its connection to the server."
Has someone run into this before?
Thanks,
NathanI have seen this happen when the smtp server is busy. The best way to
configure the email is to have it use the local SMTP servers pickup
directory and have the local smtp server relay the messages to your actual
SMTP server.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nate Dogg" <NateDogg@.discussions.microsoft.com> wrote in message
news:9CA05874-3791-48C6-858E-67A174849D78@.microsoft.com...
> When I create e-mail subscriptions they have an error with the status:
> "Failure sending mail: The transport lost its connection to the server."
> Has someone run into this before?
> Thanks,
> Nathan