Hello,
This may be a strange request, but I am going to ask about it anyways.
Say for example if I have a table named TEST and in the table there is a column named NUMBERS, such that it is like this:
NUMBERS
1
2
3
4
How could I use a select statement in a way that a comma would seperate every return value, such that if I go 'Select NUMBERS from TEST' I would get:
1,2,3,4
Instead of:
1
2
3
4
Any ideas?
ThanksThe numbers will still be as o column... but try:
SELECT CAST(numbers as varchar)+',' from TEST
Showing posts with label request. Show all posts
Showing posts with label request. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
purge process from large table
Hi,
We have specific request here: there is a large table, around 5,000,000 rows
(3GB). It has unique clustered index, created on 6 of it's 10 columns. We
have to delete around 15% of rows every day, but in a way that table keeps
being available all the time. Deletion criteria is date (where
column_date<=getdate()). There is nonclustered index created on column_date
table. Since there is availability criteria, simple: delete <table name>
where column_date<=getdate() is out of the question because of exclusive
table lock on this table.
Any ideas?
Thanks,
PedjaBy "available" do you mean readable via a SELECT?
If you are using SQL 2005, you can take advantage of the new Read Committed
Snapshot Isolation level, which allows a SELECT to read the most
recently-committed version of a set of data, even while that data is being
modified.
If you are using SQL 2000, your readers can use WITH (NOLOCK) as an option
to the SELECT statements, putting the transaction into read uncommited. Your
readers won't have to wait for writers, but you may get inaccurate data.
Is it possible that you can split this delete operation so that it occurs
several times a day? That way it can delete fewer rows, resulting in less
blocking time.
"Pedja" wrote:
> Hi,
> We have specific request here: there is a large table, around 5,000,000 ro
ws
> (3GB). It has unique clustered index, created on 6 of it's 10 columns. We
> have to delete around 15% of rows every day, but in a way that table keeps
> being available all the time. Deletion criteria is date (where
> column_date<=getdate()). There is nonclustered index created on column_dat
e
> table. Since there is availability criteria, simple: delete <table name>
> where column_date<=getdate() is out of the question because of exclusive
> table lock on this table.
> Any ideas?
> Thanks,
> Pedja|||Mark,
We use sql server 2000. By available, I mean both, read/write operations.
NOLOCK hint won't help, because once it is grabbed by purge process, table i
s
being locked until it is completed (that is why I posted this question
initially), so neither reads nor writes are allowed during this time. My ide
a
was to split deletion process to batches of 1000 rows (set rowcount 1000),
but I wanted to hear some other ideas too.
Thanks
"Mark Williams" wrote:
> By "available" do you mean readable via a SELECT?
> If you are using SQL 2005, you can take advantage of the new Read Committe
d
> Snapshot Isolation level, which allows a SELECT to read the most
> recently-committed version of a set of data, even while that data is being
> modified.
> If you are using SQL 2000, your readers can use WITH (NOLOCK) as an option
> to the SELECT statements, putting the transaction into read uncommited. Yo
ur
> readers won't have to wait for writers, but you may get inaccurate data.
> Is it possible that you can split this delete operation so that it occurs
> several times a day? That way it can delete fewer rows, resulting in less
> blocking time.
> --
> "Pedja" wrote:
>|||Pedja,
you could split up your data into several tables, one table per day.
You can access them via a UNION ALL view. Then the purge is very fast,
you just re-create the view, which is a snap, and drop the oldest
table. There are some di
vantages: some of queries against the view
will work slower, and you will not be able to enforse unique (and
sometimes other) constraints just as easily|||Hi
Divide your deletion into small batches
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Here your DML Statement
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
END
SET ROWCOUNT 0
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:38445382-60B6-4327-A3FE-C5AD8BF88E41@.microsoft.com...
> Hi,
> We have specific request here: there is a large table, around 5,000,000
> rows
> (3GB). It has unique clustered index, created on 6 of it's 10 columns. We
> have to delete around 15% of rows every day, but in a way that table keeps
> being available all the time. Deletion criteria is date (where
> column_date<=getdate()). There is nonclustered index created on
> column_date
> table. Since there is availability criteria, simple: delete <table name>
> where column_date<=getdate() is out of the question because of exclusive
> table lock on this table.
> Any ideas?
> Thanks,
> Pedjasql
We have specific request here: there is a large table, around 5,000,000 rows
(3GB). It has unique clustered index, created on 6 of it's 10 columns. We
have to delete around 15% of rows every day, but in a way that table keeps
being available all the time. Deletion criteria is date (where
column_date<=getdate()). There is nonclustered index created on column_date
table. Since there is availability criteria, simple: delete <table name>
where column_date<=getdate() is out of the question because of exclusive
table lock on this table.
Any ideas?
Thanks,
PedjaBy "available" do you mean readable via a SELECT?
If you are using SQL 2005, you can take advantage of the new Read Committed
Snapshot Isolation level, which allows a SELECT to read the most
recently-committed version of a set of data, even while that data is being
modified.
If you are using SQL 2000, your readers can use WITH (NOLOCK) as an option
to the SELECT statements, putting the transaction into read uncommited. Your
readers won't have to wait for writers, but you may get inaccurate data.
Is it possible that you can split this delete operation so that it occurs
several times a day? That way it can delete fewer rows, resulting in less
blocking time.
"Pedja" wrote:
> Hi,
> We have specific request here: there is a large table, around 5,000,000 ro
ws
> (3GB). It has unique clustered index, created on 6 of it's 10 columns. We
> have to delete around 15% of rows every day, but in a way that table keeps
> being available all the time. Deletion criteria is date (where
> column_date<=getdate()). There is nonclustered index created on column_dat
e
> table. Since there is availability criteria, simple: delete <table name>
> where column_date<=getdate() is out of the question because of exclusive
> table lock on this table.
> Any ideas?
> Thanks,
> Pedja|||Mark,
We use sql server 2000. By available, I mean both, read/write operations.
NOLOCK hint won't help, because once it is grabbed by purge process, table i
s
being locked until it is completed (that is why I posted this question
initially), so neither reads nor writes are allowed during this time. My ide
a
was to split deletion process to batches of 1000 rows (set rowcount 1000),
but I wanted to hear some other ideas too.
Thanks
"Mark Williams" wrote:
> By "available" do you mean readable via a SELECT?
> If you are using SQL 2005, you can take advantage of the new Read Committe
d
> Snapshot Isolation level, which allows a SELECT to read the most
> recently-committed version of a set of data, even while that data is being
> modified.
> If you are using SQL 2000, your readers can use WITH (NOLOCK) as an option
> to the SELECT statements, putting the transaction into read uncommited. Yo
ur
> readers won't have to wait for writers, but you may get inaccurate data.
> Is it possible that you can split this delete operation so that it occurs
> several times a day? That way it can delete fewer rows, resulting in less
> blocking time.
> --
> "Pedja" wrote:
>|||Pedja,
you could split up your data into several tables, one table per day.
You can access them via a UNION ALL view. Then the purge is very fast,
you just re-create the view, which is a snap, and drop the oldest
table. There are some di
vantages: some of queries against the viewwill work slower, and you will not be able to enforse unique (and
sometimes other) constraints just as easily|||Hi
Divide your deletion into small batches
SET ROWCOUNT 1000
WHILE 1 = 1
BEGIN
--Here your DML Statement
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
END
SET ROWCOUNT 0
"Pedja" <Pedja@.discussions.microsoft.com> wrote in message
news:38445382-60B6-4327-A3FE-C5AD8BF88E41@.microsoft.com...
> Hi,
> We have specific request here: there is a large table, around 5,000,000
> rows
> (3GB). It has unique clustered index, created on 6 of it's 10 columns. We
> have to delete around 15% of rows every day, but in a way that table keeps
> being available all the time. Deletion criteria is date (where
> column_date<=getdate()). There is nonclustered index created on
> column_date
> table. Since there is availability criteria, simple: delete <table name>
> where column_date<=getdate() is out of the question because of exclusive
> table lock on this table.
> Any ideas?
> Thanks,
> Pedjasql
Wednesday, March 7, 2012
Publishing a report to the web error
Does anyone know how to fix this error?
-I've tried allowing anonymous access to the site.
-I've added it to my trusted sites.
Error: The request failed with HTTP status 401: Unauthorized.
Thanks.
I've got pass that error and now I am getting this error. I've added my windows account to the server but still keep getting this error. Any ideas?
The report you are using does not have the correct credentials to execute the query against your underlying data source. Check what credentials you have specified through the report properties tab in report manager or in management studio.
My guess is that the report is set to run with windows integrated credentials. This would be the credentials passed to the report server from IIS. For example, if you set anoynomous credentials on the virtual directory this would be the user the report server is using to connect to the data source. A quick fix would be to store the credentials for the report in the reportserver.
-JonHP
|||I can view this just fine on my local report server. Why can't I view it through the website that I embedded it into? Doesn't make sense. I have my defalut.aspx page authenticated to windows, I have my windows account with permission on the reporting server and my local virtual directory to authenticate with windows integrated security. I also have my web browser to use current log in and password when visiting the site. Why is this so confusing to do?|||
Hi mr4100
Maybe try this. I believe that integrated security only allows 1 hop to pass credentials. You might want to try one of the following.
1) set the data sources property or the report to "Credentials stored securely in the report server" or "Credentials supplied by the user running the report"
or
2) set up a shared use a shared data source with "Credentials stored securely in the report server" or "Credentials supplied by the user running the report"
|||
Hi Mike-
This sort of error can be due to several things. I would start by checking your IIS settings on your Reports and ReportServer virtual directories. More information on setting virtual directory security settings can be found here:
http://msdn2.microsoft.com/en-us/library/zwk103ab.aspx
-JonHP
Publishing a report to the web error
Does anyone know how to fix this error?
-I've tried allowing anonymous access to the site.
-I've added it to my trusted sites.
Error: The request failed with HTTP status 401: Unauthorized.
Thanks.
Hi Mike-
This sort of error can be due to several things. I would start by checking your IIS settings on your Reports and ReportServer virtual directories. More information on setting virtual directory security settings can be found here:
http://msdn2.microsoft.com/en-us/library/zwk103ab.aspx
-JonHP
|||I've got pass that error and now I am getting this error. I've added my windows account to the server but still keep getting this error. Any ideas?
The report you are using does not have the correct credentials to execute the query against your underlying data source. Check what credentials you have specified through the report properties tab in report manager or in management studio.
My guess is that the report is set to run with windows integrated credentials. This would be the credentials passed to the report server from IIS. For example, if you set anoynomous credentials on the virtual directory this would be the user the report server is using to connect to the data source. A quick fix would be to store the credentials for the report in the reportserver.
-JonHP
|||I can view this just fine on my local report server. Why can't I view it through the website that I embedded it into? Doesn't make sense. I have my defalut.aspx page authenticated to windows, I have my windows account with permission on the reporting server and my local virtual directory to authenticate with windows integrated security. I also have my web browser to use current log in and password when visiting the site. Why is this so confusing to do?|||
Hi mr4100
Maybe try this. I believe that integrated security only allows 1 hop to pass credentials. You might want to try one of the following.
1) set the data sources property or the report to "Credentials stored securely in the report server" or "Credentials supplied by the user running the report"
or
2) set up a shared use a shared data source with "Credentials stored securely in the report server" or "Credentials supplied by the user running the report"
Subscribe to:
Posts (Atom)