SQL - Database Discussion Boards

Jeff Varszegi29-Apr-04 11:32

29-Apr-04 11:32

SELECT
   COLUMN_NAME,
   DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'whimsy'
ORDER BY ORDINAL_POSITION

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

Re: Name and Data Type of all columns

DotNet_Newbie29-Apr-04 11:56

DotNet_Newbie

29-Apr-04 11:56

Thank you..

Re: Name and Data Type of all columns

Jeff Varszegi29-Apr-04 12:05

Jeff Varszegi

29-Apr-04 12:05

You're welcome! I assume you have access to Query Analyzer, yes? You could spend a fun couple of hours just poking around in

1) the master.dbo tables, where lots of useful information is stored;

2) INFORMATION_SCHEMA views, which Microsoft recommends whenever possible;

3) the other databases like msdb, where job and replication info is stored

etc. etc. Also look up system functions and sp_ stored procedures in books online. After all that, you'll have the beginnings of the beginnings of a good working knowledge of SQL Server. And get your boss to spring for the Microsoft Press books on SQL Server; they're really good.

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

Auto Translate=False problem

saglam29-Apr-04 5:38

saglam

29-Apr-04 5:38

For some reasons I have to use Auto Translate=False in my connection string.
My connection string is like;
"data source=2003-SERVER;Initial Catalog=db_name;uid=db_user;pwd=user_password;max pool size=50;Auto Translate=False"

This works fine when I use system.data.oledb. But not working with system.data.sqlclient. It gives me

System.ArgumentException: Keyword not supported: 'auto translate'

This shows me that I cannot use Auto Translate with system.data.sqlclient, but when I try to add sqlconnection from Visual Studio 2003, ToolBox->Data->SqlConnection and then build connection string from properties window <new connection...>, I can see Auto Translate option in Data Link Properties' All tab. Ok I set it to false and turn back to code behind to see how it uses auto translate=false, I saw nothing about it. ConnectionString is in there but contains nothing about Auto Translate=False.

Any ideas about how to add Auto Translate=False to my connection string, and use it with system.data.sqlclient?

Thanks in advance.

Re: Auto Translate=False problem

Mike Dimmick4-May-04 23:04

Mike Dimmick

4-May-04 23:04

System.Data.SqlClient implements the Tabular Data Stream (TDS) protocol for communicating with SQL Server directly (well, it uses the underlying dbnetlib.dll so that any protocol can be used, but it performs all the formatting itself). You shouldn't necessarily expect all parameters to be supported.

The Auto Translate option, by my understanding, applies to character data supplied to OLE DB in byte-oriented character format (loosely referred to as 'ANSI' by a lot of MS documentation). In order to prevent misinterpretation by the remote server, it converts from the thread's character set to Unicode (UTF-16) then back from UTF-16 to the server's configured byte-oriented character set. If you turn the option off, it simply passes the bytes directly to the server with no translation; if the client and server use different encodings, this can lead to misinterpreted data.

However, in .NET you only provide character data to the managed provider as a System.String. This type is natively Unicode UTF-16; hence the Auto Translate option, if it were available, would have no effect.

If you're trying to store and retrieve binary data, use a binary-type column (binary, varbinary or image) and the appropriate type for the SqlParameter (SqlDbType.Binary, SqlDbType.VarBinary, SqlDbType.Image).

If you're reading byte-oriented character data from a file, ensure that you're interpreting it correctly by setting the Encoding in your StreamReader constructor, or use the appropriate encoding when converting a byte array to a string.

If you post the exact problem you're experiencing, it might be possible to work out a solution.

Stability. What an interesting concept. -- Chris Maunder

Re: Auto Translate=False problem

saglam10-May-04 8:38

saglam

10-May-04 8:38

My problem is; I have a sql server which has collation: Latin1_General_CI_AS, and a database with same collation (actually, I am not the designer of that db, and I cant change any settings). In a table I have a varchar(60) column. When I insert text data which contains Turkish characters, Turkish characters are not stored normal. So when I retrieve those data to my asp.net application, Turkish characters are not displayed correctly, which is expected. All my language settings in web.config file is set to Turkish, but this still couldnt solve my problem. But when I use data.oledb to connect sql server and set auto translate=false, everything works fine. But when I use data.sqlclient, Turkish characters are not displayed correctly. And I cant use auto translate=false property with sqlclient.

Any ideas?

Thanks in advanced

Re: Auto Translate=False problem

Mike Dimmick10-May-04 9:49

Mike Dimmick

10-May-04 9:49

Ah, I see what you're doing: setting Auto Translate=False simply does a byte-for-byte copy into the field - you're basically pretending to SQL Server that you're providing the correct encoding.

The database column is using code page 1252[^] (Windows Western), but your data is encoded with 1254[^].

This really isn't a good idea. Searches and sorts are unlikely to match your cultural expectations if the server has the wrong collation.

I'm afraid I can't offer a short-term solution: I don't know enough about it. The long-term solution is to get the collation changed for the field.

I think this is probably a job for Microsoft tech support.

Stability. What an interesting concept. -- Chris Maunder

SQL

Boycech29-Apr-04 4:56

Boycech

29-Apr-04 4:56

Hi, what is the best way to write SQL code when creating ASP Web enabled systems that access a Sequel Server Database? I'm use to Access so when I have a complicated query, I've been creating a carbon copy DB in Access, setting up the relationships, and then creating queries so I can use the SQL it generates in ASP. I'm not up to speed yet with writing SQL when you have to include inner and outer joins etc. What are my options other than what I'm doing now. Probably just study up on SQL? Thanks

Boycech

Re: SQL

Mike Ellison30-Apr-04 5:44

Mike Ellison

30-Apr-04 5:44

Hi Boycech. Using Access the way you're describing isn't a bad way to start getting to know SQL - just be aware that Access' flavor of SQL isn't exactly ANSI-compliant. Access is also notorious for generating sql statements that are far more verbose than they need be - downright confusing at times.

If you're really just getting started with SQL, take a look at the W3Schools Sql Tutorial[^]. You might find it useful.

Re: SQL

Boycech30-Apr-04 5:46

Boycech

30-Apr-04 5:46

Thanks I will.

Re: SQL

Bill Dean4-May-04 5:04

Bill Dean

4-May-04 5:04

Howdy,

A few random bits of advice: 1) You are dead-right: study up on SQL. 2) Make use of stored procedures (aka: sprocs). I don't know if there is an Access equivalent, but in sql-server a sproc is like a function call: you pass it parameters when you call it, it does some voodoo (run sql code) on the database and returns a record set (if you need it to). The big advantage to sprocs is that your ASP code doesn't rely on how you implemented things in the database. Think of them as a layer of insulation between the GUI and the data. So 6 months from now when you need to change something on the database, all you need to do is adjust the code in the sprocs to assure that they all still return the same data. Otherwise a design change on your database might break A LOT of your GUI.

Bill

Re: SQL

Boycech4-May-04 5:18

Boycech

4-May-04 5:18

Thanks a lot Bill!

Re: SQL

talamar10-May-04 15:46

talamar

10-May-04 15:46

Access (at least the 2003 version) does support Stored Procedures - only in an ADP Project, using an SQL database as the backend. I used Access to learn SQL, so I would reccomend it for any real beginner. Smile | :)

Create an Access Database at runtime

bsargos29-Apr-04 4:55

bsargos

29-Apr-04 4:55

Hi,

I'm writing a program based on an Access Database.
I need to create this database while installing the Application Installer.
While reading Visual Studio documentation, I saw I have to use the SQL Keyword CREATE DATABASE. This is ok for Sql Server, but not for Access.

How can I do ? Confused | :confused:

Any idea is welcome.
Thanks.

Re: Create an Access Database at runtime

BadJerry29-Apr-04 5:13

BadJerry

29-Apr-04 5:13

