Need to create and access a SQL server database that will reside on an external hard drive

I need to create a SQL server database and add some tables to it. Then access it with a C# application. The problem is that the new SQL server database and it's tables must reside on an external hard drive. How do I point SQL server to this external drive, so that I can create a database on this drive and then create tables and access data on it?

Here are two articles with examples.

http://support.microsoft.com/default.aspx?scid=kb;en-us;q307283&id=kb;en-us;q307283&ln=en-us&rnk=1&sd=msdn&fr=0&qry=q307283&src=dhcs_mspss_msdn_srch&spr=msall

http://www.codeproject.com/cs/database/CreateDB.asp

|||

Hi Steve,

I am interested in setting this up through SQL Server management studio. Your refered links talk about doing it programatically. Neat stuff, but I need to do this through SQL Server management studio. Possibly someone has some more information on this?

|||

You can create a database locally, copy the mdf and ldf files to where you want them, and then attach to the files in the new location. Here is a link in the online help:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6732a431-cdef-4f1e-9262-4ac3b77c275e.htm

Or, right click the server icon and select properties.

Select the Database Settings Page. On that page you can specify what directories you want your data to be in.

|||Once the database and tables have been set up on the external drive, do you know if they can then be viewed and queried through SQL Server Query Analyzer?|||Once the database and tables have been set up on the external drive, do you know if they can then be viewed and queried through SQL Server Management Studio?|||

I tried what I suggested and it didn't work. The Management studio would not let me browse away from the PC it was installed on. I tried hard coding some paths to a remote drive but that didn't work.

