connecting to SSAS: stored

Showing posts with label stored. Show all posts

Wednesday, March 28, 2012

problem in executing extended stored procedure

Hi ll,
When i am trying to execute extended stored procedure im getting
error as:
Msg 22048, Level 15, State 0, Line 0
Error executing extended stored procedure: Invalid Parameter
Can u guys help me outHi,
Tell us exactly which command and parameters are you using.
By the way, have you checked BOL (if applies) for the parameters that you
need to specify?
Hope this helps,
Ben Nevarez
"mohit" wrote:
> Hi ll,
> When i am trying to execute extended stored procedure im getting
> error as:
> Msg 22048, Level 15, State 0, Line 0
> Error executing extended stored procedure: Invalid Parameter
> Can u guys help me out
>|||On Jan 14, 12:18 pm, Ben Nevarez
<BenNeva...@.discussions.microsoft.com> wrote:
> Hi,
> Tell us exactly which command and parameters are you using.
> By the way, have you checked BOL (if applies) for the parameters that you
> need to specify?
> Hope this helps,
> Ben Nevarez
>
> "mohit" wrote:
> > Hi ll,
> > When i am trying to execute extended stored procedure im getting
> > error as:
> > Msg 22048, Level 15, State 0, Line 0
> > Error executing extended stored procedure: Invalid Parameter
> > Can u guys help me out- Hide quoted text -
> - Show quoted text -
Hi,
Question is something like this:
In procedure create folder for each database and backup of the
database should get saved in that folder.
For e.g : If your database name is 'Test' then in procedure create
folder 'test' and store backup of test database in test folder. Then
for database 'AdventureWorks' create folder called 'AdventureWorks'
and store backup in that folder.
For this i am using command as Execute xp_create_subdir and parameter
as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great
help if u could tell.|||Not sure about what exactly your problem is but something like this works
declare @.pathDir nvarchar(80)
set @.pathDir = 'c:\YourDB'
execute master.dbo.xp_create_subdir @.pathDir
By the way, xp_create_subdir is used on the Maintenance Plans but it looks
like it is not documented on BOL.
Hope this helps,
Ben Nevarez
"mohit" wrote:
> On Jan 14, 12:18 pm, Ben Nevarez
> <BenNeva...@.discussions.microsoft.com> wrote:
> > Hi,
> >
> > Tell us exactly which command and parameters are you using.
> >
> > By the way, have you checked BOL (if applies) for the parameters that you
> > need to specify?
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> > "mohit" wrote:
> > > Hi ll,
> > > When i am trying to execute extended stored procedure im getting
> > > error as:
> > > Msg 22048, Level 15, State 0, Line 0
> > > Error executing extended stored procedure: Invalid Parameter
> >
> > > Can u guys help me out- Hide quoted text -
> >
> > - Show quoted text -
> Hi,
> Question is something like this:
> In procedure create folder for each database and backup of the
> database should get saved in that folder.
> For e.g : If your database name is 'Test' then in procedure create
> folder 'test' and store backup of test database in test folder. Then
> for database 'AdventureWorks' create folder called 'AdventureWorks'
> and store backup in that folder.
> For this i am using command as Execute xp_create_subdir and parameter
> as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great
> help if u could tell.
>
>|||On Jan 14, 1:48=A0pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Not sure about what exactly your problem is but something like this works
> declare @.pathDir nvarchar(80)
> set @.pathDir =3D 'c:\YourDB'
> execute master.dbo.xp_create_subdir @.pathDir
> By the way, xp_create_subdir is used on the Maintenance Plans but it looks=
> like it is not documented on BOL.
> Hope this helps,
> Ben Nevarez
>
> "mohit" wrote:
> > On Jan 14, 12:18 pm, Ben Nevarez
> > <BenNeva...@.discussions.microsoft.com> wrote:
> > > Hi,
> > > Tell us exactly which command and parameters are you using.
> > > By the way, have you checked BOL (if applies) for the parameters that =you
> > > need to specify?
> > > Hope this helps,
> > > Ben Nevarez
> > > "mohit" wrote:
> > > > Hi ll,
> > > > =A0 =A0 When i am trying to execute extended stored procedure im get=ting
> > > > error as:
> > > > =A0 Msg 22048, Level 15, State 0, Line 0
> > > > Error executing extended stored procedure: Invalid Parameter
> > > > Can u guys help me out- Hide quoted text -
> > > - Show quoted text -
> > Hi,
> > =A0 =A0Question is something like this:
> > =A0 =A0In procedure create folder for each database and backup of the
> > database should get saved in that folder.
> > For e.g =A0: If your database name is 'Test' then in procedure create
> > folder 'test' and store backup of test database in test folder. Then
> > for database 'AdventureWorks' create folder called 'AdventureWorks'
> > and store backup in that folder.
> > For this i am using command as Execute xp_create_subdir and parameter
> > as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great
> > help if u could tell.- Hide quoted text -
> - Show quoted text -
hi,
The query which you gave is working fine.
But mine is not working can you just check out
create proc [dbo].[backup_proc4] @.para1 varchar(120)
As
declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
select @.str=3D'Adventureworks,Adventureworksdw,test,model'
while(1 <> 2)
begin
select @.var1=3Dsubstring(@.str,0,charindex(',',@.str))
if (@.var1=3D'')
begin
break
end
select @.para1=3D@.var1
declare @.str1 varchar(200)
set @.dirPath=3D'''D:\'+@.para1+'\'+@.para1+'.bak'''
print @.dirPath
execute master.dbo.xp_create_subdir @.dirPath
--set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ exec
xp_create_subdir @.dirPath
--exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+' '+ exec
xp_create_subdir @.dirPath)
select @.str=3Dright(@.str,len(@.str)-len(substring(@.str,
1,charindex(',',@.str))))
end
--select @.para1=3D@.str
--set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ '''D:
\backup_of_db\'+ @.para1+'.bak'''
--exec(@.str1)|||Hi,
I have not tested your stored procedure but at least I removed the syntax
errors here.
create proc [dbo].[backup_proc4] @.para1 varchar(120)
As
declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
select @.str='Adventureworks,Adventureworksdw,test,model'
while(1 <> 2)
begin
select @.var1=substring(@.str,0,charindex(',',@.str))
if (@.var1='')
begin
break
end
select @.para1=@.var1
declare @.str1 varchar(200)
set @.dirPath='''D:\'+@.para1+'\'+@.para1+'.bak'''
print @.dirPath
execute master.dbo.xp_create_subdir @.dirPath
--set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ exec
exec xp_create_subdir @.dirPath
--exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk='+' '+ exec
exec xp_create_subdir @.dirPath
select @.str=right(@.str,len(@.str)-len(substring(@.str,
1,charindex(',',@.str))))
end
--select @.para1=@.str
--set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ '''D:
--\backup_of_db\'+ @.para1+'.bak'''
--exec(@.str1)
Hope this helps,
Ben Nevarez
"mohit" wrote:
> On Jan 14, 1:48 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> wrote:
> > Not sure about what exactly your problem is but something like this works
> >
> > declare @.pathDir nvarchar(80)
> > set @.pathDir = 'c:\YourDB'
> > execute master.dbo.xp_create_subdir @.pathDir
> >
> > By the way, xp_create_subdir is used on the Maintenance Plans but it looks
> > like it is not documented on BOL.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> >
> >
> >
> > "mohit" wrote:
> > > On Jan 14, 12:18 pm, Ben Nevarez
> > > <BenNeva...@.discussions.microsoft.com> wrote:
> > > > Hi,
> >
> > > > Tell us exactly which command and parameters are you using.
> >
> > > > By the way, have you checked BOL (if applies) for the parameters that you
> > > > need to specify?
> >
> > > > Hope this helps,
> >
> > > > Ben Nevarez
> >
> > > > "mohit" wrote:
> > > > > Hi ll,
> > > > > When i am trying to execute extended stored procedure im getting
> > > > > error as:
> > > > > Msg 22048, Level 15, State 0, Line 0
> > > > > Error executing extended stored procedure: Invalid Parameter
> >
> > > > > Can u guys help me out- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > Hi,
> > > Question is something like this:
> >
> > > In procedure create folder for each database and backup of the
> > > database should get saved in that folder.
> >
> > > For e.g : If your database name is 'Test' then in procedure create
> > > folder 'test' and store backup of test database in test folder. Then
> > > for database 'AdventureWorks' create folder called 'AdventureWorks'
> > > and store backup in that folder.
> >
> > > For this i am using command as Execute xp_create_subdir and parameter
> > > as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great
> > > help if u could tell.- Hide quoted text -
> >
> > - Show quoted text -
> hi,
> The query which you gave is working fine.
> But mine is not working can you just check out
>
> create proc [dbo].[backup_proc4] @.para1 varchar(120)
> As
> declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
> select @.str='Adventureworks,Adventureworksdw,test,model'
> while(1 <> 2)
> begin
> select @.var1=substring(@.str,0,charindex(',',@.str))
> if (@.var1='')
> begin
> break
> end
> select @.para1=@.var1
> declare @.str1 varchar(200)
> set @.dirPath='''D:\'+@.para1+'\'+@.para1+'.bak'''
> print @.dirPath
> execute master.dbo.xp_create_subdir @.dirPath
> --set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ exec
> xp_create_subdir @.dirPath
> --exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk='+' '+ exec
> xp_create_subdir @.dirPath)
> select @.str=right(@.str,len(@.str)-len(substring(@.str,
> 1,charindex(',',@.str))))
> end
> --select @.para1=@.str
> --set @.str1='backup database' + ' ' + @.para1 + ' ' + 'to disk='+ '''D:
> \backup_of_db\'+ @.para1+'.bak'''
> --exec(@.str1)
>|||On Jan 14, 2:30=A0pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
wrote:
> Hi,
> I have not tested your stored procedure but at least I removed the syntax
> errors here.
> create proc [dbo].[backup_proc4] @.para1 varchar(120)
> As
> declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
> select @.str=3D'Adventureworks,Adventureworksdw,test,model'
> while(1 <> 2)
> begin
> select @.var1=3Dsubstring(@.str,0,charindex(',',@.str))
> if (@.var1=3D'')
> begin
> break
> end
> select @.para1=3D@.var1
> declare @.str1 varchar(200)
> set @.dirPath=3D'''D:\'+@.para1+'\'+@.para1+'.bak'''
> print @.dirPath
> execute master.dbo.xp_create_subdir @.dirPath
> --set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ exec
> exec xp_create_subdir @.dirPath
> --exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+' '+ exec
> exec xp_create_subdir @.dirPath
> select @.str=3Dright(@.str,len(@.str)-len(substring(@.str,
> 1,charindex(',',@.str))))
> end
> --select @.para1=3D@.str
> --set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ '''D:=
> --\backup_of_db\'+ @.para1+'.bak'''
> --exec(@.str1)
> Hope this helps,
> Ben Nevarez
>
> "mohit" wrote:
> > On Jan 14, 1:48 pm, Ben Nevarez <BenNeva...@.discussions.microsoft.com>
> > wrote:
> > > Not sure about what exactly your problem is but something like this wo=rks
> > > declare @.pathDir nvarchar(80)
> > > set @.pathDir =3D 'c:\YourDB'
> > > execute master.dbo.xp_create_subdir @.pathDir
> > > By the way, xp_create_subdir is used on the Maintenance Plans but it l=ooks
> > > like it is not documented on BOL.
> > > Hope this helps,
> > > Ben Nevarez
> > > "mohit" wrote:
> > > > On Jan 14, 12:18 pm, Ben Nevarez
> > > > <BenNeva...@.discussions.microsoft.com> wrote:
> > > > > Hi,
> > > > > Tell us exactly which command and parameters are you using.
> > > > > By the way, have you checked BOL (if applies) for the parameters t=hat you
> > > > > need to specify?
> > > > > Hope this helps,
> > > > > Ben Nevarez
> > > > > "mohit" wrote:
> > > > > > Hi ll,
> > > > > > =A0 =A0 When i am trying to execute extended stored procedure im= getting
> > > > > > error as:
> > > > > > =A0 Msg 22048, Level 15, State 0, Line 0
> > > > > > Error executing extended stored procedure: Invalid Parameter
> > > > > > Can u guys help me out- Hide quoted text -
> > > > > - Show quoted text -
> > > > Hi,
> > > > =A0 =A0Question is something like this:
> > > > =A0 =A0In procedure create folder for each database and backup of th=e
> > > > database should get saved in that folder.
> > > > For e.g =A0: If your database name is 'Test' then in procedure creat=e
> > > > folder 'test' and store backup of test database in test folder. Then=
> > > > for database 'AdventureWorks' create folder called 'AdventureWorks'
> > > > and store backup in that folder.
> > > > For this i am using command as Execute xp_create_subdir and paramete=r
> > > > as @.pathDir. Actually i am new to SQL SERVER 2005.It will be a great=
> > > > help if u could tell.- Hide quoted text -
> > > - Show quoted text -
> > hi,
> > =A0 =A0The query which you gave is working fine.
> > =A0 =A0But mine is not working can you just check out
> > create proc [dbo].[backup_proc4] @.para1 varchar(120)
> > As
> > declare @.str varchar(50),@.var1 varchar(20),@.dirPath nvarchar(100)
> > select @.str=3D'Adventureworks,Adventureworksdw,test,model'
> > while(1 <> 2)
> > begin
> > select @.var1=3Dsubstring(@.str,0,charindex(',',@.str))
> > if (@.var1=3D'')
> > begin
> > break
> > end
> > select @.para1=3D@.var1
> > declare @.str1 varchar(200)
> > set @.dirPath=3D'''D:\'+@.para1+'\'+@.para1+'.bak'''
> > print @.dirPath
> > execute master.dbo.xp_create_subdir @.dirPath
> > --set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ exe=c
> > xp_create_subdir @.dirPath
> > --exec ('backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+' '+ exec
> > xp_create_subdir @.dirPath)
> > select @.str=3Dright(@.str,len(@.str)-len(substring(@.str,
> > 1,charindex(',',@.str))))
> > end
> > --select @.para1=3D@.str
> > --set @.str1=3D'backup database' + ' ' + @.para1 + ' ' + 'to disk=3D'+ '''=D:
> > \backup_of_db\'+ @.para1+'.bak'''
> > --exec(@.str1)- Hide quoted text -
> - Show quoted text -
Hi,
Thanks buddy.

