Analysis & Data Transformation Services Pricing

Does anyone know how to purchase the analysis services nad
data transformation services plug-in's? We have existing
licensing for server 2000.
ThanksThey are part of SQL Server so covered in your existing licence provided =the analyis server is on the same system as the SQL Server, ie to run =them on separate systems requires separate licences.
If in doubt you should check with Microsoft.
Mike John
"Steve H" <anonymous@.discussions.microsoft.com> wrote in message =news:29b801c3af9e$0163d800$a601280a@.phx.gbl...
> Does anyone know how to purchase the analysis services nad > data transformation services plug-in's? We have existing > licensing for server 2000.
> > Thankssql

An internal error occurred on the report server. See the error log

I've just installed SQL Reporting Services. I have a Report Project in
Visual Studio and added already existing reports. When I try to build the
solution, I get the following error for each report:
An internal error occurred on the report server. See the error log for more
details.
Looking in the Windows Event Viewer, there are no entries that correspond to
this. The most recent entry is an hour ago, even though I got this error
message two minutes ago.
Am I looking in the wrong place? Does anyone have any other suggestions as
to why this won't work?
BTW: I also tried to upload the report on the reportserver. When I try to
run a report from there, I get a similar error:
An internal error occurred on the report server. See the error log for more
details. (rsInternalError) Get Online Help
SELECT permission denied on object 'ExecutionCache', database
'ReportServerTempDB', owner 'dbo'. SELECT permission denied on object
'ExecutionCache', database 'ReportServerTempDB', owner 'dbo'. SELECT
permission denied on object 'SnapshotData', database 'ReportServerTempDB',
owner 'dbo'.
Thank you in advance,
MKFigured it out! I had already set the permissions for the ASPNET user in the
ReportServer table to allow RSExecRole... just had to do the same for the
ReportServerTempDB
"k89mmk" wrote:
> I've just installed SQL Reporting Services. I have a Report Project in
> Visual Studio and added already existing reports. When I try to build the
> solution, I get the following error for each report:
> An internal error occurred on the report server. See the error log for more
> details.
> Looking in the Windows Event Viewer, there are no entries that correspond to
> this. The most recent entry is an hour ago, even though I got this error
> message two minutes ago.
> Am I looking in the wrong place? Does anyone have any other suggestions as
> to why this won't work?
> BTW: I also tried to upload the report on the reportserver. When I try to
> run a report from there, I get a similar error:
> An internal error occurred on the report server. See the error log for more
> details. (rsInternalError) Get Online Help
> SELECT permission denied on object 'ExecutionCache', database
> 'ReportServerTempDB', owner 'dbo'. SELECT permission denied on object
> 'ExecutionCache', database 'ReportServerTempDB', owner 'dbo'. SELECT
> permission denied on object 'SnapshotData', database 'ReportServerTempDB',
> owner 'dbo'.
> Thank you in advance,
> MK|||k89mmk wrote:
> An internal error occurred on the report server. See the error log
> for more details. (rsInternalError) Get Online Help
> SELECT permission denied on object 'ExecutionCache', database
> 'ReportServerTempDB', owner 'dbo'. SELECT permission denied on object
> 'ExecutionCache', database 'ReportServerTempDB', owner 'dbo'. SELECT
> permission denied on object 'SnapshotData', database
> 'ReportServerTempDB', owner 'dbo'.
You will find the reporting-logs normally in:
C:\Programme\Microsoft SQL Server\MSSQL\Reporting Services\LogFiles
Your error assumed that you use nt-authentification and your nt-account has
no sufficient rights on the ReportServer-DBs OR the account under wich the
reportenginge is running has no sufficient rights on his own dbs
regards
Frank
www.xax.de

An existing connection was forcibly closed by the remote host

It is very appreciated if someone can help me on this problem:

I have 2 machines, connected with workgroup (not domain machine), 1 is win2003 sp1, 1 is xp.

How can I connect SSAS from XP machine with windows authentication?

Looks the Native client fix the windows authentication with local user name.

When I connect from XP, my application with following codes:

string connstr = "Provider=MSOLAP.3;Data Source=amsserver;Password=accenture;User ID=administrator;Initial Catalog=MIP2ASProject";

AdomdConnection objConnection = new AdomdConnection();

// Assign the connection string and try to

//open a connection.

objConnection.ConnectionString = connstr;

objConnection.Open();

It give me this error:

An existing connection was forcibly closed by the remote host

First of all, you dont need the User ID and Password in the connection string if you are connecting using TCP/IP (you would use them only when connecting with HTTP using Basic auth).

Second, in order to use "Provider=MSOLAP.3" you will need to install "Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider" provider from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Edward,

Yes, I have installed oledb AS9 provider.

If you think SSAS only support windows authentication which must use inside the domain, I really couldn't understand why oledbconnection works fine with same connection string?

I am not sure about whether impersonation belong to windows authentication, but, my testing prove that oledb can connect in this way, adomd can't.

BTW, thankful if anyone can let me know how to enable SSAS's anonymous connection?

Jerry

|||

it is an antivirus problem

uninstall antivirus and try again

what antivirus do you have?

|||

What version of ADOM.NET are you using?

Try downloading and installing "Microsoft ADOMD.NET " from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

An existing connection was forcibly closed by the remote host

Hi,

I am using ASP.NET 2.0 application to fetch data from a SQL server 2005 using stroed proc.

Stored proc is also using one table from Linked Server (SQL 2000). Linked Server is configured to use login's current security context.

I am getting the following error message

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 6/15/2006 4:26:30 PM
Event time (UTC): 6/15/2006 8:26:30 PM
Event ID: 988964e6fa5249e38b1bc6c9a5ecd1e4
Event sequence: 850
Event occurrence: 7
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/1/Root/MyApp-1-127948732130920720
Trust level: Full
Application Virtual Path: /MyApp
Application Path: C:\Inetpub\wwwroot\MyApp\
Machine name: MyServer
Process information:
Process ID: 352
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE
Exception information:
Exception type: SqlException
Exception message: TCP Provider: An existing connection was forcibly closed by the remote host.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Request information:
Request URL: http://MyServer/MyApp/Page1.aspx
Request path: /MyServer/MyApp/Page1.aspx
User host address: XX.XX.XXX.XX
User: DomainName/UserName

Is authenticated: True
Authentication Type: Negotiate
Thread account name: NT AUTHORITY\NETWORK SERVICE
Thread information:
Thread ID: 1
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

Please Guide

Thanks

Is it intermittent or consistent?

1. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

This looks like a consistent issue. It normally means that you need to configuration IIS account to access SQL Server. You can take a look at this link, http://support.microsoft.com/kb/247931/en-us.

2. TCP Provider: An existing connection was forcibly closed by the remote host.

This normally is an intermittent issue. It happens when you enable connection pooling and server closes the connection for reason like connection clean up, restart, network failure, the ASP.NET( sqlclient ) use dead connection from the pool to write data and thus get the failure you see. Re-retry connection should solve the issue.

|||

Hi Nan,

Re: 1. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

IIS is configured properly to access data from SQL Server and it is working fine when IIS & SQL are on the same box. But the above message comes only when the stored proc tries to access data from a 2nd SQL Server (linked server which is not on the same box as IIS). Somehow it is not passing the credential to the 2nd SQL Server. 2nd SQL server is linked using login's current security context. Also ASP.NET 2 web.config file says <identity impersonate="true" />. I am using NT domain right now. Is this issue of "Double hop"?

Re: 2. TCP Provider: An existing connection was forcibly closed by the remote host.

Is connection pooling is by default? Can we change so that it should not use it?

Thanks

Shafiq

An existing connection was forcibly closed by the remote host

Hi,

I'm running a website using MSSQL 2000. Sometimes (not always) I get this error message on the website:

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

I have also websites running with MySQL and those don't have this problem.
I searched google but I couldn't find usefull information on this problem.

Someone who knows what's going on here ?

All I've found untill now is that it has something to do with the server not responding within a certain time, and thus assuming the connection was closed.

I can't find how I can just let it reconnect again in stead of giving me this error message?

