Manage detached mdf file

I want to manage my sql express .mdf database file (e.g. set Roles) but I seem to only be working with a temporary instance of my database when I attach to .sqlexpress and manage in SQL Server Management Studio Express (SSMSE). The Role I set is gone when I detach and re-attach.

If I try to manage my file in Visual Studio under Server Explorer, I do not see the "Roles" folder to modify roles.

Any help is appreciated.

Roles don't disappear once they've been created as they are part of the database where they are created. If you're trying to create Server Roles, those obviously stay with the Server instance, not with the database. This is true even for temporary instances. There are a couple things that are probably causing confusion.

If you created your database in VS, you are likely working with a User Instance. This is a temporary instance that is create at runtime of your application. You can manage a User Instance similar to any other Instance of SQL Server, but you can only connect to it using Named Pipes. You will have to query sys.db_os_child_instances (see BOL for more info on this) to determine what the pipe name is for the User Instances on your computer. You should also be aware that each User has a separate and independent User Instance; this is important if you're talking about Server Roles, which would have to be created on every User Instance separately after the User Instance is created the first time it is used. Database Roles will travel with your database, so those are a bit easier to manage.

You may also be confused by the fact that VS actually creates several copies of your database as part of the Build and Debug process. It is possible that you are creating the Roles in one copy, but then looking for them in another copy. Check out the FAQ topic on why data doesn't appear to be saved in VS for an explaination of what's happening with the copies of the database.

If you give us an idea of what types of Roles you're looking to manage, we'll have a better idea what to suggest.

Mike

|||

Ok, I'll take a look at the information here. The type of role I am trying to create is Application Role. In order to use Full-text index on my database I cannot use User Instances. Therefore, I need to have an Application Role for the the application to login.

Yes, I created the database in VS.

