|
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
|
|
|
|
|
Hello,
I am planning to migrate from MSFlexGrid to MsHflexGrid. In MSFlexGrid, when the .col /.row property is changed programmatically, the rowcolchange event gets fired. But in MsHflexGrid the same doesn’t work. However, on manually changing the .col/.row property through mouse click or using navigation keys, the event gets fired in MSHFlexGrid
Please help.
|
|
|
|
|
I think you may be better off asking this question in QA[^]. As this seems to be more about changing your grid control than actual database systems.
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
You are going to have a lot of problems getting support for vb6 controls, it is a dead language.
You would be better served by scrapping the entire application and rewriting in a current language where you can get support.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
I am using Microsoft SQL server 2008. I have 2 SQL tables Table 'Product1' and Table 'Product2'. In 'Product1' Table I have columns named
1.SLNO,
2.Name,
3.Place
4.State
And 'Product2' have columns named,
1.SLNO,
2.Name,
3.Place
4.State
5.regno
here 'regno' if FK to 'SLNO' (of product1 table ).
My requirement is to update the columns in 'Product2' table when I modify data in 'Product1' table ( 'name' 'Place' and 'state' column).
Is it possible to update 'Product2' table without running individual queries for each rows ?
If yes how to do this ?
Please help.
|
|
|
|
|
Since the columns in Product2 always have to match the columns in Product1 , wouldn't it make more sense to remove them from the Product2 table? Otherwise, you're just duplicating data for no reason.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
yes you are right. But for time being I cannot do that. However I got a one method which partially works. Here it goes..
So I have added 'Unique constraint' for 3 columns (Name, Place and State) in table1 table using,
ALTER TABLE table1 ADD
UNIQUE(column)
and created foreign key in table2 table using,
ALTER TABLE table2 ADD
FOREIGN KEY (column) REFERENCES table1(column);
While adding 'ON DELETE CASCADE' to table2 I am getting error, that
"There are no primary or candidate keys in the referenced table"
Is it possible to set ''ON DELETE CASCADE" without adding primary key in the parent table ?
Also how to add "ON DELETE CASCADE" for some foreign keys ? In my case I have 3 foreign keys ( Name, Place and State). But I want to add "ON DELETE CASCADE" only for Place and State.
Please help.
|
|
|
|
|
srikrishnathanthri wrote: Is it possible to set ''ON DELETE CASCADE" without adding primary key in the parent table ?
A foreign key must reference either a primary key, or a column with a unique constraint. To set up a foreign key between the tables, you'll need a unique constraint on the SLNO column in table1.
srikrishnathanthri wrote: But I want to add "ON DELETE CASCADE" only for Place and State.
I don't think you've understood what ON DELETE CASCADE means. When you delete the row from table1, any rows in table2 with the same SLNO will be deleted as well. It doesn't do anything when you change a value in the referenced row, and it doesn't delete the value from specific columns.
Cascading Referential Integrity Constraints[^]
You could do this with a trigger[^] on table1, but it's a pretty nasty workaround, which can lead to hard-to-trace problems. As it's only two tables, it would be simpler to update both at the same time.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|