I am importing a text file with '~' column delimiter and my text has in the file has some format.If i import the data into the sql table all the text is appearing in one line by missing the formart.
(here is the sample data.
empid~empname~emp_txt*
1~jjjj~kkkgkhfdhg
hhhghg
hoghodshgohdsoiahgfhoghojhqgh*
2~3333~gdskhkgjh
ghfdgh
fkjhbdskjhkghdghah*
)
IF i import the above data into a table with emp_txt as text data type
i am getting
kkkgkhfdhg
hhhghg
hoghodshgohdsoiahgfhoghojhqgh
all in one row like:kkkgkhfdhg hhhghg hoghodshgohdsoiahgfhoghojhqgh.But i should get it same as in the notepad.
for that i have changed the datatype of the emp_text column as image in my database.
But when i change the datatype my DTS is giving the following error:
Error at Destination for Row number 1. Errors encountered so far in this task: 1.
Insert error,Column3('emp_text',DBTYPE_BYTES),status 2: Error converting value.
Invalid character value for cast specification.In the format file also specify asterisk (*) as a row separator.
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Friday, March 30, 2012
One more Beginner Problem
I need to restore multiple tables in a sequence. I am writing separate data flow for each table restore.
If there is any error in any of the table restore data flow, I want to write that error in a log file.
I am writing a script component in every data flow, which will get error code & error description, that i am writing in the log fie.
Is there a way to create a public reusable error logging script file, which we can call from every data flow and log only errors which we want?
Also want to confirm if the way i am doing is correct way or is there any easier/better way to achieve this?
It goes something like this:Try
'create sqlcommand specifying connection and query
'create reader
reader = sqlcommand.ExecuteReader() <== this throws an "Object reference not set to an instance of an object" exception
'... other stuff here...
Catch ex as Exception
Row.DirectToErrorOutput()
Finally
'dispose sqlcommand and reader here...
End Try
Despite the Catch block, the Script Component still fails, and doesn't get redirected to the ErrorOutput path.
Any ideas on how to solve this?sql
one matrix above another doesn't export properly to Excel
A newb question: I've got a report with a table leftmost in the body,
with a matrix immediately to the right of the table. Immediately
below the table are 2 textboxes displaying totals from the table.
Immediately below the first matrix is another matrix displaying totals
from the first matrix:
Table Matrix1
Textboxes Matrix2
All is well in preview, HTML view, and PDF export.
After Excel export, the sheet is arranged thus:
Table Matrix1
Matrix2 Textboxes
I've tried putting the 2 matrices in a rectangle, and even inside
nested rectangles, with no effect on the incorrect Excel layout.
The report is designed this way because the users want empty space
between the detail data (in the Table and Matrix1) and the totals.
The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
expand in step with Matrix1.
I think. Help? RDL available if anybody wants it.
Thanks
MatrixLoverI am running into similar problem. I have one matrix over another and report
logic hide / unhide matrix. Every work like charm in reports, export to pdf,
but having trouble in excel export. When export to excel, the hidden matrix
also appears just below the active matrix. Any help appreciated.
"MatrixLover" wrote:
> A newb question: I've got a report with a table leftmost in the body,
> with a matrix immediately to the right of the table. Immediately
> below the table are 2 textboxes displaying totals from the table.
> Immediately below the first matrix is another matrix displaying totals
> from the first matrix:
> Table Matrix1
> Textboxes Matrix2
> All is well in preview, HTML view, and PDF export.
> After Excel export, the sheet is arranged thus:
> Table Matrix1
> Matrix2 Textboxes
> I've tried putting the 2 matrices in a rectangle, and even inside
> nested rectangles, with no effect on the incorrect Excel layout.
> The report is designed this way because the users want empty space
> between the detail data (in the Table and Matrix1) and the totals.
> The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
> expand in step with Matrix1.
> I think. Help? RDL available if anybody wants it.
> Thanks
> MatrixLover
>
with a matrix immediately to the right of the table. Immediately
below the table are 2 textboxes displaying totals from the table.
Immediately below the first matrix is another matrix displaying totals
from the first matrix:
Table Matrix1
Textboxes Matrix2
All is well in preview, HTML view, and PDF export.
After Excel export, the sheet is arranged thus:
Table Matrix1
Matrix2 Textboxes
I've tried putting the 2 matrices in a rectangle, and even inside
nested rectangles, with no effect on the incorrect Excel layout.
The report is designed this way because the users want empty space
between the detail data (in the Table and Matrix1) and the totals.
The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
expand in step with Matrix1.
I think. Help? RDL available if anybody wants it.
Thanks
MatrixLoverI am running into similar problem. I have one matrix over another and report
logic hide / unhide matrix. Every work like charm in reports, export to pdf,
but having trouble in excel export. When export to excel, the hidden matrix
also appears just below the active matrix. Any help appreciated.
"MatrixLover" wrote:
> A newb question: I've got a report with a table leftmost in the body,
> with a matrix immediately to the right of the table. Immediately
> below the table are 2 textboxes displaying totals from the table.
> Immediately below the first matrix is another matrix displaying totals
> from the first matrix:
> Table Matrix1
> Textboxes Matrix2
> All is well in preview, HTML view, and PDF export.
> After Excel export, the sheet is arranged thus:
> Table Matrix1
> Matrix2 Textboxes
> I've tried putting the 2 matrices in a rectangle, and even inside
> nested rectangles, with no effect on the incorrect Excel layout.
> The report is designed this way because the users want empty space
> between the detail data (in the Table and Matrix1) and the totals.
> The number of columns in Matrix 1 is dynamic and so I need Matrix2 to
> expand in step with Matrix1.
> I think. Help? RDL available if anybody wants it.
> Thanks
> MatrixLover
>
Wednesday, March 28, 2012
One fact table/cube or multiple fact tables/cube
Hi,
I am now starting on my 2nd analysis server project and I have 8 dimensions and 6 fact tables. One fact table has 90 million rows, the other one has 30 million rows. The other four are less then 1 million rows big.
Should I create one cube with 6 fact tables in it or 6 cubes with one fact table ?
The advantage of the first one is that if you need to make a report you can have all the data in one query which is great for the users.
The advantage of the last one is that if you develop, you can easily calculate and test a small cube.
I am also thinking to get the best of both worlds namely going for the last option and a a 7th cube which has links to all the other six cubes.
Any suggestions ?
Constantijn Enders
This will have thoughts or considerations to address most of your questions.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1358193&SiteID=1
I would love to hear other people's thoughts, too.
|||And here's a separate discussion on the same topic:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1402506&SiteID=1
|||Hi,
I finally ended up at this blog http://prologika.com/CS/blogs/blog/archive/2006/06/27/1331.aspx
This confirmed my final thought, split into smaller and then one cube to rule them all
CE
One entry per individual with multiple entries
Hi,
I have a table that consists of sales by employee by month and because there
are more than one sales category, there might be multiple entries per
employee per month. "Hours" represent the TOTAL number of hours worked for
a
particular month and it is the same for all sales categories within a month.
I'm looking for a way to have the first entry per month be populated with th
e
number of hours worked for that particular month (i.e. 160) and all others b
e
blank or 0. What would be the best way to achieve that?
Month Empl_ID Category Sales Hours
01-05 12345 A 30 160
01-05 12345 B 32 160
02-05 12345 A 44 165
02-05 12345 C 13 165
02-05 12345 E 5 165
Thanks,> I'm looking for a way to have the first entry per month be populated with
> the
> number of hours worked for that particular month (i.e. 160) and all others
> be
> blank or 0. What would be the best way to achieve that?
That would just be a kludge around a flawed design. Far better to remove the
redundant hours worked column. If the hours are only to be recorded at the
employee/month level then they belong in a separate table.
Assuming for the moment that your table design is set in stone, you could
perhaps do something like this to reset the other hours to zero:
UPDATE sales
SET hours = 0
WHERE EXISTS
(SELECT *
FROM sales AS S
WHERE S.month = sales.month
AND S.emp_id = sales.emp_id
AND S.category < sales.category) ;
(untested)
Wouldn't you rather fix the design?
David Portas
SQL Server MVP
--
"Pasha" <Pasha@.discussions.microsoft.com> wrote in message
news:9712A4A2-EA55-4AD1-9D88-6AF1C8DE92DF@.microsoft.com...
> Hi,
> I have a table that consists of sales by employee by month and because
> there
> are more than one sales category, there might be multiple entries per
> employee per month. "Hours" represent the TOTAL number of hours worked
> for a
> particular month and it is the same for all sales categories within a
> month.
> I'm looking for a way to have the first entry per month be populated with
> the
> number of hours worked for that particular month (i.e. 160) and all others
> be
> blank or 0. What would be the best way to achieve that?
> Month Empl_ID Category Sales Hours
> 01-05 12345 A 30 160
> 01-05 12345 B 32 160
> 02-05 12345 A 44 165
> 02-05 12345 C 13 165
> 02-05 12345 E 5 165
>
> Thanks,|||On Tue, 4 Oct 2005 15:07:03 -0700, Pasha wrote:
>Hi,
>I have a table that consists of sales by employee by month and because ther
e
>are more than one sales category, there might be multiple entries per
>employee per month. "Hours" represent the TOTAL number of hours worked for
a
>particular month and it is the same for all sales categories within a month
.
>I'm looking for a way to have the first entry per month be populated with t
he
>number of hours worked for that particular month (i.e. 160) and all others
be
>blank or 0. What would be the best way to achieve that?
>Month Empl_ID Category Sales Hours
>01-05 12345 A 30 160
>01-05 12345 B 32 160
>02-05 12345 A 44 165
>02-05 12345 C 13 165
>02-05 12345 E 5 165
>
>Thanks,
Hi Pasha,
You need to normalize this design. The current design allows one to
store contradicting data. What if Hours is NOT the same on all rows for
an employee in a month?
Here's how your tables should look:
CREATE TABLE Table1 -- Use a better name
(Month datetime NOT NULL, -- Maybe other datatype
Empl_ID int NOT NULL,
Hours int NOT NULL,
PRIMARY KEY (Month, Empl_ID),
-- FOREIGN KEY (Empl_ID) REFERENCES Personnel(Empl_ID),
CHECK (Hours >= 0),
)
CREATE TABLE Table1 -- Use a better name
(Month datetime NOT NULL, -- Maybe other datatype
Empl_ID int NOT NULL,
Category char(1) NOT NULL,
Sales int NOT NULL,
PRIMARY KEY (Month, Empl_ID, Category),
FOREIGN KEY (Month, Empl_ID) REFERENCES Table1 (Month, Empl_ID),
CHECK (Sales >= 0),
CHECK (Category IN ('A','B','C','D','E')),
)
For now, the kludge to set Hours to 0 for all but the "first" (based in
category) in the month is:
UPDATE BadTable
SET Hours = 0
WHERE EXISTS (SELECT *
FROM BadTable AS a
WHERE a.Empl_ID = BadTable.Empl_ID
AND a.Month = BadTable.Month
AND a.Category < BadTable.Category)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||This is a fact table for OLAP cube, so the design is good for the cube. I
was thinking about having an dentity column, minimum of which would determin
e
the first entry...
"David Portas" wrote:
> That would just be a kludge around a flawed design. Far better to remove t
he
> redundant hours worked column. If the hours are only to be recorded at the
> employee/month level then they belong in a separate table.
> Assuming for the moment that your table design is set in stone, you could
> perhaps do something like this to reset the other hours to zero:
> UPDATE sales
> SET hours = 0
> WHERE EXISTS
> (SELECT *
> FROM sales AS S
> WHERE S.month = sales.month
> AND S.emp_id = sales.emp_id
> AND S.category < sales.category) ;
> (untested)
> Wouldn't you rather fix the design?
> --
> David Portas
> SQL Server MVP
> --
> "Pasha" <Pasha@.discussions.microsoft.com> wrote in message
> news:9712A4A2-EA55-4AD1-9D88-6AF1C8DE92DF@.microsoft.com...
>
>|||Well it doesn't look much like a fact table but if it is then one option is
to normalize and then construct the fact table in a view.
David Portas
SQL Server MVP
--
"Pasha" <Pasha@.discussions.microsoft.com> wrote in message
news:3BD96ACF-A4A0-456C-8E5D-D58C5336DB06@.microsoft.com...
> This is a fact table for OLAP cube, so the design is good for the cube. I
> was thinking about having an dentity column, minimum of which would
> determine
> the first entry...
>
> "David Portas" wrote:
>|||This is by no means "good" for a cube. Every row in the fact table should
be of the same "grain" and each column in the row should be to that grain.
For this to be a proper fact table, one of two things should be true:
Either hours should be at the same level as category (so a-hours + b-hours +
e-hours total hours, or you need to split this into two fact tables, one at
the grain of a category per month, the other at hours per month. Of course
the actual shape of the fact table would be based on your source data.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Pasha" <Pasha@.discussions.microsoft.com> wrote in message
news:3BD96ACF-A4A0-456C-8E5D-D58C5336DB06@.microsoft.com...
> This is a fact table for OLAP cube, so the design is good for the cube. I
> was thinking about having an dentity column, minimum of which would
> determine
> the first entry...
>
> "David Portas" wrote:
>sql
I have a table that consists of sales by employee by month and because there
are more than one sales category, there might be multiple entries per
employee per month. "Hours" represent the TOTAL number of hours worked for
a
particular month and it is the same for all sales categories within a month.
I'm looking for a way to have the first entry per month be populated with th
e
number of hours worked for that particular month (i.e. 160) and all others b
e
blank or 0. What would be the best way to achieve that?
Month Empl_ID Category Sales Hours
01-05 12345 A 30 160
01-05 12345 B 32 160
02-05 12345 A 44 165
02-05 12345 C 13 165
02-05 12345 E 5 165
Thanks,> I'm looking for a way to have the first entry per month be populated with
> the
> number of hours worked for that particular month (i.e. 160) and all others
> be
> blank or 0. What would be the best way to achieve that?
That would just be a kludge around a flawed design. Far better to remove the
redundant hours worked column. If the hours are only to be recorded at the
employee/month level then they belong in a separate table.
Assuming for the moment that your table design is set in stone, you could
perhaps do something like this to reset the other hours to zero:
UPDATE sales
SET hours = 0
WHERE EXISTS
(SELECT *
FROM sales AS S
WHERE S.month = sales.month
AND S.emp_id = sales.emp_id
AND S.category < sales.category) ;
(untested)
Wouldn't you rather fix the design?
David Portas
SQL Server MVP
--
"Pasha" <Pasha@.discussions.microsoft.com> wrote in message
news:9712A4A2-EA55-4AD1-9D88-6AF1C8DE92DF@.microsoft.com...
> Hi,
> I have a table that consists of sales by employee by month and because
> there
> are more than one sales category, there might be multiple entries per
> employee per month. "Hours" represent the TOTAL number of hours worked
> for a
> particular month and it is the same for all sales categories within a
> month.
> I'm looking for a way to have the first entry per month be populated with
> the
> number of hours worked for that particular month (i.e. 160) and all others
> be
> blank or 0. What would be the best way to achieve that?
> Month Empl_ID Category Sales Hours
> 01-05 12345 A 30 160
> 01-05 12345 B 32 160
> 02-05 12345 A 44 165
> 02-05 12345 C 13 165
> 02-05 12345 E 5 165
>
> Thanks,|||On Tue, 4 Oct 2005 15:07:03 -0700, Pasha wrote:
>Hi,
>I have a table that consists of sales by employee by month and because ther
e
>are more than one sales category, there might be multiple entries per
>employee per month. "Hours" represent the TOTAL number of hours worked for
a
>particular month and it is the same for all sales categories within a month
.
>I'm looking for a way to have the first entry per month be populated with t
he
>number of hours worked for that particular month (i.e. 160) and all others
be
>blank or 0. What would be the best way to achieve that?
>Month Empl_ID Category Sales Hours
>01-05 12345 A 30 160
>01-05 12345 B 32 160
>02-05 12345 A 44 165
>02-05 12345 C 13 165
>02-05 12345 E 5 165
>
>Thanks,
Hi Pasha,
You need to normalize this design. The current design allows one to
store contradicting data. What if Hours is NOT the same on all rows for
an employee in a month?
Here's how your tables should look:
CREATE TABLE Table1 -- Use a better name
(Month datetime NOT NULL, -- Maybe other datatype
Empl_ID int NOT NULL,
Hours int NOT NULL,
PRIMARY KEY (Month, Empl_ID),
-- FOREIGN KEY (Empl_ID) REFERENCES Personnel(Empl_ID),
CHECK (Hours >= 0),
)
CREATE TABLE Table1 -- Use a better name
(Month datetime NOT NULL, -- Maybe other datatype
Empl_ID int NOT NULL,
Category char(1) NOT NULL,
Sales int NOT NULL,
PRIMARY KEY (Month, Empl_ID, Category),
FOREIGN KEY (Month, Empl_ID) REFERENCES Table1 (Month, Empl_ID),
CHECK (Sales >= 0),
CHECK (Category IN ('A','B','C','D','E')),
)
For now, the kludge to set Hours to 0 for all but the "first" (based in
category) in the month is:
UPDATE BadTable
SET Hours = 0
WHERE EXISTS (SELECT *
FROM BadTable AS a
WHERE a.Empl_ID = BadTable.Empl_ID
AND a.Month = BadTable.Month
AND a.Category < BadTable.Category)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||This is a fact table for OLAP cube, so the design is good for the cube. I
was thinking about having an dentity column, minimum of which would determin
e
the first entry...
"David Portas" wrote:
> That would just be a kludge around a flawed design. Far better to remove t
he
> redundant hours worked column. If the hours are only to be recorded at the
> employee/month level then they belong in a separate table.
> Assuming for the moment that your table design is set in stone, you could
> perhaps do something like this to reset the other hours to zero:
> UPDATE sales
> SET hours = 0
> WHERE EXISTS
> (SELECT *
> FROM sales AS S
> WHERE S.month = sales.month
> AND S.emp_id = sales.emp_id
> AND S.category < sales.category) ;
> (untested)
> Wouldn't you rather fix the design?
> --
> David Portas
> SQL Server MVP
> --
> "Pasha" <Pasha@.discussions.microsoft.com> wrote in message
> news:9712A4A2-EA55-4AD1-9D88-6AF1C8DE92DF@.microsoft.com...
>
>|||Well it doesn't look much like a fact table but if it is then one option is
to normalize and then construct the fact table in a view.
David Portas
SQL Server MVP
--
"Pasha" <Pasha@.discussions.microsoft.com> wrote in message
news:3BD96ACF-A4A0-456C-8E5D-D58C5336DB06@.microsoft.com...
> This is a fact table for OLAP cube, so the design is good for the cube. I
> was thinking about having an dentity column, minimum of which would
> determine
> the first entry...
>
> "David Portas" wrote:
>|||This is by no means "good" for a cube. Every row in the fact table should
be of the same "grain" and each column in the row should be to that grain.
For this to be a proper fact table, one of two things should be true:
Either hours should be at the same level as category (so a-hours + b-hours +
e-hours total hours, or you need to split this into two fact tables, one at
the grain of a category per month, the other at hours per month. Of course
the actual shape of the fact table would be based on your source data.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Pasha" <Pasha@.discussions.microsoft.com> wrote in message
news:3BD96ACF-A4A0-456C-8E5D-D58C5336DB06@.microsoft.com...
> This is a fact table for OLAP cube, so the design is good for the cube. I
> was thinking about having an dentity column, minimum of which would
> determine
> the first entry...
>
> "David Portas" wrote:
>sql
One DataRegion(Table) Multiple DataSets
I have a complexed report
It makes use of two queries and 2 tables
I need to use a group in order to display the information correctly,
If I had one query it would have worked perfectly, But the data I am
retrieving is so complexed that I need to make use of two queries other wise
I get duplicate data
Table 1 contains section1, and 2 of the displayed info
Table 2 contains the 3rd section
it looks like this;
Page 1
header
Section1
Section 2
Section 3
Footer
Page 2
header
Section1
Section 2
Section 3
Footer
So in order to accomplish this I take two tables link them to one dataset.
Add a group, But this results in the following. I need page breaks so I set
the page break option in the group properties
Page 1
header
section 1
section2
Footer
Page 2
section1
section2
Page 3
Section 3
Page 4 Section 3
I then put the 2 tables in a list box, and set the grouping on the list, And
This works 100 %. It groups all the data brilliantly. The problem is I cant
use one query, I need to use two!
SO Is their a work around or some way to link 2 datasets to one list
control.By adding the full path or something. The only way I can currently
reference more than one dataset per table is by using aggeragate funtions.
But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1 result
so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that returns
an errorData regions, in SQL Server 2000 Reporting Services, can only be bound to a
single data set with once exception: All secondary data references must be
contained in an aggregate function with the dataset specified. For example,
First(=Fields!<SomeField>.Value), "<SomeDataSet>"), is allowed. To achieve
the effect you want will have to be done in the query. Some of the tools
available to you are joins, unions, openrowset, or linked servers.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griffen" <Griffen@.discussions.microsoft.com> wrote in message
news:B7A2C3C0-4522-466A-B5D2-08ECCD3471C1@.microsoft.com...
> I have a complexed report
> It makes use of two queries and 2 tables
> I need to use a group in order to display the information correctly,
> If I had one query it would have worked perfectly, But the data I am
> retrieving is so complexed that I need to make use of two queries other
wise
> I get duplicate data
> Table 1 contains section1, and 2 of the displayed info
> Table 2 contains the 3rd section
> it looks like this;
> Page 1
> header
> Section1
> Section 2
> Section 3
> Footer
>
> Page 2
> header
> Section1
> Section 2
> Section 3
> Footer
> So in order to accomplish this I take two tables link them to one dataset.
> Add a group, But this results in the following. I need page breaks so I
set
> the page break option in the group properties
>
> Page 1
> header
> section 1
> section2
> Footer
> Page 2
> section1
> section2
> Page 3
> Section 3
> Page 4 Section 3
> I then put the 2 tables in a list box, and set the grouping on the list,
And
> This works 100 %. It groups all the data brilliantly. The problem is I
cant
> use one query, I need to use two!
> SO Is their a work around or some way to link 2 datasets to one list
> control.By adding the full path or something. The only way I can currently
> reference more than one dataset per table is by using aggeragate funtions.
> But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1
result
> so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that
returns
> an errorsql
It makes use of two queries and 2 tables
I need to use a group in order to display the information correctly,
If I had one query it would have worked perfectly, But the data I am
retrieving is so complexed that I need to make use of two queries other wise
I get duplicate data
Table 1 contains section1, and 2 of the displayed info
Table 2 contains the 3rd section
it looks like this;
Page 1
header
Section1
Section 2
Section 3
Footer
Page 2
header
Section1
Section 2
Section 3
Footer
So in order to accomplish this I take two tables link them to one dataset.
Add a group, But this results in the following. I need page breaks so I set
the page break option in the group properties
Page 1
header
section 1
section2
Footer
Page 2
section1
section2
Page 3
Section 3
Page 4 Section 3
I then put the 2 tables in a list box, and set the grouping on the list, And
This works 100 %. It groups all the data brilliantly. The problem is I cant
use one query, I need to use two!
SO Is their a work around or some way to link 2 datasets to one list
control.By adding the full path or something. The only way I can currently
reference more than one dataset per table is by using aggeragate funtions.
But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1 result
so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that returns
an errorData regions, in SQL Server 2000 Reporting Services, can only be bound to a
single data set with once exception: All secondary data references must be
contained in an aggregate function with the dataset specified. For example,
First(=Fields!<SomeField>.Value), "<SomeDataSet>"), is allowed. To achieve
the effect you want will have to be done in the query. Some of the tools
available to you are joins, unions, openrowset, or linked servers.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Griffen" <Griffen@.discussions.microsoft.com> wrote in message
news:B7A2C3C0-4522-466A-B5D2-08ECCD3471C1@.microsoft.com...
> I have a complexed report
> It makes use of two queries and 2 tables
> I need to use a group in order to display the information correctly,
> If I had one query it would have worked perfectly, But the data I am
> retrieving is so complexed that I need to make use of two queries other
wise
> I get duplicate data
> Table 1 contains section1, and 2 of the displayed info
> Table 2 contains the 3rd section
> it looks like this;
> Page 1
> header
> Section1
> Section 2
> Section 3
> Footer
>
> Page 2
> header
> Section1
> Section 2
> Section 3
> Footer
> So in order to accomplish this I take two tables link them to one dataset.
> Add a group, But this results in the following. I need page breaks so I
set
> the page break option in the group properties
>
> Page 1
> header
> section 1
> section2
> Footer
> Page 2
> section1
> section2
> Page 3
> Section 3
> Page 4 Section 3
> I then put the 2 tables in a list box, and set the grouping on the list,
And
> This works 100 %. It groups all the data brilliantly. The problem is I
cant
> use one query, I need to use two!
> SO Is their a work around or some way to link 2 datasets to one list
> control.By adding the full path or something. The only way I can currently
> reference more than one dataset per table is by using aggeragate funtions.
> But =First(Fields!SIZE.Value, "DataSet2") will only return the top 1
result
> so that doesnt work I tried (Fields!SIZE.Value, "DataSet2") but that
returns
> an errorsql
One database to rule them all
How can I setup the dbs in sql server so that when I change the data in one table the changes will cascade down to the tables in my other dbs. Therefore, one database would hold a primary key table. If I had 15 other dbs, then I could somehow link them so the data changed in the primary key table of the 1st database would cascade down to the other dbs.
Thanks
You would have to write a whole lot of TRIGGERS.
That 'could' be a substaintial performance penalty.
(And most likely a very unwieldy design.)
Let us know what you are attempting to accomplish, and perhaps we can guide you to a solution that will work AND be efficient.
|||I am attempting to update all the IDs throughout all of my databases. For example, imagine you had several databases for different applications. Maybe one application is for health information, another application is penitentary clients, and a third application is for penitentary client rehabilitation. A penitentary client would be assigned a unique number and the client shows up in the penitentary client program. If the client receives health or rehabilitation services, then their unique number is used in the other applications so you don't need to duplicate information such as their name. However, for some reason the clients number has to be changed. I want to be able to change the information in the penitentary clients db and have it automatically change in the other databases. If for some reason the ID changes at 3:00 PM, then it would be nice to have all the data updated in real time without having to wait to run a job that updates all of the data. If I get a list of new client IDs to be updated, then I would like a better way to update the tables in a database rather than running an update statement against several tables in different dbs. If a table gets added to a db, I don't want to have to remember to go and add another update statement to some job.One database to rule them all
How can I setup the dbs in sql server so that when I change the data in one table the changes will cascade down to the tables in my other dbs. Therefore, one database would hold a primary key table. If I had 15 other dbs, then I could somehow link them so the data changed in the primary key table of the 1st database would cascade down to the other dbs.
sql
Thanks
You would have to write a whole lot of TRIGGERS.
That 'could' be a substaintial performance penalty.
(And most likely a very unwieldy design.)
Let us know what you are attempting to accomplish, and perhaps we can guide you to a solution that will work AND be efficient.
|||I am attempting to update all the IDs throughout all of my databases. For example, imagine you had several databases for different applications. Maybe one application is for health information, another application is penitentary clients, and a third application is for penitentary client rehabilitation. A penitentary client would be assigned a unique number and the client shows up in the penitentary client program. If the client receives health or rehabilitation services, then their unique number is used in the other applications so you don't need to duplicate information such as their name. However, for some reason the clients number has to be changed. I want to be able to change the information in the penitentary clients db and have it automatically change in the other databases. If for some reason the ID changes at 3:00 PM, then it would be nice to have all the data updated in real time without having to wait to run a job that updates all of the data. If I get a list of new client IDs to be updated, then I would like a better way to update the tables in a database rather than running an update statement against several tables in different dbs. If a table gets added to a db, I don't want to have to remember to go and add another update statement to some job.sql
one control loop with Substring to correct first normal column violation
given a table from an excel spreadsheet that contains a column that violates
first normal ;
Area Code TimeZone
787/939 4
212/646/718/917 5
I would like to create one procedure that loops through n possible values of
the area codes and creates a separate row for each value.
The Substring(s) below would have to start from positions
1,5,9,13,17,21,25,29 to capture the 3 character codes.
Right now I am manually creating each table with separate Substring
select/inserts with a union of all the tables and a select distinct.
The 65,000 rows of the original table end up deduped into about 245 rows of
distinct area codes per time zones.
Here are the first two sets of queries (there are 7 total) followed by the
consolidation with UNION ALL
-- create row with only first area code of '/' delimited values
CREATE TABLE first_areacode
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO first_areacode
(AreaCode, TimeZone)
select SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
from AreaCodeTimeZone
-- create row with only second area code of '/' delimited values
CREATE TABLE second_areacode
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO second_areacode
(AreaCode, TimeZone)
select SUBSTRING(AreaCode, 5, 3) AS AreaCode,TimeZone
from AreaCodeTimeZone
insert into combined_areacode
SELECT * FROM first_areacode
UNION ALL
SELECT * FROM second_areacode
UNION ALL
SELECT * FROM third_areacode
UNION ALL
etc.
.
Thank you for ideas to help automate this,
-Greghazz (hazz@.sonic.net) writes:
> I would like to create one procedure that loops through n possible
> values of the area codes and creates a separate row for each value. The
> Substring(s) below would have to start from positions
> 1,5,9,13,17,21,25,29 to capture the 3 character codes. Right now I am
> manually creating each table with separate Substring select/inserts with
> a union of all the tables and a select distinct. The 65,000 rows of the
> original table end up deduped into about 245 rows of distinct area codes
> per time zones.
> Here are the first two sets of queries (there are 7 total) followed by the
> consolidation with UNION ALL
> -- create row with only first area code of '/' delimited values
> CREATE TABLE first_areacode
> (AreaCode varchar(50),
> TimeZone varchar(50))
> INSERT INTO first_areacode
> (AreaCode, TimeZone)
> select SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
> from AreaCodeTimeZone
As long as you can rely on that the area codes are exact three
characters, no extra spaces etc, this could work:
SELECT substring(AreaCode, n, 3), TimeZone
FROM tbl
CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION ALL
SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
..) AS n
WHERE len(AreaCode) <= n
By the way, which version of SQL Server are you using?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SQL 2005.
Thank you Erland !
-Greg
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns977F34D4AFD2Yazorman@.127.0.0.1...
> hazz (hazz@.sonic.net) writes:
> As long as you can rely on that the area codes are exact three
> characters, no extra spaces etc, this could work:
> SELECT substring(AreaCode, n, 3), TimeZone
> FROM tbl
> CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION
> ALL
> SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
> ...) AS n
> WHERE len(AreaCode) <= n
> By the way, which version of SQL Server are you using?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||When I used this, the AreaCode column was blank although the TimeZone column
was populated.
I just reposted this with working sql that I would still like to condense
into an algorithm. Thanks, -Greg
> SELECT substring(AreaCode, n, 3), TimeZone
> FROM tbl
> CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION
> ALL
> SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
> ...) AS n
> WHERE len(AreaCode) <= n
first normal ;
Area Code TimeZone
787/939 4
212/646/718/917 5
I would like to create one procedure that loops through n possible values of
the area codes and creates a separate row for each value.
The Substring(s) below would have to start from positions
1,5,9,13,17,21,25,29 to capture the 3 character codes.
Right now I am manually creating each table with separate Substring
select/inserts with a union of all the tables and a select distinct.
The 65,000 rows of the original table end up deduped into about 245 rows of
distinct area codes per time zones.
Here are the first two sets of queries (there are 7 total) followed by the
consolidation with UNION ALL
-- create row with only first area code of '/' delimited values
CREATE TABLE first_areacode
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO first_areacode
(AreaCode, TimeZone)
select SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
from AreaCodeTimeZone
-- create row with only second area code of '/' delimited values
CREATE TABLE second_areacode
(AreaCode varchar(50),
TimeZone varchar(50))
INSERT INTO second_areacode
(AreaCode, TimeZone)
select SUBSTRING(AreaCode, 5, 3) AS AreaCode,TimeZone
from AreaCodeTimeZone
insert into combined_areacode
SELECT * FROM first_areacode
UNION ALL
SELECT * FROM second_areacode
UNION ALL
SELECT * FROM third_areacode
UNION ALL
etc.
.
Thank you for ideas to help automate this,
-Greghazz (hazz@.sonic.net) writes:
> I would like to create one procedure that loops through n possible
> values of the area codes and creates a separate row for each value. The
> Substring(s) below would have to start from positions
> 1,5,9,13,17,21,25,29 to capture the 3 character codes. Right now I am
> manually creating each table with separate Substring select/inserts with
> a union of all the tables and a select distinct. The 65,000 rows of the
> original table end up deduped into about 245 rows of distinct area codes
> per time zones.
> Here are the first two sets of queries (there are 7 total) followed by the
> consolidation with UNION ALL
> -- create row with only first area code of '/' delimited values
> CREATE TABLE first_areacode
> (AreaCode varchar(50),
> TimeZone varchar(50))
> INSERT INTO first_areacode
> (AreaCode, TimeZone)
> select SUBSTRING(AreaCode, 1, 3) AS AreaCode,TimeZone
> from AreaCodeTimeZone
As long as you can rely on that the area codes are exact three
characters, no extra spaces etc, this could work:
SELECT substring(AreaCode, n, 3), TimeZone
FROM tbl
CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION ALL
SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
..) AS n
WHERE len(AreaCode) <= n
By the way, which version of SQL Server are you using?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SQL 2005.
Thank you Erland !
-Greg
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns977F34D4AFD2Yazorman@.127.0.0.1...
> hazz (hazz@.sonic.net) writes:
> As long as you can rely on that the area codes are exact three
> characters, no extra spaces etc, this could work:
> SELECT substring(AreaCode, n, 3), TimeZone
> FROM tbl
> CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION
> ALL
> SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
> ...) AS n
> WHERE len(AreaCode) <= n
> By the way, which version of SQL Server are you using?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||When I used this, the AreaCode column was blank although the TimeZone column
was populated.
I just reposted this with working sql that I would still like to condense
into an algorithm. Thanks, -Greg
> SELECT substring(AreaCode, n, 3), TimeZone
> FROM tbl
> CROSS JOIN (SELECT n = 1 UNION ALL SELECT 5 UNION ALL SELECT 9 UNION
> ALL
> SELECT 13 UNION ALL SELECT 17 UNION ALL SELECT 21 UNION ALL
> ...) AS n
> WHERE len(AreaCode) <= n
One column
Aside from temp table, in what circumstance one would have only one
column for a table (be it data or key)? I can't think of any case but
some of you may.
Thanks.Doug Baroter (qwert12345@.boxfrog.com) writes:
> Aside from temp table, in what circumstance one would have only one
> column for a table (be it data or key)? I can't think of any case but
> some of you may.
column for a table (be it data or key)? I can't think of any case but
some of you may.
Thanks.Doug Baroter (qwert12345@.boxfrog.com) writes:
> Aside from temp table, in what circumstance one would have only one
> column for a table (be it data or key)? I can't think of any case but
> some of you may.
The system I work with have a couple of one-column one-row tables. These
tables hold next available number in series which due to business rules
must be contiguous. They are in tables of their own to avoid a hot spot
in a table; this was an issue in SQL 6.5.
We also have a one-column table that holds dates from 1980-01-01 to 2149-12-
31. If you read a book like Joe Celko's "SQL for Smarties", you will find
several solutions that are based on a table of numbers. That is, a one-
column tables with all numbers from 1 up to some limit. Both our date table
and a table of numbers are auxillary tables that makes other queries
easier to write.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 1 Aug 2003 21:56:54 -0700 in comp.databases.ms-sqlserver,
qwert12345@.boxfrog.com (Doug Baroter) wrote:
>Aside from temp table, in what circumstance one would have only one
>column for a table (be it data or key)? I can't think of any case but
>some of you may.
>Thanks.
I have a few:
tblCityNoCounty - Cities with no county so the County field on a form
is conditionally required, e.g. enter "London" as the City and there's
no need to enter the county. Really should be 2 columns with country
as well as there's Londons around the world but then at the time it
was only for the UK.
tblCountry - Speaks for itself but then I could have other info on
that like dialling code, etc. At first it did but were dropped thru
lack of use.
tblSupplierFav - List of supplier PKs that will show in a supplier
form.
tblTitle - Mr, Mrs, Dr, Rev, etc.
zstblSQLReservedWord - used in a tool I wrote to scan all tables for
instances of SQL reserved words in table/column names in an Access
database prior to upsizing.
--
Ride Free (but you still have to pay for the petrol)
(replace sithlord with trevor for email)|||Excellent. It seems in essence the auxillary tables serve to make
system/solution work better, they themselves are not data tables, I
use this concept as well.
I need to catch up on Joe Celko's "SQL for Smarties" after I put out
immediate fire here.
Thanks.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93CB67FF99FEBYazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> > Aside from temp table, in what circumstance one would have only one
> > column for a table (be it data or key)? I can't think of any case but
> > some of you may.
> The system I work with have a couple of one-column one-row tables. These
> tables hold next available number in series which due to business rules
> must be contiguous. They are in tables of their own to avoid a hot spot
> in a table; this was an issue in SQL 6.5.
> We also have a one-column table that holds dates from 1980-01-01 to 2149-12-
> 31. If you read a book like Joe Celko's "SQL for Smarties", you will find
> several solutions that are based on a table of numbers. That is, a one-
> column tables with all numbers from 1 up to some limit. Both our date table
> and a table of numbers are auxillary tables that makes other queries
> easier to write.|||Thank you. What you described, in Erland Sommarskog's term, is auxillary tables.
Trevor Best <bouncer@.localhost> wrote in message news:<k39niv42a0s86v1htc4kdl8mbvm9v4ecuk@.4ax.com>...
> On 1 Aug 2003 21:56:54 -0700 in comp.databases.ms-sqlserver,
> qwert12345@.boxfrog.com (Doug Baroter) wrote:
> >Aside from temp table, in what circumstance one would have only one
> >column for a table (be it data or key)? I can't think of any case but
> >some of you may.
> >Thanks.
> I have a few:
> tblCityNoCounty - Cities with no county so the County field on a form
> is conditionally required, e.g. enter "London" as the City and there's
> no need to enter the county. Really should be 2 columns with country
> as well as there's Londons around the world but then at the time it
> was only for the UK.
> tblCountry - Speaks for itself but then I could have other info on
> that like dialling code, etc. At first it did but were dropped thru
> lack of use.
> tblSupplierFav - List of supplier PKs that will show in a supplier
> form.
> tblTitle - Mr, Mrs, Dr, Rev, etc.
> zstblSQLReservedWord - used in a tool I wrote to scan all tables for
> instances of SQL reserved words in table/column names in an Access
> database prior to upsizing.
Monday, March 26, 2012
Once replicated DB can't be detached
Dear Sir,
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the db?
Many thanks!
Henry
Try this:
EXEC sp_dboption 'DBNameHere', 'published', 'false'
GO
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
Dear Sir,
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the
db?
Many thanks!
Henry
|||Thanks!
"Narayana Vyas Kondreddi" wrote:
> Try this:
> EXEC sp_dboption 'DBNameHere', 'published', 'false'
> GO
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
> Dear Sir,
> I have a database which has been "Transactional" replicated one table. I
> already deleted the subscription and deleted the publication. But still the
> database has the replicate icon besides on EM and not allowing me to detach.
> What can I do to refresh the DB status in order to allow me to detach the
> db?
> Many thanks!
> Henry
>
>
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the db?
Many thanks!
Henry
Try this:
EXEC sp_dboption 'DBNameHere', 'published', 'false'
GO
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
Dear Sir,
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the
db?
Many thanks!
Henry
|||Thanks!
"Narayana Vyas Kondreddi" wrote:
> Try this:
> EXEC sp_dboption 'DBNameHere', 'published', 'false'
> GO
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
> Dear Sir,
> I have a database which has been "Transactional" replicated one table. I
> already deleted the subscription and deleted the publication. But still the
> database has the replicate icon besides on EM and not allowing me to detach.
> What can I do to refresh the DB status in order to allow me to detach the
> db?
> Many thanks!
> Henry
>
>
Once replicated DB can't be detached
Dear Sir,
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the db
?
Many thanks!
HenryTry this:
EXEC sp_dboption 'DBNameHere', 'published', 'false'
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
Dear Sir,
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the
db?
Many thanks!
Henry|||Thanks!
"Narayana Vyas Kondreddi" wrote:
> Try this:
> EXEC sp_dboption 'DBNameHere', 'published', 'false'
> GO
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
> Dear Sir,
> I have a database which has been "Transactional" replicated one table. I
> already deleted the subscription and deleted the publication. But still th
e
> database has the replicate icon besides on EM and not allowing me to detac
h.
> What can I do to refresh the DB status in order to allow me to detach the
> db?
> Many thanks!
> Henry
>
>sql
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the db
?
Many thanks!
HenryTry this:
EXEC sp_dboption 'DBNameHere', 'published', 'false'
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
Dear Sir,
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the
db?
Many thanks!
Henry|||Thanks!
"Narayana Vyas Kondreddi" wrote:
> Try this:
> EXEC sp_dboption 'DBNameHere', 'published', 'false'
> GO
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
> Dear Sir,
> I have a database which has been "Transactional" replicated one table. I
> already deleted the subscription and deleted the publication. But still th
e
> database has the replicate icon besides on EM and not allowing me to detac
h.
> What can I do to refresh the DB status in order to allow me to detach the
> db?
> Many thanks!
> Henry
>
>sql
Once replicated DB can't be detached
Dear Sir,
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the db?
Many thanks!
HenryTry this:
EXEC sp_dboption 'DBNameHere', 'published', 'false'
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
Dear Sir,
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the
db?
Many thanks!
Henry|||Thanks!
"Narayana Vyas Kondreddi" wrote:
> Try this:
> EXEC sp_dboption 'DBNameHere', 'published', 'false'
> GO
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
> Dear Sir,
> I have a database which has been "Transactional" replicated one table. I
> already deleted the subscription and deleted the publication. But still the
> database has the replicate icon besides on EM and not allowing me to detach.
> What can I do to refresh the DB status in order to allow me to detach the
> db?
> Many thanks!
> Henry
>
>
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the db?
Many thanks!
HenryTry this:
EXEC sp_dboption 'DBNameHere', 'published', 'false'
GO
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Henry" <Henry@.discussions.microsoft.com> wrote in message
news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
Dear Sir,
I have a database which has been "Transactional" replicated one table. I
already deleted the subscription and deleted the publication. But still the
database has the replicate icon besides on EM and not allowing me to detach.
What can I do to refresh the DB status in order to allow me to detach the
db?
Many thanks!
Henry|||Thanks!
"Narayana Vyas Kondreddi" wrote:
> Try this:
> EXEC sp_dboption 'DBNameHere', 'published', 'false'
> GO
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Henry" <Henry@.discussions.microsoft.com> wrote in message
> news:9DB717B5-CCE8-41B8-A7CC-15E422F750E1@.microsoft.com...
> Dear Sir,
> I have a database which has been "Transactional" replicated one table. I
> already deleted the subscription and deleted the publication. But still the
> database has the replicate icon besides on EM and not allowing me to detach.
> What can I do to refresh the DB status in order to allow me to detach the
> db?
> Many thanks!
> Henry
>
>
on update catch 22
How do you make an update cascade opperation if you want to set the PK to an excisting one?
I have a table where to post are the same only the PK differs, now I want to delete one but transfer it's dependecies. i.e redirect its references to the other post.
How can I do that using on update cascade/delete. I see a catch 22 coming up!
/Jonneponne
Hi
I would redirect all dependencies to a single post you want to keep and then delete a duplicate post.
ON UPDATE CASCADE works only if you are trying to update the KEY references by Foreign Key , not the other way round.
NB.
On inserting a rec, auto insert into related table
Hi there,
I'm new...
Could someone tell me how to automatically (via a trigger perhaps ?) insert
records into a related (child) table, based on the insert into the parent ?
I have a situation in my app where I want to add a record into a tabe called
'OrderedService'. On insert into this table, I want to insert automatically,
exactly 5 records into (foreign key) related table 'OrderedServiceResult'.
Note that the only fields that need populating on creation of the child are
those necessary for the key...
Thank you !Bazza Formez (bazza.formez@.paradise.net.nz) writes:
> Could someone tell me how to automatically (via a trigger perhaps ?)
> insert records into a related (child) table, based on the insert into
> the parent ?
> I have a situation in my app where I want to add a record into a tabe
> called 'OrderedService'. On insert into this table, I want to insert
> automatically, exactly 5 records into (foreign key) related table
> 'OrderedServiceResult'.
> Note that the only fields that need populating on creation of the child
> are those necessary for the key...
Yes, you could use a trigger for this, at least judging from the information
you have provided. Here is a simple example:
CREATE TRIGGER tbl_tri ON tbl FOR INSERT AS
INSERT othertbl(col1, col2, ...)
SELECT col1, col2, ...
FROM inserted
"inserted" that appears here is a virtual table that holds the rows
that were inserted. This table is only visible within a trigger. There
is a sister table "deleted" which holds deleted rows in a DELETE trigger.
In an UPDATE trigger both tables are populated, "inserted" holding the
new rows, and "deleted" the old rows.
Very important to understand is that a trigger on SQL Server is fired
once per statement. Thus "inserted" can hold many rows.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for this excellent reply... Your effort is appreciated.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CF4D245B5FYazorman@.127.0.0.1...
> Bazza Formez (bazza.formez@.paradise.net.nz) writes:
> Yes, you could use a trigger for this, at least judging from the
> information
> you have provided. Here is a simple example:
> CREATE TRIGGER tbl_tri ON tbl FOR INSERT AS
> INSERT othertbl(col1, col2, ...)
> SELECT col1, col2, ...
> FROM inserted
> "inserted" that appears here is a virtual table that holds the rows
> that were inserted. This table is only visible within a trigger. There
> is a sister table "deleted" which holds deleted rows in a DELETE trigger.
> In an UPDATE trigger both tables are populated, "inserted" holding the
> new rows, and "deleted" the old rows.
> Very important to understand is that a trigger on SQL Server is fired
> once per statement. Thus "inserted" can hold many rows.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
I'm new...
Could someone tell me how to automatically (via a trigger perhaps ?) insert
records into a related (child) table, based on the insert into the parent ?
I have a situation in my app where I want to add a record into a tabe called
'OrderedService'. On insert into this table, I want to insert automatically,
exactly 5 records into (foreign key) related table 'OrderedServiceResult'.
Note that the only fields that need populating on creation of the child are
those necessary for the key...
Thank you !Bazza Formez (bazza.formez@.paradise.net.nz) writes:
> Could someone tell me how to automatically (via a trigger perhaps ?)
> insert records into a related (child) table, based on the insert into
> the parent ?
> I have a situation in my app where I want to add a record into a tabe
> called 'OrderedService'. On insert into this table, I want to insert
> automatically, exactly 5 records into (foreign key) related table
> 'OrderedServiceResult'.
> Note that the only fields that need populating on creation of the child
> are those necessary for the key...
Yes, you could use a trigger for this, at least judging from the information
you have provided. Here is a simple example:
CREATE TRIGGER tbl_tri ON tbl FOR INSERT AS
INSERT othertbl(col1, col2, ...)
SELECT col1, col2, ...
FROM inserted
"inserted" that appears here is a virtual table that holds the rows
that were inserted. This table is only visible within a trigger. There
is a sister table "deleted" which holds deleted rows in a DELETE trigger.
In an UPDATE trigger both tables are populated, "inserted" holding the
new rows, and "deleted" the old rows.
Very important to understand is that a trigger on SQL Server is fired
once per statement. Thus "inserted" can hold many rows.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for this excellent reply... Your effort is appreciated.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CF4D245B5FYazorman@.127.0.0.1...
> Bazza Formez (bazza.formez@.paradise.net.nz) writes:
> Yes, you could use a trigger for this, at least judging from the
> information
> you have provided. Here is a simple example:
> CREATE TRIGGER tbl_tri ON tbl FOR INSERT AS
> INSERT othertbl(col1, col2, ...)
> SELECT col1, col2, ...
> FROM inserted
> "inserted" that appears here is a virtual table that holds the rows
> that were inserted. This table is only visible within a trigger. There
> is a sister table "deleted" which holds deleted rows in a DELETE trigger.
> In an UPDATE trigger both tables are populated, "inserted" holding the
> new rows, and "deleted" the old rows.
> Very important to understand is that a trigger on SQL Server is fired
> once per statement. Thus "inserted" can hold many rows.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>
On excel export
We have couple of group in table on one group when we try to export to excel
it supress the data visible to the user.
Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
as the size of it is as such.
What property can we provide to dynamically increase the row height when we
export.On Jun 22, 3:35 am, NAVIN.D <NAV...@.discussions.microsoft.com> wrote:
> We have couple of group in table on one group when we try to export to excel
> it supress the data visible to the user.
> Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> as the size of it is as such.
> What property can we provide to dynamically increase the row height when we
> export.
It sounds like you are dealing with a merged cells issue in Excel once
the report is exported. The best ways to avoid this is to do the
following:
- Make sure that, if you have multiple tables/controls/matrices above
each other, that they are all touching each other.
- Make sure that the last table/etc control on the report (in Layout
view) is touching the bottom of the report border with no extra space
available.
- Make sure that all controls touch the right-side border of the
report and if you have smaller width controls (in particular
textboxes) in the report, make sure to extend the textbox controls out
to the full width of the report and same width as the widest control
on the report (normally, the table/matrix controls).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||It didnt slove the issue,
I have list within an list and we have section 1 thru 7. We display the list
based the parameters seleted from report for some result section might start
from 3 thru 6 . In such case when try to export to excel i get huge gaps as i
am missing section 1 thru 2 in the report.
How do we avoid those spaces when exporting to excel,
"EMartinez" wrote:
> On Jun 22, 3:35 am, NAVIN.D <NAV...@.discussions.microsoft.com> wrote:
> > We have couple of group in table on one group when we try to export to excel
> > it supress the data visible to the user.
> >
> > Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> > as the size of it is as such.
> > What property can we provide to dynamically increase the row height when we
> > export.
>
> It sounds like you are dealing with a merged cells issue in Excel once
> the report is exported. The best ways to avoid this is to do the
> following:
> - Make sure that, if you have multiple tables/controls/matrices above
> each other, that they are all touching each other.
> - Make sure that the last table/etc control on the report (in Layout
> view) is touching the bottom of the report border with no extra space
> available.
> - Make sure that all controls touch the right-side border of the
> report and if you have smaller width controls (in particular
> textboxes) in the report, make sure to extend the textbox controls out
> to the full width of the report and same width as the widest control
> on the report (normally, the table/matrix controls).
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||I am getting the same issue, I have a mixture of TextBoxes and images in the
row and if I take the image cells away the row heights amend themselves
automatically but when the images are in the row they dont resize.
Any ideas? Any Help? Anything?
Anyone
"NAVIN.D" wrote:
> We have couple of group in table on one group when we try to export to excel
> it supress the data visible to the user.
> Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> as the size of it is as such.
> What property can we provide to dynamically increase the row height when we
> export.|||I am having the same problem on exporting a report containing matrixes, and I
don't know what to try next. This report needs to go into production very
soon.
I inserted a rectangle containing textboxes for column headers in the left
corner of the matrix as instructed. When the matrix is exported to Excel,
there are 2 separate cells where ONE extra pixel, .08in, is inserted between
2 cells, causing a merged cell. There is no apparent reason. I have checked
the alignment and width of the columns and textboxes. I tried it with and
without borders on the textboxes. Nothing is working. When I exported the
matrix after I removed the rectangle and textboxes from the left corner the
export worked correctly. Are there any solutions?
it supress the data visible to the user.
Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
as the size of it is as such.
What property can we provide to dynamically increase the row height when we
export.On Jun 22, 3:35 am, NAVIN.D <NAV...@.discussions.microsoft.com> wrote:
> We have couple of group in table on one group when we try to export to excel
> it supress the data visible to the user.
> Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> as the size of it is as such.
> What property can we provide to dynamically increase the row height when we
> export.
It sounds like you are dealing with a merged cells issue in Excel once
the report is exported. The best ways to avoid this is to do the
following:
- Make sure that, if you have multiple tables/controls/matrices above
each other, that they are all touching each other.
- Make sure that the last table/etc control on the report (in Layout
view) is touching the bottom of the report border with no extra space
available.
- Make sure that all controls touch the right-side border of the
report and if you have smaller width controls (in particular
textboxes) in the report, make sure to extend the textbox controls out
to the full width of the report and same width as the widest control
on the report (normally, the table/matrix controls).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||It didnt slove the issue,
I have list within an list and we have section 1 thru 7. We display the list
based the parameters seleted from report for some result section might start
from 3 thru 6 . In such case when try to export to excel i get huge gaps as i
am missing section 1 thru 2 in the report.
How do we avoid those spaces when exporting to excel,
"EMartinez" wrote:
> On Jun 22, 3:35 am, NAVIN.D <NAV...@.discussions.microsoft.com> wrote:
> > We have couple of group in table on one group when we try to export to excel
> > it supress the data visible to the user.
> >
> > Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> > as the size of it is as such.
> > What property can we provide to dynamically increase the row height when we
> > export.
>
> It sounds like you are dealing with a merged cells issue in Excel once
> the report is exported. The best ways to avoid this is to do the
> following:
> - Make sure that, if you have multiple tables/controls/matrices above
> each other, that they are all touching each other.
> - Make sure that the last table/etc control on the report (in Layout
> view) is touching the bottom of the report border with no extra space
> available.
> - Make sure that all controls touch the right-side border of the
> report and if you have smaller width controls (in particular
> textboxes) in the report, make sure to extend the textbox controls out
> to the full width of the report and same width as the widest control
> on the report (normally, the table/matrix controls).
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||I am getting the same issue, I have a mixture of TextBoxes and images in the
row and if I take the image cells away the row heights amend themselves
automatically but when the images are in the row they dont resize.
Any ideas? Any Help? Anything?
Anyone
"NAVIN.D" wrote:
> We have couple of group in table on one group when we try to export to excel
> it supress the data visible to the user.
> Suppose i have 10 lines to display it supress 8 lines and show only 2 lines
> as the size of it is as such.
> What property can we provide to dynamically increase the row height when we
> export.|||I am having the same problem on exporting a report containing matrixes, and I
don't know what to try next. This report needs to go into production very
soon.
I inserted a rectangle containing textboxes for column headers in the left
corner of the matrix as instructed. When the matrix is exported to Excel,
there are 2 separate cells where ONE extra pixel, .08in, is inserted between
2 cells, causing a merged cell. There is no apparent reason. I have checked
the alignment and width of the columns and textboxes. I tried it with and
without borders on the textboxes. Nothing is working. When I exported the
matrix after I removed the rectangle and textboxes from the left corner the
export worked correctly. Are there any solutions?
Friday, March 23, 2012
On Delete Triggers Question
Hello,
I've a 2 tables that would store Role/RoleMember The definition for those table is the following
Table Role Definition:
Code Snippet
CREATE TABLE [dbo].[Role](
[id] [int] IDENTITY(1,1) NOT NULL,
[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),
[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),
[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),
CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table RoleMember Definition:
Code Snippet
CREATE TABLE [dbo].[RoleMember](
[role_id] [int] NOT NULL,
[member_id] [int] NOT NULL,
CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED
(
[role_id] ASC,
[member_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])
REFERENCES [dbo].[Role] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]
GO
ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])
REFERENCES [dbo].[Role] ([id])
GO
ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]
The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.
The Trigger is define as follow:
Code Snippet
CREATE TRIGGER [dbo].[OnRoleDelete]
ON [dbo].[Role]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)
END
Suprisingly whenever I delete an entry from the Role table the deletion failed with the message
The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.
Is seems that the trigger is never called! Whats wrong with this?
Thanks for help
mavrj
You have to instead of trigger in your case...
Code Snippet
Create TRIGGER [dbo].[OnRoleDelete]
ON [dbo].[Role]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)
DELETE Role WHERE Id IN (SELECT id FROM deleted)
END
|||Thanks for the quick answer
So what is the "FOR DELETE" for?
I assume that the "INSTEAD OF" triggers disable table trigger for the scope
|||For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.
Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.
On Delete Triggers Question
Hello,
I've a 2 tables that would store Role/RoleMember The definition for those table is the following
Table Role Definition:
Code Snippet
CREATE TABLE [dbo].[Role](
[id] [int] IDENTITY(1,1) NOT NULL,
[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),
[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),
[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),
CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table RoleMember Definition:
Code Snippet
CREATE TABLE [dbo].[RoleMember](
[role_id] [int] NOT NULL,
[member_id] [int] NOT NULL,
CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED
(
[role_id] ASC,
[member_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])
REFERENCES [dbo].[Role] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]
GO
ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])
REFERENCES [dbo].[Role] ([id])
GO
ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]
The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.
The Trigger is define as follow:
Code Snippet
CREATE TRIGGER [dbo].[OnRoleDelete]
ON [dbo].[Role]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)
END
Suprisingly whenever I delete an entry from the Role table the deletion failed with the message
The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.
Is seems that the trigger is never called! Whats wrong with this?
Thanks for help
mavrj
You have to instead of trigger in your case...
Code Snippet
Create TRIGGER [dbo].[OnRoleDelete]
ON [dbo].[Role]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)
DELETE Role WHERE Id IN (SELECT id FROM deleted)
END
|||Thanks for the quick answer
So what is the "FOR DELETE" for?
I assume that the "INSTEAD OF" triggers disable table trigger for the scope
|||For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.
Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.
On Delete Triggers Question
Hello,
I've a 2 tables that would store Role/RoleMember The definition for those table is the following
Table Role Definition:
Code Snippet
CREATE TABLE [dbo].[Role](
[id] [int] IDENTITY(1,1) NOT NULL,
[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),
[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),
[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),
CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table RoleMember Definition:
Code Snippet
CREATE TABLE [dbo].[RoleMember](
[role_id] [int] NOT NULL,
[member_id] [int] NOT NULL,
CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED
(
[role_id] ASC,
[member_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])
REFERENCES [dbo].[Role] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]
GO
ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])
REFERENCES [dbo].[Role] ([id])
GO
ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]
The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.
The Trigger is define as follow:
Code Snippet
CREATE TRIGGER [dbo].[OnRoleDelete]
ON [dbo].[Role]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)
END
Suprisingly whenever I delete an entry from the Role table the deletion failed with the message
The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.
Is seems that the trigger is never called! Whats wrong with this?
Thanks for help
mavrj
You have to instead of trigger in your case...
Code Snippet
Create TRIGGER [dbo].[OnRoleDelete]
ON [dbo].[Role]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)
DELETE Role WHERE Id IN (SELECT id FROM deleted)
END
|||Thanks for the quick answer
So what is the "FOR DELETE" for?
I assume that the "INSTEAD OF" triggers disable table trigger for the scope
|||For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.
Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.
On Delete Triggers Question
Hello,
I've a 2 tables that would store Role/RoleMember The definition for those table is the following
Table Role Definition:
Code Snippet
CREATE TABLE [dbo].[Role](
[id] [int] IDENTITY(1,1) NOT NULL,
[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),
[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),
[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),
CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Table RoleMember Definition:
Code Snippet
CREATE TABLE [dbo].[RoleMember](
[role_id] [int] NOT NULL,
[member_id] [int] NOT NULL,
CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED
(
[role_id] ASC,
[member_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])
REFERENCES [dbo].[Role] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]
GO
ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])
REFERENCES [dbo].[Role] ([id])
GO
ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]
The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.
The Trigger is define as follow:
Code Snippet
CREATE TRIGGER [dbo].[OnRoleDelete]
ON [dbo].[Role]
FOR DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)
END
Suprisingly whenever I delete an entry from the Role table the deletion failed with the message
The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.
Is seems that the trigger is never called! Whats wrong with this?
Thanks for help
mavrj
You have to instead of trigger in your case...
Code Snippet
Create TRIGGER [dbo].[OnRoleDelete]
ON [dbo].[Role]
INSTEAD OF DELETE
AS
BEGIN
SET NOCOUNT ON
DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)
DELETE Role WHERE Id IN (SELECT id FROM deleted)
END
|||Thanks for the quick answer
So what is the "FOR DELETE" for?
I assume that the "INSTEAD OF" triggers disable table trigger for the scope
|||For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.
Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.
Subscribe to:
Posts (Atom)