|
Hi,
I guess you are connecting to named instance. Check:
- connection string, especially check server name and instance name. (also keep in mind that in c# \ is an escape sequence unless you have @-character before string: "\\\\server\\instance" == @"\\server\instance" )
- check that SQL Browser process is running on server
This error can also occur simply because your network doesn't allow communication to the server for example because of a firewall.
Hope this helps,
Mika
|
|
|
|
|
Hi,
I'm trying a stored procedure(in SQL server 2000) which retrieves a table of information that contains text fields. But I couldn't reach as I'm getting struck with an error being thrown as "Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT function to run this query." Could some one assist me in sorting this out.
The error is in the following line
DECLARE @Olddescription nvarchar(4000)
SELECT @Olddescription = description from @ProblemsTable where rownum = 1
Notes:
@ProblemsTable is a local table being created with the following line of codes
Declare @ProblemsTable table
(
rownum int IDENTITY (1, 1) Primary key NOT NULL,
description text
)
insert into @ProblemsTable SELECT description FROM problems WHERE id = @IncidentID
Thanks in advance,
Kathir
|
|
|
|
|
Obvious isn't it?
SELECT @Olddescription = CONVERT(NVARCHAR(4000), description) from @ProblemsTable where rownum = 1
|
|
|
|
|
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."
|
|
|
|