An error occurred while receiving data: '10054(An existing connection was forcibly closed by th

What does this error mean? I get this in the transmission_status column of sys.transmission_queue

An error occurred while receiving data: '10053(An established connection was aborted by the software in your host machine.)'.

Also, does the number in this message correspond to error 10053 in sys.messages?

Thanks

It means that the peer is closing the connection during login, most likely due to lack of CONNECT permission on the endpoint. You need to attch the profiler to the peer and monitor the 'Security Audit/Audit Broker Login' and 'Broker/Broker connection' events.

10054 and 10053 are system errors (OS), they are WSAECONNRESET and WSAECONNABORTED. they are described here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/winsock/winsock/windows_sockets_error_codes_2.asp

HTH,
~ Remus

|||

Thanks

What happened was that my certificates expired (set start date as 01/01/05 and neglected an expiry date)

An error occurred while receiving data: '10054(An existing connection was forcibly closed b

What does this error mean? I get this in the transmission_status column of sys.transmission_queue

An error occurred while receiving data: '10053(An established connection was aborted by the software in your host machine.)'.

Also, does the number in this message correspond to error 10053 in sys.messages?

Thanks

It means that the peer is closing the connection during login, most likely due to lack of CONNECT permission on the endpoint. You need to attch the profiler to the peer and monitor the 'Security Audit/Audit Broker Login' and 'Broker/Broker connection' events.

10054 and 10053 are system errors (OS), they are WSAECONNRESET and WSAECONNABORTED. they are described here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/winsock/winsock/windows_sockets_error_codes_2.asp

HTH,
~ Remus

|||

Thanks

What happened was that my certificates expired (set start date as 01/01/05 and neglected an expiry date)

An additional SQL 2005 instance - How much extra resources does it consume ?

Dear all,
In order to migrate my existing SQL 2000 databases to SQL 2005, I want to
install an additional SQL 2005 instance, how much extra computer resources,
eg. CPU, memory, ... will it consume ?
Do you have any ideal ?
Or where can I get this information ?
Please tell me if you know, thanks a lot.It depends on how much heavily you are gonna use it. I can at least say
that, every connection to SQL Server 2005 will consume 28KB if I'm
remembering correctly :)
Other stuff is environment-specific. How densely you use your databases,
your queries...
--
Ekrem Önsoy
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:470a4545$1@.127.0.0.1...
> Dear all,
> In order to migrate my existing SQL 2000 databases to SQL 2005, I want to
> install an additional SQL 2005 instance, how much extra computer
> resources,
> eg. CPU, memory, ... will it consume ?
> Do you have any ideal ?
> Or where can I get this information ?
> Please tell me if you know, thanks a lot.
>|||Thanks, I will migrate my SQL 2000 databases to this additional SQL 2005
instance one by one, so you can see, there will be no change either in the
total no. of databases or database work load.
However, people can still agrue that the server has one more SQL 2005
instance, it might consume a certain amount of resources, so, I have to know
how much resources will the SQL 2005 instance consume.
Or where can I get this information ?
Please tell me if you know.
"Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
news:795C55B8-B3D7-4134-8023-9C411C74E363@.microsoft.com...
> It depends on how much heavily you are gonna use it. I can at least say
> that, every connection to SQL Server 2005 will consume 28KB if I'm
> remembering correctly :)
> Other stuff is environment-specific. How densely you use your databases,
> your queries...
> --
> Ekrem Önsoy
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:470a4545$1@.127.0.0.1...
> > Dear all,
> >
> > In order to migrate my existing SQL 2000 databases to SQL 2005, I want
to
> > install an additional SQL 2005 instance, how much extra computer
> > resources,
> > eg. CPU, memory, ... will it consume ?
> >
> > Do you have any ideal ?
> > Or where can I get this information ?
> >
> > Please tell me if you know, thanks a lot.
> >
> >
>|||It is impossible to say. You are in the best position to answer that if you
are migrating existing dbs on the same server you should have an idea
already. Two instances will always use more resources than a single instance
but how it breaks down will be different depends on many factors, none of
which we know anything about. Can't you just upgrade the 2000 instance to
2005 and keep the dbs that require it in 80 compatibility mode?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:470a515c$1@.127.0.0.1...
> Thanks, I will migrate my SQL 2000 databases to this additional SQL 2005
> instance one by one, so you can see, there will be no change either in the
> total no. of databases or database work load.
> However, people can still agrue that the server has one more SQL 2005
> instance, it might consume a certain amount of resources, so, I have to
> know
> how much resources will the SQL 2005 instance consume.
> Or where can I get this information ?
> Please tell me if you know.
> "Ekrem Önsoy" <ekrem@.btegitim.com> wrote in message
> news:795C55B8-B3D7-4134-8023-9C411C74E363@.microsoft.com...
>> It depends on how much heavily you are gonna use it. I can at least say
>> that, every connection to SQL Server 2005 will consume 28KB if I'm
>> remembering correctly :)
>> Other stuff is environment-specific. How densely you use your databases,
>> your queries...
>> --
>> Ekrem Önsoy
>>
>> "cpchan" <cpchaney@.netvigator.com> wrote in message
>> news:470a4545$1@.127.0.0.1...
>> > Dear all,
>> >
>> > In order to migrate my existing SQL 2000 databases to SQL 2005, I want
> to
>> > install an additional SQL 2005 instance, how much extra computer
>> > resources,
>> > eg. CPU, memory, ... will it consume ?
>> >
>> > Do you have any ideal ?
>> > Or where can I get this information ?
>> >
>> > Please tell me if you know, thanks a lot.
>> >
>> >
>

