Immediate update/Queued update problem

I'm having a problem creating immediate update/queued update transactional
replication on my test servers. I go through the wizards and create the
publication using the advanced mode and set it up. It appears to work fine.
When I create the subscription, nothing happens. I cannot get the inital
snapshot to work and it will not create the triggers and stored procedures.
Anyone have any ideas?
Connect to your publisher in EM, expand Replication Monitor, expand the
replication agents, expand the distribution agent folder, locate the
distribution agent for your publication, right click on it, select Agent
Properties and change the job owner to sa.
Right click on your distribution agent and restart it. You may have to also
do this for the snapshot agent.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:5289270A-7B92-45F6-9887-A78D66ACB061@.microsoft.com...
> I'm having a problem creating immediate update/queued update transactional
> replication on my test servers. I go through the wizards and create the
> publication using the advanced mode and set it up. It appears to work
> fine.
> When I create the subscription, nothing happens. I cannot get the inital
> snapshot to work and it will not create the triggers and stored
> procedures.
> Anyone have any ideas?
|||Thank for your help.
I found that there was an issue with the computer name. The computer had
gotten its name changed and after rectifying that the triggers were written.
So.. Now I have a problem on the subscriber writing back in a two phase
commit back to the publisher. I get an error during the insert that indicates
that the sa password cannot connect. I traced the call and I see the call to
openserver where the sa login is used and the password is set as an empty
string. I cannot find where to set the login and password for the writeback.
Could someone help me out?
"Hilary Cotter" wrote:

> Connect to your publisher in EM, expand Replication Monitor, expand the
> replication agents, expand the distribution agent folder, locate the
> distribution agent for your publication, right click on it, select Agent
> Properties and change the job owner to sa.
> Right click on your distribution agent and restart it. You may have to also
> do this for the snapshot agent.
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:5289270A-7B92-45F6-9887-A78D66ACB061@.microsoft.com...
>
>

imitating nested "FOREACH" loop in SQL Query

Dear All,

I need to create a query to list all the subfolders within a folder.

I have a database table that lists the usual properties of each of the folder.

I have another database table that has two columns

1. Parent folder
2. Child folder

But this table maintains the parent child relationship only to one level.

For example if i have a folder X that has a subfolder Y and Z.
And Y has subfolders A and B.
and B has subfolder C and D
and C has subfolder E and F

The database table will look like

parentfolder child folder
X Y
X Z
Y A
Y B
B C
B D
C E
C F

I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.

I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.

The APPLY operator will do what you need.

Check out:

http://msdn2.microsoft.com/en-us/library/ms175156.aspx

For a description and an example that pretty much is like your needs.

|||

In sql server 2005 you can use CTE..

Code Snippet

Create Table #folder (

[parentfolder] Varchar(100) ,

[childfolder] Varchar(100)

);

Insert Into #folder Values('X','Y');

Insert Into #folder Values('X','Z');

Insert Into #folder Values('Y','A');

Insert Into #folder Values('Y','B');

Insert Into #folder Values('B','C');

Insert Into #folder Values('B','D');

Insert Into #folder Values('C','E');

Insert Into #folder Values('C','F');

;With CTE([parentfolder],[childfolder],[Level],[Paths]) as

