Increase nvarchar field value like a num

Hi everybody,
i want write a stored procedure. This is increase NVARCHAR (7) field like a
number.
Example :
A00001
A00002
:
:
A99999
B00001
:
:
Z99999
AA00001
:
ZZ99999
:
Error
How can i do ? can i do this with t-sql?
thanksUse an insert trigger
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> i want write a stored procedure. This is increase NVARCHAR (7) field like
> a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
> thanks
>|||Stored procedure must return a value
"Martin" <x@.y.z>, haber iletisinde unlar
yazd:ORRgvV18FHA.476@.TK2MSFTNGP15.phx.gbl...
> Use an insert trigger
> "SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
> news:OA7jsm08FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||The fastest way to to do this would be to use a lookup table; set a
bigint value to be the order-determinant (eg, 1, 2, 3,) and use the
other values as a lookup:
CREATE TABLE (ID bigint, Value NVARCHAR(7))
INSERT INTO TABLE (ID, Value)
--write a routine to populate this
VALUES (1, 'A00001')
Your stored procedure would then return the ID value bases on the
values you supply, increment the ID by one, and return the next value
in sequence. Kind of like a calendar table or a table of numbers.
HTH,
Stu|||SharkSpeed (sharkspeedtr@.yahoo.com) writes:
> i want write a stored procedure. This is increase NVARCHAR (7) field
> like a number.
> Example :
> A00001
> A00002
> :
> :
> A99999
> B00001
> :
> :
> Z99999
> AA00001
> :
> ZZ99999
> :
> Error
>
> How can i do ? can i do this with t-sql?
DECLARE @.letters varchar(2)
@.digits varchar(5)
SELECT @.digits = right(@.input, 5),
@.letters = substring(@.input, 1,
CASE len(@.input) WHEN 6 THEN 1 ELSE 2 END)
IF @.digits <> '99999'
BEGIN
SELECT @.digits = substring(convert(varchar(
convert(int, @.digits) + 100001)), 2, 5)
END
ELSE IF len(@.letters) = 1 and @.letters <> 'Z'
SELECT @.letters = char(ascii(@.letters) + 1))
ELSE IF @.letters = 'Z'
SELECT @.letters = 'AA'
ELSE IF @.letters NOT LIKE '_Z'
SELECT @.letters = substring(@.letters, 1, 1) +
char(ascii(substring(@.letters, 2, 1) + 1))
ELSE IF @.letters <> 'ZZ'
SELECT @.letters = char(ascii(substring(@.letters, 1, 1)) + 1) + 'A'
ELSE
RAISERROR ('Cannot compute a successor key to ZZ99999', 16, 1)
I did not test this, nor did I try to compile. You should be able to
make something out of it anyway.
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|||In line with what Erland posted I had started on something.
I have now also tested so you could implement this as it is.. no
warranties though.
first create this table:
CREATE TABLE nextIDTable (preChar varchar(2), postInt int)
INSERT INTO nextIDTable (preChar, postInt) values ('A', 1)
Then once you have the table and inserted the values above you can
implement the stored procedure, calling this will increment the varchar
"number" as you described you wanted:
CREATE PROC getNextID
@.nextID nvarchar(7) OUTPUT
AS
DECLARE @.MyCounter INT, @.LeadingZeros char(4), @.preChar varchar(2),
@.postInt int
-- Initialize the variable.
SET @.MyCounter = 0
SET @.postInt = (SELECT postInt FROM nextIDTable)
SET @.preChar = (SELECT RTRIM(preChar) FROM nextIDTable)
IF(@.postInt < 10) SET @.LeadingZeros = '0000'
IF(@.postInt >= 10 AND @.postInt < 100) SET @.LeadingZeros = '000'
IF(@.postInt >= 100 AND @.postInt < 1000) SET @.LeadingZeros = '00'
IF(@.postInt >= 1000 AND @.postInt < 90001) SET @.LeadingZeros = '0'
WHILE (@.MyCounter <= 51)
BEGIN
-- the loop is exited when @.MyCounter reaches -1
-- as all from ZZ to A have been checked
IF @.MyCounter = -1 return
-- for A through to Z
IF(@.MyCounter <= 25)
BEGIN
IF(@.postInt = 99999 and @.preChar = 'Z')
BEGIN
SET @.nextID = 'AA00001'
UPDATE nextIDTable SET preChar = 'AA', postInt = 1
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'Z')
BEGIN
IF(@.preChar = (CHAR(((@.MyCounter) + ASCII('A')))))
BEGIN
SET @.nextID = (CHAR(((@.MyCounter + 1) + ASCII('A')))) + '00001'
UPDATE nextIDTable SET preChar = CHAR(((@.MyCounter + 1) +
ASCII('A'))), postInt = 1
BREAK
END
END
ELSE
BEGIN
SET @.nextID = (@.preChar + RTRIM(@.LeadingZeros) + (CONVERT( char,
@.postInt)))
UPDATE nextIDTable SET postInt = postInt + 1
BREAK
END
END
-- for AA through to ZZ
IF(@.MyCounter > 25)
BEGIN
IF(@.postInt = 99999 AND @.preChar = 'ZZ')
BEGIN
-- reached the max value
RAISERROR('reached max val', 16, 1)
BREAK
END
IF(@.postInt = 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
-- next char sequence + 00001
SET @.nextID = CHAR(((@.MyCounter - 26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + '00001'
UPDATE nextIDTable SET preChar = CHAR((@.MyCounter-26 + ASCII('A')))
+ CHAR((@.MyCounter-26 + ASCII('A'))), postInt = 1
BREAK
END
IF(@.postInt < 99999 AND @.preChar <> 'ZZ' AND @.preChar NOT IN (select
preChar from nextIDTable where len(preChar) < 2))
BEGIN
SET @.nextID = CHAR(((@.MyCounter-26) + ASCII('A'))) +
CHAR((@.MyCounter-26 + ASCII('A'))) + RTRIM(@.LeadingZeros) + CONVERT(
char, @.postInt)
UPDATE nextIDTable SET postInt = @.postInt + 1
BREAK
END
END
SET @.MyCounter = @.MyCounter + 1
END
GO
good luck with it..
Gerard|||actually I just found there is a wee bug in the part after
IF(@.MyCounter > 25)
if your value is AA99999 it will jump to GG00001 but I think there's
enough here to make this work
Gerard

Increase field size AND linked tables

I would like to increase a field size to allow input of up to 16 characters
versus the current 11, but the message is that I can't do the modification
due to linked tables. I believe the modification must be done at the server
,
but I wouldn't know where to start. It would seem that it should be an easy
fix if someone could point me in the right direction. I hate to call the
tech guys out to open a couple windows and type 16. The database was done i
n
access converted to SQL.
ThanksDear rtucker913,
Please post DDL or any example which clarifies your request.
Thanks in advance,
"rtucker913" wrote:

> I would like to increase a field size to allow input of up to 16 character
s
> versus the current 11, but the message is that I can't do the modification
> due to linked tables. I believe the modification must be done at the serv
er,
> but I wouldn't know where to start. It would seem that it should be an ea
sy
> fix if someone could point me in the right direction. I hate to call the
> tech guys out to open a couple windows and type 16. The database was done
in
> access converted to SQL.
> Thankssql

Increase # of chars Query Editor when looking at large varchar field

How can I increase the # of chars so that when I copy a varchar(5500) field from Query Editor back into my Query pane, it displays all the characters, and doesn't cut it off?That is a limitation of the query windows.You will have to navigate through the menu Tools > Options > Query results > Result to text > Maximum numbers of characters displayed in one rows (<- increase this here)

HTH, Jens K. Suessmeyer.

http://www.wsqlserver2005.de|||Thank you, I know I increased that, but was looking at a record which only had a short length of chars to begin with so I wasn't seeing that the increase was actually working but good to know I was in the right place at least. Thanks!sql

Incorrect syntax near '5' where 5 is the beginning of a field name

Hi all
i get the following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near
'5'. (#170)
when trying to update or delete a record from an Access Linked table. Adding
a record is fine. There is a field called 5Years, but this has not been a
problem before. Only occurred when database was moved to a new server.
SQL server version on current and old server is 2000 sp3a
jwIt's always been my understanding that fields can't start with certain
characters, like punctuation characters and numbers.
I'm surprised this ever worked. It may have been a bug that was fixed in
your recent release?
Perhaps someone else has a more knowledgeable response.sql

Incorrect syntax near '5' where 5 is the beginning of a field name

Hi all
i get the following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
'5'. (#170)
when trying to update or delete a record from an Access Linked table. Adding
a record is fine. There is a field called 5Years, but this has not been a
problem before. Only occurred when database was moved to a new server.
SQL server version on current and old server is 2000 sp3a
jw
It's always been my understanding that fields can't start with certain
characters, like punctuation characters and numbers.
I'm surprised this ever worked. It may have been a bug that was fixed in
your recent release?
Perhaps someone else has a more knowledgeable response.

Incompatible Data Types question

Hi,

I've got a dimension table with an Int32 primary key. I have a few fact tables that have an Int16 field, which is acting as a foreign key to the dimension table. I can't actually change the data types of these tables in the SQL database, but I'd still like to create a cube that uses those 32 bit dimension tables with the 16 bit field entries. Unfortunately, the cube designer won't let me specify a relationshop between the two tables because the fields are different data-types.
What I did is create Calculated Member fields which cast the 16 bit value to a 32 bit value, and then create the relationship. I was wondering if anyone else had a different solution?

Thanks,
KobiWe had to create temporary tables and cast the fields into matching data types.

Including SQL Server Allow Nulls fields in updateable controls

When I include a field from my SQL Server database, which has it's Allow Nulls value checked, in the data source of any type of control with it's Enable Editing property check, I then can not edit the record! If I remove the Allow Nulls field I can then edit it! What am I missing here?

If your record is going to have null values in it (which is quite common) you have to add a DataSet file to your site, create a TableAdapter and then use the TableAdapter as the data source 'Object' for the data control. ... ... Wait, ... let me breath... ... that's not the end of it... ... ... When choosing the data source for the control, make sure when you get to the Define Parameters dialog that the advanced property ConvertEmptyStringToNull is true. It took me about 12 hours to figure this out but I have also read that it has taken others up to a week so I guess I'm doing good. I REALLY WISH MICROSOFT WOULD HAVE SPELLED THIS OUT MORE CLEARLY IN THEIR HELP FILES.

Including NULL Option in WHERE Clause

Hi,
I'm trying to create predicates that will work the same way whether a field
contains a blank or a null value. This is based on the fact that nulls get
converted to blanks when loaded into a VS control, then loaded into the
e.Values or e.OldValues arrays when deleting or updating the current row,
respectively, in a VS/C# FormView.
At the point where the query is constructed, I cannot tell whether the
original value was a null or not, so I attempted to use a CASE WHEN statement
in my WHERE clause like the following:
... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
ELSE '' END) ...
However, this doesn't work because the when [Address] is null, the CASE
statement returns a null which results in the predicate containing:
... AND (LTRIM(RTRIM([Address])) = null) ...
and that is not the same as:
... AND (LTRIM(RTRIM([Address])) IS null) ...
the first returns FALSE event though [Address] is null, while the second
returns TRUE.
I would further like to be able to construct this solution or any other that
works in a generic method that can be called by any query to construct its
predicate for each field where this situation is a possibility.
ThanksI think you're looking for the ISNULL function. It would be used like this:
AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
"WJB" wrote:
> Hi,
> I'm trying to create predicates that will work the same way whether a field
> contains a blank or a null value. This is based on the fact that nulls get
> converted to blanks when loaded into a VS control, then loaded into the
> e.Values or e.OldValues arrays when deleting or updating the current row,
> respectively, in a VS/C# FormView.
> At the point where the query is constructed, I cannot tell whether the
> original value was a null or not, so I attempted to use a CASE WHEN statement
> in my WHERE clause like the following:
> ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> ELSE '' END) ...
> However, this doesn't work because the when [Address] is null, the CASE
> statement returns a null which results in the predicate containing:
> ... AND (LTRIM(RTRIM([Address])) = null) ...
> and that is not the same as:
> ... AND (LTRIM(RTRIM([Address])) IS null) ...
> the first returns FALSE event though [Address] is null, while the second
> returns TRUE.
> I would further like to be able to construct this solution or any other that
> works in a generic method that can be called by any query to construct its
> predicate for each field where this situation is a possibility.
> Thanks|||Not exactly. I had tried that already. The problem is that the field itself
actually could contain either null or blank. The ISNULL function as below
works if the field is blank but not if it is null. I need a solution that
works for both. Essentially, it needs to be a test that uses one value if
true and another if false. The problem is that "= null" in SQL Server is not
the same as "is null". If the ISNULL function had an overload that would
return a value if false and would equate to "IS NULL" if true, that would
work.
Can you suggest any other options?
Thanks
"Code Wench" wrote:
> I think you're looking for the ISNULL function. It would be used like this:
> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> "WJB" wrote:
> > Hi,
> >
> > I'm trying to create predicates that will work the same way whether a field
> > contains a blank or a null value. This is based on the fact that nulls get
> > converted to blanks when loaded into a VS control, then loaded into the
> > e.Values or e.OldValues arrays when deleting or updating the current row,
> > respectively, in a VS/C# FormView.
> >
> > At the point where the query is constructed, I cannot tell whether the
> > original value was a null or not, so I attempted to use a CASE WHEN statement
> > in my WHERE clause like the following:
> >
> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> > ELSE '' END) ...
> >
> > However, this doesn't work because the when [Address] is null, the CASE
> > statement returns a null which results in the predicate containing:
> >
> > ... AND (LTRIM(RTRIM([Address])) = null) ...
> >
> > and that is not the same as:
> >
> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
> >
> > the first returns FALSE event though [Address] is null, while the second
> > returns TRUE.
> >
> > I would further like to be able to construct this solution or any other that
> > works in a generic method that can be called by any query to construct its
> > predicate for each field where this situation is a possibility.
> >
> > Thanks|||Well, did you try:
LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
"WJB" <WJB@.discussions.microsoft.com> wrote in message
news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
> Not exactly. I had tried that already. The problem is that the field
> itself
> actually could contain either null or blank. The ISNULL function as below
> works if the field is blank but not if it is null. I need a solution that
> works for both. Essentially, it needs to be a test that uses one value if
> true and another if false. The problem is that "= null" in SQL Server is
> not
> the same as "is null". If the ISNULL function had an overload that would
> return a value if false and would equate to "IS NULL" if true, that would
> work.
> Can you suggest any other options?
> Thanks
> "Code Wench" wrote:
>> I think you're looking for the ISNULL function. It would be used like
>> this:
>> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
>> "WJB" wrote:
>> > Hi,
>> >
>> > I'm trying to create predicates that will work the same way whether a
>> > field
>> > contains a blank or a null value. This is based on the fact that nulls
>> > get
>> > converted to blanks when loaded into a VS control, then loaded into the
>> > e.Values or e.OldValues arrays when deleting or updating the current
>> > row,
>> > respectively, in a VS/C# FormView.
>> >
>> > At the point where the query is constructed, I cannot tell whether the
>> > original value was a null or not, so I attempted to use a CASE WHEN
>> > statement
>> > in my WHERE clause like the following:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
>> > null
>> > ELSE '' END) ...
>> >
>> > However, this doesn't work because the when [Address] is null, the CASE
>> > statement returns a null which results in the predicate containing:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) = null) ...
>> >
>> > and that is not the same as:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
>> >
>> > the first returns FALSE event though [Address] is null, while the
>> > second
>> > returns TRUE.
>> >
>> > I would further like to be able to construct this solution or any other
>> > that
>> > works in a generic method that can be called by any query to construct
>> > its
>> > predicate for each field where this situation is a possibility.
>> >
>> > Thanks|||Thanks, Aaron & Code Wench. Almost there. I think what I actually need is a
combination of the two answers, i.e.
LTRIM(RTRIM(ISNULL([Address], '')) = '' (in reality @.Address)
Since the e.Values and e.OldValues arrays are loaded with blanks by the
SQLDataSource/ObjectDataSource objects, this ISNULL in this case converts the
current value of null to a blank and so CompareAllValues works whether the
current value is blank or null. If it is not null, however, then ISNULL
returns [Address] and if that was also the original value, or the original
value of Address if not.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, did you try:
>
> LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
>
> "WJB" <WJB@.discussions.microsoft.com> wrote in message
> news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
> > Not exactly. I had tried that already. The problem is that the field
> > itself
> > actually could contain either null or blank. The ISNULL function as below
> > works if the field is blank but not if it is null. I need a solution that
> > works for both. Essentially, it needs to be a test that uses one value if
> > true and another if false. The problem is that "= null" in SQL Server is
> > not
> > the same as "is null". If the ISNULL function had an overload that would
> > return a value if false and would equate to "IS NULL" if true, that would
> > work.
> >
> > Can you suggest any other options?
> >
> > Thanks
> >
> > "Code Wench" wrote:
> >
> >> I think you're looking for the ISNULL function. It would be used like
> >> this:
> >>
> >> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> >>
> >> "WJB" wrote:
> >>
> >> > Hi,
> >> >
> >> > I'm trying to create predicates that will work the same way whether a
> >> > field
> >> > contains a blank or a null value. This is based on the fact that nulls
> >> > get
> >> > converted to blanks when loaded into a VS control, then loaded into the
> >> > e.Values or e.OldValues arrays when deleting or updating the current
> >> > row,
> >> > respectively, in a VS/C# FormView.
> >> >
> >> > At the point where the query is constructed, I cannot tell whether the
> >> > original value was a null or not, so I attempted to use a CASE WHEN
> >> > statement
> >> > in my WHERE clause like the following:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
> >> > null
> >> > ELSE '' END) ...
> >> >
> >> > However, this doesn't work because the when [Address] is null, the CASE
> >> > statement returns a null which results in the predicate containing:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) = null) ...
> >> >
> >> > and that is not the same as:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
> >> >
> >> > the first returns FALSE event though [Address] is null, while the
> >> > second
> >> > returns TRUE.
> >> >
> >> > I would further like to be able to construct this solution or any other
> >> > that
> >> > works in a generic method that can be called by any query to construct
> >> > its
> >> > predicate for each field where this situation is a possibility.
> >> >
> >> > Thanks
>
>

