Backend generate and save the report to local drive?

Hi,
Any ideas on how to automatically generate and save the report to local
server drive backend when user click on certain link on the application?
The user do not have to render and display the report in frontend or click
on save button when prompted? The system should not prompt user for any
action instead.
Thanks in advance for any suggestion.Hi,
you can call the WebService of reporting Services (with optional
additional parameters, get the byte[] from the render method and save
this in a file on your network share of choice. That can all happen
behind the scenes and in addition if you want to asynchronously.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

Avoiding transaction logging - at least reducing it

I have an application that uses work tables within an SP to generate a recordset output to the caller as the result of a search operation. The SP does not update the main database, and the results, although sizeable, are not saved.

Clearly I do not need to log the updates to the work tables. They start out empty and the contents could be discarded.

Does SQL Server recognise this and not log the updates to work tables? Is there any way I can indicate that a table is not significant and need not be logged at all?

Would it help to put the work tables in a separate database?

Maybe it would not make much difference anyway?

Thanks in advance for any helpFortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.

A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !|||Originally posted by fadace
Fortunately (in a consistancy point of view), any update is logged, in user tables, system tables, worktables or tables in tempdb.

A specific traceflag allow the server to skip the logging, but it's not supported, not documented and... really dangerous !

Huh? Well I'm glad you didn't become very specific about what you're talking about.

Veritant:

You say Search to return result set, then you say update to work table

SELECTS are not logged

Creating a result set on the fly, like

SELECT * INTO #temp FROM...is not logged

TRUNCATE TABLE or DROP TABLE is not logged..

If you have some examples of what you're doing and need help, post it and we'll look at it...

Autonumbering causing deadlocks.

Gents,

I have come into a system that uses a secondary table to generate (for
want of a better word) Identities.

eg

create table myidents
( name sysname not null, ident int not null)

create procedure getnextident @.table sysname, @.ident int output
as
begin
if not exists (select top 1 1 from myidents where name = @.table)
insert into myidents values (@.table, 0)

update myidents
set @.ident = ident = ident + 1
where name = @.table
end

now, (ignoring for now the use of reserved words) the problem is that
this is called frequently, from other procedures. Trouble is that the
calling procedures call it from within a transaction. We now have a
wickedly hot spot on this table, with frequent deadlocks.

Is there any relatively quick fix for this? Some locking hints or
whatever.

Or do we need to go and recode, moving this kind of thing outside the
transaction (which are all rather too long for my liking), and even
cosidering using identity columns as a replacement?

ThanksIMHO, IDENTITY would be the best approach with the current design. You
might also consider using natural keys instead of surrogate key values.

If your actual myident table has no primary key on name, this will
contribute to blocking/deadlocking. Unless your application always acquires
values in the same sequence, you will be vulnerable to deadlocks unless you
specify a TABLOCKX hint. This is obviously bad for concurrency (especially
for long-running transactions) and should be avoided if possible.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0401220321.38788fa2@.posting.google.c om...
> Gents,
> I have come into a system that uses a secondary table to generate (for
> want of a better word) Identities.
> eg
> create table myidents
> ( name sysname not null, ident int not null)
> create procedure getnextident @.table sysname, @.ident int output
> as
> begin
> if not exists (select top 1 1 from myidents where name = @.table)
> insert into myidents values (@.table, 0)
> update myidents
> set @.ident = ident = ident + 1
> where name = @.table
> end
> now, (ignoring for now the use of reserved words) the problem is that
> this is called frequently, from other procedures. Trouble is that the
> calling procedures call it from within a transaction. We now have a
> wickedly hot spot on this table, with frequent deadlocks.
> Is there any relatively quick fix for this? Some locking hints or
> whatever.
> Or do we need to go and recode, moving this kind of thing outside the
> transaction (which are all rather too long for my liking), and even
> cosidering using identity columns as a replacement?
> Thanks|||You could try this:

if not exists (select top 1 1 from myidents ROWLOCK, UPDLOCK where name =
@.table)

Hope it will help.

Igor

"WangKhar" <Wangkhar@.yahoo.com> wrote in message
news:bb269444.0401220321.38788fa2@.posting.google.c om...
> Gents,
> I have come into a system that uses a secondary table to generate (for
> want of a better word) Identities.
> eg
> create table myidents
> ( name sysname not null, ident int not null)
> create procedure getnextident @.table sysname, @.ident int output
> as
> begin
> if not exists (select top 1 1 from myidents where name = @.table)
> insert into myidents values (@.table, 0)
> update myidents
> set @.ident = ident = ident + 1
> where name = @.table
> end
> now, (ignoring for now the use of reserved words) the problem is that
> this is called frequently, from other procedures. Trouble is that the
> calling procedures call it from within a transaction. We now have a
> wickedly hot spot on this table, with frequent deadlocks.
> Is there any relatively quick fix for this? Some locking hints or
> whatever.
> Or do we need to go and recode, moving this kind of thing outside the
> transaction (which are all rather too long for my liking), and even
> cosidering using identity columns as a replacement?
> Thanks|||WangKhar (Wangkhar@.yahoo.com) writes:
> create procedure getnextident @.table sysname, @.ident int output
> as
> begin
> if not exists (select top 1 1 from myidents where name = @.table)
> insert into myidents values (@.table, 0)
> update myidents
> set @.ident = ident = ident + 1
> where name = @.table
> end
> now, (ignoring for now the use of reserved words) the problem is that
> this is called frequently, from other procedures. Trouble is that the
> calling procedures call it from within a transaction. We now have a
> wickedly hot spot on this table, with frequent deadlocks.
> Is there any relatively quick fix for this? Some locking hints or
> whatever.

Deadlocks can usually be avoided by:

BEGIN TRANSACTION

SELECT @.ident = ident FROM tbl (UPDLOCK) WHERE name = @.table
UPDATE tbl SET ident = @.ident WHERE name = @.table

COMMIT TRANSACTION

However, if you have more work in the transaction, you will have lot of
blocking and not much concurrency in the system. And if you in some
procedures perform work before you come here, they might be blocking
other processes that aleady have an ident, and now wil block the other
prcoess.

The above is only really meaningful if you have busienss rules that
require consecutive series. Else you should move out the ident-generation
out of the transaction or use IDENTITY instead.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks thats pretty much what I thought ...

See if I can convince people that we need to take this action :(

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94794AA4B719Yazorman@.127.0.0.1>...
> WangKhar (Wangkhar@.yahoo.com) writes:
> > create procedure getnextident @.table sysname, @.ident int output
> > as
> > begin
> > if not exists (select top 1 1 from myidents where name = @.table)
> > insert into myidents values (@.table, 0)
> > update myidents
> > set @.ident = ident = ident + 1
> > where name = @.table
> > end
> > now, (ignoring for now the use of reserved words) the problem is that
> > this is called frequently, from other procedures. Trouble is that the
> > calling procedures call it from within a transaction. We now have a
> > wickedly hot spot on this table, with frequent deadlocks.
> > Is there any relatively quick fix for this? Some locking hints or
> > whatever.
> Deadlocks can usually be avoided by:
> BEGIN TRANSACTION
> SELECT @.ident = ident FROM tbl (UPDLOCK) WHERE name = @.table
> UPDATE tbl SET ident = @.ident WHERE name = @.table
> COMMIT TRANSACTION
> However, if you have more work in the transaction, you will have lot of
> blocking and not much concurrency in the system. And if you in some
> procedures perform work before you come here, they might be blocking
> other processes that aleady have an ident, and now wil block the other
> prcoess.
> The above is only really meaningful if you have busienss rules that
> require consecutive series. Else you should move out the ident-generation
> out of the transaction or use IDENTITY instead.

AutoNumber Column and ...

Hi *.*
I plan to crate a database and want to know if it's good to use autonumber
columns as my primary key or I should generate the numbers myself?
Also, If I want to replicate the DB, is it ok to use autonumber columns?
At last, Is there any way I can manage the autonumber start number? Means
autonumbering starts at 100 or 300000 instead of 0?
Thanks for your help,
HOA
Hoa wrote:

> Hi *.*
> I plan to crate a database and want to know if it's good to use autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
I suggest you take a step back and spend a month researching all this
along with getting your database professionally designed. Every one of
those questions are so "depends" in nature that the very fact that you
asked them tells me you don't have a good enough grasp of the situation
to appreciate the answers.
But, quickly:
1) Depends.
2) Sorta. Depends.
3) Yes.
There is an entire section in the Books Online that is called
"Replication Data Considerations" with a topic "Using IDENTITY Values".
Have you not even bothered to read that section prior to posting your
very open ended question?
Zach
|||Identity columns are fine for PK... as long as you plan on keeping your identity values unique. PK values MUST be unique; Identity does not explicitly enforce uniqueness. Identity columns are good for PKs in the fact that they are INTEGER datatype. This d
atatype is small, so it works well w/ indexes (PK defaults to unique, clustered)
Identity columns may need to be set to 'NOT FOR REPLICATION' in certain situations. See Books Online: Replication topology & NOT FOR REPLICATION for more info.
Yes, you can start an Identity value at a number greater than 0 or 1... you declare it with the IDENTITY(10,5) clause (10 being the 'seed' value, 5 being the increment.
"Hoa" wrote:

> Hi *.*
> I plan to crate a database and want to know if it's good to use autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
>
|||Oh boy. Books Online is your friend, don't let it be a stranger. These
issues are all covered in the documentation.
Your choice of a primary key depends on more factors than "it's good"... see
for a bit of my own opinion.
Using an IDENTITY in a replication scenario is okay, depending on what type
of replication. It will not work in merge replication, for example, since
IDENTITY can't guarantee uniqueness from two different database sources.
As for the identity, yes see the CREATE TABLE and DBCC CHECKIDENT tables in
Books Online. If the table hasn't been created:
CREATE TABLE Splunge
(sid INT IDENTITY(30000, 1))
INSERT Splunge DEFAULT VALUES
INSERT Splunge DEFAULT VALUES
SELECT * FROM Splunge
GO
DROP TABLE Splunge
Once again, please become familiar with Books Online. All of the questions
are answered, and then some, in the documentation you already have available
to you.
http://www.aspfaq.com/
(Reverse address to reply.)
"Hoa" <newpoorguy@.yahoo.com> wrote in message
news:eMTZ#DkeEHA.2804@.TK2MSFTNGP11.phx.gbl...
> Hi *.*
> I plan to crate a database and want to know if it's good to use
autonumber
> columns as my primary key or I should generate the numbers myself?
> Also, If I want to replicate the DB, is it ok to use autonumber columns?
> At last, Is there any way I can manage the autonumber start number? Means
> autonumbering starts at 100 or 300000 instead of 0?
> Thanks for your help,
> HOA
>
|||Using identity columns in replication is supported and as other posters have
indicated this is covered in BOL. The exact use of identities in replication
depends on hte type of replication you have selected, but if for example you
were to do a merge publication, another tab appears on the article
properties which allows you to have SQL Server assign ranges to publisher
and subscriber which ensures there is no overlap in assigned numbers - this
is enforced by check constraints. You can alternatively manage the identity
ranges yourself and Michael Hotek shows how to do this on his site
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison

Autonumber

Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
RegardsYou have to use the IDENTITY property of the column to produce auto number.
For example:
CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
GO
It is not really the ideal way to generate a primary key, but people do use
it for that purpose. If you could tell us about your table, may be someone
can suggest a better way.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <John@.nospam.infovis.co.uk> wrote in message
news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
Regards|||You can use this. But you should have a look on that article before.
http://www.sqlservercentral.com/columnists/tKetsdever/anidentitycrisis.asp
So be careful of what you need before.
--
P.RUELLO
DBA
"John" wrote:
> Hi
> How does one set an auto-number field in sql server 2005? Is this a good way
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>|||It's a "companies" table that needs an id column. Also records in
"individual contacts" table are tied to their company record in "companies"
table via this id.
Thanks
Regards
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23hZJmerZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> You have to use the IDENTITY property of the column to produce auto
> number.
> For example:
> CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
> GO
> It is not really the ideal way to generate a primary key, but people do
> use
> it for that purpose. If you could tell us about your table, may be someone
> can suggest a better way.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi
> How does one set an auto-number field in sql server 2005? Is this a good
> way
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>

Autonumber

Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
Regards
You have to use the IDENTITY property of the column to produce auto number.
For example:
CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
GO
It is not really the ideal way to generate a primary key, but people do use
it for that purpose. If you could tell us about your table, may be someone
can suggest a better way.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <John@.nospam.infovis.co.uk> wrote in message
news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
Regards
|||You can use this. But you should have a look on that article before.
http://www.sqlservercentral.com/colu...titycrisis.asp
So be careful of what you need before.
P.RUELLO
DBA
"John" wrote:

> Hi
> How does one set an auto-number field in sql server 2005? Is this a good way
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>
|||It's a "companies" table that needs an id column. Also records in
"individual contacts" table are tied to their company record in "companies"
table via this id.
Thanks
Regards
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23hZJmerZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> You have to use the IDENTITY property of the column to produce auto
> number.
> For example:
> CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
> GO
> It is not really the ideal way to generate a primary key, but people do
> use
> it for that purpose. If you could tell us about your table, may be someone
> can suggest a better way.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi
> How does one set an auto-number field in sql server 2005? Is this a good
> way
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>

Autonumber

Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
RegardsYou have to use the IDENTITY property of the column to produce auto number.
For example:
CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
GO
It is not really the ideal way to generate a primary key, but people do use
it for that purpose. If you could tell us about your table, may be someone
can suggest a better way.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"John" <John@.nospam.infovis.co.uk> wrote in message
news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
Hi
How does one set an auto-number field in sql server 2005? Is this a good way
to generate primary keys? If not, could I have some recommendations?
Thanks
Regards|||You can use this. But you should have a look on that article before.
http://www.sqlservercentral.com/col...ntitycrisis.asp
So be careful of what you need before.
--
P.RUELLO
DBA
"John" wrote:

> Hi
> How does one set an auto-number field in sql server 2005? Is this a good w
ay
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>|||It's a "companies" table that needs an id column. Also records in
"individual contacts" table are tied to their company record in "companies"
table via this id.
Thanks
Regards
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23hZJmerZFHA.3032@.TK2MSFTNGP10.phx.gbl...
> You have to use the IDENTITY property of the column to produce auto
> number.
> For example:
> CREATE TABLE dbo.x (c1 int IDENTITY(1, 1), c2 int)
> GO
> It is not really the ideal way to generate a primary key, but people do
> use
> it for that purpose. If you could tell us about your table, may be someone
> can suggest a better way.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "John" <John@.nospam.infovis.co.uk> wrote in message
> news:OJMWrZrZFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Hi
> How does one set an auto-number field in sql server 2005? Is this a good
> way
> to generate primary keys? If not, could I have some recommendations?
> Thanks
> Regards
>
>