auto expanding Temp DB to fit large requests

I have run into a glitch in SQL 2000's ability to automatically expand the temp db when temp tables become very large.

In our business it is sometimes required that I alter a customer's configuration data without modifying any of their transaction data. This requires a rather complex procedure that creates a script of insert/update/delete statements that, when run on a customer's database, modifies their configuration to a replica of our in-house test environment.

While creating this script, sometimes a few lines are dropped. The real problem is that we have no error or indication that the script had dropped lines until we attempt to run it (which is usually on-site in a live environment.) Our solution is to manually increase the size of the temp db and it's transaction log. After we do this, the script is always created correctly.

This appears to be a bug in the ability for the temp db to auto expand. Is this fixed in SQL 2005?

Various tempdb defects have been fixed in SQL Server 2005. In addition there is a new feature allowing the "automatic space growth" without zeroing pages (makes auto growth much faster).
Without going into more details I expect that your problem will be fixed
SQL Server CTP15.
Please install the CTP15 release and test it. In addition I suggest to contact
MS-CSS (SQL Server 2000 Customer Service) and report your problem for SQL Server 2000

Please let me know the test results for SQL Server 2005 CTP15
Thanks
Mirek

auto date & time

there is an auto increment function at SQL server.
is there a function to automatically to stamp the date and time of the
record?
is it the only way to do it at application level?
thanks a lot.
TonyHi Tony,
You can set a default of getdate() on a DateTime Column an this will record
the current date and time when you insert a row
Is that what you mean?
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"tony wong" <x34@.netvigator.com> wrote in message
news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
> there is an auto increment function at SQL server.
> is there a function to automatically to stamp the date and time of the
> record?
> is it the only way to do it at application level?
> thanks a lot.
> Tony
>|||Sounds like getdate() function. Something like,
insert into table values(getdate())
or when you create a table,
create table table1 (d datetime default getdate())
Pohwan Han. Seoul. Have a nice day.
"tony wong" <x34@.netvigator.com> wrote in message
news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
> there is an auto increment function at SQL server.
> is there a function to automatically to stamp the date and time of the
> record?
> is it the only way to do it at application level?
> thanks a lot.
> Tony
>|||Yes
Thanks Greg & Han
"GregO" <grego@.community.nospam> glsD:e9m7ZKxpFHA.1480@.TK2MSFTNGP10.phx.gbl...[co
lor=darkred]
> Hi Tony,
> You can set a default of getdate() on a DateTime Column an this will
> record the current date and time when you insert a row
> Is that what you mean?
>
> --
> kind regards
> Greg O
> Need to document your databases. Use the firs and still the best AGS SQL
> Scribe
> http://www.ag-software.com
> "tony wong" <x34@.netvigator.com> wrote in message
> news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
>[/color]|||Here is another example:
http://www.mssql.com.au/kb/html/gmg...=psearch_articl
e_text&@.sa_id=63
"tony wong" <x34@.netvigator.com> wrote in message
news:OMlsTGxpFHA.3656@.TK2MSFTNGP09.phx.gbl...
> there is an auto increment function at SQL server.
> is there a function to automatically to stamp the date and time of the
> record?
> is it the only way to do it at application level?
> thanks a lot.
> Tony
>|||I forgot to mention smalldatetime. Generally the data type is more economic.
Pohwan Han. Seoul. Have a nice day.
"tony wong" <x34@.netvigator.com> wrote in message
news:u%23DBjNxpFHA.764@.TK2MSFTNGP14.phx.gbl...
> Yes
> Thanks Greg & Han
>
> "GregO" <grego@.community.nospam>
> glsD:e9m7ZKxpFHA.1480@.TK2MSFTNGP10.phx.gbl...
>

Auto database connetion when SQL server restarted in VC++

How can I connect database automatically when SQL server gets restarted while VC application execution. Any suggestions...
Regards
SabithaCould u please elaborate?|||I have a problem, now I am forcefully closing my application when SQL Server failed and reexecuting the application when the server is restarted. So I have to reconnect the database when Server is restarted without reexecution of application.
How do I know that the server is failed and it is restarted??sql

Auto counting in SQL

Hi all,

I would like to have my SQL statement result to return an additional "column", automatically adding an "auto-increasing" number with it.

So if I for example select all Dates older than today's date, I would want something like this:

110/12/2006210/18/2006310/20/2006410/22/2006510/30/2006

Keep in mind that it's not my intention to fysically insert the "counting" column into the table, but rather do it "virtually".

Is this possible? And if yes, how ? :)

Thanks in advance

Nick

See if this helps:

Declare @.TTable ( col1datetime)Insert into @.TSelect'10/12/2006'unionallSELECT'10/18/2006'unionallSELECT'10/20/2006'unionallSELECT'10/22/2006'unionallSELECT'10/30/2006'SELECTCOUNT(*)AS [Row Number], T1.col1FROM @.T T1, @.T T2WHERE T1.col1 >= T2.col1Group by T1.col1

|||

SELECT *,row_number() OVER (ORDER BY ...) AS [Row Number]

FROM ...

ORDER BY ...

Just fill in the ...'s

|||

ndinakar, thanks for the answer, but not quite what i was looking for

Motley, thanks :) that was exactly what I needed ;)

cheers!

|||

I still don't get it how does this work ... an example:

If I return the values from DB using the following SQL

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTable) AS DerivedTable

it works perfectly. When I add some filtering to WHERE-part

WHERE MyColB <> 'value1' AND MyColB <> 'value2'

I get somewhat strange results. Row numbering in my case starts from row 57, second row jumps to 60, then go normally for couple of rows, then jumps to 77 and so on. So it's definately not sequential. Any ides for this ?

|||