Including New Tables in an Existing Publication

Hi.
I work for a company with 40+ remote workstations that synchronize their
databases wirelessly to receive new jobs, as well as submit field tickets
created on their laptops. This time of the year is our busiest period,
however we have some new tables in the database that we would like to
include in the replication scheme. It is my understanding that you can do
this, however you have to ensure that all of your subscriptions have
synchronized and that no data is modified until the new tables are included
in the publication, the dynamic snapshot jobs are re-run, and the
subscriptions have been re-created. Is this correct? If it is, herein lies
my problem. Because our company is in our busy period, the chances of this
happening are somewhere in between none and impossible. I was wondering if
any of you have encountered a situation like this before, and what you did
to "make the job happen". Any suggestions are MUCH appreciated, and if you
require any more information, please do not hesitate to let me know.
Best Regards,
Brad
PS - We are using SQL2000, with Merge Replication. Sorry I didn't include
this in the first post.
Brad
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:%23QGMl1X3EHA.3840@.tk2msftngp13.phx.gbl...
> Hi.
> I work for a company with 40+ remote workstations that synchronize their
> databases wirelessly to receive new jobs, as well as submit field tickets
> created on their laptops. This time of the year is our busiest period,
> however we have some new tables in the database that we would like to
> include in the replication scheme. It is my understanding that you can do
> this, however you have to ensure that all of your subscriptions have
> synchronized and that no data is modified until the new tables are
> included in the publication, the dynamic snapshot jobs are re-run, and the
> subscriptions have been re-created. Is this correct? If it is, herein
> lies my problem. Because our company is in our busy period, the chances
> of this happening are somewhere in between none and impossible. I was
> wondering if any of you have encountered a situation like this before, and
> what you did to "make the job happen". Any suggestions are MUCH
> appreciated, and if you require any more information, please do not
> hesitate to let me know.
> Best Regards,
> Brad
>
|||I think you would be best to create a new publication to distribute these
articles.
Either that or do something like this:
sp_addmergearticle 'Publication', 'NewTable', 'NewTable',
@.force_invalidate_snapshot =1 which will generate and distribute an entire
new snapshot of all the articles.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:O48xI8Z3EHA.2624@.TK2MSFTNGP11.phx.gbl...
> PS - We are using SQL2000, with Merge Replication. Sorry I didn't include
> this in the first post.
> Brad
> "Brad M." <anonymous@.discussions.microsoft.com> wrote in message
> news:%23QGMl1X3EHA.3840@.tk2msftngp13.phx.gbl...
>
|||So will this affect my users in any way? The main thing I need to know is
that will my users still be able to run their app, create records, save
them, sync, receive the new tables, and the data they created before last
sync - will it still be there, and will it make it back to the publisher?
I've thought about creating a second publication, but for ease of
administration, I'd like to keep everything in one publication if at all
possible.
Thanks alot for your help Hilary!
Best Regards,
Brad
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eOb3zNf3EHA.2156@.TK2MSFTNGP10.phx.gbl...
>I think you would be best to create a new publication to distribute these
>articles.
> Either that or do something like this:
> sp_addmergearticle 'Publication', 'NewTable', 'NewTable',
> @.force_invalidate_snapshot =1 which will generate and distribute an entire
> new snapshot of all the articles.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Brad M." <anonymous@.discussions.microsoft.com> wrote in message
> news:O48xI8Z3EHA.2624@.TK2MSFTNGP11.phx.gbl...
>
|||A safe way (I've done this) would be to add a second publication on your
server containing the new tables...
On the subscribers add a second subscription... This will leave your
production stuff in place & gives you time to roll out the new tables--
Only downside now there are 80 subs in the field logging in, + the
additional sub on the client (logs in twice... so its slower)
Once you have some down time you could then combine the 2 & redo all the
clients... (you can build scripts for this..)
"Brad M." <anonymous@.discussions.microsoft.com> wrote in message
news:O$$gO8f3EHA.524@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> So will this affect my users in any way? The main thing I need to know is
> that will my users still be able to run their app, create records, save
> them, sync, receive the new tables, and the data they created before last
> sync - will it still be there, and will it make it back to the publisher?
> I've thought about creating a second publication, but for ease of
> administration, I'd like to keep everything in one publication if at all
> possible.
> Thanks alot for your help Hilary!
> Best Regards,
> Brad
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eOb3zNf3EHA.2156@.TK2MSFTNGP10.phx.gbl...
entire[vbcol=seagreen]
their[vbcol=seagreen]
busiest[vbcol=seagreen]
that[vbcol=seagreen]
between
>
|||Okay, first let me thank you for helping me out. I realize that there is an
option to add a second publication, but I'd really like to avoid this if at
all possible. So, is it technically possible to add articles to merge
publications without reinitializing the subscriptions/affecting any data
entered by the subscriber which has not yet replicated to the publisher?
Basically, the reason why I'm trying to avoid the 2nd pub is because our
busy period is our *busiest* - we are still slammed in the spring, summer
and fall and we never get an opportunity to see all of the subscribers at
once - so I would never have enough downtime to redo the subscribers in a
timely manner which would allow for uninterrupted flow of data (which senior
management in my company demands).
If all of our merge subscriptions are dynamic snapshots (which they are),
and I want to add articles to the pub, will this require reinitializing each
subscriber?
Thanks to all of you for your responses and answers.
Best Regards,
Brad
"S c o t t K r a m e r" <sckramer2000@.hotmail.com> wrote in message
news:vc0ud.227$bu1.134@.fe61.usenetserver.com...
>A safe way (I've done this) would be to add a second publication on your
> server containing the new tables...
> On the subscribers add a second subscription... This will leave your
> production stuff in place & gives you time to roll out the new tables--
> Only downside now there are 80 subs in the field logging in, + the
> additional sub on the client (logs in twice... so its slower)
> Once you have some down time you could then combine the 2 & redo all the
> clients... (you can build scripts for this..)
>
> "Brad M." <anonymous@.discussions.microsoft.com> wrote in message
> news:O$$gO8f3EHA.524@.TK2MSFTNGP09.phx.gbl...
> entire
> their
> busiest
> that
> between
>
>

Include ID field in GROUP BY statement

I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.

The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':

ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83

And run the query:

select max(Cost),FruitName From Fruits
group by FruitName

It'll correctly return:

FruitName Cost
Apple 0.63
Pear 0.89

Now i need the ID also returned by my query so i go:

select max(Cost),FruitName,ID From Fruits
group by FruitName,ID

This doesnt return the above results with the ID appended to it, it instead returns:

ID FruitName Cost
1 Apple 0.45
2 Apple 0.63
3 Apple 0.52
4 Pear 0.89
5 Pear 0.83

As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:

ID FruitName Cost
2 Apple 0.63
4 Pear 0.89

Thanks.

I don't think you can do that with the ID field because there are multiple Ids for each item while using the group by. Can you use a Having clause? ie

select Cost,FruitName From Fruits
group by FruitName
having max(Cost)

This is a shot in the dark as i did not actually try your data. Hope it works though :)

|||

Thanks but dont think that would work as the 'HAVING' as i see it is just used to filter the results of the query so you'd use it if for example you wanted to only show max fuit more than ￡0.10

select Cost,FruitName From Fruit
group by FruitName,Cost
having max(Cost) > 0.1

Thats how i see it (could be wrong). But it still doesnt handle the problem of the ID.

|||

Hi,

This is a round about way, but it works

Select

FruitName,cost,(Select IDfrom Fruits fWhere f.Cost=frt.cost)as IDfrom

(

select FruitName,max(cost)as costFrom Fruits

group

by FruitName) frt|||

Hi TheGrox,

The below query may help you.

select id,fruitname,costfrom (select row_number()over (partitionby fruitnameorder by costdesc)as rownum,id,fruitname,costfrom Fruits) Fwhere rownum = 1

The main trick here is using the ROW_NUMBER() OVER (PARTITION BY...) clause

This is very like getting top n records from a table belonging to each category. You can follow the article athttp://www.kodyaz.com/articles/top-n-random-rows-foreach-category-or-group.aspx for more details and a sample for ROW_NUMBER() OVER (PARTITION BY...)

Eralper

http://www.kodyaz.com

|||

If you are using SQL 2000 you can try this:

Select t1.FruitName,t2.cost, Idfrom Fruits t1join (select FruitName,max(cost)as costFrom Fruitsgroup by FruitName ) t2on t1.FruitName = t2.fruitnameand t1.Cost = t2.cost

|||

Here is another one:

select t1.*from fruitsas t1where t1.ID= (select top 1 t2.IDfrom fruitsas t2where t2.FruitName = t1.FruitNameorder by t2.costDESC)

|||

Limno

I like your query better than mine. I am joining on name and cost. If there are multiple records with same name and costs, the join could return multiple records for same "Fruit" which might be incorrect.

|||I learned that solution from Umachandar Jayachandran. He is super.

In with Like

Hi all,
I would like to limit a field using a where clause then use a wildcard
search to further limit the values.
For instance using PUBS:
SELECT *
FROM authors
WHERE (au_fname IN (LIKE ‘ Jo % ’, LIKE ‘ Bill % ’))
In other words allowing me to choose a wildcard from a subset of the original.
Any ideas? Most grateful.
Sam
SELECT au_fname
FROM authors
WHERE au_fname LIKE 'Jo %'
OR au_fname LIKE 'Bill %'
or:
SELECT au_fname
FROM authors,
(SELECT 'Jo %' UNION ALL
SELECT 'Bill %') AS T(fname)
WHERE au_fname LIKE T.fname
David Portas
SQL Server MVP
|||Hi Sam,
The WHERE clause with a condition is used to filter the rows from a given
table. Just using WHERE clause doesn't limit the field (as mentioned by you).
I guess you are looking at something like this:
SELECT *
FROM authors
WHERE (au_fname LIKE 'Jo%' OR au_fname LIKE 'Bill%')
Thanks
Yogish

In with Like

Hi all,
I would like to limit a field using a where clause then use a wildcard
search to further limit the values.
For instance using PUBS:
SELECT *
FROM authors
WHERE (au_fname IN (LIKE ‘ Jo % ’, LIKE ‘ Bill % ’))
In other words allowing me to choose a wildcard from a subset of the origina
l.
Any ideas? Most grateful.
SamSELECT au_fname
FROM authors
WHERE au_fname LIKE 'Jo %'
OR au_fname LIKE 'Bill %'
or:
SELECT au_fname
FROM authors,
(SELECT 'Jo %' UNION ALL
SELECT 'Bill %') AS T(fname)
WHERE au_fname LIKE T.fname
David Portas
SQL Server MVP
--|||Hi Sam,
The WHERE clause with a condition is used to filter the rows from a given
table. Just using WHERE clause doesn't limit the field (as mentioned by you)
.
I guess you are looking at something like this:
SELECT *
FROM authors
WHERE (au_fname LIKE 'Jo%' OR au_fname LIKE 'Bill%')
Thanks
Yogish

In with Like

Hi all,
I would like to limit a field using a where clause then use a wildcard
search to further limit the values.
For instance using PUBS:
SELECT *
FROM authors
WHERE (au_fname IN (LIKE â' Jo % â', LIKE â' Bill % â'))
In other words allowing me to choose a wildcard from a subset of the original.
Any ideas? Most grateful.
SamSELECT au_fname
FROM authors
WHERE au_fname LIKE 'Jo %'
OR au_fname LIKE 'Bill %'
or:
SELECT au_fname
FROM authors,
(SELECT 'Jo %' UNION ALL
SELECT 'Bill %') AS T(fname)
WHERE au_fname LIKE T.fname
--
David Portas
SQL Server MVP
--|||Hi Sam,
The WHERE clause with a condition is used to filter the rows from a given
table. Just using WHERE clause doesn't limit the field (as mentioned by you).
I guess you are looking at something like this:
SELECT *
FROM authors
WHERE (au_fname LIKE 'Jo%' OR au_fname LIKE 'Bill%')
--
Thanks
Yogish

In TSQL how to use OPENXML to open an ntext field in a table? I can not read the ntext int

In TSQL how to use OPENXML to open an ntext field in a table? I can not read
the ntext into a local var as TSQL does not allowe ntext local variables.Already answered elsewhere...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Daniel" <softwareengineer98037@.yahoo.com> wrote in message
news:eR%23c5xVLGHA.2320@.TK2MSFTNGP11.phx.gbl...
> In TSQL how to use OPENXML to open an ntext field in a table? I can not
> read
> the ntext into a local var as TSQL does not allowe ntext local variables.
>

In Stored Proc - How do i find the next key value (integer) and use it to populate a field

Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005.

What I want is that -

1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)

