Problem in getting SUM of Running Total Field

Hi buddy...

i m creating a report as below

--------------------------
Report Head ==>>> | Field1 |
--------------------------
Field2 Field3 Field4 Field5 Field6 Field7
--------------------------

Detail Section Here.....

--------------------------
sum(Field7)
--------------------------

Field7 is my Running Formula Field. i.e. Field7= (Field1 + Field5 - Field6)
nd thats working well.

Working ::

i m generating a report that nd gets the sum of Field7 as far as there is some record in details.

but when there is no record in the details section Sum of Field7 shows me nothing,
where as i am required to display the value of Field1 in it if there is nothing in the details section.

He who give solution will be considered a well qualified person.

with thanks nd admiration in advance

Silly StarCreate another formula with this :

If IsNull(Field7) then
Field1
else
Field7|||Hey KristyW

You Are Real Genius

Thats Worked Perfectly.

Thank u So much Sir but there r more Problems regarding Crystal Report
May i ask them

Silly Star|||u r a real "Silly Star"|||Hi ...

i m creating a report as below

--------------------------
GroupHeader ==>>> | Field1 |
--------------------------
Field2 Field3 Field4 Field5 Field6 Field7
--------------------------

Detail Section Here.....

--------------------------
GroupFooter==>> sum(Field7)
--------------------------

Field7 is the Running Formula Field. i.e. Field7= (Field1 + Field5 - Field6)
and i reset this formula on GroupChange
nd thats working well.

but when a group is changed it does not work properly (Meaning the desired result is not displayed)

He who give solution will be considered a well qualified person.

with thanks nd admiration in advance

Silly Star|||hai silly star

Edit the running field as

In the summary part give field 7 as the field to summarize and typeof summary is sum

In the evaluate column check the 'for each record' and

In the reset column check the 'on chage of group'

try this and it will work for you i believe

bye

viji

Problem in deployment on SSAS 64 bit with Oracle 10g 32 bit client (as datasource)

Hi,

I installed SQL Server 2005 64 bit and have Oracle 10g 32 bit client,
I am creating a cube based on Oracle datasource by connecting Oracle 10g 32bit client.

when I build and process cube it Done successfully but when I deploye it to server then it gives following error.

Error 1 Errors in the high-level relational engine. The following exception
occurred while the managed IDbConnection interface was being used: Attempt
to load Oracle client libraries threw BadImageFormatException. This problem
will occur when running in 64 bit mode with the 32 bit Oracle client
components installed.. 0 0

Error 2 Errors in the high-level relational engine. A connection could not
be made to the data source with the DataSourceID of 'Oracle DW Call Detail',
Name of 'Oracle DW Call Detail'. 0 0

any suggestion regarding this ?

Hi Kawish,

Did you solve this prolem? I am having the same problemss as you describe here.

look forward to hearing from you..

Problem in deployment of Crystal 10 with VB6

I've facing problem in creating the package for the application using VB6 and Crystal reports 10. What are the support files needed to be included in the package. I've already included Crviewer.dll,CRAXDRT.dll. But there is no response in the client machine.Whether Crystal Reports 10 need to be installed in client machine?

Please respond!

Thanking you,

Regards,
Rajarajan RI found solution to my deployment problems in the next link -->

http://support.businessobjects.com/communityCS/TechnicalPapers/rdc10_deployment.pdf

Problem in Deploying Analysis service projects using SQL server Business Intelligence Developmen

after creating Analysis service projects using SQL server Business Intelligence Development Studio when the project is deployed it gives following error.........

Error 1 The project could not be deployed to the 'Solversa-201\SQL2005' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

connection to the server problem doesnt come at the time of creating data base,at that time it is accepting server name and displaying the database list...............

even test connection is also sucessful...................

problem is only at the time of deployment of project

Please make sure that the instance name in the Deployment tab refers to your Analysis Services instance and not your SQL Server 2005 database server instance.

Problem in creating reports using BI fields

Hi,

I have created a cube using 3 Dimensions and 1 measure. One of the dimensions is a time dimension. I have also used the ADD BI command to get the Year to Date and Year over year % . This works perfectly with the cube. But when I try and create a report I am unable to display the figures for the above mentioned columns. I get Null data there.

Does anybody have any suggestions as to why this is happening and what the way to overcome this.

Thanks in advance
MNJ

Could you provide more information on how you are creating the report and which columns are returning nulls?

Problem in Creating Trigger Sql Server 2000

