ASP script to display database structure?

Posted this to SQLServer.Tools, but maybe it belongs here...
Does anyone have an ASP script that I can use to dump the structure of an MS
SQL 2000 database?
The firewall here won't allow any remote connections through, and our DB
host does not have any web administration.
I just need the table names, field names, and field data types.
Thx!would information_schema.tables and information_schema.columns do?
Just join the views and select the columns you need.
SELECT *
FROM information_schema.tables t
JOIN information_schema.columns c
ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
"Noozer" wrote:

> Posted this to SQLServer.Tools, but maybe it belongs here...
>
> Does anyone have an ASP script that I can use to dump the structure of an
MS
> SQL 2000 database?
> The firewall here won't allow any remote connections through, and our DB
> host does not have any web administration.
> I just need the table names, field names, and field data types.
> Thx!
>
>|||Thanks!
That did the trick!
"Nigel Rivett" <NigelRivett@.discussions.microsoft.com> wrote in message
news:46273214-5138-4D9F-A0EB-7C1AC0B70344@.microsoft.com...
> would information_schema.tables and information_schema.columns do?
> Just join the views and select the columns you need.
> SELECT *
> FROM information_schema.tables t
> JOIN information_schema.columns c
> ON t.table_schema = c.table_schema
> AND t.table_name = c.table_name

ASP and SQLServer 2k Issue

All,

I have scoured all over and I think I have a problem that is a bit more complex. I have a basic 3tier app running on Win2k, SQLServer 2k and asp.net. I set the aspnet account up with access to the db. I have the following code in my form:

<code>

Sub SubmitBtn_Click(ByVal sender As Object, ByVal e As EventArgs) Dim MyConn as SQLConnection= NEw SQLConnection("server=xxx,1433;database=bsld_demo;uid=bsld;password=bsld") Dim MySQL As SQLCommand, TID as string, TParam as SQLParameter MySQL= New SqlCommand("InsertRecord", MyConn) MySQL.CommandType = CommandType.StoredProcedure MySQL.Parameters.Add(New SQLParameter("@.compmodule", compmodule.text)) MySQL.Parameters.Add(New SQLParameter("@.customername", customername.text)) TParam=MySQL.Parameters.Add(New SQLParameter("@.ID", 0)) TParam.Direction=ParameterDirection.Output MyConn.Open() MySQL.ExecuteNonQuery TID=TParam.Value MyConn.Close() End Sub

</code>

When I click on the button I get:

Login failed for user 'bsld'.

I am not sure what to do. The odd thing is on my server I have two instances of sqlserver running. Instance 1 is where I am having the problem. Instance works fine. Anybody run into this before? Thanks

Cordell

It is actually very simple the first thing you do is to register one with the other, by right clicking at the top of Enterprise Manager and registering the instance 1 with the instance. Now to your error it means Asp.net does not have an acount in the instance you are trying to connect to. There are two permissions in SQL Server, the server permission under security in the Management section of Enterprise Manager and the database permission by right click on your database go to all tasks. Then you adjust your Web.Config as needed. In the future it is better to backup and restore so everything including permission is moved so you just adjust the Web.Config. Hope this helps.|||

Sorry for the delay. Here is what I have:

<config file >

<configuration>

<appSettings/><connectionStrings>

<addname="bsld_demoConnectionString"

connectionString="Data Source=server\instance1;Initial Catalog=bsld_demo;Integrated Security=True"

providerName="System.Data.SqlClient"/>

</connectionStrings>

<system.web>

<customErrorsmode="Off"/>

<

authenticationmode="Windows"/>

<!--

</system.web>

</

configuration>

<config/>

<script>

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Dim MyConnAs SqlConnection =New SqlConnection("server=server,1433;Integrated Security=SSPI;database=bsld_demo")Dim MySQLAs SQLCommand, RIDAsString, TParamAs SQLParameter

MySQL =

