Exception with SQL connection - Database Discussion Boards

Re: Loading Excel to DataSet

Mike Ellison16-Oct-04 2:32

Mike Ellison

16-Oct-04 2:32

Hi there. In your connection string, you can use the IMEX import mode - IMEX=1. Something like this:

string sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SomeExcelWorksheet.xls;"
            + "Extended Properties=\"Excel 8.0;IMEX=1\"";

You can look at this Microsoft Support document[^] for a more thorough description of the IMEX option.

Re: Loading Excel to DataSet

leszekwlodarski17-Oct-04 23:32

leszekwlodarski

17-Oct-04 23:32

Thanks,

this is what I was looking for,

Best regards

Leszek

Asscess and linked tables

Jerome Conus14-Oct-04 20:52

Jerome Conus

14-Oct-04 20:52

Hi !

My application (developped in C++) is using an Access database. The database is actually made of two '*.mdb' files, one being linked to the other with linked tables.

It works fine : I use one database, and I can make SQL queries including the linked tables from the other without any troubles.

My problem now is if I want to install my app on someone else's computer, the path to the databases is different, but the link is absolute. Thus, the linked tables are not found and my application doesn't work.

I know that with Access, there is a menu to update the path of the linked tables, but of course, I don't want to have to do it manually through the Access Application : I want to be able, from my app, to update the linked tables automatically.

Problem : I have no idea if it is possible to do that, and how !

Any help ?

Thanks !
Jerome

Re: Asscess and linked tables

Mike Ellison16-Oct-04 2:43

Mike Ellison

16-Oct-04 2:43

Hi Jerome. I think this Microsoft Support document[^] may have some useful information for you. You may want to look at the Developer Solutions sample program (Solutions9.mdb) referenced in the document.

Insert record using DataReader

Anonymous14-Oct-04 11:46

Anonymous

14-Oct-04 11:46

Guys,

I have a stored procedure in SQL Server where I pass few parameters like username, email address etc. The stored procedure simply inserts these records into users table that has an identity column as ID. Finally I read the @@IDENTITY variable to read the ID of newly inserted record and returns it via an OUT parameter passed in the stored procedure.

Now question is if I have to call this stored procedure from my C# code, can I used DataReader for this purpose? What are the best practices?

TIA
Jixi

Re: Insert record using DataReader

Colin Angus Mackay14-Oct-04 23:30

Colin Angus Mackay

14-Oct-04 23:30

If you are accessing the newly inserted ID via an OUT parameter then you would use SqlCommand.ExecuteNonQuery().

You could alterantively return the newly inserted ID via a select statement and use a data reader if you want to. e.g. After the insert in your stored procedure you would put

SELECT @@IDENTITY As Identity

and you could retrieve that single value using a DataReader, but that is really a bit reduntant because that is what SqlCommand.ExecuteScalar() does for you anyway.

Do you want to know more?

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

Re: Insert record using DataReader

Anonymous15-Oct-04 3:51

Anonymous

15-Oct-04 3:51

Thanks for your help Colin, appreciate it.

Jixi

Exception with SQL connection

Steven M Hunt14-Oct-04 4:07

Steven M Hunt

14-Oct-04 4:07

I keep getting this same exception when i try to connect to a sql database:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
dev.Random1.Page_Load(Object sender, EventArgs e) in random1.aspx.cs:27
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750

any ideas? This is a work-related project so i need to figure out the error quickly.

-- Steve

Re: Exception with SQL connection

Colin Angus Mackay14-Oct-04 4:55

Colin Angus Mackay

14-Oct-04 4:55

You need to either log in with an account that has permission to use SQL Server, or you need to set up the permission in SQL Server for the account you want to use.

I'm not really a fan of allowing Anonymous access to things in a business senario so I would suggest that you log in using an appropriate account. As you are accessing this through an ASP.NET application I am curious as to what your security settings are, if you check the box that says allow anonymous access then it will access resources through a special ASP.NET account.

Do you want to know more?

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

Disable Auto Ansi to OEM

Its due when13-Oct-04 18:26

Its due when

13-Oct-04 18:26

Hi All,

