|
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
|
|
|
|
|
|
realfly wrote:
Re: how to set a database only permit seraral IP can connet with odbc or oledb?
If you want to limit your applications connection to select IP addresses you should really do this from the application itself and not the database. What are you working with?
Nick Parker
|
|
|
|
|
Hi!Tranks from you reply.
I'm working on an program which are manager some info in a database. And the database are at a unix machine.For security,I can not let anyone who known's the password can modify the database.So I prefer to make mts middle layer at a winnt machine,And let all client visit it to operate the database.
Then limit the database can only be visit by me.I think should by the IP. Is it right?
Thank you!
i am realfly8)
|
|
|
|
|
There are plenty of ways to limit access, especially through UNIX, however if you are serious about only allowing access by IP I would only suggest this if you have a static IP address that will never change, DHCP would be a big no no.
HTH
Nick Parker
|
|
|
|
|
I have a stored procedure that executes too slowly (as I mention in a previous posting) so I'm thinking of executing the query asynchronously and grabbing the results as they come back. it may give the impression that the query is faster than it really is.
my question is... the stored procedure is sorting the results. So if I grab the first few items from the recordset doing something simple like "MoveNext" and then the queries inserts items new items before the cursor because of sorting, I've missed those items.
I am just beginning to dig into asynchronous querys so this may be a really stupid question, in fact I'm sure it is, but I've yet to see how to deal with sorting.
Any suggestions?
|
|
|
|
|
Once SQL starts the good ol' TDS stream, I'd presume that the records are correct (i.e. SQL won't return rows to you that aren't in the right order).
Is it not possible to break up the stored proc. into smaller units?
What kind of app is this for?
Cheers,
Simon
"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)
|
|
|
|
|
I have been pawing all over the ADO.Net references and still can't see an obvious way to update a BLOB field.
Any clues?
TIA
|
|
|
|
|
Look at ".Net Data Access Architecture Guide" on MSDN:
Writing BLOB Data to the Database
The following code shows how to use ADO.NET to write binary data obtained from a file to an image field in SQL Server.
public void StorePicture( string filename ) {
FileStream fs = new FileStream( filename, FileMode.Open, FileAccess.Read );
byte[] imageData = new Byte[fs.Length];
fs.Read( imageData, 0, (int)fs.Length );
fs.Close();
SqlConnection conn = new SqlConnection("Connection String");
SqlCommand cmd = new SqlCommand("StorePicture", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@filename", filename );
cmd.Parameters["@filename"].Direction = ParameterDirection.Input;
cmd.Parameters.Add("@blobdata", SqlDbType.Image);
cmd.Parameters["@blobdata"].Direction = ParameterDirection.Input;
cmd.Parameters["@blobdata"].Value = imageData;
try {
conn.Open();
cmd.ExecuteNonQuery();
}
catch {
throw;
}
finally {
conn.Close();
}
}
|
|
|
|
|
Hi,
I have on MS-SQL server table without any key. When I use query analyzer I sould select from this table. But when I try the same sql command in my visual c++ application with oledb than it returns error on CCommand::Open. Only creating primary key helps.
Should I any way open the table without any key?
Thanks, Alex.
|
|
|
|
|
Howdy,
I'm afraid im not going to be much help, but i believe i may be experiencing the same problem.
I have posted a question which may interest you to read: click
If you make any progress I would appreciate it if you'd let me know the secrets
Cheers,
Alex
|
|
|
|
|
I used this command to create SQL Server login:
EXEC sp_addlogin 'Mazdak','password'
When I want to login to query analyzer I got this error:
---------------------------
SQL Query Analyzer
---------------------------
Unable to connect to server MAZY:
Server: Msg 18452, Level 16, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'Mazdak'. Reason: Not associated with a trusted SQL Server connection.
---------------------------
OK
---------------------------
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
|
|
|
|
|
sp_AddLogin creates a SQL Server login. You are trying to connect with a Windows login.- To connect using SQL Server authentication, select the "SQL Server authentication" option and enter the username and password;
- To add your NT account to SQL Server, use
sp_GrantLogin , e.g. EXEC sp_GrantLogin 'MAZY\Mazdak'
|
|
|
|
|
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
|
|
|
|
|
hey im trying to make a database app with the MFC wizard in c++ 6, when i add a Datagrid control, there are two columns by default, how can i add more columns to it? there is no option to do this in the properties. Can anyone tell me how to add more?
|
|
|
|
|
Sorry for cross-post... realized this is the more appropriate group after I sent the first one:
I've created a strongly typed dataset but whenever I try to select rows from
a table and try to cast the rows, I'm getting an error "Specified cast is
not valid.".
I have a table named "Inventory". I'm able to select rows and place them in
a variable with type DataRow[]. However, when I try to cast it to a
InventoryRow, I get errors every time.
Here's the code: my dataset name is PADS...
working code:
DataRow[] recordArray = this._dataSet.Inventory.Select();
error code:
<br />
PADS.InventoryRow[] recordArray =<br />
(PADS.InventoryRow[])this._dataSet.Inventory.Select();<br />
I'm certain I'm using the correct variable names because I'm able to easily
create new records with the following:
<br />
PADS.InventoryRow record = this._dataSet.Inventory.NewInventoryRow();<br />
this._dataSet.Inventory.AddInventoryRow(record);<br />
Any input would be greatly appreciated!
Andrew Connell, MCDBA
IM on MSN
andrew@aconnell.com
Andrew Connell, MCDBA
IM on MSN
andrew@aconnell.com
|
|
|
|
|
does InventoryRow directly inherit from DataRow? if not there might be problems casting it
|
|
|
|
|
I'm having some trouble creating an index with ADOX (against MSJet as the provider).
For the life of me, I can't figure out how to make the index *unique*. Sure, there is a pIndex->PutUnique() function on the Index object, but it doesn't seem to work, since the resulting database still lists the field as "duplicates OK" when I open it in Access.
I've also tried 'pIndex->Properties("Unique")->PutValue(true)', but that only results in an exception being thrown (with some unhelpful error message). But PutValue(false) doesn't result in an error. Am I missing some prerequisites for a unique index?
If I change the index in Access to "no duplicates" and reopen the database, both the pIndex->GetUnique() and Properties("Unique")->GetValue() return true.
Any help is much appreciated.
|
|
|
|