New SqlCommand("InsertRecord", MyConn)

MySQL.CommandType = CommandType.StoredProcedure

MySQL.Parameters.Add(

New SqlParameter("@.CompModule", CompModule.text))

MySQL.Parameters.Add(

New SqlParameter("@.CustomerName", CustomerName.text))

TParam = MySQL.Parameters.Add(

New SqlParameter("@.ID", 0))

TParam.Direction = ParameterDirection.Output

MyConn.Open()

MySQL.ExecuteNonQuery()

RID = TParam.Value

EndSub

<script/>

The server\ASPNET account has access on the server and to the bsld_demo db. I still get this error:

Exception Details:System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails.
Login failed for user 'server\ASPNET'.

Source Error:

Line 26: Line 27:Line 28: MyConn.Open()Line 29: Line 30: MySQL.ExecuteNonQuery()

Source File:D:\SOS\ConnectionTest\Default.aspx Line:28

Stack Trace:

[SqlException (0x80131904): Cannot open database requested in login 'bsld_demo'. Login fails.Login failed for user 'server\ASPNET'.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735251 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 ASP.default_aspx.Button1_Click(Object sender, EventArgs e) in D:\SOS\ConnectionTest\Default.aspx:28 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

What do I do now? I have tried everything else. Thanks,.|||

You need to go back to my original post about how you create a SQL Server account for Asp.net because of the text below.

(Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails.
Login failed for user 'server\ASPNET')

And clean up your connection string with the link below. Hope this helps.

http://www.carlprothman.net/Default.aspx?tabid=86

|||Thanks for the suggestions. I cleaned my string. However, I do have a SqlServer account for ASP.Net on the SQLServer. I guess that's why I am confused. In SQLServer I have an account set up as 'server\ASPNET' with server being my server name. I granted that user access to the bsld_demo db.|||

No each instance of SQL Server is a separate server permissions so check the Master database of the instance you are trying to run the application to see if Asp.net account was created in Syslogin table, if it is there try to connect to it if you cannot do that then the permissions is orphaned, that is covered in the second link, it usually involve editing the Master, most places I have worked this require a lot of Management signatures.

http://msdn2.microsoft.com/en-us/library/ms178593.aspx

http://msdn2.microsoft.com/en-us/library/ms175475.aspx

Here is Microsoft covering remote connection see if that makes a difference. Hope this helps.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

|||

Thanks Caddre. I tried the links and suggestions and nothing worked. I even did the following: I turned Anon access off and tried to log in and got the same "Exception Details:System.Data.SqlClient.SqlException: Cannot open database requested in login 'bsld_demo'. Login fails."
error. This time I got "Login failed for user 'AD\abcxyz'." which AD\abcxyz is my Network ID.

On thin I found interesting is that if I create the connection string in VWD, I can do datagrid functions like edit, delete and insert. But if I try to create a sub procedure and try to link the database that way, this is when I encounter the error. Do you or anyone else know of a way or some examples where I can create a button and code it to do the same thing as the default "Insert" feature in VWD? If I can find this, I'll avoid this issue.

|||

EDIT

I am glad you posted this database was VWD created and that makes it a user instance database, you can fix the problem by backing up the database and restore and it will become regular database. The link below covers it. Post again and let me know how it goes.

http://msdn2.microsoft.com/en-gb/library/ms143684.aspx

|||Hey Caddre. I tried the suggestion in the link and nothing. I thought I would try and create a tbl in another db and point the page to it. I get the same error. I'm almost tempted to reload the .net framework to see if that fixes it but I don't want to screw up the other instance. This is just the craziest thing I have ever seen. You have any other suggestions? You have been awesome in helping just so you know.. :-)|||

EDIT

Try backup and restore first, then the framework and this is from Microsoft support you should change from Windows Authentication to Mixed authentication

http://support.microsoft.com/kb/555332

ASP / SQLServer connection

