Please Help Me To Resolve The 28017 Error (An Incorrect Or Unsupported HTTP Function Call Was Made), Thanks A Lot.

Apr 15, 2007

Now I need to synchonize the data in Pocket PC to the remote sql server database.

I have configured the publication and the virtual directory in the server. When the Pocket PC directly connects to the server (that to say, the Pocket PC connects to the server through the ActiveSync), the data can be synchronized successfully. But when the Pockec PC connects to another computer () through the ActiveSync (this computer connects to the server through the network), the data cannot be synchronized. But when I run the application in the emulator in the same computer, the data can be synchonized with the server.



using the following codes, the error message is:

Error Code: 80004005

Message:

Minor Err: 28017

Source: Microsoft SQL Server 2005 Mobile Edtion.



private void ShowErrors(SqlCeException ex)
{
SqlCeErrorCollection oErrors = ex.Errors;

StringBuilder oStrBld = new StringBuilder();

Exception oInner = ex.InnerException;

foreach (SqlCeError oErr in oErrors)
{
oStrBld.Append("
Error Code: " + oErr.HResult.ToString("X"));
oStrBld.Append("
Message : " + oErr.Message);
oStrBld.Append("
Minor Err.: " + oErr.NativeError);
oStrBld.Append("
Source : " + oErr.Source);

foreach (int iNumPar in oErr.NumericErrorParameters)
{
if (iNumPar != 0)
oStrBld.Append("
Num. Par. : " + iNumPar);
}
foreach (String sErrPar in oErr.ErrorParameters)
{
if (sErrPar != String.Empty)
oStrBld.Append("
Err. Par. : " + sErrPar);
}
MessageBox.Show(oStrBld.ToString(), "SqlCeException");
oStrBld.Remove(0, oStrBld.Length);
}
}


The environment is:

Client:

Sql mobile (sql ce 3.0)



Server:

windows 2003 server

sql server 2000 with sp4

IIS 6



And I'm quite sure the virtual directory is correct.



Any body can help me? It's urget for me, thanks in advance.





View 3 Replies


ADVERTISEMENT

An Incorrect Or Unsupported HTTP Function Call Was Made Error

Apr 13, 2006

Hi All,

I've got this error "An incorrect or unsupported HTTP function call was made"

I can't browse SQL CE Agent using IE also.

How to solve this error? TQ

View 11 Replies View Related

Unsupported HTTP Function Call Was Made Error.

Jun 28, 2006



Hi experts,

I'm new to this forum. I have been searching around for the solution to the problem that i'm having, and I can solve it. I'm having the same problem as the title say....unsupported HTTP function call....The thing is that it worked on my development server. When I transfer all the source to a new pc and execute it, it failed with the error in the subject. I uninstalled and reinstalled everything. I ran the http://iissvrname/iisvirtualdir/sscesa20.dll and I get this "SQL Server CE Server Agent" which tell me that IIS & SQL CE are setup correctly. Here is the error log:

Source: Microsoft SQL Server 2000 windows CE edition
Number: 80004005
NativeError: 28017
Description: An incorrect or unsupported HTTP function call was made.
[,,,,,]
Param = 0
Param = 0
Param = 0
Param =
Param =
Param =

I'm running SQL 7 on the PC and using RDA to pull data from SQL7 db to the PocketPC.

Please help!!!

Thanks!

View 4 Replies View Related

SQL Server 2005, SQL Server Mobile, SQL Server Management Studio. Unsupported HTTP Function Call

Sep 5, 2005

Hi All,

View 14 Replies View Related

Inncorrect Or Unssuported Http Function Call

Dec 1, 2005

I am creating a vb.net app for a windows ce handheld device. I am using replication to create the database on the handheld. I am getting this error when this error:

View 1 Replies View Related

Connection Cannot Be Made To Http://localhost/ReportServer

Jun 14, 2007

I have developed a reports in Business Intelligence Development studio and when I am tring to deploy it.
It says that
Connection cannot be made to http://localhost/ReportServer
What does it really mean.

View 6 Replies View Related

A Connection Could Not Be Made To The Report Server Http://localhost/ReportServer.

Jan 19, 2007

Hello Guys,

Please take me out from this hell. I created report using VS2005 Business Intelligence Project. It builds fine. I can also see data in the design time. But when I am trying to deploy it gives me this error.

TITLE: Microsoft Report Designer
------------------------------

A connection could not be made to the report server http://localhost/ReportServer.

------------------------------
ADDITIONAL INFORMATION:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<html>
<head>
<title>Configuration Error</title>
<style>
body {font-family:"Verdana";font-weight:normal;font-size: .7em;color:black;}
p {font-family:"Verdana";font-weight:normal;color:black;margin-top: -5px}
b {font-family:"Verdana";font-weight:bold;color:black;margin-top: -5px}
H1 { font-family:"Verdana";font-weight:normal;font-size:18pt;color:red }
H2 { font-family:"Verdana";font-weight:normal;font-size:14pt;color:maroon }
pre {font-family:"Lucida Console";font-size: .9em}
.marker {font-weight: bold; color: black;text-decoration: none;}
.version {color: gray;}
.error {margin-bottom: 10px;}
.expandable { text-decoration:underline; font-weight:bold; color:navy; cursor:hand; }
</style>
</head>

<body bgcolor="white">

<span><H1>Server Error in '/ReportServer' Application.<hr width=100% size=1 color=silver></H1>

<h2> <i>Configuration Error</i> </h2></span>

<font face="Arial, Helvetica, Geneva, SunSans-Regular, sans-serif ">

<b> Description: </b>An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.
<br><br>

<b> Parser Error Message: </b>An error occurred loading a configuration file: Failed to start monitoring changes to 'c:inetpubwwwroot' because access is denied.<br><br>

<b>Source Error:</b> <br><br>

<table width=100% bgcolor="#ffffcc">
<tr>
<td>
<code><pre>

[No relevant source lines]</pre></code>

</td>
</tr>
</table>

<br>

<b> Source File: </b> c:inetpubwwwrootweb.config<b> Line: </b> 0
<br><br>

<hr width=100% size=1 color=silver>

<b>Version Information:</b> Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210

</font>

</body>
</html>
<!--
[HttpException]: Failed to start monitoring changes to 'c:inetpubwwwroot' because access is denied.
at System.Web.FileChangesMonitor.FindDirectoryMonitor(String dir, Boolean addIfNotFound, Boolean throwOnError)
at System.Web.FileChangesMonitor.StartMonitoringFile(String alias, FileChangeEventHandler callback)
at System.Web.Configuration.WebConfigurationHost.StartMonitoringStreamForChanges(String streamName, StreamChangeCallback callback)
at System.Configuration.BaseConfigurationRecord.MonitorStream(String configKey, String configSource, String streamname)
at System.Configuration.BaseConfigurationRecord.InitConfigFromFile()
[ConfigurationErrorsException]: An error occurred loading a configuration file: Failed to start monitoring changes to 'c:inetpubwwwroot' because access is denied. (c:inetpubwwwrootweb.config)
at System.Configuration.ConfigurationSchemaErrors.ThrowIfErrors(Boolean ignoreLocal)
at System.Configuration.BaseConfigurationRecord.ThrowIfParseErrors(ConfigurationSchemaErrors schemaErrors)
at System.Configuration.BaseConfigurationRecord.GetSectionRecursive(String configKey, Boolean getLkg, Boolean checkPermission, Boolean getRuntimeObject, Boolean requestIsHere, Object& result, Object& resultRuntimeObject)
at System.Configuration.BaseConfigurationRecord.GetSection(String configKey, Boolean getLkg, Boolean checkPermission)
at System.Configuration.BaseConfigurationRecord.GetSection(String configKey)
at System.Web.Configuration.RuntimeConfig.GetSectionObject(String sectionName)
at System.Web.Configuration.RuntimeConfig.GetSection(String sectionName, Type type, ResultsIndex index)
at System.Web.Configuration.RuntimeConfig.get_HealthMonitoring()
at System.Web.Configuration.HealthMonitoringSectionHelper..ctor()
at System.Web.Management.HealthMonitoringManager..ctor()
at System.Web.Management.HealthMonitoringManager.Manager()
at System.Web.Management.WebBaseEvent.RaiseRuntimeError(Exception e, Object source)
at System.Web.HttpResponse.ReportRuntimeError(Exception e, Boolean canThrow, Boolean localExecute)
at System.Web.HttpRuntime.FinishRequest(HttpWorkerRequest wr, HttpContext context, Exception e)
-->
--. (Microsoft.ReportingServices.Designer)

------------------------------
BUTTONS:

OK
------------------------------




View 8 Replies View Related

A Connection Could Not Be Made To The Report Server Http://localhost/reportServer..

Sep 18, 2006

hi,

I got this error when I was trying to deploy the reporting into IIS..

A connection could not be made to the report server http://localhost/reportServer.

------------------------------
ADDITIONAL INFORMATION:

The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. (Microsoft.ReportingServices.Designer)

------------------------------

The request failed with HTTP status 404: Not Found. (Microsoft.ReportingServices.Designer)


I am not able to solve it please help.

Thanks.

View 12 Replies View Related

A Connection Could Not Be Made To The Reporting Server Http://localhost/reportserver

Jul 21, 2006

Hi all,

When I build a simple project using the report wizard project.

When i deploy the report i get the following error.





TITLE: Microsoft Report Designer
------------------------------

A connection could not be made to the report server http://localhost/ReportServer.

------------------------------
ADDITIONAL INFORMATION:

Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'.
The request failed with the error message:
--
<?xml version="1.0" ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Server Unavailable
</title>
</head>
<body>
<h1><span style="font-family:Verdana;color: #ff3300">Server Application Unavailable
</span></h1>
<p>
<span style="font-family:Verdana;">
The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.
</span></p>
<p>
<b>Administrator Note:
</b> An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.
</p>
</body>
</html>

--. (Microsoft.ReportingServices.Designer)

------------------------------
BUTTONS:

OK
------------------------------


Could some one come to my rescue.

Thanx in advance.

Ronald

View 4 Replies View Related

A Connection Could Not Be Made To The Report Server Http://[IP Address]/ReportServer

Dec 6, 2006

Hi

I have sql server 2005 Business Intelligent studio on my machine and Report server on some other machine. I created one rdl file and tried to deploy it on the report server. I have checked the report folder name and everything. But the error mentioned in the subject is being thrown. Additional information is

The request failed with HTTP status 407: Proxy Authentication Required. (Microsoft.ReportingServices.Designer)

Please let me know what all I need to do to resolve the problem.

Regards

Rajesh





View 1 Replies View Related

Error: The Selected Object(s) Use An Unsupported Database Provider

Nov 26, 2007



I just installed the latest VS 2008 C# Express and SSCE 3.5. I am trying to test the "LINQ to SQL" feature. I added a data connection to Nortwind.sdf, selected "Microsoft SQL Server Compact 3.5 (.NET Framework Data Provider for Microsoft SQL Server Compact 3.5)" as data provider, tested the connection, and get the following error when I drag and drop the "Customers" table on the design surface "The selected object(s) use an unsupported database provider" ?. I can create new tables and view existing tables etc. except droppinjg it on the design surface. What am I doing wrong?

The installation on my WindowsXP machine went without a hitch.


Sam

View 10 Replies View Related

Error While Calling The Roles.AddUserToRole (error Message: Cannot Resolve Collation Conflict For Equal To Operation)

Feb 5, 2006

Hi, I have developed a website in asp.net 2. I have tester it and it is working fine on my computer but when I have uploaded it to my server I'm getting an error message when the user signup. The error occurs when I'm setting the user role to 'members'.
 
Error line > Roles.AddUserToRole(user.UserName, "members")
 
The strage thig is that it is working on my computer but not on the server. My home computer and the server are running the same software versions and the website database is the same as well.
 
To double check that my code is not generating the error I have lonched 'SQL Query Analizer' and executed the folowing code on my database:
NOTE: In my database I have create the user “teeluk12� and a role “members�
 
aspnet_UsersInRoles_AddUsersToRoles "/", "teeluk12", "members", "5/02/2006 4:44:33 pm"
 
Once again the code is working on my home computer but not on the server. On the server I'm getting the following error:
 
Server: Msg 446, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 76
Cannot resolve collation conflict for equal to operation.
 
 
 
Does anybody know what could cause the error?
Could it be some permissions that I didn't set on my server?
 
 
Thanks for my help and suggestions...
Regards,
Gonzal
 

View 9 Replies View Related

Working Since Morning, Simple Error But Unable To Resolve, ERROR : 26 , Please Help

Dec 15, 2006

dear friends,
 i started using Asp.net for developing webparts, web parts automatically connect to a database to be created and saved in the database.
i had already Sql Server 2005 Express Edition installed so my webpart page ran and automatically created the database "AspNetDb" in the "App_Data" folder.
but when i uninstalled Sql Server 2005 Express Edition and then installed the Sql Server 2005 Enterprise Edition, it gave me the following Error 26 as below :

--------------------------------------------------------- Beginning of Error Message -------------------------------------------------- 
 An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:

If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist.
If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database.
Sql Server Express must be installed on the machine.
The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
--------------------------------------------------------- End of Error Message -------------------------------------------------- 
then when i checked the Machine.config file, i found this....
  <connectionStrings>    <add name="LocalSqlServer" connectionString="data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />  </connectionStrings>
then i thought it's becoz, of the datasource thing that the Machine.Config file supports by default the default free edition of Sql Server 2005 which is the Sql Server 2005 Express Edition.
but friend in my company we use Sql Server 2005 Enterprise Edition, so can anybody pls guide me and get me out of this soup on helping me to use Sql Server 2005 Enterprise Edition by modifying either 1) Machine.Config File and/or  2) Web.Config file and/or 3) any other way, but please help i want to do this, please......
regards and thanks
Gurjit Singh 

View 2 Replies View Related

Reportserver Is Asking For Username And Password Everytime I Call Http://servername/reportserver

Dec 2, 2006

Please help everytime i call http://servername/reportserver it is presenting the login screen, if i enter the username and password then i can access the reportserver screen, same with reportmanager tool.

under iis manager for the reportserver virtual directory i have given anonymous login access.

do i have to set any other extra permissions other than anonymous login(iusr_machinenname)

thank you all for the information.



View 6 Replies View Related

Function To Call Function By Name Given As Parameter

Jul 20, 2005

I want to write function to call another function which name isparameter to first function. Other parameters should be passed tocalled function.If I call it function('f1',10) it should call f1(10). If I call itfunction('f2',5) it should call f2(5).So far i tried something likeCREATE FUNCTION [dbo].[func] (@f varchar(50),@m money)RETURNS varchar(50) ASBEGINreturn(select 'dbo.'+@f+'('+convert(varchar(50),@m)+')')ENDWhen I call it select dbo.formuła('f_test',1000) it returns'select f_test(1000)', but not value of f_test(1000).What's wrong?Mariusz

View 3 Replies View Related

How Can I Resolve This Database Error, In VS 2005?

Oct 23, 2007

Every time I try to add a new row to my table, i get this error which i don't now what it means and how i can correct it, could you please advice. i am using VS 2005 and VB Language
 
**************The error message
"No row was updated.
The data in row 2 was not committed.
Error source:mscorlib.
Error Message: Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index"

View 3 Replies View Related

Error Cannot Resolve The Collation Conflict Between

May 21, 2007

I'm getting the following errors when trying to execute the following script on the server, its part of the standard asp.net membership and roles, anybody have any ideas how I get get round this?

Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 50
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 84
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.




/****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 05/20/2007 11:23:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]
@ApplicationName nvarchar(256),
@UserNames nvarchar(4000),
@RoleNames nvarchar(4000)
AS
BEGIN
DECLARE @AppId uniqueidentifier
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@AppId IS NULL)
RETURN(2)


DECLARE @TranStarted bit
SET @TranStarted = 0

IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END

DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)
DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @Num int
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @Name nvarchar(256)
DECLARE @CountAll int
DECLARE @CountU int
DECLARE @CountR int


SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@RoleNames))
BEGIN
SELECT @NextPos = CHARINDEX(N'','', @RoleNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@RoleNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1

INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END

INSERT INTO @tbRoles
SELECT RoleId
FROM dbo.aspnet_Roles ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
SELECT @CountR = @@ROWCOUNT

IF (@CountR <> @Num)
BEGIN
SELECT TOP 1 N'''', Name
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END


DELETE FROM @tbNames WHERE 1=1
SET @Num = 0
SET @Pos = 1


WHILE(@Pos <= LEN(@UserNames))
BEGIN
SELECT @NextPos = CHARINDEX(N'','', @UserNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@UserNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1

INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END

INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

SELECT @CountU = @@ROWCOUNT
IF (@CountU <> @Num)
BEGIN
SELECT TOP 1 Name, N''''
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE u.UserId = au.UserId)

IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(1)
END

SELECT @CountAll = COUNT(*)
FROMdbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
WHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleId

IF (@CountAll <> @CountU * @CountR)
BEGIN
SELECT TOP 1 UserName, RoleName
FROM @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END

DELETE FROM dbo.aspnet_UsersInRoles
WHERE UserId IN (SELECT UserId FROM @tbUsers)
AND RoleId IN (SELECT RoleId FROM @tbRoles)
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END
'
END
GO




Thanks

View 2 Replies View Related

Error In Procedure - Cannot Resolve Collation Conflict

Dec 19, 2013

while using store procedure in report getting error as mentioned below.

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

View 1 Replies View Related

How To Fix An Error:Cannot Resolve The Collation Conflict Between Latin1_General_CI_AI And...?

Dec 12, 2007

Hello,

I have a distributed query that uses tables located on in SQL Server 2005 and SQL Server 2000? Does anyone know how I can fix the error below?

Thanks a lot for help!


Msg 468, Level 16, State 9, Line 1

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

View 13 Replies View Related

Error With Membersip And Roles Cannot Resolve The Collation Conflict Between

May 21, 2007

I'm getting the following errors when trying to execute the following script on the server, its part of the standard asp.net membership and roles, anybody have any ideas how I get get round this?   Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 50
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 84
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
  /****** Object: StoredProcedure [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles] Script Date: 05/20/2007 11:23:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[aspnet_UsersInRoles_RemoveUsersFromRoles]
@ApplicationName nvarchar(256),
@UserNames nvarchar(4000),
@RoleNames nvarchar(4000)
AS
BEGIN
DECLARE @AppId uniqueidentifier
SELECT @AppId = NULL
SELECT @AppId = ApplicationId FROM aspnet_Applications WHERE LOWER(@ApplicationName) = LoweredApplicationName
IF (@AppId IS NULL)
RETURN(2)


DECLARE @TranStarted bit
SET @TranStarted = 0

IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END

DECLARE @tbNames table(Name nvarchar(256) NOT NULL PRIMARY KEY)
DECLARE @tbRoles table(RoleId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @tbUsers table(UserId uniqueidentifier NOT NULL PRIMARY KEY)
DECLARE @Num int
DECLARE @Pos int
DECLARE @NextPos int
DECLARE @Name nvarchar(256)
DECLARE @CountAll int
DECLARE @CountU int
DECLARE @CountR int


SET @Num = 0
SET @Pos = 1
WHILE(@Pos <= LEN(@RoleNames))
BEGIN
SELECT @NextPos = CHARINDEX(N'','', @RoleNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@RoleNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@RoleNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1

INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END

INSERT INTO @tbRoles
SELECT RoleId
FROM dbo.aspnet_Roles ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredRoleName AND ar.ApplicationId = @AppId
SELECT @CountR = @@ROWCOUNT

IF (@CountR <> @Num)
BEGIN
SELECT TOP 1 N'''', Name
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT ar.LoweredRoleName FROM dbo.aspnet_Roles ar, @tbRoles r WHERE r.RoleId = ar.RoleId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(2)
END


DELETE FROM @tbNames WHERE 1=1
SET @Num = 0
SET @Pos = 1


WHILE(@Pos <= LEN(@UserNames))
BEGIN
SELECT @NextPos = CHARINDEX(N'','', @UserNames, @Pos)
IF (@NextPos = 0 OR @NextPos IS NULL)
SELECT @NextPos = LEN(@UserNames) + 1
SELECT @Name = RTRIM(LTRIM(SUBSTRING(@UserNames, @Pos, @NextPos - @Pos)))
SELECT @Pos = @NextPos+1

INSERT INTO @tbNames VALUES (@Name)
SET @Num = @Num + 1
END

INSERT INTO @tbUsers
SELECT UserId
FROM dbo.aspnet_Users ar, @tbNames t
WHERE LOWER(t.Name) = ar.LoweredUserName AND ar.ApplicationId = @AppId

SELECT @CountU = @@ROWCOUNT
IF (@CountU <> @Num)
BEGIN
SELECT TOP 1 Name, N''''
FROM @tbNames
WHERE LOWER(Name) NOT IN (SELECT au.LoweredUserName FROM dbo.aspnet_Users au, @tbUsers u WHERE u.UserId = au.UserId)

IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(1)
END

SELECT @CountAll = COUNT(*)
FROMdbo.aspnet_UsersInRoles ur, @tbUsers u, @tbRoles r
WHERE ur.UserId = u.UserId AND ur.RoleId = r.RoleId

IF (@CountAll <> @CountU * @CountR)
BEGIN
SELECT TOP 1 UserName, RoleName
FROM @tbUsers tu, @tbRoles tr, dbo.aspnet_Users u, dbo.aspnet_Roles r
WHERE u.UserId = tu.UserId AND r.RoleId = tr.RoleId AND
tu.UserId NOT IN (SELECT ur.UserId FROM dbo.aspnet_UsersInRoles ur WHERE ur.RoleId = tr.RoleId) AND
tr.RoleId NOT IN (SELECT ur.RoleId FROM dbo.aspnet_UsersInRoles ur WHERE ur.UserId = tu.UserId)
IF( @TranStarted = 1 )
ROLLBACK TRANSACTION
RETURN(3)
END

DELETE FROM dbo.aspnet_UsersInRoles
WHERE UserId IN (SELECT UserId FROM @tbUsers)
AND RoleId IN (SELECT RoleId FROM @tbRoles)
IF( @TranStarted = 1 )
COMMIT TRANSACTION
RETURN(0)
END
'
END
GOAny help appreciated thanks,   

View 5 Replies View Related

Error:Cannot Resolve Collation Conflict For Equal To Operation.

Sep 2, 2004

Exception information:System.Data.SqlClient.SqlException: Cannot resolve collation conflict for equal to operation.
Who can tell me how to resolve this problem?
Thx

View 3 Replies View Related

SQL 2012 :: How To Resolve IP Conflict Error While Server Clustering

Dec 8, 2014

I am practicing on vmware 2 node failover cluster for doing Sql failover clustering understanding.I did proper Ad Dc setup and configured san storage through iscsi software that I downloaded from net.I did target and initiator configuration for san storage on same Domain controller vm.Name of target is Target1 and has two initialtor for node1 and node2.It has two virtual disk assigned Quorumdisk.vhd and sqldisk.vhd.ON vmnode1 I have initialzed and formatted my both disk with proper volume and label.

Node which are participating in cluster are node1 and node2.while my windows clustering went fine,while Sql server installation on node 1,i got error IP Address conflict occured.

my DC has IP address:192.168.1.10
my Node1 is 192.168.1.20
node2:192.168.1.30
Cluster IP address is 192.168.1.35

What has cause this situation.What configuration is needed.How do I troubleshoot this problem as now I can not install my sql for clustering as also gave me Invalid network name error.Is my storage configuration not proper or IP address is in correct?Do I need to put my virtual disk in Clustered shared volume?

View 2 Replies View Related

Error In Store Procedure - Cannot Resolve Collation Conflict

Dec 19, 2013

While using store procedure in report getting error as mentioned below !

Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

View 2 Replies View Related

The Incoming Tabular Data Stream (TDS) Remote Procedure Call (RPC) Protocol Stream Is Incorrect

May 22, 2006

I've read the other posts related to this issue, but I'm just REALLY confused as to whats happening in my case. Like everyone else it was working fine in SQL 2000 but now in SQL 2005 there is an issue. I'm calling a stored procedure with parameters defined like this:

@action varchar(10),
@GLTransactionID int = NULL OUTPUT ,
@GLBatchID int = NULL ,
@GLAccountID int = NULL ,
@CurrencyID int = NULL ,
@LocalDebit decimal(28, 13) = NULL ,
@LocalCredit decimal(28, 13) = NULL ,
@BaseDebit decimal(28, 13) = NULL ,
@BaseCredit decimal(28, 13) = NULL ,
@TransID int =NULL,
@Description varchar(255) = NULL

I am calling this proc from VS.NET 2003 using the .Net SqlClient Data Povider (C#). I'm setting the values of the parameters like this:

cm.Parameters.Add("@action", "insert");
cm.Parameters.Add("@GLBatchID", _gLBatchID.DBValue);
cm.Parameters.Add("@GLAccountID", _gLAccountID.DBValue);
cm.Parameters.Add("@CurrencyID", _currencyID.DBValue);
cm.Parameters.Add("@LocalDebit", _localDebit.DBValue);
cm.Parameters.Add("@LocalCredit", _localCredit.DBValue);
cm.Parameters.Add("@BaseDebit", _baseDebit.DBValue);
cm.Parameters.Add("@BaseCredit", _baseCredit.DBValue);
cm.Parameters.Add("@TransID", _transID.DBValue);
cm.Parameters.Add("@Description", _description.DBValue);

When I execute the call to the stored proc I get this:

"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 8 ("@BaseDebit"): The supplied value is not a valid instance of data type numeric. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision."

Using the VS.NET command window I then inspect that parameter to see what the heck is going on and get this:

?cm.Parameters["@BaseDebit"].SqlDbType
Decimal
?cm.Parameters["@BaseDebit"].Precision
0
?cm.Parameters["@BaseDebit"].Scale
22
?cm.Parameters["@BaseDebit"].DbType
Decimal
?cm.Parameters["@BaseDebit"].Value
1000000
[System.Decimal]: 1000000

So I set a decmial parameter to 1,000,000, that parameter in the DB is defined as decimal(28,13) so should fit no problem, but it seems the Sql data provider is confused and thinks 1,000,000 is decimal (0,22)???

View 5 Replies View Related

Function Call

Mar 28, 2006

can i make a function call from stored procedure

View 3 Replies View Related

Call A Function

Feb 26, 2008

Does anybody knows how to call a function from one VB source file to another VB source file??

I have create a MDI parent form, now i want to call the function of the child form from the parent form. Does anyone know this??

View 1 Replies View Related

Incorrect Syntax In User-defined Function

Sep 2, 2005

In the script below is the DDL to create some tables and a UDF.What I'm interested in is the UDF at the end. Specifically, these fewlines:--CLOSE OTRate--DEALLOCATE OTRateELSE-- @NumRecords <= 0If I uncommment CLOSE and DEALLOCATE and check the syntax I get amessage:"Incorrect syntax near keyword ELSE"Being a good little footsoldier, I want to release resourcesexplicitly, but clearly I'm putting the CLOSE and DEALLOCATE statementsin the wrong place.Could someone please tell me where I ought to put them so that thecursor is CLOSEd and DEALLOCATEd correctly.By the way, I am not after negative comments on the data design, or thelogic (or lack of it) in the function, just why the syntax erroroccurs.Thanks as everEdwardif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Employee]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[Employee]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[PurchaseOrder]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[PurchaseOrder]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[TimesheetItem]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[TimesheetItem]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[Work]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Work]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[WorkOTRate]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[WorkOTRate]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[WorkOTRateDefaults]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[WorkOTRateDefaults]GOCREATE TABLE [dbo].[Employee] ([EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,[UserName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Title] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Surname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[DepartmentID] [int] NOT NULL ,[JobDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[StartDate] [smalldatetime] NOT NULL ,[EndDate] [smalldatetime] NULL ,[DefaultRatePerHour] [smallmoney] NULL ,[EmailAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[UserGroupID] [int] NOT NULL ,[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[LastLogon] [datetime] NULL ,[PasswordChange] [smalldatetime] NULL ,[PreviousPassword1] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword2] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword3] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword4] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[PreviousPassword5] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[PurchaseOrder] ([WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,[WorkID] [int] NOT NULL ,[OrderNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[OrderDate] [datetime] NOT NULL ,[OrderValue] [money] NOT NULL ,[FixedPrice] [bit] NOT NULL ,[Prepaid] [bit] NOT NULL ,[AllocatedHours] [int] NULL ,[RatePerHour] [money] NULL ,[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[TimesheetItem] ([ItemID] [int] IDENTITY (1, 1) NOT NULL ,[EmployeeID] [int] NOT NULL ,[TypeID] [int] NOT NULL ,[Start] [smalldatetime] NOT NULL ,[DurationMins] [int] NOT NULL ,[WorkID] [int] NULL ,[WorkComponentID] [int] NULL ,[WorkItemID] [int] NULL ,[Notes] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[OffSite] [tinyint] NULL ,[TravelTo] [smalldatetime] NULL ,[TravelToMins] [int] NULL ,[TravelFrom] [smalldatetime] NULL ,[TravelFromMins] [int] NULL ,[TravelMileage] [int] NULL ,[NonChargeableMins] [int] NULL ,[OTAuthorisedID] [int] NULL ,[OTAuthorisedDate] [smalldatetime] NULL ,[Abroad] [bit] NULL ,[InconvAllowance] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[ApprovalID] [int] NULL ,[AprovalDate] [smalldatetime] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[Work] ([WorkID] [int] IDENTITY (1, 1) NOT NULL ,[WorkTypeID] [int] NULL ,[WorkCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[Summary] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[Notes] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[Chargeable] [bit] NOT NULL ,[Complete] [bit] NOT NULL ,[ClientID] [int] NULL ,[ClientContactID] [int] NULL ,[Entered] [smalldatetime] NULL ,[ApprovalRequired] [tinyint] NULL ,[ColorCode] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[WorkOTRate] ([WorkOTRateID] [int] IDENTITY (1, 1) NOT NULL ,[WorkID] [int] NOT NULL ,[WorkDay] [int] NOT NULL ,[TimeFrom] [datetime] NOT NULL ,[TimeTo] [datetime] NOT NULL ,[RateMultiplier] [float] NOT NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[WorkOTRateDefaults] ([PKID] [int] IDENTITY (1, 1) NOT NULL ,[WorkDay] [int] NOT NULL ,[TimeFrom] [datetime] NULL ,[TimeTo] [datetime] NULL ,[RateMultiplier] [float] NOT NULL) ON [PRIMARY]GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGO/*Function to determine the actual cost, in minutes, of a particularsegment of work. This is what it does, or is supposed to do.1. From the PARAMETER WorkID, determine the conclusion of the workblock associated with the TimesheetID - i.e. StartTime + DurationMins2. Establish whether there are records in the WorkOTRate tablecorresponding to this particular WorkID, weekday and time period3. If there are, get the amount of minutes by which the work blockcoincides.4. If there are no such records, get the default values from theWorkOTRateDefaults table5. If the block doesn't cross any boundaries then it's just regularwork, so just count the minutes.25/08/2005 EC*/CREATE FUNCTION fnGetWorkCostPerTimesheetItem(@TimesheetID int)RETURNS floatASBEGINDECLARE@OTRateTimeFrom datetime,@OTRateTimeTo as datetime,@OTRateMultiplier as float,@EndTime datetime,@ReturnValue as float,@OrderRatePerHour as money,@EmployeeRatePerHour as smallmoney,@NumRecords as int,@WorkID as int,@EmployeeID as int,@StartTime as smalldatetime,@Duration as int,@Found as int,@Chargeable as bit-- Get the various bits and bobs needed for the calculationSET @ReturnValue = 0SET @Found = 0SELECT@WorkID = WorkID,@EmployeeID = EmployeeID,@StartTime = Start,@Duration = DurationMinsFROMTimesheetItemWHEREItemID = @TimesheetID-- If this work is NOT chargeable, return 0SELECT@Chargeable = ChargeableFROM[Work]WHEREWorkID = @WorkIDIF @Chargeable = 1BEGINSET @EndTime = DATEADD(mi, @Duration, @StartTime)-- Get the rate per hour for this workSELECT@OrderRatePerHour = RatePerHourFROMPurchaseOrderWHEREWorkID = @WorkID-- Get the rate per hour for the employeeSELECT@EmployeeRatePerHour = DefaultRatePerHourFROMEmployeeWHERE(EmployeeID = @EmployeeID)-- Find out if there's an OT Rate set up for this WorkIDSELECT@NumRecords = Count(*)FROMWorkOTRateWHERE((WorkID = @WorkID) AND(WorkDay = DATEPART(dd, @StartTime)))IF @NumRecords > 0BEGINDECLARE OTRate CURSOR FORSELECTTimeFrom,TimeTo,RateMultiplierFROMWorkOTRateWHERE((WorkID = @WorkID) AND(WorkDay = DATEPART(dw, @StartTime)))OPEN OTRateFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierWHILE (@@fetch_status=0)BEGIN-- Set the two time values so that they match the date underconsideration.SET @OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @OTRateTimeFrom,@StartTime) ,@OTRateTimeFrom)SET @OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @OTRateTimeTo ,@StartTime) ,@OTRateTimeTo)-- If the TimeTo part is < TimeFrom, then we know it crosses atime boundaryIF @OTRateTimeTo < @OTRateTimeFromSET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)-- If the time is between midnight and 8 a.m. it's the "next"dayIF CONVERT(datetime, @OTRateTimeFrom, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeFrom = DATEADD(dd, 1, @OTRateTimeFrom)IF CONVERT(datetime, @OTRateTimeTo, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)/*Ok, now we're in business. There are four possible scenariosthat we are interested in (ignoring when the Timesheet item period isentirely outside the OT rate period)NUMBER 1S EOT OTNUBMER 2S EOT OTNUMBER 3S EOT OTNUBMER 4S EOT OT*/-- NUMBER 1IF (@StartTime < @OTRateTimeFrom) AND (@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 2ELSE IF (@StartTime < @OTRateTimeFrom) AND (@EndTime BETWEEN@OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @EndTime)) * @OTRateMultiplier))SET @Found = 1END-- NUMBER 3IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo) AND(@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 4ELSE IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)AND (@EndTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@EndTime)) * @OTRateMultiplier))SET @Found = 1ENDFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierENDEND--CLOSE OTRate--DEALLOCATE OTRateELSE-- @NumRecords <= 0BEGINDECLARE OTRate CURSOR FORSELECTTimeFrom,TimeTo,RateMultiplierFROMWorkOTRateDefaultsWHERE(WorkDay = DATEPART(dw, @StartTime))OPEN OTRateFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierWHILE (@@fetch_status=0)BEGIN-- Set the two time values so that they match the date underconsideration.SET @OTRateTimeFrom = DATEADD(dd, DATEDIFF(dd, @OTRateTimeFrom,@StartTime) ,@OTRateTimeFrom)SET @OTRateTimeTo = DATEADD(dd, DATEDIFF(dd, @OTRateTimeTo ,@StartTime) ,@OTRateTimeTo)-- If the TimeTo part is < TimeFrom, then we know it crosses atime boundaryIF @OTRateTimeTo < @OTRateTimeFromSET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)-- If the time is between midnight and 8 a.m. it's the "next"dayIF CONVERT(datetime, @OTRateTimeFrom, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeFrom = DATEADD(dd, 1, @OTRateTimeFrom)IF CONVERT(datetime, @OTRateTimeTo, 108) BETWEEN '00:00' AND'08:00'SET @OTRateTimeTo = DATEADD(dd, 1, @OTRateTimeTo)/*Ok, now we're in business. There are four possible scenariosthat we are interested in (ignoring when the Timesheet item period isentirely outside the OT rate period)NUMBER 1S EOT OTNUBMER 2S EOT OTNUMBER 3S EOT OTNUBMER 4S EOT OT*/-- NUMBER 1IF (@StartTime < @OTRateTimeFrom) AND (@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 2ELSE IF (@StartTime < @OTRateTimeFrom) AND (@EndTime BETWEEN@OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi,@OTRateTimeFrom, @EndTime)) * @OTRateMultiplier))SET @Found = 1END-- NUMBER 3IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo) AND(@EndTime > @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@OTRateTimeTo)) * @OTRateMultiplier))SET @Found = 1END--NUMBER 4ELSE IF (@StartTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)AND (@EndTime BETWEEN @OTRateTimeFrom AND @OTRateTimeTo)BEGINSET @ReturnValue = @ReturnValue + (((DATEDIFF(mi, @StartTime,@EndTime)) * @OTRateMultiplier))SET @Found = 1ENDFETCH NEXT FROM OTRate INTO @OTRateTimeFrom, @OTRateTimeTo,@OTRateMultiplierENDENDCLOSE OTRateDEALLOCATE OTRate-- If there were no matching OT records, it's just a regular blockof work in normal hoursIF @Found = 0SET @ReturnValue = @DurationEND-- Finally we factor in the relation between the Employee's rate andthe Order's stated rate.RETURN (@ReturnValue * (@EmployeeRatePerHour / @OrderRatePerHour))ENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO

View 3 Replies View Related

Need Help Understanding A Call To A Sql Function

Feb 25, 2008

Can someone help me to understand a stored procedure I am learning about? At line 12 below, the code is calling a function named"ttg_sfGroupsByPartyId" I ran the function manually and it returns several rows/records from the query. So I am wondering? does a call to the function return a temporary table? And if so, is the temporary table named PartyId? If so, the logic seems strange to me because earlier they are using the name PartyId as a variable name that is passed in.
 
1  ALTER              PROCEDURE [dbo].[GetPortalSettings]2  (3     @PartyId    uniqueidentifier,45  AS6  SET NOCOUNT ON7  CREATE TABLE #Groups8  (PartyId uniqueidentifier)910 /* Cache list of groups user belongs in */11 INSERT INTO #Groups (PartyId)12 SELECT PartyId FROM ttg_sfGroupsByPartyId(@PartyId)

View 4 Replies View Related

Call Custom SQL Function In ASP.NET

May 9, 2006

I made an SQL function in MSSQL2000. This is a function that get's a calculated heat emission. When I run the Query in MSSQL2000 the function works. It calculates every emission for every row. When I call this SQL function in VS2005, it says it does not recognize the function. Does anyone know what this may cause? thank you. For the people who are bored, I added the SQL statement. The error is at the function

SELECT TOP 15 tbProducts.prod_code, tbProductProperties.prop_height, tbProductProperties.prop_length, tbProductProperties.prop_type, tbProductProperties.prop_default_emission, tbProductProperties.prop_weight, tbProductProperties.prop_water_volume, tbProductProperties.prop_n_value, GetHeatEmission(50,70,20,[prop_default_emission],[prop_n_value]) AS customEmission FROM tbProductClassification INNER JOIN tbProducts ON tbProductClassification.clprod_fk_prod_id = tbProducts.prod_id INNER JOIN tbProductProperties ON tbProducts.prod_id = tbProductProperties.prop_fk_prod_id WHERE (tbProductClassification.clprod_fk_class_id = 3327) AND (prop_height >= '030') AND (prop_height = '060') AND (prop_length

View 1 Replies View Related

SQL Server Call C++ Function?

Dec 11, 2001

Hi,

Does anyone know if/how SQL server can call a function in a C++ library?

Cheers,

Xiaobing

View 4 Replies View Related

Overload Function Call

Aug 1, 2006

Hi,

I want to write one function like that

dbo.function( number , 1 , 2 ) ,

but I would like to overload, and send char or number

dbo.function( number, 'abx' , ' xpto' ).

I would like to keep the same name, Can I do this? or Do I need to write to differents functions



thanks,

View 11 Replies View Related

Call Function For Inner Join

Aug 10, 2007



I have a procedure which has query
like Query 1.

Query 1

Select Clinetid
from clinet
inner join {


select centerid from GetChildCenter(@Centerid)
union
select centerid from getParentCenter(@Centerid)
} as Center c

on c.Centerid = client.Centerid


Query 2

declare @Center table ( centerid int)
insert into @Center

select centerid from getchildCenter(@Centerid) union all select centerid from getparentcenter(@Centerid)

Select Clinetid
from clinet
inner join @Center c on c.Centerid = client.Centerid





I just want to know which one is better performance wise..
because there is millions of rows for table center which is used by function getChildCenter() and GetparentCenter()

View 1 Replies View Related

Call Function From Stored Procedure

Jan 19, 2007

Hi All,
I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible.  So I have two questions:
1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures.  When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno.  I want to put that current batchno into the Update statement and update the record with the current batchno.  Can someone point me in the right direction?  Remember that I'm still a beginner on this subject.
2) Can someone provide any links to online tutorials on t-sql?
Thanks in advance.
Curtis

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved