SQL tool for MSDE: net

Showing posts with label net. Show all posts

Thursday, March 22, 2012

Alert Not Being Triggered

Hi guys,

Hope you are all well.

I am trying to set up a 'SQL Server event alert' that will 'net send' a warning message to one of the Operators that have been set-up on our SQL Server.

The alert has been configured to act on severity level '009 - User Defined', for all databases.

I have created a user defined system message with a severity level '9' on the sysmessages table.

EXEC sp_addmessage 80000, 9, 'My User Defined Message'

...and I have raised this error on our SQL Server.

RAISERROR(80000, 9, 1) WITH LOG

The error message is being generated in the SQL Server Log file, but for some reason my Alert does not appear to fire off the 'net send' event.

The operator is 'available to receive notifications', and I have tested the 'net send' command successfully.

Does anyone know why this Alert might not be sending a message with the 'net send' command?

Thanks in advance,
KevinI found the 'problem'. There is a text box in the Alert properties labelled 'Error message contains this text'. I stupidly assumed that this would be the message displayed to the Operator, when in fact this actually restricts the alert to only those events that contain the text specified in the error message.

:o :D

Tuesday, March 20, 2012

Alert for CPU>80%

I am trying to create an alert which notifies me through
net send when CPU rises above 90% of the usage.
I tried to look under performance alerts through
enterprise manager but cant see the CPU options
Any ideasHi
I would set hardware (type) alerts in Performance/System Monitor and not a
SQL Server alert.
John
"Apok" wrote:
> I am trying to create an alert which notifies me through
> net send when CPU rises above 90% of the usage.
> I tried to look under performance alerts through
> enterprise manager but cant see the CPU options
> Any ideas
>|||Hi
I replied to this in microsoft.public.sqlserver.programming.
John
"Apok" wrote:
> I am trying to create an alert which notifies me through
> net send when CPU rises above 90% of the usage.
> I tried to look under performance alerts through
> enterprise manager but cant see the CPU options
> Any ideas
>

Friday, February 24, 2012

Again, I'm NOT sure how this works with the "Copy to Output Directory" settings as di

VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!

The following article is bogus and confusing:

How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy'
http://msdn2.microsoft.com/en-us/library/ms246989.aspx

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

Do not copy

The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.

You must manually copy the database file to the output directory
AFTER setting 'Copy to Output Directory' to 'Do not copy'.

The above article is bogus and confusing.

This is rediculous!

This is the most vague and convoluted bunch of nonsince I've ever come accross!

Getting caught out on this issue for the 10th time!
And not being able to find an exact step-by-step solution.

I've tried it and it doesn't work for me.

Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.

If you have a step-by-step procedure that can be reproduced this properly please PM me.

I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.

Many thanks.

This is the exact procedure I have come up with:

NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.

NOTE 2: DO NOT hand copy the database to a folder/directory in your project.

Yes, I know its hard not to do it because you want your project nice and tidy.
I just simply could NOT get it to work.
You should NOT have myData.mdf listed in the Solution Explorer. Ever.

Create a folder for your data following NOTE 2.

Copy your data to that folder. * mine was C:\mydata\myData.mdf

Create a NEW project.

Remove any Data Connections. ( no matter what)

Save it.

Data | View Data Sources

Add New Data Source

select NEW CONNECTION ( No Matter what, do it!

Select the database. * again mine was C:\mydata\myData.mdf

Answer NO to the question:
Would you like to copy the file to your project and modify the connection?
- NO ( no matter what - ANSWER NO ! - Absolutely NO )
Then select the tables you want in the DataSet.
and Finish.

To Test -

From the Solution Explorer | click the table name drop down arrow | select details
Now Drag the table name onto the form.

The form is then populated with a Navigation control
and matching Labels with corresponding Textboxes for each field in the table.

Save it.

1) Run the app.

Add one database record to the database by pressing the Add(+) icon

Just add some quick junk data that you don't mind getting lost if it doesn't save.

YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Now exit the application.

2) Run the app again.

And verify there is one record already there.

Now add a second database record to the database by pressing the Add (+) icon.

NOW add some quick junk data that you WILL intentionally loose.