There is an easy way with DAO: CDaoDatabase::CreateDatabase... I know you posted it this in the ADO section but this could be an easy way out!

Re: Create an Access Database at runtime

Santanu Lahiri29-Apr-04 10:24

Santanu Lahiri

29-Apr-04 10:24

Not exactly. If you try to mix ADO and DAO, you normally get a ton of conflicts. More specifically, the DataType enum seems to have been defined in both libraries, and they are not compatible. That is the voice of experience Poke tongue | ;-P

speaking!

ADOX supposedly has a Create function in its catalog class that can be used to create a database at run time. I have not had much luck with it yet. Perhaps Carlos Antollini (of CADOXxxx classes fame) may have an answer? I read that he has an update to his ADOX classes about ready. I sure am looking forward to it.

Re: Create an Access Database at runtime

Santanu Lahiri30-Apr-04 5:55

Santanu Lahiri

30-Apr-04 5:55

This seems to work fine with an abbreviated Connection string.
You can not use the full-blown connection string.

I just tested it and it created the database successfully. Probably not the most efficient error reporting, but that you can modify to suit your needs.

The CADOXCatalog comes from Carlos Antollini's ADOX class available on CodeProject website.

------------------------------------------------------------------------
CString sConnectionString = _T("Provider='Microsoft.Jet.OLEDB.4.0';Data Source='%s'");
CString sMsg;
try
{
CADOXCatalog cat;
CString sConnect;
sConnect.Format (sConnectionString, sDBName);
cat.m_pCatalog->Create(_bstr_t((LPCTSTR) sConnect));
sMsg.Format (_T("Database %s created successfully"), sDBName);
}
catch (_com_error& e)
{
TCHAR szBuf[300];
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
sprintf(szBuf, "Source : %s\nDescription : %s", (LPCSTR)bstrSource,(LPCSTR)bstrDescription);
CString sErr = CString (szBuf);
sMsg.Format (_T("Can not create database %s\n%s\n%s"), sCmd, CString (e.ErrorMessage()), sErr);
}
AfxMessageBox (sMsg);

Sage Accounting Version 10

Kryptos Sol29-Apr-04 4:43

Kryptos Sol

29-Apr-04 4:43

Has anyone use connected sucessfully to Sage using there shipped driver?

You can query all of the tables using Excel, but can't using either vb6 or vb.net.

Thanks in advance.

