|
When you take a backup of your disk, you can't just take a copy of the SQL database files. If you did, you could end up with an unusable backup.
Instead, the backup product uses the Volume Shadow Copy Service to communicate with SQL Server. SQL then initiates a proper backup of the databases, writing them out to a virtual device that represents the target of the backup job.
This is perfectly normal, and nothing to worry about.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi,
I am trying to creat a simple IF - THEN in MySQL but getting a synax error message.
can anyone help please...
here is my statement:
IF EXISTS (SELECT payroll_id FROM payroll WHERE location_id = param_location AND payroll_month = param_month AND payroll_year = param_year) THEN
SET param_payroll_id = 0;
END IF;
Thanks,
Jassim[^]
Technology News @ www.JassimRahma.com
|
|
|
|
|
Never used MySql but based on my search, you can't use that syntax in Mysql. Alternate way is, just store the result of query into some variable & use it for comparison to achieve the output. Check this sample
MySQL Stored Procedures IF NOT EXISTS()[^]
BTW, always include the complete error message(just 'Syntax error' is not enough) in your question which is better for members to give you answers quickly.
|
|
|
|
|
I tried below code but getting same error for the IF--THEN
SET param_payroll_exists = (SELECT COUNT(*) FROM payroll WHERE location_id = param_location AND payroll_month = param_month AND payroll_year = param_year);
IF EXISTS (param_payroll_exists > 0) THEN
SET param_payroll_id = 0;
END IF;
the exact error is:
You have an error in your SQL syntax. Check the manual that corresponds to you MySQL server for the right syntax to use near 'param_payroll_exists > 0) THEN ..........
Technology News @ www.JassimRahma.com
|
|
|
|
|
Jassim Rahma wrote: IF EXISTS (param_payroll_exists > 0) THEN
Do you really mean EXISTS here?
Software rusts. Simon Stephenson, ca 1994. So does this signature. me, 2012
|
|
|
|
|
You didn't get Peter's reply which's fair enough? Just remove EXISTS from that query & run. It's over.
|
|
|
|
|
Hello,
I am using SQL server 2008 r2. I am new to SQL triggers and trying to call dll from SQL triggers. I have created dll using vb.net,
Public Class Class1
Public Function abc(ByVal a As String) As String
Return a.ToUpper
End Function
End Class
I have successfully added assembly to the SQL using,
CREATE ASSEMBLY triggertest
FROM 'D:\dll\triggerdll.dll'
WITH PERMISSION_SET = SAFE
and then added a trigger using,
CREATE TRIGGER tri_add_clr
ON dbo.foriegn
FOR INSERT
AS
EXTERNAL NAME triggertest.Class1.abc
Here I am getting error,
Msg 6505, Level 16, State 2, Procedure tri_add_clr, Line 1
Could not find Type 'Class1' in assembly 'triggerdll'.
where triggerdll is the name of dll.
How to solve this error ?
Am I calling dll from SQL trigger properly ?
Please help.
modified 1-Jun-16 9:55am.
|
|
|
|
|
VB.NET projects have a root namespace[^]. Unless you've cleared that in your project's properties, your class exists within that namespace, and you need to include it within the EXTERNAL NAME specification:
CREATE TRIGGER tri_add_clr
ON dbo.foriegn
FOR INSERT
AS
EXTERNAL NAME triggertest.[YourNamespace.Class1].abc
Also:
srikrishnathanthri wrote: CREATE ASSEMBLY triggertest
FROM 'D:\dll\Interop.ADODB.dll'
WITH PERMISSION_SET = SAFE
Are you sure that's the correct assembly? That looks like an interop assembly for the ancient "ADODB" library to me.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Tried your solution,
Richard Deeming wrote: CREATE TRIGGER tri_add_clr
ON dbo.foriegn
FOR INSERT
AS
EXTERNAL NAME triggertest.[YourNamespace.Class1].abc
but I am again getting error,
Msg 6573, Level 16, State 1, Procedure tri_add_clr, Line 1
Method, property or field 'abc' of class 'ClassLibrary1.Class1' in assembly 'triggerdll' is not static.
Sorry there is a mistake in question about dll name, I have edited that.
|
|
|
|
|
|
Hello everybody!
I have three tables, namely t1, t2, t3
Dim cmdText As String
cmdText = "SELECT t1.StaffName, t2.CampusName
FROM t1
INNER JOIN t2
ON t1.CampusID = t2.CampusID
ORDER BY t1.StaffName"
It is working fine.
My problems starts when I want to include t3; such that;
t1.StaffID = t3.StaffID
I don't know; how to get it done. Please note that; StaffID is the Primary Key of t1 and the Secondary Key of t3.
Would anyone help me, please!
|
|
|
|
|
Could you describe the structure of those three tables? We'd usually join between a primary and a foreign key, not a secondary one.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Are you just looking for the syntax to join another table?
SELECT
...
FROM
t1
INNER JOIN t2
ON t2.CampusID = t1.CampusID
INNER JOIN t3
ON t3.StaffID = t1.StaffID
ORDER BY
...
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you very much, Richard Deeming, for the help.
Have a very good day.
|
|
|
|
|
Hello,
I have a SQL table with some existing data. In that I have a columns named "Name" and SL No (which is a primary key). For example say I have one entry for that column as "Thilak". Suppose if a next entry comes with same name i.e "Thilak", I want to rename the existing "Thilak" as "Thilak_Slno" and new entry as "Thilak_Slno". And If again any Thilak repeats I want append serial number to that before saving or soon after saving to the database.
The purpose is simple, I am giving option to post a query in my software based on username. while selecting the name, if same name exists in entry that will be difficult to select the Name, if name comes with serial number it will be easier.
So whenever a new entry comes which is same as the existing name, I am planning to append the serial number to the existing names also to the incoming name.
So, now whether it is possible to change the name as it enters into the table ? or should I change once it get saved to database ?
How to do this ?
Please help me.
|
|
|
|
|
If you want all names to be unique, then just add the serial number in the first place. Then you do not need the extra steps of searching for an existing name and changing it when adding to your database. Alternatively just accept that there may be duplicates which you can process when searching.
|
|
|
|
|
You shouldn't change it at all. They are two separate facts, and should each be in their separate field. That way you don't need to "substring" on the name when the numbering changes, and can easily add another field if your combination is no longer unique.
The database is not there to store "formatted" values, but data. Upon retrievel, you paste to two fields together and have what you want.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hello,
I am using SQL express 2008 r2. Some times while running SQL backup query,
Text
BACKUP DATABASE [DBName] TO disk = 'TargetPath' WITH CHECKSUM
I am getting the error 'detected an error on page'. But if I run query without checksum then it runs successfully.
My question is,
1. Why that error is coming ?
2. What should I do if that error comes ?
3. Can I rely on the backup without checksum ?
Please help me.
|
|
|
|
|
Sounds like you might have some page-level corruption in your database.
Try running:
DBCC CHECKDB (N'Your-Database-Name') WITH NO_INFOMSGS, ALL_ERRORMSGS;
If you still need help, you'll need to post the full error message.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
This is the error I am getting,
Msg 3043, Level 16, State 1, Line 1
BACKUP 'broken' detected an error on page (1:143) in file 'c:\sql\mydb.mdf'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
|
|
|
|
|
That error message means your database is corrupt. Try running DBCC CHECKDB, as I suggested.
You'll probably need to restore your database from the most recent valid backup.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Brent Ozar recently published a great blog-post covering the details of what you need to do:
What to Do When DBCC CHECKDB Reports Corruption[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Good Day All
i have been using the below declared Cursor from the Stored-procedure for around 3 years now. The last couple of weeks i have been getting the error
Incorrect syntax near the keyword 'CROSS'.
I dont have a Cross Join in this Cursor and this started after Infrastructures upgraded the hardware on the SQL Server , i noticed other settings were lost but was able to recover them.
USE [DBPS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[DATABASE_MAINTANANCE_PLAN]
AS
DECLARE @DatabaseName SYSNAME = DB_NAME(), @TableName VARCHAR(256)
DECLARE @FILLFACTOR INT = 85
DECLARE @DynamicSQL NVARCHAR(max) =
'DECLARE curAllTablesInDB CURSOR FOR SELECT TABLE_SCHEMA +
''.'' + TABLE_NAME AS TABLENAME
FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLES WHERE
TABLE_TYPE = ''BASE TABLE'''
BEGIN
EXEC sp_executeSQL @DynamicSQL
OPEN curAllTablesInDB
FETCH NEXT FROM curAllTablesInDB INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @DynamicSQL = 'ALTER INDEX ALL ON ' + @TableName +
' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR,@FILLFACTOR) + ')'
PRINT @DynamicSQL
EXEC sp_executeSQL @DynamicSQL
FETCH NEXT FROM curAllTablesInDB INTO @TableName
END
CLOSE curAllTablesInDB
DEALLOCATE curAllTablesInDB
END
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vimalsoft.com
vuyiswa[at]vimalsoft.com
|
|
|
|
|
Thanks
I had a table named "CROSS" Cross is a keyword in SQL , that caused a problem for that Dynamic Query. Thanks its resolved now.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vimalsoft.com
vuyiswa[at]vimalsoft.com
|
|
|
|
|
Rather than using dynamic SQL and a cursor, why not use the undocumented sp_MSforeachtable procedure[^]?
ALTER PROC [dbo].[DATABASE_MAINTANANCE_PLAN]
AS
BEGIN
DECLARE @command nvarchar(max);
DECLARE @FillFactor int = 85;
SET @command = N'ALTER INDEX ALL ON [?] REBUILD WITH (FILLFACTOR = ' + Convert(nvarchar(3), @FillFactor) + N')';
EXEC sp_MSforeachtable @command;
END
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