Finally, I got this to work (from articlehttp://www.sqlteam.com/item.asp?ItemID=128)

DBCC TRACEON (1807);
GO

USE master;

GO
CREATE DATABASE Test
ON (FILENAME = '\\oak\C\temp\DataBase\SGWTest.mdf'),
(FILENAME = '\\oak\C\temp\DataBase\SGWTest_Log.ldf')
FOR ATTACH;
GO

Once attached, the database will act like any other database. However, I'm beginning to think Microsoft wants the engine and files to be on the same machine. It probably has something to do with performance and reliability :)

|||

Hi Steve. I am just getting back to your post. Thank you for your research and help on this. I am really greatfull to you. You have my vote for a Microsoft MVP! If I knew where to vote, I would vote for you.

I will read the article you suggested. With regards to the above script DBC TRACEON (1807) ...etc. Did you execute this as a SQL stored procedure?

If speed is an issue then I suppose I could always go with an external drive. It appears to me that possibly I can have a seperate instance of SQL server stored on this external drive, and then run that instance off of the external drive. The problem with this though is that I would have to power it up and down. Necessary I suppose if I want to have an external database.

Here is an example of a drive I was thinking about, if I had to go the route of getting an external hard drive.http://www.microcenter.com/byos/byos_single_product_results.phtml?product_id=249658

Talk to you later,

Ralph

sql

Need to create <Query> statement programmatically

I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.

Here is the data source and dataset info I am using:

DataSource
Name: WebService
Type: XML
Connection string: http://localhost/myWeb/myWebService.asmx
Credentials: No credentials

DataSet
Query tab:
Name: WebService
Data source: WebService
Command type: Text
Query String: <Query><SoapAction>......</SoapAction></Query>

Here is a sample of the <Query> string that I use when I first build the report:

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>

</Parameters>
</Method>

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>

When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".

Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!

Thanks in advance for your help!

Hi Videl,

There are a couple of ways you could accomplish this. But the best way is to take advantage of the QueryString being an expression. You could have the querystring reference a report parameter, and set this parameter to the new xml query with your web application when you run the report.

Example Dataset Query string: =Parameters!QueryParameter.Value

Or since you are just changing the sql query parameter, you could create a dataset parameter that references the report parameter. To do this you need to create a dataset parameter with the same name as the webmethod parameter. In this case, this would be sql. And the value for the dataset parameter would be the same expression above, only that in this case the value would just be the modified sql instead of the entire Xml query.

Another way is to directly modify the RDL and re-publish it, but this would not be very performant and would cause the report to be only useable by one user at a time.|||

Hi Ian,

OK, I set the dataset query string to Parameters!sql.Value, and created a report parameter called "sql". I set the default value of this parameter to :

="<Query><SoapAction>http://tempuri.org/GetDataset</SoapAction><Method Namespace='http://tempuri.org/' Name='GetDataset'><Parameters><Parameter Name='sql' Type='String'><DefaultValue>Select * From Reports</DefaultValue></Parameter></Parameters></Method><ElementPath IgnoreNamespaces='true'>GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath></Query>"

When I preview the report, I get the following error message:

An error occurred during local report processing. The definition of the report '/myReport/' is invalid. the Value expression for the textbox 'xyz' refers to the field 'xyz'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

It looks like it is not finding the data before it tries to populate the fields on the report.

I also tried using a dataset paramter, and that looked maybe easier. I created a dataset parameter called sql, and set it's default value to the sql statement I want. Then I deleted the default value of the sql statement in the query statement, and ran it. I was prompted for the sql statement, and it ran fine. However, I am not sure how to set that value from code, since that is a dataset parameter and not a report parameter. That looks like an easier solution, but could you please tell me how to set that dataset parameter from my .NET code? Thanks!!!

Marlene

|||I think using a datset parameter is easier and less error prone, then dealing with the entire Xml query. However, you should be able to use the other approach. The reason you are getting the exception is that the fields are no longer defined in the dataset, since they were tied to the query. You need to manually add the fields to the dataset by right-clicking on the dataset in the Datasets panel in the Report Designer, and click add in the context menu.

Regarding your question, to set the dataset parameter programmatically, you need to use a report parameter as the value of the dataset parameter, like you did with the query expression, and set the report parameter in your call to create render the report.

Ian|||

Hi Ian,

I did as you suggested, manually adding the fields, and creating a reports parameter called "sql". Now the dataset parameter "sql" is set to Parameters!sql.value.

Now when I run the report, I get the right number of rows, but they are all blank. I have tried setting the field values to the dataset field value, and I have tried leaving them blank, and get the same results either way. How do I get them to mesh up?

|||Yes, they need to be mapped to the dataset field names. The issue you describe is usually caused by custom fields not being mapped to existing dataset fields. Can you make sure that the fields you are mapping to exist in the dataset returned by the Xml Query? Since this is a dataset being returned, can you try adding the following custom dataset field,

Name = RowID, DatabaseField = id

This should map RowID to the id attibute of the NewTable element. This also assumes that your ElementPath describes the path to the table level, without specifying the fields to retrieve. For example,

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results{}/NewTable</ElementPath>|||

Hi,

I didn't quite understand that.

I do have the <ElementPath> tag that you described in my <query> string, except it is slightly different:

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath

When I replaced /Results with /Results{}/NewTable, data was no longer returned to the dataset, so I changed it back.

I added a dataset field named "RowID", set it's type to "Database Field" and set the value to "=id" (I did not type in the quotes). However the data still did not display.

FYI, here is the entire query string I am using. I include it since it includes that elementPath part that stubbornly refuses to play nice with NewTable:
-

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue></DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>
-
Here is a sample output from my webservice
-
<?xml version="1.0" encoding="utf-8" ?>

- <DataSet xmlns="http://tempuri.org/">

- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

- <xs:complexType>

- <xs:choice minOccurs="0" maxOccurs="unbounded">

- <xs:element name="Results">

- <xs:complexType>

- <xs:sequence>

<xs:element name="ReportID" type="xs:int" minOccurs="0" />

<xs:element name="ReportName" type="xs:string" minOccurs="0" />

<xs:element name="ReportFriendlyName" type="xs:string" minOccurs="0" />

<xs:element name="ReportDescription" type="xs:string" minOccurs="0" />

<xs:element name="Display" type="xs:boolean" minOccurs="0" />

<xs:element name="DataViewName" type="xs:string" minOccurs="0" />

<xs:element name="Example1" type="xs:boolean" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

- <NewDataSet xmlns="">

- <Results diffgr:id="Results1" msdata:rowOrder="0">

<ReportID>10</ReportID>

<ReportName>Reports_ByReportID1</ReportName>

<ReportFriendlyName>Reports by ID, w/ Dataset Parameter</ReportFriendlyName>

<ReportDescription>List of reports by report ID. This example uses a dataset parameter, which is referenced in the query string.</ReportDescription>

<Display>true</Display>

<DataViewName>Reports</DataViewName>

<Example1>false</Example1>

</Results>

</NewDataSet>

</diffgr:diffgram>

</DataSet>
--

And here are all my dataset fields:

FieldName Type Value
ReportID Database field =Fields!ReportID.Value
ReportName Database field =Fields!ReportName.Value
ReportFriendlyName Database field =Fields!ReportFriendlyName.Value

RowID Database field =id

|||Your element path is correct--you are describing the relationships to the table level. I mixed the name of your table, Results, with that of my dataset, also Results.

When creating custom Dataset fields, the value should be just the database field name. The value in the field is treated as a literal, so Reporting services is trying to match your custom dataset field, ReportID, to a field from the dataset returned from the query named "=Fields!ReportID.Value"

Try using these custom dataset field.

FieldNameType ValueReportIDDatabase field ReportIDReportNameDatabase field ReportNameReportFriendlyNameDatabase field ReportFriendlyName

More information on custom dataset fields:
http://msdn2.microsoft.com/en-us/library/ms189677.aspx|||You are the best!!!! |||When manually defining the fields in the dataset (e.g. programmatic select statement), how do you get them to 'stick'? When previewing the report, they get wiped out of the dataset once in a while.
sql

Need to create <Query> statement programmatically

I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.

Here is the data source and dataset info I am using:

DataSource
Name: WebService
Type: XML
Connection string: http://localhost/myWeb/myWebService.asmx
Credentials: No credentials

DataSet
Query tab:
Name: WebService
Data source: WebService
Command type: Text
Query String: <Query><SoapAction>......</SoapAction></Query>

Here is a sample of the <Query> string that I use when I first build the report:

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>

</Parameters>
</Method>

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>

When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".

Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!

Thanks in advance for your help!

Hi Videl,

There are a couple of ways you could accomplish this. But the best way is to take advantage of the QueryString being an expression. You could have the querystring reference a report parameter, and set this parameter to the new xml query with your web application when you run the report.

Example Dataset Query string: =Parameters!QueryParameter.Value

Or since you are just changing the sql query parameter, you could create a dataset parameter that references the report parameter. To do this you need to create a dataset parameter with the same name as the webmethod parameter. In this case, this would be sql. And the value for the dataset parameter would be the same expression above, only that in this case the value would just be the modified sql instead of the entire Xml query.

Another way is to directly modify the RDL and re-publish it, but this would not be very performant and would cause the report to be only useable by one user at a time.|||

Hi Ian,

OK, I set the dataset query string to Parameters!sql.Value, and created a report parameter called "sql". I set the default value of this parameter to :

="<Query><SoapAction>http://tempuri.org/GetDataset</SoapAction><Method Namespace='http://tempuri.org/' Name='GetDataset'><Parameters><Parameter Name='sql' Type='String'><DefaultValue>Select * From Reports</DefaultValue></Parameter></Parameters></Method><ElementPath IgnoreNamespaces='true'>GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath></Query>"

When I preview the report, I get the following error message:

An error occurred during local report processing. The definition of the report '/myReport/' is invalid. the Value expression for the textbox 'xyz' refers to the field 'xyz'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

It looks like it is not finding the data before it tries to populate the fields on the report.

I also tried using a dataset paramter, and that looked maybe easier. I created a dataset parameter called sql, and set it's default value to the sql statement I want. Then I deleted the default value of the sql statement in the query statement, and ran it. I was prompted for the sql statement, and it ran fine. However, I am not sure how to set that value from code, since that is a dataset parameter and not a report parameter. That looks like an easier solution, but could you please tell me how to set that dataset parameter from my .NET code? Thanks!!!

Marlene

|||I think using a datset parameter is easier and less error prone, then dealing with the entire Xml query. However, you should be able to use the other approach. The reason you are getting the exception is that the fields are no longer defined in the dataset, since they were tied to the query. You need to manually add the fields to the dataset by right-clicking on the dataset in the Datasets panel in the Report Designer, and click add in the context menu.

Regarding your question, to set the dataset parameter programmatically, you need to use a report parameter as the value of the dataset parameter, like you did with the query expression, and set the report parameter in your call to create render the report.

Ian|||

Hi Ian,

I did as you suggested, manually adding the fields, and creating a reports parameter called "sql". Now the dataset parameter "sql" is set to Parameters!sql.value.

Now when I run the report, I get the right number of rows, but they are all blank. I have tried setting the field values to the dataset field value, and I have tried leaving them blank, and get the same results either way. How do I get them to mesh up?

|||Yes, they need to be mapped to the dataset field names. The issue you describe is usually caused by custom fields not being mapped to existing dataset fields. Can you make sure that the fields you are mapping to exist in the dataset returned by the Xml Query? Since this is a dataset being returned, can you try adding the following custom dataset field,

Name = RowID, DatabaseField = id

This should map RowID to the id attibute of the NewTable element. This also assumes that your ElementPath describes the path to the table level, without specifying the fields to retrieve. For example,

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results{}/NewTable</ElementPath>|||

Hi,

I didn't quite understand that.

I do have the <ElementPath> tag that you described in my <query> string, except it is slightly different:

<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath

When I replaced /Results with /Results{}/NewTable, data was no longer returned to the dataset, so I changed it back.

I added a dataset field named "RowID", set it's type to "Database Field" and set the value to "=id" (I did not type in the quotes). However the data still did not display.

FYI, here is the entire query string I am using. I include it since it includes that elementPath part that stubbornly refuses to play nice with NewTable:
-

<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue></DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>
-
Here is a sample output from my webservice
-
<?xml version="1.0" encoding="utf-8" ?>

- <DataSet xmlns="http://tempuri.org/">

- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">

- <xs:complexType>

- <xs:choice minOccurs="0" maxOccurs="unbounded">

- <xs:element name="Results">

- <xs:complexType>

- <xs:sequence>

<xs:element name="ReportID" type="xs:int" minOccurs="0" />

<xs:element name="ReportName" type="xs:string" minOccurs="0" />

<xs:element name="ReportFriendlyName" type="xs:string" minOccurs="0" />

<xs:element name="ReportDescription" type="xs:string" minOccurs="0" />

<xs:element name="Display" type="xs:boolean" minOccurs="0" />

<xs:element name="DataViewName" type="xs:string" minOccurs="0" />

<xs:element name="Example1" type="xs:boolean" minOccurs="0" />

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:choice>

</xs:complexType>

</xs:element>

</xs:schema>

- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

- <NewDataSet xmlns="">

- <Results diffgr:id="Results1" msdata:rowOrder="0">

<ReportID>10</ReportID>

<ReportName>Reports_ByReportID1</ReportName>

<ReportFriendlyName>Reports by ID, w/ Dataset Parameter</ReportFriendlyName>

<ReportDescription>List of reports by report ID. This example uses a dataset parameter, which is referenced in the query string.</ReportDescription>

<Display>true</Display>

<DataViewName>Reports</DataViewName>

<Example1>false</Example1>

</Results>

</NewDataSet>

</diffgr:diffgram>

</DataSet>
--

And here are all my dataset fields:

FieldName Type Value
ReportID Database field =Fields!ReportID.Value
ReportName Database field =Fields!ReportName.Value
ReportFriendlyName Database field =Fields!ReportFriendlyName.Value

RowID Database field =id

|||Your element path is correct--you are describing the relationships to the table level. I mixed the name of your table, Results, with that of my dataset, also Results.

When creating custom Dataset fields, the value should be just the database field name. The value in the field is treated as a literal, so Reporting services is trying to match your custom dataset field, ReportID, to a field from the dataset returned from the query named "=Fields!ReportID.Value"

Try using these custom dataset field.

FieldNameType ValueReportIDDatabase field ReportIDReportNameDatabase field ReportNameReportFriendlyNameDatabase field ReportFriendlyName

More information on custom dataset fields:
http://msdn2.microsoft.com/en-us/library/ms189677.aspx|||You are the best!!!! |||When manually defining the fields in the dataset (e.g. programmatic select statement), how do you get them to 'stick'? When previewing the report, they get wiped out of the dataset once in a while.

Need to convert returned values.

I have an application which collects WMI info from servers and stores it in
SQL. The vendor reports for displaying this leave much to be desired. I've
developed numerous replacement reports using SRS, but I'm having trouble
converting the data they collect because of the way it's formatting and
stored....can anyone help?
Sample:
Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
I want to drop all of the [-] and [+] and just leave a space between labels
and values.
Any help woul be greatly appreciated.
RCITGUYMaybe in your SQL statment you could try something like this:
SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
"RCITGuy" wrote:
> I have an application which collects WMI info from servers and stores it in
> SQL. The vendor reports for displaying this leave much to be desired. I've
> developed numerous replacement reports using SRS, but I'm having trouble
> converting the data they collect because of the way it's formatting and
> stored....can anyone help?
> Sample:
> Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> I want to drop all of the [-] and [+] and just leave a space between labels
> and values.
> Any help woul be greatly appreciated.
> RCITGUY
>|||I have to apologize for sounding stupid...but where should I place this in my
existing SQL select statement? "column holding data is
"WMIConfiguration.Configuration"
___________________________________________________________________
SELECT ComputerGroup.Name, Computer.Name AS Server,
WMIConfiguration.ObjectType, WMIConfiguration.Configuration, Computer.OSVer,
Computer.Address, Computer.PhysicalMem,
Computer.PageSize
FROM Computer INNER JOIN
WMIConfiguration ON Computer.ComputerID =WMIConfiguration.ComputerID INNER JOIN
ComputerGroup ON Computer.GroupID =ComputerGroup.GroupID
WHERE (ComputerGroup.Name = @.Company)
_____________________________________________________________
"Aiwa" wrote:
> Maybe in your SQL statment you could try something like this:
> SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
> "RCITGuy" wrote:
> > I have an application which collects WMI info from servers and stores it in
> > SQL. The vendor reports for displaying this leave much to be desired. I've
> > developed numerous replacement reports using SRS, but I'm having trouble
> > converting the data they collect because of the way it's formatting and
> > stored....can anyone help?
> >
> > Sample:
> > Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> > hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> > Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> >
> > I want to drop all of the [-] and [+] and just leave a space between labels
> > and values.
> >
> > Any help woul be greatly appreciated.
> > RCITGUY
> >|||Something like this:
SELECT ComputerGroup.Name,
Computer.Name AS Server,
WMIConfiguration.ObjectType,
REPLACE(REPLACE(WMIConfiguration.Configuration,'[+]','
'),'[-]',' ') AS Configuration ,
Computer.OSVer,
Computer.Address,
Computer.PhysicalMem,
Computer.PageSize
FROM Computer
INNER JOIN WMIConfiguration ON Computer.ComputerID =WMIConfiguration.ComputerID
INNER JOIN ComputerGroup ON Computer.GroupID = ComputerGroup.GroupID
WHERE (ComputerGroup.Name = @.Company)
"RCITGuy" wrote:
> I have to apologize for sounding stupid...but where should I place this in my
> existing SQL select statement? "column holding data is
> "WMIConfiguration.Configuration"
> ___________________________________________________________________
> SELECT ComputerGroup.Name, Computer.Name AS Server,
> WMIConfiguration.ObjectType, WMIConfiguration.Configuration, Computer.OSVer,
> Computer.Address, Computer.PhysicalMem,
> Computer.PageSize
> FROM Computer INNER JOIN
> WMIConfiguration ON Computer.ComputerID => WMIConfiguration.ComputerID INNER JOIN
> ComputerGroup ON Computer.GroupID => ComputerGroup.GroupID
> WHERE (ComputerGroup.Name = @.Company)
> _____________________________________________________________
> "Aiwa" wrote:
> > Maybe in your SQL statment you could try something like this:
> > SELECT REPLACE(REPLACE(YourColumn,'[+]',' '),'[-]',' ')
> >
> > "RCITGuy" wrote:
> >
> > > I have an application which collects WMI info from servers and stores it in
> > > SQL. The vendor reports for displaying this leave much to be desired. I've
> > > developed numerous replacement reports using SRS, but I'm having trouble
> > > converting the data they collect because of the way it's formatting and
> > > stored....can anyone help?
> > >
> > > Sample:
> > > Name[-]\\.\PHYSICALDRIVE0[+]Manufacturer[-]Compaq[+]InterfaceType[-]SCSI[+]MediaType[-]Fixed
> > > hard disk media[+]Model[-]Compaq Disk Array SCSI Disk
> > > Device[+]Status[-]OK[+]Partitions[-]3[+]BytesPerSector[-]512[+]SectorsPerTrack[-]32[+]TracksPerCylinder[-]255[+]TotalSectors[-]53,309,280[+]TotalTracks[-]1,665,915[+]TotalCylinders[-]6,533[+]Size[-]27,294,351,360
> > >
> > > I want to drop all of the [-] and [+] and just leave a space between labels
> > > and values.
> > >
> > > Any help woul be greatly appreciated.
> > > RCITGUY
> > >

Need to check a table for errors

Hi - please excuse my newness to this. I have a database with several tables and one of them is causing my application to lag really bad. I figure there is either not enough space or something is just wrong in general and i don't know what. Does SQL Server have a shortcut or easy way to test a table in the database?

Thanks =)If it is only giving performance troubles, I bet it is a large table (either many columns, many rows, or both). Look into the index tuning wizard in Enterprise Manager.|||Originally posted by Avernus
Hi - please excuse my newness to this. I have a database with several tables and one of them is causing my application to lag really bad. I figure there is either not enough space or something is just wrong in general and i don't know what. Does SQL Server have a shortcut or easy way to test a table in the database?

Thanks =)

dbcc checktable(yourtable)?|||Originally posted by snail
dbcc checktable(yourtable)?

Ok will try that.

I also forgot to mention that it has worked fine for a year and all the sudden is causing literally 5 minute lag periods.

Thanks =)|||Originally posted by Avernus
Ok will try that.