Friends,
I have problem to creating a trigger.
In a trigger, i have check, if both inserted and deleted table has
row, then Check containt in both tables are same or not like this
Select Count(*) from inserted i , deleted d where i.cola = d.cola
and i.colb = d.colb
I have done it in some dynamically way, which can work for my all
tables, As code follows.
########
My Problem is, when following trigger execute, its return inserted
and deleted table does not exits.
#######
Update tm_empmastR1
Set empstatus = 'A'
Alter Trigger tr_tm_empmastR1 on tm_empmastR1
For Insert, Update, Delete
As
Declare @.ColName as VarChar(255),
@.Cmd as VarChar(8000)
Declare TCur Cursor For
Select SC.name
From sysobjects SO
Inner join syscolumns SC On SC.id = SO.id
And SO.name = 'tm_empmastR1'
order by colid
Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
Open TCur
Fetch Next From TCur Into @.ColName
While (@.@.Fetch_Status = 0)
Begin
Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
Fetch Next From TCur Into @.ColName
End
Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
Print (@.Cmd)
Exec (@.Cmd)
Close TCur
Deallocate TCur
#####
Although return Sql is runing perfectly.
#####
Thanks in advance for any reply.
Rahul Verma
FutureSoftDynamically executed SQL has its own context and doesn't have access to thes
e tables. I encourage
you to skip the dynamic SQL if at all possible. If not, you can have the out
er code populate a temp
table and then the inner (dynamic) code can access that temp table.
I would use the system tables/catalog views to generate the trigger code, fo
r instance.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rahul" <verma.career@.gmail.com> wrote in message
news:1172035114.793535.273980@.v33g2000cwv.googlegroups.com...
> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmd as VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> And SO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>|||Hi Rahul
"Rahul" wrote:

> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmd as VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> And SO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>
The dynamic SQL is in a different scope to the trigger, therefore the it
doesn't know about the inserted and deleted tables.
You should check @.@.ROWCOUNT as the first statement in the trigger to make
sure that there has been some changes.
All the cursor is doing is allowing you to avoid having to change the
trigger when your tables structure changes, on a mature system it will be
redundant and could potentially impact performance and could even cause a
bottleneck.
John|||On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Rahul
>
>
> "Rahul" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
> The dynamic SQL is in a different scope to the trigger, therefore the it
> doesn't know about the inserted and deleted tables.
> You should check @.@.ROWCOUNT as the first statement in the trigger to make
> sure that there has been some changes.
> All the cursor is doing is allowing you to avoid having to change the
> trigger when your tables structure changes, on a mature system it will be
> redundant and could potentially impact performance and could even cause a
> bottleneck.
> John- Hide quoted text -
> - Show quoted text -
Hi All
Thanks for reply.
Tibor Karaszi
i am not getting your idea,
trigger is called when we call update/insert/delete command
automatically and just after these statements.
So, we have not enough time to create and use a temp table.
If is it possible or any other idea, please mail me, Because we
require to create this type of modification with 100'th triggers, As i
am creating log.
Jhon,
This is right, if table structure change, cursor may be fail, that's
why i am using sysobject and syscolumns tables to getting all columns.
And when i use print statement, the return value of print statement
is absolutely correct and its working.
The @.@.rowcount only return no of rows in last query, its ok but
before this statement, i have to match all these fields in both table.
####
Actually my logic is
if both inserted and deleted tables contain row (ie row is updated)
and changes are actually made (ie changes must be done in any field in
a table) then insert this row in a log file with a particular flag.
else if only inserted table contain row then it insert with different
flag
same as with deleted.
####
if you have any idea to done it, please reply me
i have more than 1000 tables.
Thanks
Rahul|||Hi Rahul
"Rahul" wrote:

> On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>
> Hi All
> Thanks for reply.
> Tibor Karaszi
> i am not getting your idea,
> trigger is called when we call update/insert/delete command
> automatically and just after these statements.
> So, we have not enough time to create and use a temp table.
> If is it possible or any other idea, please mail me, Because we
> require to create this type of modification with 100'th triggers, As i
> am creating log.
>
> Jhon,
> This is right, if table structure change, cursor may be fail, that's
> why i am using sysobject and syscolumns tables to getting all columns.
> And when i use print statement, the return value of print statement
> is absolutely correct and its working.
> The @.@.rowcount only return no of rows in last query, its ok but
> before this statement, i have to match all these fields in both table.
> ####
> Actually my logic is
> if both inserted and deleted tables contain row (ie row is updated)
> and changes are actually made (ie changes must be done in any field in
> a table) then insert this row in a log file with a particular flag.
> else if only inserted table contain row then it insert with different
> flag
> same as with deleted.
> ####
> if you have any idea to done it, please reply me
> i have more than 1000 tables.
> Thanks
> Rahul
I think you have misunderstood both replies. I believe that Tibor is
suggesting that you generate your create trigger statements using the cursor
s
and you will end up with "hardcoded" columns for each table.
The checking of @.@.ROWCOUNT at the very begining of the trigger will mean
that you don't execute the code when not rows are changed, this will cut out
the unnecessary actions of the trigger.
Alter Trigger tr_tm_empmastR1 on tm_empmastR1 For Insert, Update, Delete
As
IF @.@.ROWCOUNT = 0 RETURN
...
John

Problem in Creating Trigger Sql Server 2000

Friends,
I have problem to creating a trigger.
In a trigger, i have check, if both inserted and deleted table has
row, then Check containt in both tables are same or not like this
Select Count(*) from inserted i , deleted d where i.cola = d.cola
and i.colb = d.colb
I have done it in some dynamically way, which can work for my all
tables, As code follows.
########
My Problem is, when following trigger execute, its return inserted
and deleted table does not exits.
#######
Update tm_empmastR1
Set empstatus = 'A'
Alter Trigger tr_tm_empmastR1 on tm_empmastR1
For Insert, Update, Delete
As
Declare @.ColName as VarChar(255),
@.Cmdas VarChar(8000)
Declare TCur Cursor For
Select SC.name
From sysobjects SO
Inner join syscolumns SC On SC.id = SO.id
AndSO.name = 'tm_empmastR1'
order by colid
Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
Open TCur
Fetch Next From TCur Into @.ColName
While (@.@.Fetch_Status = 0)
Begin
Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
Fetch Next From TCur Into @.ColName
End
Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
Print (@.Cmd)
Exec (@.Cmd)
Close TCur
Deallocate TCur
#####
Although return Sql is runing perfectly.
#####
Thanks in advance for any reply.
Rahul Verma
FutureSoft
Hi Rahul
"Rahul" wrote:

> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmdas VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> AndSO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>
The dynamic SQL is in a different scope to the trigger, therefore the it
doesn't know about the inserted and deleted tables.
You should check @.@.ROWCOUNT as the first statement in the trigger to make
sure that there has been some changes.
All the cursor is doing is allowing you to avoid having to change the
trigger when your tables structure changes, on a mature system it will be
redundant and could potentially impact performance and could even cause a
bottleneck.
John
|||On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Rahul
>
>
> "Rahul" wrote:
>
>
>
>
>
>
>
> The dynamic SQL is in a different scope to the trigger, therefore the it
> doesn't know about the inserted and deleted tables.
> You should check @.@.ROWCOUNT as the first statement in the trigger to make
> sure that there has been some changes.
> All the cursor is doing is allowing you to avoid having to change the
> trigger when your tables structure changes, on a mature system it will be
> redundant and could potentially impact performance and could even cause a
> bottleneck.
> John- Hide quoted text -
> - Show quoted text -
Hi All
Thanks for reply.
Tibor Karaszi
i am not getting your idea,
trigger is called when we call update/insert/delete command
automatically and just after these statements.
So, we have not enough time to create and use a temp table.
If is it possible or any other idea, please mail me, Because we
require to create this type of modification with 100'th triggers, As i
am creating log.
Jhon,
This is right, if table structure change, cursor may be fail, that's
why i am using sysobject and syscolumns tables to getting all columns.
And when i use print statement, the return value of print statement
is absolutely correct and its working.
The @.@.rowcount only return no of rows in last query, its ok but
before this statement, i have to match all these fields in both table.
####
Actually my logic is
if both inserted and deleted tables contain row (ie row is updated)
and changes are actually made (ie changes must be done in any field in
a table) then insert this row in a log file with a particular flag.
else if only inserted table contain row then it insert with different
flag
same as with deleted.
####
if you have any idea to done it, please reply me
i have more than 1000 tables.
Thanks
Rahul
|||Hi Rahul
"Rahul" wrote:

