Problem in IDENTITY COLUMNS

Hi folks! I've a merge replication setup b/w two servers.
Published tables have columns (INT IDENTITY SEED 1 INCREMENT[NOT FOR REPLICATION]).
Whenever i apply the SNAPSHOT, i have to run DBCC CHECKIDENT('table' RESEED) for each table at the subscriber twice, for the values in the columns are almost always greater than the ID-Seed value. For example the last Identity value in the column is 999 but whenever i insert a new row; i get error; couldn't insert duplicate value into the table. When i run the dbcc check i see the following message:
"Checking identity information: current identity value '1', current column value '999'."
How do i square this away?Originally posted by TALAT
Hi folks! I've a merge replication setup b/w two servers.
Published tables have columns (INT IDENTITY SEED 1 INCREMENT[NOT FOR REPLICATION]).
Whenever i apply the SNAPSHOT, i have to run DBCC CHECKIDENT('table' RESEED) for each table at the subscriber twice, for the values in the columns are almost always greater than the ID-Seed value. For example the last Identity value in the column is 999 but whenever i insert a new row; i get error; couldn't insert duplicate value into the table. When i run the dbcc check i see the following message:
"Checking identity information: current identity value '1', current column value '999'."
How do i square this away?

You will have to find the max value and do something like this.

DBCC CHECKIDENT('table',RESEED,@.max_value)|||Howdy!
Running: DBCC CHECKIDENT('table', RESEED). when i run it second time for the table; the identity value gets normal, i.e. it gets the same as the last value in the column. But it's rather painful to run it for each table. I never had this problem at the publisher it's only at the subscriber. Is there a permanent solution?

Thanx for the reply.

Problem in creating nullable columns using SELECT INTO in SQL Serv

Hi Everyone!
I have a problem that is basically a database issue in SQL Server 2000
compared to Sybase 12. I am working on a application that should support bot
h
SYbase and SQL Server as backend. My code works fine in Sybase but not in SQ
L
Server. Following is the query that will give a an idea of the issue.
Query:
--
SELECT Null as c1,
isnull( NULL,0) as c2
INTO #temp_tbl
Above query creates both c1 and c2 as nullable columns in Sybase. The same
query in SQL Server creates C1 as nullable and C2 as not null. There are
situation that result in null data and cause inserting NULL into not columns
in SQL Server. Using SET ANSI_NULL_DFLT_ON ON also did not give the required
results. I am looking for a solution which would create c1 and c2 as nullabl
e
columns in SQL Server.
Please help in finding me a solution.
Thanks in advance.
SomeshSRV wrote:
> Hi Everyone!
> I have a problem that is basically a database issue in SQL Server 2000
> compared to Sybase 12. I am working on a application that should support b
oth
> SYbase and SQL Server as backend. My code works fine in Sybase but not in
SQL
> Server. Following is the query that will give a an idea of the issue.
> Query:
> --
> SELECT Null as c1,
> isnull( NULL,0) as c2
> INTO #temp_tbl
> Above query creates both c1 and c2 as nullable columns in Sybase. The same
> query in SQL Server creates C1 as nullable and C2 as not null. There are
> situation that result in null data and cause inserting NULL into not colum
ns
> in SQL Server. Using SET ANSI_NULL_DFLT_ON ON also did not give the requir
ed
> results. I am looking for a solution which would create c1 and c2 as nulla
ble
> columns in SQL Server.
> Please help in finding me a solution.
> Thanks in advance.
> Somesh
Like this:
CREATE TABLE #temp_tbl (c1 INTEGER NULL, c2 INTEGER NULL /* !!! NO
PRIMARY KEY !!! */)
INSERT INTO #temp_tbl (c1, c2)
SELECT ...
Curiously enough, the following seems to give your desired result in
SQL Server 2000 but not in 2005. Which just goes to show the folly of
such proprietary "tricks" as this.
SELECT c1, COALESCE(c2,0) AS c2
INTO T1
FROM (SELECT NULL, NULL) AS T(c1,c2);
David Portas
SQL Server MVP
--|||David Portas wrote:
> Curiously enough, the following seems to give your desired result in
> SQL Server 2000 but not in 2005. Which just goes to show the folly of
> such proprietary "tricks" as this.
> SELECT c1, COALESCE(c2,0) AS c2
> INTO T1
> FROM (SELECT NULL, NULL) AS T(c1,c2);
>
That was just a type coercian issue. The following tests OK on both
2000 and 2005 (8.00.760 and 9.00.1399.06)
SELECT c1, COALESCE(c2,0) AS c2
INTO T1
FROM (SELECT CAST(NULL AS INTEGER),
CAST(NULL AS INTEGER)) AS T(c1,c2);
David Portas
SQL Server MVP
--

