Adventureworks 2005

I am running sqlserver2005(ctp) and trying to get the sample database adventureworks loaded so that I have some examples of proc, views, etc.

I finally found the database and downloaded it but when I try to attach I get the following message:

"The database 'AdventureWorks' cannot be opened because it is version 611. This server supports version 603 and earlier. A downgrade path is not supported. Sould not open new database 'AdventureWorks'. CREATE DATABASE is aborted. "

I have MS SQL Server management Studio version 9.00.116.00
MS Analysis Services Client Tools 2000.090.1116.00
MS Data Access Components 2000.086.1830.00(srv03_sp1_rtm.050324-1447)
MS MSXML 2.6.3.0.4.0.6.0
MS IE 6.0.3790.1830
MS .NET Framework 2.0.50215.44
Operating System 5.2.3790

I believe all of this is up to date. Can someone tell me if I need an upgrade (and where that would be) or if there is a version of the AdventureWorks I can use with my current configuration(and where that would be)?

dfw

I think when you install the CTP, there should me a menu item that prompts you to install the sample databases and other tutorials. Did you check that out? If you are using the September CTP, its there in the Readme notes on how to do this.|||Many, many thanks.
I should have realized the files would be on the install disk. I check and there they are!

dfw

Advantages and disadvantages of stored proc encryption?