Hi, I'm having trouble connecting to SQLServer in my ASP program.

here's the code

<%
dim objConnection, ObjRecorSet, strConnection, strOut, objCmd, objParameters
Set objConnection = Server.CreateObject ("ADODB.Connection")
strConnection = "DSN=SQL_Tracker;Database=Tracker;UID=sa;PWD=;"
objConnection.Open strConnection
...
%
I'm getting this error on the line that opens the connection.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
'HOME\IUSR_HOME'.
/Tracker/list_team.asp, line 11

Notes:

I've set up the DSN called 'SQL_Tracker' in my ODBC settings.
When I use the Test Connection button it's successful.

The SQL Server is running on my own computer, and so I have
full DBA rights on it

any ideas what's wrong?Your connection string uses SQL authentication but the error message looks
like Windows authentication was attempted. Did you specify SQL
authentication or Windows authentication in your ODBC setup? Is your SQL
Server configured to allow both SQL and Windows authentication?

Also, note that you should never use 'sa' for application database
connections and you may get uninvited guests with a blank 'sa' password.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Peter Morris" <nospam.ple@.se> wrote in message
news:RmS9d.5432$xb.1658@.text.news.blueyonder.co.uk ...
> Hi, I'm having trouble connecting to SQLServer in my ASP program.
> here's the code
> <%
> dim objConnection, ObjRecorSet, strConnection, strOut, objCmd,
> objParameters
> Set objConnection = Server.CreateObject ("ADODB.Connection")
> strConnection = "DSN=SQL_Tracker;Database=Tracker;UID=sa;PWD=;"
> objConnection.Open strConnection
> ...
> %>
> I'm getting this error on the line that opens the connection.
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'HOME\IUSR_HOME'.
> /Tracker/list_team.asp, line 11
>
> Notes:
> I've set up the DSN called 'SQL_Tracker' in my ODBC settings.
> When I use the Test Connection button it's successful.
> The SQL Server is running on my own computer, and so I have
> full DBA rights on it
> any ideas what's wrong?|||Stop using ODBC/DSN. This may not resolve the issue, but at least your
application will be faster & the database connection will be supported
in the future.

Please read the following pages.

http://www.aspfaq.com/show.asp?id=2126

http://msdn.microsoft.com/library/d..._components.asp

Peter Morris wrote:

> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
> 'HOME\IUSR_HOME'.
> /Tracker/list_team.asp, line 11

--
Please do not contact me directly or ask me to contact you directly for
assistance.

If your question is worth asking, it's worth posting.

If its not worth posting you should have done a search on
http://www.google.com/ http://www.google.com/grphp?hl=en&tab=wg&q= or
http://news.google.com/froogle?hl=en&tab=nf&ned=us&q= before wasting our
time.sql

ASP .Net 2.0 SQLServer MembershipProvider

Hi,

Currently I am developing a web application which takes a SQL Database file mdf as backend. I use SQL Server express edition as backend. I use SQL Membership provider as well as Role provider. All the data related to membership & the application is combined in a common database and exists as a single mdf file which I have attached through SQL Server Express Edtion.

My problem is I am able to access the application Tables & Stored procedures etc but not getting connected with the Membership methods.

Can any one help me in this aspect. The connection string what I have done for the attaching the database file is as follows

<addname="connectionstring"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="|DataDirectory|JsskDb.mdf";Trusted_Connection=Yes"providerName="System.Data.SqlClient"/>

<addname="MyProviderConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|JsskDb.mdf;Integrated Security=SSPI;"providerName="System.Data.SqlClient"/>

The first one works while the later doesn't work.

Help please,

Thanks,

Uma Ramiya

Hi Ramiya,

According to the code you posted, I think the way you create your connection string is right, It can be declared in the connectionString node but not for Membership settings, it should be declared in the membership node. Here's the correct way.