> On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>
> Hi All
> Thanks for reply.
> Tibor Karaszi
> i am not getting your idea,
> trigger is called when we call update/insert/delete command
> automatically and just after these statements.
> So, we have not enough time to create and use a temp table.
> If is it possible or any other idea, please mail me, Because we
> require to create this type of modification with 100'th triggers, As i
> am creating log.
>
> Jhon,
> This is right, if table structure change, cursor may be fail, that's
> why i am using sysobject and syscolumns tables to getting all columns.
> And when i use print statement, the return value of print statement
> is absolutely correct and its working.
> The @.@.rowcount only return no of rows in last query, its ok but
> before this statement, i have to match all these fields in both table.
> ####
> Actually my logic is
> if both inserted and deleted tables contain row (ie row is updated)
> and changes are actually made (ie changes must be done in any field in
> a table) then insert this row in a log file with a particular flag.
> else if only inserted table contain row then it insert with different
> flag
> same as with deleted.
> ####
> if you have any idea to done it, please reply me
> i have more than 1000 tables.
> Thanks
> Rahul
I think you have misunderstood both replies. I believe that Tibor is
suggesting that you generate your create trigger statements using the cursors
and you will end up with "hardcoded" columns for each table.
The checking of @.@.ROWCOUNT at the very begining of the trigger will mean
that you don't execute the code when not rows are changed, this will cut out
the unnecessary actions of the trigger.
Alter Trigger tr_tm_empmastR1 on tm_empmastR1 For Insert, Update, Delete
As
IF @.@.ROWCOUNT = 0 RETURN
...
John

Problem in Creating Trigger Sql Server 2000

