|
This example returns the name of the default collation for the server.
SELECT SERVERPROPERTY('Collation')
This example returns the name of the default collation for the Northwind database.
SELECT DATABASEPROPERTYEX('Northwind', 'Collation')
Getting the collation for a column is not as easy. The only way I have found is to use the SQL_VARIANT_PROPERTY function. It's only supposed to work on sql_variant types, but it seems to work on other types as well.
SELECT TOP 1 SQL_VARIANT_PROPERTY(Column, 'Collation') FROM Table
|
|
|
|
|
Thank you. 
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
I want to write some unicode(farsi) into my SQLServer database from my c# application.When I write them in my app they are ok but after they are written in SQL ? apear instead of them.
Any idea?
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Solved.I have to change my collation in SQL.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Hey,
I've just started trying to program with mySQL (first time programming for a database) and I'm encountering some problems. I'm using Visual C++ v6 and mySQL++ v1.7.1 and when I try to compile any program the compiler is coming up with hundreds of errors all of which reside in the mySQL++ source files. I find it hard to believe that there are actually errors in the source files and I can't seem to relsove this problem, if anyone had any ideas or solutions it would be much appreciated.
Thanks in advance.
|
|
|
|
|
 How can I access a database (in access2000) over internet connection.
I want to have an app that would upload a picture with a description to that database remotely.
Thanks
_ra
|
|
|
|
|
I am doing:
if(pAdoDb.Open((LPCTSTR)strConnection))
{
CString strCommandText;
CString strQry = _T("");
int numRecords;
strQry.Format(_T("SELECT DISTINCT (Color) From tblTest"));
CADOCommand pCommand(&pAdoDb, strCommandText = _T(""),CADOCommand::typeCmdText);
pCommand.SetText(strQry);
CADORecordset pRs(&pAdoDb);
if(!pRs.Execute(&pCommand))
AfxMessageBox("failed");
This snippet worked with "select * from tblTest".
With the distinct command above I am getting a runtime error:
CADORecordSet error
code = 80040e10
code meaning = IDispatch error #3088
Source = Microsoft JET database engine
Description = No value given for one or more required parameters
What am I doing wrong? Please help!
Thanks,
ns
|
|
|
|
|
How can I get the db2 database object size on the AS400?
Jassim Rahma
|
|
|
|
|
Does anyone have a good piece of SQL that will randomly select 1 row from a database?
I was thinking of doing a sqequence something like this
Count the number of rows
Generate a random number (rn) between 1 and the result of the count
Select Top (1) From (Select Top (rn) Order by ID DESC)
Is there a better way?
Thanks
Stephen.
|
|
|
|
|
Here are some articles about "Randomness" in SQL, however this article looks the closest to what you're trying to achieve.
Andy Gaskell, MCSD MCDBA
|
|
|
|
|
newbie here so please be kind
why won't this work?
SqlString = "SELECT * FROM Accounts ";
SqlString += "WHERE (Nick IS"; // or with the "=" sign
SqlString += tNick;
SqlString += ")";
recset.Open(CRecordset::forwardOnly,SqlString,CRecordset::readOnly);
i am trying to find the "id" so i may search another record
shotgun
shotgun
|
|
|
|
|
SqlString = "SELECT * FROM Accounts ";
SqlString += "WHERE (Nick = '";
SqlString += tNick;
SqlString += "')";
If you're looking for a string use "'"
cheers,
Chris Maunder
|
|
|
|
|
Chris's answer is correct. However you need not use the += operator, it is inefficient in this case as it does extra memory allocation. You can write
SqlString = "SELECT * FROM Accounts WHERE (Nick = 'tNick');
Best regards,
Alexandru Savescu
|
|
|
|
|
Alexpro wrote:
SqlString = "SELECT * FROM Accounts WHERE (Nick = 'tNick');
If you are only interested in the "id", its much better to use
select id from accounts where Nick='tNick'
Dave
|
|
|
|
|
I have been looking at a variety of options for doing bulk loads from the .net framework - not of which are quite what I want. There is no 'native' class that allows access to the irowsetfastload interface in oledb.
Here is a picture of what I want to do:
1. Load and parse a 1.2 million line file into a structured dataset in memory
2. Perform some 'massage' work on data in memory
3. Shove the data into SQL Server 2000 as quickly as possible
The dataset classes in ADO.net are wonderful and work perfectly for what I am trying to do. The problem is that when I issue the database inserts, they are too slow to do in the timeframe that I need. I can dump the data to a file from the dataset, and use bcp or some such method, but I don't want that additional overhead. Actually what I have implemented so far has an FTP object that allows me to load the FTP dataconnection stream right into a memory stream, and I can process the data as it's coming off the wire, put into a dataset, and them cram into database - all without any file IO (except for database of course).
The Irowsetfastload interface is seems to be exactly what I need, but I cannot access it very well from the framework, and I'm no OLEDB expert. There are very few articles about this on the internet, certainly not enough to get started. Have recently tried the xmlbulkload object through interop, but it requires a file name, so I may as well use bulk insert TSQL - it doesnt buy me anything.
A few questions:
Would it be possible to write a Managed c++, c# or VB.net class that encapsulates this interface? How would one go about doing so. If it was done in managed c++, I assume the library could easily be used by c# or VB. To give an idea of how I would like to do this - I really want a method on the dataset itself like ds.fastload with a few parameters about the load itself. Extra points if someone can figure out how to do an 'UPSERT' - pass in a primary key, and have it figure out which records to fastload, and which ones need a good old update.
Does anyone know if MSFT is planning on adding functionality to the sql client classes to enable this functionality? Any pointers or ideas much appreciated.
James;)
|
|
|
|
|
Are you using the SQL Client provider to access the database, or OleDb provider ?.
The SQL Client provider is faster than OleDb, if you are using OleDb, give it a shot to SQLClient, the interfaces are almost the same so it shouldn't be hard.
Andres Manggini.
Buenos Aires - Argentina.
|
|
|
|
|
Im using SQLclient, but bulk load interfaces are not exposed. Need something that will let me at the bulk load interfaces.
|
|
|
|
|
You might be able to use SQL Servers data transform service to do this.
I have a case where I have to upload a log of the days activity into SQL2000 each night and it is really really fast using DTS. It also allows you the option to transform the data on the fly as it is added.
If your 'massage' of the data can be done of a per-row basis then it might be a good solution for you.
Stephen
|
|
|
|
|
Hi,
What SQL statement do I have to use to get a sequential number with every row (record)? e.g. say that I have a table that has 85 records, what I want is to retrieve all the fields in every record in addition to a sequential number that represents the order of the row in the record set (not in the table). By the way, I'm using MS Access.
Thanx
(a.k.a. Wal2k)
www.wal2k.com
|
|
|
|
|
Howdy,
I'm currently undertaking to convert a c++ application which uses DAO to access an mdb file to using OLE DB to connect to MS SQL Server 2000.
The UI for part of the application is split in two, allowing the user to browse records in the top portion [in a table] whilst allowing a specific record to be edited in the bottom portion. To implement this in DAO using a single recordset was simple. The bottom view requests the data of a specific record as well as a bookmark to the record. When the record is to be saved we jump to the record identified by the bookmark, set the data appropriately and call Update(). In between getting the initial data and the save process other records may have been retrieved from the database if the user browsed around in the table view.
I am currently trying to move this functionality to OLE DB. According to the MS SQL Server documentation i have a choice of only 2 cursor types if i want read/write access. The cursor type that fits perfectly my needs is the keyset, as it allows bookmarking. The other type (dynamic) doesnt allow bookmarking. I am setting the properties of the command object in order to obtain this keyset cursor. This all seems to work fine when i view a table which has a Primary Key. However, if i try view a table which does not have a primary key, i receive an error when i attempt to Execute() the command.
This seems logical because there are no fields from which the keyset can be created, so it fails. 
How do i get around this? Am i missing something?
I am annoyed because it was so simple and smooth with DAO, it somehow allowed me to obtain bookmarks even if there is no key information in the result set. So how can i achieve the same thing using OLE DB and SQL Server?
Any suggestions at all would be great! I am about at my wits end! 
Thanks!
|
|
|
|
|
This is the code of an XML Web Service that Query an SQL Server.
It takes two strings and returns a DataSet.
It's a simple excercise, It query the database, populate a DataSet, modify a Field and Update the changes.
[WebMethod]
public DataSet GetUserData(string szUsername, string szNewLocalName)
{
string szSqlCommand = "SELECT * FROM Locals WHERE Username = '" + szUsername + "'";
SqlConnection myConnection = new SqlConnection(" User ID=sa;Password=password;Initial Catalog=laphijia;Data Source=(local)");
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(szSqlCommand, myConnection);
DataSet myDataSet = new DataSet();
mySqlDataAdapter.UpdateCommand = new SqlCommand("UPDATE Locals SET LocalName = @LocalName" +
"WHERE Username = @Username", myConnection);
mySqlDataAdapter.UpdateCommand.Parameters.Add("@LocalName", SqlDbType.NVarChar, 25, "LocalName");
SqlParameter workParam = mySqlDataAdapter.UpdateCommand.Parameters.Add("@Username", SqlDbType.NVarChar, 25);
workParam.SourceColumn = "Username";
workParam.SourceVersion = DataRowVersion.Original;
mySqlDataAdapter.Fill(myDataSet, "Locals");
myDataSet.Tables["Locals"].Rows[0]["LocalName"]= szNewLocalName;
mySqlDataAdapter.Update(myDataSet, "Locals");
return myDataSet;
}
I made this with help from the MSDN NET Framework Developer's Guide article Updating the Database with a DataAdapter and the DataSet.
When I try to access the Web Service and Invokes it, I get a 505 Internal Server Error.
I tried to remove the line:
mySqlDataAdapter.Update(myDataSet, "Locals");
and it works (it returns the DataSet as XML data, but obviously don't update the DataBase.
What's wrong with my code?
I also tried with the SQL Command Builder, but it's just the same.
Thank You.
"Nelle cose del mondo non e' il sapere ma il volere che puo'."
|
|
|
|
|
Hi guys,
I am running .net on xp pro. When i right click on server explorer and try o create a new SQL database, it asks me for a Db name and log on(NT AUth or SQL Security). I choose Nt authentication and I get DBNETLIB(cannot open connection. SQL server not found or access denied) even if I try SQL authentication I get the same error. I am loged on as administrator.
Can anyone help me 
TIA
Fred
|
|
|
|
|
May be you are logged as an administrator on your workstation, but not on the domain or on the machine SQL server is istalled.
Best regards,
Alexandru Savescu
|
|
|
|
|
bingo1995 wrote:
and I get DBNETLIB(cannot open connection. SQL server not found or access denied) even if I try SQL authentication I get the same error.
If you are using an evaluation version that has been installed for over 4 months(120 days) you will see this same error, could this be it?
If so you can uninstall and reinstall MS SQL Server to get another 120 days out of it. 
Nick Parker
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
