Doing a select count(*) on a table that I believe has more rows than the max
value of an int gives me an arithmetic overflow.
How can I rewrite my query to find the no. of rows ?Hi
Look up COUNT_BIG in Books online.
John
"Hassan" wrote:
> Doing a select count(*) on a table that I believe has more rows than the m
ax
> value of an int gives me an arithmetic overflow.
> How can I rewrite my query to find the no. of rows ?
>
>
Showing posts with label int. Show all posts
Showing posts with label int. Show all posts
Thursday, March 29, 2012
Friday, March 23, 2012
Generating Sequence Number....
Hi,
I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.
Col1
--
1
1
1
1
2
2
3
3
3
3
3
4
5
6
6
7
And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.......
Col1 Col2
-- --
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1
Thanks In Advance,
Rahul JhaI thought this would be a fun one to try - so here's a very poor attempt using an icky cursor... I don't think I have to explain to a DBA such as yourself why you shouldn't implement this method ;)
DECLARE @.col1 int
DECLARE @.col2 int
DECLARE @.id int
DECLARE @.i int
DECLARE @.x int
DECLARE @.myTable table (
id int identity(1,1)
, col1 int
, col2 int
)
SET NOCOUNT ON
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(4)
INSERT INTO @.myTable(col1) VALUES(5)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(7)
SET @.i = 1
SET @.x = 1
DECLARE theIckyCursor cursor
FOR
SELECT id
, col1
FROM @.myTable
ORDER
BY col1 ASC
OPEN theIckyCursor
FETCH NEXT FROM theIckyCursor INTO @.id, @.col1
WHILE @.@.Fetch_Status <> -1 BEGIN
IF @.x <> @.col1 BEGIN
SET @.x = @.x + 1
SET @.i = 1
END
UPDATE @.myTable
SET col2 = @.i
WHERE id = @.id
SET @.i = @.i + 1
FETCH NEXT FROM theIckyCursor INTO @.id, @.col1
END
CLOSE theIckyCursor
DEALLOCATE theIckyCursor
SELECT col1
, col2
FROM @.myTable
SET NOCOUNT OFF
You know... If you provide us with *real* data, rather than just T1, T2 etc then a better answer could be provided.
(Basically, what you're trying to do sounds very wrong!)|||thnkx georgy. Even I was trying on the same line :-). But what I am looking forward is some process (without cursor) or just the query that can do the needfull. there is a huge data migration to be done. hence suggest me some thing that doesn't eat much of the system resource.|||a table with only one column where there are duplicate values cannot possibly have a primary key, so you cannot expect a good solution
export your data to excel, use a macro to apply the sequence numbers
otherwise, tell us your real situation, not this fantasy of only one column|||Ok, let's go back to the real world scenario.
Can you provide proper column headings etc and describe WHY you want to add a sequence number?
If you explain your justification for this design; perhaps we can suggest a better solution :)
EDIT: Sniped!|||Goergy I won't able able to give you the real world schema as it's highly confidential. Scenario is exactly teh same as i said. the only difference is that there are more columns than I said in both the tables.
Why I need this, is because of i am migrating the data from and de-normalised database to a normalised one.
Thanks,
Rahul Jha|||can you use an ORDER BY to get the rows into the necessary sequence for numbering them?|||select col1,row_number()over(partition by col1 order by col1)col2 into t2 from t1|||row_number?
Can you make it more clear?
Thanks,
Rahul Jha|||row_number() (http://www.databasejournal.com/features/mssql/article.php/3572301)|||pdreyer's solution actually works. I have learned something new today :)|||IVON, can you paste the code for the same. I am not getting the meaning of row_number(). How to use this function.
I'll be obliged if any one let me know on the query that pdreyer has written.
Thanks,
Rahul Jha|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000. And my DB is in SS 2000.
Thanks,
Rahul Jha|||please answer the question in post #7|||This one ought to leave a mark!SELECT 1 AS ick
INTO #junque
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
UNION ALL SELECT 7
SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junque
UPDATE #foo
SET bat = (SELECT Count(*)
FROM #foo AS baz
WHERE baz.ick = #foo.ick
AND baz.bar <= #foo.bar)
SELECT ick, bat
FROM #foo
DROP TABLE #foo
DROP TABLE #junque-PatP|||ORDER BY on COL1 can be done......... Let me know if i hv cleared ur doubt or not............|||have you seen pats post on page 1|||This one ought to leave a mark!ouch!
[applies mark remover]|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000.
True, true.
And my DB is in SS 2000.
We didn't know that.|||ORDER BY on COL1 can be doneexcellent
here is your solution:
select ( select count(*) + 1
from daTable
where Col1 < T.Col1 ) as rownum
, Col1
from daTable as T
order
by Col1
rownum Col1
1 1
1 1
1 1
1 1
5 2
5 2
7 3
7 3
7 3
7 3
7 3
12 4
13 5
14 6
14 6
16 7
here is pdreyer's ROW_NUMBER() solution for comparison --
select row_number() over
( partition by Col1
order by Col1 ) as rownum
, Col1
from daTable
order
by Col1
rownum Col1
1 1
2 1
3 1
4 1
1 2
2 2
1 3
2 3
3 3
4 3
5 3
1 4
1 5
1 6
2 6
1 7
if you remove the PARTITION BY clause, then the result is
rownum Col1
1 1
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 3
11 3
12 4
13 5
14 6
15 6
16 7|||You guys rock............
Thanks for the solution.......... Thanks patty, r937 and pdreyer......... gr8 help that was.
Thanks,
Rahul Jha|||SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junqueI had no idea you could insert identity values that way.|||Thanks patty
*laughs out loud*|||:) :) Don Laugh Georgy............... :) :)
I really learnt few things thru this thread.........
Thanks guys once again...... You all are awesome......... Nothing to fear Rahul till the time you guys are there......... :) :) :)
Thanks,
Rahul Jha|||I had no idea you could insert identity values that way.Identity function - I think it is new to 2005. The devs here love it because they love select into too.|||that's a negative poots. the identity function is in SQL 2k. I have used it before.|||My mistake. I came across it about the time we migrated so probably got my wires crossed there.|||well, be careful for what you wish for
http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx|||-- Prepare sample data
CREATE TABLE #Sample
(
Col1 INT,
Col2 INT
)
INSERT #Sample
(
Col1
)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 6 UNION ALL
SELECT 7
-- Check precondition
SELECT * FROM #Sample
-- Setup staging mechanism
CREATE CLUSTERED INDEX IX_Sample ON #Sample (Col1)
DECLARE @.Index INT,
@.Col INT
SELECT TOP 1 @.Index = 0,
@.Col = Col1
FROM #Sample
ORDER BY Col1
UPDATE #Sample
SET @.Index = Col2 = CASE WHEN Col1 = @.Col THEN @.Index + 1 ELSE 1 END,
@.Col = Col1
DROP INDEX #Sample.IX_Sample
-- Check postcondition
SELECT * FROM #Sample
-- Clean up
DROP TABLE #Sample
I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.
Col1
--
1
1
1
1
2
2
3
3
3
3
3
4
5
6
6
7
And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.......
Col1 Col2
-- --
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1
Thanks In Advance,
Rahul JhaI thought this would be a fun one to try - so here's a very poor attempt using an icky cursor... I don't think I have to explain to a DBA such as yourself why you shouldn't implement this method ;)
DECLARE @.col1 int
DECLARE @.col2 int
DECLARE @.id int
DECLARE @.i int
DECLARE @.x int
DECLARE @.myTable table (
id int identity(1,1)
, col1 int
, col2 int
)
SET NOCOUNT ON
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(4)
INSERT INTO @.myTable(col1) VALUES(5)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(7)
SET @.i = 1
SET @.x = 1
DECLARE theIckyCursor cursor
FOR
SELECT id
, col1
FROM @.myTable
ORDER
BY col1 ASC
OPEN theIckyCursor
FETCH NEXT FROM theIckyCursor INTO @.id, @.col1
WHILE @.@.Fetch_Status <> -1 BEGIN
IF @.x <> @.col1 BEGIN
SET @.x = @.x + 1
SET @.i = 1
END
UPDATE @.myTable
SET col2 = @.i
WHERE id = @.id
SET @.i = @.i + 1
FETCH NEXT FROM theIckyCursor INTO @.id, @.col1
END
CLOSE theIckyCursor
DEALLOCATE theIckyCursor
SELECT col1
, col2
FROM @.myTable
SET NOCOUNT OFF
You know... If you provide us with *real* data, rather than just T1, T2 etc then a better answer could be provided.
(Basically, what you're trying to do sounds very wrong!)|||thnkx georgy. Even I was trying on the same line :-). But what I am looking forward is some process (without cursor) or just the query that can do the needfull. there is a huge data migration to be done. hence suggest me some thing that doesn't eat much of the system resource.|||a table with only one column where there are duplicate values cannot possibly have a primary key, so you cannot expect a good solution
export your data to excel, use a macro to apply the sequence numbers
otherwise, tell us your real situation, not this fantasy of only one column|||Ok, let's go back to the real world scenario.
Can you provide proper column headings etc and describe WHY you want to add a sequence number?
If you explain your justification for this design; perhaps we can suggest a better solution :)
EDIT: Sniped!|||Goergy I won't able able to give you the real world schema as it's highly confidential. Scenario is exactly teh same as i said. the only difference is that there are more columns than I said in both the tables.
Why I need this, is because of i am migrating the data from and de-normalised database to a normalised one.
Thanks,
Rahul Jha|||can you use an ORDER BY to get the rows into the necessary sequence for numbering them?|||select col1,row_number()over(partition by col1 order by col1)col2 into t2 from t1|||row_number?
Can you make it more clear?
Thanks,
Rahul Jha|||row_number() (http://www.databasejournal.com/features/mssql/article.php/3572301)|||pdreyer's solution actually works. I have learned something new today :)|||IVON, can you paste the code for the same. I am not getting the meaning of row_number(). How to use this function.
I'll be obliged if any one let me know on the query that pdreyer has written.
Thanks,
Rahul Jha|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000. And my DB is in SS 2000.
Thanks,
Rahul Jha|||please answer the question in post #7|||This one ought to leave a mark!SELECT 1 AS ick
INTO #junque
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
UNION ALL SELECT 7
SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junque
UPDATE #foo
SET bat = (SELECT Count(*)
FROM #foo AS baz
WHERE baz.ick = #foo.ick
AND baz.bar <= #foo.bar)
SELECT ick, bat
FROM #foo
DROP TABLE #foo
DROP TABLE #junque-PatP|||ORDER BY on COL1 can be done......... Let me know if i hv cleared ur doubt or not............|||have you seen pats post on page 1|||This one ought to leave a mark!ouch!
[applies mark remover]|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000.
True, true.
And my DB is in SS 2000.
We didn't know that.|||ORDER BY on COL1 can be doneexcellent
here is your solution:
select ( select count(*) + 1
from daTable
where Col1 < T.Col1 ) as rownum
, Col1
from daTable as T
order
by Col1
rownum Col1
1 1
1 1
1 1
1 1
5 2
5 2
7 3
7 3
7 3
7 3
7 3
12 4
13 5
14 6
14 6
16 7
here is pdreyer's ROW_NUMBER() solution for comparison --
select row_number() over
( partition by Col1
order by Col1 ) as rownum
, Col1
from daTable
order
by Col1
rownum Col1
1 1
2 1
3 1
4 1
1 2
2 2
1 3
2 3
3 3
4 3
5 3
1 4
1 5
1 6
2 6
1 7
if you remove the PARTITION BY clause, then the result is
rownum Col1
1 1
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 3
11 3
12 4
13 5
14 6
15 6
16 7|||You guys rock............
Thanks for the solution.......... Thanks patty, r937 and pdreyer......... gr8 help that was.
Thanks,
Rahul Jha|||SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junqueI had no idea you could insert identity values that way.|||Thanks patty
*laughs out loud*|||:) :) Don Laugh Georgy............... :) :)
I really learnt few things thru this thread.........
Thanks guys once again...... You all are awesome......... Nothing to fear Rahul till the time you guys are there......... :) :) :)
Thanks,
Rahul Jha|||I had no idea you could insert identity values that way.Identity function - I think it is new to 2005. The devs here love it because they love select into too.|||that's a negative poots. the identity function is in SQL 2k. I have used it before.|||My mistake. I came across it about the time we migrated so probably got my wires crossed there.|||well, be careful for what you wish for
http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx|||-- Prepare sample data
CREATE TABLE #Sample
(
Col1 INT,
Col2 INT
)
INSERT #Sample
(
Col1
)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 6 UNION ALL
SELECT 7
-- Check precondition
SELECT * FROM #Sample
-- Setup staging mechanism
CREATE CLUSTERED INDEX IX_Sample ON #Sample (Col1)
DECLARE @.Index INT,
@.Col INT
SELECT TOP 1 @.Index = 0,
@.Col = Col1
FROM #Sample
ORDER BY Col1
UPDATE #Sample
SET @.Index = Col2 = CASE WHEN Col1 = @.Col THEN @.Index + 1 ELSE 1 END,
@.Col = Col1
DROP INDEX #Sample.IX_Sample
-- Check postcondition
SELECT * FROM #Sample
-- Clean up
DROP TABLE #Sample
Monday, March 19, 2012
generating Excel file every morning
Hi, I started playing with reporting services, followed the first 3 lessons
int the walkthrough, I find it a very interesting tool.
When viewing a report in IE, we have the possibility to export to excel
file. I woudl like to do a dynamic report, let's say generated on the fly at
6 o'clock morning and send it by mail as attachment. Is it possible with
reportin g services? if yes, any good articles or tutorials somewhere
Thanks for your helpYes, this is possible. Look for subscriptions and scheduling in the BOL.
You can also start here with the How Tos:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_overview_v1_46r7.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"SalamElias" <eliassal@.online.nospam> wrote in message
news:19540F06-2F01-466E-A93E-28C19A763BA3@.microsoft.com...
> Hi, I started playing with reporting services, followed the first 3
lessons
> int the walkthrough, I find it a very interesting tool.
> When viewing a report in IE, we have the possibility to export to excel
> file. I woudl like to do a dynamic report, let's say generated on the fly
at
> 6 o'clock morning and send it by mail as attachment. Is it possible with
> reportin g services? if yes, any good articles or tutorials somewhere
> Thanks for your help
>|||Hi SalamElias,
I come across with the same situation as you mentioned in this post. Any
comment or idea you could provide? Could you share with me the
approaches/methods you taken for the task? Thanks a lot
"SalamElias" wrote:
> Hi, I started playing with reporting services, followed the first 3 lessons
> int the walkthrough, I find it a very interesting tool.
> When viewing a report in IE, we have the possibility to export to excel
> file. I woudl like to do a dynamic report, let's say generated on the fly at
> 6 o'clock morning and send it by mail as attachment. Is it possible with
> reportin g services? if yes, any good articles or tutorials somewhere
> Thanks for your help
>
int the walkthrough, I find it a very interesting tool.
When viewing a report in IE, we have the possibility to export to excel
file. I woudl like to do a dynamic report, let's say generated on the fly at
6 o'clock morning and send it by mail as attachment. Is it possible with
reportin g services? if yes, any good articles or tutorials somewhere
Thanks for your helpYes, this is possible. Look for subscriptions and scheduling in the BOL.
You can also start here with the How Tos:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_overview_v1_46r7.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"SalamElias" <eliassal@.online.nospam> wrote in message
news:19540F06-2F01-466E-A93E-28C19A763BA3@.microsoft.com...
> Hi, I started playing with reporting services, followed the first 3
lessons
> int the walkthrough, I find it a very interesting tool.
> When viewing a report in IE, we have the possibility to export to excel
> file. I woudl like to do a dynamic report, let's say generated on the fly
at
> 6 o'clock morning and send it by mail as attachment. Is it possible with
> reportin g services? if yes, any good articles or tutorials somewhere
> Thanks for your help
>|||Hi SalamElias,
I come across with the same situation as you mentioned in this post. Any
comment or idea you could provide? Could you share with me the
approaches/methods you taken for the task? Thanks a lot
"SalamElias" wrote:
> Hi, I started playing with reporting services, followed the first 3 lessons
> int the walkthrough, I find it a very interesting tool.
> When viewing a report in IE, we have the possibility to export to excel
> file. I woudl like to do a dynamic report, let's say generated on the fly at
> 6 o'clock morning and send it by mail as attachment. Is it possible with
> reportin g services? if yes, any good articles or tutorials somewhere
> Thanks for your help
>
Monday, March 12, 2012
Generating a daily statistic report SQL
Hi,
I Have a table below.
Query
PKEY id int
name varchar(128)
date_add DateTime
What is the SQL statement to get the number of query on each day?
the output should be date and quantity. There should still be an output even if there is no query on that day.
The only way I can think of is by a table-value UDF. (rough design)
function(startdate, enddate)
{
for each day from start to end
insert into result select count(*) from Query where date_add = currentDate
return
}
Is there a more efficient way to do this?
Thanks,
Max
Something like this will do.
Code Snippet
select dateadd(day,datediff(day,0,date_add),0) [day], count(*) [quantity]
from your_table_goes_here
where date_add between @.startdate and @.enddate
group by dateadd(day,datediff(day,0,date_add),0)
If you need to return "0" for unaccount date, you will need a calendar (or number) table to seed the date to be calculated by.
Wednesday, March 7, 2012
Generate Scripts Problem.
Hi all,
I am using the generate Script feature of SQL Server 2005 Express.
The result is:
CREATE TABLE [dbo].[asmt_v2_areas](
[asmt_v2_area_id] [int] IDENTITY(1,1) NOT NULL,
[asmt_v2_mine_id] [int] NULL,
[name] [varchar](60) NOT NULL,
[parent_id] [int] NULL,
[optional_questions] [tinyint] NULL,
[send_updates] [tinyint] NULL,
CONSTRAINT [PK_asmt_v2_areas] PRIMARY KEY CLUSTERED
(
[asmt_v2_area_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] //error here
) ON [PRIMARY]
END
However, when i try an run these scripts without modification, i keep
getting the error 'Syntax error near ')' on the line above.
I am exporting to 2000 servers, could this be the problem?
Any thoughts on how to fix?
Cheers,
AdamThere are some elements in the generated script which are new to 2005. I don
't know exactly what
tool you use to generate script, but perhaps that tool has some option to ge
nerate it with 2000
compatibility.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message news:O1PtNWNPGHA.3924@.TK2MSFTNGP14.p
hx.gbl...
> Hi all,
> I am using the generate Script feature of SQL Server 2005 Express.
> The result is:
> CREATE TABLE [dbo].[asmt_v2_areas](
> [asmt_v2_area_id] [int] IDENTITY(1,1) NOT NULL,
> [asmt_v2_mine_id] [int] NULL,
> [name] [varchar](60) NOT NULL,
> [parent_id] [int] NULL,
> [optional_questions] [tinyint] NULL,
> [send_updates] [tinyint] NULL,
> CONSTRAINT [PK_asmt_v2_areas] PRIMARY KEY CLUSTERED
> (
> [asmt_v2_area_id] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] //error here
> ) ON [PRIMARY]
> END
> However, when i try an run these scripts without modification, i keep gett
ing the error 'Syntax
> error near ')' on the line above.
> I am exporting to 2000 servers, could this be the problem?
> Any thoughts on how to fix?
> Cheers,
> Adam
>
>|||Hi Tibor,
Yeah, I am using SQL Server 2005 and selecting from the relevant dialog
'export as SqlServer 2000' but the error persists.
Bit of a tricky one.
Cheers,
Adam
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Obvu2bPPGHA.3408@.TK2MSFTNGP12.phx.gbl...
> There are some elements in the generated script which are new to 2005. I
> don't know exactly what tool you use to generate script, but perhaps that
> tool has some option to generate it with 2000 compatibility.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
> news:O1PtNWNPGHA.3924@.TK2MSFTNGP14.phx.gbl...
>|||What tool are you using? SQL Server Management Studio? You mentioned Express
, and I'm a bit thrown
off since that doesn't come with any GUI tool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:%23HEEkfPPGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi Tibor,
> Yeah, I am using SQL Server 2005 and selecting from the relevant dialog 'e
xport as SqlServer 2000'
> but the error persists.
> Bit of a tricky one.
> Cheers,
> Adam
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:Obvu2bPPGHA.3408@.TK2MSFTNGP12.phx.gbl...
>
I am using the generate Script feature of SQL Server 2005 Express.
The result is:
CREATE TABLE [dbo].[asmt_v2_areas](
[asmt_v2_area_id] [int] IDENTITY(1,1) NOT NULL,
[asmt_v2_mine_id] [int] NULL,
[name] [varchar](60) NOT NULL,
[parent_id] [int] NULL,
[optional_questions] [tinyint] NULL,
[send_updates] [tinyint] NULL,
CONSTRAINT [PK_asmt_v2_areas] PRIMARY KEY CLUSTERED
(
[asmt_v2_area_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] //error here
) ON [PRIMARY]
END
However, when i try an run these scripts without modification, i keep
getting the error 'Syntax error near ')' on the line above.
I am exporting to 2000 servers, could this be the problem?
Any thoughts on how to fix?
Cheers,
AdamThere are some elements in the generated script which are new to 2005. I don
't know exactly what
tool you use to generate script, but perhaps that tool has some option to ge
nerate it with 2000
compatibility.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message news:O1PtNWNPGHA.3924@.TK2MSFTNGP14.p
hx.gbl...
> Hi all,
> I am using the generate Script feature of SQL Server 2005 Express.
> The result is:
> CREATE TABLE [dbo].[asmt_v2_areas](
> [asmt_v2_area_id] [int] IDENTITY(1,1) NOT NULL,
> [asmt_v2_mine_id] [int] NULL,
> [name] [varchar](60) NOT NULL,
> [parent_id] [int] NULL,
> [optional_questions] [tinyint] NULL,
> [send_updates] [tinyint] NULL,
> CONSTRAINT [PK_asmt_v2_areas] PRIMARY KEY CLUSTERED
> (
> [asmt_v2_area_id] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] //error here
> ) ON [PRIMARY]
> END
> However, when i try an run these scripts without modification, i keep gett
ing the error 'Syntax
> error near ')' on the line above.
> I am exporting to 2000 servers, could this be the problem?
> Any thoughts on how to fix?
> Cheers,
> Adam
>
>|||Hi Tibor,
Yeah, I am using SQL Server 2005 and selecting from the relevant dialog
'export as SqlServer 2000' but the error persists.
Bit of a tricky one.
Cheers,
Adam
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Obvu2bPPGHA.3408@.TK2MSFTNGP12.phx.gbl...
> There are some elements in the generated script which are new to 2005. I
> don't know exactly what tool you use to generate script, but perhaps that
> tool has some option to generate it with 2000 compatibility.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
> news:O1PtNWNPGHA.3924@.TK2MSFTNGP14.phx.gbl...
>|||What tool are you using? SQL Server Management Studio? You mentioned Express
, and I'm a bit thrown
off since that doesn't come with any GUI tool.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:%23HEEkfPPGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Hi Tibor,
> Yeah, I am using SQL Server 2005 and selecting from the relevant dialog 'e
xport as SqlServer 2000'
> but the error persists.
> Bit of a tricky one.
> Cheers,
> Adam
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:Obvu2bPPGHA.3408@.TK2MSFTNGP12.phx.gbl...
>
Sunday, February 19, 2012
generate a random int to add to a record
I need to generate a random whole number between 0 and 999 to add to
add to every record in a table. The number needs to be different for
each record in the table.
add to every record in a table. The number needs to be different for
each record in the table.
How would I do this all within TSQL?the procedure of mystery man produces random numbers but they can
appear more than once which should not happen. So it would be
necessary to create a new number, check if it exists, if not use it,
else create a new random number, ... can become a timeconsuming
problem if you try this with numbers between 1 and 999 and you have
more than 999 records in your database :-))
If you only need it to do a random read, try this:
select * from myTable order by newid()
this will show you the records every time in another order.
hth,
Helmut
"Mystery Man" <PromisedOyster@.hotmail.com> schrieb im Newsbeitrag
news:87c81238.0307030244.627bf1@.posting.google.com ...
> Have a look at the Rand function.
> Here is a possible example
> declare @.counter int
> SET @.counter = 1
> WHILE @.counter < 1000
> begin
> print convert(int,substring(convert(varchar(8), RAND(@.counter)),6,3))
> SET @.counter = @.counter + 1
> end
Subscribe to:
Posts (Atom)