<membership defaultProvider="AspNetSqlProvider">
<providers>
<add connectionStringName="connectionstring" /// the value is according to the name in your connectionString node you set
applicationName="/"
description=""
minRequiredPasswordLength="6"
minRequiredNonalphanumericCharacters="0"
requiresUniqueEmail="false"
enablePasswordRetrieval="false"
enablePasswordReset="false"
requiresQuestionAndAnswer="false"
passwordFormat="Hashed"
name="AspNetSqlProvider"
type="System.Web.Security.SqlMembershipProvider"/>
</providers>
</membership>

Hope this helps. Thanks.

Ask a question?why i always get the same result?

when I use sqlserver 2005 do the prediction with Microsoft_Association_Rules.

I found that I alwayes get the same result(same dataset) using the SQL statement such

as:

SELECT
Predict([movies],INCLUDE_STATISTICS,10)
From
[Movie Prediction]
NATURAL PREDICTION JOIN
(SELECT (SELECT '12 Angry Men' AS [Movie]) AS [Movies]) AS t

or

SELECT
Predict([movies],INCLUDE_STATISTICS,10)
From
[Movie Prediction]
NATURAL PREDICTION JOIN
(SELECT (SELECT 'Sport-100' AS [Movie]) AS [Movies]) AS t

the result don't very by the parameter of movie name.

wish expert give me some help.

thank you!

The most likely cause is that your model has not detected any rule having '12 Angry Men' or 'Sport-100' on the left hand side. In this case (no rule matches your input) the model will return a prediction based on the most popular items across the whole training set. To find more rules, you could tweak some algorithm parameters:

- By reducing the value of the minimum probability parameter, the algorithm will generate more rules

- By reducing the minimum support parameter, the algorithm will consider less popular itemsets to generate rules

The minimum support parameter may represent a percentage (if the value is between 0 and 1) or an absolute number (value is greater than 1)

thanks

bogdan

|||

thanks very much!

ASE ODBC 12.5 Driver has problem with SQLServer 8.0 DTS: HELP!

Hi,

On one side, I have a Sybase 12.5 32 bits Server running on AIX 5.1
(maintenance level 3). On the other, I've got W2000 5.00.2195 (SP2)
with SQL Server Standard Edition, Product Version 8.00.534 (SP2),
having installed MDAC 2.6.

I connect to the Sybase 12.5 database using Merant ASE ODBC driver for
Sybase 12.5, and using the Microsoft OLE DB Provider for SQL Server
that comes with this SQLServer version.

I try to pump over data from Sybase to SQLServer to one table at a
SQLServer user database, using a package containing a select statement
on a View on the Sybase Server side, using an ODBC 12.5 System DSN
pointing to the Sybase server. All privileges are set correctly on
either side and the ODBC datasource test gives an 'OK' on trying to
reach the Sybase Server.

When I do PREVIEW, I get a resultset alright, however, when I actually
run the package as a scheduled job, its keeps on running infinitely
until manually killed. No data at all is inserted in the the SQLServer
table.

Strange thing is, the same processes is running without any
complications on a W2000 5.00.2195 (SP3), MDAC 2.7 system, with the
same SQLServer connecting to the same Sybase Server but using Merant
ASE ODBC 12.0 instead of ODBC 12.5.

How come?

Any help will be greatly appreciated.

Martijn RutteSame permissions for the SQL Server Agent account ?