AMO: Role Member not existing in Active Directory

Hi,

I have a function in VB.Net that checks if role member is existing in Role. It's doing well, however, if role member is not existing in Active Directory it would prompt me this error during role update:

No mapping between account names and security IDs was done.

So, i provided an on error-resume-next error handler just for it to ignore the error. However, in the succeeding loops, eventhough the role member is existing in AD and not existing in Role, it's prompting me the same error each time it does a Role Update.

Was the error cached the first time? How do I manage this situation well?

cherriesh

Are you saying the user account did exist in AD, was added to your SSAS role, and then removed from AD which gives you the initial error? If so, was the user account disabled in AD or dropped from AD?

B.

|||

Hi,

the domain name is maintained in a specific table in the database which the user had keyed in. I access this table to add the domain name in my SSAS role. However, if the domain name is not really a real one or was entered with typo error, the SSAS will prompt an error at Role1.Update() command since the account entered has no match in AD. If i resume-next on this error, in the succeeding role update, it will prompt me the same error.

cherrie

|||

One of my guys ran into a similar issue on an AMO script. Here is the code he used in the script to resolve the problem:

Code Snippet

Try
currentRole.Members.Add(New RoleMember(RTrim(LTrim(dr(1).ToString))))
currentRole.Update()
Catch ex As Exception
currentRole.Refresh()
End Try

Hope that helps,

B.

AMO: Merging Partitions

Does anybody have a sample AMO code for merging two existing partitions through AMO? Please help.

Thanks,

Nagy

Copied from http://technet.microsoft.com/en-us/library/ms345091.aspx

Merging Partitions

Merging partitions means performing any operation that results in two or more partitions becoming one partition.

Merging partitions is a method of the Partition object. This command merges the data of one or more source partitions into a target partition and deletes the source partitions.

Partitions can be merged only if they meet all the following criteria:

Partitions are in the same measure group.
Partitions are stored in the same mode (MOLAP, HOLAP, and ROLAP).
Partitions reside on the same server; remote partitions can be merged if on the same server.
|||

Thanks for your reply Adrian. This merges all the partitions in a measure group into one. What is the code to merge Partition_a with Partition_b when my measure group has Partition_a, Partition_b, Partition_c, Partition_d?

Regards,

Nagy

|||

If you want to merge A and B into A, create an array of size 1 with B and call Merge on partition A with that array.

The Merge method will merge the specified collection of Partitions (the sources) into the Partition on which is called (the destination).

Adrian Dumitrascu

AMO: Convert string to Role and Role Member