Friends,
I have problem to creating a trigger.
In a trigger, i have check, if both inserted and deleted table has
row, then Check containt in both tables are same or not like this
Select Count(*) from inserted i , deleted d where i.cola = d.cola
and i.colb = d.colb
I have done it in some dynamically way, which can work for my all
tables, As code follows.
########
My Problem is, when following trigger execute, its return inserted
and deleted table does not exits.
#######
Update tm_empmastR1
Set empstatus = 'A'
Alter Trigger tr_tm_empmastR1 on tm_empmastR1
For Insert, Update, Delete
As
Declare @.ColName as VarChar(255),
@.Cmd as VarChar(8000)
Declare TCur Cursor For
Select SC.name
From sysobjects SO
Inner join syscolumns SC On SC.id = SO.id
And SO.name = 'tm_empmastR1'
order by colid
Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
Open TCur
Fetch Next From TCur Into @.ColName
While (@.@.Fetch_Status = 0)
Begin
Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
Fetch Next From TCur Into @.ColName
End
Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
Print (@.Cmd)
Exec (@.Cmd)
Close TCur
Deallocate TCur
#####
Although return Sql is runing perfectly.
#####
Thanks in advance for any reply.
Rahul Verma
FutureSoftDynamically executed SQL has its own context and doesn't have access to these tables. I encourage
you to skip the dynamic SQL if at all possible. If not, you can have the outer code populate a temp
table and then the inner (dynamic) code can access that temp table.
I would use the system tables/catalog views to generate the trigger code, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rahul" <verma.career@.gmail.com> wrote in message
news:1172035114.793535.273980@.v33g2000cwv.googlegroups.com...
> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmd as VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> And SO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>|||Hi Rahul
"Rahul" wrote:
> Friends,
> I have problem to creating a trigger.
> In a trigger, i have check, if both inserted and deleted table has
> row, then Check containt in both tables are same or not like this
> Select Count(*) from inserted i , deleted d where i.cola = d.cola
> and i.colb = d.colb
> I have done it in some dynamically way, which can work for my all
> tables, As code follows.
> ########
> My Problem is, when following trigger execute, its return inserted
> and deleted table does not exits.
> #######
>
> Update tm_empmastR1
> Set empstatus = 'A'
> Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> For Insert, Update, Delete
> As
> Declare @.ColName as VarChar(255),
> @.Cmd as VarChar(8000)
> Declare TCur Cursor For
> Select SC.name
> From sysobjects SO
> Inner join syscolumns SC On SC.id = SO.id
> And SO.name = 'tm_empmastR1'
> order by colid
> Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> Open TCur
> Fetch Next From TCur Into @.ColName
> While (@.@.Fetch_Status = 0)
> Begin
> Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> Fetch Next From TCur Into @.ColName
> End
> Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> Print (@.Cmd)
> Exec (@.Cmd)
> Close TCur
> Deallocate TCur
>
> #####
> Although return Sql is runing perfectly.
> #####
>
> Thanks in advance for any reply.
> Rahul Verma
> FutureSoft
>
The dynamic SQL is in a different scope to the trigger, therefore the it
doesn't know about the inserted and deleted tables.
You should check @.@.ROWCOUNT as the first statement in the trigger to make
sure that there has been some changes.
All the cursor is doing is allowing you to avoid having to change the
trigger when your tables structure changes, on a mature system it will be
redundant and could potentially impact performance and could even cause a
bottleneck.
John|||On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Rahul
>
>
> "Rahul" wrote:
> > Friends,
> > I have problem to creating a trigger.
> > In a trigger, i have check, if both inserted and deleted table has
> > row, then Check containt in both tables are same or not like this
> > Select Count(*) from inserted i , deleted d where i.cola = d.cola
> > and i.colb = d.colb
> > I have done it in some dynamically way, which can work for my all
> > tables, As code follows.
> > ########
> > My Problem is, when following trigger execute, its return inserted
> > and deleted table does not exits.
> > #######
> > Update tm_empmastR1
> > Set empstatus = 'A'
> > Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> > For Insert, Update, Delete
> > As
> > Declare @.ColName as VarChar(255),
> > @.Cmd as VarChar(8000)
> > Declare TCur Cursor For
> > Select SC.name
> > From sysobjects SO
> > Inner join syscolumns SC On SC.id = SO.id
> > And SO.name = 'tm_empmastR1'
> > order by colid
> > Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> > Open TCur
> > Fetch Next From TCur Into @.ColName
> > While (@.@.Fetch_Status = 0)
> > Begin
> > Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> > Fetch Next From TCur Into @.ColName
> > End
> > Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> > Print (@.Cmd)
> > Exec (@.Cmd)
> > Close TCur
> > Deallocate TCur
> > #####
> > Although return Sql is runing perfectly.
> > #####
> > Thanks in advance for any reply.
> > Rahul Verma
> > FutureSoft
> The dynamic SQL is in a different scope to the trigger, therefore the it
> doesn't know about the inserted and deleted tables.
> You should check @.@.ROWCOUNT as the first statement in the trigger to make
> sure that there has been some changes.
> All the cursor is doing is allowing you to avoid having to change the
> trigger when your tables structure changes, on a mature system it will be
> redundant and could potentially impact performance and could even cause a
> bottleneck.
> John- Hide quoted text -
> - Show quoted text -
Hi All
Thanks for reply.
Tibor Karaszi
i am not getting your idea,
trigger is called when we call update/insert/delete command
automatically and just after these statements.
So, we have not enough time to create and use a temp table.
If is it possible or any other idea, please mail me, Because we
require to create this type of modification with 100'th triggers, As i
am creating log.
Jhon,
This is right, if table structure change, cursor may be fail, that's
why i am using sysobject and syscolumns tables to getting all columns.
And when i use print statement, the return value of print statement
is absolutely correct and its working.
The @.@.rowcount only return no of rows in last query, its ok but
before this statement, i have to match all these fields in both table.
####
Actually my logic is
if both inserted and deleted tables contain row (ie row is updated)
and changes are actually made (ie changes must be done in any field in
a table) then insert this row in a log file with a particular flag.
else if only inserted table contain row then it insert with different
flag
same as with deleted.
####
if you have any idea to done it, please reply me
i have more than 1000 tables.
Thanks
Rahul|||Hi Rahul
"Rahul" wrote:
> On Feb 21, 1:17 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi Rahul
> >
> >
> >
> >
> >
> > "Rahul" wrote:
> > > Friends,
> > > I have problem to creating a trigger.
> > > In a trigger, i have check, if both inserted and deleted table has
> > > row, then Check containt in both tables are same or not like this
> >
> > > Select Count(*) from inserted i , deleted d where i.cola = d.cola
> > > and i.colb = d.colb
> >
> > > I have done it in some dynamically way, which can work for my all
> > > tables, As code follows.
> >
> > > ########
> > > My Problem is, when following trigger execute, its return inserted
> > > and deleted table does not exits.
> > > #######
> >
> > > Update tm_empmastR1
> > > Set empstatus = 'A'
> >
> > > Alter Trigger tr_tm_empmastR1 on tm_empmastR1
> > > For Insert, Update, Delete
> > > As
> >
> > > Declare @.ColName as VarChar(255),
> > > @.Cmd as VarChar(8000)
> >
> > > Declare TCur Cursor For
> > > Select SC.name
> > > From sysobjects SO
> > > Inner join syscolumns SC On SC.id = SO.id
> > > And SO.name = 'tm_empmastR1'
> > > order by colid
> >
> > > Select @.Cmd = 'Select Count(*) From inserted I, deleted D Where '
> > > Open TCur
> > > Fetch Next From TCur Into @.ColName
> > > While (@.@.Fetch_Status = 0)
> > > Begin
> > > Set @.Cmd = @.Cmd + 'I.' + @.ColName + ' = D.' + @.ColName + ' And '
> > > Fetch Next From TCur Into @.ColName
> > > End
> >
> > > Set @.Cmd = SubString(@.Cmd, 1, len(@.Cmd) - 4)
> > > Print (@.Cmd)
> > > Exec (@.Cmd)
> >
> > > Close TCur
> > > Deallocate TCur
> >
> > > #####
> > > Although return Sql is runing perfectly.
> > > #####
> >
> > > Thanks in advance for any reply.
> >
> > > Rahul Verma
> > > FutureSoft
> >
> > The dynamic SQL is in a different scope to the trigger, therefore the it
> > doesn't know about the inserted and deleted tables.
> >
> > You should check @.@.ROWCOUNT as the first statement in the trigger to make
> > sure that there has been some changes.
> >
> > All the cursor is doing is allowing you to avoid having to change the
> > trigger when your tables structure changes, on a mature system it will be
> > redundant and could potentially impact performance and could even cause a
> > bottleneck.
> >
> > John- Hide quoted text -
> >
> > - Show quoted text -
>
> Hi All
> Thanks for reply.
> Tibor Karaszi
> i am not getting your idea,
> trigger is called when we call update/insert/delete command
> automatically and just after these statements.
> So, we have not enough time to create and use a temp table.
> If is it possible or any other idea, please mail me, Because we
> require to create this type of modification with 100'th triggers, As i
> am creating log.
>
> Jhon,
> This is right, if table structure change, cursor may be fail, that's
> why i am using sysobject and syscolumns tables to getting all columns.
> And when i use print statement, the return value of print statement
> is absolutely correct and its working.
> The @.@.rowcount only return no of rows in last query, its ok but
> before this statement, i have to match all these fields in both table.
> ####
> Actually my logic is
> if both inserted and deleted tables contain row (ie row is updated)
> and changes are actually made (ie changes must be done in any field in
> a table) then insert this row in a log file with a particular flag.
> else if only inserted table contain row then it insert with different
> flag
> same as with deleted.
> ####
> if you have any idea to done it, please reply me
> i have more than 1000 tables.
> Thanks
> Rahul
I think you have misunderstood both replies. I believe that Tibor is
suggesting that you generate your create trigger statements using the cursors
and you will end up with "hardcoded" columns for each table.
The checking of @.@.ROWCOUNT at the very begining of the trigger will mean
that you don't execute the code when not rows are changed, this will cut out
the unnecessary actions of the trigger.
Alter Trigger tr_tm_empmastR1 on tm_empmastR1 For Insert, Update, Delete
As
IF @.@.ROWCOUNT = 0 RETURN
...
John