*** DO NOT *** press the save icon.

Just Exit the app.

3) Again, Run the app.

Verify that the first record is still there.

Verify that the Second record is NOT there.
Its NOT there because you didn't save the data before exiting the app.

Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.

Also proving you must add your own code to catch the changes
and ask the user to save the data before exitiing or moving to another record.

As a side note, since vb.net uses detached datasets,
(a copy/snapshot of the dataset in memory and NOT directly linked to the database)
the dataset will reflect all changes made when moving around the detached datasets.
YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM.
Otherwise, they will simply be discarded without notice.

Whewh!

I hope this saves me some time the next time I want to start a new database project.

Oh, and uh, for anyone else reading this post.

Thanks,
Barry G. Sumpter

Currently working with:
Visual Basic 2005 Express
SQL Server 2005 Express

Developing Windows Forms with
101 Samples for Visual Basic 2005
using the DataGridView thru code
and every development wizard I can find within vb.net
unless otherwise individually stated within a thread.

Hi Barry,

What you describe will work as you suggest, but only when you are in Debug mode (F5). When you build you application into an EXE and deploy it to another machine (i.e. using ClickOnce, etc.) the database will not be included in the deployment and you're application will cause errors as it will be looking for the database in C:\Mydata that doesn't exist unless you've created it through some external process. Just realize that your solution is incomplete because it doesn't handle deployment, it only works for Debugging.

I offer two alternat solutions in the SQL Express blog, here. One is a more simple version of the KB article (it has limitations that are explained) and the second is a more involved deployment system that basically automates what you're talking about but includes a way to deploy the database when you deploy your application.

Mike

|||

Hi Mike,

Thanks for the reply.

You've obviously been there and I certaily appreciate your work and posts.
And please accept my well deserved respect for same.
I look forward to reading all your posts.

I'm not sure which solutions the blog in the reply was offering / refering to.

As the "Change the Copy to output directory property" just never worked in any variation.
And has been offered as a solution on many many resources.

Hince my post.

