SQL tool for MSDE: ado

Showing posts with label ado. Show all posts

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.

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.