New to Reporting Services

I am used to using Crystal Reports, but now with having Visual Studio 2005 and SQL Server 2005, I am trying to get along and use Reporting Services.

I need to understand or have somebody point me to a website or good book that I can use to learn about Report Server and the reports.

Here is the actual problem that I am having: I have built a report into the company intranet. This report works if I run it locally, however the print button does not show up understandably. Somebody mentioned making it a server-side report. So I imported the report into the Report Server of the SQL Server. At first I was getting an error message "The path of the item "\Reports\report.rdlc" is not valid. The path must be less than 260 characters long and restrictions apply. (rsInvalidItemPath)"

I believe I solved that error, but now when the page displays I do not get an error message, but I also do not have a report. The reportViewer is empty.

I then noticed that maybe I had to copy the report to IIS (I am using IIS 5.1). So I used the copy tool to copy the report to the directory for the report server in IIS (reportserver$sql). I have the property for the Server URL set to this directory and I have the report path set to \Reports\report.rdlc. Now I am getting an error message about "the item cannot be found."

Thanks for any information and help.

In SSRS all reports are actually stored inside of the Reporting Services database. As far I know, copying a file to the reporting server will not do anything for you. You need to 'Deploy' the report to the reporting services server from inside the report designer or BIDS (Business Intelligence Design Studio or something like that). However, you could also deploy the report from inside of reporting services web interface. I would also recommend the apress book on reporting services 2005. I bought the book because it was the first rs2005 book out of the gate. But it is actually really good. I started out with no knowledge of SSRS2005 and very limited knowledge of reporting technologies in general. This book made reporting services easy to learn.

Pro SQL Server 2005 Reporting Services

http://www.amazon.com/gp/product/1590594983/sr=1-4/qid=1138689829/ref=pd_bbs_4/002-0186147-5716036?%5Fencoding=UTF8

New SQL Database?

I posted this in "Visual Studio General" a few days ago, but got no response.

I am running VSTS 2005, and I have Microsoft SQL Server 2005 Developer Edition installed on my machine. I would now like to create an application that has its own database file (.mdf) included. From within my VS project, I go to "Add New Item". I select "SQL Database", put in the name, and hit [Add]. At that point I get a message box with the title "Local database file", saying "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed." Any ideas of what I'm doing wrong? Do I need SQL Server 2005 Express installed in addition to Developer Edition?

It's quite possible that this is well documented, or that I have a simple configuration set wrong, or that I'm doing something fundamentally wrong, and that all I need to do is RTFM, but despite searches, I couldn't find anything that addressed this directly. Can someone point me in the right direction? Also, if there's a better forum to post this to, please let me know.

Thanks!

Brad.

moved to the SQL forum|||

User instances are a feature of SQL Server Express, so you'll need to install SSE in addition to your Developer Edition instance to use this feature in Visual Studio.

Hope this helps,
Steve

New Server Registration to External Database

Hi All,

Quick question ...

Is it possible to add an external server registration using SQL Server Management Studio Express CTP?

Reason being I have been doing some work from home and need to port the data back to the office who are on SQL 2000. I have a database with my web hosting company and wanted to export the data to the 'live' database so I could import to the office database - any ideas?

I've tried to connect using my login details and I know the live DB is operational as I can connect through my work system. I just can't seem to be able to do it on my system at home.

Any help would be greatly appreciated as it's been a real pain to get stuff transferred. I've so far had to generate SQL script to create the procs and tables for when I get to work - the proper down side is that I lose the content of the tables which not much right now but will be loads soon!

Many Thanks

--

I have just dicovered that the CTP management studio will not connect to SQL 7 - is there any alternative or does the new version compensate for this?

There is now way to connect to SQL Server 7 in Management Studio(Express Edition or otherwise).

If you want to move the data from 2005 to 2000/7 your best bet is BCP or DTS/SSIS

|||

I'm afraid you will have to excuse my ignorance here as I am not a terribly experienced user of SQL Server. I've only be developing for the last year and a half and have always been able to use Enterprise Manager at work as everything is SQL 2000. This fortunately also connects to SQL 7 but SQL 2005 Express rather conveniently appears to have no easy backward compatibility.

So - what is BCP, DTS/SSIS and where might I find them on SQL 2005 Management Studio CTP - if that's where I should be looking?

So far, in order to migrate the structure of a database from 2005 to 2000 I have had to generate SQL scripts to create the tables and procs. A bit of a nightmare as I lose all data this way. Will the above solution resolve this issue?

Many Thanks

|||

First step is to upgrade from the CTP to the production version...