You should make sure that your where is in the correct SELECT statement.
There is for example a big difference in results when used either

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTable) AS DerivedTableWHERE MyColB <> 'value1' AND MyColB <> 'value2'

in stead of

SELECT RowNum, MyID, MyColA, MyColB

FROM (SELECT MyID, MyColA, MyColB, ROW_NUMBER() OVER (ORDER BY MyID) AS RowNum FROM MyTableWHERE MyColB <> 'value1' AND MyColB <> 'value2') AS DerivedTable

in the first statement, you're likely to get non-sequential numbering, and in the second one you should get them sequentially

|||

Yes, of course, I didn't realize that, but now it works. Thanks

Auto Commit

Hi All,
I want that by default SQL Server automatically starts a transaction for me
and I must commit every action manualy to really change something in the
database.
I am using SQL Server 2000.
Regards
Chakravarti MukeshHi!
Do please check the SET IMPLICIT_TRANSACTIONS statement in Books OnLine - I
think this is what you need.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"Chakravarti Mukesh" <cmukesh@.sparshindia.com> wrote in message
news:O6QlPOl%23FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I want that by default SQL Server automatically starts a transaction for
> me and I must commit every action manualy to really change something in
> the database.
> I am using SQL Server 2000.
> Regards
> Chakravarti Mukesh
>|||Hi,
Thanks. It worked fine. But It needed to be SET every time I open Query
Analyzer.
Chakravarti Mukesh
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:et$9XTl%23FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi!
> Do please check the SET IMPLICIT_TRANSACTIONS statement in Books OnLine -
> I think this is what you need.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
> "Chakravarti Mukesh" <cmukesh@.sparshindia.com> wrote in message
> news:O6QlPOl%23FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||... and if using Query Analyzer, there's a config setting for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in m
essage
news:et$9XTl%23FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi!
> Do please check the SET IMPLICIT_TRANSACTIONS statement in Books OnLine -
I think this is what you
> need.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
> "Chakravarti Mukesh" <cmukesh@.sparshindia.com> wrote in message
> news:O6QlPOl%23FHA.1248@.TK2MSFTNGP14.phx.gbl...
>|||Chakravarti Mukesh (cmukesh@.sparshindia.com) writes:
> Thanks. It worked fine. But It needed to be SET every time I open Query
> Analyzer.
Tools->Options->Connection Properties.
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|||Go to Tools-->Options-->Connection Properties in Query Analyzer.
I advise caution in using this option. It's unlikely that your applications
will also set this option, so it will be difficult to test procedures and
other batches that are sent by applications that expect
IMPLICIT_TRANSACTIONS to be OFF. You should also be aware that every
statement will start a transaction--even SELECT, so depending on the
TRANSACTION ISOLATION LEVEL, it's possible to block other users' access even
with the most innocent-looking query.
I understand the desire not to screw up the production database with an
ill-formed UPDATE or DELETE statement, but a better solution would be to
first execute the changes against a test database, and once you're satisfied
that the results are correct, to then issue the batch against the production
database.
"Chakravarti Mukesh" <cmukesh@.sparshindia.com> wrote in message
news:%23UVBRhl%23FHA.4012@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thanks. It worked fine. But It needed to be SET every time I open Query
> Analyzer.
> Chakravarti Mukesh
> "Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
> message news:et$9XTl%23FHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||I think Dejan was trying to suggest you use this setting in your procedures.
:)
I personally prefer explicit transactions - they can be started when needed
and rolled back or committed as appropriate based on the requirements of eac
h
individual case. But maybe I'm just a control freak.
ML
http://milambda.blogspot.com/

auotomatically closing crystal report

hay everybody,
i am using crystal report version 7.the no. of records in it is 4000.when i take the report it will close automatically.do anyone of u know the maximum capacity of crystal reprt.please help me.It is much higher than 4000. I run reports with 100000+ records every day. Make sure you have enough system RAM for yoyr reports. What are your system requirements?|||and how are you calling that report thru Frond End?

Auotmated Stored Procedure

Hi
Is there is any way to run the stored procedure automatically from master
database on daily at particular time.
Thanks is advance
NizhamYes, create a SQL Server Agent job with a TSQL jobstep which executes the pr
ocedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Junkmn" <junkmn@.gmail.com> wrote in message news:%23gwULcLMGHA.3460@.TK2MSFTNGP15.phx.gbl..
.
> Hi
> Is there is any way to run the stored procedure automatically from master
> database on daily at particular time.
> Thanks is advance
> Nizham
>|||Yes. Create a job and schedule it to run at a specific time. From EM, you
can do this by using the Jobs node under Management. ( Make sure the SQL
Agent service is enabled & started )
Anith|||Thanks Tibor Karaszi
I hope you are expert on that If possible will you please send me the sample
code to run the stored procedure sp_XYZ to be run at 1.00 am
Regards
Nizham
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ugXXSfLMGHA.1832@.TK2MSFTNGP11.phx.gbl...
> Yes, create a SQL Server Agent job with a TSQL jobstep which executes the
> procedure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Junkmn" <junkmn@.gmail.com> wrote in message
> news:%23gwULcLMGHA.3460@.TK2MSFTNGP15.phx.gbl...|||It's much easier to use the GUI than to write the code for you. Have a look
at SQL Server Agent topics in Books Online. You can also see a brief
example here:
http://www.aspfaq.com/2403
"Junkmn" <junkmn@.gmail.com> wrote in message
news:O03vlkLMGHA.668@.TK2MSFTNGP11.phx.gbl...
> Thanks Tibor Karaszi
> I hope you are expert on that If possible will you please send me the
> sample code to run the stored procedure sp_XYZ to be run at 1.00 am
> Regards
> Nizham
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:ugXXSfLMGHA.1832@.TK2MSFTNGP11.phx.gbl...
>