|
I was attending interview recently and was asked a question on SQL. Why should we avoid using cursors inside store procedure. I really didnt know until they asked that question. Can anyone please describe in few lines giving various reasons why we should avoid using cursors inside store procedure.
Thanks
Mark.
|
|
|
|
|
Poor performance. One of the main strengths of a database is performing set based queries/updates. A cursor makes it row based. There are times when it is unaoidable, but whenever possible avoid them
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob for the quick answer.
|
|
|
|
|
Perfect and precise answer
|
|
|
|
|
i am usind sql server 2000.
pl can anyone tell me how to upload image in the database and also retrive the same.
thanx..
|
|
|
|
|
Check this google result.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
|
hi all,
i use following procedure for backup of a database
Private Sub BackupDatabaseUsinSMO(ByVal DatabaseName As String, ByVal UserName As String, ByVal Password As String, ByVal ServerName As String, ByVal DestinationPath As String)<br />
<br />
Dim conn As ServerConnection = New ServerConnection(ServerName, UserName, Password)<br />
Dim myServer As Server = New Server(conn)<br />
<br />
Dim backup As Backup = New Backup<br />
backup.Action = BackupActionType.Database<br />
backup.Database = DatabaseName<br />
DestinationPath = System.IO.Path.Combine(DestinationPath, DatabaseName + ".bak")<br />
backup.Devices.Add(New BackupDeviceItem(DestinationPath, DeviceType.File))<br />
backup.Initialize = True<br />
backup.Checksum = True<br />
backup.ContinueAfterError = True<br />
backup.Incremental = False<br />
backup.LogTruncation = BackupTruncateLogType.Truncate<br />
backup.SqlBackup(myServer)<br />
<br />
<br />
End Sub
above code works well on XP OS but when i run this code on VISTA machine then it give error "Backup failed for Server 'Machine\Instance'." Inner Exception is {"An exception occurred while executing a Transact-SQL statement or batch."}
anybody have idea that how can i perform BackUp and Restore operation on VISTA .I use SQL Server 2005 Express edition
|
|
|
|
|
It makes no difference for SQL SMO whether it's XP or Vista. I'd hazard a guess that in the server with Vista, SQL Server does not have enough permission to write into the backup folder.
But as I said, this is just a guess.
|
|
|
|
|
i use same Server (SQL Server 2005 Express) for both OS . User is same since i use following procedure to install SQL Server,attach database and create login and User
str = Application.StartupPath & "\SQLEXPR32\setup.exe /qb ADDLOCAL=ALL INSTANCENAME=" & InstanceName & " SECURITYMODE=SQL SAPWD=sapassword DISABLENETWORKPROTOCOLS=0 "<br />
Shell(str, AppWinStyle.Hide, True)<br />
System.Threading.Thread.Sleep(5000)<br />
Dim MainServer As Server = New Server(ComputerName & "\" & InstanceName)<br />
With MainServer.ConnectionContext<br />
.LoginSecure = True<br />
.ConnectionString = "Server=" & ComputerName & "\" & InstanceName & " ;Trusted_Connection=Yes"<br />
Try<br />
.Connect()<br />
Catch ex As Exception<br />
Application.Exit()<br />
End Try<br />
If MainServer.Databases.Contains(DatabaseName) Then<br />
Else<br />
Dim logstr As String<br />
Dim datastr As String<br />
datastr = Application.StartupPath & "\V.mdf"<br />
logstr = Application.StartupPath & "\V_log.ldf"<br />
'Attach the database<br />
Dim sc As StringCollection<br />
sc = New StringCollection<br />
sc.Add(datastr)<br />
sc.Add(logstr)<br />
Try<br />
MainServer.AttachDatabase(DatabaseName, sc)<br />
Catch ex As Exception<br />
MsgBox(ex.Message.ToString)<br />
End Try<br />
End If<br />
<br />
If MainServer.Logins.Contains(LoginName) Then<br />
<br />
Else<br />
Dim NewLogin As Login = New Login(MainServer, LoginName)<br />
NewLogin.LoginType = LoginType.SqlLogin<br />
NewLogin.DefaultDatabase = DatabaseName<br />
NewLogin.Create(LoginPassword)<br />
<br />
Dim db As New Database<br />
db = MainServer.Databases(DatabaseName)<br />
Dim DBUser As User = New User(db, UserName)<br />
DBUser.UserType = UserType.SqlLogin<br />
DBUser.Login = LoginName<br />
DBUser.Create()<br />
DBUser.AddToRole("db_Owner")<br />
<br />
End If<br />
.Disconnect()<br />
End With
above code works fine on both OS (XP and VISTA)
modified on Tuesday, August 12, 2008 3:23 AM
|
|
|
|
|
hi all...this may sound dum but i need the help...how can i convert this value "86400000" from milliseconds into hours
living life on the flip side
|
|
|
|
|
There are 1000 milliseconds in an second
There are 60 seconds in a minute
There are 60 minutes in an hour
1000 * 60 * 60 = 3600000 milliseconds in an hour.
I'm sure you can figure out the rest of the maths.
|
|
|
|
|
Colin Angus Mackay wrote: I'm sure you can figure out the rest of the maths
You only explained the maths ...I believe he was looking for some cooked code
int LiSeconds = 86400000;
float LfHours = (LiSeconds / 60) / 60;
|
|
|
|
|
..and it was such a simple question that didn't really need asking..
Bob
Ashfield Consultants Ltd
|
|
|
|
|
We prefer not to spoon-feed people here.
|
|
|
|
|
Hello All,
I am using 2 databases. In one database I have table which has a column named PNumber and this column has data something like this :
7,189,413
7,196,205
7,202,331
7,217,254
In another database same column name is there but data is something like this:
7189413
7196205
7202331
7217254
I need to join these two column but I am not able to get the required data.
Please help me...
Thanks in advance....
modified on Monday, August 11, 2008 7:18 AM
|
|
|
|
|
I am assuming that the data type is not a numeric one, otherwise your join would work, so try
where replace(Pnumber,',','') = Pnumber
This will strip the commas out.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks for the reply, but its not working.
I want Expiry Date data but its not showing date. All the values in that column is Null but data is there in database...
|
|
|
|
|
Ok, post the table definitions and your query and I'll try to help further.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hey thanks a lot for the reply and help. Its working fine. I just used replace where I was doing join. Something like this:
SELECT CONVERT(nvarchar(12), b.pexpiry_date, 101) as Expiry_Date FROM Table_A a
left join Table_B b with(NoLock)
on replace(a.pno,',','') = b.pnumber
Thanks again...
|
|
|
|
|
Good, thats what I meant
Bob
Ashfield Consultants Ltd
|
|
|
|
|
DECLARE @value AS VARCHAR(255)<br />
<br />
SET @value='78,567,9'<br />
<br />
SELECT REPLACE(@value,',','')
I see you have already soultion but I hope you don't mind for my answer.
Regards.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Good Morning All
I have been Running a Stored Procedure from another Server and it Crashed,Am restoring my Application to a new Server. i have restored my DB's into that SQL Server and my SP's are there and my Application complains that the SP's were not Found. What i did i recompiled the SP's and try again from the Management Studio to run the Proc itself not from the App and it gave me the same Error. e.g here is what i did
Alter Procedure [sde].[PrcSelect_lock_Pro_View]
(
@Lis_key varchar(50)
)
as
SELECT Primary_ID,
Boundary_area,
Attrib_Code,
Lis_Key,
Func_Key,
Actual_Extent,
Improved_Value,
Site_Value,
Purchase_price,
Purchase_date,
prop_id,
confidence_score,
fin_ls_prediction,
new_lis_key,
new_func_key,
Tshwane_Prediction,
matchflag,
source,
flagtype,
Locked,
New,
Country
FROM Property_mass WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key)
AND (Locked IS NULL)
Order by Lis_key,Func_key
Update Property_Mass
set Locked = 1
WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key And Locked IS NULL)
When i run this code it execute fine , but when you run the PS and pass the Param then it says
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'PrcSelect_lock_Pro_View'
Even if i see the SP.
What could be a Problem.
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Are you specifying the correct owner ([sde].) when you run the SP?
|
|
|
|
|
Thanks it worked.
i was supposed to use the sde user.
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|