If you have the scripts then you now need the data, there are 2 choices, use whats called a linked server(Books On Line can help you try this out) or you can generate text files using BCP. DTS/SSIS is not available if you are pushing the data from a 2005 server, however you could try pulling it from the 2005 to the 7 server on the 7 server, ie have DTS in 7.0 connect to 2005.

BTW in your statement above you are mixing up the tools and the products...

EM and QA come with SQL 7 and 2000, the 2000 versions work with 7 and 2000.

SSMS comes with SQL 2005, it works with 2005 and 2000.

Once you have a database in a higher version (2000 or 2005) there is no easy way to go back to an older version (7 or 2000)

New Server Registration to External Database

Hi All,

Quick question ...

Is it possible to add an external server registration using SQL Server Management Studio Express CTP?

Reason being I have been doing some work from home and need to port the data back to the office who are on SQL 2000. I have a database with my web hosting company and wanted to export the data to the 'live' database so I could import to the office database - any ideas?

I've tried to connect using my login details and I know the live DB is operational as I can connect through my work system. I just can't seem to be able to do it on my system at home.

Any help would be greatly appreciated as it's been a real pain to get stuff transferred. I've so far had to generate SQL script to create the procs and tables for when I get to work - the proper down side is that I lose the content of the tables which not much right now but will be loads soon!

Many Thanks

--

I have just dicovered that the CTP management studio will not connect to SQL 7 - is there any alternative or does the new version compensate for this?

There is now way to connect to SQL Server 7 in Management Studio(Express Edition or otherwise).

If you want to move the data from 2005 to 2000/7 your best bet is BCP or DTS/SSIS

|||

I'm afraid you will have to excuse my ignorance here as I am not a terribly experienced user of SQL Server. I've only be developing for the last year and a half and have always been able to use Enterprise Manager at work as everything is SQL 2000. This fortunately also connects to SQL 7 but SQL 2005 Express rather conveniently appears to have no easy backward compatibility.

So - what is BCP, DTS/SSIS and where might I find them on SQL 2005 Management Studio CTP - if that's where I should be looking?

So far, in order to migrate the structure of a database from 2005 to 2000 I have had to generate SQL scripts to create the tables and procs. A bit of a nightmare as I lose all data this way. Will the above solution resolve this issue?

Many Thanks

|||

First step is to upgrade from the CTP to the production version...

If you have the scripts then you now need the data, there are 2 choices, use whats called a linked server(Books On Line can help you try this out) or you can generate text files using BCP. DTS/SSIS is not available if you are pushing the data from a 2005 server, however you could try pulling it from the 2005 to the 7 server on the 7 server, ie have DTS in 7.0 connect to 2005.

BTW in your statement above you are mixing up the tools and the products...

EM and QA come with SQL 7 and 2000, the 2000 versions work with 7 and 2000.

SSMS comes with SQL 2005, it works with 2005 and 2000.

Once you have a database in a higher version (2000 or 2005) there is no easy way to go back to an older version (7 or 2000)

New Query with Current Connection

I am missing "New Query with Current Connection" button in SQL Editor in the
SQL management studio. Few posts on the Web refer explain that this button
should allow me to open a new query in the existing window.
I've checked "Add Remove Buttons" for SQL editor but I still cannot find it.
Any ideas?
Bojan Kuhar wrote:
> I am missing "New Query with Current Connection" button in SQL Editor in the
> SQL management studio. Few posts on the Web refer explain that this button
> should allow me to open a new query in the existing window.
> I've checked "Add Remove Buttons" for SQL editor but I still cannot find it.
> Any ideas?
I'm not sure it can be done, but "Ctrl-N" will give you the "New query
with current connection".
Regards
Steen
|||Fantastic.
Now, one more. Is there something like "Open Query with Current Connection".
I'd like to avoid the Connect to Database Enginde dialog.
More-less like Qury Analyzer that allows you to open a new query in the
existing window.
Regards
Bojan
"Steen Persson (DK)" wrote:

> Bojan Kuhar wrote:
> I'm not sure it can be done, but "Ctrl-N" will give you the "New query
> with current connection".
> Regards
> Steen
>
|||My fault. The button was called New Query with Current Connection in one of
the betas. The released version renamed it to New Query. I changed most of
the references, but I missed a few. I think I got rid of them all by web
release 1 of Books Online though.
The New Query button will use the connection that has focus when you click
it. Either Object Explorer, or an existing Query Editor window. If your
current context has no connection, then it will pop up the Connect to Server
dialog box.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Bojan Kuhar" <BojanKuhar@.discussions.microsoft.com> wrote in message
news:01230DB5-BEE5-4452-B89E-470DCCE488F2@.microsoft.com...
>I am missing "New Query with Current Connection" button in SQL Editor in
>the
> SQL management studio. Few posts on the Web refer explain that this
> button
> should allow me to open a new query in the existing window.
> I've checked "Add Remove Buttons" for SQL editor but I still cannot find
> it.
> Any ideas?
|||Bojan Kuhar (BojanKuhar@.discussions.microsoft.com) writes:
> Fantastic.
> Now, one more. Is there something like "Open Query with Current
> Connection". I'd like to avoid the Connect to Database Enginde dialog.
> More-less like Qury Analyzer that allows you to open a new query in the
> existing window.
What Steen said, CTRL-N is the key. Provided one thing: under Tools->
Options->Keyboard select SQL 2000 instead of Standard.
The New Query button also works if you are in a query window or Object
Explorer, and so does File->New->Query with Current Connection.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

New project - thinking of using Visual studio 2005

Hi
I've been developing sql server stored procedures for what seems forever,
right now I just use query analyzer.
I have a new project, and just for a chuckle I'm thinking of using Visual
Studio 2005 for my IDE instead of query analyzer, I'm still pretty much just
going to be creating sql server stored procedures (SQL 2K).
Does anybody have any hints, gotcha's or guidance on whether this is a good
idea, and if so any tips?
I've played around, and one thing I can't find, can I run a SQL and have a
nice output to grid option, like with query analyzer?
The main reason I want to use this, is for being able to put all my sql in a
project, and the integration with sourcesafe.
Thanks in advanceInstead of the 'full' Visual Studio, use the Sql Server Management Studio.
When you disable the 'Summary' tab at startup, it behaves more or less the
same way as ye olde QA, but a little better :)
Yes, you can still have grids and text output :)
Peter
"..." <...@.nowhere.com> wrote in message
news:ehgLtTgQGHA.4896@.TK2MSFTNGP10.phx.gbl...
> Hi
> I've been developing sql server stored procedures for what seems forever,
> right now I just use query analyzer.
> I have a new project, and just for a chuckle I'm thinking of using Visual
> Studio 2005 for my IDE instead of query analyzer, I'm still pretty much
> just going to be creating sql server stored procedures (SQL 2K).
> Does anybody have any hints, gotcha's or guidance on whether this is a
> good idea, and if so any tips?
> I've played around, and one thing I can't find, can I run a SQL and have a
> nice output to grid option, like with query analyzer?
> The main reason I want to use this, is for being able to put all my sql in
> a project, and the integration with sourcesafe.
> Thanks in advance
>|||There is more flexibility within visual studio itself wrt managing your
project (you can add more folders for managing DDL/DML scripts and such).
Typically I manage my project and the source control integration from within
VS and jump back and forth to management studio depending on the specific
task at hand (say building up and testing a specific set of queries within a
larger procedure). SQL management studio allows for source control
integration and projects but is slightly different. The overall impression
I
have gotten from the two is that the SQL management studio projects are
geared more toward DBA work where as VS is more for the DB developer.
HTH
--Tony
"Rogas69" wrote:

> Instead of the 'full' Visual Studio, use the Sql Server Management Studio.
> When you disable the 'Summary' tab at startup, it behaves more or less the
> same way as ye olde QA, but a little better :)
> Yes, you can still have grids and text output :)
> Peter
> "..." <...@.nowhere.com> wrote in message
> news:ehgLtTgQGHA.4896@.TK2MSFTNGP10.phx.gbl...
>
>

New Installation - Can Not Connet to Server On My PC

I have just installed SQL Express and Server Management Studio on my PC. I also have IIS 5.0 on WinXP. When I open Management Studio and try to connect the sI get the following error message:- Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider) .

This happens if I change the server, change the authentication method or try my user name or the 'sa ' user. To make things even more frustrating I did the same installation on my laptop earlier this year with no problems.

Has anyone any answers to my problem?

Thanks

hi,

having modified the authentication property (and restarted the service), did you enable the sa login?.. verify in the Instance->Security->Logins->sa Properties (Status tab) the login is enabled..

regards

New Install VS 2005 Pro & SQL Server 2005 Dev Problem

VS looks OK and MSSQLSERVER and SQLEXPRESS etc. show in 'Services/Local' as started/automatic.

I am trying to do Management Studio Tutorial. For the change from SEM.

The problem is in opening SQL Server Management Studio there is a dialog

'Connect to Server' showing Type = Database engine, Authentication = Windows Authentication, Server name = blank.

When I enter Server name = MSSQLSERVER and press Connect I get an error message 'Cannot connect to MSSQLSERVER… Error 53.

How do I get to see the tutorial and recognise the SQL?

XP.Pro on a big fast machine for Web development all on the one machine

Some experience of SQL Server on W2K and W2K3 with VS. Notice the SQL Server does not show in the task bar as before.

To answer my own question in case it helps others.

This looks like Catch22 at first sight.

In 'Connect to Server' dialog Click 'Options'.

See a change to two tabs, 'Local' and 'Connection Properties'.

Click 'Server Name' and browse for more. Click.

Shows Browse for Servers and my two instances, with names different to what shows in Services. Is this for security?

Select, click Connect and the SSMC shows with 7 items, hurray.

The annoying thing is there is nothing to indicate how to do this on the dialog.

The Help topic of the dialog name does not help, as usual.

Polite comment. I notice there are few queries similar to my question in this forum. Does this mean I am unusual or people have a better place to go for such.

I will certainly be a frequent user of the forum for Web applications in VS/VB.

|||Thank you for sharing your solution. it may help others in the future.

Derekfoxes wrote:

Polite comment. I notice there are few queries similar to my question in this forum. Does this mean I am unusual or people have a better place to go for such.

There are a lot of people here who are very conversant with SQL Server. But installation and setup for SQL Server 2005 is relatively new and we're not yet used to all of the common pitfalls and their solutions. TheSQL Server newsgroups are probably the best place for that level of question as more of the SQL Server MVPs tend to hang out there, or perhaps theSQL Server Forums at the MSDN Forums.|||

Thanks for the reply and the two sites.

Trying to install Adventure Works now.

Tiny point, the forum ack and notifications are arriving duplicated.

|||

Derekfoxes wrote:

Tiny point, the forum ack and notifications are arriving duplicated.

Thanks for the alert; we areaware of the problem and the source code update should be forthcoming.

New Install of Sql server Express fails to restore Master database

Hello,

I have a fresh install of sqlExpress and Management Studio Express on my test server. I want to restore my master database from backup.

From the command prompt I set the Sqlservr -s SQLEXPRESS -m

Then I opened another comand prompt and ran my SQLCMD script to restore the Master Database.

here is the sql script:
RESTORE DATABASE [Master] FROM DISK = N'E:\COPLEYNEWSDATABASEBACKUP\Master.bak' WITH FILE = 1, MOVE N'mastlog' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Master_1.ldf', NOUNLOAD, STATS = 10
GO

I recieve the following error.

Msg 3154, Level 16, State 4, Server COPLEYNEWS\SQLEXPRESS, Line 1
The backup set holds a backup of a database other than the existing 'Master' dat
abase.
Msg 3013, Level 16, State 1, Server COPLEYNEWS\SQLEXPRESS, Line 1

How do I restore a Master Database on SQL Express?

the error indicates the backupset does not contain a backup of master. do 'restore headeronly' and verify its contents.

aslo, you will want to name the system database as "master" (all lower case) and do not include the "move" option as part of your system database restore.

|||

Thanks for the reply,

I change the *.sql restore script to:

RESTORE DATABASE [Master] FROM DISK = N'E:\COPLEYNEWSDATABASEBACKUP\Master.bak' WITH FILE= 1, NOUNLOAD, REPLACE, STATS = 10

GO

Now I get the error that I am restoring a Master.bak file to a SQL Server 9.0.1399 and I need to restore it to a SQL Server 9.0.2047 server.

So, right know I have SQL Server 9.0.2047 that has the Master.bak file.

I am trying to restore that file on a SQL Server 9.0.1399.

My problem right now is that I know I need to restore on the SQL Server 9.0.2047 but I can't figure out which version of SQL Server Express to download.
I thought it was the SQL Server Express w/advanced Services. There is also the issue of service packs for SQL Express.

Can someone please give me the download link to the correct version of SQL Server 9.0.2047?

Thanks in advance.

|||

version 9.0.1399 is RTM and 9.0.2047 is sp1.

you want to download the latest from site. it's sp1 patched.

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

|||

After a week of trying to figure out a working disaster recovery solution for sql express, I have finally succeeded.

I wanted to do a disaster recovery to the same server with a tape backup and then restore the Master.bak, Model.bak, MSDB.bak, and MyDatabase.bak, back to the SQLExpress DBMS. Let me start out by saying that you cannot do that unless you have a working copy of the Master.mdf on the server you are trying to restore to. The catch here is that the tape backup software will not back up the Master.mdf because its always being used by SQLExpress.

