Problem running stored procedure inside another store procedure

hi,

I want to use a stored procedure inside a stored procedure simulteanously changing the database.
this is my base store procedure

alter PROCEDURE create_file @.dbname sysname
AS
declare @.fname varchar(30)
declare @.fsizes nvarchar
BEGIN
DECLARE @.cmd varchar(1000)
set @.cmd = 'osql -E -d ' + @.dbname + ' -Q "exec ret_sizes @.fname OUTPUT,@.fsizes OUTPUT"'
exec master..xp_cmdshell @.cmd
END

if i execute the his "exec create_file ertis"
i get error as

Msg 137, Level 15, State 2, Server HCC-BPVHD1, Line 1
Must declare the variable '@.fname'.
NULL

the procedure called inside the mail procedure is

alter procedure ret_sizes
@.fname varchar(30) OUTPUT,
@.fsizes nvarchar OUTPUT
as
begin
select @.fname=name, @.fsizes=size from sysfiles
order by fileid
end

Please help meThe @.Cmd string doesn't seem to include the second exec.

set @.cmd = 'osql -E -d ' + @.dbname + ' + '-Q' + ' "exec ret_sizes @.fname OUTPUT,@.fsizes OUTPUT"' '

exec @.CMD

__________________________________________________ ___________

Quote:

Originally Posted by eRTIS SQL

hi,

I want to use a stored procedure inside a stored procedure simulteanously changing the database.
this is my base store procedure

alter PROCEDURE create_file @.dbname sysname
AS
declare @.fname varchar(30)
declare @.fsizes nvarchar
BEGIN
DECLARE @.cmd varchar(1000)
set @.cmd = 'osql -E -d ' + @.dbname + ' -Q "exec ret_sizes @.fname OUTPUT,@.fsizes OUTPUT"'
exec master..xp_cmdshell @.cmd
END

if i execute the his "exec create_file ertis"
i get error as

Msg 137, Level 15, State 2, Server HCC-BPVHD1, Line 1
Must declare the variable '@.fname'.
NULL

the procedure called inside the mail procedure is

alter procedure ret_sizes
@.fname varchar(30) OUTPUT,
@.fsizes nvarchar OUTPUT
as
begin
select @.fname=name, @.fsizes=size from sysfiles
order by fileid
end

Please help me

sql

Problem replicating with mssql server 2000

Hi, i have been trying to replicate one of my server i have created the
ditributor and published the data base. I also have pushed a
subscription but when it is creating the snapshot i get this error:
Invalid object Name dbo.fn_dlxGet_fmt
But in the articles that i'm publishing there is no dbo.fn_dlxGet_fmt.
And the Agent give this error:
Cannot use DROP VIEW on DLXTABLE Because DLXTable is a table, and i not
publishing any views.
I'm using transactional replication, and i'm letting the replication
process to create the snapshot on the client, i have not created any
table, view or functions. In the replication client.Darth,
Could the function be used in a constraint definition in one of your tables?
HTH
Jerry
"darth timeus" <darth.timeus@.gmail.com> wrote in message
news:1129142934.774404.87880@.g44g2000cwa.googlegroups.com...
> Hi, i have been trying to replicate one of my server i have created the
> ditributor and published the data base. I also have pushed a
> subscription but when it is creating the snapshot i get this error:
> Invalid object Name dbo.fn_dlxGet_fmt
> But in the articles that i'm publishing there is no dbo.fn_dlxGet_fmt.
> And the Agent give this error:
> Cannot use DROP VIEW on DLXTABLE Because DLXTable is a table, and i not
> publishing any views.
> I'm using transactional replication, and i'm letting the replication
> process to create the snapshot on the client, i have not created any
> table, view or functions. In the replication client.
>|||The table DLXVOIPUSER, uses this function as a formula:
([dbo].[fn_dlxGet_dtmf]([dlxlast_name]))
what can i do i'm a newbie in this.
Jesus

Problem replicating with mssql server 2000