Security is good for me as for developer. But what are advantages and
divantages of stored proc encryption?
May be encrypted sp's are executing slow?Though encryption is good from security point of view, SQL Server 2000
stored procs can be easily decrypted. In terms of performance there's no
difference, as the execution plan will be the same. But if you do encrypt,
make sure you have the source readily available.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
Security is good for me as for developer. But what are advantages and
divantages of stored proc encryption?
May be encrypted sp's are executing slow?|||...also, troubleshooting in the production environment becomes a bit more d
ifficult. Like execute
the proc, compare the source (which you don't have) to the execution plan.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:udzTFmxnFHA.3540@.TK2MSFTNGP10.phx.gbl...
> Though encryption is good from security point of view, SQL Server 2000
> stored procs can be easily decrypted. In terms of performance there's no
> difference, as the execution plan will be the same. But if you do encrypt,
> make sure you have the source readily available.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
> news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
> Security is good for me as for developer. But what are advantages and
> divantages of stored proc encryption?
> May be encrypted sp's are executing slow?
>
>|||Oleg,
better is upload the code and its revisions to VSS
"Narayana Vyas Kondreddi" wrote:

> Though encryption is good from security point of view, SQL Server 2000
> stored procs can be easily decrypted. In terms of performance there's no
> difference, as the execution plan will be the same. But if you do encrypt,
> make sure you have the source readily available.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
> news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
> Security is good for me as for developer. But what are advantages and
> divantages of stored proc encryption?
> May be encrypted sp's are executing slow?
>
>|||Thank you All.
By the way,
what about encryption in sql2005 editions?
"Oleg Cherkasenko" <oleg@.opel.com.ua> wrote in message
news:eO8n$ixnFHA.3312@.tk2msftngp13.phx.gbl...
> Security is good for me as for developer. But what are advantages and
> divantages of stored proc encryption?
> May be encrypted sp's are executing slow?
>|||The encryption is trivial to break with freely available tools.
Encrypted code has very little to do with real security. It's pretty
much a cosmetic feature that *may* limit low-level meddling by
incompetent users but won't protect you against smart hackers.
David Portas
SQL Server MVP
--

ADSI and Reporting Services Permission issue.

I have a stored proc that calls on a view in sql server. The view is
actually my Active Directory LDAP query. I have already created a
linked server and account for my user name jkim.
I have a report that polls data from this stored proc. After I set the
datasource which uses an account "sqluser", I try to the run the report
and I get this:
An error has occurred during report processing. (rsProcessingAborted)
Get Online Help
Query execution failed for data set 'DispatchActivity'.
(rsErrorExecutingCommand) Get Online Help
OLE DB provider 'ADSDSOObject' reported an error. The provider
indicates that the user did not have the permission to perform the
operation.
The data source uses the account "sqluser" which is an account that's
stored in the report server. I'm pretty sure this is what's causing
the error. Does 'sqluser' have to be an actual Active Directory user
for it to have access to the ADSDSOObject?
I don't know if I'm making any sense.nevermind, i figured it out.
i didn't check the "use as windows authentication" checkbox in the
datasource.

ADODB fill recrodset with stored proc

I have a ADODB recordset problem. I want to use a stored proc to retrieve a
recordset. Code goes something like:
Set SQLCmd = New ADODB.Command
Set SQLCmd.ActiveConnection = SQLConn
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = SQLConn
rs.CursorType = adOpenForwardOnly
SQLCmd.CommandText = "usp_RPT_PForm_SetWhereTableBooks"
SQLCmd.CommandType = adCmdStoredProc
'*** Set up parameters
SQLCmd.Parameters.Refresh
SQLCmd.Parameters(1) = "aaa"
…
SQLCmd.Parameters(6) = 0
Set rs = SQLCmd.Execute
The proc usp_RPT_PForm_SetWhereTableBooks should returns a recordset
Pre tests:
I know the connection is active because when I call
SQLCmd.Parameters.Refresh the parameter list (of 6 items) are created.
I have copied the parameters into the sql query analyzer and records are
returned
The problem is that the recordset is not being filled
Can you tell me what I am missing please.
Thanks in advance,
Stewart RogersDo you have "Set NoCOunt On"
at the beginning of your Stored Proc '
If Not, add it and try again...
"Datasort" wrote:

> I have a ADODB recordset problem. I want to use a stored proc to retrieve
a
> recordset. Code goes something like:
>
> Set SQLCmd = New ADODB.Command
> Set SQLCmd.ActiveConnection = SQLConn
> Set rs = New ADODB.Recordset
> Set rs.ActiveConnection = SQLConn
> rs.CursorType = adOpenForwardOnly
>
> SQLCmd.CommandText = "usp_RPT_PForm_SetWhereTableBooks"
> SQLCmd.CommandType = adCmdStoredProc
> '*** Set up parameters
> SQLCmd.Parameters.Refresh
> SQLCmd.Parameters(1) = "aaa"
> …
> SQLCmd.Parameters(6) = 0
> Set rs = SQLCmd.Execute
> The proc usp_RPT_PForm_SetWhereTableBooks should returns a recordset
> Pre tests:
> I know the connection is active because when I call
> SQLCmd.Parameters.Refresh the parameter list (of 6 items) are created.
> I have copied the parameters into the sql query analyzer and records are
> returned
> The problem is that the recordset is not being filled
> Can you tell me what I am missing please.
> Thanks in advance,
> Stewart Rogers
>|||try this:
Dim rs As Adodb.Recordset
Set rs = new ADODB.Recordset
SQLConn.usp_Rpt_PFrom_SetWhereTableBooks "aaa", rs
I bet that works.
Greg Jackson
Portland, OR|||That did it ... Thanks!!! Can you tell me why it works with nocount on?
"CBretana" wrote:
> Do you have "Set NoCOunt On"
> at the beginning of your Stored Proc '
> If Not, add it and try again...
>
> "Datasort" wrote:
>|||Datasort...
Yes, what's going on is that SQL Server sends the Record count information
ahead of the actual recordset... and ADO is not smart enough to tell the
difference, so it's trying to "Read" the record count info as your recordset
,
and failing... It couldn' be coded to figure this out, because ADO 2.x added
functionality to handle multiple recordsets in one call to the server.
There's a method (on the RecordSet Object) in ADO called .NextRecordset, tha
t
moves to the next one in the sequence, until it retruns null when you call i
t
on the last one.
"Datasort" wrote:
> That did it ... Thanks!!! Can you tell me why it works with nocount on?
> "CBretana" wrote:
>|||And, obviously, adding Set NoCount On, removes this information from the TDS
(Tabular Data Stream), and then all that's sent backto ADO Is the actual
recordset...
"Datasort" wrote:
> That did it ... Thanks!!! Can you tell me why it works with nocount on?
> "CBretana" wrote:
>|||Odd, horribly odd!
That sounds like a bug since the record count is not returned (or was not
returned) as a recordset, it is returned as a message AFAIK. Further, the
record count is returned after the requested recordset, not before since it
doesn't know the count until it has returned the rerdorset.
So, I would prefer to see this regarded as a BIG BAD BUG!
Comments?
- Tim
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:C6A14044-735D-4DD7-802D-1BB719A4A40F@.microsoft.com...
> Datasort...
> Yes, what's going on is that SQL Server sends the Record count information
> ahead of the actual recordset... and ADO is not smart enough to tell the
> difference, so it's trying to "Read" the record count info as your
> recordset,
> and failing... It couldn' be coded to figure this out, because ADO 2.x
> added
> functionality to handle multiple recordsets in one call to the server.
> There's a method (on the RecordSet Object) in ADO called .NextRecordset,
> that
> moves to the next one in the sequence, until it retruns null when you call
> it
> on the last one.
> "Datasort" wrote:
>|||this is common and well documented behavior in ado
GAJ|||So its a "feature" then?
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23nc7lqYKFHA.3420@.tk2msftngp13.phx.gbl...
> this is common and well documented behavior in ado
>
> GAJ
>

ADO.NET & SELECT ... UNION issue

Hi,

I have discovered something weird.

I prepared a dataset that consists of a table adapter which has a select command of type stored proc. My stored procedure performs a select on a table1 and then table2 using UNION. My stored proc is running perfectly in the SQL Management studio (SQL2005) - no questions.

In Visual Studio when I test my dataset querying my tableadapter I get a result that is not just a UNION but a join of empty columns (number of empty columns = number of columns from one of my tables) and then the result of select statement from my stored proc.

And then my asp.net code fails as well because my gridView is expecting only 3 columns but instead I am getting 6 (3 empty + 3 those I was expecting in the first place.)

I think ADO.NET converted you UNION to a JOIN which either means your table was UNION compatible implicitly or ADO.NET internally is using very old SET operation based JOIN syntax. So check your table to see if it is explicitly UNION compatible, I am assuming you know UNION performs implicit distinct to remove duplicates so its requirements are strict compared to UNION ALL which includes duplicate rows. Hope this helps.

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

|||

Hey,

That is exactly what happened. ADO.net converted my resultset to some sort of join and I know why.

The thing that caused that abnormal behavior was I used a join on two tables that have different column names. Once I've changed my select statement in both parts of the union to return exactly the same names of columns it's started working properly.

ADO and the RowID

I am trying to update a SQL db record with ADO commands from an asp page thru a stored proc using the RowID and it is not working. RowID is the Identity seed record.

Here is the stored proc:

CREATE PROCEDURE [sp_Update_tblECMTimeTrackingMain]
@.RowID int,
@.StartTime datetime,
@.EndTime datetime,
@.TransxStatus varchar(50)
AS
Begin
UPDATE [tblECMTimeTrackingMain]
SET FStartTime = @.StartTime,
FEndTime = @.EndTime,
TransxStatus = @.TransxStatus
where RowID = @.RowID
End
GO

Here is the asp/ado code:

set cmdINSERT = Server.CreateObject("ADODB.command") cmdINSERT.ActiveConnection = strCONN_DATA cmdINSERT.CommandText = "[sp_Update_tblECMTimeTrackingMain]" cmdINSERT.CommandType = 4

set param = cmdINSERT.CreateParameter("@.RowID",3,1,4) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.FStartTime",135,1,8) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.FEndTime",135,1,8) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.TransxStatus",129,1,50) cmdINSERT.Parameters.Append param

