Newbie question on stored procedures and linked servers

I have a "main" SQL Server 2005 server and several "farm" SQL Servers using
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the main
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
John
In general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John
|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting to
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example above
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John
|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John
sql

Newbie question on stored procedures and linked servers

I have a "main" SQL Server 2005 server and several "farm" SQL Servers using
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the mai
n
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
JohnIn general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting t
o
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example abov
e
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John

Newbie question on stored procedures and linked servers

I have a "main" SQL Server 2005 server and several "farm" SQL Servers using
2005.
I'd like to be able to create stored procedures on the main server and run
them from the farm server against the farm server. For example, one stored
procedure would create a database and tables. I'd like it to live on the main
server and create the database and tables on the farm server.
Is this possible? If so, are there any examples available?
JohnIn general, stored procedures executing on remote servers are restricted
from changed the schema on the remote server. It's a good security
consideration.
Now there are many 'work-a-rounds'. One that I employ is having the 'main'
server create a SQL script file, dropping that in a location available to
the remote server, and then executing a job that looks for file(s) in that
location (perhaps filtered by naming conventions, etc.), and then executes
that script file.
Perhaps if you provided a bit more details about what you are attempting to
accomplish, someone here will have a better solution.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:009DE414-CED1-4882-B8F6-FA314C8643BE@.microsoft.com...
> I have a "main" SQL Server 2005 server and several "farm" SQL Servers
> using
> 2005.
> I'd like to be able to create stored procedures on the main server and run
> them from the farm server against the farm server. For example, one stored
> procedure would create a database and tables. I'd like it to live on the
> main
> server and create the database and tables on the farm server.
> Is this possible? If so, are there any examples available?
> John|||"Arnie Rowland" wrote:
> Perhaps if you provided a bit more details about what you are attempting to
> accomplish, someone here will have a better solution.
>
The main server is a data warehouse storing metadata and fact data for
approximately 1000 cubes that have to be produced monthly. The farm servers
are the servers where the cubes will be produced. (We do this now with
Oracle and are moving to SS05.)
I want to avoid having multiple farm servers where if a stored procedure is
changed it has to be propagated to mulitple servers. So in the example above
the stored procedure would accept some variables and create the database and
tables on the farm server that are used to create one of the thousand cubes.
So in essence I want a local server to call a remote server stored procedure
that will create a database and tables on the local server.
John|||You may wish to look into aspects of schema replication.
With SQL Server 2005, you can replicate Stored Procedures. So you would only
have to manage the main server, and yes, propagation is involved, but it
becomes a background process and part of the database 'system' itself. Not
something that would have to be managed manually.
Calling those replicated 'remote' procedures is, perforce, a normal
operation.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JColaizzi" <JColaizzi@.discussions.microsoft.com> wrote in message
news:3ABDDDF1-C638-4FAA-BBE1-A10581257EED@.microsoft.com...
> "Arnie Rowland" wrote:
>> Perhaps if you provided a bit more details about what you are attempting
>> to
>> accomplish, someone here will have a better solution.
> The main server is a data warehouse storing metadata and fact data for
> approximately 1000 cubes that have to be produced monthly. The farm
> servers
> are the servers where the cubes will be produced. (We do this now with
> Oracle and are moving to SS05.)
> I want to avoid having multiple farm servers where if a stored procedure
> is
> changed it has to be propagated to mulitple servers. So in the example
> above
> the stored procedure would accept some variables and create the database
> and
> tables on the farm server that are used to create one of the thousand
> cubes.
> So in essence I want a local server to call a remote server stored
> procedure
> that will create a database and tables on the local server.
> John

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 here - stored procedures

Hi I just installed sql server 2005 for the first time today. Anyways I have a bunch of stored procs from an application i developed on the SQL 2000 server. Anyways I am having a problem with one of my stored procedures.

CREATE PROCEDURE ProvinceRetrieveAdHoc

(@.whereClause VARCHAR(256))

AS

EXEC("SELECT * FROM tblProvince WHERE " + @.whereClause)

RETURN

GO

However this isn't working, I am getting this error:

Incorrect syntax near 'SELECT * FROM tblProvince WHERE '.

Now I am guessing the EXEC statement must have changed, as every stored proc that uses the exec command errors out with this same error. All my other procedures run just fine. Can anyone please tell me how to fix it?

THanks

oh wow nm, i figured it out.. just replaced double quotes with single.sql

Newbie question about sprocs