Ok, I've resolved this by creating the database in sql express. You were right about the different copies of the mdf file. I guess if I don't want to use User Instances (which I don't) then I need to create the db in sql express and update my connection string to use either windows authentication or sql authentication.

I am having login issues however...will create another post for that.

Thank you.

Manage Data

Hello,
I am working on an ASP.NET web site using an SQL database.
Is there a way to Insert/Delete and Update data in the database
without creating all the forms in ASP.NET?
Since I am the only person to work with the database it would be
easier for me than creating all ASP.NET forms.
Thanks,
MiguelHi
Hmm, so get in SSMS and/or Query Builder and issue DML there.
"shapper" <mdmoura@.gmail.com> wrote in message
news:d128e2f4-c3fc-4873-b491-1ccc1ee40928@.t54g2000hsg.googlegroups.com...
> Hello,
> I am working on an ASP.NET web site using an SQL database.
> Is there a way to Insert/Delete and Update data in the database
> without creating all the forms in ASP.NET?
> Since I am the only person to work with the database it would be
> easier for me than creating all ASP.NET forms.
> Thanks,
> Miguel

Manage Data

Hello,
I am working on an ASP.NET web site using an SQL database.
Is there a way to Insert/Delete and Update data in the database
without creating all the forms in ASP.NET?
Since I am the only person to work with the database it would be
easier for me than creating all ASP.NET forms.
Thanks,
Miguel
Hi
Hmm, so get in SSMS and/or Query Builder and issue DML there.
"shapper" <mdmoura@.gmail.com> wrote in message
news:d128e2f4-c3fc-4873-b491-1ccc1ee40928@.t54g2000hsg.googlegroups.com...
> Hello,
> I am working on an ASP.NET web site using an SQL database.
> Is there a way to Insert/Delete and Update data in the database
> without creating all the forms in ASP.NET?
> Since I am the only person to work with the database it would be
> easier for me than creating all ASP.NET forms.
> Thanks,
> Miguel

Manage a data source for a report found in a SharePoint Document Library

I was working for 2 days on integrating reporting services with MOSS 2007.

The problem i faces was to create a data source for the

uploaded report (Not created using a wizard ) that will connect

me to the SQL server.

As i clicked on the arrow near the uploaded report to manage the data source, i found no

data source for this item.

I would be pleased to know the howto create this data source and where.

Best Regards,

Lana

Here are two recommended approaches to create a shared data source in MOSS 2007:
- Create one in the Report Designer and deploy it to the apprpriate SharePoint Document Library OR
- Add the "Report Data Source" content type from the Document Library Settings. Next, select the "Report Data Source" content type from the Document Library's "New" menu. Next, you should see a web page to create a shared data source.

Hope this helps.

thanx,
Prash

|||

Dear Prash,

I created the doc library that reads a data source

I uploaded a dataSource.rds but it was uploaded as hidden coz i

viewed the content of the doc in the Actions --> open with

explorer view.

Nevertheless, i created a new Report Data Source having the Data

Source Type as Microsoft SQL server but i didn't know what to insert in

theConnection string (Enter a connection string for accessing the report data source).

Although my connection string was empty, i clicked on my uploaded report to manage the data source but i found no data source for this item.

Thanks,

Lana

|||

Please note, that the .rds file is not the shared data source file in SharePoint. For details on creating Shared Data source Refer to SQL BOL topic "How to: Create and Manage Shared Data Sources (Reporting Services in SharePoint Integrated Mode) "

Have you tried deploying the shared data source and Report directly from the designer instead of manually uploading the file into SharePoint? If you manually upload a .rdl file with reference to a shared data source you will have to edit the reference once you have uploaded it to SharePoint via "Manage Data Sources". Generally, deploying the report and shared data source from the designer should preserve the reference from the report to the shared data source.

For details on connection string information refer to SQL BOL topic on "Connecting to a Data Source".

Hope this helps.

thanx,
Prash

sql

Making SSIS Replace instead of Add on

I am working on a configuration database in SSIS. One of the modules in the package is giving me endless amounts of grief. The module is for some reason set up to add data onto the end of any data already stored in the table. Instead, I would like it to replace this data. I have tried an Execute SQL Task that should delete all of the rows in the table, but this isn't working. Is there a more efficient way to do this?

-Kyle

No, sounds like you're on the right track. Destinations only add new data. If you want to remove the old data first then you should use an Execute SQL Task with a DELETE or TRUNCATE statement. Are you getting an error?
|||

Would a truncate table work (from an execute sql task or ole db command transform)?

truncate table myTable

|||

EWisdahl wrote:

Would a truncate table work (from an execute sql task or ole db command transform)?

truncate table myTable

Yes! Provided you have permissions to truncate.

making Report Parameters optional

I′m currently working on a report (SSRS 2005) with parameters which I want to make optional. In other words: when I open the report in the preview or IE, I′m always forced to enter values for every single parameter. But I want to have them optional so I can decide whether to choose values (as the option "Multi-value" is enabled) or leave them blank. (ignore in selection result)

is this possible? - I hope;-)

thanks in advance

pamike

The way to make entering parameter values optional is to give them default values. You can set the default value to give you all results and thereby achieve your result.|||

hi thanks for the answer csi_hugh, but i still don′t know:"non queried" or "from query" (or NULL?) and which value (expression) has to be set as default value?

thx

pamike

|||

The answer depends on your data. If the parameter is used in a Where condition of your Dataset, you have to consider what value will return all records based on the Where condition. In the case of Multi-select parameters, if you are using SP1 you can add the value "All" to the top of the choice lists, and then add Where conditions to your Dataset that if @.param = 'All' or Field = @.param. That will give you all records if you pick 'All'.

Hope that helps.

|||

I am also seeking an answer for this question. I guess we can do it with default values and using stored procedure to make the select.

Certainly we wish RS can provide this feature. it is fairly a basic feature.

|||

What we did to achieve this is to create a parameter with a (valid) default value.

Say, the parameter @.filter of type 'integer' is allowed to have a 'null' value and has default value 'null'.

We use this same default value in the query, like (very simple example)

SELECT * FROM Employee WHERE active=1 AND company_id=CASE WHEN @.filter IS NULL THEN company_id ELSE @.filter END

This way, if @.filter has a value other than 'null', we limit our query result to Employees with a company_id equal to @.filter. If @.filter has no value (it gets it's default value of 'null') then the query result is not limited based on company_id.

It's not pretty, but it works without creating stored procedures or functions.

making Report Parameters optional

I′m currently working on a report (SSRS 2005) with parameters which I want to make optional. In other words: when I open the report in the preview or IE, I′m always forced to enter values for every single parameter. But I want to have them optional so I can decide whether to choose values (as the option "Multi-value" is enabled) or leave them blank. (ignore in selection result)

is this possible? - I hope;-)