cmdINSERT.Parameters(0) = CInt(mACTREFNUM) ...when I change this to "568" it actually does the update. Yes there is something in mACTREFNUM.

cmdINSERT.Parameters(1) = meStartTime

cmdINSERT.Parameters(2) = meEndTime

cmdINSERT.Parameters(3) = meStatus

cmdINSERT.Execute lngRECS,,128

Any clues?

Is there any indication as to what, if any, error is happening? What value is stored in mACTREFNUM and can you verify that the result of CInt(mACTREFNUM) is, in fact, an integer value?

Thanks,

Jason

ADO and the RowID

I am trying to update a SQL db record with ADO commands from an asp page thru a stored proc using the RowID and it is not working. RowID is the Identity seed record.

Here is the stored proc:

CREATE PROCEDURE [sp_Update_tblECMTimeTrackingMain]
@.RowID int,
@.StartTime datetime,
@.EndTime datetime,
@.TransxStatus varchar(50)
AS
Begin
UPDATE [tblECMTimeTrackingMain]
SET FStartTime = @.StartTime,
FEndTime = @.EndTime,
TransxStatus = @.TransxStatus
where RowID = @.RowID
End
GO

Here is the asp/ado code:

set cmdINSERT = Server.CreateObject("ADODB.command") cmdINSERT.ActiveConnection = strCONN_DATA cmdINSERT.CommandText = "[sp_Update_tblECMTimeTrackingMain]" cmdINSERT.CommandType = 4