Does anyone know how to disable the Auto Ansi to OEM option in SQL 2000. Its not in the tab as that is for the DB library and not the ODBC connection that 2000 uses. Any help would be greatly appreciated. Thanks

N.

search for a specific string in a stored procedure

its_skar13-Oct-04 11:29

its_skar

13-Oct-04 11:29

hi

I have a very large database with several stored procedures. now, I need to find a stored procedure that does some calculation if that stored procedure has a string "MESSAGE"(just an example) in an IF statement somewhere down the procedure.

currently i am opening every stored procedure, doing a Ctrl+F for the above string and if i find it then that means i determine that this is the stored procedure that i will have to work with(change the calculation in it or some work)

so my question is that can i write something ... that will list me all the stored procedures which have a certain string contained in them??

I am new to sql stuff .. so please help me. this could save me a lot of time.

thanks
-messageman

Re: search for a specific string in a stored procedure

Christian Graus14-Oct-04 10:04

Christian Graus

14-Oct-04 10:04

sp_helptext is a system stored procedure which takes the name of a stored procedure and then lists it. This code will list all your stored procedure names:

SELECT name FROM sysobjects WHERE type = 'P'

So all you need to do is make a table of the names, then go through it, calling sp_helptext to get the stored procedure text, and if your text matches, return the name.

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Re: search for a specific string in a stored procedure

its_skar14-Oct-04 13:43

its_skar

14-Oct-04 13:43

hi christian

thank you for the reply. the thing is that there are 120 stored procedures in the database and I need to know which stored procedure's BODY contains the string 'MESSAGE', etc. some stored procedures are almost 4 pages long.

I am not searching for the stored procedure named %Message%. but searching for a stored procedure whose body contains the string.

can you please give me a code sample ?

thanks

Re: search for a specific string in a stored procedure

Grimolfr15-Oct-04 7:29

Grimolfr

15-Oct-04 7:29

SELECT
    ROUTINE_NAME
FROM
    INFORMATION_SCHEMA.ROUTINES
WHERE
    ROUTINE_DEFINITION LIKE '%MESSAGE%'

Grim

(aka Toby)

MCDBA, MCSD, MCP+SB

Need a Second Life?[^]

SELECT * FROM user WHERE clue IS NOT NULL
GO

(0 row(s) affected)

Re: search for a specific string in a stored procedure

Christian Graus17-Oct-04 9:46

Christian Graus

17-Oct-04 9:46

its_skar wrote:
the thing is that there are 120 stored procedures in the database and I need to know which stored procedure's BODY contains the string 'MESSAGE', etc. some stored procedures are almost 4 pages long.

Sure - so you need to use the code I provided to build a table that has a list of stored procedure names. Then you need to use that tempory table to step through the stored procedures, IN CODE, retrieving the text of the stored procedures, WHERE the text is LIKE %Message%.

its_skar wrote:
I am not searching for the stored procedure named %Message%. but searching for a stored procedure whose body contains the string.

That's why I gave you the name of the stored procedure that returns all the text of the procedure. You also need to get the names, otherwise, how will you know where to get the text from ?

its_skar wrote:
can you please give me a code sample ?

If I find the time tonight, I'll see what I can put together for you.

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

Disable ODBC default settings

Its due when12-Oct-04 20:30

Its due when

12-Oct-04 20:30

Quoting from MSDN:
"SQL Query Analyzer uses the SQL Server ODBC driver, which, by default, sets these SQL-92 options on: SET ANSI_WARNINGS, SET ANSI_PADDING, and SET ANSI_NULLS. Any errors returned are formatted as ODBC errors rather than DB-Library errors."

Is there any way to modify these options so that they are not the default?
Thanks!

Re: Disable ODBC default settings

Mike Dimmick13-Oct-04 12:38

Mike Dimmick

13-Oct-04 12:38

If you mean in Query Analyzer, go to Tools/Options and change the settings on the Connection Properties tab. This affects new connections.

Stability. What an interesting concept. -- Chris Maunder

regd MSSQL database connection

Anonymous12-Oct-04 18:55

Anonymous

12-Oct-04 18:55

Hi,
I have a program in which I connect to MSSQL database on my system ( local host) I want to connect to a remote database on a system with particular IP. How do I do this?

Karteek

