Database

Re: Avoid using cursors inside Store procedure - why?

11-Aug-08 8:52

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

MarkWagner811-Aug-08 9:24

MarkWagner8

11-Aug-08 9:24

Thanks Bob for the quick answer.

Re: Avoid using cursors inside Store procedure - why?

Manas Bhardwaj11-Aug-08 10:54

Manas Bhardwaj

11-Aug-08 10:54

Perfect and precise answer Rose | [Rose]

upload image in data base..

amit201111-Aug-08 5:31

amit2011

11-Aug-08 5:31

i am usind sql server 2000.
pl can anyone tell me how to upload image in the database and also retrive the same.

thanx..

Re: upload image in data base..

Blue_Boy11-Aug-08 5:36

Blue_Boy

11-Aug-08 5:36

Check this google result.[^]

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

Re: upload image in data base..

Manas Bhardwaj11-Aug-08 5:38

Manas Bhardwaj

11-Aug-08 5:38

http://support.microsoft.com/kb/309158[^]

Backup failed on VISTA but successfully run on XP

sunil goyalG11-Aug-08 4:28

sunil goyalG

11-Aug-08 4:28

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

Re: Backup failed on VISTA but successfully run on XP

SimulationofSai11-Aug-08 5:08

SimulationofSai

11-Aug-08 5:08

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.

Re: Backup failed on VISTA but successfully run on XP [modified]

sunil goyalG11-Aug-08 21:13

sunil goyalG

11-Aug-08 21:13

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

Convert to hours

Agweet11-Aug-08 2:39

Agweet

11-Aug-08 2:39

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

Colin Angus Mackay11-Aug-08 2:49

Colin Angus Mackay

11-Aug-08 2:49

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.

Recent blog posts:
*SQL Server / Visual Studio install order
*Installing SQL Server 2005 on Vista
*Crazy Extension Methods Redux
* Mixins

My Blog

Manas Bhardwaj11-Aug-08 4:56

Manas Bhardwaj

11-Aug-08 4:56

Colin Angus Mackay wrote:
I'm sure you can figure out the rest of the maths

You only explained the maths Wink | ;)

...I believe he was looking for some cooked code Smile | :)

int LiSeconds = 86400000;
float LfHours = (LiSeconds / 60) / 60;

Ashfield11-Aug-08 5:08

11-Aug-08 5:08

..and it was such a simple question that didn't really need asking.. Laugh | :laugh:

Bob
Ashfield Consultants Ltd

PIEBALDconsult14-Aug-08 4:08

PIEBALDconsult

14-Aug-08 4:08

We prefer not to spoon-feed people here.

How to wirte this query

varshavmane11-Aug-08 1:12

varshavmane

11-Aug-08 1:12

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

Ashfield11-Aug-08 1:26

11-Aug-08 1:26

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

varshavmane11-Aug-08 1:46

varshavmane

11-Aug-08 1:46

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...

Ashfield11-Aug-08 3:09

11-Aug-08 3:09

Ok, post the table definitions and your query and I'll try to help further.

Bob
Ashfield Consultants Ltd

varshavmane11-Aug-08 3:26

varshavmane

11-Aug-08 3:26

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... Smile | :)

Ashfield11-Aug-08 4:39

11-Aug-08 4:39

Good, thats what I meant Smile | :)

Bob
Ashfield Consultants Ltd