Problem in creating reports using BI fields

Hi,

I have created a cube using 3 Dimensions and 1 measure. One of the dimensions is a time dimension. I have also used the ADD BI command to get the Year to Date and Year over year % . This works perfectly with the cube. But when I try and create a report I am unable to display the figures for the above mentioned columns. I get Null data there.

Does anybody have any suggestions as to why this is happening and what the way to overcome this.

Thanks in advance
MNJ

Could you provide more information on how you are creating the report and which columns are returning nulls?

Problem in creating reports using BI fields

Hi,

I have created a cube using 3 Dimensions and 1 measure. One of the dimensions is a time dimension. I have also used the ADD BI command to get the Year to Date and Year over year % . This works perfectly with the cube. But when I try and create a report I am unable to display the figures for the above mentioned columns. I get Null data there.

Does anybody have any suggestions as to why this is happening and what the way to overcome this.

Thanks in advance
MNJ

Could you provide more information on how you are creating the report and which columns are returning nulls?sql

Problem in creating nullable columns using SELECT INTO in SQL Serv

Hi Everyone!
I have a problem that is basically a database issue in SQL Server 2000
compared to Sybase 12. I am working on a application that should support bot
h
SYbase and SQL Server as backend. My code works fine in Sybase but not in SQ
L
Server. Following is the query that will give a an idea of the issue.
Query:
--
SELECT Null as c1,
isnull( NULL,0) as c2
INTO #temp_tbl
Above query creates both c1 and c2 as nullable columns in Sybase. The same
query in SQL Server creates C1 as nullable and C2 as not null. There are
situation that result in null data and cause inserting NULL into not columns
in SQL Server. Using SET ANSI_NULL_DFLT_ON ON also did not give the required
results. I am looking for a solution which would create c1 and c2 as nullabl
e
columns in SQL Server.
Please help in finding me a solution.
Thanks in advance.
SomeshSRV wrote:
> Hi Everyone!
> I have a problem that is basically a database issue in SQL Server 2000
> compared to Sybase 12. I am working on a application that should support b
oth
> SYbase and SQL Server as backend. My code works fine in Sybase but not in
SQL
> Server. Following is the query that will give a an idea of the issue.
> Query:
> --
> SELECT Null as c1,
> isnull( NULL,0) as c2
> INTO #temp_tbl
> Above query creates both c1 and c2 as nullable columns in Sybase. The same
> query in SQL Server creates C1 as nullable and C2 as not null. There are
> situation that result in null data and cause inserting NULL into not colum
ns
> in SQL Server. Using SET ANSI_NULL_DFLT_ON ON also did not give the requir
ed
> results. I am looking for a solution which would create c1 and c2 as nulla
ble
> columns in SQL Server.
> Please help in finding me a solution.
> Thanks in advance.
> Somesh
Like this:
CREATE TABLE #temp_tbl (c1 INTEGER NULL, c2 INTEGER NULL /* !!! NO
PRIMARY KEY !!! */)
INSERT INTO #temp_tbl (c1, c2)
SELECT ...
Curiously enough, the following seems to give your desired result in
SQL Server 2000 but not in 2005. Which just goes to show the folly of
such proprietary "tricks" as this.
SELECT c1, COALESCE(c2,0) AS c2
INTO T1
FROM (SELECT NULL, NULL) AS T(c1,c2);
David Portas
SQL Server MVP
--|||David Portas wrote:
> Curiously enough, the following seems to give your desired result in
> SQL Server 2000 but not in 2005. Which just goes to show the folly of
> such proprietary "tricks" as this.
> SELECT c1, COALESCE(c2,0) AS c2
> INTO T1
> FROM (SELECT NULL, NULL) AS T(c1,c2);
>
That was just a type coercian issue. The following tests OK on both
2000 and 2005 (8.00.760 and 9.00.1399.06)
SELECT c1, COALESCE(c2,0) AS c2
INTO T1
FROM (SELECT CAST(NULL AS INTEGER),
CAST(NULL AS INTEGER)) AS T(c1,c2);
David Portas
SQL Server MVP
--