Hi,

I have a functionality in my AMO that deletes role member from a specific role but first I must check if that role member is existing in that role before calling any delete.

This is the function that checks if role member is existing:

Function RoleContains(ByVal MyRole As Role, ByVal MemberName As String) As Boolean

For Each rm As RoleMember In MyRole.Members

If String.Compare(rm.Name, MemberName, True) = 0 Then

Return True

End If

Next

End Function

However, the one i will pass to the parameters are taken from the textbox of vb.net. so the data type is string and i'm having a problem passing this to Role object.

how can i do that?

cherriesh

The role collection includes a FindByName() method. So, if you were after a database role object, you can get a role object from it's name by doing something like:

myRole = db.Roles.FindByName("Test Role")

AMO: Check if role member is existing in Role

Hi,

In AMO, how do I check if role member is existing in Role? I have this command (VB.net):

If UserRole1.Members.Contains(mydatareader(0)) = true then

.

.

end if

But it keeps having an error:

Unable to cast object of type 'System.String' to type 'Microsoft.AnalysisServices.RoleMember'.

How do i check properly?

thanks .

cherriesh

The contains function expects a MemberRole reference, not a string. I can't see anyway of checking directly if a role contains a given member name so you would have to loop over the collection. Something like the following should work. Note: I have coded this off the top of my head so I hope that I have not made any typo's but you should get the general idea.

Code Snippet

If RoleContains(UserRole1, mydatareader(0)) then

.

.

end if

Function RoleContains( myRole as Role, memberName as string)

For each rm as RoleMember in myRole.Members

'// I have used string.compare to do a case-insensitive compare

If string.compare(rm.Name, memberName,true) = 0 then

return true

End If

Next

Return false

End Function

ambigous column name message

I am modifying an existing stored procedure in SQL server 2005. I have added a new field to the sp and am now receiving an ambiguous column name message. The column being referred to was in the sp before I modified. The column is on the line above where I added my new field ( EMPLOYEE NUMBER) to the sp. I am at a loss to why I'm getting this error message when executing the sp because this column existed before I modified. Can anyone help me understand why I'm getting this message all of a sudden and/ or where to look for help? Thanks in advance for any light you can help shed on this matter. Code snippet is below:

[BILL DUE DATE], [PAYMENT DATE AND TIME],

[EMPLOYEE NUMBER] )

Msg 209, Level 16, State 1, Procedure spBuildNoReasonLetter, Line 34

Ambiguous column name 'PAYMENT DATE AND TIME'.

There is no way we can 'guess' what is happening without seeing the code.

Please post the entire stored procedure code.

|||

Sorry, here is the sp:

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERPROCEDURE [dbo].[spBuildNoReasonLetter]

AS

setnocounton

TRUNCATETABLE [NO REASON LETTER];

INSERTINTO [NO REASON LETTER] ( [EMPLOYER NUMBER], [EMPLOYER NAME],

[CONTACT PERSON], [ADDRESS LINE 1],

[ADDRESS LINE 2], [ADDRESS LINE 3],

[EMPLOYEE SSN], [SERVICE CODE],

[EMPLOYEE NAME], [BILLING PERIOD],

[BILL DUE DATE], [PAYMENT DATE AND TIME],

[EMPLOYEE NUMBER] )

SELECTDISTINCT [NIGHT BATCH TABLE].[EMPLOYER NUMBER],

[COMPANY ADDRESS].[FULL NAME] AS [EMPLOYER NAME],

[COMPANY ADDRESS].[CONTACT PERSON],

[COMPANY ADDRESS].[ADDRESS LINE 1],

[COMPANY ADDRESS].[ADDRESS LINE 2],

[COMPANY ADDRESS].[CITY]+', '+[COMPANY ADDRESS].[STATE]+' '+[COMPANY ADDRESS].[ZIP CODE] AS [ADDRESS LINE 3],

[NIGHT BATCH TABLE].[EMPLOYEE SSN],

[NIGHT BATCH TABLE].[SERVICE CODE],