http://support.microsoft.com/?kbid=269074

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Martijn Rutte" <opales@.zonnet.nl> wrote in message
news:80634782.0311110348.6e386e95@.posting.google.c om...
> Hi,
> On one side, I have a Sybase 12.5 32 bits Server running on AIX 5.1
> (maintenance level 3). On the other, I've got W2000 5.00.2195 (SP2)
> with SQL Server Standard Edition, Product Version 8.00.534 (SP2),
> having installed MDAC 2.6.
> I connect to the Sybase 12.5 database using Merant ASE ODBC driver for
> Sybase 12.5, and using the Microsoft OLE DB Provider for SQL Server
> that comes with this SQLServer version.
> I try to pump over data from Sybase to SQLServer to one table at a
> SQLServer user database, using a package containing a select statement
> on a View on the Sybase Server side, using an ODBC 12.5 System DSN
> pointing to the Sybase server. All privileges are set correctly on
> either side and the ODBC datasource test gives an 'OK' on trying to
> reach the Sybase Server.
> When I do PREVIEW, I get a resultset alright, however, when I actually
> run the package as a scheduled job, its keeps on running infinitely
> until manually killed. No data at all is inserted in the the SQLServer
> table.
> Strange thing is, the same processes is running without any
> complications on a W2000 5.00.2195 (SP3), MDAC 2.7 system, with the
> same SQLServer connecting to the same Sybase Server but using Merant
> ASE ODBC 12.0 instead of ODBC 12.5.
> How come?
> Any help will be greatly appreciated.
> Martijn Rutte|||Hi Allan,

Exactly the same permissions.

Martijn

"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message news:<uQQgTvEqDHA.360@.TK2MSFTNGP12.phx.gbl>...
> Same permissions for the SQL Server Agent account ?
> http://support.microsoft.com/?kbid=269074

article request

can anybody tell me a useful online article,site or a book, to know about SQL
server internals
On Thu, 13 Oct 2005 02:07:03 -0700, "enbee"
<enbee@.discussions.microsoft.com> wrote:

>can anybody tell me a useful online article,site or a book, to know about SQL
>server internals
You could do a lot worse than Kalen Delaney's book Inside SQL Server
2000.
If you are referrring to SQL Server 2005 I believe an updated edition
is in preparation.
Andrew Watt
MVP - InfoPath
|||Inside Microsoft SQL Server 2000
http://vyaskn.tripod.com/inside_micr...erver_2000.htm
Also, "The Guru's Guide to SQL Server Architecture and Internals"
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"enbee" <enbee@.discussions.microsoft.com> wrote in message
news:9E5DBA88-D015-47FF-AC47-44AFFA4936CB@.microsoft.com...
can anybody tell me a useful online article,site or a book, to know about
SQL
server internals
|||SQL Server magazine puts out a CD every 6 months with the latest and
greatest. Kalen Delaney, as one person noted, is certainly an authority on
SQL Server and there are loads of articles she's written. I use it
frequently and often and every chance I get.
A years subscription (which includes online access to SQL Server magazine)
is about $60.
Cheers.
HTH
"enbee" wrote:

> can anybody tell me a useful online article,site or a book, to know about SQL
> server internals

article request

can anybody tell me a useful online article,site or a book, to know about SQ
L
server internalsOn Thu, 13 Oct 2005 02:07:03 -0700, "enbee"
<enbee@.discussions.microsoft.com> wrote:

>can anybody tell me a useful online article,site or a book, to know about S
QL
>server internals
You could do a lot worse than Kalen Delaney's book Inside SQL Server
2000.
If you are referrring to SQL Server 2005 I believe an updated edition
is in preparation.
Andrew Watt
MVP - InfoPath|||Inside Microsoft SQL Server 2000
http://vyaskn.tripod.com/inside_mic...server_2000.htm
Also, "The Guru's Guide to SQL Server Architecture and Internals"
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"enbee" <enbee@.discussions.microsoft.com> wrote in message
news:9E5DBA88-D015-47FF-AC47-44AFFA4936CB@.microsoft.com...
can anybody tell me a useful online article,site or a book, to know about
SQL
server internals|||SQL Server magazine puts out a CD every 6 months with the latest and
greatest. Kalen Delaney, as one person noted, is certainly an authority on
SQL Server and there are loads of articles she's written. I use it
frequently and often and every chance I get.
A years subscription (which includes online access to SQL Server magazine)
is about $60.
Cheers.
HTH
"enbee" wrote:

> can anybody tell me a useful online article,site or a book, to know about
SQL
> server internals

Array As parameters to procedure

Hi friends,
Please help me I want to pass an Aarry parameters from asp.net to sqlserver stored procedure. Is it possible, if yes how.

regards,
Asad Mahmood

Hi,

SQL Server doesn't seem to have any array-like parameter so what you can do is that create a CSV (comma separated values) out from your data and pass that to the procedure (which could take that as varchar(8000) etc depending on the needed length).

In the proc you could parse this CSV into say a temp table containing values as integers, if you use a function.Here is an example of such function.

|||It will be easier if you are using Arraylist but try this link for how to do it. Hope this helps.
http://www.sommarskog.se/arrays-in-sql.html

Arithmetic Overflow - VB6.0/Stored Proc - Application

Dear All,

I am a Singapore based ASP/VB developer and need your advise for a problem i am facing in my VB/SQLServer/Stored Procedure application.

When i executed a stored procedure(SQLServer 6.5) from VB(6.0) application, I had error messages, from ADO error collection, which are "Arithmetic overflow error converting numeric to data type numeric." followed by "The statement has been terminated.". This is the exact message(s) i am getting.

The commands i executed are :

1.
update live_stock
set
stock_value = convert(numeric(9,3), convert(numeric(28,3) , "2593500")/1 ),
day = convert(Char(3), datename(dw, getdate())) ,
stock_timestamp = getdate()
where
stock_code="C27 " and
market =

Pls note that the database data type of "stock_value" column is "int".

2.
update live_stock
set
stock_volume = convert(numeric(9,3), convert(numeric(28,3) , "1017000")/1 ),
day = convert(Char(3), datename(dw, getdate())) , stock_timestamp = getdate()
where
stock_code="C27 " and
market =

Pls note that the database data type of "stock_volume" column is "numeric](11, 2)".

Need you advise/help as this is taking up lot of my time.

Thanks and regards,
Muru
Singaporei know nothing of asp or vb, so maybe the reasons why you are doing it that way escape me

if i were doing it, i would write

set stock_value = 2593500

rudy|||Thanks for the reply.

For simplicity, i have mentioned the expression like "2593500"/1. Actually, this 1 is a parameter - could be 1 or 2 etc...

Any suggestion?

Regards,
Muru|||okay, try not putting quotes around your numerics

e.g. set stock_value = 2593500 / 2

rudy|||Originally posted by r937
okay, try not putting quotes around your numerics

e.g. set stock_value = 2593500 / 2

rudy

2593500 is also a parameter of string and is being passed from VB. My backend VB application is updating the database only and this program is an ugraded version of existing version. Currently, there are ASP pages accessing this data from the sqlserver database.

I can do the conversion on the VB side. But my concern is that it may affect the formatting for existing ASP pages. And also that, this will lead to changes in the parameter datatype.

For this reason, i am keeping all the existing data types for the parameters for all the functions and stored procedures.

Thanks,
Muru|||Here is the problem - you have a value that has 7 digits to the left and 3 digits to the right of the decimal point. However, your precision that you are asking for is 9 digits total - but you have 10 total. That is why you are receiving the overflow. Either change to (9,2) or (10,3) and that will fix your overflow problem. Also, be careful and examine how sql server handles rounding/truncating when you store an int/decimal(11,2) from a decimal(x,3). In your case -- When you convert to an int, ss will truncate. When you convert to a smaller decimal place, ss will round. So, in your situation you have 2 different "rounding" models which may eventually affect (adversely) your results - depending on how scientific or precise you are trying to be.|||rnealejr is correct, (you are receiving the overflow because you have 10 digits that you are casting to 9). I would add the following more general warning:

It is often very important to be aware of the implications of (both) explicit (as rnealejr pointed out) and implicit type conversions (unfortunatly, the results are often DBMS implementation, and / or version specific);