When I run my stored proceedure in query analyzer and copy the text to the pane in the report designer, all is well, but if I run the same stored proceedure as a stored procedure in the report designer it doesn't return all the fields that it does as text. What's the difference? What do I do to fix this?Two things come to mind. Ownership and location of the stored procedure.
OWNERSHIP:
Objects like tables, views, and stored procedures can be owned by different
logins.
Lets say you have two users in your database
user1
user2
They can both create a table called FOO
Each table can have different columns.
Each user can also create a stored procedure named procFOO
These stored procedures can return different columns.
LOCATION OF THE STORED PROCEDURE:
It is possible to create stored procedures in multiple databases. For
example, procFOO within northwind could select from the Products table and
procFOO within pubs could select from the authors table. Are you calling
the stored procedure from the database that you created it in (and are
running it from Query Analyzer)?
--
Keith
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:8F8426EB-7C6C-49C5-9C8F-825EB089A880@.microsoft.com...
> When I run my stored proceedure in query analyzer and copy the text to the
pane in the report designer, all is well, but if I run the same stored
proceedure as a stored procedure in the report designer it doesn't return
all the fields that it does as text. What's the difference? What do I do to
fix this?|||So I hit the nail on the head; was it an ownership or a location issue?
--
Keith
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:8EC3378E-037A-4C6A-93BE-7367603FDBE3@.microsoft.com...
> Thanks you were a great help. All is well!
> "Keith Kratochvil" wrote:
> > Two things come to mind. Ownership and location of the stored
procedure.
> >
> > OWNERSHIP:
> > Objects like tables, views, and stored procedures can be owned by
different
> > logins.
> > Lets say you have two users in your database
> > user1
> > user2
> > They can both create a table called FOO
> > Each table can have different columns.
> > Each user can also create a stored procedure named procFOO
> > These stored procedures can return different columns.
> >
> > LOCATION OF THE STORED PROCEDURE:
> > It is possible to create stored procedures in multiple databases. For
> > example, procFOO within northwind could select from the Products table
and
> > procFOO within pubs could select from the authors table. Are you
calling
> > the stored procedure from the database that you created it in (and are
> > running it from Query Analyzer)?
> >
> > --
> > Keith
> >
> >
> > "Arly" <Arly@.discussions.microsoft.com> wrote in message
> > news:8F8426EB-7C6C-49C5-9C8F-825EB089A880@.microsoft.com...
> > > When I run my stored proceedure in query analyzer and copy the text to
the
> > pane in the report designer, all is well, but if I run the same stored
> > proceedure as a stored procedure in the report designer it doesn't
return
> > all the fields that it does as text. What's the difference? What do I do
to
> > fix this?
> >
> >

newbie question

I can connect to database stored on the sql server. But I cannot map drive
through windows xp. Any suggestions.
Lindac wrote:
> I can connect to database stored on the sql server. But I cannot map
> drive through windows xp. Any suggestions.
Are you trying to map a drive on the SQL Server box? If so, you need to
share the drive from the server to make it available to users on the
network.
SQL Server does not provide access to its data using file access (like
you used to do with dBase). It uses a network protocol like TCP/IP or
Named Pipes. The server listens on a specific TCP/IP port or a Named
Pipe "Pipe" and routes calls to the SQL Server service.
David G.

newbie question

I can connect to database stored on the sql server. But I cannot map drive
through windows xp. Any suggestions.Lindac wrote:
> I can connect to database stored on the sql server. But I cannot map
> drive through windows xp. Any suggestions.
Are you trying to map a drive on the SQL Server box? If so, you need to
share the drive from the server to make it available to users on the
network.
SQL Server does not provide access to its data using file access (like
you used to do with dBase). It uses a network protocol like TCP/IP or
Named Pipes. The server listens on a specific TCP/IP port or a Named
Pipe "Pipe" and routes calls to the SQL Server service.
David G.

newbie question

I can connect to database stored on the sql server. But I cannot map drive
through windows xp. Any suggestions.Lindac wrote:
> I can connect to database stored on the sql server. But I cannot map
> drive through windows xp. Any suggestions.
Are you trying to map a drive on the SQL Server box? If so, you need to
share the drive from the server to make it available to users on the
network.
SQL Server does not provide access to its data using file access (like
you used to do with dBase). It uses a network protocol like TCP/IP or
Named Pipes. The server listens on a specific TCP/IP port or a Named
Pipe "Pipe" and routes calls to the SQL Server service.
David G.

newbie query help

hello all,i am developing an application which displays available pcs in our lab.all activites are stored in a sql server table.Sql server 2000

