Hi to all
Is possible to access to the report datasets or even report items from the
custom code embedded in the report?, If yes, how?,could you send some sample
code?
ThanksNo, you would need to pass in the values you are interested in as parameters
to your function.
For example:
=Code.MyFunction(Fields!CityCode.Value) or
=Code.MyFunction(ReportItems!textbox1.Value)
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Hi to all
> Is possible to access to the report datasets or even report items from the
> custom code embedded in the report?, If yes, how?,could you send some
sample
> code?
> Thanks
>
>|||Ok... I think this is very restrictive, I need to condition the visibility
of some components in base to some information contained in the dataset,
like if exist some value for one column and if it is the same for others
columns (if exist some) and I was trying to create a general function for
all my components , it seems that I'll have to create other datasets that
makes this kind of comparision with SQL or some SP, If you have a best idea
please let me know...
What about to pass Datasets as parameters? is possible?
Thanks
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:u$vbDaadEHA.2664@.TK2MSFTNGP09.phx.gbl...
> No, you would need to pass in the values you are interested in as
parameters
> to your function.
> For example:
> =Code.MyFunction(Fields!CityCode.Value) or
> =Code.MyFunction(ReportItems!textbox1.Value)
> --
> This post is provided 'AS IS' with no warranties, and confers no rights.
All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No
user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach
of
> children under 3.
> "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> > Hi to all
> > Is possible to access to the report datasets or even report items from
the
> > custom code embedded in the report?, If yes, how?,could you send some
> sample
> > code?
> > Thanks
> >
> >
> >
> >
>|||No, you cannot pass the entire data set as a parameter.
You could hand in the entire fields collection, however, if that would help
you any.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:u1qA$AcdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> Ok... I think this is very restrictive, I need to condition the visibility
> of some components in base to some information contained in the dataset,
> like if exist some value for one column and if it is the same for others
> columns (if exist some) and I was trying to create a general function for
> all my components , it seems that I'll have to create other datasets that
> makes this kind of comparision with SQL or some SP, If you have a best
idea
> please let me know...
> What about to pass Datasets as parameters? is possible?
> Thanks
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:u$vbDaadEHA.2664@.TK2MSFTNGP09.phx.gbl...
> > No, you would need to pass in the values you are interested in as
> parameters
> > to your function.
> > For example:
> >
> > =Code.MyFunction(Fields!CityCode.Value) or
> > =Code.MyFunction(ReportItems!textbox1.Value)
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach
> of
> > children under 3.
> > "Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
> > news:%235LQ2$PdEHA.4004@.TK2MSFTNGP10.phx.gbl...
> > > Hi to all
> > > Is possible to access to the report datasets or even report items from
> the
> > > custom code embedded in the report?, If yes, how?,could you send some
> > sample
> > > code?
> > > Thanks
> > >
> > >
> > >
> > >
> >
> >
>
Showing posts with label items. Show all posts
Showing posts with label items. Show all posts
Sunday, March 25, 2012
Wednesday, March 7, 2012
Databound image in report header
After finding out that SSRS doesn't directly support databound items in the header or footer, I found the workaround from Microsoft yesterday at http://msdn2.microsoft.com/en-us/library/ms159677.aspx. The problem is that their solution only works for the first page of the report, so it's basically useless! Because this is an image, it also means the workaround of using a parameter doesn't work, either.
Does anybody have any advice? Is there a solution to this problem?
One approach to accomplish this is to create a custom function that queries the database for the image, caches it and then returns it for use in the Value property of the database image in the report header.Private m_headerImage As Object
Public Function GetHeaderImage() As object
' be sure to add appropriate error handling
If m_headerImage = Nothing Then
Dim adapter as new System.Data.SqlClient.SqlDataAdapter("Select ThumbnailPhoto From Production.Productphoto", "Data Source=localhost; Initial Catalog=AdventureWorks; Integrated Security=SSPI;" )
Dim ds as new System.Data.DataSet()
adapter.Fill(ds)
m_headerImage = ds.Tables(0).Rows(0)(0)
End If
Return m_headerImage
End Function
Set the image Value property to
=Code.GetHeaderImage()
Also, you will need to add a references to the System.Sql.Data and System.Xml assemblies.
More information on cutom code:
http://msdn2.microsoft.com/en-us/library/ms155798.aspx
Ian|||
Mark, which version of SSRS are you using?
Note: the parameter approach for databound images in page header/footer works correctly with RS 2005 SP1.
-- Robert
|||Robert,I am using RS 2005 SP1; I just assumed that the parameter approach wouldn't work for binary image data due to the size factor. I'll have to try that out.
-Mark
|||Robert,
How exactly do I get this to work using the parameter approach? I've tried everything I can think of. It works for text, but not images.
Thanks,
Mark
|||
Mark, I attached a small sample report (based on the Northwind database) that shows how to do it.
Important parts:
* the report parameter default value is based on a calculated field (ImageBase64) from the dataset
* the calculated field is converting the image data field into a base-64 encoded string, e.g. =Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))
* the image report item takes the value of the Image report parameter and converts it back from a base-64 string to a byte array:
=Convert.FromBase64String(Parameters!Image.Value)
-- Robert
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>02f7d41c-e5a6-4015-9bd4-6aa837db9bb6</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Image">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DataSet1</DataSetName>
<ValueField>ImageBase64</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Image</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>1.5in</Height>
</Body>
<PageHeader>
<ReportItems>
<Image Name="image2">
<Sizing>AutoSize</Sizing>
<MIMEType>image/bmp</MIMEType>
<Width>2.875in</Width>
<Source>Database</Source>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
</Style>
<Value>=Convert.FromBase64String(Parameters!Image.Value)</Value>
</Image>
</ReportItems>
<Height>2.625in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageHeader>
<rd:ReportID>14f7b895-1a77-410d-ae9c-58cfb7418d60</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from employees</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="LastName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>LastName</DataField>
</Field>
<Field Name="FirstName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>FirstName</DataField>
</Field>
<Field Name="Title">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Title</DataField>
</Field>
<Field Name="TitleOfCourtesy">
<rd:TypeName>System.String</rd:TypeName>
<DataField>TitleOfCourtesy</DataField>
</Field>
<Field Name="BirthDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>BirthDate</DataField>
</Field>
<Field Name="HireDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>HireDate</DataField>
</Field>
<Field Name="Address">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Address</DataField>
</Field>
<Field Name="City">
<rd:TypeName>System.String</rd:TypeName>
<DataField>City</DataField>
</Field>
<Field Name="Region">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Country</DataField>
</Field>
<Field Name="HomePhone">
<rd:TypeName>System.String</rd:TypeName>
<DataField>HomePhone</DataField>
</Field>
<Field Name="Extension">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Extension</DataField>
</Field>
<Field Name="Photo">
<rd:TypeName>System.Byte[]</rd:TypeName>
<DataField>Photo</DataField>
</Field>
<Field Name="Notes">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Notes</DataField>
</Field>
<Field Name="ReportsTo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ReportsTo</DataField>
</Field>
<Field Name="PhotoPath">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PhotoPath</DataField>
</Field>
<Field Name="ImageBase64">
<Value>=Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))</Value>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Robert,
I am in the same fix regarding the databound image situation. I've used the hidden text box field inside the body of the report that converts the image (a field in the dataset) to a string and then the actual image in the page header references the hidden text box. This works great in the preview of BI Studio. It also works when the report is exported to Excel. However, when exported to PDF, the image is not shown. Is this even possible?
Regarding the solution above, I tried your suggestion. However, I'm back to the situation where a "fields cannot be used in report parameter expressions". Is there something I'm missing in your explanation above? This particular report will eventually become an "rdlc" report on a remote site so I'm limited as to what I can do in the BI environment.
Any help or direction would be appreciated! I've spent way too much time on what should be a simple task, in my opinion, to implement.
Also, do you still have the sample report?
Thanks,
John
Databound image in report header
After finding out that SSRS doesn't directly support databound items in the header or footer, I found the workaround from Microsoft yesterday at http://msdn2.microsoft.com/en-us/library/ms159677.aspx. The problem is that their solution only works for the first page of the report, so it's basically useless! Because this is an image, it also means the workaround of using a parameter doesn't work, either.
Does anybody have any advice? Is there a solution to this problem?
One approach to accomplish this is to create a custom function that queries the database for the image, caches it and then returns it for use in the Value property of the database image in the report header.Private m_headerImage As Object
Public Function GetHeaderImage() As object
' be sure to add appropriate error handling
If m_headerImage = Nothing Then
Dim adapter as new System.Data.SqlClient.SqlDataAdapter("Select ThumbnailPhoto From Production.Productphoto", "Data Source=localhost; Initial Catalog=AdventureWorks; Integrated Security=SSPI;" )
Dim ds as new System.Data.DataSet()
adapter.Fill(ds)
m_headerImage = ds.Tables(0).Rows(0)(0)
End If
Return m_headerImage
End Function
Set the image Value property to
=Code.GetHeaderImage()
Also, you will need to add a references to the System.Sql.Data and System.Xml assemblies.
More information on cutom code:
http://msdn2.microsoft.com/en-us/library/ms155798.aspx
Ian|||
Mark, which version of SSRS are you using?
Note: the parameter approach for databound images in page header/footer works correctly with RS 2005 SP1.
-- Robert
|||Robert,I am using RS 2005 SP1; I just assumed that the parameter approach wouldn't work for binary image data due to the size factor. I'll have to try that out.
-Mark
|||Robert,
How exactly do I get this to work using the parameter approach? I've tried everything I can think of. It works for text, but not images.
Thanks,
Mark
|||
Mark, I attached a small sample report (based on the Northwind database) that shows how to do it.
Important parts:
* the report parameter default value is based on a calculated field (ImageBase64) from the dataset
* the calculated field is converting the image data field into a base-64 encoded string, e.g. =Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))
* the image report item takes the value of the Image report parameter and converts it back from a base-64 string to a byte array:
=Convert.FromBase64String(Parameters!Image.Value)
-- Robert
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>02f7d41c-e5a6-4015-9bd4-6aa837db9bb6</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Image">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DataSet1</DataSetName>
<ValueField>ImageBase64</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Image</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>1.5in</Height>
</Body>
<PageHeader>
<ReportItems>
<Image Name="image2">
<Sizing>AutoSize</Sizing>
<MIMEType>image/bmp</MIMEType>
<Width>2.875in</Width>
<Source>Database</Source>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
</Style>
<Value>=Convert.FromBase64String(Parameters!Image.Value)</Value>
</Image>
</ReportItems>
<Height>2.625in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageHeader>
<rd:ReportID>14f7b895-1a77-410d-ae9c-58cfb7418d60</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from employees</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="LastName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>LastName</DataField>
</Field>
<Field Name="FirstName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>FirstName</DataField>
</Field>
<Field Name="Title">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Title</DataField>
</Field>
<Field Name="TitleOfCourtesy">
<rd:TypeName>System.String</rd:TypeName>
<DataField>TitleOfCourtesy</DataField>
</Field>
<Field Name="BirthDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>BirthDate</DataField>
</Field>
<Field Name="HireDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>HireDate</DataField>
</Field>
<Field Name="Address">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Address</DataField>
</Field>
<Field Name="City">
<rd:TypeName>System.String</rd:TypeName>
<DataField>City</DataField>
</Field>
<Field Name="Region">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Country</DataField>
</Field>
<Field Name="HomePhone">
<rd:TypeName>System.String</rd:TypeName>
<DataField>HomePhone</DataField>
</Field>
<Field Name="Extension">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Extension</DataField>
</Field>
<Field Name="Photo">
<rd:TypeName>System.Byte[]</rd:TypeName>
<DataField>Photo</DataField>
</Field>
<Field Name="Notes">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Notes</DataField>
</Field>
<Field Name="ReportsTo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ReportsTo</DataField>
</Field>
<Field Name="PhotoPath">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PhotoPath</DataField>
</Field>
<Field Name="ImageBase64">
<Value>=Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))</Value>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Robert,
I am in the same fix regarding the databound image situation. I've used the hidden text box field inside the body of the report that converts the image (a field in the dataset) to a string and then the actual image in the page header references the hidden text box. This works great in the preview of BI Studio. It also works when the report is exported to Excel. However, when exported to PDF, the image is not shown. Is this even possible?
Regarding the solution above, I tried your suggestion. However, I'm back to the situation where a "fields cannot be used in report parameter expressions". Is there something I'm missing in your explanation above? This particular report will eventually become an "rdlc" report on a remote site so I'm limited as to what I can do in the BI environment.
Any help or direction would be appreciated! I've spent way too much time on what should be a simple task, in my opinion, to implement.
Also, do you still have the sample report?
Thanks,
John
Sunday, February 26, 2012
Databinding question
Hi,
I have a page created within VS 2005 which uses a detailsView with a SQLDataSource which has insert, edit and delete items allowed with it.
The problem is that if I delete a record I dont want to refresh the page as I want to set a label value to say item deleted. The problem then though is to select the item to delete I have a drop down which populates the details view on index change, but if I delete the item I cannot do a databind when its complete because it just binds to the existing dataset and does not do a fresh call on the database.
Is there a command I can run to refresh the dataset on click of the delete button?
Thanks
I think I would do databinding in a sub that does nothing else but the databinding. I normally do this in a sub called something like sub bindcontrols() or something like that. Then when I need to refresh the data I can just call that sub.|||Thanks for your reply.
The problem is the databinding is handled by VS and I dont think I have a choice of where it runs.
|||try the following after deleting detailsView.databind() it should refresh the content Hope this helps
Databases not showing up in EM on sql 2000, but do in 2005.
We have a SQL 2000 server that started showing 'no items' in enterprise
manager. However, when you connect with SQL2005 client tools they show up.
Any ideas?Eric
Have you "Refresh" on database folder?
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AE0271E-33B9-4225-B007-A8D023103627@.microsoft.com...
> We have a SQL 2000 server that started showing 'no items' in enterprise
> manager. However, when you connect with SQL2005 client tools they show up.
> Any ideas?
manager. However, when you connect with SQL2005 client tools they show up.
Any ideas?Eric
Have you "Refresh" on database folder?
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AE0271E-33B9-4225-B007-A8D023103627@.microsoft.com...
> We have a SQL 2000 server that started showing 'no items' in enterprise
> manager. However, when you connect with SQL2005 client tools they show up.
> Any ideas?
Databases not showing up in EM on sql 2000, but do in 2005.
We have a SQL 2000 server that started showing 'no items' in enterprise
manager. However, when you connect with SQL2005 client tools they show up.
Any ideas?
Eric
Have you "Refresh" on database folder?
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AE0271E-33B9-4225-B007-A8D023103627@.microsoft.com...
> We have a SQL 2000 server that started showing 'no items' in enterprise
> manager. However, when you connect with SQL2005 client tools they show up.
> Any ideas?
manager. However, when you connect with SQL2005 client tools they show up.
Any ideas?
Eric
Have you "Refresh" on database folder?
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AE0271E-33B9-4225-B007-A8D023103627@.microsoft.com...
> We have a SQL 2000 server that started showing 'no items' in enterprise
> manager. However, when you connect with SQL2005 client tools they show up.
> Any ideas?
Databases not showing up in EM on sql 2000, but do in 2005.
We have a SQL 2000 server that started showing 'no items' in enterprise
manager. However, when you connect with SQL2005 client tools they show up.
Any ideas?Eric
Have you "Refresh" on database folder?
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AE0271E-33B9-4225-B007-A8D023103627@.microsoft.com...
> We have a SQL 2000 server that started showing 'no items' in enterprise
> manager. However, when you connect with SQL2005 client tools they show up.
> Any ideas?
manager. However, when you connect with SQL2005 client tools they show up.
Any ideas?Eric
Have you "Refresh" on database folder?
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:0AE0271E-33B9-4225-B007-A8D023103627@.microsoft.com...
> We have a SQL 2000 server that started showing 'no items' in enterprise
> manager. However, when you connect with SQL2005 client tools they show up.
> Any ideas?
Sunday, February 19, 2012
Database_suspect
OK, I am new to sqlserver. Anyway, I was trying to open up one of my db and it had database_suspect next to it and no items listed under it.
How do I resolve this to bring the db available once again. Thanks.Try this code:
sp_resetstatus [ @.DBName = ] 'database'
If you you on BOL and type in Suspect it will outline the steps that you need to take.
Thanks
Lystra|||...I don't think you want to reset the databases status (ti can be dangerous). you should look to restore the database...|||i got same prob few days ago. the below failed to restore in my case but it is said that its success is about 90%
good luck
*********************************************
-- Important!!!
-- There are 3 levels of repair that can be done.
-- You try step 1 = REPAIR_FAST first, if it does not work (db is still suspect in Enterprice Manager)
-- then you should stop service and start service again before continue with next step.
-- Next step 2 = REPAIR_REBUILD. If that does not work (still suspect) then you stop and start service.
-- Do NOT skip the stop and start service step between each time you try a higher level of repair. SQL needs to be reset.
-- Step 3 if the other 2 do not work is REPAIR_ALLOW_DATA_LOSS. There has been very few times when this one has not been sufficient.
-- It is normally not actual data that dissapears if REPAIR_ALLOW_DATA_LOSS can fix the problem but the other steps cannot
-- but rather primary keys, foreign keys, default constraints or indexes. There is no guarantee though.
-- This is step 1 - run this in master in query analyser and replace 'ips' with your database name
-- Note: this is name as seen in Enterprice Manager and not physical file name
dbcc checkdb ('ips',REPAIR_FAST)
-- This is step 2 - Do the same as step 1 (don't forget to stop and start service before)
dbcc checkdb ('ips',REPAIR_REBUILD)
-- Now starts step 3. Actual data CAN be lost with this command but it usually doesn't happen. Make sure you have copy
-- of the physical files (BOTH .mdf and .ldf!) before you run it. To only have copy of .mdf is sometimes enough, sometimes not, have BOTH!
-- To be able to run level 3, database must be in single user mode
-- Use this command in query analyser to set db as Single User mode. You should use it Directly after you have stopped and restarted
-- the SQL service so that the query analyser session you execute it in is the only user.
sp_dboption 'ips', 'single user', 'TRUE'
-- Step 3 - replace name the same as step 1
dbcc checkdb ('ips',REPAIR_ALLOW_DATA_LOSS)
-- If the repair works, then you can set back single user mode with this command
-- You can also go to database properties in Enterprice Manager
sp_dboption 'ips', 'single user', 'false'
-- resets the suspect status bit. Normally doesn't work but can always try. This command forces the suspect bit to off
-- stop and start service after and look in enterprice manager. If SQL cannot read database, then it willl set back
-- the suspect bit automatically.
exec sp_resetstatus 'ips'
-- Some more misc scripts that can be useful
/*
EXEC sp_detach_db 'inbound_mike', 'true' -- true and false apply to option 'skipchecks', loses replication!!!
EXEC sp_detach_db 'inbound', 'true' -- true and false apply to option 'skipchecks', loses replication!!!
-- after attach, users in that database will be un-usable. Run sp_dropuser 'ips' after and go into Enterprise Manager and re-create.
sp_dropuser 'ips'
EXEC sp_attach_db @.dbname = N'inbound_mike',
@.filename1 = N'd:\mssql7\data\inbound_Data_mike.mdf',
@.filename2 = N'd:\mssql7\data\inbound_Log_mike.ldf'
EXEC sp_attach_db @.dbname = N'SODUAT',
@.filename1 = N'e:\mssql7\data\SODUAT_Data.mdf',
@.filename2 = N'e:\mssql7\data\SODUAT_Log.ldf'
RESTORE DATABASE ips FROM DISK = 'd:\ipsdatabasedump.bak' -- must drop rep. regardless
WITH MOVE 'ips' TO 'g:\mssql7\data\ips.mdf',
MOVE 'ips_log' TO 'c:\mssql7\data\ips_log.ldf'
ALTER DATABASE Tempdb Modify file
( NAME = 'tempdev', FILENAME = 'e:\mssql7\data\ips_.mdf')
ALTER DATABASE Tempdb Modify file
( NAME = 'templog', FILENAME = 'h:\mssql7\data\tempdb.ldf')
exec xp_cmdshell 'net start "pcANYWHERE Host Service"'
exec xp_cmdshell 'net stop "pcANYWHERE Host Service"'
exec xp_cmdshell 'dir d:\mssql7\backup'
exec xp_cmdshell 'dir c:\mssql7\data'
exec xp_cmdshell 'del c:\mssql7\devtools\samples\backup\*.* /q' -- q = quiet, don't ask for confirmation when *.*
*/
How do I resolve this to bring the db available once again. Thanks.Try this code:
sp_resetstatus [ @.DBName = ] 'database'
If you you on BOL and type in Suspect it will outline the steps that you need to take.
Thanks
Lystra|||...I don't think you want to reset the databases status (ti can be dangerous). you should look to restore the database...|||i got same prob few days ago. the below failed to restore in my case but it is said that its success is about 90%
good luck
*********************************************
-- Important!!!
-- There are 3 levels of repair that can be done.
-- You try step 1 = REPAIR_FAST first, if it does not work (db is still suspect in Enterprice Manager)
-- then you should stop service and start service again before continue with next step.
-- Next step 2 = REPAIR_REBUILD. If that does not work (still suspect) then you stop and start service.
-- Do NOT skip the stop and start service step between each time you try a higher level of repair. SQL needs to be reset.
-- Step 3 if the other 2 do not work is REPAIR_ALLOW_DATA_LOSS. There has been very few times when this one has not been sufficient.
-- It is normally not actual data that dissapears if REPAIR_ALLOW_DATA_LOSS can fix the problem but the other steps cannot
-- but rather primary keys, foreign keys, default constraints or indexes. There is no guarantee though.
-- This is step 1 - run this in master in query analyser and replace 'ips' with your database name
-- Note: this is name as seen in Enterprice Manager and not physical file name
dbcc checkdb ('ips',REPAIR_FAST)
-- This is step 2 - Do the same as step 1 (don't forget to stop and start service before)
dbcc checkdb ('ips',REPAIR_REBUILD)
-- Now starts step 3. Actual data CAN be lost with this command but it usually doesn't happen. Make sure you have copy
-- of the physical files (BOTH .mdf and .ldf!) before you run it. To only have copy of .mdf is sometimes enough, sometimes not, have BOTH!
-- To be able to run level 3, database must be in single user mode
-- Use this command in query analyser to set db as Single User mode. You should use it Directly after you have stopped and restarted
-- the SQL service so that the query analyser session you execute it in is the only user.
sp_dboption 'ips', 'single user', 'TRUE'
-- Step 3 - replace name the same as step 1
dbcc checkdb ('ips',REPAIR_ALLOW_DATA_LOSS)
-- If the repair works, then you can set back single user mode with this command
-- You can also go to database properties in Enterprice Manager
sp_dboption 'ips', 'single user', 'false'
-- resets the suspect status bit. Normally doesn't work but can always try. This command forces the suspect bit to off
-- stop and start service after and look in enterprice manager. If SQL cannot read database, then it willl set back
-- the suspect bit automatically.
exec sp_resetstatus 'ips'
-- Some more misc scripts that can be useful
/*
EXEC sp_detach_db 'inbound_mike', 'true' -- true and false apply to option 'skipchecks', loses replication!!!
EXEC sp_detach_db 'inbound', 'true' -- true and false apply to option 'skipchecks', loses replication!!!
-- after attach, users in that database will be un-usable. Run sp_dropuser 'ips' after and go into Enterprise Manager and re-create.
sp_dropuser 'ips'
EXEC sp_attach_db @.dbname = N'inbound_mike',
@.filename1 = N'd:\mssql7\data\inbound_Data_mike.mdf',
@.filename2 = N'd:\mssql7\data\inbound_Log_mike.ldf'
EXEC sp_attach_db @.dbname = N'SODUAT',
@.filename1 = N'e:\mssql7\data\SODUAT_Data.mdf',
@.filename2 = N'e:\mssql7\data\SODUAT_Log.ldf'
RESTORE DATABASE ips FROM DISK = 'd:\ipsdatabasedump.bak' -- must drop rep. regardless
WITH MOVE 'ips' TO 'g:\mssql7\data\ips.mdf',
MOVE 'ips_log' TO 'c:\mssql7\data\ips_log.ldf'
ALTER DATABASE Tempdb Modify file
( NAME = 'tempdev', FILENAME = 'e:\mssql7\data\ips_.mdf')
ALTER DATABASE Tempdb Modify file
( NAME = 'templog', FILENAME = 'h:\mssql7\data\tempdb.ldf')
exec xp_cmdshell 'net start "pcANYWHERE Host Service"'
exec xp_cmdshell 'net stop "pcANYWHERE Host Service"'
exec xp_cmdshell 'dir d:\mssql7\backup'
exec xp_cmdshell 'dir c:\mssql7\data'
exec xp_cmdshell 'del c:\mssql7\devtools\samples\backup\*.* /q' -- q = quiet, don't ask for confirmation when *.*
*/
Subscribe to:
Posts (Atom)