Problem in creating new record

hi all,
I am getting a problem when creating a record in my database

when i click on new button to create a record it is throwing a error message:

Fatal error: could not get the next record number
(ODBC call-failed.-3146)
7411-37000[microsoft][Odbc sql server driver] [sql server] server 'xxxx' is not configured for Dataaccess. ODBC. recordset
3146-odbc--callfailed DAO-recordset.

please some one help me.

Shashidhar.Where is this button you speak of?

Problem in creating endpoint

Hello Everybody,

I dont know whether this is right forum to post these type of query or not. but please help me in solving this problem.

I am creating Endpoint in sql server 2005.

but in very first line its giving error.

plz let me know whats wrong with this code.

USE [Northwind]

GO

CREATE ENDPOINT customer_endpoint

STATE=STARTED

AS HTTP (

PATH='/sql',

AUTHENTICATION= (INTEGRATED),

PORTS = (CLEAR),

SITE = '*'

)

FOR SOAP (

WEBMETHOD 'http://tempUri.org'.'GetCustomerInfo'

(

NAME='Northwind.dbo.upGetCustomer', FORMAT=ROWSETS_ONLY,

schema=STANDARD

),

WSDL=DEFAULT,

BATCHES=ENABLED,

DATABASE='Northwind'

)

GO

and the Error i am getting is:

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'ENDPOINT'.

Please Help me ASAP.

Regards,

Vineet

I just did a cut and paste of the DDL and it worked for me. I ran it against SQL2K5 RTM build. The Northwind database is not available by default with SQL2K5, even then it should merely fail by saying "Database not found".

Problem in creating endpoint

Hello Everybody,

I dont know whether this is right forum to post these type of query or not. but please help me in solving this problem.

I am creating Endpoint in sql server 2005.

but in very first line its giving error.

plz let me know whats wrong with this code.

USE [Northwind]

GO

CREATE ENDPOINT customer_endpoint

STATE=STARTED

AS HTTP (

PATH='/sql',

AUTHENTICATION= (INTEGRATED),

PORTS = (CLEAR),

SITE = '*'

)

FOR SOAP (

WEBMETHOD 'http://tempUri.org'.'GetCustomerInfo'

(

NAME='Northwind.dbo.upGetCustomer', FORMAT=ROWSETS_ONLY,

schema=STANDARD

),

WSDL=DEFAULT,

BATCHES=ENABLED,

DATABASE='Northwind'

)

GO

and the Error i am getting is:

Msg 170, Level 15, State 1, Line 2

Line 2: Incorrect syntax near 'ENDPOINT'.

Please Help me ASAP.

Regards,

Vineet

I just did a cut and paste of the DDL and it worked for me. I ran it against SQL2K5 RTM build. The Northwind database is not available by default with SQL2K5, even then it should merely fail by saying "Database not found".

sql

problem in creating database and table in one shot

I need to create a database and then create tables in it..

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1')
DROP DATABASE [DB1]