Table structure is field name datatype null values
logon varchar(6) canbe null (values = login or logoff)
username varchar(20) canbe null (eg user01,B023)
computername varchar(20) canbenull (if its in lab1 name will be lab1-01,lab1-02..,if its in lab2 name will be lab2-01,lab2-02.)
logontime datetime(8) canbenull (26/11/2002 11:17:52)

table sample is

Logon computername username logontime



Login lab1-01 x 2003-07-09 11:17:00.000

Login lab1-03 y 2003-07-09 11:19:00.000

Logoff lab1-01 x 2003-07-09 11:20:00.000

with these details is it possible to see currently how many machines r occupied in each lab.if so how?i am planning to write an asp page which will display currently available machines,so tat user can go to particular lab .i am a sql newbie .if any 1 can guide me tat wuld be helpful for me .thx in advance

-regards
aravindas far as i can see (im finding it hard to understand exactly what fields will be in your database) all you should need to extract the info is something along the lines of:

select from tableA computername
where (logon = ' ')

presuming the table is called 'tableA'

you might be better off having another field of type boolean which simply shows whether anyone is logged into the machine, maybe 0 for vacant or 1 for in-use.

hope this helps|||assume that a computer is "occupied" when the latest logon for it is "Login"

this query will list the individual machines:
select computername
, logontime as LastLoginTime
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
order
by computername
this query will count them by lab and list them in order by the fewest occupied to the most:
select left(computername,4) as Lab
, count(*) as OccupiedCount
from yourtable as TT
where logontime
= ( select max(logontime)
from yourtable
where logontime = TT.logontime )
and logon = 'Login'
group
by left(computername,4)
order
by 2
rudy|||How can you tell if they are occupied if all you have is the last logon time? You need a field that indicates their logoff time as well. With that, you can not only tell which machines are occupied (at least one associated logon has a null logoff time), but also other valuable information such as usage patterns.

blindman|||blindman, the logoffs are there, as separate rows

that's why my query checks for when the latest logon for the machine is "Login"

:)|||hello all thx for ur replies ,i didt read ur query.i created a new table with computername,status (which can be login or logoff) ,when old table gets inserted it triggers an event which sets the status of particular machine in new table .now i can query the new table to see the availability...is it a good practice? its my first sql prg .gimme some inputs

-regards
aravind|||Ahh...I see your logoff rows now. I've worked tables that way before, but I prefer storing logoffs as a separate field rather than a separate record. That way each row represents a single session and it makes calculations easier. By using a separate field you don't need the Max subquery, and finding the total amount of time for a particular user across sessions is as simple as sum(LogoffTime - LogonTime).

blindman

Newbie on Triggers

System Sql server 2000

Hi all

I've dipped my toe in stored procedures, I'm now having a look at triggers

If I had 2 tables and I wanted to update table 2 with some data from table
1, every time table 1 has a new entry made in it (e.g. for an audit trail) I
thought I would be able to use a trigger on table 1.

for instance

on insert of new value in table 1 execute a trigger

The trigger gets the value of the newly created primary key in table 1 (the
primary key is generated by Autonumber) and inserts a copy of the primary
key into table 2

I thought the following would work

ALTER TRIGGER update_table2
ON dbo.table1
FOR INSERT AS
begin
declare @.new_key as bigint
set @.new_key = dbo.table1(PK_test_primary_key)

INSERT INTO dbo.table2
(FK_from_table1)
VALUES (@.new_key)
end

unfortunatly I cant read the value of the newly entered primary key from
table 1. I get a message saying PK_test_primary_key doesn't exist in
dbo.table1

does anyone know if what I am trying to achieve is possible, and maybe a
clue as a good way to go about doing it?

many thanks

AndyHi just a bit more...

after some more googling I have found @.@.identity and it seems to give the
identity of the last added record in the table calling the trigger

is this correct, and is it safe to do this i.e.

ALTER TRIGGER update_table2
ON dbo.table1
FOR INSERT AS
begin

INSERT INTO dbo.table2
(FK_from_table1)
VALUES (@.@.identity)
end

I've also been playing with objConn.BeginTrans in my vb6 code and I write
lots of records to table 1 before confirming the transaction. I believe that
the autonumbers are only generated on completion of the transaction, so how
many triggers will be executed (one per transaction or 1 per new record) and
at what time (as the records are written or on compltion of the
transaction)... Will it reliably update table 2 with the correct info from
table 1 if say dozens of additions are made to table 1 from different
sources at the same time...?

