Newbie question on parameters to stored procedure

Hi All,
I had posted this question in the vb.net news group and don't seem to be
getting anywhere. This question may be more apt for this group, I guess. I
have pasted the post below.
****************************************
********************
I am trying to pass parameters to a stored procedure from vb.net code and
fails with the error that the variable is not a parameter to the stored
procedure
Here is the vb.net code
----
--
command = New SqlCommand("sp_updateProducts")
command.Connection = connection
command.CommandType = CommandType.StoredProcedure
command.Transaction = trans
command.Parameters.Add(New SqlParameter("@.pMacId",
SqlDbType.Char))
command.Parameters.Add(New SqlParameter("@.pProdDt",
SqlDbType.DateTime))
command.Parameters.Add(New SqlParameter("@.pProdInfo",
SqlDbType.VarChar))
command.Parameters(0).Direction = ParameterDirection.Input
command.Parameters(1).Direction = ParameterDirection.Input
command.Parameters(2).Direction = ParameterDirection.Input
command.Parameters(0).Value = machineID
command.Parameters(1).Value = updateDate
command.Parameters(2).Value = joinStr
command.ExecuteNonQuery()
----
--
Here is the stored procedure code:
----
--
CREATE PROCEDURE dbo.sp_updateProducts
(
@.pMachineId AS CHAR(6),
@.pProdDt AS DATETIME,
@.pProdinfo VARCHAR(4000)
)
AS
BEGIN
.....
.....
.....
END
GO
----
--
The error message occurs on ExecuteNonQuery() and says that @.pMacId is not a
prameter to the stored procedure sp_updateProducts
I may be missing something very naive! Could anybody suggest the cause of
the error?
Thanks
kd@.pMacId is not a parameter. Ther parameter is called @.pMachineId.
Do NOT use the "sp_" prefix for stored procs (unless you want to create
system procs in Master - something that I wouldn't recommend on a production
system).
"sp_" denotes a system proc and if you create procs with this name outside
Master they may not execute and their performance will suffer from recompile
s.
David Portas
SQL Server MVP
--|||Hi Kd -
The string you specify in the VB.Net call for the name of the parameter
should match the name of the parameter as specified in the stored
procedure.
In the VB.Net code you create a paramter called @.pMacId, but in the
procedure it's named @.pMachineId. Make sure they are given the same name.
BTW - considering changing your procedure name to something like
usp_UpdateProducts. With a prefix of sp_, SQL Server will look first to
the master database for the procedure - slowing your system down a bit.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
kd wrote:
> Hi All,
> I had posted this question in the vb.net news group and don't seem to be
> getting anywhere. This question may be more apt for this group, I guess. I
> have pasted the post below.
> ****************************************
********************
> I am trying to pass parameters to a stored procedure from vb.net code and
> fails with the error that the variable is not a parameter to the stored
> procedure
> Here is the vb.net code
> ----
--
> command = New SqlCommand("sp_updateProducts")
> command.Connection = connection
> command.CommandType = CommandType.StoredProcedure
> command.Transaction = trans
> command.Parameters.Add(New SqlParameter("@.pMacId",
> SqlDbType.Char))
> command.Parameters.Add(New SqlParameter("@.pProdDt",
> SqlDbType.DateTime))
> command.Parameters.Add(New SqlParameter("@.pProdInfo",
> SqlDbType.VarChar))
> command.Parameters(0).Direction = ParameterDirection.Input
> command.Parameters(1).Direction = ParameterDirection.Input
> command.Parameters(2).Direction = ParameterDirection.Input
> command.Parameters(0).Value = machineID
> command.Parameters(1).Value = updateDate
> command.Parameters(2).Value = joinStr
> command.ExecuteNonQuery()
> ----
--
> Here is the stored procedure code:
> ----
--
> CREATE PROCEDURE dbo.sp_updateProducts
> (
> @.pMachineId AS CHAR(6),
> @.pProdDt AS DATETIME,
> @.pProdinfo VARCHAR(4000)
> )
> AS
> BEGIN
> .....
> .....
> .....
> END
> GO
> ----
--
> The error message occurs on ExecuteNonQuery() and says that @.pMacId is not
a
> prameter to the stored procedure sp_updateProducts
> I may be missing something very naive! Could anybody suggest the cause of
> the error?
> Thanks
> kd
>|||kd
I think the problem is you are refering to @.pMacId as a parameter of the SP
but actually a name of parameter is @.pMachineId (see CREATE PROC ...)
Am I right?
"kd" <kd@.discussions.microsoft.com> wrote in message
news:C2C6C755-604B-4FED-B113-13D2F7D345C9@.microsoft.com...
> Hi All,
> I had posted this question in the vb.net news group and don't seem to be
> getting anywhere. This question may be more apt for this group, I guess. I
> have pasted the post below.
> ****************************************
********************
> I am trying to pass parameters to a stored procedure from vb.net code and
> fails with the error that the variable is not a parameter to the stored
> procedure
> Here is the vb.net code
> ----
--
> command = New SqlCommand("sp_updateProducts")
> command.Connection = connection
> command.CommandType = CommandType.StoredProcedure
> command.Transaction = trans
> command.Parameters.Add(New SqlParameter("@.pMacId",
> SqlDbType.Char))
> command.Parameters.Add(New SqlParameter("@.pProdDt",
> SqlDbType.DateTime))
> command.Parameters.Add(New SqlParameter("@.pProdInfo",
> SqlDbType.VarChar))
> command.Parameters(0).Direction = ParameterDirection.Input
> command.Parameters(1).Direction = ParameterDirection.Input
> command.Parameters(2).Direction = ParameterDirection.Input
> command.Parameters(0).Value = machineID
> command.Parameters(1).Value = updateDate
> command.Parameters(2).Value = joinStr
> command.ExecuteNonQuery()
> ----
--
> Here is the stored procedure code:
> ----
--
> CREATE PROCEDURE dbo.sp_updateProducts
> (
> @.pMachineId AS CHAR(6),
> @.pProdDt AS DATETIME,
> @.pProdinfo VARCHAR(4000)
> )
> AS
> BEGIN
> .....
> .....
> .....
> END
> GO
> ----
--
> The error message occurs on ExecuteNonQuery() and says that @.pMacId is not
a
> prameter to the stored procedure sp_updateProducts
> I may be missing something very naive! Could anybody suggest the cause of
> the error?
> Thanks
> kd
>|||Hi,
But, I thought @.pMacId is a value name, which could differ, in the call and
the definition, just like how it is with vb.net procedures and functions!
And thanks for the advice on the usage of "sp_"
kd
"David Portas" wrote:

> @.pMacId is not a parameter. Ther parameter is called @.pMachineId.
> Do NOT use the "sp_" prefix for stored procs (unless you want to create
> system procs in Master - something that I wouldn't recommend on a producti
on
> system).
> "sp_" denotes a system proc and if you create procs with this name outside
> Master they may not execute and their performance will suffer from recompi
les.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi David,
Changing the parameter name to @.pMachineId fixed the error.
Thanks
kd
"David Portas" wrote:

> @.pMacId is not a parameter. Ther parameter is called @.pMachineId.
> Do NOT use the "sp_" prefix for stored procs (unless you want to create
> system procs in Master - something that I wouldn't recommend on a producti
on
> system).
> "sp_" denotes a system proc and if you create procs with this name outside
> Master they may not execute and their performance will suffer from recompi
les.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Joe,
Thanks for the solution
kd
"Joe Webb" wrote:

> Hi Kd -
> The string you specify in the VB.Net call for the name of the parameter
> should match the name of the parameter as specified in the stored
> procedure.
> In the VB.Net code you create a paramter called @.pMacId, but in the
> procedure it's named @.pMachineId. Make sure they are given the same name.
> BTW - considering changing your procedure name to something like
> usp_UpdateProducts. With a prefix of sp_, SQL Server will look first to
> the master database for the procedure - slowing your system down a bit.
> HTH...
> Joe Webb
> SQL Server MVP
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/t...il/-/0972688811
>
> kd wrote:
>|||Hi Uri,
Thanks for the solution
kd
"Uri Dimant" wrote:

> kd
> I think the problem is you are refering to @.pMacId as a parameter of the
SP
> but actually a name of parameter is @.pMachineId (see CREATE PROC ...)
> Am I right?
>
> "kd" <kd@.discussions.microsoft.com> wrote in message
> news:C2C6C755-604B-4FED-B113-13D2F7D345C9@.microsoft.com...
> --
> --
> --
> --
> a
>
>

newbie Question - Grouping charts?