Problem in Displaying NTEXT field from database?

Hello,

I have around 7 ntext fields in my data base table and I am getting data from the data base table through executing stored procedure, But when I am displaying data using record set, few of the ntext fields in recored set are empty .Iam sure that these are having data in table.

I am not sure why recordset is lossing that ntext field data?Because of this I am unable to display that data in web form.

any ideas really appriciated.

Thanks

Ram

text and ntext fields should always be either the only data returned or the last item in a select statement.

For example:

SELECT ntextField FROM myTable WHERE something = true

SELECT myFirstField, myOtherField, ntextField FROM myTable WHERE something = true

This is a limitation of MSSQL server and (some versions) of MySQL. If you are using MSSQL Server 2005, I would suggest changing the database column to a nvarchar(MAX) as this will store the same number of characters as an ntext column. (I believe they intended to remove text and ntext from the next release of MSSQL Server in favor of varchar(MAX) and nvarchar(MAX), but don't quote me on that.)

|||

I am using SQLserver 2000, What will be the possible solutions for this problem.

Thanks

|||

You will have to select each ntext field individually. So you will need 7 select statements each time.

However, I would recommend changing the database to use a different type of column. If you know the data will never be larger than 8K you can still use nvarchar(8000) on SQL Server 2000. Or you could combine some of the columns and then use delimiters so that you have one huge column with something like ||| between entries (but this may cause other problems.)

In general text, ntext and blob fields should be used as rarely as possible, it is usually easier to put that much data into a file and then store the files name and location in SQL Server.

Let me know if this doesn't really answer your question.

|||

Hi,

Its working for me now..What I did is ...

I have got all ntext fields from the recordset first into some varialbles before acesing non ntext fields and mapped to the form controls.

thats it.

thanks

Problem in Dataset

Hi

I created a stored procedure which returns a dataset
that is passed to crystal report like this

dset = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "spr_CR_Demo2")