I think I worry too much and it seems to work when I test it, but I like to
know what happens when 8-)

thanks again

Andy

"aaj" <a.b@.c.com> wrote in message
news:4028f77b$0$29799$afc38c87@.news.easynet.co.uk. ..
> System Sql server 2000
> Hi all
> I've dipped my toe in stored procedures, I'm now having a look at triggers
> If I had 2 tables and I wanted to update table 2 with some data from table
> 1, every time table 1 has a new entry made in it (e.g. for an audit trail)
I
> thought I would be able to use a trigger on table 1.
> for instance
> on insert of new value in table 1 execute a trigger
> The trigger gets the value of the newly created primary key in table 1
(the
> primary key is generated by Autonumber) and inserts a copy of the primary
> key into table 2
> I thought the following would work
> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> declare @.new_key as bigint
> set @.new_key = dbo.table1(PK_test_primary_key)
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.new_key)
> end
> unfortunatly I cant read the value of the newly entered primary key from
> table 1. I get a message saying PK_test_primary_key doesn't exist in
> dbo.table1
> does anyone know if what I am trying to achieve is possible, and maybe a
> clue as a good way to go about doing it?
> many thanks
> Andy|||aaj (a.b@.c.com) writes:
> after some more googling I have found @.@.identity and it seems to give the
> identity of the last added record in the table calling the trigger
> is this correct, and is it safe to do this i.e.
> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.@.identity)
> end

No, this is not the way to go. In a trigger you have access to two
virtual tables, inserted and deleted. inserted holds the rows that
were inserted, and in case of an UPDATE statment, the values after
the update. And deleted holds the values that were removed by a DELETE
or an UPDATE statement.

Beware that trigger fires once by statement, so many rows can be affected
at once.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> declare @.new_key as bigint
> set @.new_key = dbo.table1(PK_test_primary_key)
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.new_key)
> end
Triggers utilize the virtual "inserted" and "deleted" tables. Check
out this article:http://www.sqlteam.com/item.asp?ItemID=3850.|||You should also not that the @.@.identity is not just the last identity
created for that table, it is the last identity created. That makes the
value of @.@.identity sort of unpredictable. The other guys are right that in
the trigger you should use INSERTED and DELETED tables, but if you need to
access the latest identity created within a given stored procedure you can
use SCOPE_IDENTITY().

"aaj" <a.b@.c.com> wrote in message
news:4028fa81$0$29827$afc38c87@.news.easynet.co.uk. ..
> Hi just a bit more...
> after some more googling I have found @.@.identity and it seems to give the
> identity of the last added record in the table calling the trigger
> is this correct, and is it safe to do this i.e.
> ALTER TRIGGER update_table2
> ON dbo.table1
> FOR INSERT AS
> begin
> INSERT INTO dbo.table2
> (FK_from_table1)
> VALUES (@.@.identity)
> end
>
> I've also been playing with objConn.BeginTrans in my vb6 code and I write
> lots of records to table 1 before confirming the transaction. I believe
that
> the autonumbers are only generated on completion of the transaction, so
how
> many triggers will be executed (one per transaction or 1 per new record)
and
> at what time (as the records are written or on compltion of the
> transaction)... Will it reliably update table 2 with the correct info
from
> table 1 if say dozens of additions are made to table 1 from different
> sources at the same time...?
> I think I worry too much and it seems to work when I test it, but I like
to
> know what happens when 8-)
> thanks again
> Andy
>
> "aaj" <a.b@.c.com> wrote in message
> news:4028f77b$0$29799$afc38c87@.news.easynet.co.uk. ..
> > System Sql server 2000
> > Hi all
> > I've dipped my toe in stored procedures, I'm now having a look at
triggers
> > If I had 2 tables and I wanted to update table 2 with some data from
table
> > 1, every time table 1 has a new entry made in it (e.g. for an audit
trail)
> I
> > thought I would be able to use a trigger on table 1.
> > for instance
> > on insert of new value in table 1 execute a trigger
> > The trigger gets the value of the newly created primary key in table 1
> (the
> > primary key is generated by Autonumber) and inserts a copy of the
primary
> > key into table 2
> > I thought the following would work
> > ALTER TRIGGER update_table2
> > ON dbo.table1
> > FOR INSERT AS
> > begin
> > declare @.new_key as bigint
> > set @.new_key = dbo.table1(PK_test_primary_key)
> > INSERT INTO dbo.table2
> > (FK_from_table1)
> > VALUES (@.new_key)
> > end
> > unfortunatly I cant read the value of the newly entered primary key from
> > table 1. I get a message saying PK_test_primary_key doesn't exist in
> > dbo.table1
> > does anyone know if what I am trying to achieve is possible, and maybe a
> > clue as a good way to go about doing it?
> > many thanks
> > Andy|||Jason Sauer (jason.sauer@.fuse.net) writes:
> You should also not that the @.@.identity is not just the last identity
> created for that table, it is the last identity created. That makes the
> value of @.@.identity sort of unpredictable. The other guys are right that
> in the trigger you should use INSERTED and DELETED tables, but if you
> need to access the latest identity created within a given stored
> procedure you can use SCOPE_IDENTITY().

