Estimating Disk Space

Thanks for your replies Tibor / John.
Is there any script/tool available to estimate the size of
the target database. I have the table structures and
estimated rows.
Thanks,
HariI believe that the book "Inside SQL Server 2000" from MS Press comes with such a tool. The book is a
"must" IMO for any intermediate/advanced SQL Server person anyhow, IMO... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hari Shankar" <anonymous@.discussions.microsoft.com> wrote in message
news:2429f01c45f3c$1f2b89c0$a401280a@.phx.gbl...
> Thanks for your replies Tibor / John.
> Is there any script/tool available to estimate the size of
> the target database. I have the table structures and
> estimated rows.
> Thanks,
> Harisql

Escape Sequences

Is there an escape sequence available for the SQL Analyzer that will not
mung the `'` (apostrophe)
If I add it as a parameter in my code, it works, but I am curious to know
all the same.I'm not sure what you mean by "that will not mung the `'` (apostrophe)", but
if you pass a string which includes a single quote, you need to escape that
single quote with a single quote. I.e., double each single quote before
passing the string to SQL Server.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Wayne M J" <not@.home.nor.bigpuddle.com> wrote in message
news:%23ex1p5wvDHA.2444@.TK2MSFTNGP12.phx.gbl...
> Is there an escape sequence available for the SQL Analyzer that will not
> mung the `'` (apostrophe)
> If I add it as a parameter in my code, it works, but I am curious to know
> all the same.
>|||"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:OMO5w8wvDHA.2072@.TK2MSFTNGP10.phx.gbl...
> I'm not sure what you mean by "that will not mung the `'` (apostrophe)",
but
> if you pass a string which includes a single quote, you need to escape
that
> single quote with a single quote. I.e., double each single quote before
> passing the string to SQL Server.
That is exactly what I was looking for.
Thanks.

Errors in SQL Server. How prevent them?

Im designing a dynamic website with Dreamweaver MX, ASP VBScript language, SQL 2000 Server and PWS as a server. The site is not available in Internet yet.

For the moment I have created all the main functions of the website. Almost all the actions that the site leads to term it does it with stored procedures (sp) on SQL Server. Those SP works well from the web application, and all the actions (to insert data, to modify data, to check data, etc.) works perfect. But I have been advised that I learn to capture the possible errors that SQL Server can return to user, and to avoid that the site fell. Someone could explain me what kind of errors can return me? And how can I solve it?

I can not understand why I need to know how to capture those errors because, if were managing to happen, the browser show us a message of error and the user try again the operation, as I have seen at many sites. After that, I should quarrel to discover why these errors happen, and then I try to solve it, or take the aid of an expert administrator. Or, can I really to prevent these potential errors and to minimize their effects?

In the other hand, I cant understand why my stored procedures are able to return errors or to bring problems to my web application, now it works well.

Thank you.

Here is an example of one of my stored procedures:

CREATE PROCEDURE check_mail_password_inscription
@.E-mail varchar(50), @.Password char(10),
@.Offer_num int, @.Date datetime, @.Motives_interest varchar(250)
As
Declare @.Buyer_num int

If Exists(select E-mail from Buyers where E-mail = @.E-mail
and Password = @.Password)
begin

select @.Buyer_num = Buyer_num
from Buyers
where E-mail = @.E-mail
and Password = @.Password

select @.Buyer_num AS Buyer_num, 1 AS Value
INSERT INTO Inscriptions_in_offers (Offer_num, Buyer_num, Date, Motives_interest)
VALUES (@.Offer_num, @.Buyer_num, @.Date, @.Motives_interest)
end
else
begin
select 0 AS Value
endAny data update can cause a deadlock and there is very little you can do to prevent it - you can minimise it but not prevent. Systems that are not designed with this in mind usually have a lot of problems when they get a few concurrent users.

Your system should always take account of the possibility of any operation failing.

In your SP what happens if the insert fails? to you still want to return a resultset?
And why return a single row resultset rather than output parameters which are much faster.
You are also returning a different shape resultset for the two paths which is a bit odd.

I would guess that you also need to read up on transaction control.|||What you recommend me to minimize the errors in my stored procedures? Now they are totally helpless. I need to use BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, @.@.ERROR variable, RAISERROR, RETURN statement to force an unconditional exit or sp_addmessage stored procedure? Or all together where this is possible?

If my sp fails and the action cannot be done, I need communicate it to my web application so that the user tries again the operation.

I only know this tools in general, never I used to. Would you mind give me a complete example with my stored procedure please?

CREATE PROCEDURE check_mail_password_inscription
@.E-mail varchar(50), @.Password char(10),
@.Offer_num int, @.Date datetime, @.Motives_interest varchar(250)
As
Declare @.Buyer_num int

If Exists(select E-mail from Buyers where E-mail = @.E-mail
and Password = @.Password)
begin

select @.Buyer_num = Buyer_num
from Buyers
where E-mail = @.E-mail
and Password = @.Password

select @.Buyer_num AS Buyer_num, 1 AS Value
INSERT INTO Inscriptions_in_offers (Offer_num, Buyer_num, Date, Motives_interest)
VALUES (@.Offer_num, @.Buyer_num, @.Date, @.Motives_interest)
end
else
begin
select 0 AS Value
end

I have a manual of SQL Server that says that exist two types of transactions: explicit and implicit. Explicit transaction means that begin with the BEGIN TRANSACTION statement and end with the COMMIT TRANSACTION statement. Implicit transaction means that, more or less, if you don t use the BEGIN TRANSACTION and COMMIT TRANSACTION statements, nor do you turn on implicit transactions, SQL Server autocommits the transaction. With this, I understand that is not necessary to write the statements begin tr and commit tr in my sp. It is like that?


Thank you,
Cesar

error:26 when connecting to sql server express...

I've got an ASP.net 2.0 application that I published and moved to a publicly available server. From my machine it works fine, but not from the production machine.

I'm attempting to connect to a machine that has sql server express installed.

The message in the browser:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

So, I went by the kb article and made sure tcpip is enabled. This uses port 3288. server browser is enabled and the machine accepts remote connections.My connection string is (minus the ip & user/pwd):

<add name="IRRMAConnectionString" connectionString="Data Source=<ip_address>\SQLEXPRESS,3288;Initial Catalog=IRRMA;User ID=<user>;password=<pwd>" providerName="System.Data.SqlClient"/>

I can ping the db server from my app server. On my dev machine it connects. Windows firewall is turned off on the db server.

This seems to be an issue with a firewall, I think?

Any direction would be appreciated.

Thanks,

Scott Jones

Scott@.fourindianajones.com

You should check this out and go from there.

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

I would say it has to do with the UDP 1434 or the sqlbrowser.exe in the exceptions. But once you follow all the steps you should get closer to the resolution.

error:26 when connecting to sql server express...

I've got an ASP.net 2.0 application that I published and moved to a publicly available server. From my machine it works fine, but not from the production machine.

I'm attempting to connect to a machine that has sql server express installed.

The message in the browser:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

So, I went by the kb article and made sure tcpip is enabled. This uses port 3288. server browser is enabled and the machine accepts remote connections.My connection string is (minus the ip & user/pwd):

<add name="IRRMAConnectionString" connectionString="Data Source=<ip_address>\SQLEXPRESS,3288;Initial Catalog=IRRMA;User ID=<user>;password=<pwd>" providerName="System.Data.SqlClient"/>

I can ping the db server from my app server. On my dev machine it connects. Windows firewall is turned off on the db server.

This seems to be an issue with a firewall, I think?

Any direction would be appreciated.

Thanks,

Scott Jones

Scott@.fourindianajones.com

You should check this out and go from there.

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

I would say it has to do with the UDP 1434 or the sqlbrowser.exe in the exceptions. But once you follow all the steps you should get closer to the resolution.