convert ###,###,##0 to INT

Hi,

I've imported an Excel file into a work table, via an Access Project. One of my fields is an integer, represented in Excel with thousands separator e.g. 3,137,458

The above now sits in a varchar column, and I need to convert these values to an INT. Strangely, is numeric() returns One, but then convert( int, ...) does not like the commas.

To add insult to injury, my MSDE does not seem to allow me to CREATE FUNCTION. It protests even if I do Grant Create Function to Login, while running as 'sa'. Side question: is this a known limitation of MSDE ?

Is there an efficient way to convert such strings to Int ?

I note that the commas may actually be missing, since their presence depends on the "Digit Grouping" value in the Regional Settings of Control Panel.

In the past, I was using Sybase, and I had to use set-based queries, running against a few work fields in my table. The first query would use charindex() to find the position of the first comma, if any. The second query would pick up the portion of the string up to the comma, then another query chasing the next comma, etc. Rather painful.Hmm...maybe you could try playing around with the replace command to filter out the commas.

I tested this 1 line code in QA and it works fine.

select cast(replace('3,137,458',',','') as int).|||oops, temporary blindness ... apologies ... please ignore this question

convert( int, REPLACE( column_name, ',', '' ) )|||thanks, mate, I've just found it at the same time. Works like a charm.
Me self-learner too...

Conversion of IDENTITY to UNIQUEIDENTIFIER during Replication?