2). and set COMPANY_ID = (new) ID

Any thoughts?

Thanks

ALTER PROCEDURE usp_tbl_Company_Insert
@.Company_ID int,
@.Name varchar(200),

AS

<FIND THE NEW ID of the new row in the database>

@.Company_ID = (new ID)

INSERT INTO tbl_Company (Company_ID, Name,)
VALUES (@.Company_ID, @.Name)

If its an IDENTITY column then you cannot insert any value into the field. SQL Server will insert that value for you. If you want to find out the value that was inserted use SCOPE_IDENTITY() function immediately after the insert. You can also get the value into a variable and use it accordingly.

In SQL2000 what is = to MySQL's field type of LONGTEXT?

I just found out that MYSQL handels LONGTEXT and I know that SQL2000 does not
have such a field type. When I first was developing my DB application I tried
to use BLOB's unsuccessfully and ended up using an XML data solution instead
for large text amounts. (The application is simple field storing webpage
content.) Currenly I've configured the text fields as VarChars but that
definitely has some limits. Thanks if you can help.
SQL Server 2000's version is TEXT (a member of the BLOB family)
SQL Server 2005 has NVARCHAR(MAX) which can be used a lot easier.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bobobenito" <bobobenito@.discussions.microsoft.com> wrote in message
news:975E78A2-92DC-4300-AF7E-A1D73DC9DB58@.microsoft.com...
>I just found out that MYSQL handels LONGTEXT and I know that SQL2000 does
>not
> have such a field type. When I first was developing my DB application I
> tried
> to use BLOB's unsuccessfully and ended up using an XML data solution
> instead
> for large text amounts. (The application is simple field storing webpage
> content.) Currenly I've configured the text fields as VarChars but that
> definitely has some limits. Thanks if you can help.

