Managed Stored Procedures

Can anyone point me to a good resource to create managed stored procedures in SQL 2005 Express using VB.NET and VS2003?You could create managed stored procedures only using .NET 2.0.
VS2003 use .NET 1.1.
Good article about VB.NET and SQL/CLR: http://www.devx.com/dotnet/Article/21286

Manage with SQL Server Management Studio Express

I have read that SQL Server Compact Edition can be managed within SQL Server Management Studio Express.

Can anyone show me how to do it?

Or I can manage the Compact Edition in other way instead of in VS 2005.

Thanks a lot,

JD

SQL Server Management Studio Express SP2 will allow you to manage SQL Compact Edition (despite the statement on the download page). It can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=6053C6F8-82C8-479C-B25B-9ACA13141C9E&displaylang=en

sql

manage the stored procedure permissions....

hello all....can anyone tell me how can i manage the stored procedure permissions in SQL Server Express. Untill now i have developed in SQL Server 2000 and there i used SQL Enterprise Manager for this problem..in SQL Express i can't handle it...Manny thanks.....

hi,

for a graphic management tool, you can have a look at the Microsoft free provided one, you can get for free at http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

regards

|||

Thanks Andrea....but i have allready installed this tool...

in the mean time ....i've find the solution using that tool......go in the DB tree : Security-Users (choose the user that i want to add permisions) -Properties- Securables- here i add the objects and grant the permisions....

Manage SQL Express Over Lan

Hey Everyone

I have a desktop machine and a laptop machine. Both have XP Pro. I prefer to code on my laptop, but I want to use my desktop machine as a home server/development environment because its always on. I have IIS (HTTP and FTP), .NET 2.0, my mp3 server, etc up and running just fine on my desktop.

When I'm working on an application, I access the site with VWD through a network share. It's worked great so far. What I haven't been able to do, however, is connect to the database with VWD or Management Studio Express. I don't even really know where to begin with this one. What I don't want to do is open this up to the internet. I'd like to just keep it accessible from the LAN (the database, not the website)

I'm new to database stuff, and I don't really know where to look to figure out how to do this. Basically, I want to have the same functionality with VWD or Management Studio that I would have if I was physically on the machine with the SQL Express server.

If anyone can provide some advice, I'd really appreciate it.

Thanks!

Brandon

Any advice?|||

If you develop in your laptop then just get the no deployment Developer edition so you can develop with VWD in your laptop and move only finished code to the desktop. That way Express and IIS in the desktop will be deployment testing place because you can deploy with Express in house. If you choose to get it try the link below for the SQL Server 2005 developer edition. Hope this helps.

http://www.provantage.com/microsoft-e32-00575~7MCSB0EX.htm

|||

Thanks for the post!

Sorry. What I said was very vague.

I prefer to write code on my laptop, but the code is actually stored on my desktop. I access it from my laptop with a network share. I don't want my laptop to run IIS or SQL, that's what I want the desktop to do. So far, everything is working great. My desktop has IIS and SQL running very well. The problem is that I don't know how to work on the databases from my laptop. I can access the application code on my desktop through a network share, but I don't know how to access the SQL Server on my desktop.

Any ideas?

|||

If SQL Server is not in your laptop it is remote so you have to configure remote connection and if you don't have Management Studio installed you need it to configure the connection. The links below will help you and I don't know about VWD your should have a datalink property. At this moment you have developed only the application layer but you need both to run your application. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

|||You're amazing! Thank you!|||

bqmassey:

You're amazing! Thank you!

I am glad I could help.

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.

Making use of RGDI output

According to the Reporting Services feature matrix, the image output that Report Server Express has is something called RGDI. From what I've gathered, RGDI stands for Remote GDI.

What is the RGDI file format useful for? I don't know the proper file extension to give the file, nor do I know what viewer can view it.

Is there some way to convert the RGDI format to a more universally accepted file format such as .tif, .gif, .jpg, etc...? I'm hoping that I can just capture the binary stream, and convert the stream to a different image format before saving the file.

Thanks!

RGDI is not useful in itself, it's only usable and used by the Winforms report controls in VS 2005.

Thanks
Tudor|||

So you are saying that RGDI isn't available via a call to the Render() web service method to Report Server Express?

In that case, is Report Server Express going to support calling Render with a format of "IMAGE" and return a .tif file? If not, does RS Express support rendering to any sort of image type? The .tif functionality seems pretty useful.

Thanks.

|||

RGDI is available, but it only works with the WinForms Report Control. It consists of serialized GDI calls, which are not generally useful. The Express Server supports EMF format for printing.