The web cast never loads properly and closes down all my explorer windows when I force it to close.
Which I think is pretty much like a hammer with no handle.
(I can't seem to grasp it very well.)
And a hyperlink at the bottom of the page with the appropfriate title takes me to some soft of a spam web page.
Which makes me dought the reseach thread.
Regretably, I've been putting up with this type of presentation for some time now.
Where a 30 sentence subject is so buried in advertising its hard to extract the information.
At this stage I can't confirm viability of the webcase information.
But would be very very interested in viewing it in its entirity.
Is there another place where the webcast can be viewed?
I would prefer to view the video before starting my first official project using vb.net 2005 and SQL Server 2005.
To prove to myself it can be done.

The reply although well intentioned, I believe, took my post out of context.
If I might be so bold as to explain:

My post addresses the issue the subject relates to in context.
i.e. ...saving updates to DB ... (during development)
and makes no mention of deployment.
My post also has step-by-step procedures and proof of solution.
And has already been confirmed by a number of resources.

As you can read in my other posts over the last few days I've been querying the subject of the reply.
Specifically, what version of SQL should I be developing against and then deploying.
(I'm sure we know there are a meriad of SQL Versions to add to the confusion.)
Perhaps this reply would better server as a response to one of those posts.
And mayhaps was intended for same.
I haven't had the chance as yet to sort out all the details regarding deployment, again, as mentioned in my other posts.
If the oneclick method doesn't allow me to edit the properties of the database location or add additional objects then
its a short coming of MS products I'm used to.
And not an assumed shortcoming of a deployment procedure I haven't been able to considered yet.

I'll happily research the issue when discoverd.
And perhaps find an easy workaround.
And, as always, I will post my findings once solidified.

All the best.
Great work.

And thanks again,

Barry G. Sumpter

|||

Hi all,

OK, Many thanks to the individual who requested to remain anonymous for arranging the video download for me.

The video answered heaps of my queries.
The video did mention that we ARE allowed to distribute SQL Server Express as long as we sign an agreement.

IS the distributable version the same one from the Express downloads?
Including the SQL Server Management Studio, etc.

Or is it a cut down version like MSDE with only a command line interace?
I'll have to reseach more on the "user instance" concepts and what it means and how to use it.

The DB installation technique mentioned is one we have been using in vb6 and MSAccess solutions for a number of years.
Where all new installs, updates, and major revisions are handled in scripts.
The scripts (in vb6 code) are Generated by third party tools to build the database from scratch, make updates, and convert/port the data for major revisions.

All executed thru code as mentioned. Either automated or thru an admin interface.
With a .ini file to point to the database on a file server somewhere.

Including versioned and date and time stamped backups, of course.

It's a lot of work keeping all the versions and revisions organized.
Same issues, different language, different database.

We were hoping MS had sorted this out. Oh well.

Does anyone know where the "Copy to Output Directory" concept came from?
Did it originate in some C version or Java or Web feature before .net?
Or is it a new concept for all in .net?

And does it actually work in non express versions?

All the best,

Barry G. Sumpter

|||

BarrySumpter wrote:
The video did mention that we ARE allowed to distribute SQL Server Express as long as we sign an agreement.
IS the distributable version the same one from the Express downloads?
Including the SQL Server Management Studio, etc.
Or is it a cut down version like MSDE with only a command line interace?

There are two "versions" of SQL Express, both of which you can distribute. We call them SQL Express and SQL Express with Advanced Services. You can see a high level break down of the differences here but the upshot is they are both identical database engines with Express Advanced offering some additional serices and the inclusion of Management Studio Express within the installer. (SSMSE is also available as a standalone installer.) Deployment of SSMSE is allowable as long as you are deploying it with SQL Express.

BarrySumpter wrote:
I'll have to reseach more on the "user instance" concepts and what it means and how to use it.

Start with the white paper

BarrySumpter wrote:
Same issues, different language, different database.

You can never get away from the classics. Seriously though, a database isn't a normal file that you can just replace to update it, this is true for any file that has user modifyable information. Everyone, MS included, has to cope with ways to "script" the structural update of user modifyable files without destroying the user data.

VS has recently released VS 2005 Team Systems for Database Professionals which introduces some great tools for creating, testing and version tracking of databases. It also has tools to create deployment and update scripts based on the version tracking system. If maintaining complex database across multiple versions is a significant portion of your life, I suggest you check it out.

BarrySumpter wrote:
Does anyone know where the "Copy to Output Directory" concept came from?
Did it originate in some C version or Java or Web feature before .net?
Or is it a new concept for all in .net?
And does it actually work in non express versions?

No clue where the idea came from, but it functions identically in all versions of VS 2005. Actually, this is a pretty slick technology for handling the deployment of project files in VS and it works quite well for VS files. The paradigm breaks down for database files as I discussed in the webcast as database files present some specific challenges with regards to distribution. VS is working to solve this problem for their next release.

For my purposes, 'Copy if Newer' has worked well once the database is more or less complete, but as mentioned, it doesn't prevent the copying of a new database over the old 100% of the time, so there will still be cases where you'll get a copy over. There are a myriad of ways you can solve this problem, which to use is based on your specific needs. One that isn't discussed often (maybe not enough) is to just put the database in the SQL Data directory, Attach it to the server and change your connection string. This is very similar to what you're already suggest as it requires that you do not put the database into the Project. It has the same issues when it comes to deployment time if your plan is to deploy the database to every computer as a local data store. If that is the case, you can always just add the database to the project (and change your connection string again) once you are ready to deploy.

Mike

|||

Hi Mike,

Thanks so much for your detailed replys and staying with this thread.

I do see the defference in the express editions now.

I will be needing client reporting on the SQL Express data.
The chart shows "Reporting Services" with Advanced Services.
I'm hoping its a proper reporting interface for building reports from sql express data.
And perhaps be able to save each report layout and execut them from a menu etc.

As a side note: As I speak with other vb6 to .net developers often, the subject of MSAccess reporting engine comes up regularly.
And how dissapointed we are in how MS hasn't persued the reporting engine as a stand alone reporting object,
as we confirm to one another that the MSAccess reporting engine is the best we've ever used.
The latest fantasy is to have the MSAccess reporting engine interface be able to access a sql server database.

Thanks so much for the hyperlink to the white paper: SQL Server 2005 Express Edition User Instances

Where I found heaps of info and an excellent definitions for:

User Instances

... A SQL Server instance is a SQL Server executable program running on a server. Each instance has a name, a sqlservr.exe process in memory, buffer memory, its own copies of the system databases, and its own set of user databases. By default, SQL Server Express installs as an instance named "SQLEXPRESS," for example. You connect to a named instance by specifying the instance name with the server name in the connection string. That is why you normally specify ".\SQLEXPRESS" as the server name when connecting to a local SQL Server Express database. The dot means the local server and \SQLEXPRESS specifies the SQLEXPRESS named instance. The SQL Server service (sqlservr.exe) runs as a Microsoft Windows service and executes in the user context of the user specified as the service account in the Windows service manager. For SQL Server Express, this account defaults to "NT AUTHORITY\NETWORK SERVICE" although a different account can be specified during setup.

SQL Server Express expands the concept of SQL Server instances by supporting user instances. A user instance is similar to a normal instance but it is created on demand while normal instances are created during setup. The service account for a user instance is the Windows user who opened the SQL Client connection to the database. In other words, if Sally opens a connection to a database file specifying the user instance option in the connection string, the user instance will have Sally as the service account.
...

Thanks for suggeting VS 2005 Team Systems for Database Professionals but I'll be looking for a no frills 3rd party tool to generate the scripts for me.
As you mentioned "Tried n True" procedures slighly modified to compensate for SQL Express idios are hard to get away from.

... "No clue where the idea came from" ...
Any C people out there? Java? Or Web? Have you seen this before?
Can anyone confrim its a "new idea" from MS?

For me I dought I'll be using 'Copy if Newer' and would like to be able to just turn it off completely.
And handle my db as dicussed.
I just don't see a time where the database is close to completion.
i.e. I exprect the database to always be in transition.
In some way or another.

I'll post my methodology once I get to it.

Maybe a class guru will be able build a souped up version everyone can use like the one in your article. And preferably in vb.net. ;)

Thanks again and all the best,

Barry G. Sumpter

|||

Here is someone who has actually gone as far as writting a Visual Studio addin to compensate for this anomoly.

It may help someone reading this post.

I HAVE NOT tested this as yet.

http://www.codeproject.com/useritems/db_manual_copy_add-in.asp

|||

Thanks for pointing out the add-in Barry, it's always nice to see the creative solutions from our customers. As I said, VS is working on this, so hopfully we'll see better behavior in the next version.

BarrySumpter wrote:
I will be needing client reporting on the SQL Express data.
The chart shows "Reporting Services" with Advanced Services.
I'm hoping its a proper reporting interface for building reports from sql express data.
And perhaps be able to save each report layout and execut them from a menu etc.

Let me give you the five minute primer on Reporting, Visual Studio Express and Reporting Services...

Reporting Services - This is a service that runs on top of IIS. The version that installs with SQL Express Advanced only supports local relational datasources. (i.e. It can only pull data from a relational database and the SQL instance has to be installed on the same computer where RS is installed.) The full version of RS that is installed with the paid Editions of SQL do not have these limitations.

I'm guessing that by "client reporting" you are looking to have reports built directly into your application. This is not what Reporting Sericies does, as I said, it's a service on IIS. The report building tools installed with the SQL Express Toolkit builds reports for the IIS based Reporting Services, not for client based reports in VS. Initially, VS Express Editions did not include any reporting fuctionality as this was considered enough of a value add that it was only release in the paid editions. VS later modified a bit by releasing a report viewer for all Express Editions and a report designer for VWD Express only. If you're goal is to create reports built into a Windows Forms application you actually need to have one of the paid editions of VS.

You're not totally out of luck if your application is going to be connected through. The report viewer that is available for all VS Express Editions can display reports that are hosted in Reporting Services as well, so you could create your reports for the RS system on IIS and then just show them in your client UI while you are connected to the network. Based on the limitations above, this will not really work for data that is stored locally in your project using a User Instance.

Mike

|||

Another excellent response.

Thanks for the heads-up!

Although, I'm not ready to start in depth research on the subject,
I'm wondering if I could use the inetExplorer control to view the RS/VWD Express Reports?

|||

Hi all,

In the same idea of using SQL Express taking it another step further and attepting to salvage some work where I used the vb.net wizards...

Subject: Is there an easy way to convert vb.net drag-n-drip wizard created dataset .xsd location to all code?

Desc: I need to convert fully funcitonal detailed forms SQL Server and DBName to code retrieval only.

As the subject and Description reads, I need to update my forms to programatically open a .ini file and get the Server and DBName and use them to point to the correct SQL Server Server and SQL Server Database Name.

Any hints to standardize this would be greatly appreciated.

I'm just a bit lost at the moment trying to sort all this out before continuing my first official assignment.

app.config

AttachDbFilename= is a place I can find that has the database

[code]

</configSections>

<add name="myApp.My.MySettings.myAppDataConnectionString"

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename='C:\myProjects\MyAppData\MyData.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True"

providerName="System.Data.SqlClient" />

....

[/code]

and Settings.settings

again the two AttachDbFilename= is a place I can find that has the database

[Code]

<?xml version='1.0' encoding='utf-8'?>

<ConnectionString>Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myData\myData.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True</ConnectionString>

<ProviderName>System.Data.SqlClient</ProviderName>

</SerializableConnectionString></DesignTimeValue>

<Value Profile="(Default)">Data Source=.\SQLEXPRESS;AttachDbFilename="C:\myData\myData.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True</Value>

</Setting>

</Settings>

</SettingsFile>

[/code]

Is there a way to override or replace these settings with one from a .ini file?

Ultimatly, my fantasy, is to be able to take full advantage of the drag-n-drop wizards so I can setup a master/detail form with database connections etc.
i.e. very quickly.

Then, hopefully, be able to change the code slightly, to point to a SQL express server and DBName retrieved from a .ini file.

I may be getting the new .net technology mixed up with the old. I hope I'm getting the jist accross.

|||

Maybe I could default the installation to the app directory?

And maybe to setting.settings and app.config script have a app.path variable I could use?

Also, ClickOnce Patterns and Practices Whitepaper Download

a 66 page document from an IDesign Newsletter.

|||

Hi Barry,

It depends on what you mean when you say RS/VWD Express Reports. The thing is, there are two different report formats, one that is hosted on Reporting Services, the other is the client report that you can create using the VS Designer. I don't know what the specific extentions are.

If you're refering to the reports that are hosted by Reporting Services, I'm guessing you could view them using the InetExplorer control, but I'm not sure that makes sence given that you can also view them using the ReportViewer control which is available for Windows Forms apps, even the ones you make in VS Express. Download it from here. (Should have given you that link before, but I got lazy. )

If you're talking about the reports created by the VS designer, I doubt those can be viewed in the inetExplorer control.

One idea I've been wondering about, but haven't had a chance to try myself is whether you can use the Report Designer addin for VWD Express (available here) to design reports and then copy those reports into your VB or C# projects. It's a bit of a round about solution, but if it get you there...

Mike

|||

Hi Mike,

Thanks for another detailed reply.

Yes, I'm getting just a bit too far ahead of myself at this point but will Favorite this thread in my SQL Reporting section.

Also, my solution for my database location issues:

Jeez! Took far far too long to nut this out myself!

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

At runtime, for me

DataDirectory is

C:\myVB2005ExpressProjects\MyProjectFolder\myProject\bin\Debug
I've confirmed this by placing my database there.

I'm NOT sure how this works with the "Copy to Output Directory" settings as discussed in this original post...

Running the Compiled executable:

Confirmed using Data sub directory:

MyProject | Settings | Type: (Connection Sting) |

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\data\myData.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

Build

Copy the .exe from ...bin\Release to C:\xxx

Copy myData.mdf and MyData_log.ldf to C:\xxx\Data\

NOTE: Data Subdirectory needed with this example.

Run the executable and test the database connection.

Works for me!

No convoluted class this or collection that.

Again, I'm NOT sure how this works with the "Copy to Output Directory" settings as discussed in this original post...

|||"...Again, I'm NOT sure how this works with the "Copy to Output Directory" settings as discussed in this original post......"

Well for crying out loud! and ROFL!

That DataDirectory directive is what vb.net express defaults too when using the dataconnection wizards!
Specifically, when answering Yes to the Question:
Would you like to copy the file to your project and modify the connection?

Which is where all this started in the first place.

Now that I've gone full circle this is what I've found:

I created a new project and ran thru the connection wizard to setup the dataset to point to my database
and included ALL tables.

I then dragged the DataSource to the form to get a grid.

The solution explorer then reflects the database .mdf file.

I view the properties for the .mdf file and set the "Copy to output directory" to "never copy"

Then run the app and get an error because the runtime can't find the database in the bin\debug\data folder

I then view the properties for the .mdf file again and set the "Copy to output directory" to "always copy"

Then run the app and make changes and find the changes are not saved by moving next and previous.

I then add an "Update" button and add the following to its _click event:

Me.Validate()
Me.TblBindingSource.EndEdit()
Me.TblTableAdapter.Update(Me.myDataSet.myTable)
(slightly changed here for context)

Then run the app and make changes and click the update button and find the changes are now saved correctly by moving next and previous.

I then exit the app and nrun the app and find the data has returned to its original state. i.e. no updates
(I believe its because I have set the "Copy to output directory" to "always copy".

Now that the "Copy to output directory" to "always copy" has copied the database to the bin\debug\data folder,
I now set the "Copy to output directory" to "Never Copy".
I then Run the app, make the changes, save the changes, verify changes, exit the app, run the app, and verify the changes are correct. Noteing that the database was not copied to the output directory.

I believe "Copy to output directory" should be "Replace db in debug folder" (always, never, when newer).
And should be defaulted to "Never".

Perhaps the combination of having to code the Me.TblTableAdapter.Update along with the "always copy"/"Always replace db" setting
AND receiving numerous IDE Errors is what made me doubt this functionality in the first place.

I DO NOT know how this effects Database updates after I've setup my datasets (.xsd).
I do know that Database updates ARE NOT reflected in the dataset .xsd files automatically.
In the past I had to also update the dataset .xsd files by hand.
Which makes multiple changes to the database at one time almost impossible.
If I had built the Dataset using the wizard (as before by selecting all tables) I could just rebuild it.
But found that I loose whatever additional queries I've added to the Dataset.

hmmm, following on, I'm now thinking the lost queries should have been created as stored procedures.
Then selected the tables and the stored procedures when rebuilding the dataset.

I must also mention that I did attempt to add a few querys to the new dataset in the new project
and received multiple vb.net ide errors but vb.net did NOT crash out as I recall previously.
Perhaps the Service Packs have corrected some annomoly since then.

Whew!

|||

Hello Barry,

Choosing the *.mdf in the Solution explorer and setting it's Properties >Advanced>Copy to Output Directory: to Copy if newer will solve the problem for the Tabor VB09 example.

Cheers,

Sunday, February 19, 2012

after SP1 getting rsInvalidDataSourceCredential

I am using a custom data source. (using ADO.net dataset). I don't store the
credentials on the report server. (basically I select the 4th option:
Credentials are not required). Everything was fine till I installed RS SP1.
Now whenever I run a report I get the following error:
rsInvalidDataSourceCredentialSetting 400 The current action cannot be
completed because the user data source credentials that are required to
execute this report are not stored in the report server database.
This is absurd, since I don't require credentials and I have specifcally
checked the option "Credentials are not required" for my data source.
Now comes the funny part. I decided to select the "Credentials stored
securely in the report server" option and gave "JUNK" values for user name
and password. Now my reports work!!!!!.. Whats going on ? This is crazy!
anyways.george tharakan wrote:
>I am using a custom data source. (using ADO.net dataset). I don't store the
> credentials on the report server. (basically I select the 4th option:
> Credentials are not required). Everything was fine till I installed RS SP1.
> Now whenever I run a report I get the following error:
> rsInvalidDataSourceCredentialSetting 400 The current action cannot be
> completed because the user data source credentials that are required to
> execute this report are not stored in the report server database.
> This is absurd, since I don't require credentials and I have specifcally
> checked the option "Credentials are not required" for my data source.
> Now comes the funny part. I decided to select the "Credentials stored
> securely in the report server" option and gave "JUNK" values for user name
> and password. Now my reports work!!!!!.. Whats going on ? This is crazy!
> anyways.
Microsoft Service Packs are always a ticket to adventure.
You never know where you'll eventually wind up.
GeoSynch|||Please check the following RS BOL webpage - in particular scroll to the
bottom and read the section about "No Credentials" very carefully:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_datasources_v1_87e9.asp
It says that you have to configure an account for "unattended report
processing". The following BOL webpage explains how to do this:
http://msdn.microsoft.com/library/en-us/rsadmin/htm/arp_configserver_v1_8teq.asp
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"GeoSynch" <SpamSlayed@.Casablanca.com> wrote in message
news:OYew$g9IFHA.2936@.TK2MSFTNGP15.phx.gbl...
> george tharakan wrote:
>>I am using a custom data source. (using ADO.net dataset). I don't store
>>the
>> credentials on the report server. (basically I select the 4th option:
>> Credentials are not required). Everything was fine till I installed RS
>> SP1.
>> Now whenever I run a report I get the following error:
>> rsInvalidDataSourceCredentialSetting 400 The current action cannot be
>> completed because the user data source credentials that are required to
>> execute this report are not stored in the report server database.
>> This is absurd, since I don't require credentials and I have specifcally
>> checked the option "Credentials are not required" for my data source.
>> Now comes the funny part. I decided to select the "Credentials stored
>> securely in the report server" option and gave "JUNK" values for user
>> name
>> and password. Now my reports work!!!!!.. Whats going on ? This is crazy!
>> anyways.
> Microsoft Service Packs are always a ticket to adventure.
> You never know where you'll eventually wind up.
>
> GeoSynch
>|||Thanks Robert.
I understand whats going on now.

after set new_broker, it works for half hour and don;t work anymore, please advise, thanks!

I use ASP.NET 2.0 and SQL 2005, the SQLCacheDependency didn't work stable for me, it usually works and after a while, it stops working. Recently, It doesn't work. Today I reset service broker by

Alter DB set new_broker

It seems all work, but too early to be happy, it doesn't work now anymore. I don't see any record in

sys.transmission_queue

And I don't see new entries in

sys.dm_qn_subscriptions neither when I modify a record which bind to SQLCacheDependy.

I see some errors in log like

Message
The query notification dialog on conversation handle '{8F8CC642-6340-DB11-8F09-0014227B7B80}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service 'SqlQueryNotificationService-f79776f7-9ca5-4c5f-8a66-0d81f7673683' because it does not exist.</Description></Error>'.

Any idea how to find out the problem?

thanks!

Have you applied this patch: http://support.microsoft.com/kb/913364/EN-US/ ?

HTH,
~ Remus

Thursday, February 16, 2012

After re-installing SQL server, app did not work

For some reasons, I uninstalled and reinstalled SQL server in local pc. Now, none of my app is working. My pc is XP professional with sp2 and VS.NET is 2003.
I spent a couple of days but no luck. It drived me crazy!
Help!!!did you add the MACHINENAME/ASPNET user account to the db?|||In Web.config file, I use "sa" in connection string, do I need to add ASP.NET account?|||if you are using the useraccount "sa" to connect to the sql server then the user "sa" should have access to the database.

Sunday, February 12, 2012

After an insert, how do I get the primary key of the new row?

I am using C# and ADO.NET

After executing an INSERT, I would like to retrieve the primary key of the last row inserted. I've tried running SELECT @.@.IDENTITY in a query, but I get an OleDbException with the message: {"Syntax error. in query expression 'SELECT @.@.IDENTITY'."}. does anyone know what to do?Hi,

have a look here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconretrievingidentityorautonumbervalues.asp

If you are on SQL2k5 you can use the new OUPUT parameter and put the information back via this technology.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

You can use DataTables and DataAdapter to make insertion in the database after the insertion into the database the DataAdapter retrive the values from database for automatically.

You have to use CommandBuilder for this purpose.
Make changes to the DataTable and then run UpdateDataBase method on that table of the DataAdaper..check MSDN for details.

Or you can create Stord procedure to Insert in DB after inseration it will get the PK and will return it for you.

Just Select Max(pk_id) From Table1

|||Akbar,

Select Max(pk_id) seems like a simple way to do this. In fact, I feel stupid for not thinking of it. One followup question to this method: Does this method fail if there are multiple writers writing to this database? This fails if someone else writes to the database before you send your second query, correct?|||The recommended ways of doing that is by using scope_identity()|||The aggregate MAX is a function that was used in the old days to get the highest / recent value. Its not practical / preferable / suggested / recommended / whatever in days of writing with multiple users to a database. Like Joyei said, I would rather use the SCOPE_IDENTITY() approach.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Someone asked me the same thing today and after a couple of hours I've came up with this (the code is in VB but I will try to explain the concept as well as I can):

I'll use a sample table from a SQL Server (I work with an SQL 2000 right now). The table is called _FluxModels and has two fields FluxModelID (an identity field) and FluxModel (a varchar (50) field).

In order to update the table I use a SqlConnection, a SqlDataAdapter, a DataTable and a DataRow.

Instead of generating the InsertCommand with a CommandBuilder, I declare a separate SqlCommand and use this for getting the CommandText and Parameters.

Then I pass the CommandText and the Parameters to the InsertCommand of the DataAdapter.

I added a new parameter "@.ID" (you can use any name you like) of type SqlDbType.Int.

I added this text to the CommandText of the InsertCommand : select @.ID=SCOPE_IDENTITY()

I set the UpdatedRowSource property of the InsertCommand to UpdateRowSource.OutputParameters (actually it works without this setting).

Now, whenever I add a new record, I have the FluxModelID returned in the @.ID parameter of the InsertCommand of the SqlDataAdapter.

Maybe this is not the best way for doing this but it works.

Here is the code I used:

Dim cn As New SqlConnection("Data Source=[YOUR SQL SERVER];Initial Catalog=[YOUR DATABASE NAME];Integrated Security=True")

Dim da As New SqlDataAdapter("Select * from _FluxModels", cn)

Dim db As New SqlCommandBuilder(da)

Dim dt As New DataTable

Dim drr() As DataRow 'array of DataRows used for updating

Dim dr As DataRow

Dim NewCmd As SqlCommand

Dim i As Integer

Dim p As SqlParameter

cn.Open()

'Use the NewCmd instead of da.InsertCommand

NewCmd = db.GetInsertCommand

'Initialize the da.InserCommand as a new SqlCommand

da.InsertCommand = New SqlCommand

'Pass the parameters from the generated command

For i = 0 To NewCmd.Parameters.Count - 1

p = New SqlParameter

p.ParameterName = NewCmd.Parameters(i).ParameterName

p.SourceColumn = NewCmd.Parameters(i).SourceColumn

p.Direction = NewCmd.Parameters(i).Direction

p.DbType = NewCmd.Parameters(i).DbType

p.Value = NewCmd.Parameters(i).Value

da.InsertCommand.Parameters.Add(p)

'Pass the connection to the InsertCommand

da.InsertCommand.Connection = da.SelectCommand.Connection

'and the Commandtext

da.InsertCommand.CommandText = NewCmd.CommandText

'modify the CommandText

da.InsertCommand.CommandText = da.InsertCommand.CommandText & " select @.ID=SCOPE_IDENTITY()"

'add the parameter for the identity

da.InsertCommand.Parameters.Add("@.ID", SqlDbType.Int, 4, "")

'set the parameter direction

da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Direction = ParameterDirection.Output

'works without the next line

'da.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters

'Fill the DataTable

da.Fill(dt)

'Add the new record

dr = dt.NewRow

dr("FluxModel") = "BBB"

dt.Rows.Add(dr)

'Use an array of 1 DataRow to update the database

ReDim drr(0)

drr(0) = dr

da.Update(drr)

'Test if the parameter contains a valid value

If Not da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Value Is System.DBNull.Value Then

'Display the identity field of the new record

MsgBox(da.InsertCommand.Parameters(da.InsertCommand.Parameters.Count - 1).Value)

End If

'Close the connection to the database

cn.Close()

I hope this will help you.