Wayne Taylor
Kryptos Solutions
Cry | :((

exec two stored procedure in a main stored procedure

manasrahfantom29-Apr-04 2:27

manasrahfantom

29-Apr-04 2:27

Hi there

i have a stored procedure like this:

CREATE PROCEDURE SP_Main

AS
SET NOCOUNT ON

BEGIN TRANSACTION

exec SP_Sub_One output

exec SP_Sub_Two output

IF @@ERROR = 0
BEGIN
-- Success. Commit the transaction.
Commit Tran
END
ELSE
Rollback Tran

return
GO

now the problem is, when i execute the stored procedure's inside the main stored procedure, and these sub sp's has an error on it, the main stored procedure doesnt rollback the transation.

now i can put the "begin transation" in the two sub stored procedure's. The problem is

what if the first "SP_Sub_One" executed successfully, and there was error in "SP_Sub_Two"

now the "SP_Sub_One" has been executed and i cant rollback it... the error occured in the
"SP_Sub_Two" stored procedure ... so it wont run ... so there will be error in the data

how can i make a mian "BEGIN TRANSACTION" , that even it include the execution of stored procedure in it.

Thanks in advance

Mahmoud Manasrah

Re: exec two stored procedure in a main stored procedure

Jeff Varszegi29-Apr-04 3:11

Jeff Varszegi

29-Apr-04 3:11

Check out this transaction documentation from the MSDN site.

Note especially this paragraph:

Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback are in fact rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back.

Over time, I've learned to always use named transactions as a matter of course. When I use them in stored procedures, I often name them after the procedure; so in procedure pr_execute_code_a, I'd probably use

BEGIN TRANSACTION peca

This only minimizes the chance of a naming conflict, so you still have to be careful, but you get the idea. With named transactions, you're free to wrap stored procedures in other ones at will and still guarantee data integrity; the inner stored procedures can roll back just their own data if that's appropriate, and the outermost procedure has complete control over its own transaction; it can roll everything back or not.

When I am creating an "open" stored-procedure API, I always make sure that the procedure returns some non-zero value (always 1 if I'm not returning specific error codes) in case of an error, in addition to a RAISERROR call and/or a rollback of a transaction. That way calling code (which may be an enclosing procedure) has enough information to roll back a transaction. You know how to get this from an EXECUTE statement, right?

<br />
DECLARE @returnCode         AS INT<br />
<br><br />
-- ...<br />
-- ...<br />
<br><br />
EXEC @returnCode = pr_DoSomething<br />
--<br />
IF (@returnCode <> 0) BEGIN<br />
   -- roll back your transaction, if appropriate<br />
   -- raise an error, if appropriate<br />
   RETURN(1) -- if appropriate<br />
END -- IF<br />

In your case, if you're the one controlling and using the code and the inner procedures are always called by some other, you may not need transactions in your inner procedures at all, only in the outermost one(s).

Regards,

Jeff Varszegi

EEEP!

Re: exec two stored procedure in a main stored procedure

manasrahfantom30-Apr-04 21:33

manasrahfantom

30-Apr-04 21:33

Hi Jeff ,

Thank you alot of ur reply, it was reallly great

Wish u luck

Thanks Alot

Mahmoud Manasrah

Re: exec two stored procedure in a main stored procedure

Jeff Varszegi1-May-04 6:49

Jeff Varszegi

1-May-04 6:49

You're welcome! Thanks for the extra luck-- I could use some right now. Have a great day.

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

ADO.NET Deletion from SQL DB problem.

Member 105947128-Apr-04 19:52

Member 1059471

28-Apr-04 19:52

I am using a dateset.

Heres the problem:
Lets assume theres a table T, with one row in it. I populate my dataset by using Fill().

In my software I now delete the row (from the dataset only).

If I use the Update() with the dataset, the row will be removed from the DB. This is fine.

But what happens if I do this:
1) Call Fill() to populate the dataset.
2) Delete the row from the dataset.
3) Add a new row to the dataset.
4) Delete this new row again.

The problem is that there will now be two rows in the dataset that is flagged to be deleted when Update() is called. But only one of these will be in the database!

If I call Update() now, the system will try to delete both from the DB, but since only one of them is available in the DB to delete, a Concurrency exception will be thrown (since no rows will be affected when the system tries to delete the nonexistant row).

What is the best practice in these kind of situations?

Thanks for your time.

Transferring Data

ibok2328-Apr-04 5:40

ibok23

28-Apr-04 5:40

I am having to come up with factors that may be involved when you are transferring data from one database architecture to another. In other words, if I wanted to transfer to RDBMS from a network architecture what do I need to look for?

How can I find out this information - if I wanted to research? I have been trying to find it, but I must be putting in the wrong keywords for my search.

Thank you,

ibok23

check login exists

Dpriya28-Apr-04 2:50

Dpriya

28-Apr-04 2:50

Hi,
How do I check whether a login exists for a particular database in MSDE?

Code:
SqlConnection objConn = new SqlConnection(strCon);
objConn.Open();
SqlCommand objCmd = new SqlCommand();
objCmd.Connection = objConn;
//eg: if login 'priya' does not exist in database PRIYA how do i do it
objCmd.CommandText = @" EXEC sp_addlogin @loginame ='abb',@passwd ='abb',@defdb = 'ABB' EXEC sp_grantdbaccess N'abb', N'abb' EXEC sp_addrolemember N'db_owner', N'abb'" ;
objCmd.ExecuteNonQuery();

Thankx in advance
Priya

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