Now, I suppose I could have dettached the Master.mdf before I backed it up, but I decided to go a different route.

I found out that there is a directory called "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Template Data" and in it is a copy of the same contents (except I think that they are the original files created automatically by the original install, in other words, not uptodate) found in the "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data" directory where the files need to reside in order to do a restore to the database, which happened to be missing after I did my tape back up restore. I simply draged and dropped a copy of the missing *mdf, *ldf files and then I was able to launch the SQLExpress Studio Management Express and connect to the SQL Server. The only thing different was that the Model database was set to (Read Only) but that didn't concern me because I was going to restore all the Databases (Master, Model, MSDB, MyDatabase) anyway.

After that was solved, I could now run any *.sql restore scripts and have them work.

Next step is to restore the Master.mdf but you can only do that in single user mode.
So, I went into SQLServer Express, single user mode with the command prompt:
C:cd "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn"
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn: Sqlservr.exe -s SQLEXPRESS -m
Press<enter>
After it does its thing you need to open an additional command prompt window because it will say that its ready for connections in single user mode, but you will not be given a new cursor prompt.

So I opened a new comand prompt window:
C:\cd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
C:\Program Files\Microsoft SQL Server\90\Tools\Binn sqlcmd -S.\SQLExpress -i"E:\scripts\MASTERFULLRESTORE.sql"

This is what my MASTERFULLRESTORE.sql restore script file looks like (I created it previously using the GUI in sql express and then saved it to a script file instead of clicking on OK):
RESTORE DATABASE [Master] FROM DISK = N'E:\DATABASEBACKUP\Master.bak' WITH FILE= 1, NOUNLOAD, REPLACE, STATS = 10
GO

The SQL restore script will work this time becuase I have a working instance SQLExpress thanks to the previous drag and drop restore of the Master.mdf from the Template Data directory.

After that you need to go into Services and start the 'SQLServer Service'.
Then simply go into SQLExpress like normal and restore the Model, MSDB, from the GUI interface by right clicking on each one and selecting restore and then point to your *bak files.

When it came time to restore 'MyDatabase', I could see it in SQLExpress Management Studio Express but it was just a shell of it with no contents inside. This shell of my database is from the Master.mdf restore. Unfortunately, even though I could see it in the SQLExpress listing, when I right clicked on it, hoping for the restore option, It was grayed out. So I closed SQL Express and ran my restore.sql script from the command prompt using SQLCMD.exe and it succeeded.
So I opened a new comand prompt window:
C:\cd "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"
C:\Program Files\Microsoft SQL Server\90\Tools\Binn sqlcmd -S.\SQLExpress -i"E:\scripts\MYDATABASEFULLRESTORE.sql"

I then went back into SQLExpress and ran some queries to test and everything seems to be working now.

Thanks to everyone for helping to point me in the right direction to solve this problem.

New Folder link not doing anything

Ive just installed Reporting Services onto Window 2003. I have Visual studio
2003 installed and IIS 6
When I go to the report manager the 'New folder' link does not work nor can
i create a new data source or upload a file. I click on these and nothing
happens.
When I go to a different machine other than the one where the server is
installed and go to the Report manager URL these links work, however this is
problematic as the datasource and reports etc sit on the server.
Anyone have any similar problems?.. what i do notice is on the server i am
prompted for a username and password each time i start report manager and am
thinking this might have something to do with it, on a different machine i am
prompted once and then not again.
thanksAdd the URL to your report server to Local Intranet Sites in your browser.
That should take care of the username prompting, and might solve the
problems with the links not working.
Add http://myreportserver/ , not http://myreportserver/reports.
Kaisa M. Lindahl Lervik
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:5202B8BA-910D-4B66-A87F-7149FAC3CED3@.microsoft.com...
> Ive just installed Reporting Services onto Window 2003. I have Visual
> studio
> 2003 installed and IIS 6
> When I go to the report manager the 'New folder' link does not work nor
> can
> i create a new data source or upload a file. I click on these and nothing
> happens.
> When I go to a different machine other than the one where the server is
> installed and go to the Report manager URL these links work, however this
> is
> problematic as the datasource and reports etc sit on the server.
> Anyone have any similar problems?.. what i do notice is on the server i am
> prompted for a username and password each time i start report manager and
> am
> thinking this might have something to do with it, on a different machine i
> am
> prompted once and then not again.
> thanks|||If this is Reporting Services 1.1, then you may need to edit the config
files. I recall that something like http://machinename is stored in some
values by the isntallation routine. If you access SRS with a name other
than the machine name, the buttons stop working. Look in both config files
for Reports and ReportServer.
- Chris
"Charlie" <Charlie@.discussions.microsoft.com> wrote in message
news:5202B8BA-910D-4B66-A87F-7149FAC3CED3@.microsoft.com...
> Ive just installed Reporting Services onto Window 2003. I have Visual
> studio
> 2003 installed and IIS 6
> When I go to the report manager the 'New folder' link does not work nor
> can
> i create a new data source or upload a file. I click on these and nothing
> happens.
> When I go to a different machine other than the one where the server is
> installed and go to the Report manager URL these links work, however this
> is
> problematic as the datasource and reports etc sit on the server.
> Anyone have any similar problems?.. what i do notice is on the server i am
> prompted for a username and password each time i start report manager and
> am
> thinking this might have something to do with it, on a different machine i
> am
> prompted once and then not again.
> thanks

New database not included in "Back Up Database Task"

I'm having a problem with a Maintenance Plan created for SQL Server 2005 using Microsoft SQL Server Management Studio. See version information at the bottom of this post.

I have a Maintenance Plan using the "Back Up Database Task" that is set to perform a "Full" back up of "All Databases" on the local server. However, it appears that the list of "All databases" are hard-coded to be those databases that were available at the time the task was created. It seems any newly created databases don't appear to be in the list. If I attempt to edit the task and select the "These databases:" option, the newly created databases aren't even in the list.

Is there some way to have the task refresh the list of the databases available for backup? Ideally, I'd like any newly created databases to get backed up automatically without having to modify the task.

Thanks for your help,

Jonathan.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Hi,

I tested this on a SQL SP2 build 3175 and could not reproduce the issue.

Any newly added database automatically shows-up in the list.

I advise to patch and re-test.

Regards,

Philippe

|||After doing further tests, I determined that not all newly created databases are excluded from the backup. However, it appears that databases that were created on this SQL Server 2005 database and then had data restored from a .bak file from a SQL Server 7 database server are being excluded. I'm guessing it's a permission problem.

Can someone tell me exactly what users and/or roles need to be added to my databases in order for them to be backed up via the "Back UP Database Task"? Alternatively, if there's another non-permission solution, please let me know.

Thanks,

Jonathan.

|||

You must change the compatibility mode of your Database to SQL server 2005 this way it will allow all the features of SQL server 2005 to be used.... such as the backup.

Cheers

Byblyk

|||

Yep, that does it. For any others that run into the same problem and need a bit more handholding, open up "SQL Server Management Studio". Under "Databases", select right click your database and select "Properties". Under the "Options" page, set the "Compatability level" to "SQL Server 2005 (90)".

Note, it's important to make sure you check what changes to the compatability level mean before setting them willy nilly. Here's a useful guide:

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

Thanks Byblyk!

New database not included in "Back Up Database Task"

I'm having a problem with a Maintenance Plan created for SQL Server 2005 using Microsoft SQL Server Management Studio. See version information at the bottom of this post.

I have a Maintenance Plan using the "Back Up Database Task" that is set to perform a "Full" back up of "All Databases" on the local server. However, it appears that the list of "All databases" are hard-coded to be those databases that were available at the time the task was created. It seems any newly created databases don't appear to be in the list. If I attempt to edit the task and select the "These databases:" option, the newly created databases aren't even in the list.

Is there some way to have the task refresh the list of the databases available for backup? Ideally, I'd like any newly created databases to get backed up automatically without having to modify the task.

Thanks for your help,

Jonathan.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Hi,

I tested this on a SQL SP2 build 3175 and could not reproduce the issue.

Any newly added database automatically shows-up in the list.

I advise to patch and re-test.

Regards,

Philippe

|||After doing further tests, I determined that not all newly created databases are excluded from the backup. However, it appears that databases that were created on this SQL Server 2005 database and then had data restored from a .bak file from a SQL Server 7 database server are being excluded. I'm guessing it's a permission problem.

Can someone tell me exactly what users and/or roles need to be added to my databases in order for them to be backed up via the "Back UP Database Task"? Alternatively, if there's another non-permission solution, please let me know.

Thanks,

Jonathan.

|||

You must change the compatibility mode of your Database to SQL server 2005 this way it will allow all the features of SQL server 2005 to be used.... such as the backup.

Cheers

Byblyk

|||

Yep, that does it. For any others that run into the same problem and need a bit more handholding, open up "SQL Server Management Studio". Under "Databases", select right click your database and select "Properties". Under the "Options" page, set the "Compatability level" to "SQL Server 2005 (90)".

Note, it's important to make sure you check what changes to the compatability level mean before setting them willy nilly. Here's a useful guide:

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

