Edit .mdf database

Greetings,

Are there any tools freely available to modify or edit a .mdf database
file? As far as seeing the actual tables?

Regards,
cdMSDE:
http://www.microsoft.com/sql/msde/
http://www.aspfaq.com/show.asp?id=2442

--
David Portas
SQL Server MVP
--|||No, and I'm not sure why you'd want to do this - even if you managed to
change data pages in a .mdf file directly, you would corrupt the
metadata (indexes, allocation maps, statistics etc.), and the
transaction log would also need to be updated. As far as I know, MSSQL
itself is the only tool that can read and modify .mdf files (at least
outside Microsoft).

I'm not sure what your real goal is, but if you need to retrieve data
from an .mdf file, then you can try attaching it to an MSSQL
installation with sp_attach_single_file_db. If you don't have an MSSQL
installation, you can download MSDE for free, although it's limited to
2GB databases.

If this isn't helpful, I suggest you give some more details of what
you're trying to do.

Simon|||I pretty much want to be able to look at a .mdf file to see what could
be mangled in it. If for some reason the application that writes to
the database gives an error because of invalid character or something
in a certain field i would like to see what field it is and what's in
it. Simply attaching it does me no good.|||If you are developing an application then you could attach the DB to
Developer Edition (cost $50) to test for this sort of problem with the
benefit of Query Analyzer, Profiler and the other tools.

Is this scenario something you have actually experienced? What makes
you think that you could have a problem caused by an "invalid
character"?

If you think a database is corrupt then the DBCC command can be used to
validate a table or database and fix these problems.

--
David Portas
SQL Server MVP
--|||I don't really understand what sort of errors you're talking about. If
you can't insert data into a table, then MSSQL will return an error
message, which you need to handle in your client:

http://www.sommarskog.se/error-handling-I.html

Perhaps if you can give a more specific example of the sort of error
you're getting, someone can suggest a solution. It would also be good
to know which version of MSSQL you have, what client application or
library you're using, what query you're executing etc.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||(mindphasr@.gmail.com) writes:
> I pretty much want to be able to look at a .mdf file to see what could
> be mangled in it.

If your database is corrupt so that you cannot easily repair it with
DBCC, you should open a case with Microsoft. Editing the internal
structures of database file will just mangle it even more.

> If for some reason the application that writes to the database gives an
> error because of invalid character or something in a certain field i
> would like to see what field it is and what's in it. Simply attaching it
> does me no good.

Your application cannot on its own corrupt the database. It would
need help from either a bug in SQL Server or bad hardware.

If your application produces an error message, you should first use the
Profiler to find where what statements the application is sending. Once
there you can run the queries in Query Analyzer. In QA you can run commands
like sp_help to see the definition of tables and constraints etc.

My guess is that you run a third-party app. In such, you should open a
support case with that vendor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Easy Report parameter Question

Im trying to hard code a Year in as an available value for the user to pick out of a drop down box. This is what i have so far.

Label Value

Travel Year 2006

well i want to go ahead and put two value for that one label , like this:

Label Value

Travel Year 2006, 2007

How do i do this? I tried putting a comma, and i tried putting a semi colon, but it always just grabs the first number "2006".

I know it cant be this hard! please help! THanks!

Enter another Label/Value combination like this:

Label Value

TravelYear 2006

TravelYear 2007

or you could write a little query to do it.

-Mike

|||

ok, this is actually not what im writing, i just tried to explain it in a simpler way, this is what i want

Label Value

Task National TN001, TN002, TN003, TN004, TN005, TN006, TN007

Task Vet TV001,TV002, TV003, TV004, TV005

Survey National SN001, SN002, SN003, SN004, SN005

and so on

Its a way of grouping the same type of tasks together in the parameter, so the user doesnt have to individually go through a long list of codes.

THere should be away to put one option with multiple values.

|||

You could manually create a dataset like this:

Select 'Task National' as Label, 'TN001, TN002, TN003, TN004, TN005, TN006, TN007' as Value

union

Select 'Task Vet' as Label, 'TV001,TV002, TV003, TV004, TV005' as Value

union

Select 'Survey National' as Label, 'SN001, SN002, SN003, SN004, SN005' as Value

Then parse the values and use them.

|||

I ended up just putting it in the where clause like this

Code Snippet

WHERE REGION_KEY=@.Region_Key

AND LEFT(Qry_Questions.[Question Code],2)IN (@.QuestionCode)

so it grouped the ones with the same 2 first letters. Works great!|||Nice job. Sometimes you have to be a little creative to get things to work right. :-)

Dynamically display/hide the parameter input

I have a handful of reports that are currently used by sales reps, and I'm trying to make them available to their regional VP's, and coporate users (executives and administrative staff that support Sales nationwide).

Currently, the reports take the UserID and resolve it to show the information that is only appropriate for that specific rep.

What I would like to do is have the parameter section at the top of the report be displayed for higher level users, so they could select an individual sales rep from a drop-down. (Ideally, the RVP's would only be able to select from reps in their region, but the corporate users would be able to select any rep.) The problem is, I don't want any of the sales reps to be able to select a rep other than themselves, for obvious reasons.

Is there a way to have the parameter section hidden/displayed dynamically, based on the UserID, so that users other than reps would have the ability to enter the desired rep name, but reps would not?

If you are using the .NET ReportView, it is just:

ReportViewer1.ShowParameterPrompts = False

|||I am using Report Manager.|||If you want any level of control, you are going to have to host the ReportViewer control in an ASP.NET page and customize the web page/report based on the user. There is not much you can do in ReportManager as far as fine control.|||

1. One way is to control this by writting a html page instead of report manager home page within which you will have a link to each of the report. Depending on who the user is you will change the path of the link (with or without parameters), meaning you will default all the parameters and pass them within the hidden link, but this wont work if you have more that 1 parameter and would want to hide only the user id parameter

2. Another option is to control this within the report. As your userid parameter list is being populated by a stored procedure, you will make the stored procedure accept a input parameter which is the userId of the person that is trying to run the report. Depending on who the user is the SP will return only the required users list.

Meaning

1. if XXX is passed and if XXX is RVP then it will return only the sales persons of XXX's region.

2. If YYY is passed and if YYY is sales person then it will return back only YYY

3. If CORP is passed and if CORP is a corporate user then the procedure will return all sales person's names

Hope this is clear and helps!!!

|||I think another way of achieving this is writting specific RDL code.|||

Thanks.

In the short term, this was the best solution. I wrote a sp that populates the drop down based on the NetLogin value.

So, the sales reps can see the drop down, but it only has their name in it.

|||You can also set the parameters visible in the URL using the &rsarameters=True/False.

i.e.

Code Snippet

http://SERVER/ReportServer/ReportPath&rs:Command=Render&rs:Parameters=False

HTH,
Jimmy

Dynamic Table Creation/Modification

I'm wondering if there is a control available for creating/modifying db tables through a web interface. I want for users to be able to add/remove, rename, and change the datatype of certain fields in a database table. I've been searching all day online if such a control exists in asp.net but haven't found anything.

The introduction to AJAX video centres around this very idea - he uses a GridView bound to a DataSet which is in turn bound to a SQL Express table. Anyway, you can view the video athttp://download.microsoft.com/download/7/8/f/78f2d61a-74c6-47b6-835c-0d1efa5524af/ScuttGu_asp_net_atlas.wmv. It's a good video to watch, anyway, very informative.