(

Select [parentfolder],[childfolder], 1 Level, Cast(Parentfolder + '\' + childfolder as varchar) Paths From #folder Where parentfolder = 'X'

UNION ALL

Select data.[parentfolder],data.[childfolder], Level + 1,Cast(Paths + '\' + data.[childfolder] as varchar)From #folder Data Join CTE On Data.ParentFolder = CTE.childfolder

)

Select * from CTE Order By Paths

|||Nicely done Mani!

Images on a report

Hi being new to reporting services this is probably simple, so here goes.

Using Visual Studio 2005 to create the report. I have a SQLExpress db.

In the Product table that I want to run the report on there is a field called ImageName it contains the image filename e.g CanonIXUS.jpg

and I have a directory with all the images in it.

So how do I get the report writer to show the Image for the product in the row?

Thanks

Please check the documentation:
http://msdn2.microsoft.com/en-us/library/ms156482(SQL.90).aspx

In your case, it sounds like you want to add an "external" image where you will need to dynamically construct the path with an expression.

-- Robert

|||

Hi,

Thanks for that.

However all the documentation talks of an image wizard and various properties when you drag an image control onto a report.

Unfortunatly after adding a report (test.rdlc) to my VS2005 project none of these options seem to be available to me. I can easily display data but I don't have a place to select the various properties for an image eg. "external" .

Any Ideas ?

|||

You can use the VS property browser to set the image properties. To set the source of an image to "external", select the image, go to the Properties window and locate the Source property.

-Albert

Images on a report

Hi being new to reporting services this is probably simple, so here goes.

Using Visual Studio 2005 to create the report. I have a SQLExpress db.

In the Product table that I want to run the report on there is a field called ImageName it contains the image filename e.g CanonIXUS.jpg

and I have a directory with all the images in it.

So how do I get the report writer to show the Image for the product in the row?

Thanks

Please check the documentation:
http://msdn2.microsoft.com/en-us/library/ms156482(SQL.90).aspx

In your case, it sounds like you want to add an "external" image where you will need to dynamically construct the path with an expression.

-- Robert

|||

Hi,

Thanks for that.

However all the documentation talks of an image wizard and various properties when you drag an image control onto a report.

Unfortunatly after adding a report (test.rdlc) to my VS2005 project none of these options seem to be available to me. I can easily display data but I don't have a place to select the various properties for an image eg. "external" .

Any Ideas ?

|||

You can use the VS property browser to set the image properties. To set the source of an image to "external", select the image, go to the Properties window and locate the Source property.

-Albert

sql

Images not displaying on reports!

We are trying to create a Report w/ images that are stored in the SQL Server table.

These images are being copied and pasted into an ACCESS application (w/, as said above, a SQL Server back-end)....

All I get on the report is a box with a red 'X'.

(Now, I have tried the routine to remove the OLE header as suggested on other places on the Internet. While this works for the Northwind images, this is not working on our images. Which leads me to guess that the OLE Header on our images may be different than 78. But how do we know or calculate that?)..

My guess would be that some other people have encountered similar problems.

Any help will be highly appreciated.

Thanks..

Were you able to find the solution for this?

I'm encountering the same problem.

Thanks!

sql

Images

I want to create a catalogue such thing with stores items in a shop, some with pictures. How do I store images in sql server 2000?
Thank YouPainfully...just store the path to the image...

Look up READTEXT, WRITETEXT in bol if your still interested...|||sort the threads on this forum .. its the tenth largest thread..
but please read it completely to get the idea to store images in SQL server|||how do you sort the threads??|||Click on the word reply...

http://www.dbforums.com/t203835.html|||are u sure u gav me the correct link...|||Originally posted by asbirpam
I want to create a catalogue such thing with stores items in a shop, some with pictures. How do I store images in sql server 2000?

Thank You

You can create a field in your table with the type of image. you can then save the image in the table.|||Check the thread http://www.dbforums.com/t203835.html referred by Brett which is updated.|||http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part3/c1161.mspx is a really comprehensive article on that topic

image datatype - get data out and create insert statement

I am needing to get the data out of a table that has an image datatype, and create an insert statement so that the data can be inserted into other databases(to be used as part of an upgrade script).

I have used the procedure InsertGenerator in the past to create insert statements out of tables that have data. This is the first time I have come across the image datatype. The image part of the procedure looks like this: (@.dataType, @.colName, and @.stringData are nvarchar)

IF @.dataType='image'
BEGIN
SET @.stringData=@.stringData+'''''''''+
isnull(cast(convert(varbinary,'+@.colName+')
as varchar(6)),''0'')+'''''',''+'
END

It successfully creates the insert statement. The insert statement runs successfully, but I am not sure if the image itself is ok. When I use the related application, it crashes and I cannot tell if it is because of the image. When I run a sql trace the last statement ran is pulling back the image as well as a few other columns. When I run the trace statement in query analyzer, it works, but in query analyzer it isnt displaying the image.

My question is will the above statement successfully convert an image to a value that can be inserted into another table and the image recreated when you run the application? Is there something else I need to do to get the image value in a table out so that it can be used in an insert statment.

BTW this is on SQL 2000.

Thank you so much,

Tracey

This thread (found by just looking down the message list without having to even change pages -WOW), may give you the information you desire.

Using a stored procedure to reinsert an image

|||

Perhaps I am not understanding, I am using a script to create and insert statement an example is below. The FileBinary column is the image. I cannot tell if this is correctly extracted as am image. The insert is successful when ran in query analyzer, but the image doesnt display in the application, the application crashes, so I cant tell if it is the image or not.
Creating an insert statement to the same database is not what Im trying to do. I am trying to create an insert statement that can be ran on other like databases not on the network, that are needing this piece of information for the latest version of our application.

insert into JTFile (FileID, Path, Name, Extension, Type, Description, HashValue, Seed, RecordActive,FileBinary) values('0588dbda3f4d483d84259626c251f072','','','.rpt','','test','','2055291722',1,'??')

Im new to stored Procedures

I create my store proc. I want to give it a parameter AccNmbr and I want it to return to me the total of the columns SubNmbr in total variable; total is gonna be an output. So I do this in my Stored Proc:

Create Procedure dbo.totalSub
@.AccNmbr bigint,
@.total bigint out
AS
Select @.total=sum(SubNmbr) where AccNmbr=@.AccNmbr
return

Go

When I run the proc it tells me that parameter total was not provided. I also tried with return (instead of Return @.total)
it always tells me missing total parameter. But I want total to be my result, not my input. Only AccNmbr is my input.
Thanks a lot for your help.

When you create this Stored Procedure, you have 2 input parameters. Either you can specify one as output, or just have 1 input... Here's a modification that will do what it seems you were doing. You also missed what table you were pulling the results from, that is mentioned by "FROM TABLE".

Here ya go:

Create

Procedure dbo.totalSub
@.AccNmbr int,
AS
Selectsum(SubNmbr)FROMTABLEwhere AccNmbr=@.AccNmbr
Go

|||

You re right, I correct my stuff as shown bellow:

My stored procedure works perfectly when I run Query Analyser, but when I run my VB program I get the eror: I get the message : An SqlParameter with ParameterName'@.total' is not contained by this SqlParameterCollection.

Here is my stored Proc and my VB program is right below

I- Stored Proc:

CREATE PROCEDURE dbo.totalsub
@.account bigint,
@.total bigint output
AS
select total=sum(SubPhnNmbr) from tblsub whereSubAccNmbr=@.account
return
GO

II- And my pogram in VB is:

Dim totsub As Int64
Dim cm As New SqlCommand
Dim cn As New MyConnection
cn.open
'my connection is defined by me don't worry about it
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "totalsub"
cm.Connection = cn
Dim pm As SqlParameter
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Account", System.Data.SqlDbType.BigInt))
pm.Value = 100000165
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("total", System.Data.SqlDbType.BigInt, 4))
pm.Direction = ParameterDirection.Output
totsub = cm.Parameters("total").Value
cm.ExecuteScalar()
totsub = cm.Parameters("total").Value

I also tried using @.total instead of total and I tried ParameterDirection.ReturmValue instead of ParameterDirection.Output

No Luck, thanks a lot for help

iis log report

hi i want to create a report using iis log
how can i do it

i can do it by transferring log to a sql table
but isn't it a way to directly use log file in reportThe best way would be to directly let the log populate in a ODBC database where you can directly report from.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||But i m using W3C Extended log file,
i need to create report using this file as this format provide much more detail then ODBC option.

should i use custom data extension.

IIF String Contains a value?

I want to create an IIF expression that changes the color of a field based on if a string value contains a 4 or 5 Any ideas on how to accomplish this?

Hi,

You can write an iif() expression in the background color property of that field.

The following expression will help:

iif(instr("string5",5) or instr("string4",4),"Gray","White")

You can change the color by choosing what u require from the constants provided else custon color.

Somiya

|||Thanks, but I think I need a little more help. The value I need to look for will be in a string like "3,4,6,8" so I think I need the Like conparison function, but I'm not sure of the syntax

The psudo code is this:

If instrI(String) contains a 4 or instrI(String) contains 5 display in Red else Black

Do you know what the syntax should be for IIF with a like conparison?|||

i think the same expression should work in a way similar to Like.

An iff() expression with Like operator would be in this case:

iif(("3,4,6,8" Like "*4*") or ("3,4,6,8" Like "*5*") ,"Red",Black")

* is for any 4 preceeding and followed with any number of characters

Somiya

Iif statement to prevent divide by zero?

Hi- I'm trying to create a calculated field that is the percentage difference
between two database fields. To prevent a divide by zero, I tried making it:
= Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
This should provide the % diff, or in the case that dsPrice is 0, 1 (100%).
When I try to run the report, however, it comes back as a divide by zero for
fields where dsPrice = 0. Does reporting services evaluate both portions of
the Iif, then output one? How do I avoid this divide by zero error?
Thanks in advance!
Peter L.iif always evaluates both sides. try using the short circuit operator
'andalso' or 'orelse' in a function and add it to the code and call it from
the expression.|||If the correct zero value is 100%, you can just move the pieces around like
this:
= Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
That way, the division doesn't happen at all until the values are replaced.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"plandry@.newsgroups.nospam"
<plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
> Hi- I'm trying to create a calculated field that is the percentage
> difference
> between two database fields. To prevent a divide by zero, I tried making
> it:
> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
> This should provide the % diff, or in the case that dsPrice is 0, 1
> (100%).
> When I try to run the report, however, it comes back as a divide by zero
> for
> fields where dsPrice = 0. Does reporting services evaluate both portions
> of
> the Iif, then output one? How do I avoid this divide by zero error?
> Thanks in advance!
> Peter L.|||Whoops, I think that should have been more like this:
= Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
Iif(Fields!dsPrice.Value = 0, 1, Fields!dsPrice.Value)
Anyway, you get the idea!! :-)
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:eGkBJLo8EHA.1264@.TK2MSFTNGP12.phx.gbl...
> If the correct zero value is 100%, you can just move the pieces around
> like this:
> = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
> That way, the division doesn't happen at all until the values are
> replaced.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "plandry@.newsgroups.nospam"
> <plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
> news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
>> Hi- I'm trying to create a calculated field that is the percentage
>> difference
>> between two database fields. To prevent a divide by zero, I tried making
>> it:
>> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
>> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
>> This should provide the % diff, or in the case that dsPrice is 0, 1
>> (100%).
>> When I try to run the report, however, it comes back as a divide by zero
>> for
>> fields where dsPrice = 0. Does reporting services evaluate both portions
>> of
>> the Iif, then output one? How do I avoid this divide by zero error?
>> Thanks in advance!
>> Peter L.
>|||That did the trick... Thanks a bunch!
I will file that away in the "ninja reporting tricks" :)
"Jeff A. Stucker" wrote:
> Whoops, I think that should have been more like this:
> = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> Iif(Fields!dsPrice.Value = 0, 1, Fields!dsPrice.Value)
> Anyway, you get the idea!! :-)
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:eGkBJLo8EHA.1264@.TK2MSFTNGP12.phx.gbl...
> > If the correct zero value is 100%, you can just move the pieces around
> > like this:
> >
> > = Iif(Fields!dsPrice.Value = 0, 1, Fields!eePrice.Value ) /
> > Iif(Fields!eePrice.Value = 0, 1, Fields!dsPrice.Value)
> >
> > That way, the division doesn't happen at all until the values are
> > replaced.
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "plandry@.newsgroups.nospam"
> > <plandrynewsgroupsnospam@.discussions.microsoft.com> wrote in message
> > news:4B6AE1CA-72DB-4DAE-8E63-147AF61BDC21@.microsoft.com...
> >> Hi- I'm trying to create a calculated field that is the percentage
> >> difference
> >> between two database fields. To prevent a divide by zero, I tried making
> >> it:
> >> = Iif( Fields!dsPrice.Value <> 0, (Fields!eePrice.Value -
> >> Fields!dsPrice.Value) / Fields!dsPrice.Value, 1)
> >> This should provide the % diff, or in the case that dsPrice is 0, 1
> >> (100%).
> >> When I try to run the report, however, it comes back as a divide by zero
> >> for
> >> fields where dsPrice = 0. Does reporting services evaluate both portions
> >> of
> >> the Iif, then output one? How do I avoid this divide by zero error?
> >>
> >> Thanks in advance!
> >> Peter L.
> >
> >
>
>

IID_IDBDataSourceAdmin Error Trying to Create a Database using Query Analyzer on a Mobile Device

Hi,

Please provide some help regarding the "Interface Defining Error: IID_IDBDataSourceAdmin" error while trying to create a SDF database using Query Analyzer on a Windows CE 5.0 mobile device (Symbol MC3000).

Error: 0x80004005 E_FAIL

Native Error: 28558

Description: SQL Mobile encountered problems when creating database [,,,,]

Param. 0: 0

Param. 1: 0

Param. 2: 0

Param. 3:

Param. 4:

Param. 5:

A list of (related) installed packages:

NETCFv2.wce5.armv4i.cab

sqlce30.dev.ENU.wce5.armv4i.CAB

sqlce30.repl.wce5.armv4i.CAB

sqlce30.wce5.armv4i.CAB

PS.

Basically I have developed a mobile application that programmatically creates the database, the code worked on a similar device (Win CE 50), trying to run the application on a new device resulted in database creation errors. I tried creating a test database manually .. and this is what I got.

Browsing MSDN or searching on the Forum did not help.

~Zarko Gajic

AH!

Problem "solved". The device was in its cradle BUT no battery was installed!

When the battery was inserted I was able to create the database using Query Analyzer.

However, I now have additional problems:

Trying to create the database programmatically (using SQlCeEngine.CreateDatabase) results in:

ErrorCode: 8007000E

Minor Code 28558.

Again I can not find any meaningful info on this.

~Zarko Gajic