Impact on replication when a server goes into standby mode

We have the following scenario:

Server A replicates Database A to Server B.

Server C has Database A on it as well, but in standby mode. We are applying the transaction logs generated by Database A on Server A to the database on Server C leaving it in standby mode each time.

Let's say we had planned maintenance for Server Aand dumped the last set of transactions on Server A in standby mode to be applied to to Server C. What happens to the replica on Server B? When I start to use Server C, can I backup its transactions and apply them to Server A, and then have those transactions replicated to Server B? And then what do I do when the maintenance is complete so that I can swithc back to Server A and have the replication continue on as before the maintenance to Server B?

Thanls

In SQL 2000 unless you

1) have configured Server C to also ship the master, msdb, distirbution and publication databases
2) restore the last log with the keep_replication switch
3) take Server A completely offline
4) rename ServerC to ServerA and reboot ServerC(now ServerA)
5) run sp_replrestart
6) use the continue on data consistency error profile

it will not work.

If you also follow the sequence in a failback scenario it will also work.

In SQL 2005 you can configure Server A and C as log shipping failover partners (use a remote distributor and set the publisherfailoverpartner accordingly).

Note that it is possible to do the following in SQL 2000

1) ship logs to ServerC from Server A.
2) on failover restore the tail of A to C with the keep_replication switch
3) use the sp_replrestart command to pick up where you left off

There is more potential exposure to data loss with the above method.

images not showing up on server

I am serving up reports using the web service using the render method.
The reports have images on them. The following scenarios work
.Net application and Reporting Services are all on the same
workstation. Images show up and everything works well.
.Net application on same workstation and I point to Reporting Services
which is on Test environment server A. Images show up and everthing
works well.
This is not working
.Net application is on server B and Reporting Services is on the same
server A. The report shows up but all images show up with the red x.
What can be different between the code on my local workstation and the
code on the server? I have copied all the neccessary files out there.
Is there something on my workstation which has Visual Studio installed
that is not on the server B which only has IIS installed? Or is there
something else I could be missing in the migration to the server
environment?We have found that the report comes up just fine in the server
environment but the image is using different credentials than the user
that is logged in. If we run impersonate as user ABC, all reports come
up fine under ABC, but the image prompts a login box. The image wants
to load under the users that logged in and not the user we use to
impersonate. Any thoughts on how to get around this?

Image Sizing

Hi
I have done some research re image sizing and have not been able to confirm
if the following be done. Has anybody had this type of request and solved
it? ..
Logo to display on the page in specific place, limit the image size to a
width of 4cm wide or 2cm high. whichever dimension is exceeded first. Image
should maintain its apect ratio
Many thanks
IanCan you just resize the image file? Or create new version(s) in the
size(s) you require?|||Thank you for the reply, ... unfortunately the details of the requirements
ended up being slightly different (image resizing was not reventually
equired) to what I had been led to believe and the research that I was doing .
"toolman" wrote:
> Can you just resize the image file? Or create new version(s) in the
> size(s) you require?
>sql

Image Filepath in sql

Hi in my table i have a field which would store the file path of an image, the datatype is a varchar, and i wrote the following filepath in it but it didnt work, when i tried to call it up in the browser it didnt show, is the way i saved it below the right format?

C:\Millio\Documents\Project\Images\car.jpg

That's fine, but you will have to post some of your code so we can see why it might not be diplaying.

|||

Hi thanks for responding this is the part where i am calling the image from;

<ItemTemplate>

<span><spanstyle="font-size: 14pt">

<asp:ImageID="Image1"ImageUrl='<%# Eval("Images") %>'runat="server"/>

<br/>

|||

You should store the virtual path of your image in the database rather than the physical path.

|||

If you use a physical path like the one you are showing, you will need to use an HttpHandler to stream the image to the browser. The img tag takes a url value for the src attribute, so you could move the images to a folder within your web site, and reference them with a virtual path. Or if Project is already your virtual directory (the root folder for your web site), the path that you should store in the database is "images/car.jpg" or "~/images/car.jpg". Both will work, but the second one is ASP.NET specific, and can only be used when setting the ImageURL property of an asp server control. I would opt for the first one, just in case I decided to convert the site to PHP.

Or more realistically, decide to use the value to build up the src attribute of an <img> tag in code, and don't bother with a server control.

|||

Hi Mike the reason i did it that way is because i am using a user control to load up my pages, and i didnt want to have to make a page for each category, i will show you what i mean;

<%@.ControlLanguage="C#"AutoEventWireup="true"CodeFile="CategoryList.ascx.cs"Inherits="CategoryList" %>

<scriptrunat="server">

string _Category = "washing machines";

public string Category

{

get { return _Category; }

set { _Category = value; }

}

protected void DataList1_SelectedIndexChanged(object sender, EventArgs e)

{

Label ID = (Label)DataList1.SelectedItem.FindControl("categoryIDLabel");

// Label AD = (Label)DataList1.SelectedItem.FindControl("PriceLabel");

Label MD = (Label)DataList1.SelectedItem.FindControl("categoryNameLabel");

Trace.Write("category " + ID.Text);

//Trace.Write("Price " + AD.Text);

Trace.Write("categoryName " + MD.Text);

Session["scategoryID"] = ID.Text;

//Session["scategorydescription"] = AD.Text;

Session["scategoryName"] = MD.Text;

}

protected void Page_Load(object sender, EventArgs e)

{

SqlDataSource1.SelectParameters["categoryName"].DefaultValue = _Category;

}

</script>

<asp:DataListID="DataList1"runat="server"BackColor="White"BorderColor="Transparent"

BorderStyle="None"BorderWidth="1px"CellPadding="4"DataSourceID="SqlDataSource1"

ForeColor="Black"GridLines="Horizontal"OnSelectedIndexChanged="DataList1_SelectedIndexChanged">

<FooterStyleBackColor="#CCCC99"ForeColor="Black"/>

<SelectedItemStyleBackColor="White"Font-Bold="True"ForeColor="White"Font-Italic="False"Font-Overline="False"Font-Strikeout="False"Font-Underline="False"/>

<HeaderStyleBackColor="#333333"Font-Bold="True"ForeColor="White"/>

<SeparatorStyleBackColor="Black"BorderColor="Black"BorderStyle="Solid"BorderWidth="2px"/>

<AlternatingItemStyleBackColor="White"Font-Bold="False"Font-Italic="False"Font-Overline="False"

Font-Strikeout="False"Font-Underline="False"/>

<ItemTemplate>

<span><spanstyle="font-size: 14pt">

<asp:ImageID="Image1"ImageUrl='<%# Eval("categoryImage") %>'runat="server"/>

<br/>

<asp:LabelID="categoryNameLabel"runat="server"Text='<%# Eval("categoryName") %>'ForeColor="DimGray"Font-Size="11pt"Font-Names="Trebuchet MS"></asp:Label><br/>

<br/>

<spanstyle="color: black"></span>

<asp:LabelID="CategoryIDLabel"runat="server"Visible="False"Text='<%# Eval("categoryID") %>'ForeColor="DimGray"Font-Size="11pt"></asp:Label><br/>

<br/>

<asp:LabelID="categoryDescriptionLabel"runat="server"Visible="True"Text='<%# Eval("categoryDescription") %>'ForeColor="DimGray"Font-Size="11pt"></asp:Label><br/>

<br/>

<br/>

</ItemTemplate>

</asp:DataList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:streamConnectionString %>"

SelectCommand="SELECT [categoryID], [categoryImage], [categoryName], [categoryDescription] FROM [Categories] WHERE ([categoryName] = @.categoryName)">

<SelectParameters>

<asp:ParameterName="categoryName"Type="string"/>

</SelectParameters>

</asp:SqlDataSource>

Image background problem

Hai,

I hope anybody can help me with the following problem.

I've created a report on which there is a single image (using: Insert->Picture) with a white background. If I export this report to pdf or word or html the background of this image is blueish...

I have tried using transparent gif, transparent png but nothing seems to work.

