Showing posts with label specific. Show all posts
Showing posts with label specific. Show all posts

Wednesday, March 21, 2012

how to loop through several excel sheets in a file in integration services

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Moving thread to the Intergration Services forum. They may be able to help out.

|||

Trisha1802 wrote:

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Unfortunately its not possible to loop through sheets unless you know the names of them and you know that they won't change. If those conditions are satisfied then you can use the ForEach item enumerator in the Foreach Loop.

An Excel Sheet enumerator would be a nice addition. Perhaps you could request it at Microsoft Connect?

-Jamie

|||

Interesting situation. In SSIS Excel Source BOL section (http://msdn2.microsoft.com/en-us/library/ms141683.aspx) there is a link "HOWTO: Use ADO with Excel Data from Visual Basic or VBA" that has some sample code to "Enumerate Tables and Fields and Their Properties". I wonder if you could use some of that code inside of a script task to loop through all the sheet in your file and write their name in a flat file. Then it would be pretty easy to use a ForEach loop container with a data flow that connects to the excel file and runs a query like 'Select * from <SSIS variable with the sheet name>'

I have no idea how to write such script component and don't know how feasible that could be; so you do the research.

Rafael Salas

|||

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

|||Is there a link where I can see the use of the Foreach item enumerator?|||

Trisha1802 wrote:

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

You have to type them in at design-time basically. That's why I said you need to know them. Its not particularly clever or dynamic but it'll work.

-Jamie

|||

Just to add my 2cents to the request list -- I am trying to export from SQL Server to a single workbook with about 100 worksheets. I was hoping to be able to loop through a two-column recordset from a static SQL Server table with the names of the worksheets and the SQL command to extract/export data into each worksheet. The name and path of the workbook remain constant. However, it appears that the "Excel Destination" Data Flow does not accept variables for either the name of the worksheet (or workbook). That means I have to hard-code everything related to the export to 100 worksheets/workbooks. I was hoping to avoid needing to create 100 little export file tasks in the package, but that appears to be my only recourse. Any other ideas?

-Andre Chan

|||

You don't need to create 100 litle exports; you need to change the data access mode of your Excel Destination component to 'Table Name or View Name variable". Then you need to use a SSIS variable to hold the Excel sheet name you want to write into. You can use a forErachLoop container in the control flow to go over the 100 sheet names.

Rafael Salas

|||

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

|||

Andre Chan wrote:

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

I see the difference now. I still think you can write all inside of a single workbook, and you are very close to make it. So, if you already succeed looping through the 100 data sets; then trick I think is to make the package to write into the same file but different sheet each time. For that, try to create the 100 sheets in your excel destination file; add a variable to the package called, let's say DestinationSheet, and for each iteration change the value of that variable to one of the 100 sheet your destination file has (according with the set of data you are processing of course).You will not use the FileSystemTask to rename any files; because you are writing into a single file.

Rafael Salas

|||

Thanks, I did get my package finally to run, but I do have one last problem specified below in the last paragraph. As you might expect, my problems were somewhat "trivial" and/or only remotely related. First, I had to specify the worksheet name with $ attached at the end. Perhaps because I had renamed the worksheets within the workbook along the way? That's a software artifact that would be nice to clear up, as there was no helpful error message provided by SSIS, only that the "database" could not be found. That's where I got confused by the wording of the error message -- it never said "worksheet". Second, my source table data had columns specified as "varchar" but Excel Destination task widget only accepts "nvarchar". Changing the datatypes in the source columns to nvarchar fixed that problem. At least SSIS did give an obliquely useful error message in this case (but didn't explicitly tell me to go change the source table datatypes, only that varchar and nvarchar did not "match").

There was also a bit of necessary work-around because, within the ForEach container, in order to specify the source column mappings returned via my stored procedure from the "SQLCommand" variable, or for Excel to recognize its worksheet name/column mappings via its "WorksheetName" variable, I had to pre-define default values for the variables. It would have been nice (but quite advanced) for SSIS to anticipate the first row from the initial SQL recordset that fills the ForEach command.

I have one last problem. If the Excel worksheet already contains data, SSIS appears to be insert new rows for the data (or append it to the existing data -- I haven't figured that out yet). Is there any way to get SSIS to clear the worksheet before dumping data into the worksheet. For example, when setting up a PivotTable or other query from within Excel itself, there are multiple options for completely clearing the existing data first, inserting new rows and clearing rows, or just inserting new rows. I would just like the data cleared, because I don't want to have any VLOOKUP references to this spreadsheet to get corrupted. Rright now, each worksheet has a few hundred rows, but the VLOOKUP references rows 2 through 10000 (ignoring header row), just to ensure it gets the whole data range.

Thanks,

Andre

|||

The last bit of pre-processing I want to do in Excel, I know how to do in an Excel macro, but not in SSIS. Can anyone adapt this into an SSIS Script task? Here is the macro code snippet:

Sub ClearWorksheets()

With Application

.Calculation = xlManual

.EnableEvents = False

Worksheets("MyData").Range("A:H").Clear

.EnableEvents = True

.Calculation = xlAutomatic

End With

End Sub

After the range is cleared, then I can fill that same range. Ideally, the worksheet name and workbook name would both come from a variable.

Thanks,

Andre

|||

Andre,

I actually don't know if SSIS can 'delete' or 'truncate' an excel sheet; What i know though, is SSIS is accessing the excel file using and OLE DB provider for Microsoft Jet; so each sheet is treated as table. With this said; do yourself a favor, research arround Microsoft Jet 4.0, its OLE DB provider and may be you will see your options around what you want to acomplish. that may help you also to understand the limitations of the data types.

If truncating or deleting the existing rows before loading the new data is not possible; you could have an empty excel file with the required structure in a specifc location and then you just create a new copy to be used as the target every time the package runs.

I hope this takes you one step further on all this.

Rafael Salas

|||

Hi Rafael,

Trying to use a DELETE statement against the Excel file yields an error message (the syntax varies based on where I click OK), but generally all say something to the effect of "Deleting data in a linked table is not supported by this ISAM. (Microsoft JET Database Engine)".

So my only recourse seems to be, as you suggested, to (1) create a "blank" Excel template with all the worksheets I need, (2) when my package runs, create a working copy of the template, (3) fill the copy with data on each worksheet, (4) archive the "published" Excel file, if it exists, and (5) copy/rename the working copy to the published filename.

Thanks for your comments, your support and encouragement are appreciated; and if any Microsoft people are following this, it would be nice to address all of the SSIS to Excel issues raised in this thread.

Regards,

Andre Chan

how to loop through several excel sheets in a file in integration services

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Moving thread to the Intergration Services forum. They may be able to help out.

|||

Trisha1802 wrote:

Hello, I'm new at Integration services and I have an excel file with information in several worksheets. I want to loop through some specific sheets to retrieve the data and save it in a database table. I know how to retrieve the data from one sheet, but I don't know how to do it for several sheets. Any ideas?...I would appreciate any help.

Unfortunately its not possible to loop through sheets unless you know the names of them and you know that they won't change. If those conditions are satisfied then you can use the ForEach item enumerator in the Foreach Loop.

An Excel Sheet enumerator would be a nice addition. Perhaps you could request it at Microsoft Connect?

-Jamie

|||

Interesting situation. In SSIS Excel Source BOL section (http://msdn2.microsoft.com/en-us/library/ms141683.aspx) there is a link "HOWTO: Use ADO with Excel Data from Visual Basic or VBA" that has some sample code to "Enumerate Tables and Fields and Their Properties". I wonder if you could use some of that code inside of a script task to loop through all the sheet in your file and write their name in a flat file. Then it would be pretty easy to use a ForEach loop container with a data flow that connects to the excel file and runs a query like 'Select * from <SSIS variable with the sheet name>'

I have no idea how to write such script component and don't know how feasible that could be; so you do the research.

Rafael Salas

|||

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

|||Is there a link where I can see the use of the Foreach item enumerator?|||

Trisha1802 wrote:

Thank you very much for your reply Jamie.

I haven't used the Foreach item enumerator...could you please explain to me a little bit more how to pass the sheet name to the container?. I saw that I have to add some columns and I have the ones I need...but I don't know how to do the next part.

You have to type them in at design-time basically. That's why I said you need to know them. Its not particularly clever or dynamic but it'll work.

-Jamie

|||

Just to add my 2cents to the request list -- I am trying to export from SQL Server to a single workbook with about 100 worksheets. I was hoping to be able to loop through a two-column recordset from a static SQL Server table with the names of the worksheets and the SQL command to extract/export data into each worksheet. The name and path of the workbook remain constant. However, it appears that the "Excel Destination" Data Flow does not accept variables for either the name of the worksheet (or workbook). That means I have to hard-code everything related to the export to 100 worksheets/workbooks. I was hoping to avoid needing to create 100 little export file tasks in the package, but that appears to be my only recourse. Any other ideas?

-Andre Chan

|||

You don't need to create 100 litle exports; you need to change the data access mode of your Excel Destination component to 'Table Name or View Name variable". Then you need to use a SSIS variable to hold the Excel sheet name you want to write into. You can use a forErachLoop container in the control flow to go over the 100 sheet names.

Rafael Salas

|||

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

|||

Andre Chan wrote:

To clarify a bit more -- within my For Each loop container, I could always use Excel Destination task to write to a single "dummy" filename, and then use the File System task within the same For Each loop container to re-name the file to whatever I want (using my variable?). In this case, I end up with 100 workbooks rather than 1 workbook with 100 worksheets, but that is better than nothing. Note that I need these worksheets because they are referenced via VLOOKUP from another well-formatted workbook.

The bigger problem I am having now is trying to populate these workbook/worksheets using a dynamic variable SQL command. I want to use a single stored procedure with specified parameters to fill each workbook/worksheet, to make it easier to manage in the database. These SQL commands are loaded into a SQL Server table. It seems built-in options in SSIS are to use a specific pre-defined, pre-created view or table name. But in this case then I need to create 100 different tables or views in the database, and that is what I am trying to avoid, because editing the logic would require updating all 100 views (and perhaps creating/updating all 100 tasks in the package if the view/table names change).

-Andre Chan

I see the difference now. I still think you can write all inside of a single workbook, and you are very close to make it. So, if you already succeed looping through the 100 data sets; then trick I think is to make the package to write into the same file but different sheet each time. For that, try to create the 100 sheets in your excel destination file; add a variable to the package called, let's say DestinationSheet, and for each iteration change the value of that variable to one of the 100 sheet your destination file has (according with the set of data you are processing of course).You will not use the FileSystemTask to rename any files; because you are writing into a single file.

Rafael Salas

|||

Thanks, I did get my package finally to run, but I do have one last problem specified below in the last paragraph. As you might expect, my problems were somewhat "trivial" and/or only remotely related. First, I had to specify the worksheet name with $ attached at the end. Perhaps because I had renamed the worksheets within the workbook along the way? That's a software artifact that would be nice to clear up, as there was no helpful error message provided by SSIS, only that the "database" could not be found. That's where I got confused by the wording of the error message -- it never said "worksheet". Second, my source table data had columns specified as "varchar" but Excel Destination task widget only accepts "nvarchar". Changing the datatypes in the source columns to nvarchar fixed that problem. At least SSIS did give an obliquely useful error message in this case (but didn't explicitly tell me to go change the source table datatypes, only that varchar and nvarchar did not "match").

There was also a bit of necessary work-around because, within the ForEach container, in order to specify the source column mappings returned via my stored procedure from the "SQLCommand" variable, or for Excel to recognize its worksheet name/column mappings via its "WorksheetName" variable, I had to pre-define default values for the variables. It would have been nice (but quite advanced) for SSIS to anticipate the first row from the initial SQL recordset that fills the ForEach command.

I have one last problem. If the Excel worksheet already contains data, SSIS appears to be insert new rows for the data (or append it to the existing data -- I haven't figured that out yet). Is there any way to get SSIS to clear the worksheet before dumping data into the worksheet. For example, when setting up a PivotTable or other query from within Excel itself, there are multiple options for completely clearing the existing data first, inserting new rows and clearing rows, or just inserting new rows. I would just like the data cleared, because I don't want to have any VLOOKUP references to this spreadsheet to get corrupted. Rright now, each worksheet has a few hundred rows, but the VLOOKUP references rows 2 through 10000 (ignoring header row), just to ensure it gets the whole data range.

Thanks,

Andre

|||

The last bit of pre-processing I want to do in Excel, I know how to do in an Excel macro, but not in SSIS. Can anyone adapt this into an SSIS Script task? Here is the macro code snippet:

Sub ClearWorksheets()

With Application

.Calculation = xlManual

.EnableEvents = False

Worksheets("MyData").Range("A:H").Clear

.EnableEvents = True

.Calculation = xlAutomatic

End With

End Sub

After the range is cleared, then I can fill that same range. Ideally, the worksheet name and workbook name would both come from a variable.

Thanks,

Andre

|||

Andre,

I actually don't know if SSIS can 'delete' or 'truncate' an excel sheet; What i know though, is SSIS is accessing the excel file using and OLE DB provider for Microsoft Jet; so each sheet is treated as table. With this said; do yourself a favor, research arround Microsoft Jet 4.0, its OLE DB provider and may be you will see your options around what you want to acomplish. that may help you also to understand the limitations of the data types.

If truncating or deleting the existing rows before loading the new data is not possible; you could have an empty excel file with the required structure in a specifc location and then you just create a new copy to be used as the target every time the package runs.

I hope this takes you one step further on all this.

Rafael Salas

|||

Hi Rafael,

Trying to use a DELETE statement against the Excel file yields an error message (the syntax varies based on where I click OK), but generally all say something to the effect of "Deleting data in a linked table is not supported by this ISAM. (Microsoft JET Database Engine)".

So my only recourse seems to be, as you suggested, to (1) create a "blank" Excel template with all the worksheets I need, (2) when my package runs, create a working copy of the template, (3) fill the copy with data on each worksheet, (4) archive the "published" Excel file, if it exists, and (5) copy/rename the working copy to the published filename.

Thanks for your comments, your support and encouragement are appreciated; and if any Microsoft people are following this, it would be nice to address all of the SSIS to Excel issues raised in this thread.

Regards,

Andre Chan

sql

Monday, March 19, 2012

How to log disconnects?

Check the app logs first, are they logging query/conection
errors,timeouts,etc?
And look for specific errors (ADO,OLE-DB,etc)
Will wrote:
> I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!That kind of logging 'should' happen on the client. For a limited time span,
you may wish to use Profiler.
Application timeout is the first thing to explore. Check the ADO connection
timeout (I think the default is 60 seconds -but I don't remember with
certainty.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Will" <wfj5444@.gmail.com> wrote in message
news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...
>I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!
>|||I need to log user disconnects for a specific DB. I do not know how to
do this relabily. The application owner is under the impression that
our SQL cluster is disconnecting their users. Which I am fairly
confident is not the case.
Any ideas on how to do this? Specifically disconnects that are not
'requested' but either forced or lost.
Thanks!|||Check the app logs first, are they logging query/conection
errors,timeouts,etc?
And look for specific errors (ADO,OLE-DB,etc)
Will wrote:
> I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!|||That kind of logging 'should' happen on the client. For a limited time span,
you may wish to use Profiler.
Application timeout is the first thing to explore. Check the ADO connection
timeout (I think the default is 60 seconds -but I don't remember with
certainty.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Will" <wfj5444@.gmail.com> wrote in message
news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...
>I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!
>|||Right,
That is the issue only one application out of many is seeing the issue.
It does not sound like an SQL Server issue. They are seeing the
disconnects on their side. We have no other users reporting any other
issues or symptoms.
Thanks,
Will
Arnie Rowland wrote:[vbcol=seagreen]
> That kind of logging 'should' happen on the client. For a limited time spa
n,
> you may wish to use Profiler.
> Application timeout is the first thing to explore. Check the ADO connectio
n
> timeout (I think the default is 60 seconds -but I don't remember with
> certainty.)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...|||Right,
That is the issue only one application out of many is seeing the issue.
It does not sound like an SQL Server issue. They are seeing the
disconnects on their side. We have no other users reporting any other
issues or symptoms.
Thanks,
Will
Arnie Rowland wrote:[vbcol=seagreen]
> That kind of logging 'should' happen on the client. For a limited time spa
n,
> you may wish to use Profiler.
> Application timeout is the first thing to explore. Check the ADO connectio
n
> timeout (I think the default is 60 seconds -but I don't remember with
> certainty.)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...

How to log disconnects?

I need to log user disconnects for a specific DB. I do not know how to
do this relabily. The application owner is under the impression that
our SQL cluster is disconnecting their users. Which I am fairly
confident is not the case.
Any ideas on how to do this? Specifically disconnects that are not
'requested' but either forced or lost.
Thanks!Check the app logs first, are they logging query/conection
errors,timeouts,etc?
And look for specific errors (ADO,OLE-DB,etc)
Will wrote:
> I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!|||That kind of logging 'should' happen on the client. For a limited time span,
you may wish to use Profiler.
Application timeout is the first thing to explore. Check the ADO connection
timeout (I think the default is 60 seconds -but I don't remember with
certainty.)
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Will" <wfj5444@.gmail.com> wrote in message
news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...
>I need to log user disconnects for a specific DB. I do not know how to
> do this relabily. The application owner is under the impression that
> our SQL cluster is disconnecting their users. Which I am fairly
> confident is not the case.
> Any ideas on how to do this? Specifically disconnects that are not
> 'requested' but either forced or lost.
> Thanks!
>|||Right,
That is the issue only one application out of many is seeing the issue.
It does not sound like an SQL Server issue. They are seeing the
disconnects on their side. We have no other users reporting any other
issues or symptoms.
Thanks,
Will
Arnie Rowland wrote:
> That kind of logging 'should' happen on the client. For a limited time span,
> you may wish to use Profiler.
> Application timeout is the first thing to explore. Check the ADO connection
> timeout (I think the default is 60 seconds -but I don't remember with
> certainty.)
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Will" <wfj5444@.gmail.com> wrote in message
> news:1151588003.074757.326780@.m73g2000cwd.googlegroups.com...
> >I need to log user disconnects for a specific DB. I do not know how to
> > do this relabily. The application owner is under the impression that
> > our SQL cluster is disconnecting their users. Which I am fairly
> > confident is not the case.
> >
> > Any ideas on how to do this? Specifically disconnects that are not
> > 'requested' but either forced or lost.
> >
> > Thanks!
> >

how to locate a table that contains a specific trigger?

Hello,
the following tsql
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
dependent upon. I need to locate the table that contains this specific
trigger.
I tried
select DISTINCT OBJECT_NAME([id]) FROM sysdepends
WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
but this did not yield anything (empty).
How can I locate the table which contains this trigger?
Thanks,
RichReverse id and depid:
select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
"Rich" wrote:

> Hello,
> the following tsql
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) = 'tbl_CompanyChangeHistory'
> displays a list of UDFs and a trigger that tbl_CompanyChangeHistory is
> dependent upon. I need to locate the table that contains this specific
> trigger.
> I tried
> select DISTINCT OBJECT_NAME([id]) FROM sysdepends
> WHERE OBJECT_NAME([depid]) like 't_ChgHistory_Write%'
> but this did not yield anything (empty).
> How can I locate the table which contains this trigger?
> Thanks,
> Rich
>|||Thanks for your reply. I had the [id] and [depid] columns switched around
which was incorrect. But your solution retrieved the dependent table and a
dependent UDF (still good stuff).
Here is something else I tried that actually retrieve the table I was
looking for
select OBJECT_NAME([parent_obj]) FROM sysobjects
WHERE [id] = 1467920351
The [id] here is the [id] of the trigger which I retrieved like this:
select * from sysobjects where xtype ='tr'
and name = 't_CompaniesWrite'
"Mark Williams" wrote:
> Reverse id and depid:
> select DISTINCT OBJECT_NAME([depid]) FROM sysdepends
> WHERE OBJECT_NAME([id]) like 't_ChgHistory_Write%' --trigger name?
>
> --
>
> "Rich" wrote:
>|||Rich
I'd not rely on sysdepends table ,instead take a look at Vyas's examle
CREATE PROCEDURE sp_FindObject
@.SearchString varchar (255)
AS
SET nocount ON
DECLARE @.Name varchar(255)
DECLARE @.Text nvarchar(4000)
CREATE TABLE #Objs
( ObjName varchar (255))
DECLARE Obj CURSOR
FOR
SELECT [NAME]
FROM
(
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='P' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='V' )
AND so.id = sc.id
UNION ALL
SELECT [NAME],[TEXT] FROM sysobjects so, syscomments sc
WHERE (so.xtype ='TR' )
AND so.id = sc.id
) AS Der WHERE [TEXT] LIKE @.SearchString
OPEN Obj
FETCH Next FROM Obj INTO @.Name
WHILE @.@.FETCH_STATUS=0
BEGIN
INSERT INTO #Objs VALUES (@.Name)
FETCH Next FROM Obj INTO @.Name
END
CLOSE Obj
DEALLOCATE Obj
SELECT objname FROM #Objs GROUP BY objname
DROP TABLE #Objs
GO
EXEC sp_FindObject '%HOST_ID()%'
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:BF61EDA5-4130-40E8-8FA5-2DF3938B0E9C@.microsoft.com...
> Thanks for your reply. I had the [id] and [depid] columns switched around
> which was incorrect. But your solution retrieved the dependent table and
> a
> dependent UDF (still good stuff).
> Here is something else I tried that actually retrieve the table I was
> looking for
> select OBJECT_NAME([parent_obj]) FROM sysobjects
> WHERE [id] = 1467920351
> The [id] here is the [id] of the trigger which I retrieved like this:
> select * from sysobjects where xtype ='tr'
> and name = 't_CompaniesWrite'
> "Mark Williams" wrote:
>

Monday, March 12, 2012

How to list all filegroups for a specific database?

Hi, all here,

Would please any expert here give me any guidance for how to list all filegroups for a specific database within SQL Server 2005?

Thanks a lot in advance for that.

With best regards,

Yours sincerely,

Within the database use the following command:

SELECT * FROM sys.filegroups

HTH, Jens Suessmeyer.


http://www.sqlserver2005.de

|||

use northwind
select * from sysfiles

select * from sysfilegroups

|||

Hi, Jens and Joeydj, thanks a lot for your guidance.

With best regards,

Yours sincerely,