dset.Tables(0).TableName = "spr_CR_Demo2"

crystal.SetDataSource(dset)
CrystalReportViewer2.ReportSource = crystal

But only one record get displayed,how can i do it
anyone can help me

ThanxAre you sure that sp returns more than one record?
Open the report and Do Verify database. Save the report and Try it again

Monday, March 26, 2012

Problem In Connecting Oracle9i SP with CR11.0

Hi!

I made an Stored Proc. in oracle 9i, in which i inserted data in a temporary table. The SP got compiled successfully. But when i tried to add that SP in the CR 11.0, through DSN, i got an error msg.

Database Connector Error: 'HY000:[Oracle][ODBC][Ora]ORA-01456: may not perform insert/delete/update operation inside a READ ONLY transaction
ORA-06512: at "SCOTT.TEST_INSERTDATA1", line 6
ORA-06512: at line 1

If data is not inserted in stored proc, then no error comes while connecting Crystal report to stored proc of oracle.

This problem is specifically for those stored procs, where data is inserted in some table inside stored proc and that is used in CR.

Code of Oracle Stored Proc is as follows.
----------------

create or replace procedure Test_InsertData1(curmain in out CommonCursor_Pkg.abc,arg1 varchar2,arg2 varchar2)
as
lcsqry varchar2(500);
begin
lcsqry:='delete from reptable1';
execute immediate(lcsqry);

for i in 1..10 loop
lcsqry:='insert into reptable1(sfld1,nfld1) values(''aa'','||i||')';
execute immediate(lcsqry);
end loop;
commit;

lcsqry:='select sfld1,nfld1 from reptable1';
open curmain for (lcsqry);

end;
/
--------------------
-- Here CommonCursor_Pkg.abc denotes a ref cursor type named 'abc' declared inside CommonCursor_Pkg package. Code for this is :

create package CommonCursor_Pkg
as
type abc is ref cursor;

end;
/
-------------------

-- 'reptable1' is a global temporary table.

--CR 11.0 is connected to oracle in following manner.

Goto
Database Expert-> create new connection ->ODBC(RDO)
Then selecting the Oracle DSN from the list.

Or

Goto
Database Expert-> Create new Connection-> OLE DB(ADO)

please see how to fix this problem.

Thanks and regards

Vineet kumarThe procedure should have select statement at the end. As you are using using dynamic sql, scope is over if you run that proceudre. Try using global temporary tablesql

Friday, March 23, 2012

problem in comparing date

Hi,

I have designed an employee portal. The moment the user logs in and logs out the current datetime will be stored.
Before the user logs out I should ensure whether he/she has entered the work done form for that day.

I wrote
select count(*) from workdone where work_date_time=getdate()
If there are any rows that means he/she has filled up the form else not filled.

But the query is comparing the date as well as time.
I want to compare only the date.
How should I reframe the query

Regards
cmrhema

Quote:

Originally Posted by cmrhema

Hi,

I have designed an employee portal. The moment the user logs in and logs out the current datetime will be stored.
Before the user logs out I should ensure whether he/she has entered the work done form for that day.

I wrote
select count(*) from workdone where work_date_time=getdate()
If there are any rows that means he/she has filled up the form else not filled.

But the query is comparing the date as well as time.
I want to compare only the date.
How should I reframe the query

Regards
cmrhema

SQL Server stores dates and time together. Depending on your application and how you are passing your date parameter in as part of your where clause (ie which part of the world you are in) you need to be mindful of your regional settings and look at the CONVERT function in SQL Server

SELECT CONVERT(char(10), GETDATE(), 103)

Will return getdate as character conversion based on the UK regional setting (103)

Regards

Jim :)

Problem in a QUERY with COUNT

CREATE PROCEDURE [dbo].[GD_SP_HARDWARE_MONITOR_COUNT]

-- Add the parameters for the stored procedure here

@.Direccao nvarchar(10)

DECLARE @.NrMon int

BEGIN

SELECT dbo.Monitor.MON_Monitor AS Item, COUNT(*) AS Unidades, dbo.Monitor.MON_CustoUnitario AS Total

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID

WHERE (dbo.HARDWARE.MONITOR_ID <> 5)

GROUP BY dbo.Monitor.MON_Monitor, dbo.Monitor.MON_CustoUnitario

END

DEAR FRIENDS,

HOW CAN I MULTIPLICATE THE VALUE FROM UNIDADES AND TOTAL?

Current output:

Unidades

/Total

TFT 17

417

/24,35

TFT 15

3254

/22,08

GOAL:

Unidades

/Total

TFT 17

417

/24,35

/10153,95

TFT 15

3254

/22,08

/71848,32

THANKS

Wrap your query in an outer query:

SELECT Item, Unidades, Total, Unidades*Total AS SomethingNew
FROM
(
Here comes your existing query
) SubQUery

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

THANKS!!!!!!

FANTASTIC!!!!

|||

Another question:
I made a UNION with 2 querys :

ALTER PROCEDURE [dbo].[GD_SP_FACTURA]

-- Add the parameters for the stored procedure here

@.Direccao nvarchar(10)

BEGIN

SELECT Item, Unidades, CustoUnitario, Unidades*CustoUnitario AS Total

FROM

(

SELECT dbo.ModeloPC_Tipo.MOD_Nome AS Item, COUNT(*) AS Unidades, dbo.ModeloPC_Tipo.MOD_CustoUnit AS CustoUnitario

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.ModeloPC ON dbo.HARDWARE.MODELO_ID = dbo.ModeloPC.MODELO_ID INNER JOIN

dbo.ModeloPC_Tipo ON dbo.ModeloPC.MOD_Tipo = dbo.ModeloPC_Tipo.MOD_ID

WHERE (dbo.HARDWARE.MONITOR_ID <> 5)

GROUP BY dbo.SERVICO.S_NomeDir, dbo.ModeloPC_Tipo.MOD_CustoUnit, dbo.ModeloPC_Tipo.MOD_Nome

HAVING (dbo.SERVICO.S_NomeDir = @.Direccao)

) SubQUery

UNION

SELECT Item, Unidades, CustoUnitario, Unidades*CustoUnitario AS Total

FROM

(

SELECT dbo.Monitor.MON_Monitor AS Item, COUNT(*) AS Unidades, dbo.Monitor.MON_CustoUnitario AS CustoUnitario

FROM dbo.HARDWARE INNER JOIN

dbo.ADServico_User ON dbo.HARDWARE.UserID = dbo.ADServico_User.UserID INNER JOIN

dbo.SERVICO ON dbo.ADServico_User.GrupoServico = dbo.SERVICO.S_GrupoServico INNER JOIN

dbo.Monitor ON dbo.HARDWARE.MONITOR_ID = dbo.Monitor.MONITOR_ID

WHERE (dbo.HARDWARE.MONITOR_ID =1 OR dbo.HARDWARE.MONITOR_ID=2) AND dbo.SERVICO.S_NomeDir=@.Direccao

GROUP BY dbo.Monitor.MON_Monitor, dbo.Monitor.MON_CustoUnitario

) SubQUery

END

OUTPUT:
Desktops 166 433,09 71892,94
Portáteis 3 675,84 2027,52
TFT 15 166 22,08 3665,28
TFT 17 3 24,35 73,05

How can I SUM the last Column of the 2 queries? How can I SUM (Total)?

Thanks!!!

|||

--1.SUM the last two columns

SELECT t.Item, t.Unidades, (t.CustoUnitario+t.Total) AS LAST2Sum FROM (Your UNION result) t

--2.SUM your TOTAL

SELECT SUM(t.Total) AS SumTotal FROM (Your UNION result) t

Problem in a dynamic stored procedurehelp

I create a stored procedure as following:
......

CREATE Procedure myAutoSearch
(
@.Make varchar(50),
@.Model varchar(50),
@.AutoType varchar(50),
@.Miles float,
@.Zipcode varchar(5)
)
AS

DECLARE @.RowCount int
SELECT @.RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @.Zipcode AND CityType = 'D'

if @.RowCount > 0
BEGIN

SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear,
a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@.ZIPCode,@.Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @.Make
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @.Miles
/*
The above functions requires the Distance Assistant.
Also note that SQL Server caches the results so that this and the "SELECT dbo.DistanceAssistant"
functions are both only computed once.
*/
ORDER BY Distance, Make

END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...

...............

This stored procedure work very well.

The question is how I add some dynamic condition inside the where condition.

I want to add:

If @.Model <> "See All Models"
AND a.Model = @.Model'

If @.AutoType <> "New/Used"
AND a.Condition = @.AutoType

I try several ways, but fail.

If you know how to add these two dynamic parameters into the condition of stored procedure, please help.Pass paremeters like this:

CREATE Procedure myAutoSearch
(
@.Make varchar(50),
@.Model varchar(50) = NULL,
@.AutoType varchar(50),
@.Miles float,
@.Zipcode varchar(5)
)

and then do this in the Where clause:

a.Model = IsNull(@.Model,a.Model)

IsNull returns the left-most non-null value, so if @.Model is not passed in (and thus, gets a default value of NULL) then the comparison will be a.Model=a.Model, and thus always true.|||Hi douglas,

Thanks for answering my question. I think you misunderstand my question.
There are a lot of models for each Make. So if user select specific Make (like "Toyata") and "See All Model", then I only need add one condition "AND a.Make = @.Make" in my SP. But if user select specific Make and specific Model (like Tayata, Corolla), then in my where condition, I need add two condition " AND a.Make = @.Make AND a.Model = @.Model". So I need dynamic to add "AND a.Model = @.Model".

In your code, if I don't pass Model (Model=' ' or 'NULL') or Pass Model = "See All Model", then Model condition will become "AND a.Model = 'NULL' ", not any model will be retrieved.

In my situation, I have two or three this kind of dynamic conditions.

Any way to solve this problem?

Thanks again.

Lin|||The code I supplied will handle EXACTLY that situation. If parameter is NULL, then ALL will be selected. As I explained:

IsNull returns the left-most non-null value, so if @.Model is not passed in (and thus, gets a default value of NULL) then the comparison will be a.Model=a.Model, and thus always true.|||A small suggestion for the Original Poster is to d/l or use Books Online that's available for free download at Microsoft.com/Sql or in the Start Menu under Sql Server.

It's a great help doc that I have in my task bar as a quick link, and I use it frequently.

that's all. :) Enjoy your day.|||Edited by SomeNewKid. Please post code between<code> and</code> tags.

Hi douglas,

Here is my SP, I have added @.model = NULL, and added IsNull in where condition as you said:

..............

CREATE Procedure Ruying_AutoSearch7
(
@.Make varchar(50),
@.Model varchar(50) = NULL,
@.Miles float,
@.Zipcode varchar(5)
)
ASDECLARE @.RowCount int
SELECT @.RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @.Zipcode AND CityType = 'D'
if @.RowCount > 0
BEGIN
SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear,
a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@.ZIPCode,@.Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @.Make
AND a.Model = IsNull(@.Model,a.Model)
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @.Miles
/*
The above functions requires the Distance Assistant.
Also note that SQL Server caches the results so that this and the "SELECT dbo.DistanceAssistant"
functions are both only computed once.
*/
ORDER BY Distance, Make
END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...
GO

...............

The following is my middle tier class funtion:

..................

Public Function GetAutoSearchItems7(ByVal Make As String, ByVal Model As String, ByVal Miles As Double, ByVal Zipcode As String) As SqlDataReader ' Create Instance of Connection and Command Object
 Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
 Dim myCommand As SqlCommand = New SqlCommand("Ruying_AutoSearch6", myConnection)
 ' Mark the Command as a SPROC
 myCommand.CommandType = CommandType.StoredProcedure
 ' Add Parameters to SPROC
 Dim parameterMake As SqlParameter = New SqlParameter("@.Make", SqlDbType.VarChar, 50)
 parameterMake.Value = Make
 myCommand.Parameters.Add(parameterMake)
 Dim parameterModel As SqlParameter = New SqlParameter("@.Model", SqlDbType.VarChar, 50)
 parameterModel.Value = Model
 myCommand.Parameters.Add(parameterModel)
 Dim parameterMiles As SqlParameter = New SqlParameter("@.Miles", SqlDbType.Float, 8)
 parameterMiles.Value = Miles
 myCommand.Parameters.Add(parameterMiles)
 Dim parameterZipcode As SqlParameter = New SqlParameter("@.Zipcode", SqlDbType.VarChar, 5)
 parameterZipcode.Value = Zipcode
 myCommand.Parameters.Add(parameterZipcode)
 ' Execute the command
 myConnection.Open()
 Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
 ' Return the datareader result
 Return result
 End Function

.............

The folowing is ASP.NET code behind code:

....................

 SMake = Request.Form("ddlMake")
 SModel = Request.Form("ddlModel")
 lblMiles.Text = Request.Form("ddlMile")
 SMile = CInt(lblMiles.Text)
 lblZipcode.Text = Request.Form("txtZipcode")
 AutoCondition = Request.Form("rdlAutoCondition") lblMake.Text = SMake
 If SModel = "See All Models" Then
 lblModel.Text = ""
 Else
 lblModel.Text = SModel
 End If
 If AutoCondition = "New" Then
 lblAutoCondition.Text = "New"
 ElseIf AutoCondition = "Used" Then
 lblAutoCondition.Text = "Used"
 Else
 lblAutoCondition.Text = "New/Used"
 End If
 Dim mySearch As Ruying.SearchDB = New Ruying.SearchDB()
 dgSearchResult.DataSource = mySearch.GetAutoSearchItems7(SMake, lblModel.Text, SMile, lblZipcode.Text)
 dgSearchResult.DataBind()
 dgSearchResult.Dispose()

...............

The resule is that if I select "See All Model", nothing show up.
I change the code to:

 If SModel = "See All Models" Then
 lblModel.Text = "NULL"
 Else
 lblModel.Text = SModel
 End If

The result is same.

But if I select other model (specific model), there are data show up.

Anywhere I can change?

Thanks.

Lin|||You are confusing NULL with 'NULL'. NULL (no quotes) is a special word in SQL Server meaning no value, whereas 'NULL' is a 4 character string.

Do this:


If Model<>String.Empty AND Model<>"See All Models" then
 Dim parameterModel As SqlParameter = New SqlParameter("@.Model", SqlDbType.VarChar, 50)
 parameterModel.Value = Model
 myCommand.Parameters.Add(parameterModel)
End If

This way, the parameter is NOT added if not set, and the default set will be NULL (as opposed to 'NULL', 'Show All Models' or '').|||Hi douglas,

I use


If Model<>String.Empty then Dim parameterModel As SqlParameter = New SqlParameter("@.Model", SqlDbType.VarChar, 50)
 parameterModel.Value = Model
 myCommand.Parameters.Add(parameterModel)
End If

in the middle tier function. Now SP works very well. Thank you very much.

One more question in my program.

In asp page, I have AutoCondition to be selected. It has three values as "New", "Used" and "New/Used". In database, AutoCondition has "New", "Like New", "Good" and "Acceptable" values. Now I must use two stored procedure to retrieve the data. I use: AND a.Condition = IsNull(@.Condition, a.Condition) for user select "New" and "New/Used" (when select the "New/Used", set @.Condition = NULL, just like above case). I use: AND a.Condtion <> 'New' in second stored procedure for user select "Used".

Does any way can dynamic change this condition in WHERE clause and become just one stored procedure?

Thanks.

Lin|||I would create a table with the car conditions.

ConditionID int IDENTITY
Description nvarchar(100)
IsNew bit

Then, don't store the text, but store the COnditionID in the Auto table. Only "New" qualifies as new. So you would do a join, and have a condition like:

IsNew=IsNull(@.IsNew,IsNew)

Pass 1 for @.IsNew if they select New, 0 if they select Used, and NULL if they say New/Used.

Opon reflection, it is possible bit cannot be null, in which case you would need to use SmallInt (you should check this out).|||I'll try it. Thank you very much.sql

Wednesday, March 21, 2012