Does anybody have any idea how i can solve this?

Thanx in advance,

BertStrange problem
Are you able to chage that color in Word or Pdf?

Im sure this is an easy one...Error trap to skip over a "bad" object.

Hello, I have the following code to iterate through each view in a SQL
Server and call the "sp_refreshview" command against it. It works
great until it finds a view that is damaged, or otherwise cannot be
refreshed. Then the whole routine stops working.

Can someone please help me re-write this code so that any views that
fail the "sp_refreshview" command get skipped. I'm sure it's just a
matter of putting some basic error trapping into the loop, but I've had
a few goes at it and failed.

Many thanks.

DECLARE @.DatabaseObject varchar(255)
DECLARE ObjectCursor CURSOR
FOR SELECT table_name FROM information_schema.tables WHERE table_type =
'view'
OPEN ObjectCursor
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC sp_refreshview @.DatabaseObject
Print @.DatabaseObject + ' was successfully refreshed.'
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
END
CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO(rod.weir@.gmail.com) writes:
> Hello, I have the following code to iterate through each view in a SQL
> Server and call the "sp_refreshview" command against it. It works
> great until it finds a view that is damaged, or otherwise cannot be
> refreshed. Then the whole routine stops working.
> Can someone please help me re-write this code so that any views that
> fail the "sp_refreshview" command get skipped. I'm sure it's just a
> matter of putting some basic error trapping into the loop, but I've had
> a few goes at it and failed.

If you are on SQL 2005, lookup TRY-CATCH in Books Online.

If you are on SQL 2000, you could possibly do the linked-server trick:
http://www.sommarskog.se/error-hand...#linked-servers.

I'm not into refreshing views myself, but I can't think of a way to
detect this condition before-hand.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland,

I need to refresh all views because there are some views that have
embedded views within them, using a Select * statement. When the
underlying view changes (new column etc), the parent view does not pick
up the new column in the embedded view that it references.

Using SQL Server 2000. Surely there must be a simple way to trap the
error and skip over it right?

Perhaps just after the following line...

EXEC sp_refreshview @.DatabaseObject

...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
primarily a VB developer, so this TSQL has got me a little puzzled.

I'll give your website a read. Thanks again.|||> ...you examine @.@.Error and ignore or continue in the loop?

Some errors will abort the batch so you are SOL after the error. If the
linked server doesn't work for you, you might try preceeding the
sp_refreshview with a select statement with SET FMTONLY ON. That will allow
you detect the error and skip the sp_refreshview for problem views.

DECLARE @.DatabaseObject nvarchar(261)
DECLARE ObjectCursor CURSOR FAST_FORWARD READ_ONLY
FOR SELECT
QUOTENAME(TABLE_SCHEMA) +
'.' +
QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'VIEW'

OPEN ObjectCursor
WHILE 1 = 1
BEGIN
FETCH NEXT FROM ObjectCursor INTO @.DatabaseObject
IF @.@.FETCH_STATUS = -1 BREAK
PRINT 'Refreshing view ' + @.DatabaseObject
EXEC ('SET FMTONLY ON SELECT * FROM ' + @.DatabaseObject)
IF @.@.ERROR = 0
BEGIN
EXEC sp_refreshview @.DatabaseObject
PRINT 'View ' + @.DatabaseObject + ' refreshed'
END
ELSE
BEGIN
PRINT 'Error refreshing view ' + @.DatabaseObject
END
END

CLOSE ObjectCursor
DEALLOCATE ObjectCursor
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

<rod.weir@.gmail.com> wrote in message
news:1143705825.644611.51750@.u72g2000cwu.googlegro ups.com...
> Thanks Erland,
> I need to refresh all views because there are some views that have
> embedded views within them, using a Select * statement. When the
> underlying view changes (new column etc), the parent view does not pick
> up the new column in the embedded view that it references.
> Using SQL Server 2000. Surely there must be a simple way to trap the
> error and skip over it right?
> Perhaps just after the following line...
> EXEC sp_refreshview @.DatabaseObject
> ...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
> primarily a VB developer, so this TSQL has got me a little puzzled.
> I'll give your website a read. Thanks again.|||[My newsserver had an outage, and my original post got lost. Now that it's
back, I'm reposting]

(rod.weir@.gmail.com) writes:
> I need to refresh all views because there are some views that have
> embedded views within them, using a Select * statement.

Did anyone tell you that this is bad practice? :-)

> Using SQL Server 2000. Surely there must be a simple way to trap the
> error and skip over it right?
> Perhaps just after the following line...
> EXEC sp_refreshview @.DatabaseObject
> ...you examine @.@.Error and ignore or continue in the loop? Sorry, I'm
> primarily a VB developer, so this TSQL has got me a little puzzled.

The problem is that there are quite few errors that abort the batch, and
those you cannot trap easily in SQL 2000. I seem to recall that refreshview
errors belongs to this group. The linked-server trick is a serious kludge,
but for this case it could be worth the pain.

Then again, if you are a VB developer, just code the loop in a VB program
or in VB script. That's probably easier than setting up linked servers for
this task.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.seBooks Online for SQL
Server 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||Hi Dan,

Many thanks for your response. This code does exactly what I'm after.
It skipped over the bad queries and kept refreshing the good ones. I
modified the following line to speed it up a little bit.

EXEC ('SET FMTONLY ON SELECT * FROM ' + @.DatabaseObject + 'Where 1=0')

Notice the Where 1 = 0 clause? Much quicker now.

Thanks Dan and Erland. Problem solved.

p.s. Erland. I am going to start another thread on the evils of
embedded queries. I have heard a lot of people say that this is a bad
practice, however I've never heard any really compelling evidence to
say why.

I'm not sure why this one fails,

I used the following SQL to extract data from our Firms emplyee database and
creates a text file that gets imported into our copy machine controllers.
Output lines look like this:
A|U|4111|MPLS, PBMS|01|0||
A|U|4222|RIC, PBMS|06|0||
A|U|2036|user, name|01|0|N|1234562|
The last number is the last 7 of the persons corporate VISA card. On SQL
2000 this SQL worked:
select 'A|U|' +
RTRIM(EMPLOYEE_CODE) + '|'
+ RTRIM(EMPLOYEE_NAME) + '|'
+ rtrim(offc) + '|0|' +
case position
when 'partner' then 'P'
else 'N'
end
+ '|' , _visano , '|'
from HBM_PERSNL
where INACTIVE = 'N'
and EMPLOYEE_NAME NOT like '%CMS%'
and EMPLOYEE_NAME NOT Like '%billing%'
and EMPLOYEE_NAME NOT Like '%temp%'
On SQL 2005 it doesn't: The sql runs but only delivers those with that
VISA Numeber, where it use to give you both those with and without cards.
Any Ideas?When you don't have a credit card number, are you storing a NULL instead?
Are you getting any errors? How are you creating the file?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jay Bukstein" <JayBukstein@.discussions.microsoft.com> wrote in message
news:697B4582-6D4F-47BD-B5DF-F1AD95422D0C@.microsoft.com...
I used the following SQL to extract data from our Firms emplyee database and
creates a text file that gets imported into our copy machine controllers.
Output lines look like this:
A|U|4111|MPLS, PBMS|01|0||
A|U|4222|RIC, PBMS|06|0||
A|U|2036|user, name|01|0|N|1234562|
The last number is the last 7 of the persons corporate VISA card. On SQL
2000 this SQL worked:
select 'A|U|' +
RTRIM(EMPLOYEE_CODE) + '|'
+ RTRIM(EMPLOYEE_NAME) + '|'
+ rtrim(offc) + '|0|' +
case position
when 'partner' then 'P'
else 'N'
end
+ '|' , _visano , '|'
from HBM_PERSNL
where INACTIVE = 'N'
and EMPLOYEE_NAME NOT like '%CMS%'
and EMPLOYEE_NAME NOT Like '%billing%'
and EMPLOYEE_NAME NOT Like '%temp%'
On SQL 2005 it doesn't: The sql runs but only delivers those with that
VISA Numeber, where it use to give you both those with and without cards.
Any Ideas?|||Hi Jay
a quick question, is _visano a column in the HBM_PERSNL table?
I noticed that at the end of the query you are using a comma instead of + to
concat the string, should the query be:
select 'A|U|' +
RTRIM(EMPLOYEE_CODE) + '|'
+ RTRIM(EMPLOYEE_NAME) + '|'
+ rtrim(offc) + '|0|' +
case position
when 'partner' then 'P'
else 'N'
end
+ '|' + _visano + '|'
from HBM_PERSNL
where INACTIVE = 'N'
and EMPLOYEE_NAME NOT like '%CMS%'
and EMPLOYEE_NAME NOT Like '%billing%'
and EMPLOYEE_NAME NOT Like '%temp%'
Lucas
"Jay Bukstein" wrote:

> I used the following SQL to extract data from our Firms emplyee database a
nd
> creates a text file that gets imported into our copy machine controllers.
> Output lines look like this:
> A|U|4111|MPLS, PBMS|01|0||
> A|U|4222|RIC, PBMS|06|0||
> A|U|2036|user, name|01|0|N|1234562|
> The last number is the last 7 of the persons corporate VISA card. On SQL
> 2000 this SQL worked:
> select 'A|U|' +
> RTRIM(EMPLOYEE_CODE) + '|'
> + RTRIM(EMPLOYEE_NAME) + '|'
> + rtrim(offc) + '|0|' +
> case position
> when 'partner' then 'P'
> else 'N'
> end
> + '|' , _visano , '|'
> from HBM_PERSNL
> where INACTIVE = 'N'
> and EMPLOYEE_NAME NOT like '%CMS%'
> and EMPLOYEE_NAME NOT Like '%billing%'
> and EMPLOYEE_NAME NOT Like '%temp%'
> On SQL 2005 it doesn't: The sql runs but only delivers those with that
> VISA Numeber, where it use to give you both those with and without cards.
> Any Ideas?
>|||Sounds like your server or session defaults on '05 yield null on
concatenation; i.e., select 'a' + NULL yields NULL whereas on 2000 select
'a' + NULL yields 'a'.
Suggest to modify the query to
....
'|' + ISNULL(_visano, '') + '|'
...
In fact, this is a good practice for any columns that allow null. That way
changes to server or session settings always yield the same results.
"Jay Bukstein" <JayBukstein@.discussions.microsoft.com> wrote in message
news:697B4582-6D4F-47BD-B5DF-F1AD95422D0C@.microsoft.com...
>I used the following SQL to extract data from our Firms emplyee database
>and
> creates a text file that gets imported into our copy machine controllers.
> Output lines look like this:
> A|U|4111|MPLS, PBMS|01|0||
> A|U|4222|RIC, PBMS|06|0||
> A|U|2036|user, name|01|0|N|1234562|
> The last number is the last 7 of the persons corporate VISA card. On SQL
> 2000 this SQL worked:
> select 'A|U|' +
> RTRIM(EMPLOYEE_CODE) + '|'
> + RTRIM(EMPLOYEE_NAME) + '|'
> + rtrim(offc) + '|0|' +
> case position
> when 'partner' then 'P'
> else 'N'
> end
> + '|' , _visano , '|'
> from HBM_PERSNL
> where INACTIVE = 'N'
> and EMPLOYEE_NAME NOT like '%CMS%'
> and EMPLOYEE_NAME NOT Like '%billing%'
> and EMPLOYEE_NAME NOT Like '%temp%'
> On SQL 2005 it doesn't: The sql runs but only delivers those with that
> VISA Numeber, where it use to give you both those with and without cards.
> Any Ideas?
>

Im baffled by the single quotes with STMT

Hi,

Don't worry about the vars, they are defined,
the following line give me an err of "Incorrect syntax near '.'."
Goal: to rename nonstardard column name.

EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%[^A-Za-z0-9_#$@.]%','')',
'COLUMN';

Thanks.Doug Baroter (qwert12345@.boxfrog.com) writes:
> Don't worry about the vars, they are defined,
> the following line give me an err of "Incorrect syntax near '.'."
> Goal: to rename nonstardard column name.
> EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%[^A-Za-z0-9_#$@.]%','')',
> 'COLUMN';

You can only pass constants and variables as parameters to stored procedures.
You cannot pass an expression as a parameter, but you must put everything
in variables.

Furthermore, replace() only handles fixed strings, and does not have
any capacbilities to find patterns.

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

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

Thanks for the quick response. Please my further question below.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941BEC43659B9Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> > Don't worry about the vars, they are defined,
> > the following line give me an err of "Incorrect syntax near '.'."
> > Goal: to rename nonstardard column name.
> > EXEC sp_rename '+@.tbuffer+'.['+@.cbuffer+']','+Replace(+@.cbuffer+','%[^A-Za-z0-9_#$@.]%','')',
> > 'COLUMN';
> You can only pass constants and variables as parameters to stored procedures.
> You cannot pass an expression as a parameter, but you must put everything
> in variables.
Are you saying I should do something like
set @.tbuffer = '''+@.tbuffer+''';
set @.cbuffer = '''+@.cbuffer+''';
and then
EXEC sp_rename @.tbuffer+'.['+@.cbuffer+']' ...

> Furthermore, replace() only handles fixed strings, and does not have
> any capacbilities to find patterns.
Since replace can't do the job for this case, what other option do I
have to remove the non-standard character(s), I've looked at charindex
and stuff function, could they fit in here or a better way to do it?|||Doug Baroter (qwert12345@.boxfrog.com) writes:
> Are you saying I should do something like
> set @.tbuffer = '''+@.tbuffer+''';
> set @.cbuffer = '''+@.cbuffer+''';
> and then
> EXEC sp_rename @.tbuffer+'.['+@.cbuffer+']' ...

Rather:

SELECT @.old_name = 'tbl.' + <whatever>
SELECT @.new_name = <whichever>
EXEC sp_rename @.old_name, @.new_name, 'column'

> Since replace can't do the job for this case, what other option do I
> have to remove the non-standard character(s), I've looked at charindex
> and stuff function, could they fit in here or a better way to do it?

Unfortunately, SQL is poor for this task. You are probably better off
if you write some program in Perl, Visual Basic, VBscript, C or whatever
is your favourite client language.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks, Erland, pls see below.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941D8E88A84Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> > Are you saying I should do something like
> > set @.tbuffer = '''+@.tbuffer+''';
> > set @.cbuffer = '''+@.cbuffer+''';
> > and then
> > EXEC sp_rename @.tbuffer+'.['+@.cbuffer+']' ...
> Rather:

> SELECT @.old_name = 'tbl.' + <whatever>
> SELECT @.new_name = <whichever>
> EXEC sp_rename @.old_name, @.new_name, 'column'
Unfortunately the tbl name is dynamically determined. But as you
recommended below, I just use a non-SQL language to take care of the
whole problem except one minor one, that is, I haven't got the RegExp
part fully completed.

> > Since replace can't do the job for this case, what other option do I
> > have to remove the non-standard character(s), I've looked at charindex
> > and stuff function, could they fit in here or a better way to do it?
> Unfortunately, SQL is poor for this task. You are probably better off
> if you write some program in Perl, Visual Basic, VBscript, C or whatever
> is your favourite client language.|||Doug Baroter (qwert12345@.boxfrog.com) writes:
>> SELECT @.old_name = 'tbl.' + <whatever>
>> SELECT @.new_name = <whichever>
>> EXEC sp_rename @.old_name, @.new_name, 'column'
> Unfortunately the tbl name is dynamically determined.

SELECT @.old_name = @.tbl + '.' + @.column

The point is that when you come to sp_rename you must have a single
value.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||OK. Thanks.
Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns941D616A4DA25Yazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.boxfrog.com) writes:
> >> SELECT @.old_name = 'tbl.' + <whatever>
> >> SELECT @.new_name = <whichever>
> >> EXEC sp_rename @.old_name, @.new_name, 'column'
> > Unfortunately the tbl name is dynamically determined.
> SELECT @.old_name = @.tbl + '.' + @.column
> The point is that when you come to sp_rename you must have a single
> value.

I'll be posible process a bulk load for following xml document?

the xml doc is:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="entity" sql:relation="ENTITY">
<xs:complexType mixed="true">
<xs:sequence minOccurs="0" maxOccurs="unbounded">
<xs:element ref="entity"/>
</xs:sequence>
<xs:attribute name="name" use="required" sql:field="NAME"/>
<xs:attribute name="descripcion" use="required" sql:field="DESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ADDRESS"/>
</xs:complexType>
</xs:element>
<xs:element name="entities" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="entity" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

the table is:

TABLE [dbo].[ENTITY](
[ENTITYNAME] [varchar](50) NULL,
[ENTITYDESCRIPCION] [varchar](200) NULL,
[ENTITYADRESS] [varchar](50) NULL,
) ON [PRIMARY]

GO

SOMEBODY COULD DO IT ?

Yes this shape would be possible to bulkload. However the column names you have specified with sql:field do not match the columns in the database, so if you are having trouble with this schema, that might be one possibility.

<xs:attribute name="name" use="required" sql:field="NAME"/>
<xs:attribute name="descripcion" use="required" sql:field="DESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ADDRESS"/>

look like they should be:

<xs:attribute name="name" use="required" sql:field="ENTITYNAME"/>
<xs:attribute name="descripcion" use="required" sql:field="ENTITYDESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ENTITYADRESS"/>

-Todd

I'll be posible process a bulk load for following xml document?

the xml doc is:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="entity" sql:relation="ENTITY">
<xs:complexType mixed="true">
<xs:sequence minOccurs="0" maxOccurs="unbounded">
<xs:element ref="entity"/>
</xs:sequence>
<xs:attribute name="name" use="required" sql:field="NAME"/>
<xs:attribute name="descripcion" use="required" sql:field="DESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ADDRESS"/>
</xs:complexType>
</xs:element>
<xs:element name="entities" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="entity" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

the table is:

TABLE [dbo].[ENTITY](
[ENTITYNAME] [varchar](50) NULL,
[ENTITYDESCRIPCION] [varchar](200) NULL,
[ENTITYADRESS] [varchar](50) NULL,
) ON [PRIMARY]

GO

SOMEBODY COULD DO IT ?

Yes this shape would be possible to bulkload. However the column names you have specified with sql:field do not match the columns in the database, so if you are having trouble with this schema, that might be one possibility.

<xs:attribute name="name" use="required" sql:field="NAME"/>
<xs:attribute name="descripcion" use="required" sql:field="DESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ADDRESS"/>

look like they should be:

<xs:attribute name="name" use="required" sql:field="ENTITYNAME"/>
<xs:attribute name="descripcion" use="required" sql:field="ENTITYDESCRIPCION"/>
<xs:attribute name="address" use="required" sql:field="ENTITYADRESS"/>

-Todd

IIS/MS SQL/ASP... cant connect

I have an ASP script with the following code in the global.asa:

Application("DBConnectString")="DSN=Test_Server;UID=Michael;PWD=xxxx"

But I get the error:

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
/TheResort/i_asputils.asp, line 8

Problem:

I setup the ODBC portion and it correctly saw the MS SQL server, and the test connection worked fine. Yet when I ever I enter that ODBC name "Test_Server", and the password of the Win XP Pro user I am signed in as, it fails to connect.

How can I be sure what domain,user,password I need to enter?Ttry using "DSN=asset;UID=;PWD=" and don't forget to insert the user id and password.

Also, when you set up the odbc "system" dsn, remember to click on the test connection button to verify the connection.

http://www.google.com/url?sa=U&start=9&q=http://support.microsoft.com/support/kb/articles/Q169/3/77.asp&e=7415 information.

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?
The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>
|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
Ekrem nsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
Ekrem nsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS server set up for .net app

Hi,
I use windows authentication for .net app to connection to sql 2k server.
I got the following error --(Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection)
Connection string -- server=sql serv1;database=mydb;trusted_connection
= true
appserver = app1
Any ideas?The idea is that the windows user account used to run your application
(Window Form app or ASP.NET app?) does not have a mapped SQL Server login
created in the SQL Server, thus cannot log into SQL Server.
You need to create a SQL Server login that mapps to that windows user
account or a Windows user security group, which includes that user account
as member. Then you need to add this SQL Server login to target database as
user, then assign appropriate permissions.
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>|||Ensure that the Windows Account that you work your app under is added to the
domain and it has a valid Login in your SQL Server.
If the Windows user account is not in your domain and you use Trusted
Connections then you get this error.
--
Ekrem Önsoy
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23ygmjY3OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I use windows authentication for .net app to connection to sql 2k server.
> I got the following error --(Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection)
> Connection string -- server=sql
> serv1;database=mydb;trusted_connection = true
> appserver = app1
> Any ideas?
>

IIS issue

I just installed IIS on my windows xp pro machine and when I try to access a
website on the local IIS server, I get the following error message:

"You are not authorized to view this page"

I have enabled "Anonymous access" inside Directory Security option of IIS
and I also granted full NTFS permissions to all users so that the IIS root
directory is readily accessible. However, I am still not able to access any
website that resides on the localhost.

Please help.

Thanks.
JessIs this a SQL Server question?

IIS error on New Subscription

hi. i was following the newbie's guide: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp?frame=true

on the creating a new subscription i was doing okay until i get to the last part wherein i click finish and this error pops up when it starts synchronizing data..

An error has occured on the computer running IIS. Try restarting the IIS server.

i already tried restarting the server.. is there anything else i can do to make it work?

Nijen,

There are many errors that can occur when synchronizing merge replication - the first thing you want to do is get as much information as possible and a specific error code. Place your call to repl.Synchronize() in a try/catch block and catch the specific exception that the SQL Mobile Client Agent throws during the replication. To traverse the SqlCe Exception object and extract the message information, use this method:

try{ repl.Synchronize(); }

catch (SqlCeException sqlex) { DisplaySQLCEErrors(sqlex);}

////////

public void DisplaySQLCEErrors(SqlCeException ex)
{
SqlCeErrorCollection errorCollection = ex.Errors;

StringBuilder bld = new StringBuilder();
Exception inner = ex.InnerException;

foreach (SqlCeError err in errorCollection)
{
bld.Append("\n Error Code: " + err.HResult.ToString("X"));
bld.Append("\n Message : " + err.Message);
bld.Append("\n Minor Err.: " + err.NativeError);
bld.Append("\n Source : " + err.Source);

foreach (int numPar in err.NumericErrorParameters)
{
if ( 0 != numPar ) bld.Append( "\n Num. Par. : " + numPar );
}

foreach ( string errPar in err.ErrorParameters )
{
if ( String.Empty != errPar ) bld.Append( "\n Err. Par. : " + errPar );
}

MessageBox.Show( bld.ToString(), "SQL Server CE Error" );
}
}

Once you have the specific error code, you can troubleshoot your replication.

http://msdn2.microsoft.com/en-us/library/ms171849(SQL.90).aspx.

-Darren

|||

Darren,

do you have coding for catch error in VBNet?

I've got An error has occured on the computer running IIS when I want to replicate my PDA to PC.. i'm using SQL Server 2000, VsNet 2003

|||

Here is the code in VB.NET:

Public Sub DisplaySQLCEErrors(ByVal ex As SqlCeException)

Dim errorCollection As SqlCeErrorCollection = ex.Errors
Dim bld As New StringBuilder()
Dim inner As Exception = ex.InnerException
Dim err As SqlCeError

For Each err In errorCollection

bld.Append(ControlChars.Lf + " Error Code: " + err.HResult.ToString())
bld.Append(ControlChars.Lf + " Message : " + err.Message)
bld.Append(ControlChars.Lf + " Minor Err.: " + err.NativeError.ToString())
bld.Append(ControlChars.Lf + " Source : " + err.Source)

Dim numPar As Integer
For Each numPar In err.NumericErrorParameters
If (numPar <> 0) Then
bld.Append(ControlChars.Lf + " Num. Par. : " + numPar.ToString())
End If
Next numPar
Dim errPar As String
For Each errPar In err.ErrorParameters
If (errPar <> String.Empty) Then
bld.Append(ControlChars.Lf + " Err. Par. : " + errPar)
End If
Next errPar

MessageBox.Show(bld.ToString(), "SQL Mobile Error")

Next err

End Sub

-Darren

|||

Thanks Darren,

But why I've got error at line

Dim bld As New StringBuilder -- Type 'StringBuilder' is not defined.

|||

:D I've to Imports System.Text..

THANKSSSS!!!

IIS error on New Subscription

hi. i was following the newbie's guide: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp?frame=true

on the creating a new subscription i was doing okay until i get to the last part wherein i click finish and this error pops up when it starts synchronizing data..

An error has occured on the computer running IIS. Try restarting the IIS server.

i already tried restarting the server.. is there anything else i can do to make it work?

Nijen,

There are many errors that can occur when synchronizing merge replication - the first thing you want to do is get as much information as possible and a specific error code. Place your call to repl.Synchronize() in a try/catch block and catch the specific exception that the SQL Mobile Client Agent throws during the replication. To traverse the SqlCe Exception object and extract the message information, use this method:

try{ repl.Synchronize(); }

catch (SqlCeException sqlex) { DisplaySQLCEErrors(sqlex);}

////////

public void DisplaySQLCEErrors(SqlCeException ex)
{
SqlCeErrorCollection errorCollection = ex.Errors;

StringBuilder bld = new StringBuilder();
Exception inner = ex.InnerException;

foreach (SqlCeError err in errorCollection)
{
bld.Append("\n Error Code: " + err.HResult.ToString("X"));
bld.Append("\n Message : " + err.Message);
bld.Append("\n Minor Err.: " + err.NativeError);
bld.Append("\n Source : " + err.Source);

foreach (int numPar in err.NumericErrorParameters)
{
if ( 0 != numPar ) bld.Append( "\n Num. Par. : " + numPar );
}

foreach ( string errPar in err.ErrorParameters )
{
if ( String.Empty != errPar ) bld.Append( "\n Err. Par. : " + errPar );
}

MessageBox.Show( bld.ToString(), "SQL Server CE Error" );
}
}

Once you have the specific error code, you can troubleshoot your replication.

http://msdn2.microsoft.com/en-us/library/ms171849(SQL.90).aspx.

-Darren

|||

Darren,

do you have coding for catch error in VBNet?

I've got An error has occured on the computer running IIS when I want to replicate my PDA to PC.. i'm using SQL Server 2000, VsNet 2003

|||

Here is the code in VB.NET:

Public Sub DisplaySQLCEErrors(ByVal ex As SqlCeException)

Dim errorCollection As SqlCeErrorCollection = ex.Errors
Dim bld As New StringBuilder()
Dim inner As Exception = ex.InnerException
Dim err As SqlCeError

For Each err In errorCollection

bld.Append(ControlChars.Lf + " Error Code: " + err.HResult.ToString())
bld.Append(ControlChars.Lf + " Message : " + err.Message)
bld.Append(ControlChars.Lf + " Minor Err.: " + err.NativeError.ToString())
bld.Append(ControlChars.Lf + " Source : " + err.Source)

Dim numPar As Integer
For Each numPar In err.NumericErrorParameters
If (numPar <> 0) Then
bld.Append(ControlChars.Lf + " Num. Par. : " + numPar.ToString())
End If
Next numPar
Dim errPar As String
For Each errPar In err.ErrorParameters
If (errPar <> String.Empty) Then
bld.Append(ControlChars.Lf + " Err. Par. : " + errPar)
End If
Next errPar

MessageBox.Show(bld.ToString(), "SQL Mobile Error")

Next err

End Sub

-Darren

|||

Thanks Darren,

But why I've got error at line

Dim bld As New StringBuilder -- Type 'StringBuilder' is not defined.

|||

:D I've to Imports System.Text..

THANKSSSS!!!

IIS and Internet Access

Hi All,
I have the following going on: A win 2k3 server running sql server and
reporting services. I have IIS configured on it and can run reports
internally on my network but when I try to access a report from the web I get
nothing back. I can access my web projects but cant access and display
reports from web. I am using a static ip address to access the website for
now. I don't have a domain name setup for this box yet if that makes a
difference. and internally when I refer to the box in the website I use the
boxs computer name. Any ideas on what could be wrong here?
Thanks,
JJThis topic might help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/drp_deploying_v1_0h9e.asp
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"JJ" <JJ@.discussions.microsoft.com> wrote in message
news:9B7DEDB4-0940-4FED-B6C9-0F05CCDBEF43@.microsoft.com...
> Hi All,
> I have the following going on: A win 2k3 server running sql server and
> reporting services. I have IIS configured on it and can run reports
> internally on my network but when I try to access a report from the web I
> get
> nothing back. I can access my web projects but cant access and display
> reports from web. I am using a static ip address to access the website for
> now. I don't have a domain name setup for this box yet if that makes a
> difference. and internally when I refer to the box in the website I use
> the
> boxs computer name. Any ideas on what could be wrong here?
> Thanks,
> JJ|||Hi,
Did you arrange security settings for the browsing reports?
You should grant "Browser" Role to Guests or everyone from the Report
Manager to the folder where your reports are.
Eralper
http://www.kodyaz.com
"JJ" wrote:
> Hi All,
> I have the following going on: A win 2k3 server running sql server and
> reporting services. I have IIS configured on it and can run reports
> internally on my network but when I try to access a report from the web I get
> nothing back. I can access my web projects but cant access and display
> reports from web. I am using a static ip address to access the website for
> now. I don't have a domain name setup for this box yet if that makes a
> difference. and internally when I refer to the box in the website I use the
> boxs computer name. Any ideas on what could be wrong here?
> Thanks,
> JJ|||Hi eralper,
Man I am so feed up trying to get Reporting Services to be accessed from
the web. I have windows 2003 server running Sql Server 2000 with Reporting
Services sp2 installed. I can see and execute the reports through Report
Manager with no problem internally. But when I have a buddy check out the
website using an IP address. He gets nothing. I have setup roles for
IUSR_Account as browser role and assigned the report trying to access to the
security property under report. In the datasource for report I have included
the user name and password that matchs a windows account mapped to a Sql
server user account. No problem there. How do you have IIS setup under
Directory securities for Report and Report Manager set?
I don't have a domain name setup for this server yet. I am using Static IP
address for the moment. Are you using SSL for Reporting Services? What else
should I check?
Thanks,
JJ
"eralper" wrote:
> Hi,
> Did you arrange security settings for the browsing reports?
> You should grant "Browser" Role to Guests or everyone from the Report
> Manager to the folder where your reports are.
> Eralper
> http://www.kodyaz.com
> "JJ" wrote:
> > Hi All,
> >
> > I have the following going on: A win 2k3 server running sql server and
> > reporting services. I have IIS configured on it and can run reports
> > internally on my network but when I try to access a report from the web I get
> > nothing back. I can access my web projects but cant access and display
> > reports from web. I am using a static ip address to access the website for
> > now. I don't have a domain name setup for this box yet if that makes a
> > difference. and internally when I refer to the box in the website I use the
> > boxs computer name. Any ideas on what could be wrong here?
> >
> > Thanks,
> >
> > JJ|||Hi JJ,
I tried to give a detailed answer on
http://www.kodyaz.com/ShowPost.aspx?PostID=19
I hope it helps.
Eralper
http://www.kodyaz.com
"JJ" wrote:
> Hi eralper,
> Man I am so feed up trying to get Reporting Services to be accessed from
> the web. I have windows 2003 server running Sql Server 2000 with Reporting
> Services sp2 installed. I can see and execute the reports through Report
> Manager with no problem internally. But when I have a buddy check out the
> website using an IP address. He gets nothing. I have setup roles for
> IUSR_Account as browser role and assigned the report trying to access to the
> security property under report. In the datasource for report I have included
> the user name and password that matchs a windows account mapped to a Sql
> server user account. No problem there. How do you have IIS setup under
> Directory securities for Report and Report Manager set?
> I don't have a domain name setup for this server yet. I am using Static IP
> address for the moment. Are you using SSL for Reporting Services? What else
> should I check?
> Thanks,
> JJ
>
> "eralper" wrote:
> >
> > Hi,
> >
> > Did you arrange security settings for the browsing reports?
> >
> > You should grant "Browser" Role to Guests or everyone from the Report
> > Manager to the folder where your reports are.
> >
> > Eralper
> > http://www.kodyaz.com
> >
> > "JJ" wrote:
> >
> > > Hi All,
> > >
> > > I have the following going on: A win 2k3 server running sql server and
> > > reporting services. I have IIS configured on it and can run reports
> > > internally on my network but when I try to access a report from the web I get
> > > nothing back. I can access my web projects but cant access and display
> > > reports from web. I am using a static ip address to access the website for
> > > now. I don't have a domain name setup for this box yet if that makes a
> > > difference. and internally when I refer to the box in the website I use the
> > > boxs computer name. Any ideas on what could be wrong here?
> > >
> > > Thanks,
> > >
> > > JJ|||Hi Eralper,
I tried to access your website but I keep on getting Obj ref not set
error. This is happening even when I enter www. kodyak.com.
Thanks,
JJ
"eralper" wrote:
> Hi JJ,
> I tried to give a detailed answer on
> http://www.kodyaz.com/ShowPost.aspx?PostID=19
> I hope it helps.
> Eralper
> http://www.kodyaz.com
>
> "JJ" wrote:
> > Hi eralper,
> >
> > Man I am so feed up trying to get Reporting Services to be accessed from
> > the web. I have windows 2003 server running Sql Server 2000 with Reporting
> > Services sp2 installed. I can see and execute the reports through Report
> > Manager with no problem internally. But when I have a buddy check out the
> > website using an IP address. He gets nothing. I have setup roles for
> > IUSR_Account as browser role and assigned the report trying to access to the
> > security property under report. In the datasource for report I have included
> > the user name and password that matchs a windows account mapped to a Sql
> > server user account. No problem there. How do you have IIS setup under
> > Directory securities for Report and Report Manager set?
> >
> > I don't have a domain name setup for this server yet. I am using Static IP
> > address for the moment. Are you using SSL for Reporting Services? What else
> > should I check?
> >
> > Thanks,
> >
> > JJ
> >
> >
> > "eralper" wrote:
> >
> > >
> > > Hi,
> > >
> > > Did you arrange security settings for the browsing reports?
> > >
> > > You should grant "Browser" Role to Guests or everyone from the Report
> > > Manager to the folder where your reports are.
> > >
> > > Eralper
> > > http://www.kodyaz.com
> > >
> > > "JJ" wrote:
> > >
> > > > Hi All,
> > > >
> > > > I have the following going on: A win 2k3 server running sql server and
> > > > reporting services. I have IIS configured on it and can run reports
> > > > internally on my network but when I try to access a report from the web I get
> > > > nothing back. I can access my web projects but cant access and display
> > > > reports from web. I am using a static ip address to access the website for
> > > > now. I don't have a domain name setup for this box yet if that makes a
> > > > difference. and internally when I refer to the box in the website I use the
> > > > boxs computer name. Any ideas on what could be wrong here?
> > > >
> > > > Thanks,
> > > >
> > > > JJ|||Hi,
Actually my configuration differs from yours with my web server is IIS5
working on Win2k. But I believe this will not cause a problem for anonymous
access to reports after the steps I'll try to summarize below.
Considering the site security, I'm not sure this is the best practice for
reporting services application but it seems working.
First you can view and run reports without problem because default
authentication method for virtual directories "Reports" and "ReportServer" is
window authentication. And I think you browse the reports with an admin group
user.
With these default configurations of the setup of Reporting Services, your
buddy should be able to get the username and password screen for windows
authentication when he calls http://YourIPComesHere/Reports
If you change IIS permissions for these two virtual directories (Reports &
ReportServer), your buddy should see an empty page because the internet guest
account has no browser role permission on none of the folders below home
(pointing VS.Net Reporting Services projects) or the individual reports in
those folders.
So before switching to anonymous access on these virtual directories, you
should grant Browser role permission to "everyone" in approtiate folders and
reports.
I used "everyone", I do not know if it makes sense for IUSR_ComputerName
So first go to Home directory (http://YourServer/Reports). Select Properties
tab for Home and hit the "New Role Assignment" button to add "everyone" as
Browser. By doing this, anonymous users will be able to list contents of the
Reports folder.
Second, select "Detailed View" and "Edit" the report folder where your
reports are deployed. Go to Security tab and add "everyone" as Browser.
I'm not sure but you may need to repeat a similar step (adding everyone as
Browser) for the reports.
You should hide the datasources in the list by editing their properties.
Check the "hide in list view" option in General tab.
One important note: you must edit RSWebApplication config file in
"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportManager" folder (default place for this configuration file)
Update ReportServerURL by changing the computer name with your IP. Otherwise
users will be able reach the report but will not be able to see it processing.
Now is time for changing the permissions of the two virtual directories
Reports and ReportServer via IIS Manager. Go to properties of the virtual
directories then select directory security tab. And allow anonymous access.
This configuration works for me. I hope you may find something useful within
my notes.
Eralper
http://www.kodyaz.com
"JJ" wrote:
> Hi Eralper,
> I tried to access your website but I keep on getting Obj ref not set
> error. This is happening even when I enter www. kodyak.com.
> Thanks,
> JJ
> "eralper" wrote:
> > Hi JJ,
> >
> > I tried to give a detailed answer on
> > http://www.kodyaz.com/ShowPost.aspx?PostID=19
> >
> > I hope it helps.
> >
> > Eralper
> > http://www.kodyaz.com
> >
> >
> > "JJ" wrote:
> >
> > > Hi eralper,
> > >
> > > Man I am so feed up trying to get Reporting Services to be accessed from
> > > the web. I have windows 2003 server running Sql Server 2000 with Reporting
> > > Services sp2 installed. I can see and execute the reports through Report
> > > Manager with no problem internally. But when I have a buddy check out the
> > > website using an IP address. He gets nothing. I have setup roles for
> > > IUSR_Account as browser role and assigned the report trying to access to the
> > > security property under report. In the datasource for report I have included
> > > the user name and password that matchs a windows account mapped to a Sql
> > > server user account. No problem there. How do you have IIS setup under
> > > Directory securities for Report and Report Manager set?
> > >
> > > I don't have a domain name setup for this server yet. I am using Static IP
> > > address for the moment. Are you using SSL for Reporting Services? What else
> > > should I check?
> > >
> > > Thanks,
> > >
> > > JJ
> > >
> > >
> > > "eralper" wrote:
> > >
> > > >
> > > > Hi,
> > > >
> > > > Did you arrange security settings for the browsing reports?
> > > >
> > > > You should grant "Browser" Role to Guests or everyone from the Report
> > > > Manager to the folder where your reports are.
> > > >
> > > > Eralper
> > > > http://www.kodyaz.com
> > > >
> > > > "JJ" wrote:
> > > >
> > > > > Hi All,
> > > > >
> > > > > I have the following going on: A win 2k3 server running sql server and
> > > > > reporting services. I have IIS configured on it and can run reports
> > > > > internally on my network but when I try to access a report from the web I get
> > > > > nothing back. I can access my web projects but cant access and display
> > > > > reports from web. I am using a static ip address to access the website for
> > > > > now. I don't have a domain name setup for this box yet if that makes a
> > > > > difference. and internally when I refer to the box in the website I use the
> > > > > boxs computer name. Any ideas on what could be wrong here?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > JJ|||When you mean grant access to everyone do you mean use IUSR_Compname account
and assign to folders.
By the way I really like your website! Any ideas on what's causing the
error? Would like to access it again. Did you build it yourself?
Thanks,
JJ
"eralper" wrote:
> Hi,
> Actually my configuration differs from yours with my web server is IIS5
> working on Win2k. But I believe this will not cause a problem for anonymous
> access to reports after the steps I'll try to summarize below.
> Considering the site security, I'm not sure this is the best practice for
> reporting services application but it seems working.
> First you can view and run reports without problem because default
> authentication method for virtual directories "Reports" and "ReportServer" is
> window authentication. And I think you browse the reports with an admin group
> user.
> With these default configurations of the setup of Reporting Services, your
> buddy should be able to get the username and password screen for windows
> authentication when he calls http://YourIPComesHere/Reports
> If you change IIS permissions for these two virtual directories (Reports &
> ReportServer), your buddy should see an empty page because the internet guest
> account has no browser role permission on none of the folders below home
> (pointing VS.Net Reporting Services projects) or the individual reports in
> those folders.
> So before switching to anonymous access on these virtual directories, you
> should grant Browser role permission to "everyone" in approtiate folders and
> reports.
> I used "everyone", I do not know if it makes sense for IUSR_ComputerName
> So first go to Home directory (http://YourServer/Reports). Select Properties
> tab for Home and hit the "New Role Assignment" button to add "everyone" as
> Browser. By doing this, anonymous users will be able to list contents of the
> Reports folder.
> Second, select "Detailed View" and "Edit" the report folder where your
> reports are deployed. Go to Security tab and add "everyone" as Browser.
> I'm not sure but you may need to repeat a similar step (adding everyone as
> Browser) for the reports.
> You should hide the datasources in the list by editing their properties.
> Check the "hide in list view" option in General tab.
> One important note: you must edit RSWebApplication config file in
> "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportManager" folder (default place for this configuration file)
> Update ReportServerURL by changing the computer name with your IP. Otherwise
> users will be able reach the report but will not be able to see it processing.
> Now is time for changing the permissions of the two virtual directories
> Reports and ReportServer via IIS Manager. Go to properties of the virtual
> directories then select directory security tab. And allow anonymous access.
>
> This configuration works for me. I hope you may find something useful within
> my notes.
> Eralper
> http://www.kodyaz.com
> "JJ" wrote:
> > Hi Eralper,
> >
> > I tried to access your website but I keep on getting Obj ref not set
> > error. This is happening even when I enter www. kodyak.com.
> >
> > Thanks,
> >
> > JJ
> >
> > "eralper" wrote:
> >
> > > Hi JJ,
> > >
> > > I tried to give a detailed answer on
> > > http://www.kodyaz.com/ShowPost.aspx?PostID=19
> > >
> > > I hope it helps.
> > >
> > > Eralper
> > > http://www.kodyaz.com
> > >
> > >
> > > "JJ" wrote:
> > >
> > > > Hi eralper,
> > > >
> > > > Man I am so feed up trying to get Reporting Services to be accessed from
> > > > the web. I have windows 2003 server running Sql Server 2000 with Reporting
> > > > Services sp2 installed. I can see and execute the reports through Report
> > > > Manager with no problem internally. But when I have a buddy check out the
> > > > website using an IP address. He gets nothing. I have setup roles for
> > > > IUSR_Account as browser role and assigned the report trying to access to the
> > > > security property under report. In the datasource for report I have included
> > > > the user name and password that matchs a windows account mapped to a Sql
> > > > server user account. No problem there. How do you have IIS setup under
> > > > Directory securities for Report and Report Manager set?
> > > >
> > > > I don't have a domain name setup for this server yet. I am using Static IP
> > > > address for the moment. Are you using SSL for Reporting Services? What else
> > > > should I check?
> > > >
> > > > Thanks,
> > > >
> > > > JJ
> > > >
> > > >
> > > > "eralper" wrote:
> > > >
> > > > >
> > > > > Hi,
> > > > >
> > > > > Did you arrange security settings for the browsing reports?
> > > > >
> > > > > You should grant "Browser" Role to Guests or everyone from the Report
> > > > > Manager to the folder where your reports are.
> > > > >
> > > > > Eralper
> > > > > http://www.kodyaz.com
> > > > >
> > > > > "JJ" wrote:
> > > > >
> > > > > > Hi All,
> > > > > >
> > > > > > I have the following going on: A win 2k3 server running sql server and
> > > > > > reporting services. I have IIS configured on it and can run reports
> > > > > > internally on my network but when I try to access a report from the web I get
> > > > > > nothing back. I can access my web projects but cant access and display
> > > > > > reports from web. I am using a static ip address to access the website for
> > > > > > now. I don't have a domain name setup for this box yet if that makes a
> > > > > > difference. and internally when I refer to the box in the website I use the
> > > > > > boxs computer name. Any ideas on what could be wrong here?
> > > > > >
> > > > > > Thanks,
> > > > > >
> > > > > > JJ|||Hi,
I wrote "everyone" which will include "IUSR_Compname" also in the textbox
while defining a new role for folders. But I believe "IUSR_Compname" will
also work.
Thanks for your appreciation. One of my friends also said he had a problem
after the first use of the site. I will be checking what is going on and
inform you :)
"JJ" wrote:
> When you mean grant access to everyone do you mean use IUSR_Compname account
> and assign to folders.
> By the way I really like your website! Any ideas on what's causing the
> error? Would like to access it again. Did you build it yourself?
> Thanks,
> JJ
> "eralper" wrote:
> > Hi,
> >
> > Actually my configuration differs from yours with my web server is IIS5
> > working on Win2k. But I believe this will not cause a problem for anonymous
> > access to reports after the steps I'll try to summarize below.
> >
> > Considering the site security, I'm not sure this is the best practice for
> > reporting services application but it seems working.
> >
> > First you can view and run reports without problem because default
> > authentication method for virtual directories "Reports" and "ReportServer" is
> > window authentication. And I think you browse the reports with an admin group
> > user.
> >
> > With these default configurations of the setup of Reporting Services, your
> > buddy should be able to get the username and password screen for windows
> > authentication when he calls http://YourIPComesHere/Reports
> >
> > If you change IIS permissions for these two virtual directories (Reports &
> > ReportServer), your buddy should see an empty page because the internet guest
> > account has no browser role permission on none of the folders below home
> > (pointing VS.Net Reporting Services projects) or the individual reports in
> > those folders.
> >
> > So before switching to anonymous access on these virtual directories, you
> > should grant Browser role permission to "everyone" in approtiate folders and
> > reports.
> > I used "everyone", I do not know if it makes sense for IUSR_ComputerName
> >
> > So first go to Home directory (http://YourServer/Reports). Select Properties
> > tab for Home and hit the "New Role Assignment" button to add "everyone" as
> > Browser. By doing this, anonymous users will be able to list contents of the
> > Reports folder.
> >
> > Second, select "Detailed View" and "Edit" the report folder where your
> > reports are deployed. Go to Security tab and add "everyone" as Browser.
> >
> > I'm not sure but you may need to repeat a similar step (adding everyone as
> > Browser) for the reports.
> >
> > You should hide the datasources in the list by editing their properties.
> > Check the "hide in list view" option in General tab.
> >
> > One important note: you must edit RSWebApplication config file in
> > "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> > Services\ReportManager" folder (default place for this configuration file)
> > Update ReportServerURL by changing the computer name with your IP. Otherwise
> > users will be able reach the report but will not be able to see it processing.
> >
> > Now is time for changing the permissions of the two virtual directories
> > Reports and ReportServer via IIS Manager. Go to properties of the virtual
> > directories then select directory security tab. And allow anonymous access.
> >
> >
> > This configuration works for me. I hope you may find something useful within
> > my notes.
> >
> > Eralper
> > http://www.kodyaz.com
> >
> > "JJ" wrote:
> >
> > > Hi Eralper,
> > >
> > > I tried to access your website but I keep on getting Obj ref not set
> > > error. This is happening even when I enter www. kodyak.com.
> > >
> > > Thanks,
> > >
> > > JJ
> > >
> > > "eralper" wrote:
> > >
> > > > Hi JJ,
> > > >
> > > > I tried to give a detailed answer on
> > > > http://www.kodyaz.com/ShowPost.aspx?PostID=19
> > > >
> > > > I hope it helps.
> > > >
> > > > Eralper
> > > > http://www.kodyaz.com
> > > >
> > > >
> > > > "JJ" wrote:
> > > >
> > > > > Hi eralper,
> > > > >
> > > > > Man I am so feed up trying to get Reporting Services to be accessed from
> > > > > the web. I have windows 2003 server running Sql Server 2000 with Reporting
> > > > > Services sp2 installed. I can see and execute the reports through Report
> > > > > Manager with no problem internally. But when I have a buddy check out the
> > > > > website using an IP address. He gets nothing. I have setup roles for
> > > > > IUSR_Account as browser role and assigned the report trying to access to the
> > > > > security property under report. In the datasource for report I have included
> > > > > the user name and password that matchs a windows account mapped to a Sql
> > > > > server user account. No problem there. How do you have IIS setup under
> > > > > Directory securities for Report and Report Manager set?
> > > > >
> > > > > I don't have a domain name setup for this server yet. I am using Static IP
> > > > > address for the moment. Are you using SSL for Reporting Services? What else
> > > > > should I check?
> > > > >
> > > > > Thanks,
> > > > >
> > > > > JJ
> > > > >
> > > > >
> > > > > "eralper" wrote:
> > > > >
> > > > > >
> > > > > > Hi,
> > > > > >
> > > > > > Did you arrange security settings for the browsing reports?
> > > > > >
> > > > > > You should grant "Browser" Role to Guests or everyone from the Report
> > > > > > Manager to the folder where your reports are.
> > > > > >
> > > > > > Eralper
> > > > > > http://www.kodyaz.com
> > > > > >
> > > > > > "JJ" wrote:
> > > > > >
> > > > > > > Hi All,
> > > > > > >
> > > > > > > I have the following going on: A win 2k3 server running sql server and
> > > > > > > reporting services. I have IIS configured on it and can run reports
> > > > > > > internally on my network but when I try to access a report from the web I get
> > > > > > > nothing back. I can access my web projects but cant access and display
> > > > > > > reports from web. I am using a static ip address to access the website for
> > > > > > > now. I don't have a domain name setup for this box yet if that makes a
> > > > > > > difference. and internally when I refer to the box in the website I use the
> > > > > > > boxs computer name. Any ideas on what could be wrong here?
> > > > > > >
> > > > > > > Thanks,
> > > > > > >
> > > > > > > JJ

IIF Statement with Date checking

Hello, I am using the following to try to find date ranges and do some work.
Here is the statement
=iif(fields!mbr_join_date.value = "7/01/2003", mbt_code, "Not Grandfathered")
I have looked at the query result and I see alot of dates that are this date
but it's not putting the MBT_Code in for the value. Anyone have any
suggestions. Should I convert the Date in the Query to a different format
currently it shows as yyyy-mm-dd 00:00:00 should I trim off the time or
convert it to a string value?
Thank you in advanceScrocker,
I'm not 100% sure if your doing this in the SQL or in a cell on the
report. If its in the report you need to write it like:
iif(fields!mbr_join_date.value = "7/01/2003", FIELDS!mbt_cod.VALUE, "Not
Grandfathered")
You can't reference the field name in a control without usin gthe FIELDS!
collection.
If its in your SQL, well, IIF's dont' work, so I'm thinking your not doing
it, but if you are then use a CASE statement
CASE WHEN mbr_join_date = '7/01/2003' THEN mbr_code ELSE 'Not Grandfathered'
END as mbr_code
Michael C
"scrocker" wrote:
> Hello, I am using the following to try to find date ranges and do some work.
> Here is the statement
> =iif(fields!mbr_join_date.value = "7/01/2003", mbt_code, "Not Grandfathered")
> I have looked at the query result and I see alot of dates that are this date
> but it's not putting the MBT_Code in for the value. Anyone have any
> suggestions. Should I convert the Date in the Query to a different format
> currently it shows as yyyy-mm-dd 00:00:00 should I trim off the time or
> convert it to a string value?
> Thank you in advance
>|||Thanks Michael, I will give that a shot, Yes it's in the report not in SQL
Michael C wrote:
>Scrocker,
> I'm not 100% sure if your doing this in the SQL or in a cell on the
>report. If its in the report you need to write it like:
>iif(fields!mbr_join_date.value = "7/01/2003", FIELDS!mbt_cod.VALUE, "Not
>Grandfathered")
>You can't reference the field name in a control without usin gthe FIELDS!
>collection.
>If its in your SQL, well, IIF's dont' work, so I'm thinking your not doing
>it, but if you are then use a CASE statement
>CASE WHEN mbr_join_date = '7/01/2003' THEN mbr_code ELSE 'Not Grandfathered'
>END as mbr_code
>Michael C
>> Hello, I am using the following to try to find date ranges and do some work.
>> Here is the statement
>[quoted text clipped - 7 lines]
>> convert it to a string value?
>> Thank you in advance|||You might try:
=iif(fields!mbr_join_date.value < "7/02/2003", mbt_code, "Not
Grandfathered")
Adding one day to the date value - you might have to specify the date
as
"2003-07-02 00:00:00" (yyyy-mm-dd 00:00:00 format)
to ensure you are setting the date/time to the very start of the
following day.

iif statement problem

hi all,

i've browsed some of the smilar iif expression problems but no idea how to solve mine.

i get an #Error when i wrote the following expr :

=sum(iif(Fields!revenue_type.Value="R", Fields!amount.Value,0))

but no error when it's

=sum(iif(Fields!revenue_type.Value="R", Fields!amount.Value,0) - iif(Fields!revenue_type.Value="T", Fields!amount.Value,0))

If i filter the dataset and just take in data where revenue_type = "R" then there won't be error... how come?

did i do something wrong? please help... this seems so simple yet i couldn't get it..

oh btw, i wrote the expression in table footer|||Maybe somewhere Fields!revenue_type is "null", then accessing Fields!revenue_type.Value will generate error. Try checking (IsNothing(Fields!revenue_type.Value) = false and Fields!revenue_type.Value = "R").|||

hi Maciej, thanks for your reply but it still return #Error.

there's no null value for revenue_type

|||

Ok, after your post I've tried the same thing in one of my reports and got the same error (tip - it is good to read the "error list" ). Mine says:

Warning 1 [rsAggregateOfMixedDataTypes] The Value expression for the textbox ‘textbox116’ uses an aggregate function on data of varying data types. Aggregate functions other than First, Last, Previous, Count, and CountDistinct can only aggregate data of a single data type. c:\...\Obroty producentow.v1.2.rdl

So the problem is in aggregation varying data types. Simplest way to fix it:

=Sum(iff(<your bool>,CDbl(<your sum field>),CDbl(0))

CDbl is used only as a example (it will conver every number to "double" data type). You can use different conversion method (from Common functions\Conversion).

I hope this will help

Maciej

|||

thanks Maciej. it works! but i don't get why the expr needs to be converted to double data type...

cos i got that warning as well but didn't get it...

|||Maybe RS is not so clever Your field if decimal or duble or something and "0" is integer so RS gets lost |||You hit the nail on the head with "Maybe RS is not so clever." I experienced the same #error issue in some of my calculated columns in a table in my report but oddly not until i added 2 text boxes above the table to my report. The text boxes display a begin and end time from a separate data set query. When I added those, I got the #error issue. But when i remove them, it goes away. I did not edit the table with the calculated columns in any way. I was able to get the #error issue to go away by using the CDbl solution you described (THANKS!) and leave the text boxes with the datetimes on the report. But there has to be a bug somewhere because that makes no sense. MICROSOFT> PLEASE NOTE AND FIX!!!