Authentication in SQL Server

Hi folks,

Got a massive problem.
I'm starting to develop a new website using ASP.NET v3.5 (the CTP of Microsoft's new Visual Web Developer "Orcas"). I saved the project onto a network drive (called X: for the purposes of this discussion) - X:\Orcas_WS\VVProject.
Set up a new database and table to upload files from an ASP.NET AJAX form to a database table, fields as follows:
[ImgID] - type int, Identity field, auto-increment of 1;
[GallID] - type int;
[GallPos] - type int;
[ImgContentType] - type nchar(10);
[ImgContent] - type image (binary data);

Worked a dream when running the development server on my laptop and using my own username and password to upload stuff to (in connection string, using Integrated Security = SSPI).
Have now uploaded this to a live server, installed .NET Framework v3.5 on my server, (attempted to) enable IUSR_MACHINE account to have read/write access to my DB and now it's all gone to pot. None of my accounts can authenticate against this DB using .NET - whether it's NETWORK SERVICE, or IUSR_CM-SUMM, even my own username and password, with Integrated Security both set to SSPI and False.
I am now getting intermittent errors where login will either succeed but .NET will try and use named pipes and says "there is nothing on the other end of the pipe" with an event 3005 in my event log, or I get an "login failed for user (username)" message and a failure audit in my event log.

I read up somewhere about having to deregister the Service Principal Name to avoid auth errors (which is what seems to be happening), but having trawled Microsoft's website for hours, there seems to be no documentation or instruction on how to deregister an SPN.

Please help folks, this project is financially lucrative and I want to make a fantastic impression on the client with a low development time overhead!

Many thanks in advance,

medicineworkerAdmin, please close thread, problem sorted - my MS SQL Server was being stoopid, my schema permissions had (rather strangely) been entirely wiped out... lol, have fixed.

Cheers!

Attn TSQL Challenge Seekers

Hi Folks - Well this is going to be a fun ride. I have a simple table that I need to do some slightly not so simple logic on. I am using SQL2005 and this happens in a stored proc. I need to do this in straight T-SQL and w/o using a cursor, you can use a CTE if needed.

ID PARENTID CAT CATRANK THEVALUE 1 1 A 0 11 2 1 B 1 22 3 1 C 2 33 4 2 A 0 44 5 2 B 1 55 6 2 C 2 66 7 3 A 0 12 8 3 B 1 13 9 4 A 0 14 10 4 B 1 15 11 4 C 2 16

I need to a variation on select cat, sum(thevalue) from TABLE1 group by cat

which results in

CAT THEVALUE A 81 B 105 C 115

the wrinkle is that each item in the group by (A,B,C) needs to consider 1 row from each parentid. in other words if the system is calculating the C row, and a parentid is missing a C entry then the system needs to use the B entry for that parentid.

so to calculate the A row we simply do. this is simple because there is an A row for every parentid

PARENTID CAT THEVALUE 1 A 11 2 A 44 3 A 12 4 A 14 SUM 81

the C row is the tricky part. there is no C for parentid 3, so in that case I need to use the next lower ranked item, which is a B

PARENTID CAT THEVALUE 1 C 33 2 C 66 3 B 13 4 C 16 SUM 128

so after all is said and done the final result needs to look like this

CAT THEVALUE A 81 B 105 C 128

Try:

Code Snippet

use tempdb

go

create table dbo.t1 (

ID int not null,

PARENTID int not null,

CAT char(1) not null,

CATRANK int not null,

THEVALUE int not null

)

go

insert into dbo.t1 values(1, 1, 'A', 0, 11)

insert into dbo.t1 values(2, 1, 'B', 1, 22)

insert into dbo.t1 values(3, 1, 'C', 2, 33)

insert into dbo.t1 values(4, 2, 'A', 0, 44)

insert into dbo.t1 values(5, 2, 'B', 1, 55)

insert into dbo.t1 values(6, 2, 'C', 2, 66)

insert into dbo.t1 values(7, 3, 'A', 0, 12)

insert into dbo.t1 values(8, 3, 'B', 1, 13)

insert into dbo.t1 values(9, 4, 'A', 0, 14)

insert into dbo.t1 values(10, 4, 'B', 1, 15)

