|
Hi friends
I want to execute the procedure which is as shown below
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
CREATE procedure xyz(@refno as varchar(25),@Type as varchar(15))
as set nocount on
Create Table #tbl_xyz
(
'some fileds
)
Set @Tempcode=0
Declare xyz_cursor Cursor for
Set @sql1 = 'Select a,b c and d like ''' + @labrefno + ''' order by e ' + @Type
exec sp_executesql @sql1
Open xyz_cursor
Fetch next from xyz_cursor into some values
While @@Fetch_status=0
BEGIN
'''''''''
BEGIN
END
''''''''''''''
''''''''''''''
''''''''''''''
Close xyz_cursor
Deallocate xyz_cursor
select * from #tbl_xyz
'''''''''''''''''''''''''''''''''''''''''''''
In the execution of above procedure , it won't allow me to set any value under the cursor declaration and even if execute under the cusor and i want to perform this procedure by passing the '@refno' and '@Type' dynamically as a parameter .
Is there any solution regarding this , then please guide me.
Thanks
Param
param
|
|
|
|
|
Do you have to use cursors 99% of the time you don't. What are you trying to do ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi ,
I am using the System.Data.Common.DBCommand to insert values into a table in a loop with each loop generating a SQL statement and inserting values into the table. The scenario is something like this.
foreach loop
{
DBCommand db = db.GetSqlStringCommand(loop generated string);
db.Connection = db.DbProviderFactory.CreateConnection();
db.Connection.ConnectionString = db.ConnectionStringWithoutCredentials;
db.Connection.Open();
db.ExecuteNonQuery();
db.Connection.Open();
}
the question is that should i open and close the connection everytime? if i dont open the connection in the loop and if i try something like this
DBCommand db = null;
db.Connection = db.DbProviderFactory.CreateConnection();
db.Connection.ConnectionString = db.ConnectionStringWithoutCredentials;
db.connection.open();
foreach loop
{
DBCommand db = db.GetSqlStringCommand(loop generated string);
db.Connection = db.DbProviderFactory.CreateConnection();
db.Connection.ConnectionString = db.ConnectionStringWithoutCredentials;
db.ExecuteNonQuery();
}
Then it throws an error telling that a connection does not exist. Is there anyother way to open the connection just once?
--Amrish
|
|
|
|
|
Amrish Deep wrote: Is there anyother way to open the connection just once?
Have your connection opened before the foreach, and then close it after the foreach loop is done...
I'd like to help but I don't feel like Googling it for you.
|
|
|
|
|
Hi,
It would be better if you open connection before the For loop and close when complete for loop is executed, as suggested by the other poster. But i would like to add that use try catch blocks for opening connections and executing queries. If any exception is thrown it would be handled then.
Also remember to close the connection in the finally block of the try catch statement. This is important since the finally block gets executed regardless of an error. For example, do something like this ....
declare connection here
Try
instantiate connection here
open connection
for loop starts here
statements in loop
for loop ends here
catch ex as Exception
any error handling goes here
finally
close and dispose connection here
end try
Hope i am clear enough.
"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder
coolestCoder
|
|
|
|
|
Hi, I have a problem creating a new row in my first database project, using ADO .NET.
When I remove the call to AddSummaryRow, UpdateDatabase yields no error, but when it's there, I get an System.Format Exception "Input string was not in a correct format".
Do you guys have any idea of what the problem is?
<code>
private void UpdateCurrentRow()
{
currentRow = acp2DataSet.Summary.FindBySerial_Number(serialNumber);
if(currentRow == null)
{
acp2DataSet.Summary.AddSummaryRow("None", "Unassigned", serialNumber);
UpdateDatabase();
}
}
public void UpdateDatabase()
{
acp2DataAdapter.Update(acp2DataSet.Summary);
}
</code>
|
|
|
|
|
Sounds like you have an data type error. It mihgt be that your trying to input a double, or decimal, or other number format into a string or vise versa. Make sure that your inputs are the same data type as method your inputing them into.
Example:
Private void UpdateRow(argument1 as string)
you cant input an integer as argument1 because its supposed to be a string.
|
|
|
|
|
hi
""The Microsoft Jet database engine cannot open the file '\\software-11\Access\terminaldata.mdb'. It is already opened exclusively by another user, or you need permission to view its data. 12/14/2006 12:38:50 PM
""
i m getting this error when i access mdb file from my window service,,
i have accesss to that file ,, but in my service i get this exfeption
can any body help me ,, thanks in advance
-- modified at 6:59 Thursday 14th December, 2006
hello
hello
|
|
|
|
|
ghumman63 wrote: i have accesss to that file
Does some other application have exclusive access to the file though?
|
|
|
|
|
Are you using access to access the access mdb while trying to access it with the application you are debugging. You have to close access to allow the application to access it.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
no
only my service is accessing this db ,, not any ohter application
hello
|
|
|
|
|
ghumman63 wrote: only my service is accessing this db ,, not any ohter application
Have you included Access itself as "any other application"?
|
|
|
|
|
Have you included Access itself as "any other application"?
what does that mean ? i am sorry
hello
|
|
|
|
|
Have you got "Microsoft Access" accessing the db while your application is running ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
no
"Microsoft Access" is closed while my application is running
hello
|
|
|
|
|
|
ghumman63 wrote: ""The Microsoft Jet database engine cannot open the file '\\software-11\Access\terminaldata.mdb'. It is already opened exclusively by another user, or you need permission to view its data. 12/14/2006 12:38:50 PM
""
i m getting this error when i access mdb file from my window service,,
i have accesss to that file ,, but in my service i get this exfeption
can any body help me ,, thanks in advance
There are two primary ways to open a connection to an MS Access database, first ‘shared’, second ‘exclusively’. If the database has been opened exclusively by someone else you will only be able to open a read only connection to it. Shared as implied allows for multiple connections to it.
Your post(s) seem to indicate nothing else has an open connection to the database.
What is your connection string?
Does your service have permission to the file share & *.mdb file its self?
MS Access also supports a primitive user roles schema. Have you modified the default “admin” role?
The bottom line here is the error message your seeing is the result of some type of permissions issue, be it at the file level or in the database it self.
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
i am very new to extended stoared procedure.
i want it to use to send multiple records to sp at once
can any one Explain me what is the exact excution sequance of CLRSP
is this method useful for my task
Plz Help me.
Its urgent.
|
|
|
|
|
Hello,
I have created a backup of sql 2005, but when i go to restore that backup get the following error message below.
The way I have done my backup is full backup and overwrite existing backup. Everything else is at the default. This works ok.
When I restore I select the backup and under options overwrite existing database. Everything else is set to default.
Does anyone have any ideas about this.
Many thanks in advance,
Steve
TITLE: Microsoft SQL Server Management Studio
------------------------------
Restore failed for Server 'DEV01'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.Smo)
|
|
|
|
|
I use a sql script which closes all connections to the database. Note this is in development so I don't really care about the impact to the connected applications.
print getdate()
GO
use master
declare @dbName nvarchar(1024)
SELECT @dbName = 'dbname'
declare @rowCount int
declare @spid int
SELECT @rowCount = 1
while (@rowCount > 0)
BEGIN
create table #temp
(
spid int,
ecid int,
status varchar(256),
loginname varchar(256),
hostname varchar(256),
blk int,
dbname varchar(256),
cmd varchar(256),
request_id int,
id int identity
)
insert into #temp
exec sp_who
SELECT TOP 1 * FROM #temp WHERE dbname = @dbName
SELECT top 1 @spid = spid
FROM #temp
WHERE dbname = @dbName
SELECT @rowCount = @@rowcount
if (@rowCount > 0)
exec('kill ' + @spid )
drop table #temp
END
RESTORE DATABASE [MedinaGold]
FROM DISK = N'backupFileLocation'
WITH FILE = 1,
MOVE N'data_name' TO N'c:\data\2005\cs\_Data.mdf',
MOVE N'log_name' TO N'c:\data\2005\cs\_Log.ldf',
NOUNLOAD, REPLACE, STATS = 10
GO
Just replace the database and file names with your values.
Using the GridView is like trying to explain to someone else how to move a third person's hands in order to tie your shoelaces for you.
-Chris Maunder
|
|
|
|
|
Hello Chris,
Thanks for the script and I am sure it will come in handy. However, is there a simple way using server management studio?
Many thanks,
Steve
|
|
|
|
|
I haven't found one other then going into the list of active connections and closing them out from the list and then going back and running restore. The more you use SQL Server the more you end up doing through the query window anyway.
Using the GridView is like trying to explain to someone else how to move a third person's hands in order to tie your shoelaces for you.
-Chris Maunder
|
|
|
|
|
|
Hi, I have difficulties about creating an Array of Object of type MySqlDataAdapter.
I used this syntax:
MySqlDataAdapter[] adtArrObj;
// my other codes here....
adtAddObj[0] = new MySqlDataAdapter(cmdObj);
I got error...
I've tried some other way, like:
MySqlDataAdapter[] adtArrObj = new MySqlDataAdapter()[];
error too..
any help? thx
|
|
|
|
|
MySqlDataAdapter[] adtArrObj = new MySqlDataAdapter[arraySize];
Using the GridView is like trying to explain to someone else how to move a third person's hands in order to tie your shoelaces for you.
-Chris Maunder
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