I also forgot to mention that it has worked fine for a year and all the sudden is causing literally 5 minute lag periods.

Thanks =)

Also you could check for fragmentation (it is good idea to do this sometimes):

DBCC SHOWCONTIG|||In Query Analyzer execute one of the queries your app issues after setting Show Execution Plan to ON (Ctrl+K.) The corresponding pane will show you how the Optimizer processed the request. Pay close attention to anything in RED there, as well as table/index scans.|||"Also you could check for fragmentation (it is good idea to do this sometimes):

DBCC SHOWCONTIG"

I get an error trying to run this alone. I read something about having to include an Object ID with this command. Can you show me an example of how that looks written out for one table? Thanks, I appreciate this bigtime =)

Need to change connection string for SQL 2005?

Hi,
DevStudio 2003, SQL Server 2005, Windows 2000 Pro SP4
I have been using the Enterprise Library Data Access Application Block to
connect to a local SQL 2000 database. The connection string parameters are:
<connectionStrings>
<connectionString name="Temp Connection String">
<parameters>
<parameter name="database" value="Temp" isSensitive="false" />
<parameter name="Integrated Security" value="True" isSensitive="false"
/>
<parameter name="packet size" value="4096" isSensitive="false" />
<parameter name="persist security info" value="false"
isSensitive="false" />
<parameter name="server" value="(local)" isSensitive="false" />
</parameters>
</connectionString>
</connectionStrings>
Having then removed SQL2000 and replaced it with SQL2005, the above
connection parameters no longer work. In order to connect, I'm having to
replace "(local)" with the explicit IP address of the local PC.
Can SQL 2005 be made to work with "(local)"? Having to change the "server"
value to the IP address means changing my installer to fill in this value,
which I'd rather not do. It also means that other developers in the team
have to change the connection string parameters in order to test on their
local databases.
For your information, SQL 2005 is configured to use both the Shared Memory &
Named Pipes protocols. Native Client configuration WAS setup with Shared
Memory first and Named Pipes second. I've tried disabling Shared Memory and
forcing Named Pipes to be used (on the basis that the SQL 2000 installation
was using Named Pipes) but to no avail.
Regards
David RazzettiHmmh, (local) should work on SQL2k5, whats the error message the
application Block is throwing ?
HTH, jens Suessmeyer.|||Hi Jens,
I'm using the application block to serve my code with SqlConnection objects.
The application block successfully supplies a SqlConnection object, but a
call to 'Open()' on the connection throws the SqlException "SQL Server does
not exist or access denied".
Regards
David Razzetti
"Jens" wrote:

> Hmmh, (local) should work on SQL2k5, whats the error message the
> application Block is throwing ?
> HTH, jens Suessmeyer.
>|||Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
?
HTH, Jens Suessmeyer.|||Hi Jens,
"." appears to work correctly with both SQL 2000 and SQL 2005, so I guess I
can use that instead of "(local)". I'd still be interested to hear from
anyone who knows why "(local)" might not be working on my installation of SQ
L
2005.
Regards
David Razzetti
"Jens" wrote:

> Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
> ?
> HTH, Jens Suessmeyer.
>|||Don=B4t know what version / build you are using but in earlier build the
local was replaced by localhost.
HTH, jens Suessmeyer.

Need to change connection string for SQL 2005?

Hi,
DevStudio 2003, SQL Server 2005, Windows 2000 Pro SP4
I have been using the Enterprise Library Data Access Application Block to
connect to a local SQL 2000 database. The connection string parameters are:
<connectionStrings>
<connectionString name="Temp Connection String">
<parameters>
<parameter name="database" value="Temp" isSensitive="false" />
<parameter name="Integrated Security" value="True" isSensitive="false"
/>
<parameter name="packet size" value="4096" isSensitive="false" />
<parameter name="persist security info" value="false"
isSensitive="false" />
<parameter name="server" value="(local)" isSensitive="false" />
</parameters>
</connectionString>
</connectionStrings>
Having then removed SQL2000 and replaced it with SQL2005, the above
connection parameters no longer work. In order to connect, I'm having to
replace "(local)" with the explicit IP address of the local PC.
Can SQL 2005 be made to work with "(local)"? Having to change the "server"
value to the IP address means changing my installer to fill in this value,
which I'd rather not do. It also means that other developers in the team
have to change the connection string parameters in order to test on their
local databases.
For your information, SQL 2005 is configured to use both the Shared Memory &
Named Pipes protocols. Native Client configuration WAS setup with Shared
Memory first and Named Pipes second. I've tried disabling Shared Memory and
forcing Named Pipes to be used (on the basis that the SQL 2000 installation
was using Named Pipes) but to no avail.
Regards
David Razzetti
Hmmh, (local) should work on SQL2k5, whats the error message the
application Block is throwing ?
HTH, jens Suessmeyer.
|||Hi Jens,
I'm using the application block to serve my code with SqlConnection objects.
The application block successfully supplies a SqlConnection object, but a
call to 'Open()' on the connection throws the SqlException "SQL Server does
not exist or access denied".
Regards
David Razzetti
"Jens" wrote:

> Hmmh, (local) should work on SQL2k5, whats the error message the
> application Block is throwing ?
> HTH, jens Suessmeyer.
>
|||Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
?
HTH, Jens Suessmeyer.
|||Hi Jens,
"." appears to work correctly with both SQL 2000 and SQL 2005, so I guess I
can use that instead of "(local)". I'd still be interested to hear from
anyone who knows why "(local)" might not be working on my installation of SQL
2005.
Regards
David Razzetti
"Jens" wrote:

> Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
> ?
> HTH, Jens Suessmeyer.
>
|||Don=B4t know what version / build you are using but in earlier build the
local was replaced by localhost.
HTH, jens Suessmeyer.
sql

Need to back up & restore a SQL server database when only SQL 2000 and 2005 are available.

I am maintaining a .net 1.0 asp.net web application that accesses a SQL server 2000 database. I need to backup the database and then restore it on a laptop. Currently it resides on a server. The problem is that on the server they are using sql server 2005. If I backup using 2005 and then try and restore this database using SQL server 2000 or 2005 on the laptop, I get an error message. "Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE DATABASE is terminating abnormally.

What can I do to restore the database? Must it be backed up with SQL server 2000 and then restored with SQL Server 2005?

Please help!

you cannot back up a db from 2005 and restore it on 2000 server. Its possible the other way though.