Hi,
I tried to find an answer to this via BOL and web but to no avail.
Consider this situation:
CREATE TABLE [dbo].[foodetail] (
[fooid] [int] IDENTITY (1, 1) NOT NULL ,
[footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[foofact] (
[fooid] [int] NOT NULL ,
[volume] [float] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
(
[fooid]
) ON [PRIMARY]
GO
I.e. a fact and a detail table that are joined via fooid but there is no
foreign key defined.
Now, is it possible to replicate content of these two tables and have
fooid converted consistently to a UUID during replication? If not, is it
possible to do it if there is a FK defined?
Thanks a lot!
Kind regards
robertWhy would you want to convert your primary key to GUID? Merge replication
will add rowguid column when you set it up and replicate independently. You
could add your own guid column and populate it, but there's no 'during
replication'. It stays
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I tried to find an answer to this via BOL and web but to no avail.
> Consider this situation:
> CREATE TABLE [dbo].[foodetail] (
> [fooid] [int] IDENTITY (1, 1) NOT NULL ,
> [footext] [varchar] (100) COLLATE Latin1_General_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[foofact] (
> [fooid] [int] NOT NULL ,
> [volume] [float] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[foodetail] WITH NOCHECK ADD
> CONSTRAINT [PK_foodetail] PRIMARY KEY CLUSTERED
> (
> [fooid]
> ) ON [PRIMARY]
> GO
>
> I.e. a fact and a detail table that are joined via fooid but there is no
> foreign key defined.
> Now, is it possible to replicate content of these two tables and have
> fooid converted consistently to a UUID during replication? If not, is it
> possible to do it if there is a FK defined?
> Thanks a lot!
> Kind regards
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:uxsiRk07FHA.2716@.TK2MSFTNGP11.phx.gbl...
[vbcol=seagreen]
> Why would you want to convert your primary key to GUID? Merge
> replication will add rowguid column when you set it up and replicate
> independently. You could add your own guid column and populate it,
> but there's no 'during replication'. It stays
I want to get data from n databases to a single centralized DB. In oder
to minimize changes needed to be done to application code ideally I use
IDENTITY columns on local instances and have them converted to GUID
columns during replication because IDENTITY is not globally unique (in
fact likelyhood of collisions is extremely high :-)).
Now, in order to not having to change application code and join generation
on the centralized server ideally we would continue to use the same column
names ("fooid" in this example).
As far as I understand functionality of merge replication, every row in a
table gets a GUID to uniquely identify the row. This would work for the
detail table but not for the fact table as that contains other detail id
columns as well and in order to be able to join them properly we would
need all detail table's GUID's here.
Basically the product in question was never meant to support replication
and now I'm trying to find out whether there's a way to retrofit that
efficiently (meaning developer time as well as run time). :-)
Cheers
robert|||I believe you're better off with adding the column on each table with local
info and extend keys to include it.
Something like 'locationID'. PK in that case is on two columns.
Not very nice, but it could be good enough. Implementing it wouldnt take a
lot of time since you would implement same changes on all databases and all
'LocationID' values are the same for each database.
As far as I can see, alternative would be to add guid column and then update
all FK columns with values from PK and then start replication or something.
MC
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
> MC wrote:
>
>
> I want to get data from n databases to a single centralized DB. In oder
> to minimize changes needed to be done to application code ideally I use
> IDENTITY columns on local instances and have them converted to GUID
> columns during replication because IDENTITY is not globally unique (in
> fact likelyhood of collisions is extremely high :-)).
> Now, in order to not having to change application code and join generation
> on the centralized server ideally we would continue to use the same column
> names ("fooid" in this example).
> As far as I understand functionality of merge replication, every row in a
> table gets a GUID to uniquely identify the row. This would work for the
> detail table but not for the fact table as that contains other detail id
> columns as well and in order to be able to join them properly we would
> need all detail table's GUID's here.
> Basically the product in question was never meant to support replication
> and now I'm trying to find out whether there's a way to retrofit that
> efficiently (meaning developer time as well as run time). :-)
> Cheers
> robert
>|||MC wrote:
> "Robert Klemme" <bob.news@.gmx.net> wrote in message
> news:eWnGOS17FHA.1000@.tk2msftngp13.phx.gbl...
[vbcol=seagreen]
> I believe you're better off with adding the column on each table with
> local info and extend keys to include it.
Unfortunately "each table" means all tables that have to be replicated.

> Something like 'locationID'. PK in that case is on two columns.
> Not very nice, but it could be good enough. Implementing it wouldnt
> take a lot of time since you would implement same changes on all
> databases and all 'LocationID' values are the same for each database.
This would mean that the number of columns in fact tables (large!) nearly
doubles. Plus, this would necessitate an application change to change SQL
query generation (joins!).

> As far as I can see, alternative would be to add guid column and then
> update all FK columns with values from PK and then start replication
> or something.
Hmm... I'll have to think about this a bit. Thanks for the valuable
feedback!
Kind regards
robert

Conversion between Date Formats

Hi. I have a DB in which we store dates in yyyy/mm/dd. However when we
want to display this date via a web frontend, it needs to be in
dd/mm/yyyy. I've declared a function (shown below) which converts
between these date formats and returns a varchar(20). This works fine
however now I need to have the ability to sort on this date field in
the frontend. This requires my function to return a datetime in the
required format. Can this be done?

DECLARE @.InputDate nvarchar(20)
DECLARE @.OutputDate nvarchar(20)
DECLARE @.Day nvarchar(2)
DECLARE @.Month nvarchar(2)
DECLARE @.Year nvarchar(4)
DECLARE @.Time nvarchar(12)

SET @.InputDate = '2005/03/01 14:30:00'
SET @.Day = cast(datepart(day,@.InputDate) as nvarchar(2))
SET @.Month = cast(datepart(month,@.InputDate) as nvarchar(2))
SET @.Year = cast(datepart(year,@.InputDate) as nvarchar(4))
SET @.Time = substring(cast(@.InputDate as nvarchar(23)),12,12)

SET @.OutputDate = replicate('0',2-len(@.Day)) + @.Day + '/' +
replicate('0',2-len(@.Month)) + @.Month + '/' +
@.Year + ' ' + @.Time

SELECT @.OutputDate AS OutputDate

Thx
VilenReturn dates as dates and format them for display in the front end or
middle tier. Some users may prefer to format them differently to the
way you do.

In the database dates should be stored as DATETIME or SMALLDATETIME
datatypes. These DO NOT have any fixed format and will always sort
chronologically. It isn't a good idea to sort on a function or
expression if you can avoid it.

--
David Portas
SQL Server MVP
--

Control the size of a text box via a report parameter

We have some note fields that are very large so reports take up too many
pages.
However we would like to either limit the field to lets say 4 lines or all
lines via some parameter.
Is there any way to do this?Did you look into using the Left() function to limit the field content to a
certain length based on a report parameter? You could use an expression
similar to this for the textbox value property:
=iif(Parameters!RestrictLength.Value = True,
Left(Fields!LongDescription.Value, 200), Fields!LongDescription.Value)
MSDN documentation for Left():
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vblr7/html/vafctLeft.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:eMmW3XeFFHA.3272@.TK2MSFTNGP10.phx.gbl...
> We have some note fields that are very large so reports take up too many
> pages.
> However we would like to either limit the field to lets say 4 lines or all
> lines via some parameter.
> Is there any way to do this?
>

Constraint and Linked Server query problem

Hi,

I've got a problem querying a remote table via linked server (SQL Server 2000 SP4).
If I do
select * from <linked server>.<database name>.<db owner>.<Table1>
it returns all data, as expected.
However, if I do:
select * from <linked server>.<database name>.<db owner>.<table name>
where ColumnID = 51588
it doesn't return a row, even though it exists in the remote table.

A likely reason for this could be the fact that the column ColumnID has a constraint on it:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [repl_identity_range_sub]
CHECK NOT FOR REPLICATION ([ColumnID] > 90000 and [ColumnID] < 95000)

I've tried making the constraint NOCHECK, but still get the same problem.
ALTER TABLE [dbo].[REGION_1] NOCHECK CONSTRAINT [repl_identity_range_sub]

Does anyone have an idea why this is hapenning and how to get around it (other than obviously just dropping the constraint - which works by the way, but can not be done as it is used).

Any help would be greatly appreciated.

NR

Hi,

never heard of that issue. If you have control over the remote server, start a SQl profiler session to see which statement is arriving on which database. Perhaps you are connection to the wrong server (or to another database). Querying with filters on remote server is no magic, so that should be no problem.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Already tried that and it's no help, all I can see is some system sps executing: sp_tables_info_rowset, sp_columns_rowset, sp_indexes_rowset, sp_check_constbytable_rowset, sp_table_statistics_rowset, a few DBCC SHOW_STATISTICS and then it executes sp_getschemalock followed by exec sp_releaseschemalock 1.

All those are for my table.

Weird thing is that a select * from the table works. Filtering on any other columns other than the one with constraint on it also works, it is only using the column with the constraint that cosistently does not work.

NR

|||

I would make sure that you are turning off constraints and doing the select in the same transaction. Otherwise I don't have any ideas.

It doesn't address the issue, but you may just want to do a pass-through openquery select to get around the issue.

|||

I'm afraid it doesn't help as the constraint is already marked as NOCHECK, and I'd rather not have to drop and recreate it (as it's put on by merge replication).