thanks in advance

pamike

The way to make entering parameter values optional is to give them default values. You can set the default value to give you all results and thereby achieve your result.|||

hi thanks for the answer csi_hugh, but i still don′t know:"non queried" or "from query" (or NULL?) and which value (expression) has to be set as default value?

thx

pamike

|||

The answer depends on your data. If the parameter is used in a Where condition of your Dataset, you have to consider what value will return all records based on the Where condition. In the case of Multi-select parameters, if you are using SP1 you can add the value "All" to the top of the choice lists, and then add Where conditions to your Dataset that if @.param = 'All' or Field = @.param. That will give you all records if you pick 'All'.

Hope that helps.

|||

I am also seeking an answer for this question. I guess we can do it with default values and using stored procedure to make the select.

Certainly we wish RS can provide this feature. it is fairly a basic feature.

|||

What we did to achieve this is to create a parameter with a (valid) default value.

Say, the parameter @.filter of type 'integer' is allowed to have a 'null' value and has default value 'null'.

We use this same default value in the query, like (very simple example)

SELECT * FROM Employee WHERE active=1 AND company_id=CASE WHEN @.filter IS NULL THEN company_id ELSE @.filter END

This way, if @.filter has a value other than 'null', we limit our query result to Employees with a company_id equal to @.filter. If @.filter has no value (it gets it's default value of 'null') then the query result is not limited based on company_id.

It's not pretty, but it works without creating stored procedures or functions.

Making field read only

Goodday all

I have a details view on a vb form .

Now that i have been working on the program , i realise that i would like to make some fields " read only '

Is that possible , and if so how?

Thanks

Rob

You could:

1 - Set permissions in SQL Server for SELECT only.
2. -Set the form properties to 'Read Only' (Enabled = False) for that textbox.

sql

Make sure the application for the project type (.rptproj) is installed

Hi,

Till Some time back my SSRS SSIS services were working fine. I was able to create/open any project from the Business Intelligence Development Studio. That time only BI suite was installed in Visual Studio 2005.

But due to some other requirements , we had to installed Complete VS.NET 2005 on the same system. After the installation i am only able to create/open ASP.NET applications. All earlier Reporting and Integration project are giving error on opening. Even i a m not able to create a new SSRS or SSIS application.

Error Message says:

"The application for the project 'c:\documents and settings\....rtpproj' is not installed.

Make sure the application for the project type (.rptproj) is installed"

I remote login to this server to work on.

There is another thing, one of my colleague when he log in to this system using his ID, he is able to create new porjects. This is system is not a Terminal Server in true sense.

So What should be the solution for this?.

Installation/Reinstallation of Sql Server component or reinstallation of everything.

Or may be some better option.Pls your suggestions will be highly appreciated.

Thanks

Sumit

I think you need to re-install the client components, especially the BI Development Studio|||Can you also check the .NET framework version installed after that VS installation.sql

Make sure the application for the project type (.rptproj) is installed

Hi,

Till Some time back my SSRS SSIS services were working fine. I was able to create/open any project from the Business Intelligence Development Studio. That time only BI suite was installed in Visual Studio 2005.

But due to some other requirements , we had to installed Complete VS.NET 2005 on the same system. After the installation i am only able to create/open ASP.NET applications. All earlier Reporting and Integration project are giving error on opening. Even i a m not able to create a new SSRS or SSIS application.

Error Message says:

"The application for the project 'c:\documents and settings\....rtpproj' is not installed.

Make sure the application for the project type (.rptproj) is installed"

I remote login to this server to work on.

There is another thing, one of my colleague when he log in to this system using his ID, he is able to create new porjects. This is system is not a Terminal Server in true sense.

So What should be the solution for this?.

Installation/Reinstallation of Sql Server component or reinstallation of everything.

Or may be some better option.Pls your suggestions will be highly appreciated.

Thanks

Sumit

I think you need to re-install the client components, especially the BI Development Studio|||Can you also check the .NET framework version installed after that VS installation.|||

I received this same exact error

wondering if you found a "quick" fix, or maybe just one that works

Please help

Major Problems with Calculated Fields

Is anyone else having problems with Calculated Members? Everything was working fine with viewing the cubes in the browser, in Reporting Services, and via Pivot tables...but once I put in some simple calculated members (simple parallel period functions), query times are extremely slow, everything locks up,unexpected Visual Studio crashes occur...SSAS pretty much becomes unusable.

Anyone with similar experiences?

You should see some performance improvements in SP1.

In limited number of cases you can use NON_EMPTY_BEHAVIOR when defining you calculated member. You got to be careful with it for parallel period such that you might not have a value for certain measure in parallel period. But if you can guarantee that, you should see way better performance.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Do you know if this is a problem with SSAS, Reporting Services, or the OLAP 9.0 Provider? I seem to be having the same problem when using Reporting Services 2000 and the 9.0 Provider. If I can't report on SSAS 2005 easily, I simply can't upgrade until the issues are resolved.

Maintenance Plan: Delete old files not working right

We do a full backup every night. For some reason the backup files are
staying on the server for 5 full days. Since each backup is 6GB this
is presenting a disk space problem. As far as we can tell, we've
changed all of our SQLServer Maintenance backup jobs to delete any
files older than 23 hours, and even rebooted the server to ensure any
old settings that were set for 5 day file deletion are gone.
Does anyone know why the server is continuing to store files as old as
5 days when we've set our systems up to delete after 23 hours? What
are we missing?
Thanks,
-Rob
Below KB might help:
http://support.microsoft.com/default...&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RW" <rwuhrman@.hotmail.com> wrote in message news:a628b717.0410270951.39dc39f3@.posting.google.c om...
> We do a full backup every night. For some reason the backup files are
> staying on the server for 5 full days. Since each backup is 6GB this
> is presenting a disk space problem. As far as we can tell, we've
> changed all of our SQLServer Maintenance backup jobs to delete any
> files older than 23 hours, and even rebooted the server to ensure any
> old settings that were set for 5 day file deletion are gone.
> Does anyone know why the server is continuing to store files as old as
> 5 days when we've set our systems up to delete after 23 hours? What
> are we missing?
> Thanks,
> -Rob

Maintenance Plan: Delete old files not working right

We do a full backup every night. For some reason the backup files are
staying on the server for 5 full days. Since each backup is 6GB this
is presenting a disk space problem. As far as we can tell, we've
changed all of our SQLServer Maintenance backup jobs to delete any
files older than 23 hours, and even rebooted the server to ensure any
old settings that were set for 5 day file deletion are gone.
Does anyone know why the server is continuing to store files as old as
5 days when we've set our systems up to delete after 23 hours? What
are we missing?
Thanks,
-RobBelow KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RW" <rwuhrman@.hotmail.com> wrote in message news:a628b717.0410270951.39dc39f3@.posting.google.com...
> We do a full backup every night. For some reason the backup files are
> staying on the server for 5 full days. Since each backup is 6GB this
> is presenting a disk space problem. As far as we can tell, we've
> changed all of our SQLServer Maintenance backup jobs to delete any
> files older than 23 hours, and even rebooted the server to ensure any
> old settings that were set for 5 day file deletion are gone.
> Does anyone know why the server is continuing to store files as old as
> 5 days when we've set our systems up to delete after 23 hours? What
> are we missing?
> Thanks,
> -Rob

Maintenance Plan: Delete old files not working right

We do a full backup every night. For some reason the backup files are
staying on the server for 5 full days. Since each backup is 6GB this
is presenting a disk space problem. As far as we can tell, we've
changed all of our SQLServer Maintenance backup jobs to delete any
files older than 23 hours, and even rebooted the server to ensure any
old settings that were set for 5 day file deletion are gone.
Does anyone know why the server is continuing to store files as old as
5 days when we've set our systems up to delete after 23 hours? What
are we missing?
Thanks,
-RobBelow KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RW" <rwuhrman@.hotmail.com> wrote in message news:a628b717.0410270951.39dc39f3@.posting.googl
e.com...
> We do a full backup every night. For some reason the backup files are
> staying on the server for 5 full days. Since each backup is 6GB this
> is presenting a disk space problem. As far as we can tell, we've
> changed all of our SQLServer Maintenance backup jobs to delete any
> files older than 23 hours, and even rebooted the server to ensure any
> old settings that were set for 5 day file deletion are gone.
> Does anyone know why the server is continuing to store files as old as
> 5 days when we've set our systems up to delete after 23 hours? What
> are we missing?
> Thanks,
> -Rob