Hi - can anyone help me with the best way to group & repeat charts?
The scenario is that I have 3 different CPU capacity report charts ran on 20
different servers & I want to group the reports together by server so it
will look like:
Server1
CPU Chart1
CPU Chart2
CPUChart3
Server2
CPUChart1
CPUChart2
CPUChart3
......
I can set up grouping by server on a table & produce the CPUReport1 chart
for each server but I cannot add CPUReport2 to the table as it is based on a
different dataset.
I'm sure there must be a technique to do this kinda thing but I haven't been
using reporting services for long & I can't figure it out!
Any ideas much appreciated!
Thanks
Mike KneeDid you tried subreport? that will solve this kind of requirement.
"Michael Knee" wrote:
> Hi - can anyone help me with the best way to group & repeat charts?
> The scenario is that I have 3 different CPU capacity report charts ran on 20
> different servers & I want to group the reports together by server so it
> will look like:
> Server1
> CPU Chart1
> CPU Chart2
> CPUChart3
> Server2
> CPUChart1
> CPUChart2
> CPUChart3
> ......
> I can set up grouping by server on a table & produce the CPUReport1 chart
> for each server but I cannot add CPUReport2 to the table as it is based on a
> different dataset.
> I'm sure there must be a technique to do this kinda thing but I haven't been
> using reporting services for long & I can't figure it out!
> Any ideas much appreciated!
> Thanks
> Mike Knee
>
>|||Cool - I will give that a look, thanks
"Bava Mani" <BavaMani@.discussions.microsoft.com> wrote in message
news:82B6ABAF-AE51-44B8-8BA5-FABDF26DDB48@.microsoft.com...
> Did you tried subreport? that will solve this kind of requirement.
> "Michael Knee" wrote:
>> Hi - can anyone help me with the best way to group & repeat charts?
>> The scenario is that I have 3 different CPU capacity report charts ran on
>> 20
>> different servers & I want to group the reports together by server so it
>> will look like:
>> Server1
>> CPU Chart1
>> CPU Chart2
>> CPUChart3
>> Server2
>> CPUChart1
>> CPUChart2
>> CPUChart3
>> ......
>> I can set up grouping by server on a table & produce the CPUReport1 chart
>> for each server but I cannot add CPUReport2 to the table as it is based
>> on a
>> different dataset.
>> I'm sure there must be a technique to do this kinda thing but I haven't
>> been
>> using reporting services for long & I can't figure it out!
>> Any ideas much appreciated!
>> Thanks
>> Mike Knee
>>

Newbie Question

I hope this is the correct group for this question.I am a
newbie to using SQL Server and need some help. I have a
Access database that I am testing with SQL Sever 7. When
I imported my tables into the SQL Server I just imported
them into the Master Database that is already there. The
performance was very good scrolling through the records
within the database was very fast.
I then created a new database and imported the tables
into the new database and now, when I scroll through the
records within the database it is very slow.
I have noticed that there are a lot of different system
tables in the database, are these file making the
performance faster? I linked the database to SQL Server
using ODBC.. Is there anything that I can do to improve
the performance in the new database?
Thanks in AdvanceUser database objects should never be in Master database. This is for inter
nal SQL Server system only. When you place an object in Master that is also
in one of your other databases, then query the table from the non master da
tabase, the table from mast
er is used over the table from the non master database. This may be causing
some of the slowdown.

Newbie problem using Windows Groups and Schema

I created a Windows Group in Active Directory ("Database1Users"). populated it with users, and planned to allow everyone in it to have access to a sql 2005 database.

I went to the sql server, Security (at the general level), Logins, New login, and created the Login "<domain name>\Database1Users". I assigned "Database1" as Default database, selected the database and assigned it to the above user name (which is actually a group name). I also typed in a default schema of "dbo" and gave the user account the role "db_owner" (just learning....) . Pressing OK gave me this error message:

>>>>

The DEFAULT_SCHEMA clause can not be used with a Windows Group or with principals mapped to certificates or an asymmetric keys."

>>>>

Oh..... how am I supposed to map a Windows group to give the users the access they need?

TIA,

barkingdog

See this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=159533&SiteID=1.

You can map a Windows group, just don't try setting a default schema for it.

Thanks
Laurentiu

sql

Newbie Entity Relationship Advice