In this particular case @.@.identity is the only choice. scope_identity()
will return NULL, because there have been no inserts into a table
with an identity column in the current scope, that is the trigger.

The only time @.@.identity will not return the right value, is when there
are more than one trigger on the table, and the other trigger executes
first and that trigger too inserts into a table with a identity column.
Maybe not the most likely scenario.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

newbie needs help

Hello all, I'm learning SQL and need help with a query. I have a table that
stores employee ID and values per a given year. EmplID is stored in field 1
,
Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number o
f
Yr fields. Not all of the Yr values exist for all Yr fields. I need to be
able to to pull the most recent value for a given row/record. So, for
instance, if EmplID ABCD stores the following:
EmplID Yr1 Yr2 Yr3 Yr4
ABCD 41 42 43 0
I need to pull 41 if Yr1 is the most recent year.
If Emplid EFGH stores the following:
EmplID Yr1 Yr2 Yr3 Yr4
EFGH 0 42 0 0
I need to pull 42, the Yr2 value, because no value exists for Yr1.
I am using SQL Server on a WinXP box. Any help would be greatly appreciated
.First of all, this is a poor design for a table. What are you doing,
continually adding new fields (we call them columns, or attributes) each
year? Better would be to have a column named "Year".
EmplID Year Value
ABCD 1 41
ABCD 2
You should, when possible, learn a bit about data modeling and
normalization... But
"kiloez" wrote:
> Hello all, I'm learning SQL and need help with a query. I have a table th
at
> stores employee ID and values per a given year. EmplID is stored in field
1,
> Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number
of
> Yr fields. Not all of the Yr values exist for all Yr fields. I need to b
e
> able to to pull the most recent value for a given row/record. So, for
> instance, if EmplID ABCD stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> ABCD 41 42 43 0
> I need to pull 41 if Yr1 is the most recent year.
> If Emplid EFGH stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> EFGH 0 42 0 0
> I need to pull 42, the Yr2 value, because no value exists for Yr1.
> I am using SQL Server on a WinXP box. Any help would be greatly appreciated.[/col
or]|||Sorry submitted that before done...
First of all, this is a poor design for a table. What are you doing,
continually adding new fields (we call them columns, or attributes) each
year? Better would be to have a column named "Year".
EmplID Year Value
ABCD Yr1 41
ABCD Yr2 42
ABCD Yr3 43
ABCD Yr4 44
etc. .............
Second, what is Yr1, Yr2, Yr3, Yr4, mean ? Are these the actual column
names in your table? Do they map to actual years spmewhere else in your data
model?
When possible, you should learn a bit about data modeling and
normalization...
Given your problem, a solution based on the columns that exist in the table
right now would break when you add a new column for Yr5, unless the solution
was constructed to treat the "YrN" columns dynamically. That type of
solution would have to read system tables to determine which YR columns
existed, and then write dynamic sql to execute against the table.. A complex
but doable task. It would be much simpler (and therefore quicker more cost
effective) to restructure the data correctly.
"kiloez" wrote:
> Hello all, I'm learning SQL and need help with a query. I have a table th
at
> stores employee ID and values per a given year. EmplID is stored in field
1,
> Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number
of
> Yr fields. Not all of the Yr values exist for all Yr fields. I need to b
e
> able to to pull the most recent value for a given row/record. So, for
> instance, if EmplID ABCD stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> ABCD 41 42 43 0
> I need to pull 41 if Yr1 is the most recent year.
> If Emplid EFGH stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> EFGH 0 42 0 0
> I need to pull 42, the Yr2 value, because no value exists for Yr1.
> I am using SQL Server on a WinXP box. Any help would be greatly appreciated.[/col
or]|||First off my dear friend, you might want to re-evaluate the database design.
You have just violated the second normal form (or is it the third? I get
) Anyways, the best solution would be to move your data to another
table and drop this table here.
I would suggest the following design for your table
CREATE TABLE Emp_Details (
EmpID int NOT NULL,
Year char(4) NOT NULL,
Value int NOT NULL DEFAULT 0
)
Then, populate it as follows:
INSERT INTO Emp_Details
SELECT EmplID, 'Yr1', Yr1
FROM Emp_Table
INSERT INTO Emp_Details
SELECT EmplID, 'Yr2', Yr2
FROM Emp_Table
INSERT INTO Emp_Details
SELECT EmplID, 'Yr3', Yr3
FROM Emp_Table
INSERT INTO Emp_Details
SELECT EmplID, 'Yr4', Yr4
FROM Emp_Table
and so on...
now you have a sensible looking table. you may want to drop your old table
and rename this table. Let everyone know that you have just got some sensibl
e
table design done and that the applications need to change the way they
access data.
Once this is done, you can run the following query:
SELECT Emp_ID, MAX(Year), Value
FROM Emp_Details
WHERE (Value <> 0)
GROUP BY Emp_ID, Value
--that said, if you want a quick fix or boss is not willing to let go of
his ideas of 3NF, you can run this query:
SELECT EmplID,
CASE
WHEN (Yr1 <> 0) THEN Yr1
ELSE
CASE
WHEN (Yr2 <> 0) THEN Yr2
ELSE
CASE
WHEN (Yr3 <> 0) THEN Yr3
ELSE Yr4
END CASE
END CASE
END CASE
FROM Empl_Details
Hope that helps,
Aneesh.
"kiloez" wrote:
> Hello all, I'm learning SQL and need help with a query. I have a table th
at
> stores employee ID and values per a given year. EmplID is stored in field
1,
> Yr1 val in field 2, Yr2 val in field 3... and so on up to any given number
of
> Yr fields. Not all of the Yr values exist for all Yr fields. I need to b
e
> able to to pull the most recent value for a given row/record. So, for
> instance, if EmplID ABCD stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> ABCD 41 42 43 0
> I need to pull 41 if Yr1 is the most recent year.
> If Emplid EFGH stores the following:
> EmplID Yr1 Yr2 Yr3 Yr4
> EFGH 0 42 0 0
> I need to pull 42, the Yr2 value, because no value exists for Yr1.
> I am using SQL Server on a WinXP box. Any help would be greatly appreciated.[/col
or]|||Thanks for the help guys. I had nothing to do with the design and
development of the DB. I was only asked to query some of the tables.
Aneesh, the quick fix was perfect for what I was looking to accomplish.
Worked out just fine.
Many thanks,
kiloez
"Aneesh Aravind" wrote:
> First off my dear friend, you might want to re-evaluate the database desig
n.
> You have just violated the second normal form (or is it the third? I get
> ) Anyways, the best solution would be to move your data to another
> table and drop this table here.
> I would suggest the following design for your table
> CREATE TABLE Emp_Details (
> EmpID int NOT NULL,
> Year char(4) NOT NULL,
> Value int NOT NULL DEFAULT 0
> )
> Then, populate it as follows:
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr1', Yr1
> FROM Emp_Table
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr2', Yr2
> FROM Emp_Table
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr3', Yr3
> FROM Emp_Table
> INSERT INTO Emp_Details
> SELECT EmplID, 'Yr4', Yr4
> FROM Emp_Table
> and so on...
> now you have a sensible looking table. you may want to drop your old table
> and rename this table. Let everyone know that you have just got some sensi
ble
> table design done and that the applications need to change the way they
> access data.
> Once this is done, you can run the following query:
> SELECT Emp_ID, MAX(Year), Value
> FROM Emp_Details
> WHERE (Value <> 0)
> GROUP BY Emp_ID, Value
> --that said, if you want a quick fix or boss is not willing to let go of
> his ideas of 3NF, you can run this query:
> SELECT EmplID,
> CASE
> WHEN (Yr1 <> 0) THEN Yr1
> ELSE
> CASE
> WHEN (Yr2 <> 0) THEN Yr2
> ELSE
> CASE
> WHEN (Yr3 <> 0) THEN Yr3
> ELSE Yr4
> END CASE
> END CASE
> END CASE
> FROM Empl_Details
> Hope that helps,
> Aneesh.
> "kiloez" wrote:
>