[MAIN EMPLOYEE].[FULL NAME] AS [EMPLOYEE NAME],Convert(varchar(10),

[COMPANY PAY TABLE].[BILLING PERIOD START],101)+' Thru '+Convert(varchar(10),[COMPANY PAY TABLE].[BILLING PERIOD END],101)AS [BILLING PERIOD],

[NIGHT BATCH TABLE].[BILL DUE DATE], [PAYMENT DATE AND TIME],

[MAIN EMPLOYEE].[EMPLOYEE NUMBER]

FROM(([NIGHT BATCH TABLE] INNERJOIN [COMPANY ADDRESS] ON([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY ADDRESS].[ADDRESS KEY])AND

([NIGHT BATCH TABLE].[ADDRESS TYPE] = [COMPANY ADDRESS].[ADDRESS TYPE]))

INNERJOIN [MAIN EMPLOYEE] ON [NIGHT BATCH TABLE].[EMPLOYEE SSN] = [MAIN EMPLOYEE].[EMPLOYEE SSN])

INNERJOIN [NO REASON LETTER] ON [MAIN EMPLOYEE].[EMPLOYEE NUMBER] = [NO REASON LETTER].[EMPLOYEE NUMBER]

INNERJOIN [COMPANY PAY TABLE] ON([NIGHT BATCH TABLE].[BILL DUE DATE] = [COMPANY PAY TABLE].[BILL DUE DATE])AND

([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY PAY TABLE].[EMPLOYER NUMBER])

WHERE([NIGHT BATCH TABLE].[REASON CODE]='X'AND

[MAIN EMPLOYEE].[STATUS CODE] In('00','01','09'));

UPDATE NRL

SET NRL.[CARRIER NAME] = CA.[FULL NAME],

NRL.[LOGO PATH] = CA.[CONTACT PERSON],

NRL.[TOLL FREE SERVICE NO] = CA.[TOLL FREE SERVICE NO]

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY ADDRESS] AS CA ON(CA.[ADDRESS KEY] = NRL.[EMPLOYER NUMBER] AND

CA.[ADDRESS TYPE] ='R')

UPDATE NRL

SET NRL.[SORT FIELD1] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

ME.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 1),

NRL.[SORT FIELD2] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

ME.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 2),

NRL.[EMPLOYEE SSN] = dbo.fnReturnFieldOrBlank(ME.[EMPLOYEE SSN], CO.[DO NOT DISPLAY SSN])

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY] AS CO ON(CO.[EMPLOYER NUMBER] = NRL.[EMPLOYER NUMBER])

INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE SSN] = NRL.[EMPLOYEE SSN])

INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])