set param = cmdINSERT.CreateParameter("@.RowID",3,1,4) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.FStartTime",135,1,8) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.FEndTime",135,1,8) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.TransxStatus",129,1,50) cmdINSERT.Parameters.Append param

cmdINSERT.Parameters(0) = CInt(mACTREFNUM) ...when I change this to "568" it actually does the update. Yes there is something in mACTREFNUM.

cmdINSERT.Parameters(1) = meStartTime

cmdINSERT.Parameters(2) = meEndTime

cmdINSERT.Parameters(3) = meStatus

cmdINSERT.Execute lngRECS,,128

Any clues?

Is there any indication as to what, if any, error is happening? What value is stored in mACTREFNUM and can you verify that the result of CInt(mACTREFNUM) is, in fact, an integer value?

Thanks,

Jason

ado 2.8 and sqlxml.... retrieve from field?

hello,
i have a server side xml implementation that works great but i can't get the
data to my client.
i have a stored proc that returns 2 recordsets... both are "selecct...
form... for xml". the data in the field is exactly as i want it (when i run
it in sql query analyzer).
problem #1: i can retrieve the text from a command object if i execute it
into an ado stream.... but i can't get both recordsets. weird, right? cause
you can get multiple recordsets from a command object... but nope. your
command object has to write to a stream, not a recordset.
problem #2: ok, so i use a recordset. great, right? i can execute it, get my
data, move to the next recordset, get my data... but nope. i cannot figure
out a way to get the data out of the field. it's in binary form.
i need to retrieve both text strings... but how?
anyone help? please?
dushan bilbijaHello Dushan,
Its been years since I've worked with this and for a good reason. Using SqlX
ml
with classic ADO is a PITA compared to .NET. If I were you, I'd write a .NET
component that uses the ExecuteXmlReader (or even ExecuteScalar) to get the
strings and pass those back. You should be able to call that assembly (the
output of .NET compliation, given the extension DLL) from your application
as needed.
Its a hack, but it should work :)
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||not a bad idea... but i managed to find the solution. if you carry out a
sequence of select statements, each with for xml, the command object
retrieves them all and concatenated. so it works out well.
dushan
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad742a79c8c7e4e55f74cd40@.news.microsoft.com...
> Hello Dushan,
> Its been years since I've worked with this and for a good reason. Using
> SqlXml with classic ADO is a PITA compared to .NET. If I were you, I'd
> write a .NET component that uses the ExecuteXmlReader (or even
> ExecuteScalar) to get the strings and pass those back. You should be able
> to call that assembly (the output of .NET compliation, given the extension
> DLL) from your application as needed.
> Its a hack, but it should work :)
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

ado 2.8 and sqlxml.... retrieve from field?

hello,
i have a server side xml implementation that works great but i can't get the
data to my client.
i have a stored proc that returns 2 recordsets... both are "selecct...
form... for xml". the data in the field is exactly as i want it (when i run
it in sql query analyzer).
problem #1: i can retrieve the text from a command object if i execute it
into an ado stream.... but i can't get both recordsets. weird, right? cause
you can get multiple recordsets from a command object... but nope. your
command object has to write to a stream, not a recordset.
problem #2: ok, so i use a recordset. great, right? i can execute it, get my
data, move to the next recordset, get my data... but nope. i cannot figure
out a way to get the data out of the field. it's in binary form.
i need to retrieve both text strings... but how?
anyone help? please?
dushan bilbija
Hello Dushan,
Its been years since I've worked with this and for a good reason. Using SqlXml
with classic ADO is a PITA compared to .NET. If I were you, I'd write a .NET
component that uses the ExecuteXmlReader (or even ExecuteScalar) to get the
strings and pass those back. You should be able to call that assembly (the
output of .NET compliation, given the extension DLL) from your application
as needed.
Its a hack, but it should work
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||not a bad idea... but i managed to find the solution. if you carry out a
sequence of select statements, each with for xml, the command object
retrieves them all and concatenated. so it works out well.
dushan
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad742a79c8c7e4e55f74cd40@.news.microsoft.co m...
> Hello Dushan,
> Its been years since I've worked with this and for a good reason. Using
> SqlXml with classic ADO is a PITA compared to .NET. If I were you, I'd
> write a .NET component that uses the ExecuteXmlReader (or even
> ExecuteScalar) to get the strings and pass those back. You should be able
> to call that assembly (the output of .NET compliation, given the extension
> DLL) from your application as needed.
> Its a hack, but it should work
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>