Hello...
We have some tables that had been using Identity columns as a Primary
Key...but we abandoned that approach a few weeks ago and adopted GUIDs
instead.
These tables are included in Publications that were originally on SQL
2000...but we upgraded to SQL 2005.
Is there a way I can remove the Identity constraint from the server
pub...does the Not for Replication handle this?
Or...would be better to Drop the column and reinitialize the subscribers?
thanks for any help
- will
You would be best to drop the identity column, NFR will not drop the
identity column but will not enforce the identity property if the insert is
caused by a replication process.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"dw" <dw@.discussions.microsoft.com> wrote in message
news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
> Hello...
> We have some tables that had been using Identity columns as a Primary
> Key...but we abandoned that approach a few weeks ago and adopted GUIDs
> instead.
> These tables are included in Publications that were originally on SQL
> 2000...but we upgraded to SQL 2005.
> Is there a way I can remove the Identity constraint from the server
> pub...does the Not for Replication handle this?
> Or...would be better to Drop the column and reinitialize the subscribers?
> thanks for any help
> - will
|||Thanks for the help. I may test out going the NFR route...just so I don't
have to mess with the table schema too much. Will changing to NFR force a
Re-Init of for the subscribers?
"Hilary Cotter" wrote:
> You would be best to drop the identity column, NFR will not drop the
> identity column but will not enforce the identity property if the insert is
> caused by a replication process.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "dw" <dw@.discussions.microsoft.com> wrote in message
> news:16F706A2-F1D3-4215-9A70-DDDD5575920E@.microsoft.com...
>
>
|||This is something you do on the subscriber - so for transactional
replication it will have no impact on reinitialization. For merge and
updateable subscribers it will.
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
"dw" <dw@.discussions.microsoft.com> wrote in message
news:C109A5B7-07DE-4E85-88C5-6BA71CBCCEFD@.microsoft.com...[vbcol=seagreen]
> Thanks for the help. I may test out going the NFR route...just so I
> don't
> have to mess with the table schema too much. Will changing to NFR force a
> Re-Init of for the subscribers?
> "Hilary Cotter" wrote:
sql
Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts
Friday, March 30, 2012
Remove Grand Total in Pivot Table issue using asp.net
Hi,
I am trying to remove the grad total amount from the pivot table list with asp.net,programing. The data I called from cube, I have rows,columns and data axis values, when I place my measure in to data axis it autometically created sub total and grand totals, I want remove those. I could not found any property or method to remove that.
Please help me on this.
Hi Please help, I still not got any answer.
Wednesday, March 28, 2012
remove columns created by replication process
One of our users tried to set replication solution in a sqlserver (the idea was given up).
SQL Server added in each table a column related to replication.
We want to remove theses columns and I used the following script :
select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
+'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
from sysconstraints where object_name(constid) like '%msrep%'
Question:
1. I want to know how to introduce a carraige return in order to have some thing like this :
...
ALTER TABLE dbo.T_CommandCopyFile
DROP CONSTRAINT DF__T_Command__msrep__44AB0736
GO
ALTER TABLE dbo.T_CommandCopyFile
DROP COLUMN msrepl_tran_version
...
2. Is there any other solution to do this more simply ?I'd use:DECLARE @.crlf CHAR(2)
SET @.crlf = Char(13) + Char(10)-PatP|||Could you give me more explanation (why : char(13)+char(10))
I tried only char(13) only and I noticed the result (space in the beginning of the line).|||You really want the history lesson?
Ok, back in the days of CP/M, there was hot debate as to what constituted a "line end". The Unix crew wanted Line Feed (0x0a). The OASIS crew wanted Carriage Return (0x0d). Nobody would budge.
Teletypes needed both, and CR took longer to execute than LF did, so it was always sent first. Since nobody could make a "command decision" Gary Kildall made the call that they'd use what the teletypes wanted, to make it easier to print files and vex both of the software camps!
MS-DOS basically picked up where CP/M left off, so it followed the same convention. Windoze is the GUI that was later bolted on to MS-DOS, so it used the same convention... You see where we are headed here, right?
Anywho, the short answer boils down to Transact SQL sees a "line end" as being a carriage return followed by a line feed, aka Char(13) + Char(10) to us hydro-carbon based types.
-PatP
SQL Server added in each table a column related to replication.
We want to remove theses columns and I used the following script :
select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
+'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
from sysconstraints where object_name(constid) like '%msrep%'
Question:
1. I want to know how to introduce a carraige return in order to have some thing like this :
...
ALTER TABLE dbo.T_CommandCopyFile
DROP CONSTRAINT DF__T_Command__msrep__44AB0736
GO
ALTER TABLE dbo.T_CommandCopyFile
DROP COLUMN msrepl_tran_version
...
2. Is there any other solution to do this more simply ?I'd use:DECLARE @.crlf CHAR(2)
SET @.crlf = Char(13) + Char(10)-PatP|||Could you give me more explanation (why : char(13)+char(10))
I tried only char(13) only and I noticed the result (space in the beginning of the line).|||You really want the history lesson?
Ok, back in the days of CP/M, there was hot debate as to what constituted a "line end". The Unix crew wanted Line Feed (0x0a). The OASIS crew wanted Carriage Return (0x0d). Nobody would budge.
Teletypes needed both, and CR took longer to execute than LF did, so it was always sent first. Since nobody could make a "command decision" Gary Kildall made the call that they'd use what the teletypes wanted, to make it easier to print files and vex both of the software camps!
MS-DOS basically picked up where CP/M left off, so it followed the same convention. Windoze is the GUI that was later bolted on to MS-DOS, so it used the same convention... You see where we are headed here, right?
Anywho, the short answer boils down to Transact SQL sees a "line end" as being a carriage return followed by a line feed, aka Char(13) + Char(10) to us hydro-carbon based types.
-PatP
remove chr(10) char with Tsql?
Hello,
I am writing data to a table using a DTS package from a VB
app. One of the columns, nvarchar column, is getting
carriage/return char appended to the data.
Select '*' + fld1 + '*' From tbl1
fld1 contains digits. I get this
* 10*
If I do this:
Declare @.s varchar(10)
Select @.s = fld1 From tbl1
Print '*' + @.s + '*'
I get this:
*
10*
If I say Update tbl1 Set fld1 = Ltrim(fld1)
I still get * 10*. But if I say Update tbl1 Set fld1 = 10
Now I get *10*
How can I remove these chr(10) chars with Tsql? Should I
loop through a cursor and use Substring?
declare @.s varchar(20)
declare @.t varchar(20)
select @.s = cn from tblincidentsn where rownum = 1
print '*' + @.s + '*'
set @.t = substring(@.s,3, len(@.s) - 2)
print ''
print '*' + @.t + '*'
gives me
*
02*
*02* <-- correct format
Thanks in advance for any suggestions.
RonAre you sure you don't want both CHAR(13) and CHAR(10) (CR and LF,
respectively)?
SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||SET @.s = REPLACE(REPLACE(@.s, CHAR(10), ''), CHAR(13), '')
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||> SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
I usually replace them separately, for two reasons:
(a) not all programs behave well. Some only include 10, some only include
13.
(b) not all programs behave well. Many include these in the wrong order.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePxgmo2IFHA.3484@.TK2MSFTNGP12.phx.gbl...
> (a) not all programs behave well. Some only include 10, some only include
> 13.
> (b) not all programs behave well. Many include these in the wrong order.
Both are excellent points!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
I am writing data to a table using a DTS package from a VB
app. One of the columns, nvarchar column, is getting
carriage/return char appended to the data.
Select '*' + fld1 + '*' From tbl1
fld1 contains digits. I get this
* 10*
If I do this:
Declare @.s varchar(10)
Select @.s = fld1 From tbl1
Print '*' + @.s + '*'
I get this:
*
10*
If I say Update tbl1 Set fld1 = Ltrim(fld1)
I still get * 10*. But if I say Update tbl1 Set fld1 = 10
Now I get *10*
How can I remove these chr(10) chars with Tsql? Should I
loop through a cursor and use Substring?
declare @.s varchar(20)
declare @.t varchar(20)
select @.s = cn from tblincidentsn where rownum = 1
print '*' + @.s + '*'
set @.t = substring(@.s,3, len(@.s) - 2)
print ''
print '*' + @.t + '*'
gives me
*
02*
*02* <-- correct format
Thanks in advance for any suggestions.
RonAre you sure you don't want both CHAR(13) and CHAR(10) (CR and LF,
respectively)?
SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||SET @.s = REPLACE(REPLACE(@.s, CHAR(10), ''), CHAR(13), '')
http://www.aspfaq.com/
(Reverse address to reply.)
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:530f01c52368$17e37240$a601280a@.phx.gbl...
> Hello,
> I am writing data to a table using a DTS package from a VB
> app. One of the columns, nvarchar column, is getting
> carriage/return char appended to the data.
> Select '*' + fld1 + '*' From tbl1
> fld1 contains digits. I get this
> * 10*
> If I do this:
> Declare @.s varchar(10)
> Select @.s = fld1 From tbl1
> Print '*' + @.s + '*'
> I get this:
> *
> 10*
> If I say Update tbl1 Set fld1 = Ltrim(fld1)
> I still get * 10*. But if I say Update tbl1 Set fld1 = 10
> Now I get *10*
> How can I remove these chr(10) chars with Tsql? Should I
> loop through a cursor and use Substring?
> declare @.s varchar(20)
> declare @.t varchar(20)
> select @.s = cn from tblincidentsn where rownum = 1
> print '*' + @.s + '*'
> set @.t = substring(@.s,3, len(@.s) - 2)
> print ''
> print '*' + @.t + '*'
> gives me
> *
> 02*
> *02* <-- correct format
> Thanks in advance for any suggestions.
> Ron|||> SET @.String = REPLACE(@.String, CHAR(13)+CHAR(10), '')
I usually replace them separately, for two reasons:
(a) not all programs behave well. Some only include 10, some only include
13.
(b) not all programs behave well. Many include these in the wrong order.|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ePxgmo2IFHA.3484@.TK2MSFTNGP12.phx.gbl...
> (a) not all programs behave well. Some only include 10, some only include
> 13.
> (b) not all programs behave well. Many include these in the wrong order.
Both are excellent points!
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
remove character from string
Hi I am filling a table with a select statement and one of the columns
(Time1data)returned from the select statement looks like 10:30, it is a
character type. The table I am trying to put it in is expecting 1030 and is
character 4 type. Just wondering if anyone knows how to remove the : as part
of the select statement?
I tried select Right (Time1data,4) but this just gets the right 4 characters.
Thanks.
Paul G
Software engineer.
Hi Paul,
You can use REPLACE, like this:
SELECT REPLACE(Time1data, ':', '')
FROM ...
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||It worked! thanks.
Paul G
Software engineer.
"Plamen Ratchev" wrote:
> Hi Paul,
> You can use REPLACE, like this:
> SELECT REPLACE(Time1data, ':', '')
> FROM ...
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
sql
(Time1data)returned from the select statement looks like 10:30, it is a
character type. The table I am trying to put it in is expecting 1030 and is
character 4 type. Just wondering if anyone knows how to remove the : as part
of the select statement?
I tried select Right (Time1data,4) but this just gets the right 4 characters.
Thanks.
Paul G
Software engineer.
Hi Paul,
You can use REPLACE, like this:
SELECT REPLACE(Time1data, ':', '')
FROM ...
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||It worked! thanks.
Paul G
Software engineer.
"Plamen Ratchev" wrote:
> Hi Paul,
> You can use REPLACE, like this:
> SELECT REPLACE(Time1data, ':', '')
> FROM ...
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
sql
remove character from string
Hi I am filling a table with a select statement and one of the columns
(Time1data)returned from the select statement looks like 10:30, it is a
character type. The table I am trying to put it in is expecting 1030 and is
character 4 type. Just wondering if anyone knows how to remove the : as part
of the select statement?
I tried select Right (Time1data,4) but this just gets the right 4 characters.
Thanks.
--
Paul G
Software engineer.Hi Paul,
You can use REPLACE, like this:
SELECT REPLACE(Time1data, ':', '')
FROM ...
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||It worked! thanks.
--
Paul G
Software engineer.
"Plamen Ratchev" wrote:
> Hi Paul,
> You can use REPLACE, like this:
> SELECT REPLACE(Time1data, ':', '')
> FROM ...
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
(Time1data)returned from the select statement looks like 10:30, it is a
character type. The table I am trying to put it in is expecting 1030 and is
character 4 type. Just wondering if anyone knows how to remove the : as part
of the select statement?
I tried select Right (Time1data,4) but this just gets the right 4 characters.
Thanks.
--
Paul G
Software engineer.Hi Paul,
You can use REPLACE, like this:
SELECT REPLACE(Time1data, ':', '')
FROM ...
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||It worked! thanks.
--
Paul G
Software engineer.
"Plamen Ratchev" wrote:
> Hi Paul,
> You can use REPLACE, like this:
> SELECT REPLACE(Time1data, ':', '')
> FROM ...
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
remove character from string
Hi I am filling a table with a select statement and one of the columns
(Time1data)returned from the select statement looks like 10:30, it is a
character type. The table I am trying to put it in is expecting 1030 and is
character 4 type. Just wondering if anyone knows how to remove the : as par
t
of the select statement?
I tried select Right (Time1data,4) but this just gets the right 4 characters
.
Thanks.
--
Paul G
Software engineer.Hi Paul,
You can use REPLACE, like this:
SELECT REPLACE(Time1data, ':', '')
FROM ...
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||It worked! thanks.
--
Paul G
Software engineer.
"Plamen Ratchev" wrote:
> Hi Paul,
> You can use REPLACE, like this:
> SELECT REPLACE(Time1data, ':', '')
> FROM ...
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
(Time1data)returned from the select statement looks like 10:30, it is a
character type. The table I am trying to put it in is expecting 1030 and is
character 4 type. Just wondering if anyone knows how to remove the : as par
t
of the select statement?
I tried select Right (Time1data,4) but this just gets the right 4 characters
.
Thanks.
--
Paul G
Software engineer.Hi Paul,
You can use REPLACE, like this:
SELECT REPLACE(Time1data, ':', '')
FROM ...
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||It worked! thanks.
--
Paul G
Software engineer.
"Plamen Ratchev" wrote:
> Hi Paul,
> You can use REPLACE, like this:
> SELECT REPLACE(Time1data, ':', '')
> FROM ...
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
Wednesday, March 7, 2012
Remote Data Access (RDA.Pull)
Hi,
I am trying to use RDA.pull to pull data from SQL server 2000 to sql mobile, but it fails when some columns are included in the SQLSelectString, though the datatypes involved are only char, float, smallint and smalldatetime, and nothing is longer than 30. Since there is a very large number of columns, it is difficult to isolate the guilty one by trial and error! Coud someone please help?
- Nag Rao
Can you please try pulling a table with no.of columns less than 128!
Thanks,
Laxmi
Remote Data Access (RDA.Pull)
Hi,
I am trying to use RDA.pull to pull data from SQL server 2000 to sql mobile, but it fails when some columns are included in the SQLSelectString, though the datatypes involved are only char, float, smallint and smalldatetime, and nothing is longer than 30. Since there is a very large number of columns, it is difficult to isolate the guilty one by trial and error! Coud someone please help?
- Nag Rao
Can you please try pulling a table with no.of columns less than 128!
Thanks,
Laxmi
Subscribe to:
Posts (Atom)