Newbie help with dynamic SQL

I am creating a stored procedure that will perform a search against a
table. I am passing search parameters to the SP. If the user did not
select a value on the front end, then I am passing NULL into the SP for
that field. So my question is, what is the best practice to only search
on a field if a value is passed for the given field?

This is what I was thinking, but obviously this doesn't work:

@.vchrFieldOne VARCHAR (200) = NULL

SELECT ...
FROM ...
WHERE
0=0

AND
CASE @.vchrFieldOne
WHEN NULL THEN (0 = 0)
ELSE (vchrFieldOne = @.vchrFieldOne)
ENDMine would look something like this:

CREATE PROC pub_info2 @.vchrFieldOne varchar(200) = NULL
AS
If (@.vchrFieldOne is null)
BEGIN
-- select statement where value is not given
END
ELSE
BEGIN
-- select statement where value is given
END

Hope it helps,
Tony Sebion

"Erich93063" <erich93063@.gmail.com> wrote in message
news:1123175211.631764.144360@.z14g2000cwz.googlegr oups.com:

> I am creating a stored procedure that will perform a search against a
> table. I am passing search parameters to the SP. If the user did not
> select a value on the front end, then I am passing NULL into the SP for
> that field. So my question is, what is the best practice to only search
> on a field if a value is passed for the given field?
> This is what I was thinking, but obviously this doesn't work:
> @.vchrFieldOne VARCHAR (200) = NULL
> SELECT ...
> FROM ...
> WHERE
> 0=0
> AND
> CASE @.vchrFieldOne
> WHEN NULL THEN (0 = 0)
> ELSE (vchrFieldOne = @.vchrFieldOne)
> END|||Erich93063 (erich93063@.gmail.com) writes:
> I am creating a stored procedure that will perform a search against a
> table. I am passing search parameters to the SP. If the user did not
> select a value on the front end, then I am passing NULL into the SP for
> that field. So my question is, what is the best practice to only search
> on a field if a value is passed for the given field?
> This is what I was thinking, but obviously this doesn't work:
> @.vchrFieldOne VARCHAR (200) = NULL
> SELECT ...
> FROM ...
> WHERE
> 0=0
> AND
> CASE @.vchrFieldOne
> WHEN NULL THEN (0 = 0)
> ELSE (vchrFieldOne = @.vchrFieldOne)
> END