problem getting result set through a stored procedure call using VB.

Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.See if this helps: http://www.sqlxml.org/faqs.aspx?faq=104
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"abc" <er.nehasinghal@.gmail.com> wrote in message
news:1121936278.733465.80930@.g47g2000cwa.googlegroups.com...
Problem regarding getting an XML script from a stored procedure that
returns XML string format of a select query on a temporary table
created by the stored procedure itself and values also inserted within
the stored procedure.sql

Problem getting execution status of a SQL Agent Job

I’m having trouble retrieving the execution status of a job – perhaps someone can help me.

I have a stored proc (on SQL 2005) that dynamically creates and executes a SQL Agent Job. The stored proc first has to check whether the job exists. If it already exists and is not active then I simply reuse the existing job. However if the job exists but is currently executing then I need to create a new job because there can be multiple instances of the program that the job runs. My problem is how to check whether the job is currently executing. I tried using sp_help_job as shown below but the rowcount seems to return 1 even when there are no jobs executing!

Exec msdb.dbo.sp_help_job

@.job_aspect = 'JOB',

@.execution_status = 0,

@.job_name = @.JobName

If @.@.rowcount > 0 Begin

…

End

I then tried this code…

If (select count(*) from msdb.dbo.sysjobs j

left outer join msdb.dbo.sysjobhistory h on j.job_id = h.job_id where name = @.JobName and

h.step_id = 1 and

run_status = 4) > 0

Begin

…

End

… but it doesn’t seem to pick up records even when the job is currently executing.

How can I, in T-SQL, get the execution status of the job?

I have the same issue. When I began to dig into sp_help_job, I noticed a call within the procedure to build a temp table:

exec master.dbo.xp_sqlagent_enum_jobs 1, {insert job owner here}

This procedure outputs a 'Running' field that will return a 1 if it the job is running, or otherwise a 0. This is not complete code for you, but should get you pointed in the right direction. I am going to build a temp table with this XP and filter on the job ID to return the status.

|||sp_help_jobactivity is the sp that should give status of jobs in SQL2005. See BOL for more info on this SP.sql

Problem generating error file using BULK INSERT or BCP thru xp_cmdshell.

BCP thru xp_cmdshell from stored procedure:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE

EXEC xp_cmdshell 'bcp database.dbo.table in c:\scheduled.csv -S SERVER\SQLEXPRESS -T -t, -r\n -c -e "error.txt"';

This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.

=================================================================

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unable to open BCP error-file

=================================================================

Error message when using BULK INSERT as follows:

BULK INSERT database.dbo.table from 'c:\unscheduled.csv' with

(FIELDTERMINATOR = ',', ERRORFILE = 'c:\error.txt');

Returns the following error message:

=================================================================

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

=================================================================

The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.

EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2

bcp or bulk insert will not overwrite or append to the error file. If you get an error the first time, and want to rerun your command, you need to either delete these files, or specify a new location for the error file.

|||

Unfourtunetly I am deleting the file, I'm still doing so by hand in testing.

On bcp thru xp_cmdshell it does not generate an errorfile at all (this actually works just fine from the command line just not thru a stored procedure), with bulk insert it generates a error file which is completely blank (even tho I know there is 6 rows that cannot be imported) of size 0kb, so totally empty. Also it does not actually execute the insert.

Most likely it is a security setting. I can not find it however. Another possiblity is maybe I disabled a required service.

Anyways, thanks for the reply.

Running SQL Express 2005 on WINXP PRO SP2.

|||

Does the service account which SQL Server is running under have write permissions to the disk?

According to the bulk insert error message, it does (your message says the file exists), but you say in the next post you're deleting it?

|||

Ah your the bomb.

SQLExpress service was running under network authority not local system account. I switched it to local system and now the bulk import with errorfile switch is working from sqlcmd. Write permissions or something, anyways I know where to look now, thanks!

Tuesday, March 20, 2012

Problem for Calling A Stored Procedure, Please help.

I am writing a Stored Procedure for other server (using C++ to receive the output values) as below, where @.Total ， @.balance， @.A are output values
create proc [MaxTime]

@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30)

begin

declare @.balancefloat

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

declare @.A float

declare @.Total float

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = 'select @.A = A FROM' + @.table

+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)

+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +

' ORDER BY LEN(code) DESC'

exec sp_executesql @.sql,N'@.Afloat output',@.Aoutput

set @.Total = @.balance / @.A + @.freetotal

end

return

but the server got nothing, then i wrote another Stored Procedure below:

create proc [MaxTime]

(@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30),

@.Total float output,

@.balance float output,

@.A float output)

--here is the only modified i made

begin

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = \'select @.A = A FROM\' + @.table

+ \' WHERE LEFT(code, 1) = \' + LEFT(@.incomingcode, 1)

+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @.incomingcode+ \') = 1\' +

\' ORDER BY LEN(code) DESC\'

exec sp_executesql

--@.sql,N\'@.Afloat output\',@.Aoutput --im not sure about this line

set @.Total = @.balance / @.A + @.freetotal

end

return

system error message：

Msg 170, Level 15, State 1, Procedure MaxTime, Line 11

Line 11: Incorrect syntax near \'@.Total \'.

Msg 137, Level 15, State 1, Procedure MaxTime,, Line 21

Must declare the variable \'@.Total \'.

Please help, appreciated

Hi,xxd

If you wanna get data from database without using dataset.
maybe you can try function.

By the below case, the SQL substring can't add the local varity into the sentance.
It was because the "exec sp_executesql " will be the other Transcation.

|||Hi, HutTsai:

thanks for ur reply, as u mentioned about dataset, did you mean in SQL? or on the other server side?

and for the 'exec sp_executesql', it was only for excuting the dynamic@.sql to get those values that i need to calculate in set @.Total = @.balance / @.A + @.freetotal.

Thanks
|||

Hello xxd

as the requirement,I think you need to clac the value @.total return for Client that call sp [MAXTIME]

this is the sample code I wrote, try it. modi by your sample code.
if you need the detail for this. Let me know. :)

why "cursor"?
my target was get the return value From dynamic-SQLstring,
using the cursor delcare in global.
then fetch its content for out return value.

it's a better method.

Reference: Stored Procedure,Cursor;

Cheers,
Hunt

/* Sample Code by Hunt Begin*/

create proc [MaxTime]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output)
--here is the only modified i made

as
begin
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.SQL nvarchar(4000)
select @.balance = balance, @.table = table, @.freetotal = freetotal
from info where number = @.number

set @.sql =
' Declare tmpcur cursor for '
' select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'

exec (@.sql)
open tmpcur;
Fetch Next From tmpcur into @.A;
close tmpcur;
Deallocate tmpcur;

select @.total = @.balance / @.A + @.freetotal

/*Sample Code by Hunt End*/

|||Thanks Hunt:

1st, there are something wrong for this part below:
' Declare tmpcur cursor for '
' select @.a = a FROM'
please advise me that how to modify it, coz this is my 1st time to see write cursor this way :)
and for my case, i don't really need to use cursor, coz the ' select @.A = A FROM' + @.table
+ ' WHERE LEFT(code, 1) = ' + LEFT(@.incomingcode, 1)
+ ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @.incomingcode+ ') = 1' +
' ORDER BY LEN(code) DESC'
will only back me one set of data. anyway.

based on your code, i modified mine as below:
alter proc [MaxTime]

(@.number varchar(30),

@.numbera varchar(30),

@.numberb varchar(30),

@.Total float output,

@.balance float output)

begin

declare @.table varchar(20)

declare @.freetotal varchar(20)

declare @.SQL nvarchar(4000)

select @.balance = balance, @.table = table, @.freetotal = freetotal

from info where number = @.number
SELECT @.SQL = \'select @.A = A FROM\' + @.table

+ \' WHERE LEFT(code, 1) = \' + LEFT(@.incomingcode, 1)

+ \' AND CHARINDEX(LTRIM(RTRIM(code)), \' + @.incomingcode+ \') = 1\' +

\' ORDER BY LEN(code) DESC\'

exec sp_executesql

--don't know where did i get those \ from

set @.Total = @.balance / @.A + @.freetotal

end

return @.Total
return @.balance

however, got error message like:
Msg 201, Level 16, State 4, Procedure MaxTime, Line 0
Procedure 'MaxTime' expects parameter '@.Total', which was not supplied.