|||I have changed my code slightly and am now getting a differenet message (new code is below error message:

Msg 4104, Level 16, State 1, Procedure spBuildNoReasonLetter, Line 17

The multi-part identifier "NO REASON LETTER.EMPLOYEE NUMBER" could not be bound.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERPROCEDURE [dbo].[spBuildNoReasonLetter]

AS

setnocounton

TRUNCATETABLE [NO REASON LETTER];

INSERTINTO [NO REASON LETTER] ( [EMPLOYER NUMBER], [EMPLOYER NAME],

[CONTACT PERSON], [ADDRESS LINE 1],

[ADDRESS LINE 2], [ADDRESS LINE 3],

[EMPLOYEE SSN], [SERVICE CODE],

[EMPLOYEE NAME], [BILLING PERIOD],

[BILL DUE DATE], [PAYMENT DATE AND TIME],

[EMPLOYEE NUMBER])

SELECTDISTINCT [NIGHT BATCH TABLE].[EMPLOYER NUMBER],

[COMPANY ADDRESS].[FULL NAME] AS [EMPLOYER NAME],

[COMPANY ADDRESS].[CONTACT PERSON],

[COMPANY ADDRESS].[ADDRESS LINE 1],

[COMPANY ADDRESS].[ADDRESS LINE 2],

[COMPANY ADDRESS].[CITY]+', '+[COMPANY ADDRESS].[STATE]+' '+[COMPANY ADDRESS].[ZIP CODE] AS [ADDRESS LINE 3],

[NIGHT BATCH TABLE].[EMPLOYEE SSN],

[NIGHT BATCH TABLE].[SERVICE CODE],

[MAIN EMPLOYEE].[FULL NAME] AS [EMPLOYEE NAME],Convert(varchar(10),

[COMPANY PAY TABLE].[BILLING PERIOD START],101)+' Thru '+Convert(varchar(10),[COMPANY PAY TABLE].[BILLING PERIOD END],101)AS [BILLING PERIOD],

[NIGHT BATCH TABLE].[BILL DUE DATE], [PAYMENT DATE AND TIME],

[MAIN EMPLOYEE].[EMPLOYEE NUMBER]

FROM(([NIGHT BATCH TABLE] INNERJOIN [COMPANY ADDRESS] ON([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY ADDRESS].[ADDRESS KEY])AND

([NIGHT BATCH TABLE].[ADDRESS TYPE] = [COMPANY ADDRESS].[ADDRESS TYPE]))

INNERJOIN [MAIN EMPLOYEE] ON [NIGHT BATCH TABLE].[EMPLOYEE SSN] = [MAIN EMPLOYEE].[EMPLOYEE SSN])

INNERJOIN [MAIN EMPLOYEE] AS MET ON [NO REASON LETTER].[EMPLOYEE NUMBER] = [MAIN EMPLOYEE].[EMPLOYEE NUMBER]

INNERJOIN [COMPANY PAY TABLE] ON([NIGHT BATCH TABLE].[BILL DUE DATE] = [COMPANY PAY TABLE].[BILL DUE DATE])AND

([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY PAY TABLE].[EMPLOYER NUMBER])

WHERE([NIGHT BATCH TABLE].[REASON CODE]='X'AND

[MAIN EMPLOYEE].[STATUS CODE] In('00','01','09'));

UPDATE NRL

SET NRL.[CARRIER NAME] = CA.[FULL NAME],

NRL.[LOGO PATH] = CA.[CONTACT PERSON],

NRL.[TOLL FREE SERVICE NO] = CA.[TOLL FREE SERVICE NO]

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY ADDRESS] AS CA ON(CA.[ADDRESS KEY] = NRL.[EMPLOYER NUMBER] AND

CA.[ADDRESS TYPE] ='R')

UPDATE NRL

SET NRL.[SORT FIELD1] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

MET.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 1),

NRL.[SORT FIELD2] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],

ME.[FULL NAME],

ME.[EMPLOYEE SSN],

MET.[EMPLOYEE NUMBER],

ME.[DEPARTMENT CODE],

ME.[LOCATION CODE], 2),

NRL.[EMPLOYEE SSN] = dbo.fnReturnFieldOrBlank(ME.[EMPLOYEE SSN], CO.[DO NOT DISPLAY SSN])

FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY] AS CO ON(CO.[EMPLOYER NUMBER] = NRL.[EMPLOYER NUMBER])

INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE SSN] = NRL.[EMPLOYEE SSN])

INNERJOIN [MAIN EMPLOYEE] AS MET ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])

|||

Hi,

you are not referencing the Table in the Select clause, therefore you cannot use it in the join part.

BTW. Did I mention that it is horrorible to use space and special characters in defintions ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Now I'm confused.

First you TRUNCATE the [NO REASON LETTER] table.

Then you attempt to use JOIN conditions to that EMPTY table (but there is no previous mention of a JOIN to that table.. What are you trying to accomplish?

INNERJOIN [MAIN EMPLOYEE] AS MET ON [NO REASON LETTER].[EMPLOYEE NUMBER] = [MAIN EMPLOYEE].[EMPLOYEE NUMBER]

Even if it 'could' happen (and it just can't), since the table is empty, this would serve to filter out ALL possible rows and nothing would be inserted.

So what's the point?

|||

One other thing to keep in mind is that if you are going to Alias a table in a join clause, you probably should use it in the join.

INNERJOIN [MAIN EMPLOYEE] AS MET ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])

I would think that you would want to use MET.[EMPLOYEE NUMBER] instead of ME.

Ben Miller