Thanks Byblyk!

New database not included in "Back Up Database Task"

I'm having a problem with a Maintenance Plan created for SQL Server 2005 using Microsoft SQL Server Management Studio. See version information at the bottom of this post.

I have a Maintenance Plan using the "Back Up Database Task" that is set to perform a "Full" back up of "All Databases" on the local server. However, it appears that the list of "All databases" are hard-coded to be those databases that were available at the time the task was created. It seems any newly created databases don't appear to be in the list. If I attempt to edit the task and select the "These databases:" option, the newly created databases aren't even in the list.

Is there some way to have the task refresh the list of the databases available for backup? Ideally, I'd like any newly created databases to get backed up automatically without having to modify the task.

Thanks for your help,

Jonathan.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Hi,

I tested this on a SQL SP2 build 3175 and could not reproduce the issue.

Any newly added database automatically shows-up in the list.

I advise to patch and re-test.

Regards,

Philippe

|||After doing further tests, I determined that not all newly created databases are excluded from the backup. However, it appears that databases that were created on this SQL Server 2005 database and then had data restored from a .bak file from a SQL Server 7 database server are being excluded. I'm guessing it's a permission problem.

Can someone tell me exactly what users and/or roles need to be added to my databases in order for them to be backed up via the "Back UP Database Task"? Alternatively, if there's another non-permission solution, please let me know.

Thanks,

Jonathan.

|||

You must change the compatibility mode of your Database to SQL server 2005 this way it will allow all the features of SQL server 2005 to be used.... such as the backup.

Cheers

Byblyk

|||

Yep, that does it. For any others that run into the same problem and need a bit more handholding, open up "SQL Server Management Studio". Under "Databases", select right click your database and select "Properties". Under the "Options" page, set the "Compatability level" to "SQL Server 2005 (90)".

Note, it's important to make sure you check what changes to the compatability level mean before setting them willy nilly. Here's a useful guide:

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

Thanks Byblyk!

New data types in SQL Server 2005

SQL Server 2005 has some new data types like XML, varchar(MAX). Are they
supported by C# in Visual Studio 2003 and 2005?Hi Roy
From http://tinyurl.com/c2glu
DataTypes - UDTs, the XML data type, and "MAX" BLOBs and CLOBs
SQL Server 2005 adds support for user-defined types, a native XML data type
and better large data support. Large data support is improved by using the
Transact-SQL types VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX).
User-defined types and a native XML type are defined by the SQL:1999 and
SQL:2003 specifications. To use these data types with SqlClient, new classes
in the System.Data.SqlTypes namespace are defined (SqlUdt and SqlXml),
support is added to the SqlDbTypes enumeration and IDataReader.GetValue was
enhanced to support returning UDTs as .NET Object types, and support
returning XML as a .NET String.
These new SQL Server 2005 types are supported in DataReaders returned from
SQL SELECT statements and as Parameters using SqlParameter. A special class,
SqlMetaData, can return information about extended properties of these new
data types, such as the XML schema collection that a strongly typed XML
column adheres to, or the database name of a UDT. You can use these types
from the client directly, in generic code, and also in the DataSet. Finally
you can perform partial updates on the "MAX" data types from the client,
which required using special SQL functions before ADO.NET 2.0.
You may also want to look at the Hands-On Labs in the resource kit.
For more information check out http://msdn.microsoft.com/SQL/2005/
John
"Roy" wrote:

> SQL Server 2005 has some new data types like XML, varchar(MAX). Are they
> supported by C# in Visual Studio 2003 and 2005?

New Data Connection Problem in VS.NET 2005

I have all of the prerequisites installed:

- SQL Server 2005 Compact Edition

- Visual Studio 2005 SP1 (Standard Edition)

- SQL Server Management Studio Express SP2

When trying to add a new Compact Edition SQL DB data connection from within Visual Studio ("SQL Mobile", "SQL Compact...", etc.), nothing is available for it. I have Access, ODBC, SQL Server, SQL DB File, Oracle, and Other. Even when selecting other, there are no Compact/Mobile types available.

Please Help...I've been going crazy trying to get this option.

Thank you

Anybody? Anybody at all?|||

I guess you'll have to settle for me.