but one step closed i think

Cheers.
|||

ok,the important checkpoint on sys.procedure -> "exec sp_executesql"
check with my sample.
you'll get what you want.
Hint: as you set output varity with value,you shouldn't return any value. it's useless.
Reference with Books Online "sp_executesql"
Cheers,
Hunt
alter proc [sp_test1]
(@.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
@.Total float output,
@.balance float output)
as
declare @.table varchar(20)
declare @.freetotal varchar(20)
declare @.A float
declare @.SQL nvarchar(4000);
declare @.SQLparm nvarchar(500);

set @.table ='car'
set @.balance = 3.0
set @.freetotal = 2.0
--the section below will be the most important.
set @.SQLparm = N'@.A float output'
select @.sql = ' select @.A = 2.0 FROM ' + @.table
exec sp_executesql @.sql,@.SQLparm ,@.A output
set @.Total = @.balance / @.A + @.freetotal
go

declare @.tot float
declare @.free float
exec [sp_test1] '1','2','3',@.tot output,@.free output
print ''
print @.tot
print @.free
go

|||thank you for writing all of those code.

however, why i need those below?
declare @.tot float
declare @.free float
exec [sp_test1] '1','2','3',@.tot output,@.free output

and i think it should be like exec [sp_test1] '1','2','3', @.Total output, @.balance output

otherwise, in fact, there are two clients are calling this procedure 1 of them was alright for getting the outputs, the other one doesn't, it is an application, in this application all i can do is to identify three input parameters,
which are @.number varchar(30),
@.numbera varchar(30),
@.numberb varchar(30),
and there are no more space for @.Total output and @.balance output.

so that when this application pass the exec command to sql server, it would be like exec [sp_test1] '1','2','3' rather than exec [sp_test1] '1','2','3', @.Total output, @.balance output

hope i explained clearly.

Cheers
|||

this thread got a little problem,i can't post any word on it.

try put default value behind the varity.
@.total float =0 out,@.balance float =0 out

in this sample,you can call sp with no output param.
check it.

Cheer.

|||hi thanks,
i'll try it tomorrow, let you know then|||did you mean that
exec [sp_test1] '1','2','3', @.Total float = 0 output, @.balance float = 0 output

?
or exec [sp_test1] '1','2','3','0','0'

it works fine when i am using exec [sp_test1] '1','2','3','0','0' for outputing values for the server (C++) or i do not even need to use the output value, it also works. However i still can not set the @.total float =0 out,@.balance float =0 out for the application case, and that application tells me that it did not get anything.

so is there any better way to solve it out?

many thx
|||

You have to put the default value when you create stored procedure.
as below

Create Proc [MAXTime] (@.numbera varchar(20),@.numberb varchar(20),@.numberc varchar(20),
@.total float =0 out,@.balance float =0 out);

after you alter the sp,you can call the proc by
exec [MAXTime] '1','2','3'
or
exec [MAXTime] '1','2','3',@.total out,@.balance out

try it.

|||hi HuntTsai:

thanks for that, by now i do not thin k the output will solve my problem, after all i realised that there are 3 types of output function of sproc:

1select @.something
2@.something output
3return@.something and return(0)

all of above are the same(of course not) or for some special using?
thanks

|||

I think I don't know what's your original requirement(or question).
maybe it could be describe more detail. On basiclly, the output Question seems like be sloved.

anyway,when we use stored procedure, it was defined for regular process.
and the return types that you said in last post were the normal method.
(I add the 4th as fire_trigger).

1. select @.something
2. @.something output
3. return@.something and return(0)
4. sometimes we also set it up as another type of trigger.

Best Regrads. :)

|||Hi HuntTsai:

Thanks a lot.

Problem Executing Stored Procedures

We have an application that uses SQL-DMO to get a directory/file list for
the server by calling extended stored procedures, xp_availablemedia and
xp_cmdshell.
The app can successfully navigate the directories on systems running MSDE on
Window 2000 Professional, however, it fails to do so on systems running
Windows XP Professional.
There are no differences in the environment the application runs in other
than the version of Windows. Some of the characteristics of the
installation are: The application and MSDE are installed using the same
scripts on both versions of Windows. The systems where the app is installed
are running as part of a workgroup rather than a domain. We have run
svrnetcn.exe and verified that named pipes and TCP/IP are both enabled. To
confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
the command line. Also, we can successfully execute the stored procedures
using SQL statements so it appears that the problems are related to SQLDMO.
Is there something additional or different that needs to be done on XP than
on 2000? Does anyone have any suggestions about how to solve this problem?
Thanks,
Tom
hi Tom,
"news.microsoft.com" <tom@.nospam.waspbarcode.com> ha scritto nel messaggio
news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
> We have an application that uses SQL-DMO to get a directory/file list for
> the server by calling extended stored procedures, xp_availablemedia and
> xp_cmdshell.
> The app can successfully navigate the directories on systems running MSDE
on
> Window 2000 Professional, however, it fails to do so on systems running
> Windows XP Professional.
> There are no differences in the environment the application runs in other
> than the version of Windows. Some of the characteristics of the
> installation are: The application and MSDE are installed using the same
> scripts on both versions of Windows. The systems where the app is
installed
> are running as part of a workgroup rather than a domain. We have run
> svrnetcn.exe and verified that named pipes and TCP/IP are both enabled.
To
> confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
> the command line. Also, we can successfully execute the stored procedures
> using SQL statements so it appears that the problems are related to
SQLDMO.
> Is there something additional or different that needs to be done on XP
than
> on 2000? Does anyone have any suggestions about how to solve this
problem?
I do currently use SQL-DMO with success both on Win2k, WinXP pro and Win2003
server std...
I never had the need to manually register this somponent when installing
MSDE, and both procedures you are mentioning are run with success...
just the usual caveat... does the account running SQL Server and SQL Server
Agent have the right privileges?
did you try some simple SQL-DMO code to test it?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||My guess is that it has nothing to do with SQL-DMO, but is instead a
permissions issue. xp_cmdshell has some rather strict permission issues when
being invoked (its all in the BOL). By default, XP has a tighter security
setup than Win2k, so this may be part of the root of your problem. The fact
that you are running in a Workgroup network may also be a factor, since its
security model is much different than a domain network. Have you tried
running the profiler against the system when running your application
against MSDE on XP?
Jim
"news.microsoft.com" <tom@.nospam.waspbarcode.com> wrote in message
news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
> We have an application that uses SQL-DMO to get a directory/file list for
> the server by calling extended stored procedures, xp_availablemedia and
> xp_cmdshell.
> The app can successfully navigate the directories on systems running MSDE
on
> Window 2000 Professional, however, it fails to do so on systems running
> Windows XP Professional.
> There are no differences in the environment the application runs in other
> than the version of Windows. Some of the characteristics of the
> installation are: The application and MSDE are installed using the same
> scripts on both versions of Windows. The systems where the app is
installed
> are running as part of a workgroup rather than a domain. We have run
> svrnetcn.exe and verified that named pipes and TCP/IP are both enabled.
To
> confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
> the command line. Also, we can successfully execute the stored procedures
> using SQL statements so it appears that the problems are related to
SQLDMO.
> Is there something additional or different that needs to be done on XP
than
> on 2000? Does anyone have any suggestions about how to solve this
problem?
> Thanks,
> Tom
>
|||Andrea,
Both SQL Server and Agent are running under the Local System account so they
should have enough privileges.
The same application that is having problems with SQL-DMO also uses SQL-DMO
to do backups and restores and doesn't have any problems.
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:c43nsp$2cuegu$1@.ID-207518.news.uni-berlin.de...
> hi Tom,
> "news.microsoft.com" <tom@.nospam.waspbarcode.com> ha scritto nel messaggio
> news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
for
MSDE
> on
other
> installed
> To
from
procedures
> SQLDMO.
> than
> problem?
> I do currently use SQL-DMO with success both on Win2k, WinXP pro and
Win2003
> server std...
> I never had the need to manually register this somponent when installing
> MSDE, and both procedures you are mentioning are run with success...
> just the usual caveat... does the account running SQL Server and SQL
Server
> Agent have the right privileges?
> did you try some simple SQL-DMO code to test it?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Jim,
Unfortunately, we have no control over the environment where the application
is being run, but, it is usually installed on stand-alone systems or
pier-to-pier networks that are not part of a domain.
Since we can execute the extended stored procedures in SQL commands, we are
in the process of converting the application so it doesn't use SQL-DMO
except for the backup and restore.
Tom
"J Young" <thorium48@.hotmail.com> wrote in message
news:OTbnG4OFEHA.2052@.TK2MSFTNGP11.phx.gbl...
> My guess is that it has nothing to do with SQL-DMO, but is instead a
> permissions issue. xp_cmdshell has some rather strict permission issues
when
> being invoked (its all in the BOL). By default, XP has a tighter security
> setup than Win2k, so this may be part of the root of your problem. The
fact
> that you are running in a Workgroup network may also be a factor, since
its
> security model is much different than a domain network. Have you tried
> running the profiler against the system when running your application
> against MSDE on XP?
> Jim
> "news.microsoft.com" <tom@.nospam.waspbarcode.com> wrote in message
> news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
for
MSDE
> on
other
> installed
> To
from
procedures
> SQLDMO.
> than
> problem?
>