Dear All,
Firstly since the first time posting on this group, I apologise if this is
posted on the wrong newsgroup but could someone please help me with the
question below.
I have received a database designed by a colleague who has since left the
organisation and I was wondering if there were any tools or techniques that
could help create an entity relationship diagram of the database. With Acces
s
you had a utility called database documentor, which could help me with my
task. If this is not possible I would like to get a list of all tables and
Primary/Foreign key constraints.
There are over 100 tables and it would take a long time to go through each
one looking at primary keys and foreign keys. Is there anyone out there that
can offer this SQL Server 2000 newbie some helping advice?
p.s. What newsgroup should I have posted a general question on?
Thanks.
Alastair MacFarlaneYes. You can do this in SQL Server 2000. Look in the Database and you will
see a "Diagrams" icon. Use that to create the entity relationship.
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:477DE3D1-0457-4AF3-B067-BA5A880364F0@.microsoft.com...
> Dear All,
> Firstly since the first time posting on this group, I apologise if this is
> posted on the wrong newsgroup but could someone please help me with the
> question below.
> I have received a database designed by a colleague who has since left the
> organisation and I was wondering if there were any tools or techniques
> that
> could help create an entity relationship diagram of the database. With
> Access
> you had a utility called database documentor, which could help me with my
> task. If this is not possible I would like to get a list of all tables
> and
> Primary/Foreign key constraints.
> There are over 100 tables and it would take a long time to go through each
> one looking at primary keys and foreign keys. Is there anyone out there
> that
> can offer this SQL Server 2000 newbie some helping advice?
> p.s. What newsgroup should I have posted a general question on?
> Thanks.
> Alastair MacFarlane|||The Database Diagram tool within Enterprise Manager allows you to create a
visual diagram of the database that can also be used to create, edit or
delete tables. You can launch the wizard by right-cling on the Diagrams
node under the desired database and selecting 'new database diagram'. See
the Books Online for details on usage.
Hope this helps.
Dan Guzman
SQL Server MVP
"Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
in message news:477DE3D1-0457-4AF3-B067-BA5A880364F0@.microsoft.com...
> Dear All,
> Firstly since the first time posting on this group, I apologise if this is
> posted on the wrong newsgroup but could someone please help me with the
> question below.
> I have received a database designed by a colleague who has since left the
> organisation and I was wondering if there were any tools or techniques
> that
> could help create an entity relationship diagram of the database. With
> Access
> you had a utility called database documentor, which could help me with my
> task. If this is not possible I would like to get a list of all tables
> and
> Primary/Foreign key constraints.
> There are over 100 tables and it would take a long time to go through each
> one looking at primary keys and foreign keys. Is there anyone out there
> that
> can offer this SQL Server 2000 newbie some helping advice?
> p.s. What newsgroup should I have posted a general question on?
> Thanks.
> Alastair MacFarlane|||With SQL Server, create a DB diagram via Enterprise manager by selecting all
the tables. This will get u the ERD along with all existing relationships...
R
"Alastair MacFarlane" wrote:

> Dear All,
> Firstly since the first time posting on this group, I apologise if this is
> posted on the wrong newsgroup but could someone please help me with the
> question below.
> I have received a database designed by a colleague who has since left the
> organisation and I was wondering if there were any tools or techniques tha
t
> could help create an entity relationship diagram of the database. With Acc
ess
> you had a utility called database documentor, which could help me with my
> task. If this is not possible I would like to get a list of all tables an
d
> Primary/Foreign key constraints.
> There are over 100 tables and it would take a long time to go through each
> one looking at primary keys and foreign keys. Is there anyone out there th
at
> can offer this SQL Server 2000 newbie some helping advice?
> p.s. What newsgroup should I have posted a general question on?
> Thanks.
> Alastair MacFarlane|||Thanks Yosh, Dan and Rakesh. I didn't realise that the wizard would create
the entity diagram for you. This is better that Access!
Alastair
"Yosh" wrote:

> Yes. You can do this in SQL Server 2000. Look in the Database and you will
> see a "Diagrams" icon. Use that to create the entity relationship.
>
> "Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
> in message news:477DE3D1-0457-4AF3-B067-BA5A880364F0@.microsoft.com...
>
>|||The Diagram will automatically draw connecting lines only if referential
integrity constraints were implemented on the tables.
"Yosh" <Yosh@.nospam.com> wrote in message
news:u3iMO5irFHA.3788@.TK2MSFTNGP12.phx.gbl...
> Yes. You can do this in SQL Server 2000. Look in the Database and you will
> see a "Diagrams" icon. Use that to create the entity relationship.
>
> "Alastair MacFarlane" <AlastairMacFarlane@.discussions.microsoft.com> wrote
> in message news:477DE3D1-0457-4AF3-B067-BA5A880364F0@.microsoft.com...
>