|||

Brian, I would like to save the results of a Render() in the RGDI format to later be opened using a winforms application that may not have access to our servers.

Is it possible to save the RGDI results and open them later with the ReportViewer in a disconnected environment?

Making use of RGDI output

According to the Reporting Services feature matrix, the image output that Report Server Express has is something called RGDI. From what I've gathered, RGDI stands for Remote GDI.

What is the RGDI file format useful for? I don't know the proper file extension to give the file, nor do I know what viewer can view it.

Is there some way to convert the RGDI format to a more universally accepted file format such as .tif, .gif, .jpg, etc...? I'm hoping that I can just capture the binary stream, and convert the stream to a different image format before saving the file.

Thanks!

RGDI is not useful in itself, it's only usable and used by the Winforms report controls in VS 2005.

Thanks
Tudor|||

So you are saying that RGDI isn't available via a call to the Render() web service method to Report Server Express?

In that case, is Report Server Express going to support calling Render with a format of "IMAGE" and return a .tif file? If not, does RS Express support rendering to any sort of image type? The .tif functionality seems pretty useful.

Thanks.

|||

RGDI is available, but it only works with the WinForms Report Control. It consists of serialized GDI calls, which are not generally useful. The Express Server supports EMF format for printing.

|||

Brian, I would like to save the results of a Render() in the RGDI format to later be opened using a winforms application that may not have access to our servers.

Is it possible to save the RGDI results and open them later with the ReportViewer in a disconnected environment?

Making SQL 2005 Developer accept the connection string for SQL express edition

Hi, I have installed on my laptop SQL server 2005 Developer edition, I can change ANY of the settings of the SQL server and OS.

I am writing code with a team using continuous integration where the connection string is the following:
"Provider=SQLNCLI;Server=.\SQLEXPRESS;Database=Secret;Trusted_Connection=Yes;"

is there a way (create an alias,rename server, other?) to make my local dev SQL server accept the connection string above, as at the moment I have to rename the connection string every time before checking in code and after downloading the latest version from the code repository.

any advice or tips will be greatly appreciated

Thanks

JW.Hi,

setup an alias using the SQL Server Configuration manager. Configure a new alias to listen to .\sqlexpress and redirect to the installed dev server.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de

Making report add in work

Good day

I have been trying to install some type of reporting service . I have found a add in for web developer 2005 express . I drag the nessesay onto my form from the tool box and data table , but when I test the application all I get is xml , like below. Have I left something out. sorry I forgot to add that I also get this error on the default page-

An error has occurred during report processing.

ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'GetDatabyShipsID' that has no parameters.

<?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="ConnectionString">

+<ConnectionProperties>

<ConnectString/>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>baa3d63e-0dc9-43cf-9477-0040139b2ea1</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>2.5cm</BottomMargin>

<RightMargin>2.5cm</RightMargin>

Hi;

Ok from the error message you are getting ie. "An error has occurred during report processing.

ObjectDataSource 'ObjectDataSource1' could not find a non-generic method 'GetDatabyShipsID' that has no parameters." the error seems to indicate that the datasource you are using (ObjectDataSOurce1) is supposed to call a method called "GetDatebyShipsID" that has no parameters. Check if you have that method and add select parameters to your datasource.

Making Backup pay attention ?

Hi, i have a vb.net here i have 2 timers. one look every night at 2 o'clock pm and make a backup of my sql server express database to drive c:

In the Program runs at this moment some other action and another timer that checks different things and write this to the sql database .

are the some things that i have to pay attention when i make the backup ?

Or is it equal when i make a backup and at the same time i write data ?

SQL Server is perfectly capable of taking a backup while other operations are happening. Just remember that this could have impact on performance if your hardware is not properly sized.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Inaddition to what Wesley had said, always schedule the backup in off production hours so that the load will be less and it would'nt cause a performance bottleneck|||

though, sql server is capable of taking backup online... what i feel is its always better to take the backup after all the major activities are completed. You schedule the backup after the Program does the database activities. And also check the backup plan u need. If fullbackup is taking lot of time , take differential backup daily and weekly full backup. And also do not keep the backup and original database in the same location (c:\ which u have mentioned).

Madhu

|||

Okay thank you for your reply.

but somethimes i got the following error

************** Ausnahmetext **************
System.ArgumentOutOfRangeException: no negative number necessarily (Nicht negative Zahl erforderlich.)