Re: regd MSSQL database connection

Colin Angus Mackay12-Oct-04 23:54

Colin Angus Mackay

12-Oct-04 23:54

You don't specify what your program is written in, so I'll assume it is a language that targets the .NET Framework.

You change the connection string property of the SqlCommand object so that the part of the string that reads "Server=(local)", changes the (local), or 127.0.0.1 or whatever to the IP or machine name of the remote server.

Do you want to know more?

Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.

Help! Using a Palm III as a network conn.?

...---...12-Oct-04 6:06

...---...

12-Oct-04 6:06

Could someone suggest how I might best use my PalmIII w/ my laptop to emulate a network database or databases?
I need a lot of work/ SQL and .NET? (C# and C++/MFC)
Any articles, advice suggestions would be greatly appreciated. thanks
[ps- If it seems like I don't know what I'm talking about, you're right-- I don't.....]

Re: Help! Using a Palm III as a network conn.?

Christian Graus14-Oct-04 10:11

Christian Graus

14-Oct-04 10:11

You can't, simple as that. A PalmIII is only able to connect via a conduit, I believe, and certainly there is no support for SQL databases on the Palm.

Why do you need the network angle ? Just install MSDE or SQL Server on your laptop, the system will be agnostic to where the db comes from anyhow.

Christian

I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer

AddNew();

Stgt12-Oct-04 4:11

Stgt

12-Oct-04 4:11

hello I do not have the following problem I can data to my data base add. Only if a data record is present before already in the data base, it functions. That that is appropriate for best ones owing to

void CAdoDatabaseDoc::AddNew()
{
try
{
if (m_ptrRs->Supports(adAddNew))
{

m_piAdoRecordBinding->AddNew(&m_rsRecSet);

strcpy(m_rsRecSet.m_szLastName, " " );
strcpy(m_rsRecSet.m_szFirstName, " " );
strcpy(m_rsRecSet.m_szAge, " " );
m_rsRecSet.m_dtAuswertedatum = (DATE)dtBlank;
m_rsRecSet.m_dtVersuchsdatum = (DATE)dtBlank;

}

}

....

Re: AddNew();

Stgt13-Oct-04 22:48

Stgt

13-Oct-04 22:48

hi my problem is now that I get an empty data base entry. How does one make that correct?

thanks

Re: AddNew();

Anonymous13-Oct-04 22:49

Anonymous

13-Oct-04 22:49

void CAdoDatabaseDoc::AddNew()
{

try
{

// Kann ein neuer Datensatz hinzugefügt werden?
if (m_ptrRs->Supports(adAddNew))
{

m_ptrRs->AddNew();

m_ptrRs->Update();

//Leeren Datensatz hinzufügen
//m_piAdoRecordBinding->AddNew(&m_rsRecSet);

//COleDateTime dtBlank;

//strcpy(m_rsRecSet.m_szLastName, " " );
//strcpy(m_rsRecSet.m_szFirstName, " " );
//strcpy(m_rsRecSet.m_szAge, " " );
//m_rsRecSet.m_dtAuswertedatum = (DATE)dtBlank;
//m_rsRecSet.m_dtVersuchsdatum = (DATE)dtBlank;
//strcpy(m_rsRecSet.m_szLieblingsfarbe, " " );

//Aktuellen Datensatz aktualisieren
//m_piAdoRecordBinding->Update(&m_rsRecSet);

}

}

// Fehler?
catch (_com_error &e)

{
// Fehlermeldung generieren
GenerateError(e.Error(), e.Description());
}

}

Connection Object

EdbertP11-Oct-04 20:29

EdbertP

11-Oct-04 20:29

I have something I want to clarify.
I did a .NET project some time ago and I'm not sure I remember this correctly, but I don't think I called conn.Open() whenever I want to fill a dataset.
I think I read somewhere that calling dataAdapter.Fill() will open the connection and close it right away after it fills the dataset.

If for example I have 100 datasets to fill, would it make a difference if I call conn.Open() before I tried to fill the datasets and then call conn.Close(), or should I leave it to the dataAdapter.Fill() to open and close the connection?
Which one is more efficient? Does it depend on the database server (connection pooling, etc)?

Thanks,

Edbert P.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.