insert into dbo.t1 values(11, 4, 'C', 2, 16)

go

select

c.cat,

sum(d.thevalue) as sum_thevalue

from

(

select

parentid,

cat,

catrank

from

(

select distinct

cat, catrank

from

dbo.t1

) as a

cross join

(

select distinct

parentid

from

dbo.t1

) as b

) as c

inner join

dbo.t1 as d

on d.parentid = c.parentid

and d.catrank = (

select max(e.catrank)

from dbo.t1 as e

where e.parentid = c.parentid and e.catrank <= c.catrank

)

group by

c.cat

order by

c.cat

go

drop table dbo.t1

go

AMB

|||Oh Snap! that was fast ... ty I will look at this

Attempting to transpose/pivot columns to rows... I think.

I have something of a challenge for you folks (at least it's very challenging for me).

I have a table that has data that looks like this:

DateOrderNum_WorkDay

2006-06-1__AA1

2006-06-1__AB1

2006-06-2__BA2

2006-06-2__BB2

2006-06-2__BC2

2006-06-5__CA3

2006-06-5__CB3

etc.

So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.

What I am trying to write is a SQL statement that will display results thus:

Day1Day2Day3_etc.

AA__BA_CAetc.

AB__BB_CBetc.

Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.

I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!

2 questions.

1 - What version of SQL Server are you using? Is it 2005?

2- Is the set of Work days dynamic, or do you know the specific work days when you are writing the query. More specifically, do the columns in the result need to be generated dynamically from the data in the table?

|||

Ryan,

Thanks for the quick reply! Here are the answers to your questions:

1. I'm using SQL Server 2005 (and I just found the PIVOT command which is new in 2005 apparently).

2. The set of workdays is dynamic. Depending on the size of the month and holidays there could be as few as 20 or as many as 23. And if I understand your second question then yes the columns will need to be generated dynammically. The data in the table will be changing daily and this SQL statement is generating a grid view on a web page.

Thanks again!

|||

Unfortunately, the dynamic aspect of your query makes it pretty complicated.

The PIVOT function is nice, but it requires that you know the resulting columns when you build the SQL. So, what you need to is create a stored proc that dynamically builds the PIVOT sql syntax by looping through the data in your table.

I know of a couple of good articles with examples of doing this (all-be-it using the old SQL 2000 SQL pivot logic).

A good article on simple PIVOTS. http://www.aspfaq.com/show.asp?id=2462

That article has a link to 2 dynamic pivot articles. I think the first is the best for you. http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

If I were you, I'd consider re-writing this for the new PIVOT function, and perhaps someone here already has. If this doesn't work, I could probably find some time to do this for you. Let me know. But, I think just using the old version described in this article should work.

|||

Thanks again, Ryan! You have a wealth of knowledge.

I am doing this for a project that needs to be ready this Wednesday, so I may not have enough time to construct this myself by then. However, I am loathe to have you do my work for me. So, I will probably do a quick and dirty view on the data and over the next week or so wrap my mind around your links and other advice. If I do need further advice I will post to this thread and hope you check back now and then.

Thanks for everything!

|||

Hi there Ryan,

I've seen you've gave a solution in case of SQL Server 2005. Do you have a solution for 2000?

Thanks in advance

|||You can have the best example here:
http://orafaq.com/node/1871|||

Code Snippet

If you use SQL Server 2005,

use tempdb

go

create table data (

[date] datetime ,

[ordernum] varchar(100) ,

[workday] int

);

go

insert into data values('2006-06-1','AA','1');

insert into data values('2006-06-1','AB','1');

insert into data values('2006-06-2','BA','2');

insert into data values('2006-06-2','BB','2');

insert into data values('2006-06-2','BC','2');

insert into data values('2006-06-5','CA','3');

insert into data values('2006-06-5','CB','3');

go

;with cte

as

(

select *, row_number() over(partition by date order by ordernum) rid from data

)

select

isnull([1],'') as day1

, isnull([2],'') as day2

, isnull([3],'') as day3

, isnull([4],'') as day4

, isnull([5],'') as day5

, isnull([6],'') as day6

, isnull([7],'') as day7

from

(

select

workday

, rid

, ordernum

from

cte

) as data

pivot

(

max(ordernum)

for workday in

([1],[2],[3],[4],[5],[6],[7] /*.....,[n]*/)

)as pvt

Attempting to transpose/pivot columns to rows... I think.

I have something of a challenge for you folks (at least it's very challenging for me).

I have a table that has data that looks like this:

DateOrderNum_WorkDay

2006-06-1__AA1

2006-06-1__AB1

2006-06-2__BA2

2006-06-2__BB2

2006-06-2__BC2

2006-06-5__CA3

2006-06-5__CB3

etc.

So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.

What I am trying to write is a SQL statement that will display results thus:

Day1Day2Day3_etc.

AA__BA_CAetc.

AB__BB_CBetc.

Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.

I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!

2 questions.

1 - What version of SQL Server are you using? Is it 2005?

2- Is the set of Work days dynamic, or do you know the specific work days when you are writing the query. More specifically, do the columns in the result need to be generated dynamically from the data in the table?

|||

Ryan,

Thanks for the quick reply! Here are the answers to your questions:

1. I'm using SQL Server 2005 (and I just found the PIVOT command which is new in 2005 apparently).

2. The set of workdays is dynamic. Depending on the size of the month and holidays there could be as few as 20 or as many as 23. And if I understand your second question then yes the columns will need to be generated dynammically. The data in the table will be changing daily and this SQL statement is generating a grid view on a web page.

Thanks again!

|||

Unfortunately, the dynamic aspect of your query makes it pretty complicated.

The PIVOT function is nice, but it requires that you know the resulting columns when you build the SQL. So, what you need to is create a stored proc that dynamically builds the PIVOT sql syntax by looping through the data in your table.

I know of a couple of good articles with examples of doing this (all-be-it using the old SQL 2000 SQL pivot logic).

A good article on simple PIVOTS. http://www.aspfaq.com/show.asp?id=2462

That article has a link to 2 dynamic pivot articles. I think the first is the best for you. http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

If I were you, I'd consider re-writing this for the new PIVOT function, and perhaps someone here already has. If this doesn't work, I could probably find some time to do this for you. Let me know. But, I think just using the old version described in this article should work.

|||

Thanks again, Ryan! You have a wealth of knowledge.

I am doing this for a project that needs to be ready this Wednesday, so I may not have enough time to construct this myself by then. However, I am loathe to have you do my work for me. So, I will probably do a quick and dirty view on the data and over the next week or so wrap my mind around your links and other advice. If I do need further advice I will post to this thread and hope you check back now and then.

Thanks for everything!

Attachement generated by xp_sendmail

Hello folks, I am sending a email from sql server that consists of results of a query and that's working perfect.
When I open the text file generated by xp_sendmail in notepad it's OK, but when I open it in MS Word or WordPad, I see garbage characters in form of squares added to the content. Those sqare are unicode spaces equivalent to Nchar(140) or Nchar(0020) in SQL. I tried to replace them using the replace function :

declare @.foo varchar(8000)
set @.foo='select top 4 Replace(priMarykey,Nchar(0020),'') as primarykey,
Replace(Sitename,Nchar(0020),'') as Sitename,
Replace(REgNumber,Nchar(0020),'') as REgNumber,
Replace(ContactEmpFirstNames,Nchar(0020),'') as ContactEmp
from organisationsite'
EXEC master..xp_sendmail @.recipients = 'Bertrandk@.Holala.com',
@.subject = 'Test mail',
@.query =@.foo,@.no_header= 'TRUE',
@.message='some attachement',
@.attach_results = 'TRUE'
This does not work, the spaces are still in the file
Is there something I can do to correct this?
Thanks in anticipation for your help.

Hi,

what about converting your whole message to VARCHAR ? Will that work in your situation, or do you need additional stored information in UNICODE from your columns ?

set @.foo='select top 4 CONVERT(VARCHAR(4000),Replace(priMarykey,Nchar(0020),'')) (...)

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||I tried that but it did not help. I would like to know if everyone just uses notepad to open sql attachements or if they do use other text editors, do they also get the same problem that I am getting?|||

No, never has that problem, although dealing with UNICODE data. Could you please post you table definition in here ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de