-- create a new database
CREATE DATABASE [DB1] ON PRIMARY
( NAME = N'DB1', FILENAME = N'D:\DB1.mdf' ,
SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 30720KB )
LOG ON
( NAME = N'DB1_log', FILENAME = N'D:\DB1_log.ldf' , SIZE = 2048KB , MAXSIZE = UNLIMITED , FILEGROWTH = 30720KB )
COLLATE Latin1_General_CI_AS

CREATE TABLE [DB1].[dbo].[SALES](
[PERIOD] [int] NOT NULL,
[LOC] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

If i run the above statement one after one, they work; however, if I run all of them together (or in sp), the following error raised:

Msg 2702, Level 16, State 2, Line 43
Database 'DB1' does not exist.

May I know how can i create a database and then immediately the tables in sp!

Thanks

use master;

go

drop database mydatabase;

go

create database mydatabase;

go

use mydatabase;

go

create table mytable(i int );

|||Thx, but it doesn't work in stored procedure.|||

HI;

EXEC('CREATE TABLE [DB1].[dbo].[SALES](
[PERIOD] [int] NOT NULL,
[LOC] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]')

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||thx

Problem in creating Crystal reports

Hello friends,

I am creating crystal reports, I have used a crystal report viewer, but when i run it , i am asked the database login, I have set the windows NT Integrated security,so what userID and Password i should supply?

Kindly help me outIt should be no UID or PWD.
It should be windows authentication.

Good Luck.

Problem in creating Crystal reports

Hello friends,

I m stuck up with crystal reports. I have used crystal report viewer , but when I run it , i am asked database login , I have used Windows Integrated security what userid and password should I supply

Kindly help me outWhat are you using? Crystal Reports? The Crystal Report viewer in .Net? I'm assuming you are using SQL Server 2000? Need more info.

Erik Roessing

Problem in creating Asymmetric keys

I am a novice to the SQL server. I am trying to create Asymmetric key using the query

CREATE ASYMMETRIC KEY PacificSales19 AUTHORIZATION dbo

FROM FILE = ' C:\temp\temp1.snk'

ENCRYPTION BY PASSWORD = 'ABC123!@.#$';

GO

But I alwys get the follwing error

The certificate, asymmetric key, or private key file does not exist or has invalid format.

Can anyone please guide me as to how to go ablout creating the ASYMMETRIC KEY FROM FILE.

Thanks and regards

The statement you are trying is correct - this is how you can create an asymmetric key from a file.

You should check that the specified path is correct and that the file is not corrupted. How did you generate that file?

Thanks
Laurentiu

Problem in Creating AFTER INSERT Trigger

I am trying to create a AFTER INSERT Trigger. I created it and it worked for one record. But when I am trying to input multiple inserts it gives me error even on my insert statement. I am sure the syntax is right. Can anyone confirm this syntax. When I created a small table for testing this syntax worked... I dont know why its not working here.

INSERT INTO media (
media_name,
description,
active_flag,
activity_log,
data_entry_status_code,
category_id,
vendor_id,
created_by,
created_date
)
VALUES
('Test Media20', 'Test Description20', 1, 'Test Log20', 1, -1,-1, -1,'1/1/2001 12:00:00 AM'),
('Test Media21', 'Test Description21', 1, 'Test Log21',1, -1,-1,-1,'1/1/2001 12:00:00 AM'),
('Test Media22', 'Test Description22', 1, 'Test Log22',1, -1,-1,-1,'1/1/2001 12:00:00 AM')It is an insert statement.
Show me trigger text, please and what error messages you have if any.|||Also what is table [media] definition?
I mean show me datatypes of your columns.sql

Problem in Charting

I am dynamically generating the RDL for a chart,but i am not able to show markers on the X-Axis when the report is generated.

FYI ,I am creating a orthographic column chart.

Any ideas?

Markers can be turned on using the Chart properties dialog in VS.NET.

In the Data tab, Select the Values you want to display makers. Cick "Edit". Then on the Appearance tab, click the Show markers checkbox.

-Chris

|||

Hi,

I suppose u didnt read my question fully..I am dynamically generating the RDL wherein I need to show the labels (Markers) on the X-Axis.

|||

You can always lookup the RDL specification available here: http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx

Here is an example of what you would need to generate to get tickmarks on the x-axis (depending on major and minor interval settings or automatic intervals if omitted):

<CategoryAxis>
<Axis>
<Title />
<Visible>true</Visible>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<MajorInterval>5</MajorInterval>
<MinorInterval>1</MinorInterval>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<Margin>true</Margin>
<Scalar>true</Scalar>
</Axis>
</CategoryAxis>

-- Robert