Problem exporter PDF.

Hello everybody:

I have a simple report (table), and when I make export in PDF, he(it) takes out to me a badly formated file PDF (columns moved and empty pages).

It is what the problem?

Thank you in advance.

Go to the report properties window from the report menu. There set the page layout, width, height, margins. Unfortunately there's no default landscape/portrait option you control this using width/height. These setting affect both print and PDF output.|||

Thank you for your reponse:

but I have any attempt but ùca marche not,

you can look at the .PDF file

http://ftf-166.yousendit.com/download/1/ED443F25437C8D89/f2aa0f49f46c00359990203df421f4b6476a6e19/Situation%20de%20compte%20MBE.pdf

and say to me it needs to put what to post(show) 4 columns.

Thank's

|||

I see 2 problems with the PDF.

Firstly, the table is too wide to fit within the margins, so either reduce the table width of the table or increase the page margins. This can be found in the report properties page, layout tab.

Secondly, the long result set will span many pages so it would be usefull to have the table header repeated on every page. In the table properties screen on the general tab there is a checkbox for this.

Problem DTSing into table with Identity field

Hello everyone,

I have a table X that has two columns (names have been changed to protect the guilty and innocent alike):
ID which is an identity column, and
Data which is a varchar.

I am trying to DTS data from a text file into this table. I want the identity column ID to auto-populate and each row of information from the file to be stored in the Data field.

I am creating the DTS package using the wizard in Enterprise Mgr. No matter what I try, I keep getting the following error:

Cannot insert the value NULL into column 'ID', table 'X'; column does not allow nulls. INSERT fails.

Any advice on what I need to do to get this working? It seems like such a simple thing and I'm getting very frustrated. :eek:

Thanks in advance.
CathyDTS sucks.

Are you using the wizard. Have you tried hitting the transform button and ignore on the source column.|||Does every record in the text file have an ID value? Sounds like there must be NULLs.

ddave|||Hello again,

Thrasymachus,
Yes, I'm using the wizard and setting the transformation to <ignore> the identity column. And yes, DTS does suck! :-)

dolfandave,
My file does not have values for the identity column, I'm trying to get them to be auto-generated by SQL Server, like they would if I did an insert into the table in isql.

Thanks,
Cathy|||did u try checking the "Enable Identity insert" checkbox in the Tranform Data Task properties?

problem dropping columns

One of our developers accidentally added a 'rowguid' column to all of our
tables (mssql 2000). I'm trying to write a script that will drop this
column from all the tables; however, I've run into a problem where I can't
drop them because there are dependant contraints/indexes. The following
code is what I have so far. Is there's a way to identify and drop all
dependancies on this column first?
DECLARE @.TableName sysname
DECLARE @.ColumnName sysname
DECLARE RowGuidColumnList CURSOR
FOR select t.name, c.name
FROM sysobjects t
JOIN syscolumns c
ON (c.id = t.id and t.type = 'U')
WHERE c.name = 'rowguid'
order by t.name, c.name
OPEN RowGuidColumnList
FETCH NEXT FROM RowGuidColumnList
INTO @.TableName, @.ColumnName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Removing rowguid column from ' + @.TableName
execute('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' + @.ColumnName)
FETCH NEXT FROM RowGuidColumnList
INTO @.TableName, @.ColumnName
END
CLOSE RowGuidColumnList
DEALLOCATE RowGuidColumnList
Thanks, DougYou can find information about dependencies of some
particular column from system tables:
sysconstraints,
syscolumns,
sysobjects
Take a look about this tables in BOL.
Regards
----
All information provided above AS IS
>--Original Message--
>One of our developers accidentally added a 'rowguid'
column to all of our
>tables (mssql 2000). I'm trying to write a script that
will drop this
>column from all the tables; however, I've run into a
problem where I can't
>drop them because there are dependant
contraints/indexes. The following
>code is what I have so far. Is there's a way to identify
and drop all
>dependancies on this column first?
>DECLARE @.TableName sysname
>DECLARE @.ColumnName sysname
>DECLARE RowGuidColumnList CURSOR
>FOR select t.name, c.name
>FROM sysobjects t
> JOIN syscolumns c
> ON (c.id = t.id and t.type = 'U')
>WHERE c.name = 'rowguid'
>order by t.name, c.name
>OPEN RowGuidColumnList
>FETCH NEXT FROM RowGuidColumnList
>INTO @.TableName, @.ColumnName
>WHILE @.@.FETCH_STATUS = 0
>BEGIN
> PRINT 'Removing rowguid column from ' + @.TableName
> execute('ALTER TABLE ' + @.TableName + ' DROP COLUMN ' +
@.ColumnName)
> FETCH NEXT FROM RowGuidColumnList
> INTO @.TableName, @.ColumnName
>END
>CLOSE RowGuidColumnList
>DEALLOCATE RowGuidColumnList
>
>Thanks, Doug
>
>.
>