NR

|||I know it isn't a real answer to the problem, but if your needs are limited, you may want to just create a view that is outside the replication scheme and pull the remote data using the view. Good luck.|||

Hi,

I have the same problem and found the following solution to the problem.

I added an additional check constraint to each questionable table to deactivate the "optimization" filter that was intended for effective table partitioning:

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD CONSTRAINT [CK_Customer_AntiPartitioning] CHECK NOT FOR REPLICATION ([CustomerID] > 0)
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [CK_Customer_AntiPartitioning]

the trick is that the optimization feature needs an unambiguous check constraint set that is not the case, because the replication range always overlap with the above added range. the "nocheck constraint" statement disables actual checking so that you do not have significant performance penalty

Greetings - Richie

Constraint and Linked Server query problem

Hi,

I've got a problem querying a remote table via linked server (SQL Server 2000 SP4).
If I do
select * from <linked server>.<database name>.<db owner>.<Table1>
it returns all data, as expected.
However, if I do:
select * from <linked server>.<database name>.<db owner>.<table name>
where ColumnID = 51588
it doesn't return a row, even though it exists in the remote table.

A likely reason for this could be the fact that the column ColumnID has a constraint on it:
ALTER TABLE [dbo].[Table1] ADD
CONSTRAINT [repl_identity_range_sub]
CHECK NOT FOR REPLICATION ([ColumnID] > 90000 and [ColumnID] < 95000)

I've tried making the constraint NOCHECK, but still get the same problem.
ALTER TABLE [dbo].[REGION_1] NOCHECK CONSTRAINT [repl_identity_range_sub]

Does anyone have an idea why this is hapenning and how to get around it (other than obviously just dropping the constraint - which works by the way, but can not be done as it is used).

Any help would be greatly appreciated.

NR

Hi,

never heard of that issue. If you have control over the remote server, start a SQl profiler session to see which statement is arriving on which database. Perhaps you are connection to the wrong server (or to another database). Querying with filters on remote server is no magic, so that should be no problem.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Already tried that and it's no help, all I can see is some system sps executing: sp_tables_info_rowset, sp_columns_rowset, sp_indexes_rowset, sp_check_constbytable_rowset, sp_table_statistics_rowset, a few DBCC SHOW_STATISTICS and then it executes sp_getschemalock followed by exec sp_releaseschemalock 1.

All those are for my table.

Weird thing is that a select * from the table works. Filtering on any other columns other than the one with constraint on it also works, it is only using the column with the constraint that cosistently does not work.

NR

|||

I would make sure that you are turning off constraints and doing the select in the same transaction. Otherwise I don't have any ideas.

It doesn't address the issue, but you may just want to do a pass-through openquery select to get around the issue.

|||

I'm afraid it doesn't help as the constraint is already marked as NOCHECK, and I'd rather not have to drop and recreate it (as it's put on by merge replication).

NR

|||I know it isn't a real answer to the problem, but if your needs are limited, you may want to just create a view that is outside the replication scheme and pull the remote data using the view. Good luck.|||

Hi,

I have the same problem and found the following solution to the problem.

I added an additional check constraint to each questionable table to deactivate the "optimization" filter that was intended for effective table partitioning:

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD CONSTRAINT [CK_Customer_AntiPartitioning] CHECK NOT FOR REPLICATION ([CustomerID] > 0)
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [CK_Customer_AntiPartitioning]

the trick is that the optimization feature needs an unambiguous check constraint set that is not the case, because the replication range always overlap with the above added range. the "nocheck constraint" statement disables actual checking so that you do not have significant performance penalty

Greetings - Richie