sql

Need the logs...

Hello:
I am troubleshooting an application issue which uses SQL. The vendor has
asked me to provide him with the SQL transaction logs. I have no idea how
to get that information. Can anyone offer some advice on this?
Harrison Midkiff
Harrison
Does the vendor want to read the data from LOG ?
Take a look at third party product
http://www.lumigent.com/
"Harrison Midkiff" <HMidkiff@.aviinc.com> wrote in message
news:OQBiK2yMHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hello:
> I am troubleshooting an application issue which uses SQL. The vendor has
> asked me to provide him with the SQL transaction logs. I have no idea
> how to get that information. Can anyone offer some advice on this?
> Harrison Midkiff
>
|||Make sure he wants T-logs, not SQL Errorlogs. Most of the time, supoprt
folks want either the ERRORLOGs or the whole database. Rarely the t-log by
itself
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u5KXT7yMHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Harrison
> Does the vendor want to read the data from LOG ?
> Take a look at third party product
> http://www.lumigent.com/
>
>
>
> "Harrison Midkiff" <HMidkiff@.aviinc.com> wrote in message
> news:OQBiK2yMHHA.4712@.TK2MSFTNGP04.phx.gbl...
>
sql

Need the logs...

Hello:
I am troubleshooting an application issue which uses SQL. The vendor has
asked me to provide him with the SQL transaction logs. I have no idea how
to get that information. Can anyone offer some advice on this?
Harrison MidkiffHarrison
Does the vendor want to read the data from LOG ?
Take a look at third party product
http://www.lumigent.com/
"Harrison Midkiff" <HMidkiff@.aviinc.com> wrote in message
news:OQBiK2yMHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hello:
> I am troubleshooting an application issue which uses SQL. The vendor has
> asked me to provide him with the SQL transaction logs. I have no idea
> how to get that information. Can anyone offer some advice on this?
> Harrison Midkiff
>|||Make sure he wants T-logs, not SQL Errorlogs. Most of the time, supoprt
folks want either the ERRORLOGs or the whole database. Rarely the t-log by
itself
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u5KXT7yMHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Harrison
> Does the vendor want to read the data from LOG ?
> Take a look at third party product
> http://www.lumigent.com/
>
>
>
> "Harrison Midkiff" <HMidkiff@.aviinc.com> wrote in message
> news:OQBiK2yMHHA.4712@.TK2MSFTNGP04.phx.gbl...
>

Need the logs...

Hello:
I am troubleshooting an application issue which uses SQL. The vendor has
asked me to provide him with the SQL transaction logs. I have no idea how
to get that information. Can anyone offer some advice on this?
Harrison MidkiffHarrison
Does the vendor want to read the data from LOG ?
Take a look at third party product
http://www.lumigent.com/
"Harrison Midkiff" <HMidkiff@.aviinc.com> wrote in message
news:OQBiK2yMHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Hello:
> I am troubleshooting an application issue which uses SQL. The vendor has
> asked me to provide him with the SQL transaction logs. I have no idea
> how to get that information. Can anyone offer some advice on this?
> Harrison Midkiff
>|||Make sure he wants T-logs, not SQL Errorlogs. Most of the time, supoprt
folks want either the ERRORLOGs or the whole database. Rarely the t-log by
itself
--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
Real-world stuff I run across with SQL Server:
http://kevin3nf.blogspot.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u5KXT7yMHHA.4712@.TK2MSFTNGP04.phx.gbl...
> Harrison
> Does the vendor want to read the data from LOG ?
> Take a look at third party product
> http://www.lumigent.com/
>
>
>
> "Harrison Midkiff" <HMidkiff@.aviinc.com> wrote in message
> news:OQBiK2yMHHA.4712@.TK2MSFTNGP04.phx.gbl...
>> Hello:
>> I am troubleshooting an application issue which uses SQL. The vendor has
>> asked me to provide him with the SQL transaction logs. I have no idea
>> how to get that information. Can anyone offer some advice on this?
>> Harrison Midkiff
>

Need SQL server advice

Hi, I need some advice concerning a vb.net windows application I'm building using SQL server as it's database.

The idea is to have a main enterprise SQL server database on the company network. The application will be installed on stand alone computers with it's own Standard SQL server database which will mirror the network database. This is so users can work with the application when not connected to the network.

The Network database will often be updated with new data and I will program the application so that everytime it is opened it will check the network database for new data and then update the local database. Of course this can only happen if the computer is connected to the network.

My questions are:

1. When installing the application on a users computer is it possible to initate the setup for the local SQL server in the application setup?

2.Once the setup is complete can there be a transfer of all the data tables and stored procedures on the network database to the local database automatically or would I have to create all that through code?

3.When there is an update of data from the network database to the local database can this be done transparently to the user or would this have to happen through DTS.

thanks for any advice on these questions

1. When installing the application on a users computer is it possible to initate the setup for the local SQL server in the application setup?

You must have a "class module" in the application that stores the connection string. when the application run the very first time you may read the connection string from a file then store it to the CM.connstring

At the form load event of every form you can assign the connection string to your connection object.

sqlconn.connectionstring=cm.connstring.

2.Once the setup is complete can there be a transfer of all the data tables and stored procedures on the network database to the local database automatically or would I have to create all that through code?

you must design a transactional replication or merge replication with the server

3.When there is an update of data from the network database to the local database can this be done transparently to the user or would this have to happen through DTS.

answer on #2 resolves this also

sql

Need SQL Performance Advice (Scenario Given)

Hey guys, having a bit of a urgency here. I need to make certain queries faster in our application, lemme give you a scoup. One of the queries i need to optimize or find a better way to do is the current scenario. We have jobs in our system, these jobs have applicants, some of them are direct applicants, some are matches (from searches), and some are applicants with resumes only.
Some of our clients have 100+ jobs, when they see the first page we list all the jobs with 6 counts next to them:

Job Title, Total Applicants, New Applicants, Total Matches, New Matches, Total Resumes, New Resumes

So we have 6 counts for each job, say you have 100+ jobs thats a lot of counts. Currently i'm performing a sub query for each of the counts since they represent different data points, the only thing that changes is the WHERE clause.

As you can see this can be very heavy and in some cases (100+ jobs) depending of number of applicants might take up to a minute to finish.

I would like to get some opinions from you guys on how to make this run faster, obviously caching cannot be considered since they employer must see the live data (counts)

Any help is greatly appreciated.indexes on the join columns?|||yes indexes are in place, i dont really thing that they are the problem,

i think the subqueries are killing it since one query needs to be run to get each of the count right? is there any other way to structure this thing?

thanks for your help|||depends

without seeing the subqueries, it's kinda difficult to tell what's wrong or whether an alternative structure is possible|||Without seeing your code, my first suggestion would be to "unwrap" the subqueries. Make a single pass through the data, doing a single join. Reimplement the counts as sums, and use CASE to provide the "smarts" to make it work.

As Rudy pointed out, without seeing what you are doing, we are pretty sorely limited in how we can help. This is kind of like calling someone on another continent and telling them "my stomach hurts" and asking them what you should do about it.

-PatP

Need some help with MSDE 2000

I have to create an application using MSDE, I install the software on my pc
and it is working fine but my problem is that I have to store the database
on the J drive of my LAN and must be able to access if from my local PC or
any other pc connected to the Lan. When I put the database on my pc I can
access the the tables, but when I put the database on the J drive of the LAN
the MSDE client on my PC does not find the databases. Is there a way to tell
the MSDE client on my PC to go to the J drive on the lan and find the
databases? The data must be store on the J drive of the Lan. I would really
appreciate some help here as I am very new to MSDE. In ms access I just had
to set the path to the database and that was it.
thanks in advance.
On Sat, 12 Jun 2004 01:37:34 -0400, Patrick Blackman
<caribsoft@.videotron.ca> wrote:

> the MSDE client on my PC does not find the databases. Is there a way to
> tell the MSDE client on my PC to go to the J drive on the lan and find
> the
> databases?
You should install MSDE on the machine where it runs, not on another PC
into a mapped drive. That's just what my feeling tells me. There might ne
network installation guides on msdn.microsoft.com.
Furthermore, if you need to access MSDE over the net, make sure
authentication (hint: SECURITYMODE=SQL, for instance) is set up properly
as well as "firewalls" are disabled or at least let the appropriate ports
through (I can't recall the MSDE listening port).
HTH,
Stefan
Give a man fire and he is warm for a day.
Set him on fire and he is warm for the rest of his life.

Need recomendations on architecture.

We will be creating a moderately high-volume OLTP database application that needs 24/7 availability. We are planning to offload OLAP processing to a second copy of the system. We will be using SQL Server 2005.

I originally planned to set the second server up with SQL Server 2005 mirroring to cover the 24/7 availability requirement, with the idea that we could also do OLAP reporting off of the mirrored copy of the database. But I've gotten some indications that a mirror database is offline and not available for querying. So I figured I would use transactional replication to keep the OLAP database current. Now I am wondering if I need to use mirroring at all, or if I should just use transactional replication on the entire database and swap to the replicated database if the production server crashes.

What is everyone's opinion?

Replication only, for both OLAP reporting and failover?
Mirroring to one database for failover, with replication to a another database for OLAP reporting?My recommendation is to mirror for redundancy, much like you'd use clustering. Mirroring allows you to set up N servers that appear to the user as a single server. It gives you a quick, easy, and supported way to do something a lot like what log shipping did in previous versions, with automatic failover.

I'd also suggest that you use replication for OLAP processing, but that it go to a completely different server than your mirror does. You really want the replicated copy of your OLTP data to serve as the pre-staging area in your DW architecture, so I'd really want to avoid using a mirrored database for this use. By creating this "pre-staging" image, you can then roll the data into the staging form in whatever is the most convenient fashion, allowing it to flow quickly and easily through your staging area to the atomic level of your DW. At that point you can decide if you want to go to the extra effort to create relational rollups (a good idea for many reasons), or if you want to go directly to cubes (faster, but you lose a lot of options if you take the shortcut).

-PatP|||I'm afraid the company would balk at footing the bill for a third server to house the OLAP copy. What I may suggest is housing the mirrored database and the OLAP database on the same server.|||Understood... While we can figure out ways to provide exactly what users want, we are all constrained by what the users can afford. I think that you need to at least present the users the information that they need in order to make an informed decision... They need to know what you'd recommend as well as what you can live with...

-PatP

need opinions

I am developing a web application that will host many companies' data in one
database. And there will be the possibility that the company may decide to
take their data at some point and host it themselves.
So my question relates to the table structure and relationships. If a
company decides to take their data, I want it to be as easy as pulling all
data from all tables where Company_id = x. That said, let's just assume I'm
going to have the Company_Id in all tables or most. Do I create a separate
primary key field that's unique across all companies or unique within the
company_id ? I like the idea of all companies starting at 1 for their
primary key IDs, but this also brings more complexity and inefficiencies to
querying the database. Plus it means defining a two field unique index per
table and having to base all relationships between tables on two fields.
The joins are more complicated.
I'm leaning toward using one primary key field across all companies to link
tables, but still having the Company_id in all tables just to be able to
extract the data easily if I ever need to. The Primary key field would not
be an Auto ID, as this would pose problems when migrating existing data to a
new database.
Or do I just use a different database per customer. I expect having 1000 or
more companies, so I don't think separate databases will fly.
Looking for your opinion and getting ideas about how things are being done
elsewhere.
thanks,
BillBill
CREATE TABLE Companies
(
company_id INT NOT NULL PRIMARY KEY,
company_description VARCHAR(50) NOT NULL
.....
.....
)
CREATE TABLE Companies_Data
(
row_id INT NOT NULL PRIMARY KEY,
company_id INT NOT NULL REFERENCES Companies (company_id),
comp_activities VARCHAR(...) or INTEGER ( Depends on your business
requierements)
customer_id.....
.....
)
CREATE TABLE Customers
(
custoemid INT NOT NULL PRIMARY KEY,
custname VRACHAR(50) NOT NULL,
....
)
Look , the company may have more than one actvities , right? Consider
crerating an UNIQUE CONSTRAINT on these fields
> Or do I just use a different database per customer. I expect having 1000
> or more companies, so I don't think separate databases will fly.
Well, I have seen some systems that customers hosted their databases (with
costomer info) and just connected to the main server to make operations as
well as the main server has all info about customers. It is really depends
on your business requirements.
"Bill H" <bill@.somewhere.com> wrote in message
news:OwWFpNXTGHA.5900@.tk2msftngp13.phx.gbl...
>I am developing a web application that will host many companies' data in
>one database. And there will be the possibility that the company may
>decide to take their data at some point and host it themselves.
> So my question relates to the table structure and relationships. If a
> company decides to take their data, I want it to be as easy as pulling all
> data from all tables where Company_id = x. That said, let's just assume
> I'm going to have the Company_Id in all tables or most. Do I create a
> separate primary key field that's unique across all companies or unique
> within the company_id ? I like the idea of all companies starting at 1
> for their primary key IDs, but this also brings more complexity and
> inefficiencies to querying the database. Plus it means defining a two
> field unique index per table and having to base all relationships between
> tables on two fields. The joins are more complicated.
> I'm leaning toward using one primary key field across all companies to
> link tables, but still having the Company_id in all tables just to be able
> to extract the data easily if I ever need to. The Primary key field would
> not be an Auto ID, as this would pose problems when migrating existing
> data to a new database.
> Or do I just use a different database per customer. I expect having 1000
> or more companies, so I don't think separate databases will fly.
>
> Looking for your opinion and getting ideas about how things are being done
> elsewhere.
> thanks,
> Bill
>

Need list of tables which a application is accessing.

I have an application accessing various tables in a SQL Server database.
I want to have a list of all those table which the application has accessed
for select , Insert and update operation.
Currently i am using SQL Profiler to trap all the queries and searching for
table names in TextData column.
Is there any way to get the list of tables which the application is accessin
g.You can track that information yourself quite easily, or use a third party
application.
http://www.aspfaq.com/2448
http://www.aspfaq.com/2496
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ishwar Jindal" <anonymous@.discussions.microsoft.com> wrote in message
news:1B5549AE-58A5-4A3E-9422-EDE6FF7A07CD@.microsoft.com...
> I have an application accessing various tables in a SQL Server database.
> I want to have a list of all those table which the application has
accessed for select , Insert and update operation.
> Currently i am using SQL Profiler to trap all the queries and searching
for table names in TextData column.
> Is there any way to get the list of tables which the application is
accessing.

Need list of tables which a application is accessing.

I have an application accessing various tables in a SQL Server database.
I want to have a list of all those table which the application has accessed for select , Insert and update operation.
Currently i am using SQL Profiler to trap all the queries and searching for table names in TextData column.
Is there any way to get the list of tables which the application is accessing.
You can track that information yourself quite easily, or use a third party
application.
http://www.aspfaq.com/2448
http://www.aspfaq.com/2496
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ishwar Jindal" <anonymous@.discussions.microsoft.com> wrote in message
news:1B5549AE-58A5-4A3E-9422-EDE6FF7A07CD@.microsoft.com...
> I have an application accessing various tables in a SQL Server database.
> I want to have a list of all those table which the application has
accessed for select , Insert and update operation.
> Currently i am using SQL Profiler to trap all the queries and searching
for table names in TextData column.
> Is there any way to get the list of tables which the application is
accessing.

Need list of tables which a application is accessing.

I have an application accessing various tables in a SQL Server database
I want to have a list of all those table which the application has accessed for select , Insert and update operation
Currently i am using SQL Profiler to trap all the queries and searching for table names in TextData column.
Is there any way to get the list of tables which the application is accessing.You can track that information yourself quite easily, or use a third party
application.
http://www.aspfaq.com/2448
http://www.aspfaq.com/2496
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ishwar Jindal" <anonymous@.discussions.microsoft.com> wrote in message
news:1B5549AE-58A5-4A3E-9422-EDE6FF7A07CD@.microsoft.com...
> I have an application accessing various tables in a SQL Server database.
> I want to have a list of all those table which the application has
accessed for select , Insert and update operation.
> Currently i am using SQL Profiler to trap all the queries and searching
for table names in TextData column.
> Is there any way to get the list of tables which the application is
accessing.

Need information on createDrillThroughContext function

In our application, we want to implement createDrillThroughContext function.

Would anyone tell me what that function does, how to use it, where I can use it, and any other related information.

The information on the RDL spec is not very helpful.

Hi Jinlin-

You can find more information about DrillThroughContext at: http://msdn2.microsoft.com/en-us/library/ms159112.aspx

The CreateDrillThroughContext() method is used by report builder for autogeneration of drillthrough reports. It produces a DrillThroughContext which is passed back to the model. Given the model and the DrillThroughConext, the report server will auto create a report.

In general, if you which to create custom model based drillthrough reports, use report builder to create a report and set the "Drillthough" property under the report properties menu. You then can bind that report to a specific model item using Management Studio.

Thanks, Jon