Hello,
I am using SQL 7.0 to replicate to an immediate-updating subscriber and I am
having trouble when updating a table from the subscriber. When I run the
update statement, I get the error "incorrect syntax near ')'. I believe that
this is either in the auto-generated MSsync trigger or the sp on the
publisher. I am sure the original update statement is correct.
It seems almost as though a parameter is missing... I don't know.
One thing that I noticed is that I only receive this error on certain
tables. The main differences are:
1. the tables that don't work are really wide in columns (by all means they
should be three tables each) whereas the tables that do work are relatively
small.
2. the tables that don't work used to have text columns, but I changed them
to enormous varchar columns. The tables that do work have never had text
columns.
My experience with replication is limited, so any help or advice in this
matter would be greatly appreciated.
Thanks in advance,
-b.
Please can you confirm what service pack you are using, as this issue (or a
v.similar one) cropped up prior to sp2 and was fixed there
(http://support.microsoft.com/kb/238658/EN-US/).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you for your reply. Yes, I read something about that. I had applied sp4
and assumed that it was cumulative... is it not?
-b.
"Paul Ibison" wrote:
> Please can you confirm what service pack you are using, as this issue (or a
> v.similar one) cropped up prior to sp2 and was fixed there
> (http://support.microsoft.com/kb/238658/EN-US/).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Okay, I just read that the service packs are cumulative.
However, I did read something that said that text and image columns will not
replicate normally from immediate updating subscribers, and that special
considerations need to be made
(http://msdn.microsoft.com/library/de...lplan_1l4e.asp)
I no longer have text columns in the tables, but I DID, however, publish to
the subscriber previously with text fields before changing them to varchars
and publishing to the subscriber again. Is it possible that these triggers
remain from the previous publication...?
-b.
"pukeboot" wrote:
[vbcol=seagreen]
> Thank you for your reply. Yes, I read something about that. I had applied sp4
> and assumed that it was cumulative... is it not?
> -b.
> "Paul Ibison" wrote:
|||That shouldn't be the case, unless you did a nosync initialization. Can you
post up the text of some of the triggers so I can take a look?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||to avoid a massively lengthy post, I have sent the trigger code directly to
you via attachment...
Thanks again,
-b.
"Paul Ibison" wrote:
> That shouldn't be the case, unless you did a nosync initialization. Can you
> post up the text of some of the triggers so I can take a look?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||My email address isn't the one I post with (long story). Please send to Paul
.. Ibison@.ReplicationAnswers . Com (no spaces).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I read that as "paulibison@.replicationanswers.com" ... I wasn't sure with the
line breaks and the elipsis.
I forwarded the message there. I hope that's it.
-b.
"Paul Ibison" wrote:
> My email address isn't the one I post with (long story). Please send to Paul
> .. Ibison@.ReplicationAnswers . Com (no spaces).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Bobby,
there's nothing I can see wrong in the trigger, and no reference to text
columns. As you are on sp4 (publisher, distributor and subscriber?), this is
baffling. I'd run profiler to see where it gets to, and fails, in the
synchronization, and also enable logging to see what that reveals
(http://support.microsoft.com/?id=312292).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I am unfamiliar with the profiler, but I gave it a go and found that the
"internal abort" event was occuring after this line in the trigger (bear with
this... it's a long line):
exec @.retcode = dbo.sp_executesql @.rpc_proc, @.rpc_types,
@.c1
output,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8,@.c9,@.c10,@.c11,@. c12,@.c13,@.c14,@.c15,@.c16,@.c17,@.c18,@.c19,@.c20,@.c21,@. c22,@.c23,@.c24,@.c25,@.c26,@.c27,@.c28,@.c29,@.c30,@.c31,@. c32,@.c33,@.c34,@.c35,@.c36,@.c37,@.c38,@.c39,@.c40,@.c41,@. c42,@.c43,@.c44,@.c45,@.c46,@.c47,@.c48,@.c49,@.c50,@.c51,@. c52,@.
c53,@.c54,@.c55,@.c56,@.c57,@.c58,@.c59,@.c60,@.c61,@.c62,@. c63,@.c64,@.c65,@.c66,@.c67,@.c68,@.c69,@.c70,@.c71,@.c72,@. c73,@.c74,@.c75,@.c76,@.c77,@.c78,@.c79,@.c80,@.c81,@.c82,@. c83,@.c84,@.c85,@.c86,@.c87,@.c88,@.c89,@.c90,@.c91,@.c92,@. c93,@.c94,@.c95,@.c96,@.c97,@.c98,@.c99,@.c100,@.c101,@.c10 2,@.c1
03,@.c104,@.c105,@.c106,@.c107,@.c108,@.c109,@.c110,@.c111 ,@.c112,@.c113,@.c114,@.c115,@.c116,@.c117,@.c118,@.c119,@. c120,@.c121,@.c122,@.c123,@.c124,@.c125,@.c126,@.c127,@.c1 28,@.c129,@.c130,@.c131,@.c132,@.c133,@.c134,@.c135,@.c136 ,@.c137,@.c138,@.c139,@.c140,@.c141,@.c142,@.c143,@.c144,@. c145,
@.c146,@.c147,@.c148,@.c149,@.c150,@.c151,@.c152,@.c153,@.c 154,@.c155,@.c156,@.c157,@.c158,@.c159,@.c160,@.c161,@.c16 2,@.c163,@.c164,@.c165,@.c166,@.c167,@.c168,@.c169,@.c170, @.c171,@.c172,@.c173,@.c174,@.c175,@.c176,@.c177,@.c178,@.c 179,@.c180,@.c181,@.c182,@.c183,@.c184,@.c185,@.c186,@.c18 7,@.c1
88,@.c189,@.c190,@.c191,@.c192,@.c193,@.c194,@.c195,@.c196 ,@.c197,@.c198,@.c199,@.c200,@.c201,@.c202,@.c203,@.c204,@. c205,@.c206,@.c207,@.c208,@.c209,@.c210,@.c211,@.c212,@.c2 13,@.c214,@.c215,@.c216,@.c217,@.c218,@.c219,@.c220,@.c221 ,@.c222,@.c223,@.c224,@.c225,@.c226,@.c227,@.c228,@.c229,@. c230,
@.c231,@.c232,@.c233,@.c234,@.c235,@.c236
output,@.c237,@.c238,@.c239,@.c240,@.c241,@.c242,@.c243,@. c244,@.c245,@.c246,@.c247,@.c248
,@.c1_old,@.c2_old,@.c3_old,@.c4_old,@.c5_old,@.c6_old,@. c7_old,@.c8_old,@.c9_old,@.c10_old,@.c11_old,@.c12_old, @.c13_old,@.c14_old,@.c15_old,@.c16_old,@.c17_old,@.c18_ old,@.c19_old,@.c20_old,@.c21_old,@.c22_old,@.c23_old,@. c24_old,@.c25_old,@.c26_old,@.c27_old,@.c28_old,@.c29_o ld,@.c
30_old,@.c31_old,@.c32_old,@.c33_old,@.c34_old,@.c35_ol d,@.c36_old,@.c37_old,@.c38_old,@.c39_old,@.c40_old,@.c4 1_old,@.c42_old,@.c43_old,@.c44_old,@.c45_old,@.c46_old ,@.c47_old,@.c48_old,@.c49_old,@.c50_old,@.c51_old,@.c52 _old,@.c53_old,@.c54_old,@.c55_old,@.c56_old,@.c57_old, @.c58_
old,@.c59_old,@.c60_old,@.c61_old,@.c62_old,@.c63_old,@. c64_old,@.c65_old,@.c66_old,@.c67_old,@.c68_old,@.c69_o ld,@.c70_old,@.c71_old,@.c72_old,@.c73_old,@.c74_old,@.c 75_old,@.c76_old,@.c77_old,@.c78_old,@.c79_old,@.c80_ol d,@.c81_old,@.c82_old,@.c83_old,@.c84_old,@.c85_old,@.c8 6_old
,@.c87_old,@.c88_old,@.c89_old,@.c90_old,@.c91_old,@.c92 _old,@.c93_old,@.c94_old,@.c95_old,@.c96_old,@.c97_old, @.c98_old,@.c99_old,@.c100_old,@.c101_old,@.c102_old,@.c 103_old,@.c104_old,@.c105_old,@.c106_old,@.c107_old,@.c 108_old,@.c109_old,@.c110_old,@.c111_old,@.c112_old,@.c 113_o
ld,@.c114_old,@.c115_old,@.c116_old,@.c117_old,@.c118_o ld,@.c119_old,@.c120_old,@.c121_old,@.c122_old,@.c123_o ld,@.c124_old,@.c125_old,@.c126_old,@.c127_old,@.c128_o ld,@.c129_old,@.c130_old,@.c131_old,@.c132_old,@.c133_o ld,@.c134_old,@.c135_old,@.c136_old,@.c137_old,@.c138_o ld,@.c
139_old,@.c140_old,@.c141_old,@.c142_old,@.c143_old,@.c 144_old,@.c145_old,@.c146_old,@.c147_old,@.c148_old,@.c 149_old,@.c150_old,@.c151_old,@.c152_old,@.c153_old,@.c 154_old,@.c155_old,@.c156_old,@.c157_old,@.c158_old,@.c 159_old,@.c160_old,@.c161_old,@.c162_old,@.c163_old,@.c 164_o
ld,@.c165_old,@.c166_old,@.c167_old,@.c168_old,@.c169_o ld,@.c170_old,@.c171_old,@.c172_old,@.c173_old,@.c174_o ld,@.c175_old,@.c176_old,@.c177_old,@.c178_old,@.c179_o ld,@.c180_old,@.c181_old,@.c182_old,@.c183_old,@.c184_o ld,@.c185_old,@.c186_old,@.c187_old,@.c188_old,@.c189_o ld,@.c
190_old,@.c191_old,@.c192_old,@.c193_old,@.c194_old,@.c 195_old,@.c196_old,@.c197_old,@.c198_old,@.c199_old,@.c 200_old,@.c201_old,@.c202_old,@.c203_old,@.c204_old,@.c 205_old,@.c206_old,@.c207_old,@.c208_old,@.c209_old,@.c 210_old,@.c211_old,@.c212_old,@.c213_old,@.c214_old,@.c 215_o
ld,@.c216_old,@.c217_old,@.c218_old,@.c219_old,@.c220_o ld,@.c221_old,@.c222_old,@.c223_old,@.c224_old,@.c225_o ld,@.c226_old,@.c227_old,@.c228_old,@.c229_old,@.c230_o ld,@.c231_old,@.c232_old,@.c233_old,@.c234_old,@.c235_o ld,@.c236_old,@.c237_old,@.c238_old,@.c239_old,@.c240_o ld,@.c
241_old,@.c242_old,@.c243_old,@.c244_old,@.c245_old,@.c 246_old,@.c247_old,@.c248_old
, @.bitmap
plus, I also get an error that says:
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@.c30o'.
If you have any ideas what might be causing this, or if you have any further
advice on what I can do to resolve the problem, I would love to hear it.
Thanks for all your input.
-b.
"Paul Ibison" wrote:
> Bobby,
> there's nothing I can see wrong in the trigger, and no reference to text
> columns. As you are on sp4 (publisher, distributor and subscriber?), this is
> baffling. I'd run profiler to see where it gets to, and fails, in the
> synchronization, and also enable logging to see what that reveals
> (http://support.microsoft.com/?id=312292).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Showing posts with label trouble. Show all posts
Showing posts with label trouble. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
image properties
Hello
Im having abit off trouble with my images positioning..
I have 3 images in the header placed side by side with a rectangle around them sent to the back
In layout view the images look correct, however when i deploy the report the rectangle appears on top and then the images appear undernealth each other...when they should be inside the rectangle. is there something like float on top properties similar to objects in Word?
A separate question, I also have a table to the right off the table i have an image and under that image another image...when i preview the report the second image is pushed to the bottom of where the table ends...is there someway to make the image appear side by side to the table instead off getting pushed down to the bottom...something similar to a frame in HTML ?
thanks
Yes, you are right SSRS 2005 has layers. You can put your image(s) on the top layer by simply right clicking on the image that you want to bring to the top and clicking "Bring to Front".
As far as your second question goes, I would try a rectangle tool to hold the table and the pictures next to it. Although I'm not quite sure if that will work because a rectangle can grow. I'll post something if I can think of another better idea.
|||Hi
I think this is a problem due to the page width.
Right click on the report (the blank space below the report) and there you get Report properties.
In that report properties you will find Layout tab.
In the layout tab set increase the page width and also note to check for this
Page Width = Left Margin + Right margin + The report layout width.
Probly this will solve your problem.
|||1. Make sure the images are inside of the rectangle, not just on top of them.
2. Our HTML renderer does not support overlapping items so make sure the images are not overlapping each other. Perhaps put a bit of space between them as a quick and simple test.
For question #2, have you tried putting the two images in a rectangle?
Thanks.
image properties
Hello
Im having abit off trouble with my images positioning..
I have 3 images in the header placed side by side with a rectangle around them sent to the back
In layout view the images look correct, however when i deploy the report the rectangle appears on top and then the images appear undernealth each other...when they should be inside the rectangle. is there something like float on top properties similar to objects in Word?
A separate question, I also have a table to the right off the table i have an image and under that image another image...when i preview the report the second image is pushed to the bottom of where the table ends...is there someway to make the image appear side by side to the table instead off getting pushed down to the bottom...something similar to a frame in HTML ?
thanks
Yes, you are right SSRS 2005 has layers. You can put your image(s) on the top layer by simply right clicking on the image that you want to bring to the top and clicking "Bring to Front".
As far as your second question goes, I would try a rectangle tool to hold the table and the pictures next to it. Although I'm not quite sure if that will work because a rectangle can grow. I'll post something if I can think of another better idea.
|||Hi
I think this is a problem due to the page width.
Right click on the report (the blank space below the report) and there you get Report properties.
In that report properties you will find Layout tab.
In the layout tab set increase the page width and also note to check for this
Page Width = Left Margin + Right margin + The report layout width.
Probly this will solve your problem.
|||1. Make sure the images are inside of the rectangle, not just on top of them.
2. Our HTML renderer does not support overlapping items so make sure the images are not overlapping each other. Perhaps put a bit of space between them as a quick and simple test.
For question #2, have you tried putting the two images in a rectangle?
Thanks.
sqlImage not displayed in report
Hello everyone,
I have a Reporting Services report which must show photographs stored in a SQL Server database. The trouble is that the photos just don't display. The report just shows the little red cross icon.
When I use MS-Access forms and reports it works fine. But I need this to work with Reporting Services!
Help please!
Jerome
What data type are the pictures stored in SQL DB? You may want to change the MIMEType of the Image control on your report to see if which one is correct. I've been using image/bmp to accommodate pictures stored as varbinary data type. HTH.|||Hi.
I discovered that the problem has to do with the way Access stores images. It stores all the OLE data necessary to display the images in Access, but this is not compatible with SQL Server. I solved the problem by purchasing and installing DBPix software. It cost me US$ 100.
Cheers,
Jerome
|||Technet has an article about how to access OLE images. See the section entitled 'Task List Item 2 – Database Images' in http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/migratereports.mspx. Note that the code as presented only works for OLE images created on an en-US system:
Code Snippet
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields.Picture.Value),105))For images created on other locales, check out this post: http://forums.devarticles.com/showpost.php?p=51707&postcount=3.
Thanks.
Image not displayed in report
Hello everyone,
I have a Reporting Services report which must show photographs stored in a SQL Server database. The trouble is that the photos just don't display. The report just shows the little red cross icon.
When I use MS-Access forms and reports it works fine. But I need this to work with Reporting Services!
Help please!
Jerome
What data type are the pictures stored in SQL DB? You may want to change the MIMEType of the Image control on your report to see if which one is correct. I've been using image/bmp to accommodate pictures stored as varbinary data type. HTH.|||Hi.
I discovered that the problem has to do with the way Access stores images. It stores all the OLE data necessary to display the images in Access, but this is not compatible with SQL Server. I solved the problem by purchasing and installing DBPix software. It cost me US$ 100.
Cheers,
Jerome
|||Technet has an article about how to access OLE images. See the section entitled 'Task List Item 2 – Database Images' in http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/migratereports.mspx. Note that the code as presented only works for OLE images created on an en-US system:
Code Snippet
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields.Picture.Value),105))For images created on other locales, check out this post: http://forums.devarticles.com/showpost.php?p=51707&postcount=3.
Thanks.
Wednesday, March 7, 2012
IIS -> SQLServer on another machine
We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become visible
to Server2 so it can be granted Login to SQLServer? Is this trusted scenario
secure if the firewall limits traffic to HTTP only?
Thanks!Why don't you have your IIS and SQL Server on a domain and use domain
accounts?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message
news:OGVqjoOEEHA.3472@.TK2MSFTNGP09.phx.gbl...
We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become visible
to Server2 so it can be granted Login to SQLServer? Is this trusted scenario
secure if the firewall limits traffic to HTTP only?
Thanks!|||What is meant exactly by having IIS and SQL server "on a domain"? I'm not t
he network administrator -- we don't really have one ;-( The guy with the
most experience with setting things up has said we're using ActiveDirectory
with Windows2003 server. Our SQL server 2000 is installed on Server1. IIS
is installed on server2. The DNS names of these servers are server1.{
ourdomain}.net and server2.{ourdomain}.net. Are these servers "on a do
main"?
BTW, we have tried to substitute a named domain user for the anonymous IUSR_
SERVER2 built-in user. Although I was able to grant that user login and dbac
cess and add it to a role, the IIS app didn't work. Does changing the machin
e user for IIS require a reboot of the IIS server?
Is there any way to see traffic between IIS server and the sql server box?
Thanks for the help.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:%23IJPbwOEEHA.
1240@.TK2MSFTNGP10.phx.gbl...
Why don't you have your IIS and SQL Server on a domain and use domain accoun
ts?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message news:OGVqjoOEEHA.3472@.TK2MSFTNG
P09.phx.gbl...
We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become visible
to Server2 so it can be granted Login to SQLServer? Is this trusted scenario
secure if the firewall limits traffic to HTTP only?
Thanks!|||If you're using AD, then you're on a domain. Looks like you're almost there
by using a domain user for IUSR_SERVER2. You have granted that same user
access to SQL Server on Server1, as well as having granted the appropriate
access to databases and their objects. I don't have access to an IIS server
right now but I think you'd have to configure it to use the domain user
account. This would likely require stopping and starting the IIS service on
the server.
You could use Network Monitor to look at the traffic but I don't think you
need to get down to that level.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message
news:O#CEd2QEEHA.712@.tk2msftngp13.phx.gbl...
What is meant exactly by having IIS and SQL server "on a domain"? I'm not
the network administrator -- we don't really have one ;-( The guy with the
most experience with setting things up has said we're using ActiveDirectory
with Windows2003 server. Our SQL server 2000 is installed on Server1. IIS
is installed on server2. The DNS names of these servers are
server1.{ourdomain}.net and server2.{ourdomain}.net. Are these se
rvers "on
a domain"?
BTW, we have tried to substitute a named domain user for the anonymous
IUSR_SERVER2 built-in user. Although I was able to grant that user login and
dbaccess and add it to a role, the IIS app didn't work. Does changing the
machine user for IIS require a reboot of the IIS server?
Is there any way to see traffic between IIS server and the sql server box?
Thanks for the help.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23IJPbwOEEHA.1240@.TK2MSFTNGP10.phx.gbl...
Why don't you have your IIS and SQL Server on a domain and use domain
accounts?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message
news:OGVqjoOEEHA.3472@.TK2MSFTNGP09.phx.gbl...
We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to
make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become
visible
to Server2 so it can be granted Login to SQLServer? Is this trusted
scenario
secure if the firewall limits traffic to HTTP only?
Thanks!|||316989 PRB: "Login Failed" Error Message When You Create a Trusted Data
http://support.microsoft.com/?id=316989
- Programmatically change the security context of the ASP.NET worker
process to a user who has the correct SQL Server permissions. -or-
- Change the default configuration of ASP.NET so that the ASP.NET
worker process starts and runs under the context of a user who has the
correct permissions in SQL Server. -or-
- Grant the correct permissions on SQL Server so that the aspnet_wp
account (or NetworkService account, for an application that runs on IIS
6.0) has the appropriate access to the required resources.
317012 INFO: Process and Request Identity in ASP.NET
http://support.microsoft.com/?id=317012
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We have taken option #2 from the post below (thank you Kevin). But what
should we be seeing in EventViewer/Security in the "user" column under those
circumstances? We're seeing SERVER2$ rather than the domain username we have
supplied. Is that expected behavior?
to recap: SQL Server 2000 running on Server1
IIS 6.0 running on Server2
Windows2003 / ActiveDirectory
We did this:
--edited the processModel section of machine.config setting userName to a
domain user with SQL logon and dbaccess rights, let's call it SQL_USER
-- supplied OURDOMAIN\SQL_USER as the "anonymous" user in the IIS dialog
-- edited machine.config: setting impersonate="true"
userName="OURDOMAIN\SQLUSER" password="pwd"
--Then we rebooted the machine on which IIS is installed
-- granted OURDOMAIN\SQL_USER read/write/modify rights to the ASPNET
temporary directory
With EventViewer->Security running on Server1 (the SQL Server), we look to
see who is trying to access the SQL machine from the IIS web pages.
Invariably the user appears as "SERVER2$" not as SQL_USER. What should we
see in EventViewer/Security on Server1? Is our reconfiguration of the
default ASP.NET user incomplete?
Thanks
Timo
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:lygZpHREEHA.3568@.cpmsftngxa06.phx.gbl...
>
> 316989 PRB: "Login Failed" Error Message When You Create a Trusted Data
> http://support.microsoft.com/?id=316989
> - Programmatically change the security context of the ASP.NET worker
> process to a user who has the correct SQL Server permissions. -or-
> - Change the default configuration of ASP.NET so that the ASP.NET
> worker process starts and runs under the context of a user who has the
> correct permissions in SQL Server. -or-
> - Grant the correct permissions on SQL Server so that the aspnet_wp
> account (or NetworkService account, for an application that runs on IIS
> 6.0) has the appropriate access to the required resources.
>
> 317012 INFO: Process and Request Identity in ASP.NET
> http://support.microsoft.com/?id=317012
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||It sounds like ASPNET process is running under localsystem. It is
attempting to connect using localsystem
credentials, and thus comes across as Server2$.
See if these resources help you :
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetch08.asp
and
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetch12.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become visible
to Server2 so it can be granted Login to SQLServer? Is this trusted scenario
secure if the firewall limits traffic to HTTP only?
Thanks!Why don't you have your IIS and SQL Server on a domain and use domain
accounts?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message
news:OGVqjoOEEHA.3472@.TK2MSFTNGP09.phx.gbl...
We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become visible
to Server2 so it can be granted Login to SQLServer? Is this trusted scenario
secure if the firewall limits traffic to HTTP only?
Thanks!|||What is meant exactly by having IIS and SQL server "on a domain"? I'm not t
he network administrator -- we don't really have one ;-( The guy with the
most experience with setting things up has said we're using ActiveDirectory
with Windows2003 server. Our SQL server 2000 is installed on Server1. IIS
is installed on server2. The DNS names of these servers are server1.{
ourdomain}.net and server2.{ourdomain}.net. Are these servers "on a do
main"?
BTW, we have tried to substitute a named domain user for the anonymous IUSR_
SERVER2 built-in user. Although I was able to grant that user login and dbac
cess and add it to a role, the IIS app didn't work. Does changing the machin
e user for IIS require a reboot of the IIS server?
Is there any way to see traffic between IIS server and the sql server box?
Thanks for the help.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:%23IJPbwOEEHA.
1240@.TK2MSFTNGP10.phx.gbl...
Why don't you have your IIS and SQL Server on a domain and use domain accoun
ts?
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message news:OGVqjoOEEHA.3472@.TK2MSFTNG
P09.phx.gbl...
We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become visible
to Server2 so it can be granted Login to SQLServer? Is this trusted scenario
secure if the firewall limits traffic to HTTP only?
Thanks!|||If you're using AD, then you're on a domain. Looks like you're almost there
by using a domain user for IUSR_SERVER2. You have granted that same user
access to SQL Server on Server1, as well as having granted the appropriate
access to databases and their objects. I don't have access to an IIS server
right now but I think you'd have to configure it to use the domain user
account. This would likely require stopping and starting the IIS service on
the server.
You could use Network Monitor to look at the traffic but I don't think you
need to get down to that level.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message
news:O#CEd2QEEHA.712@.tk2msftngp13.phx.gbl...
What is meant exactly by having IIS and SQL server "on a domain"? I'm not
the network administrator -- we don't really have one ;-( The guy with the
most experience with setting things up has said we're using ActiveDirectory
with Windows2003 server. Our SQL server 2000 is installed on Server1. IIS
is installed on server2. The DNS names of these servers are
server1.{ourdomain}.net and server2.{ourdomain}.net. Are these se
rvers "on
a domain"?
BTW, we have tried to substitute a named domain user for the anonymous
IUSR_SERVER2 built-in user. Although I was able to grant that user login and
dbaccess and add it to a role, the IIS app didn't work. Does changing the
machine user for IIS require a reboot of the IIS server?
Is there any way to see traffic between IIS server and the sql server box?
Thanks for the help.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23IJPbwOEEHA.1240@.TK2MSFTNGP10.phx.gbl...
Why don't you have your IIS and SQL Server on a domain and use domain
accounts?
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Timo" <timo@.anonymous.com> wrote in message
news:OGVqjoOEEHA.3472@.TK2MSFTNGP09.phx.gbl...
We're having some trouble setting up a two-server IIS/SQLServer
configuration on a Windows 2003 network. What we'd like to have is this:
{ANONYMOUS INTERNET WWW USERS }
:
FIREWALL (ONLY HTTP TRAFFIC ALLOWED)
:
IIS (Server1 running Win2003)
:
:
SQLServer (Server2 running Win2003)
But SQLServer on Server2 cannot "see" the IUSR_SERVER1 or the
SERVER1\ASPNET guest account. We're unable to grant SERVER1\ASPNET login:
exec sp_grantlogin 'SERVER1\ASPNET' --we get an error that this NT user or
group is not recognized. From what I've read, it seems we will need to
make
Server1 "trusted for Authentication Delegation" (along with some other
things -- use TCP/IP and use Kerberos). Will making Server1 trused for
authentical delegation cause the SERVER1\ASPNET guest user to become
visible
to Server2 so it can be granted Login to SQLServer? Is this trusted
scenario
secure if the firewall limits traffic to HTTP only?
Thanks!|||316989 PRB: "Login Failed" Error Message When You Create a Trusted Data
http://support.microsoft.com/?id=316989
- Programmatically change the security context of the ASP.NET worker
process to a user who has the correct SQL Server permissions. -or-
- Change the default configuration of ASP.NET so that the ASP.NET
worker process starts and runs under the context of a user who has the
correct permissions in SQL Server. -or-
- Grant the correct permissions on SQL Server so that the aspnet_wp
account (or NetworkService account, for an application that runs on IIS
6.0) has the appropriate access to the required resources.
317012 INFO: Process and Request Identity in ASP.NET
http://support.microsoft.com/?id=317012
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We have taken option #2 from the post below (thank you Kevin). But what
should we be seeing in EventViewer/Security in the "user" column under those
circumstances? We're seeing SERVER2$ rather than the domain username we have
supplied. Is that expected behavior?
to recap: SQL Server 2000 running on Server1
IIS 6.0 running on Server2
Windows2003 / ActiveDirectory
We did this:
--edited the processModel section of machine.config setting userName to a
domain user with SQL logon and dbaccess rights, let's call it SQL_USER
-- supplied OURDOMAIN\SQL_USER as the "anonymous" user in the IIS dialog
-- edited machine.config: setting impersonate="true"
userName="OURDOMAIN\SQLUSER" password="pwd"
--Then we rebooted the machine on which IIS is installed
-- granted OURDOMAIN\SQL_USER read/write/modify rights to the ASPNET
temporary directory
With EventViewer->Security running on Server1 (the SQL Server), we look to
see who is trying to access the SQL machine from the IIS web pages.
Invariably the user appears as "SERVER2$" not as SQL_USER. What should we
see in EventViewer/Security on Server1? Is our reconfiguration of the
default ASP.NET user incomplete?
Thanks
Timo
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:lygZpHREEHA.3568@.cpmsftngxa06.phx.gbl...
>
> 316989 PRB: "Login Failed" Error Message When You Create a Trusted Data
> http://support.microsoft.com/?id=316989
> - Programmatically change the security context of the ASP.NET worker
> process to a user who has the correct SQL Server permissions. -or-
> - Change the default configuration of ASP.NET so that the ASP.NET
> worker process starts and runs under the context of a user who has the
> correct permissions in SQL Server. -or-
> - Grant the correct permissions on SQL Server so that the aspnet_wp
> account (or NetworkService account, for an application that runs on IIS
> 6.0) has the appropriate access to the required resources.
>
> 317012 INFO: Process and Request Identity in ASP.NET
> http://support.microsoft.com/?id=317012
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||It sounds like ASPNET process is running under localsystem. It is
attempting to connect using localsystem
credentials, and thus comes across as Server2$.
See if these resources help you :
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetch08.asp
and
http://msdn.microsoft.com/library/d...-us/dnnetsec/ht
ml/SecNetch12.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Sunday, February 19, 2012
IIF and empty fields
Hi, apologies if this is a silly mistake on my part, but... I'm having trouble with working out if a field is empty
I've been trying to use "SELECT newfield = IIF(IsEmpty(table.field), 0, 1) ... " to return a bit value of whether the string field has something in it..
The syntax check says that IsEmpty is not a valid function - but I dont understand how it can be... am I missing something here?
I also tried using IIF(table.field = '', 0, 1) and that returns "invalid syntax near ="
Please help because its really confusing me, ThanksHi,
IsEmpty and IIF are Analysis Server functions, so you can't use it in reqular T-SQL. I don't seen any indication in your message that your using OLAP, so I'll assume you're using regular T-SQL with relational data.
What do you consider to be empty? A null value? Or what is commonly considered to be no data for a particular datatype (empty string for strings, zero for numerics, etc.)?
If nulls, consider using the ISNULL function. If the value is null it returns the value of your choice, otherwise the non-null value. The NULLIF function can help in some cases.
But if empty is an empty string or zero, etc., you can compare those values. Or use the CASE block, which lets you return different values based on various conditions.
I suspect that you'll find what you need in these T-SQL features.
Don|||ok, thanks a lot. explains why its not working anyway :)
Subscribe to:
Posts (Atom)