Problem executing stored procedure to update all rows of a table

Hi,
I got a stored procedure which interacts with the pubs database. The
following is the code for the stored procedure:
CREATE PROCEDURE usp_UpdatedPrices_para
@.Type char(12)= '%',
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type
Now, if I execute the above stored procedure(from QA) in the following manne
r:
exec usp_UpdatedPrices_para 'Business' , 1
all four rows of the price field of titles table corresponding to the
'Business' type gets updated.
Now I need to execute the stored procedure so that all the rows get the
updates for all the various types. Since I have a default value for the type
as '%', I am
executing the stored procedure as
exec usp_UpdatedPrices_para , 1
to which I am getting a syntax error as the following:
Line 1: Incorrect syntax near ','
If anybody could suggest me something about the error, it would be helpful.
Thanks.Hello, Jack
You can call the procedure like this:
EXEC usp_UpdatedPrices_para DEFAULT, 1
or:
EXEC usp_UpdatedPrices_para @.Percent=1
However, this will not get you the expected result, because your
condition is "Type='%'" (not "Type LIKE '%'"). I suggest that you omit
the default for the @.Type parameter (leave it to be NULL) and use a
condition like this:
WHERE Type=@.Type OR @.Type IS NULL
Razvan

Problem executing stored procedure to update all rows of a tab

Thanks Razvan for your help. As per your advise I have changed the stored
procedure as follows:
CREATE PROCEDURE usp_UpdatedPrices_para1
@.Type char(12),
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type or @.Type IS NULL
Now I am trying to execute the stored procedure as:
exec usp_UpdatedPrices_para1 @.percent = 10
To the above command I am getting the following error message:
Procedure 'usp_UpdatedPrices_para1' expects parameter '@.Type', which was not
supplied.
Do you have any further ideas for resolution. Thanks.
"Razvan Socol" wrote:

> Hello, Jack
> You can call the procedure like this:
> EXEC usp_UpdatedPrices_para DEFAULT, 1
> or:
> EXEC usp_UpdatedPrices_para @.Percent=1
> However, this will not get you the expected result, because your
> condition is "Type='%'" (not "Type LIKE '%'"). I suggest that you omit
> the default for the @.Type parameter (leave it to be NULL) and use a
> condition like this:
> WHERE Type=@.Type OR @.Type IS NULL
> Razvan
>When passing parameters to a stored procedure using the @.variable=value
syntax, you must explicity set a valure for all input parameters that don't
have a default value assigned in the procedure defenition; even if you want
it to be NULL.
Try
EXEC usp_UpdatedPrices_para1 @.Type=NULL, @.Percent=1
Alternatively, you could define the procedure to use a default of NULL for
the variable @.Type
CREATE PROCEDURE usp_UpdatedPrices_para2
@.Type char(12)=NULL,
@.Percent Money
AS
UPDATE titles
SET Price = Price * (1 + @.percent/100)
WHERE Type = @.Type or @.Type IS NULL
And then execute as
EXEC sp_UpdatedPrices_para2 @.Percent=1
"Jack" wrote:
> Thanks Razvan for your help. As per your advise I have changed the stored
> procedure as follows:
> CREATE PROCEDURE usp_UpdatedPrices_para1
> @.Type char(12),
> @.Percent Money
> AS
> UPDATE titles
> SET Price = Price * (1 + @.percent/100)
> WHERE Type = @.Type or @.Type IS NULL
> Now I am trying to execute the stored procedure as:
> exec usp_UpdatedPrices_para1 @.percent = 10
> To the above command I am getting the following error message:
> Procedure 'usp_UpdatedPrices_para1' expects parameter '@.Type', which was n
ot
> supplied.
> Do you have any further ideas for resolution. Thanks.
> "Razvan Socol" wrote:
>|||Thanks a lot Mark for your generous help. I tried executing both the
procedures and both worked great. Best Regards.
"Mark Williams" wrote:
> When passing parameters to a stored procedure using the @.variable=value
> syntax, you must explicity set a valure for all input parameters that don'
t
> have a default value assigned in the procedure defenition; even if you wan
t
> it to be NULL.
> Try
> EXEC usp_UpdatedPrices_para1 @.Type=NULL, @.Percent=1
> Alternatively, you could define the procedure to use a default of NULL for
> the variable @.Type
> CREATE PROCEDURE usp_UpdatedPrices_para2
> @.Type char(12)=NULL,
> @.Percent Money
> AS
> UPDATE titles
> SET Price = Price * (1 + @.percent/100)
> WHERE Type = @.Type or @.Type IS NULL
> And then execute as
> EXEC sp_UpdatedPrices_para2 @.Percent=1
>
> "Jack" wrote:
>

Problem executing Stored Procedure from ASP