Hi, i have been trying to replicate one of my server i have created the
ditributor and published the data base. I also have pushed a
subscription but when it is creating the snapshot i get this error:
Invalid object Name dbo.fn_dlxGet_fmt
But in the articles that i'm publishing there is no dbo.fn_dlxGet_fmt.
And the Agent give this error:
Cannot use DROP VIEW on DLXTABLE Because DLXTable is a table, and i not
publishing any views.
I'm using transactional replication, and i'm letting the replication
process to create the snapshot on the client, i have not created any
table, view or functions. In the replication client.
Darth,
Could the function be used in a constraint definition in one of your tables?
HTH
Jerry
"darth timeus" <darth.timeus@.gmail.com> wrote in message
news:1129142934.774404.87880@.g44g2000cwa.googlegro ups.com...
> Hi, i have been trying to replicate one of my server i have created the
> ditributor and published the data base. I also have pushed a
> subscription but when it is creating the snapshot i get this error:
> Invalid object Name dbo.fn_dlxGet_fmt
> But in the articles that i'm publishing there is no dbo.fn_dlxGet_fmt.
> And the Agent give this error:
> Cannot use DROP VIEW on DLXTABLE Because DLXTable is a table, and i not
> publishing any views.
> I'm using transactional replication, and i'm letting the replication
> process to create the snapshot on the client, i have not created any
> table, view or functions. In the replication client.
>
|||The table DLXVOIPUSER, uses this function as a formula:
([dbo].[fn_dlxGet_dtmf]([dlxlast_name]))
what can i do i'm a newbie in this.
Jesus
sql

Problem replicating with mssql server 2000

Hi, i have been trying to replicate one of my server i have created the
ditributor and published the data base. I also have pushed a
subscription but when it is creating the snapshot i get this error:
Invalid object Name dbo.fn_dlxGet_fmt
But in the articles that i'm publishing there is no dbo.fn_dlxGet_fmt.
And the Agent give this error:
Cannot use DROP VIEW on DLXTABLE Because DLXTable is a table, and i not
publishing any views.
I'm using transactional replication, and i'm letting the replication
process to create the snapshot on the client, i have not created any
table, view or functions. In the replication client.Darth,
Could the function be used in a constraint definition in one of your tables?
HTH
Jerry
"darth timeus" <darth.timeus@.gmail.com> wrote in message
news:1129142934.774404.87880@.g44g2000cwa.googlegroups.com...
> Hi, i have been trying to replicate one of my server i have created the
> ditributor and published the data base. I also have pushed a
> subscription but when it is creating the snapshot i get this error:
> Invalid object Name dbo.fn_dlxGet_fmt
> But in the articles that i'm publishing there is no dbo.fn_dlxGet_fmt.
> And the Agent give this error:
> Cannot use DROP VIEW on DLXTABLE Because DLXTable is a table, and i not
> publishing any views.
> I'm using transactional replication, and i'm letting the replication
> process to create the snapshot on the client, i have not created any
> table, view or functions. In the replication client.
>|||The table DLXVOIPUSER, uses this function as a formula:
([dbo].[fn_dlxGet_dtmf]([dlxlast_name]))
what can i do i'm a newbie in this.
Jesus

Problem on Saving Data to one of the Database but work normal on other DB