-- Simple example:
Select
1234567 / 17 As 'Truncation',
1.0000 * (1234567 / 17 ) As 'Truncation + Scale',
1.0000 * 1234567 / 17 As 'LessTruncation',
1.0000 * 1234567 / '17' As 'MuchLessTruncation',
1.0000 * (1234567 / '17') As 'Truncation + Scale'

-- More simple examples with some explanation:
Select
1234567.0000 / 17.0000 As 'A',
(1.0000 * 1234567) / (1.0000 * 17) As 'B',
1234567 / 17 As 'C'

-- Some Conversions involve truncation
Select
Cast ((1234567.0000 / 17.0000) AS Int) As 'Cast A --> Int',
Cast (((1.0000 * 1234567) / (1.0000 * 17)) AS Int) As 'Cast B --> Int',
Cast ((1234567 / 17) AS Int) As 'Cast C --> Int'

-- Some Conversions involve rounding
Select
Cast ((1234567.0000 / 17.0000) AS Money) As 'Cast A --> Money',
Cast (((1.0000 * 1234567) / (1.0000 * 17)) AS Money) As 'Cast B --> Money',
Cast ((1234567 / 17) AS Money) As 'Cast C --> Money'

-- Other Conversions are not as intuitively obvious
-- Char (implicit conversions) of A, B, and C give:
Select 1234567.0000 / '17.0000' As 'A Char',
'B Char results in an overflow' As 'B Char',
1234567 / '17' As 'C Char'
-- NOTE B Char --> results in an overflow (OVERFLOW error result)
Select (1.0000 * 1234567) / (1.0000 * '17') As 'B Char Overflow'|||For future reference, go to your sql books online and look at the "Cast and Convert" reference.|||Dear All,

Thanks a lot for your help.

I have changed the scaling from numeric(9,3) to (11,3) and it now works. When tried with (10,3), my program gave the same error again for another set of data and hence i am using (11,3).

Thanks and regards,
Muru

are there any sql servers?