In SQL2000 what is = to MySQL's field type of LONGTEXT?

I just found out that mysql handels LONGTEXT and I know that SQL2000 does no
t
have such a field type. When I first was developing my DB application I trie
d
to use BLOB's unsuccessfully and ended up using an XML data solution instead
for large text amounts. (The application is simple field storing webpage
content.) Currenly I've configured the text fields as VarChars but that
definitely has some limits. Thanks if you can help.SQL Server 2000's version is TEXT (a member of the BLOB family)
SQL Server 2005 has NVARCHAR(MAX) which can be used a lot easier.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bobobenito" <bobobenito@.discussions.microsoft.com> wrote in message
news:975E78A2-92DC-4300-AF7E-A1D73DC9DB58@.microsoft.com...
>I just found out that mysql handels LONGTEXT and I know that SQL2000 does
>not
> have such a field type. When I first was developing my DB application I
> tried
> to use BLOB's unsuccessfully and ended up using an XML data solution
> instead
> for large text amounts. (The application is simple field storing webpage
> content.) Currenly I've configured the text fields as VarChars but that
> definitely has some limits. Thanks if you can help.

In SQL2000 what is = to MySQL's field type of LONGTEXT?

I just found out that MYSQL handels LONGTEXT and I know that SQL2000 does not
have such a field type. When I first was developing my DB application I tried
to use BLOB's unsuccessfully and ended up using an XML data solution instead
for large text amounts. (The application is simple field storing webpage
content.) Currenly I've configured the text fields as VarChars but that
definitely has some limits. Thanks if you can help.SQL Server 2000's version is TEXT (a member of the BLOB family)
SQL Server 2005 has NVARCHAR(MAX) which can be used a lot easier.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bobobenito" <bobobenito@.discussions.microsoft.com> wrote in message
news:975E78A2-92DC-4300-AF7E-A1D73DC9DB58@.microsoft.com...
>I just found out that MYSQL handels LONGTEXT and I know that SQL2000 does
>not
> have such a field type. When I first was developing my DB application I
> tried
> to use BLOB's unsuccessfully and ended up using an XML data solution
> instead
> for large text amounts. (The application is simple field storing webpage
> content.) Currenly I've configured the text fields as VarChars but that
> definitely has some limits. Thanks if you can help.