|
you can use
1> DTS Export/Import utility for that please go thru
http://support.microsoft.com/kb/319951[^]
2>exec master..xp_cmdshell but for that you need to enable from surface area configuration
3> BCP utility
4> you can use linked server for excel file to sql table.
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\servername\e$\inetpub\wwwroot\game\myexcelfile.xls;',
'SELECT * FROM [scores]') select * from LinkedServerName.DBname.tbl_Stats
5> SSIS package to transfer data from a table in a SQL Server 2005 database to a Microsoft Excel spreadsheet
Reasons are not Important but Results are Important.
Swati Tripathi
|
|
|
|
|
hi every body!
I use Oracle and for connection management I use OLEDB, the problem is where I want to do Update. the Update command is generated by an OleDBDataAdapter as the code below:
Public Sub UpdateData(ByVal oiDataSet As DataSet, ByVal siSQL As String, Optional ByVal siTableName As String = Nothing)
Dim oDataAdapter As New OleDbDataAdapter
Try
If mbPiDisposed = True Then
Throw New ObjectDisposedException(msPiModuleName, "This object has already been disposed. You cannot reuse it.")
End If
oDataAdapter.SelectCommand = New OleDbCommand(siSQL, mcnPiConnection, mtrPiTransaction)
Dim oCommandBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(oDataAdapter)
If siTableName = Nothing Then
oDataAdapter.Update(oiDataSet)
Else
oDataAdapter.Update(oiDataSet, siTableName)
End If
Catch ex As Exception
Throw New Exception(msPiExceptionMessage, ex)
Finally
End Try
End Sub
when I call this Sub (I pass a simple "SELECT * FROM TABLE_NAME" and Updated data as "oiDataSet" to the OleDBDataAdapter as shown above) this error is shown: "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"
I searched the Internet But every one said that you may have not define a primary key, but my table has one!!
I should say the INSERT commands are OK, the problem is with UPDATE and DELETE commands.
every suggestion would be appreciated
|
|
|
|
|
At a guess I would say that your select statement does not include the primary key for the table.
As a general recommendation I suggest you learn about Data Access Layer (DAL) and not rely on the adapter auto generation tools, you will be a better developer faster if you understand the tools you are using.
|
|
|
|
|
thank you for reply,
yes I have not the Where Clause in my statement. if it was my choice, yes! as you said, it's the worst way to let the tools do the job!! but this code was a part of an old, big and critical project which my boss prefers not to change the code unless it's necessary!!! so I want to be sure there is not any other way except using "Where" and pass primary keys!! so I can content the boss to change the code!! 
by the way thanks for your kind recommendation,
ANOTHER HELP EQUEST: do you know any other way??;) (my boss is so hard!)
|
|
|
|
|
Sorry, I would look at adding the primary key to the select statement as the minimal intervention required.
Alternatively I would look if there is a combination of existing fields that could be used as a primary key and write my own update method.
How can it be "old and critical" if your update does not work?
|
|
|
|
|
thanks,
about your question: we are moving the data base from SQL Server to Oracle. every thing goes OK by SQL, but this problem is by Oracle 
|
|
|
|
|
Ok then it is the different databases, you may have not set a primary key on the table to be updated in Oracle
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I found where is the problem.
after all discussions, about how can sombody send an Update command to data base!!! the key is here:
Every thing was ok in code and table difinitions. just in connection string a property should be defined: "OLEDB.NET=True"
was easy?? 
|
|
|
|
|
That's a perfect answer!!!!How did you know that? hard work for me 
|
|
|
|
|
I have a large database with a subset of five tables described here:
http://www.senselessdestruction.com/AssociationProblem.jpg[^]
Using the Entity Framework, when creating an entity model based on those tables I get the error:
Foreign key constraint 'FK_UserContractCompanies_TimesheetContractEntries' has been omitted from the storage model. Column 'CompanyId' of table 'SMSModel.Store.UserContractCompanies' is a foreign key participating in multiple relationships. A one-to-one Entity Model will not validate since data inconsistency is possible.
The intended business logic is to have a subset of users as contract users. A contract user arrives at the workplace, can sign in (an entry is made in the TimesheetEntries table), work for X hours and then sign out. For any user that is a contract user, the TimesheetContractEntries table exists to record their times broken into two records. The first record would be start and end times showing the first 8.5 hours of work, without a related ContractCompanyId, and the second record would be the remaining time with a related ContractCompanyId.
I'm using VS 2010 Beta 2, and have tried including foreign key columns in the model. Is the schema of my database flawed or is there anyway to overcome this limitation in the EF?
-Mike.
|
|
|
|
|
DBCC CHECKDB 'TEST"
I just cancelled this queury to run my DB full backup .
this qeuery rolling back more than 7hrs .
how do i stop thi roll back and get my full backup .
this qeury was running from SQL server agent .
|
|
|
|
|
As far as I know you can't, stopping the database may leave it in an unrecoverable state. We have had dev databases in this state and had to recover from backups.
|
|
|
|
|
I have to store hindi,chinese ... etc language words in my db using oracle 10g ... how can i do that..
can any one help me please..??
or atleast .. how can i do the language conversion from HINDI to english ... any sourcecode?
so that i can store english strings directly....
plzz help
regards,
Vineeth Koganti
|
|
|
|
|
n3ophyt3 wrote: I have to store hindi,chinese ... etc language words in my db
What is the problem? A string of characters has no meaning to the database, it is just characters, so you can store any language you like. It is only when you extract those characters from the database and display them that they have any meaning, e.g. a message read by a human operator.
|
|
|
|
|
Hi,
You can create a Unicode database that enables you to store UTF-8 encoded characters as SQL CHAR datatypes (CHAR, VARCHAR2, CLOB, and LONG).
For more info. please look into
http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch6unicode.htm[^]
n3ophyt3 wrote: how can i do the language conversion from HINDI to english ... any sourcecode?
Some pseudo code
function OtherLanguage2English(value,offset)
{
var ctrlValue=value;
var english="";
for (var idx=0; idx<ctrlValue.length; idx++)
{
individualChar = ctrlValue.substring(idx,idx+1);
var code =individualChar.charCodeAt(0);
english=english+ String.fromCharCode(code-offset);
switch(offset)
{
case 2358:document.getElementById('txtEnglish').value=english;break;
}
}
}
Though it is in javascript but this function also converts Hindi to English.. So I thought that you may get some idea.
Sorry for not getting an appropriate code for your help. I will update as and when I will get the code for oracle for serving the same purpose.
Niladri Biswas
|
|
|
|
|
Gentleman:
I recently put MSSQL Server 2005 on my server running Windows 2003...it went off perfectly. I decided to replace the MSSQL 2000 that I was running on my desktop (Win XP) as well. Unfortunately, although it said that there were no errors, the Server Management Studio does not appear in the program files (or anyplace else) that I can find. The only links that appear are for the Server Configuration and the Surface Area Configuration. I tried to do a new install (on top), but the application said that all components were already installed. Before I remove the installed app and start all over, I want to know if I am missing something? Somewhere? All assistance is greatly appreciated. Pat
|
|
|
|
|
Probably you installed from a package that had only server components and no client tools. See if you can get a package that can install Management Studio.
|
|
|
|
|
Shameel,
Thank you for taking the time to respond. My install was a full Enterprise edition. I did figure out the problem and wish to share it with you (and others) because it is especially important to anyone that has installed a version of SQL 2005 Express or MS Visual Studio 2008, which automatically installs the express version. When installing over a previously installed version, any new install must be done from a DOS prompt using an update command on the command line. If this is not done, it will see the previous version as being successfully installed and will skip the install of the Database server. I have done this and it is now working perfectly. Of course, it took me the better part of a day to figure it out. Hopefully, this might save someone else that trouble. Thank you again and happy coding....Pat
|
|
|
|
|
Sir,
In my ASP.NET application I have the dashboard.
Number of records needs to be fetched from database into it. Hence,I want to write stored procedure which gives me these records using multiple Select statements. Is it possible to fetch it? If yes how it can be done.
Thanx
|
|
|
|
|
Try googling for "ado.net multiple result sets".
|
|
|
|
|
If you are trying to get multiple records from the same table them multiple selects are not required.
If you have multiple records sets coming from different table then you will need multiple selects.
WARNING multiple recordset returned from the same can destroy your system, it can be dramatically slower to get multiple recordsets.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi, coming to ur question
deepseeindeepsy wrote: Is it possible to fetch it?
Ans : Yes
I am giving a small example
Step 1: Create the Stored Proc
Alter Procedure GetMultipleRecords
As
Begin
-- First select
Select * from test1
-- Second select
Select * from test2
End
So this will give u two record sets
Step 2: Use Data Adapter & DataSet
From Asp.net application, by using DataAdapter get the records set into the DataSet
Step 3: Use respective datatable(s) from DataSet
Get the record sets from the DataSet into DataTable(some pseudo code in c#)
e.g.
DataSet ds = getDataRecords();->You will get the records from database
if(ds!=null && ds.tables.count > 0)
{
DataTable firstDt = ds.tables[0];
DataTable secondDt = ds.tables[1];
}
Hope you get the idea.
Now try by urself and I am sure you will reach the point.
Have a nice day.
Niladri Biswas
|
|
|
|
|
Hello Friends,
I've a table as shown below
ID RefID
1 0
2 1
3 2
4 1
5 1
and i want to display it's output as shown below but i'm not getting the way
please suggest a way
ID RefID Total
1 0 3
2 1 1
3 2 0
4 1 0
5 1 0
|
|
|
|
|
This is one solution if the table is small, it requires a seperate sub select for each record
Select A.ID, A.RefID, (Select count(*) from Table where RefID = ID) Total
From Table
For a more efficient method on a large table I would use a LEFT join to itself on TableA.ID = TableB.RefID then use Isnull, a case statement and count to get the same result from a large table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Try this
declare @tbl table(id int identity, refid int)
insert into @tbl
select 0 union all select 1 union all
select 2 union all select 1 union all
select 1
select t.id,t.refid,case when x.cnt is null then 0 else x.cnt end as total
from @tbl t
left join
(
select refid,count(refid) as cnt
from @tbl
where refid <> 0
group by refid
having (count(refid)>0)) x
on t.id = x.refid
Output :
id refid total
1 0 3
2 1 1
3 2 0
4 1 0
5 1 0
Niladri Biswas
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