I have two servers, however sometimes connection break out. can i detect sql
server accessable on other server at anytime?Ping Server
CREATE TABLE #t_ip (ip varchar(255))
DECLARE @.PingSql varchar(1000)
SELECT @.PingSql = 'ping ' + '0.0.0.0'
INSERT INTO #t_ip EXEC master.dbo.xp_cmdshell @.PingSql
IF EXISTS (SELECT TOP 2 * FROM #t_ip WHERE IP = 'Request timed out' )
BEGIN
DROP TABLE #t_ip
RETURN
END
DROP TABLE #t_ip
"Sava Gltekin" <savas@.merkez.com.tr> wrote in message
news:OCmj26bvFHA.3764@.TK2MSFTNGP09.phx.gbl...
>I have two servers, however sometimes connection break out. can i detect
>sql server accessable on other server at anytime?
>

Are there any good performance tuning tools that I can use to moni

Are there any good performance tuning tools that I can use to monitor SQL
Server database? Thanks.
You can try:
perfmon (comes with windows)
For sql 05 you can use "SQL Server Profiler". I am not sure if it comes
with 2k coz I never used it.
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:146ECA05-943B-4ACE-9418-6FD09859872E@.microsoft.com...[vbcol=seagreen]
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
|||If you are looking for tools available in SQL Server and/or Windows there
are several choices like Performance/System Monitor in Windows, SQL Server
Profiler, DMVs, DMFs, some system stored procedures, etc.
For more details look for 'Tools for Performance Monitoring and Tuning' in
SQL Server 2005 BOL.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Iter" wrote:
[vbcol=seagreen]
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
|||Is there any third party tools that I can use? Thanks.
"Ben Nevarez" wrote:
[vbcol=seagreen]
> If you are looking for tools available in SQL Server and/or Windows there
> are several choices like Performance/System Monitor in Windows, SQL Server
> Profiler, DMVs, DMFs, some system stored procedures, etc.
> For more details look for 'Tools for Performance Monitoring and Tuning' in
> SQL Server 2005 BOL.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Iter" wrote:
|||Also take a look at DMVStats at http://www.codeplex.com/sqldmvstats
Linchi
"Iter" wrote:
[vbcol=seagreen]
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
|||Else than Database Tuning Advisor and SQL Server Profiler?
I'm not sure if you are aware of these tools however these tools come with
SQL Server 2005 (in case you install Management Tools)
Ekrem ?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:E0686DDF-3C36-4354-9120-E41B32A2C37C@.microsoft.com...
> Are there any good performance tuning tools that I can use to monitor SQL
> Server database? Thanks.

Are there any good performance tuning tools that I can use to moni

Are there any good performance tuning tools that I can use to monitor SQL
Server database? Thanks.You can try:
perfmon (comes with windows)
For sql 05 you can use "SQL Server Profiler". I am not sure if it comes
with 2k coz I never used it.
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:146ECA05-943B-4ACE-9418-6FD09859872E@.microsoft.com...[vbcol=seagreen]
> In addition, it can use for monitoring blocking. Thanks.
> "Iter" wrote:
>|||In addition to the other posts, check out SQLDIAG.EXE (comes with 2005). It'
ll help you automate the
collection of this information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:E0686DDF-3C36-4354-9120-E41B32A2C37C@.microsoft.com...
> Are there any good performance tuning tools that I can use to monitor SQL
> Server database? Thanks.|||Else than Database Tuning Advisor and SQL Server Profiler?
I'm not sure if you are aware of these tools however these tools come with
SQL Server 2005 (in case you install Management Tools)
Ekrem ?nsoy
"Iter" <Iter@.discussions.microsoft.com> wrote in message
news:E0686DDF-3C36-4354-9120-E41B32A2C37C@.microsoft.com...
> Are there any good performance tuning tools that I can use to monitor SQL
> Server database? Thanks.

Are packets from .NET to SQL server secure?

When I'm getting data from sqlserver using ado.net and a sqldataadapter, are the resultant network traffic packets secure? If I wanted to deploy my objects at a remote site, would I still be safe going straight to my sql server from there or should I build a web service and then auto generate 'remote' versions of my objects that will then communicate to the web service on https?

thanks
pati think you need to encrypt your data using SSL to secure your data transfer...other wise any sniffer prog can get the data...

hth|||You can do the VPN thing too - that's secure, right?

Are NT users stored in SQL Server 2000/2005 transaction logs?

I am trying to implement auditing the cheapest way possible. I know SQL
Server users get logged but how about NT users?
--
MCP MCDBA MCAD MCSD MCT MCTS:SQL Server 2005
"Helping people grow and develop their full potential as God has plan for
them"Hi
Logins are for SQL Server access
Users are for database access
http://vyaskn.tripod.com/sql_server...t_practices.htm --sec
urity
best practices
"bass_player" <bassplayer@.discussions.microsoft.com> wrote in message
news:1719566F-D101-441F-95F6-B9D11AAD347B@.microsoft.com...
>I am trying to implement auditing the cheapest way possible. I know SQL
> Server users get logged but how about NT users?
> --
> MCP MCDBA MCAD MCSD MCT MCTS:SQL Server 2005
> "Helping people grow and develop their full potential as God has plan for
> them"|||They (nt or sql logins) do not get logged in the transaction log. Do check
out the following article for details:
http://www.microsoft.com/technet/se...r/sql2kaud.mspx
-oj
"bass_player" <bassplayer@.discussions.microsoft.com> wrote in message
news:1719566F-D101-441F-95F6-B9D11AAD347B@.microsoft.com...
>I am trying to implement auditing the cheapest way possible. I know SQL
> Server users get logged but how about NT users?
> --
> MCP MCDBA MCAD MCSD MCT MCTS:SQL Server 2005
> "Helping people grow and develop their full potential as God has plan for
> them"