Problem creating report with Table having dynamic columns.

The requierment is tyo have a dynamic report, that has a table with number of
columns decided on the input from recordset.
The SQL procedure takes in parameters and returns recordset, that can have
varying number of columns. All we want to do is to link this recordset to a
table on the report.
The report table should show the columns as they are in the Recordset. ( and
the column name in the recordset shall appear as the column header in the
Reporting table.)
-- Alok Kumar GuptaWhat you could do in this case is make the SP return a
fixed number of records all the time and return zero
values for the column not required.
Then map all the fields to columns in a table and set
visibility expression for columns to the appropriate
condition.
>--Original Message--
>The requierment is tyo have a dynamic report, that has a
table with number of
>columns decided on the input from recordset.
>The SQL procedure takes in parameters and returns
recordset, that can have
>varying number of columns. All we want to do is to link
this recordset to a
>table on the report.
>The report table should show the columns as they are in
the Recordset. ( and
>the column name in the recordset shall appear as the
column header in the
>Reporting table.)
>-- Alok Kumar Gupta
>.
>|||I have the same issue but my columns are being generated dynamically by user
data and I have no way of knowing how many there will be nor the names.
"Alok" wrote:
> I thought of that, but that seems to be a very crude way of achieving it.
> Doesn't the reporting service supports something like a grid to simply
> display what you get from the recordset.
> Another problem I found in the approach is about the column headers in the
> table.
> I could not write an expression for the column headers in the Table that
> could read the column titles in my recordset.
> thanks for your response.
> Alok
>
> "anonymous@.discussions.microsoft.com" wrote:
> > What you could do in this case is make the SP return a
> > fixed number of records all the time and return zero
> > values for the column not required.
> > Then map all the fields to columns in a table and set
> > visibility expression for columns to the appropriate
> > condition.
> > >--Original Message--
> > >The requierment is tyo have a dynamic report, that has a
> > table with number of
> > >columns decided on the input from recordset.
> > >
> > >The SQL procedure takes in parameters and returns
> > recordset, that can have
> > >varying number of columns. All we want to do is to link
> > this recordset to a
> > >table on the report.
> > >
> > >The report table should show the columns as they are in
> > the Recordset. ( and
> > >the column name in the recordset shall appear as the
> > column header in the
> > >Reporting table.)
> > >
> > >-- Alok Kumar Gupta
> > >.
> > >
> >

problem counting multiple occurrances of a pair of entries

Hi Everyone,

I was having problems writing up a query to do the following:

I have a table with columns A B and C

I needed to count all occurrances of unique (A,B) tuples in the table

For Eg:

A B C
foo foo ZOO
foo foo BCV
xoo cdv rdf
foo foo ert
xoo cdv see
red gre

i needed an output

foo foo 3
xoo cdv 1SELECT A,B,COUNT(*)
FROM myTable99
GROUP BY A,B
HAVING COUNT(*) > 1

problem counting multiple occurrances of a pair of entries

Apologies for the previous post - I hit the wrong mouse button

I was having problems writing up this query

I had a table with columns A B C i needed to write up a query that would count all occurrances of unique A,B pair entries in the table

for eg:

A B C
red pink x
red pink y
green blue z
red pink a
green yello b
green blue c

The query should return

red pink 3
green blue 2
green yello 1

I hope that helps... any help would be appreciated

Thanks in advanceOK...really simple now...just cut and paste in to QA

USE Northwind
GO

CREATE TABLE myTable99 (A varchar(10),B varchar(10),C varchar(10))
GO

INSERT INTO myTable99 (A,B,C)
SELECT 'red', 'pink', 'x' UNION ALL
SELECT 'red', 'pink', 'y' UNION ALL
SELECT 'green','blue', 'z' UNION ALL
SELECT 'red', 'pink', 'a' UNION ALL
SELECT 'green','yello', 'b' UNION ALL
SELECT 'green','blue', 'c'

SELECT A,B,COUNT(*)
FROM myTable99
GROUP BY A,B
GO

DROP TABLE myTable99
GO