Hi all,
I must be overlooking the obvious (apologies) but can't seem to figure out
why I'm unable to execute the following (where both parameters are 'int'
datatype:
#######
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#######
The error that I receive is: "Microsoft OLE DB Provider for SQL Server
error '80040e14'. Syntax error or access violation."
The procedure (which simply calls a UDF, below) executes successfully in QA
when I set the parameter values; maybe I need to 'unlearn' the quotes
syntax from Access, or...?
#######
ALTER FUNCTION dbo.udf_User_MessagesFunction
(@.UID int, @.Target int)
RETURNS TABLE
AS
RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
FROM dbo.vw_User_MessagesView
WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND (MessageID
NOT IN
(SELECT DISTINCT MessageID
FROM tblArchivedMessages
WHERE UserID IN
(SELECT DISTINCT
UserID
FROM
tblArchivedMessages
WHERE UserID
= @.UID))) )
#######
Suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.comIf the values are ints this should work for you, i think one of the session
parameters is NULL, try to print or Response.write the Commandtext which is
concatenated. If you cant do it, run the profiler to see what kind of
values are sent to the server. There must be an error in the commandtext
like "SP_proc ,1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> schrieb im
Newsbeitrag news:1b8688508a3d4e9a947810f1e57f0ecc@.SQ
droptable.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com|||Also, adCmdStoredProc is for designating stored procedures as the source of
the CommandText. If you are adding parameters after the name of your SP,
this is no longer true and you must use adCmdText instead.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
"The Gekkster via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:1b8688508a3d4e9a947810f1e57f0ecc@.SQ
droptable.com...
> Hi all,
> I must be overlooking the obvious (apologies) but can't seem to figure out
> why I'm unable to execute the following (where both parameters are 'int'
> datatype:
> #######
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #######
> The error that I receive is: "Microsoft OLE DB Provider for SQL Server
> error '80040e14'. Syntax error or access violation."
> The procedure (which simply calls a UDF, below) executes successfully in
> QA
> when I set the parameter values; maybe I need to 'unlearn' the quotes
> syntax from Access, or...?
> #######
> ALTER FUNCTION dbo.udf_User_MessagesFunction
> (@.UID int, @.Target int)
> RETURNS TABLE
> AS
> RETURN ( SELECT MessageID, PostDate, Target, Subject, Content
> FROM dbo.vw_User_MessagesView
> WHERE (Target = @.Target) AND (Expiration >= CurrentDate) AND
> (MessageID
> NOT IN
> (SELECT DISTINCT MessageID
> FROM tblArchivedMessages
> WHERE UserID IN
> (SELECT DISTINCT
> UserID
> FROM
> tblArchivedMessages
> WHERE UserID
> = @.UID))) )
> #######
> Suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com|||Thanks for the input. I'm finding that trying to execute the SP and pass
the parameters from ASP like this simply doesn't work (with any SPs):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
Session("UserGroupID") & ""
objCommand.CommandType = adCmdStoredProc
set objRS = objCommand.Execute
set objCommand = Nothing
#####
So for further testing I re-wrote it like this and it works without any
problem (using 'Parameters.Refresh' here for the sake of simplicity):
#####
set objCommand = server.CreateObject("ADODB.command")
objCommand.ActiveConnection = objConn
objCommand.CommandText = "usp_User_Messages"
objCommand.CommandType = adCmdStoredProc
objCommand.Parameters.Refresh
objCommand.Parameters(1).Value = Session("UserID")
objCommand.Parameters(2).Value = Session("UserGroupID")
set objRS = objCommand.Execute
set objCommand = Nothing
#####
What has me really puzzled is that the original code worked when using an
Access '02 back end; but problems since the change to SQL Server 2000 SP3.
And it's not just this one, but with all that pass a parameter.
Is there something I've done wrong with the syntax in some way, or with
quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
quite the same between Access and SQL Server?
All thoughts welcome. Thanks.
Message posted via http://www.droptable.com|||This link describes using parameterized queries.
1191519.html" target="_blank">http://www.experts-exchange.com/Pro...>
1191519.html
You're not actually using adCmdStoredProc format as specified for SQL
Server, since you're appending the parameter values to the end of the
CommandText. As Sylvain pointed out you're using adCmdText format.
adCmdStoredProc format with Parameters is safer in general. For instance,
imagine the following scenario:
Session("UserID") = "10"
Session("UserGroupID") = "1; SELECT * FROM master.dbo.syscomments;"
This is SQL Injection, and is particularly an issue when user input is
passed to a SQL command.
Access Jet and SQL Server have some differences in the way in which they
handle commands; it looks like Jet is more 'forgiving' in this instance.
"The Gekkster via droptable.com" <forum@.droptable.com> wrote in message
news:ad4750a2b6684595b0e6bdf10483b7f3@.SQ
droptable.com...
> Thanks for the input. I'm finding that trying to execute the SP and pass
> the parameters from ASP like this simply doesn't work (with any SPs):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages " & Session("UserID") & "," &
> Session("UserGroupID") & ""
> objCommand.CommandType = adCmdStoredProc
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> So for further testing I re-wrote it like this and it works without any
> problem (using 'Parameters.Refresh' here for the sake of simplicity):
> #####
> set objCommand = server.CreateObject("ADODB.command")
> objCommand.ActiveConnection = objConn
> objCommand.CommandText = "usp_User_Messages"
> objCommand.CommandType = adCmdStoredProc
> objCommand.Parameters.Refresh
> objCommand.Parameters(1).Value = Session("UserID")
> objCommand.Parameters(2).Value = Session("UserGroupID")
> set objRS = objCommand.Execute
> set objCommand = Nothing
> #####
> What has me really puzzled is that the original code worked when using an
> Access '02 back end; but problems since the change to SQL Server 2000 SP3.
> And it's not just this one, but with all that pass a parameter.
> Is there something I've done wrong with the syntax in some way, or with
> quotes, or...? Or is this just a case of things (i.e. ASP, ADO) being not
> quite the same between Access and SQL Server?
> All thoughts welcome. Thanks.
> --
> Message posted via http://www.droptable.com

Problem executing stored procedure from asp

We have an ASP page which executes a stored procedure this works fine on
windows 2000 server/SQL 2000 server. We are trying to use the same page
locally on WinXP SP2/MSDE 2000A and we seem to be experiencing a strange
problem. When run locally the asp complains that it cannot find the stored
procedure. We have run profiler on both Win2000 and locally and noticed that
the commands which are executed are very different.See below
Win 2000 Server/SQL Server 2000
RPC:Completed exec spShowPhysicalBlockSeats 'TEST', 'E16' Microsoft(R)
Windows (R) 2000 Operating System sa 0 2104 0 173 1304 71 2004-11-15
11:37:49.787
WinXP/MSDE2000A
SQL:BatchCompleted spShowPhysicalBlockSeats Microsoft Windows Operating
System sa 0 4 0 0 3840 55 2004-11-15 11:36:25.927
RPC:Completed exec spShowPhysicalBlockSeats Microsoft Windows Operating
System sa 0 4 0 0 3840 55 2004-11-15 11:36:25.927
SQL:BatchCompleted select * from spShowPhysicalBlockSeats Microsoft
Windows Operating System sa 0 4 0 16 3840 55 2004-11-15 11:36:25.927
The asp page istelf is unchanged apart from the connection string, as you
can imagine we are a little confused about why the execution is so
different, and why the WinXP version seems to execute it 3 times in slightly
different ways.
Any help or advice would be greatly received.
Cheers
Andy
Well I can account for two differences: (a) on Windows 2000, you didn't
capture the SQL:BatchCompleted event in profiler, and (b) you called the
stored procedure with different parameters.
Can you show us the code for the stored procedure, the ASP Code that is
calling it, and whether you see any major differences aside from what your
*different* traces show using *different* SP calls?
http://www.aspfaq.com/
(Reverse address to reply.)
"Andy Kerner" <andrewkerner@.hotmail.com> wrote in message
news:#bSUxhwyEHA.3120@.TK2MSFTNGP12.phx.gbl...
> We have an ASP page which executes a stored procedure this works fine on
> windows 2000 server/SQL 2000 server. We are trying to use the same page
> locally on WinXP SP2/MSDE 2000A and we seem to be experiencing a strange
> problem. When run locally the asp complains that it cannot find the stored
> procedure. We have run profiler on both Win2000 and locally and noticed
that
> the commands which are executed are very different.See below
> Win 2000 Server/SQL Server 2000
> --
> RPC:Completed exec spShowPhysicalBlockSeats 'TEST', 'E16' Microsoft(R)
> Windows (R) 2000 Operating System sa 0 2104 0 173 1304 71 2004-11-15
> 11:37:49.787
> WinXP/MSDE2000A
> --
> SQL:BatchCompleted spShowPhysicalBlockSeats Microsoft Windows Operating
> System sa 0 4 0 0 3840 55 2004-11-15 11:36:25.927
> RPC:Completed exec spShowPhysicalBlockSeats Microsoft Windows Operating
> System sa 0 4 0 0 3840 55 2004-11-15 11:36:25.927
> SQL:BatchCompleted select * from spShowPhysicalBlockSeats Microsoft
> Windows Operating System sa 0 4 0 16 3840 55 2004-11-15 11:36:25.927
> The asp page istelf is unchanged apart from the connection string, as you
> can imagine we are a little confused about why the execution is so
> different, and why the WinXP version seems to execute it 3 times in
slightly
> different ways.
> Any help or advice would be greatly received.
> Cheers
> Andy
>

Wednesday, March 28, 2012

problem in executing extended stored procedure

problem in executing extended stored procedure

Problem in Displaying NTEXT field from database?

Problem in Dataset

Monday, March 26, 2012

Problem In Connecting Oracle9i SP with CR11.0

Friday, March 23, 2012

problem in comparing date

Problem in a QUERY with COUNT

Problem in a dynamic stored procedurehelp

Wednesday, March 21, 2012

problem getting result set through a stored procedure call using VB.

Problem getting execution status of a SQL Agent Job

Problem generating error file using BULK INSERT or BCP thru xp_cmdshell.

Tuesday, March 20, 2012

Problem for Calling A Stored Procedure, Please help.

Problem Executing Stored Procedures

Problem executing stored procedure to update all rows of a table

Problem executing stored procedure to update all rows of a tab

Problem executing Stored Procedure from ASP

Problem executing stored procedure from asp

connecting to SSAS

Blog Archive

About Me