Parametername: count
bei System.Buffer.BlockCopy(Array src, Int32 srcOffset, Array dst, Int32 dstOffset, Int32 count)
bei System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
bei System.Data.SqlClient.TdsParserStateObject.ReadString(Int32 length)
bei System.Data.SqlClient.TdsParser.ProcessError(Byte token, TdsParserStateObject stateObj)
bei System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
bei System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
bei System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
bei System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

This happens sometimes when i execute the following command

BACKUP DATABASE MYDBA TO DISK="c:\test.bak' WITH INIT,NOSKIP

and with this i execute it

Try

Dim cmdSQLServer As New SqlCommand

With cmdSQLServer

.CommandTimeout = 0

.CommandText = sSQLCommand

.CommandType = CommandType.Text

.Connection = g_Connection

intRowsAffected = .ExecuteNonQuery

End With

cmdSQLServer.Dispose()

Catch sqldbe As SqlException

End Try

|||

Did you see that you are using double quotes before the path ? The path should be enclosed in single quotes. You could also try the SMO namespace which enabled you to administer your SQL Server in a object-oriented way.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

making a report

how can i create a report for my database? how can i create bar graphs, etc.? im using msde and sql express. pls help. thanks.

MSDE and SQL Express are both free, and neither comes with any reporting tools, AFAIK. SQL Server may, I'm not sure, but the most likely way I would go about doing this is to write a program which reads the database, and then creates a graph from the data. There are free and commercial graphing tools about, but basic graphs are pretty easy to draw in code.

|||What you want is reporting services, but this only comes with the licensed sql server (2000 and 2005) versions which you have to pay for. However, you can create an ODBC link to a sql server database in excel, and produce the required reports there for free!

HTHsql

make a copy of database

Hi:

I have installed sql server 2005 express and SQL Server Management Studio Express

How can I generate a database from another copying the structure and data?

For example I have a database named Customers, I need to make a copy of Customers named Customers2. Customers2 also will be attached to the same Database Engine Server where Customers is attached.

How I Can do it?

Thanks!

P.S.

I tryied to make a copy of mdf an ldf files from Windows Explorer and renamed these files but I could not attach to the same Database Engine Server because I got an error.

The easiest way to do it is with the Backup and Restore Wizard because during restore you can change the name of the new one to customer2, I do it all the time, email the .bak or restore the .bak with a new name for a different department. If you don't have the SQL Server Express Advanced download it from the link below to use the Backup and Restore Wizard. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/

Maintenance Plans is SSE and Studio Management Express

Hi all,

I am using the Express versions of SQL Server and the Management console and can't seem to find anyway to set up basic maintenance plans.

Is this feature not in the Express management studio? I hope I am just missing it and it is not totally missing. I am trying to get my skills back up to speed so I can tackle some DBA jobs again and am working from home at the moment.

Can someone fill in the blanks for me to tell me if this infact is not available in the express management studio or where it is if it infact is available.

Many Thanks.

Steven

Maint Plans are not part of Express and hence not exposed in SSMS-E|||

Where can I find some scripts that allow scheduling of a backup operation?

I intend to schedule it through the Windows scheduler, as SSE has no scheduling agent.

E.

|||Hi edmund1,

Two example calls are below. They each do the same thing so you only need one of them:

osql -U sa -S .\SQLEXPRESS -Q"BACKUP DATABASE NateTest TO DISK = 'C:\NateTest.bak'" -o c:\osql_log.txt -P

osql -U sa -S .\SQLEXPRESS -Q"BACKUP DATABASE NateTest TO DISK = 'C:\NateTest.bak'" -P > c:\osql_log.txt

You can then schedule the batch script to run every so often. Basically the above called are connecting to a local SQL Server Express instance (.\SQLEXPRESS) using the "sa" account and will execute the BACKUP DATABASE command (which you can alter as appropriate). A log file (osql_log.txt) is produced and this will contain the outcome of the call plus any errors that happened etc. I have used the -P switch without a value to indicate that a NULL password is to be used for the account - if you use a SQL Server login that has a password you need to put it in after the -P switch, e.g. -P ******.

Other options for osql that you might use are:

* Windows authentication. In this case, remove the -U and -P switches and just put in a -E switch

* Passing a file that contains a list of commands to execute. In this case, remove the -Q switch and add in the -i switch with the name of the file to execute, e.g.

-i c:\query.qry

Hope that helps a bit but sorry if it doesn't
|||Good example but I strongly recomend using sqlcmd not osql as osql is going away and sqlcmd is richer.|||

Thanks both.

Yes, this helps alot. I will try to script via sqlcmd.

I am also trying to script a signle batch of all databases that have a common naming (for example, they all start with the letters "DB_"). I'll post my solution for others to see.

E.