It doesn't work for several reasons.

1) WHEN NULL - is the same as "WHEN @.charFieldOne = NULL", but in SQL
NULL is never equal to anything, not even another NULL. NULL is a
unknown value, and any comparison with NULL yields the value UNKNOWN.
Correct is WHEN @.vcharFieldOne IS NULL.
2) The return value of a CASE expresssion is always an SQL Server data
type, and there is no boolean data type in T-SQL. Thus you cannot
have "THEN (0 = 9".

The normal way to write this is

AND (vchrFieldOne = @.vchrFieldOne OR @.vchrFieldOne IS NULL)

However, while this works as far as giving the correct result, the
performance can be unbearable. I have an article on my web site that
discusses a number of alternatives for dynamic searches,
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for the reply. I tried it (At least what I think you meant) but
I get an error (Incorrect syntax near the keyword 'AND' and Incorrect
syntax near the keyword 'END'. Here is what I have:

SELECT ...
FROM...
WHERE 0=0

IF (@.vchrFieldOne IS NULL)

BEGIN
AND 0=0
END
ELSE
BEGIN
AND vchrFieldOne = @.vchrFieldOne
END|||Nice article. Thanks for sharing it.|||YES, I read your article and used your example for dynamic SQL and it
works PERFECTLY. THANKS!!!!!!!!!!!!!

Newbie Help

Looking to create a flat file from a stored proc that is comma delim.. can
this be done? I know I can use DTS but the table is a temp table and that
wont work.
Thanks for your help in advance.
--
Austin Henderson <><
Network AdministratorYou can shell out to bcp via xp_cmdshell but will have to use a global temp
table (##) as bcp will create it's own connection and your local temp table
would not be visible to it.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Austin Henderson" <kahenderson@.firstfleetinc.NOSPAM.com> wrote in message
news:uQpnrkbcDHA.384@.TK2MSFTNGP12.phx.gbl...
Looking to create a flat file from a stored proc that is comma delim.. can
this be done? I know I can use DTS but the table is a temp table and that
wont work.
Thanks for your help in advance.
--
Austin Henderson <><
Network Administrator

newbie ? about stored procedures

I currently have several sprocs that require a variable which determines
which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
returns fields 4, 5, 6. When I run the sprocs the appropriate fields show up
in the results window but when I refresh the fields, reguardless of what
variable I used it displays X's fields in the fields window. Can I not create
two datasets running the same sproc using different variables. If not is this
so this something that I'll be able to do in the near future? Any help would
be most appreciated.Hey there,
As far as I know Reporting Services scans the Stored Procedure, caches the
field names and uses those.
Why don't you have your stored procedure return generic colum names such as
"Col1", "Col2", "Col3" and then apply some conditional formatting on those
column headers when they're placed on the report. Something like this:
txtColumn1.Value = IIF(Parameters!MyCondition.Value = "X", "Zip Code",
"Postal Code")
I'm pretty sure that this is by design since Reporting Services needs to
know the column names.
Hopefully I'm on the same page as you...
Ben
"Arly" <Arly@.discussions.microsoft.com> wrote in message
news:045E8DA3-0DA9-4EAE-9A7E-91343815D69D@.microsoft.com...
> I currently have several sprocs that require a variable which determines
> which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
> returns fields 4, 5, 6. When I run the sprocs the appropriate fields show
up
> in the results window but when I refresh the fields, reguardless of what
> variable I used it displays X's fields in the fields window. Can I not
create
> two datasets running the same sproc using different variables. If not is
this
> so this something that I'll be able to do in the near future? Any help
would
> be most appreciated.|||Thanks, Ben but that would mean changing hundreds of sprocs that are being
used by our VB programmers as well. Any other ideas?
"Benjamin Pierce" wrote:
> Hey there,
> As far as I know Reporting Services scans the Stored Procedure, caches the
> field names and uses those.
> Why don't you have your stored procedure return generic colum names such as
> "Col1", "Col2", "Col3" and then apply some conditional formatting on those
> column headers when they're placed on the report. Something like this:
> txtColumn1.Value = IIF(Parameters!MyCondition.Value = "X", "Zip Code",
> "Postal Code")
> I'm pretty sure that this is by design since Reporting Services needs to
> know the column names.
> Hopefully I'm on the same page as you...
>
> Ben
>
> "Arly" <Arly@.discussions.microsoft.com> wrote in message
> news:045E8DA3-0DA9-4EAE-9A7E-91343815D69D@.microsoft.com...
> > I currently have several sprocs that require a variable which determines
> > which set of fields get returned. For ex: X returns fields 1, 2, 3 and Y
> > returns fields 4, 5, 6. When I run the sprocs the appropriate fields show
> up
> > in the results window but when I refresh the fields, reguardless of what
> > variable I used it displays X's fields in the fields window. Can I not
> create
> > two datasets running the same sproc using different variables. If not is
> this
> > so this something that I'll be able to do in the near future? Any help
> would
> > be most appreciated.
>
>

newbie - Stored procedure

I have simple question - could be a bit stupid
I created a form in which several fields are obligatory, some are free to
fill in
now what is the best practice to follow
Should i create several SP's for every possible combination ?
Should i Create one SP where variables are possibly empty (if that is the
best thing to do, what's the right syntax ?)
Or should I create one SP with the obligatory values, and afterwards search
through the resultset?
thanx in advance...One proc should do it. You can have parameters with default values, if you
so choose:
create proc MyProc
(
@.id int
, @.x char (5) = 'ALFKI'
)
as
...
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Boonaap" <Boonaap@.discussions.microsoft.com> wrote in message
news:415B2831-6626-457A-98D4-42ED64F2C7F7@.microsoft.com...
I have simple question - could be a bit stupid
I created a form in which several fields are obligatory, some are free to
fill in
now what is the best practice to follow
Should i create several SP's for every possible combination ?
Should i Create one SP where variables are possibly empty (if that is the
best thing to do, what's the right syntax ?)
Or should I create one SP with the obligatory values, and afterwards search
through the resultset?
thanx in advance...

Newbie - Can I have a Timer for a SP?

I'd like to have a stored procedure run every n seconds. Not sure how to
accomplish. Any ideas are appreciated.
Regards,
MikeYou can create a job to execute the stored procedure. To create job open
Enterprise Manager. Expand your Servername. Then click on "Management" then
click "JOB". Right click and click "New Job" and then you can set the SQL
statement to execute that stored procedure and you can set the schedule (the
minimum is every 1 minute).
"Mike Hildner" <mhildner@.afweb.com> wrote in message
news:uMJK3xalDHA.1808@.TK2MSFTNGP09.phx.gbl...
> I'd like to have a stored procedure run every n seconds. Not sure how to
> accomplish. Any ideas are appreciated.
> Regards,
> Mike
>