I too have had problems installing the lastest SP of Visual Studio (VS) which includes the latest SQL Server Compact Edition (SQLCe) binaries. The problem is that you must make sure that if you've installed any CTPs they must be scrubbed off first. So, assuming you've done that or never installed the beta CTPs (which were used to implement SQL Server Everywhere Edition), you need to first install VS 2005 SP1 from http://msdn2.microsoft.com/en-us/vstudio/bb265237.aspx. Next, you might also need to install the SQLCe MSI to get the bit installed in the GAC. These bits can be found here http://www.microsoft.com/sql/editions/compact/downloads.mspx. As shown in my EBook "Hitchhiker's Guide to SQL Server 2005 Compact Edition", you can also download the SDK but you should now see the SQL Server Compact Edition as one of the choices when creating a Data Connection.

hth

|||APipeDream

I am having the same problem - did you get anywhere with it ?
|||

No, that's the order I used already, and I don't have any CTPs installed. I also have the SDK installed.

|||

So am I.

I uninstalled/re-installed all componenets still no luck.

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Gotta create a DDEX Provider?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1477140&SiteID=1

|||

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Thanks This worked:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

New Data Connection Problem in VS.NET 2005

I have all of the prerequisites installed:

- SQL Server 2005 Compact Edition

- Visual Studio 2005 SP1 (Standard Edition)

- SQL Server Management Studio Express SP2

When trying to add a new Compact Edition SQL DB data connection from within Visual Studio ("SQL Mobile", "SQL Compact...", etc.), nothing is available for it. I have Access, ODBC, SQL Server, SQL DB File, Oracle, and Other. Even when selecting other, there are no Compact/Mobile types available.

Please Help...I've been going crazy trying to get this option.

Thank you

Anybody? Anybody at all?|||

I guess you'll have to settle for me.

I too have had problems installing the lastest SP of Visual Studio (VS) which includes the latest SQL Server Compact Edition (SQLCe) binaries. The problem is that you must make sure that if you've installed any CTPs they must be scrubbed off first. So, assuming you've done that or never installed the beta CTPs (which were used to implement SQL Server Everywhere Edition), you need to first install VS 2005 SP1 from http://msdn2.microsoft.com/en-us/vstudio/bb265237.aspx. Next, you might also need to install the SQLCe MSI to get the bit installed in the GAC. These bits can be found here http://www.microsoft.com/sql/editions/compact/downloads.mspx. As shown in my EBook "Hitchhiker's Guide to SQL Server 2005 Compact Edition", you can also download the SDK but you should now see the SQL Server Compact Edition as one of the choices when creating a Data Connection.

hth

|||APipeDream

I am having the same problem - did you get anywhere with it ?
|||

No, that's the order I used already, and I don't have any CTPs installed. I also have the SDK installed.

|||

So am I.

I uninstalled/re-installed all componenets still no luck.

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Gotta create a DDEX Provider?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1477140&SiteID=1

|||

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Thanks This worked:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

New Data Connection Problem in VS.NET 2005

I have all of the prerequisites installed:

- SQL Server 2005 Compact Edition

- Visual Studio 2005 SP1 (Standard Edition)

- SQL Server Management Studio Express SP2

When trying to add a new Compact Edition SQL DB data connection from within Visual Studio ("SQL Mobile", "SQL Compact...", etc.), nothing is available for it. I have Access, ODBC, SQL Server, SQL DB File, Oracle, and Other. Even when selecting other, there are no Compact/Mobile types available.

Please Help...I've been going crazy trying to get this option.

Thank you

Anybody? Anybody at all?|||

I guess you'll have to settle for me.

I too have had problems installing the lastest SP of Visual Studio (VS) which includes the latest SQL Server Compact Edition (SQLCe) binaries. The problem is that you must make sure that if you've installed any CTPs they must be scrubbed off first. So, assuming you've done that or never installed the beta CTPs (which were used to implement SQL Server Everywhere Edition), you need to first install VS 2005 SP1 from http://msdn2.microsoft.com/en-us/vstudio/bb265237.aspx. Next, you might also need to install the SQLCe MSI to get the bit installed in the GAC. These bits can be found here http://www.microsoft.com/sql/editions/compact/downloads.mspx. As shown in my EBook "Hitchhiker's Guide to SQL Server 2005 Compact Edition", you can also download the SDK but you should now see the SQL Server Compact Edition as one of the choices when creating a Data Connection.

hth

|||APipeDream

I am having the same problem - did you get anywhere with it ?
|||

No, that's the order I used already, and I don't have any CTPs installed. I also have the SDK installed.

|||

So am I.

I uninstalled/re-installed all componenets still no luck.

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Gotta create a DDEX Provider?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1477140&SiteID=1

|||

I found a solution:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.

|||

Thanks This worked:

I re-installed the following off the original install DVD - sqlmobile30devtoolsenu.msi, then installed the update SSCE31VSTools-ENU.exe which can be downloaded from Microsoft.