Hi All,
I have some trouble on supporting a Windows Base System wrote by DELPHI
language implemented in my Company
Environment :
There are two Windows Server 2003 Std Ed servers
Server1 is Application Server
Server2 is Database Server(MS SQL2000 Std SP3a)
Hardware:
IBM Dual Xeon CPU
4GB Memory
Raid5 with 137GB Harddisk Space
Networking:
Server1 exposed to the Internet with firewall protected
A cross over cable connected between Server1 and Server2
Our Case:
We setup the Application on Server1
And all Workstations using the Windows Base Interface to use the program
If some user work outside Office, they will use the program through a remote
connection named TAXXI(http://www.taxxi.com).
Now we met a problem, on the SQL Server, we have created 3-5 database for
different group of users.
One of the database named D on the SQL Server always hang without any error
log.
It suddenly hang when user save their Data.
They can get the data from the System ,
only Save the data will make the screen freeze and lost of data after the
system back to normal in a hrs later.
We can't find any strange log on the EVENT VIEWER or SQL Server Log
From the Performance Monitor, we just found the Page Fault/sec was high and
always reach the Peak on Server1.
For Server2, only Write to Physical Disk was high.
If we login to other database in the time database D freeze the user screen
when save,
it works normally with input or save data to the system.
Plz help and give some suggestions for us to solve the problem.
Thx
Jack
Have you checked for database blocking during the save operation?
Hope this helps.
Dan Guzman
SQL Server MVP
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>
|||I agree with Dan, it sounds like a locking problem... when things are hung
up, use sp_who or Sp_who2, and check the blocking field. If it is non-zero,
then the corresponding spid is being blocked by the spid in the blocking
field...
This problem is generally caused by long-running trasnactions.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>

Problem on Saving Data to one of the Database but work normal on other DB

Hi All,
I have some trouble on supporting a Windows Base System wrote by DELPHI
language implemented in my Company
Environment :
There are two Windows Server 2003 Std Ed servers
Server1 is Application Server
Server2 is Database Server(MS SQL2000 Std SP3a)
Hardware:
IBM Dual Xeon CPU
4GB Memory
Raid5 with 137GB Harddisk Space
Networking:
Server1 exposed to the Internet with firewall protected
A cross over cable connected between Server1 and Server2
Our Case:
We setup the Application on Server1
And all Workstations using the Windows Base Interface to use the program
If some user work outside Office, they will use the program through a remote
connection named TAXXI(http://www.taxxi.com).
Now we met a problem, on the SQL Server, we have created 3-5 database for
different group of users.
One of the database named D on the SQL Server always hang without any error
log.
It suddenly hang when user save their Data.
They can get the data from the System ,
only Save the data will make the screen freeze and lost of data after the
system back to normal in a hrs later.
We can't find any strange log on the EVENT VIEWER or SQL Server Log
From the Performance Monitor, we just found the Page Fault/sec was high and
always reach the Peak on Server1.
For Server2, only Write to Physical Disk was high.
If we login to other database in the time database D freeze the user screen
when save,
it works normally with input or save data to the system.
Plz help and give some suggestions for us to solve the problem.
Thx
JackHave you checked for database blocking during the save operation?
Hope this helps.
Dan Guzman
SQL Server MVP
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>|||I agree with Dan, it sounds like a locking problem... when things are hung
up, use sp_who or Sp_who2, and check the blocking field. If it is non-zero,
then the corresponding spid is being blocked by the spid in the blocking
field...
This problem is generally caused by long-running trasnactions.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>

Problem on Saving Data to one of the Database but work normal on other DB

Hi All,
I have some trouble on supporting a Windows Base System wrote by DELPHI
language implemented in my Company
Environment :
There are two Windows Server 2003 Std Ed servers
Server1 is Application Server
Server2 is Database Server(MS SQL2000 Std SP3a)
Hardware:
IBM Dual Xeon CPU
4GB Memory
Raid5 with 137GB Harddisk Space
Networking:
Server1 exposed to the Internet with firewall protected
A cross over cable connected between Server1 and Server2
Our Case:
We setup the Application on Server1
And all Workstations using the Windows Base Interface to use the program
If some user work outside Office, they will use the program through a remote
connection named TAXXI(http://www.taxxi.com).
Now we met a problem, on the SQL Server, we have created 3-5 database for
different group of users.
One of the database named D on the SQL Server always hang without any error
log.
It suddenly hang when user save their Data.
They can get the data from the System ,
only Save the data will make the screen freeze and lost of data after the
system back to normal in a hrs later.
We can't find any strange log on the EVENT VIEWER or SQL Server Log
From the Performance Monitor, we just found the Page Fault/sec was high and
always reach the Peak on Server1.
For Server2, only Write to Physical Disk was high.
If we login to other database in the time database D freeze the user screen
when save,
it works normally with input or save data to the system.
Plz help and give some suggestions for us to solve the problem.
Thx
JackHave you checked for database blocking during the save operation?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>|||I agree with Dan, it sounds like a locking problem... when things are hung
up, use sp_who or Sp_who2, and check the blocking field. If it is non-zero,
then the corresponding spid is being blocked by the spid in the blocking
field...
This problem is generally caused by long-running trasnactions.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>