What Is SqlInjection And How To Resolve/stop It?
Feb 14, 2007Hi all,
I found some articles online regarding sql injection, but not clear. Can someone give me few examples how to avoid it.
Thanks
Sham
ADVERTISEMENT
SQLInjection With OpenXML
Mar 9, 2006I am researching the use of OpenXml for doing mass updates/inserts.Does anyone know how this procedure works as far as sql injection isconcerned? I've always been taught to use sp's with parameters...doesusing OpenXML open up any holes in that idea?My thinking is that it would be fine (maybe even better), because thefields will still be treated as literals.Alternatively, are there any other suggestions for doing massiveamounts of updates/inserts?
View 1 Replies View RelatedHow To Resolve This Problem
Feb 7, 2008hi i'm new to .net and i have one problem in my application that is :i have application table with appId, appname columns, i bind the appname to gridview and assigned appid value to selected value of gridview.on gridview one column have checkboxes and one is itemtemplate lable which specifies the appname. if user click on the checkbox then it sholud specify appId of selected applicationname. if user clicks on list of application then how to read those list of application id's and how to put them in table.and how to insert the values in database like:RoleId AppId1 A0011 A0031 A0041 A0051 A0081 A0101 A011in that format, ????
View 1 Replies View RelatedResolve Locking
Dec 11, 2004i've a SQL2000 server and SQLserver CE installed, also i've about 100 reps having a palm with them, entering a data through a developed software, then make a daily connection to make synchronization and send their data to the server.
most of thes reps make the synchronization at the same time each day, so most of them get the error message of Deadlock on a resource of process ID...
how can i resolve this issue and what is the most appropriate type of locking i can use to prevent this issue?
thanks in advance
How To Resolve Deadlocks?
Aug 1, 2007Hi,
We are facing Deadlock issues, when we try to execute a procedure for
different parameters simultaneously,
Example:
proc_name '20070630','755','Y','html'
proc_name '20070630','681','Y','html'
------
When we are executing the above procedure simultaneously..we are getting
the Deadlock error..
We included 'With(Rowlock)'.But still we are getting deadlocks.
Here is the trace file for your reference.. We could not find the KEY
parameter in this Trace file and we are not able to find the object name
which is creating the Deadlock.
------
2007-07-16 13:59:49.70 logon Login failed for user 'JUnitCustomer'.
2007-07-16 14:00:11.01 logon Login failed for user 'JUnitCustomer'.
2007-07-16 14:00:11.06 logon Login failed for user 'JUnitCustomer'.
2007-07-16 14:00:11.17 logon Login failed for user 'JUnitCustomer'.
2007-07-16 14:00:11.21 logon Login failed for user 'JUnitCustomer'.
2007-07-16 14:00:39.90 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:78 ECID:0 Ec0x6B133538) Value:0x6a
2007-07-16 14:00:39.90 spid4 Victim Resource Owner:
2007-07-16 14:00:39.90 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:78 ECID:0 Ec0x6B133538) Value:0x6a
2007-07-16 14:00:39.90 spid4 Requested By:
2007-07-16 14:00:39.90 spid4 Grant List 2::
2007-07-16 14:00:39.90 spid4 Input Buf: Language Event:
proc_create_fsa_export '20070630','755','Y','html'
2007-07-16 14:00:39.90 spid4 SPID: 74 ECID: 0 Statement Type: UPDATE
Line #: 252
2007-07-16 14:00:39.90 spid4 Owner:0x219ed520 Mode: S Flg:0x0 Ref:0
Life:00000001 SPID:74 ECID:0
2007-07-16 14:00:39.90 spid4 Grant List 0::
2007-07-16 14:00:39.90 spid4 PAG: 10:1:3325 CleanCnt:2 Mode: SIU Flags:
0x2
2007-07-16 14:00:39.90 spid4 Node:2
2007-07-16 14:00:39.90 spid4
2007-07-16 14:00:39.90 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:74 ECID:0 Ec0x7BB75538) Value:0x54
2007-07-16 14:00:39.90 spid4 Requested By:
2007-07-16 14:00:39.90 spid4 Input Buf: Language Event:
proc_create_fsa_export '20070630','284','Y','html'
2007-07-16 14:00:39.90 spid4 SPID: 78 ECID: 0 Statement Type: UPDATE
Line #: 224
2007-07-16 14:00:39.90 spid4 Owner:0x1c109a00 Mode: S Flg:0x0 Ref:0
Life:00000001 SPID:78 ECID:0
2007-07-16 14:00:39.90 spid4 Grant List 2::
2007-07-16 14:00:39.90 spid4 Grant List 0::
2007-07-16 14:00:39.90 spid4 PAG: 10:1:45083 CleanCnt:2 Mode: SIU Flags:
0x2
2007-07-16 14:00:39.90 spid4 Node:1
2007-07-16 14:00:39.90 spid4
2007-07-16 14:00:39.90 spid4 Wait-for graph
2007-07-16 14:00:39.90 spid4
2007-07-16 14:00:39.90 spid4 ...
2007-07-16 14:00:41.29 logon Login failed for user 'JUnitCustomer'.
2007-07-16 14:00:41.32 logon Login failed for user 'JUnitCustomer'.
----------
Can any one of you please help to solve this issue.
Resolve Conflict
Apr 10, 2006Hi,
l've a raw data which contains list of device name and # of wires used, i.e.
DEVICE NAME # of Wires
--------------------- -------------
A 10
B 11
C 17
D 5
A 0
E 0
So l would like to import these data into my table (with device name as primary key) which each device only appear once. If the device name appear twices, then l should ignore device with # of wires = 0. If device name appear twice and # of wires <> 0, then l should log it.
How should l do that in SSIS ?
Cannot Resolve The Collation Conflict
May 31, 2008 hi allin my local machine there was no problem. however, when i put my database on the the hosting company's database server i got this error System.Data.SqlClient.SqlException:
Cannot resolve the collation conflict between
"SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal
to operationplease help it's urgent thanks
Cannot Sort A Row Of Size How To Resolve
Nov 30, 2004I have a SQL Stored Procedure which select data from some tables. I worked on some rows but when I inserted one more row which had a large data- new row has inserted but I cannot select and order its record and SQLsrv raised this error
"Cannot sort a row of size 8154, which is greater than the allowable maximum of 8094"
any one, plz help me to Fix this.
Thanks alot
How To Resolve DBCC Errors?
Sep 1, 2002Hi there:
My database has DBCC errors and I am trying to find out the ways to solve it. Any help from you will be highly apprecitated. The error says:
Table Corrupt: Object ID 909325622, index ID 6, page ID (1:864966). The PageId in the page header = (12336:1599554370).Server: Msg 2535, Level 16, State 1, Line 1
Table Corrupt: Page (1:864987) is allocated to object ID 61243273, index ID 3, not to object ID 909390389, index ID 19712 found in page header.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed.
Thanking you,
Eva
How To Resolve This Problem(rsReadOnlyReportParameter)?
Oct 23, 2006I used private assembly that I defined by myself,it can works in the report designer,when I deploy it to my report server,It can run successfully in the server,but I defined a hyperlink for ReportId,when I click the hyperlink on the preview screen,It will prompt a error message(The report parameter 'ReportID' is read-only and cannot be modified. (rsReadOnlyReportParameter) Get Online Help),so somebody help me,please.how to fix this one?
thank you!
Cannot Resolve A Package Path
Aug 15, 2007I have a parent package - Main.dtsx that calls a number of child packages. I also use configuration packages as I must deploy the application across multiple environments.
Main.dtsx contains a single Ccnfiguration file - Child_Packages.dtsConfig. This contains the connection strings for all the child packages.
Each child package contains a single configuration file - Main.dtsConfig that contains a superset of OLE DB connections. That is no single child package uses all of the connections contained in main.dtsConfig.
Each child package will execute independently without error. However, when called from Main.dtsx, I get the following errors:
Error: 2007-08-15 15:44:22.93
Code: 0xC001000E
Source: UserSecurity
Description: The connection "CRM" is not found. This error is thrown by Connections collection when the specific connection element is not found.
End Error
Warning: 2007-08-15 15:44:22.93
Code: 0x8001F02F
Source: UserSecurity
Description: Cannot resolve a package path to an object in the package ".Connections[CRM].Properties[ConnectionString]". Verify that the package path is valid.
End Warning
Warning: 2007-08-15 15:44:22.94
Code: 0x80012017
Source: UserSecurity
Description: The package path referenced an object that cannot be found: "Package.Connections[CRM].Properties[ConnectionString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.
End Warning
I have seen discussions that suggest that these errors occur where the connection in the config file is not found in the child package. Is there any way of suppressing this ?
Are there any simpler methods that allow me to change connections in each environment?
I suspect I will end up having to tailor config files for each child package, but I live in hope.
Michael M.
How Can I Resolve This Database Error, In VS 2005?
Oct 23, 2007Every 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"
Error Cannot Resolve The Collation Conflict Between
May 21, 2007I'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
How To Resolve Duplicate Data Problem
Apr 10, 2007Hi All
i want the output like this
date poid sales ref unit cost ordered received sold shrinkage sale type postage delivery payment type
23/3/2007 12345 test - - 1 - tel 20 shipping credit card
for that i have written two sql queries
qry1 =
///
"SELECT im_products_stock_logs.orderid,im_products_stock_logs.log_type,
im_products_stock_logs.log_date, im_products_stock_logs.poid,
products.lead_time,products.cost_price,orders.sales_type,
isnull(orders.totalamt,0) as totalamt, isnull(orders.shippingamt,0)
as shippingamt, orders.delivery_method, orders.payment_method
FROM im_products_stock_logs LEFT OUTER JOIN orders ON
im_products_stock_logs.orderid = orders.orderid LEFT OUTER JOIN Products on
im_products_stock_logs.productid= products.productid WHERE
(im_products_stock_logs.productid = 790) and poid=14 order by log_date desc "
///
qry2=
///
SELECT im_products_stock_logs.log_type, SUM(im_products_stock_logs.qty)
AS qty, im_products_stock_logs.poid, DAY(im_products_stock_logs.log_date)
AS Expr2, YEAR(im_products_stock_logs.log_date) AS Expr3,
MONTH(im_products_stock_logs.log_date) AS Expr4,
{ fn MINUTE(im_products_stock_logs.log_date) } AS Expr5,
{ fn HOUR(im_products_stock_logs.log_date) }
AS Expr6 FROM im_products_stock_logs LEFT OUTER JOIN orders ON
im_products_stock_logs.orderid = orders.orderid LEFT OUTER JOIN
products ON im_products_stock_logs.productid = products.productid WHERE
( im_products_stock_logs.productid = 790 and im_products_stock_logs.colorid = 2 )
GROUP BY im_products_stock_logs.log_type, im_products_stock_logs.poid,
DAY(im_products_stock_logs.log_date), YEAR(im_products_stock_logs.log_date),
MONTH(im_products_stock_logs.log_date), { fn HOUR(im_products_stock_logs.log_date)
}, { fn MINUTE(im_products_stock_logs.log_date) }
ORDER BY YEAR(im_products_stock_logs.log_date) DESC,
MONTH(im_products_stock_logs.log_date) DESC,
DAY(im_products_stock_logs.log_date) DESC,
{ fn HOUR(im_products_stock_logs.log_date) }
DESC, { fn MINUTE(im_products_stock_logs.log_date) } DESC
///
the table use in are
im_products_stock_logs-orderid,log_type,log_date,poid,
products-lead_time,cost_price
orders-sales_type,delivery_method,payment_method
the sample data is
orders
orderid sales_type delivery_method payment_method
1025 tel v shipping 1
products
productid lead_time cost_price
13 4 45.00
im_products_stock_logs
logid productid orderid log_type log_date poid
40 13 1025 sold 23/3/2007 8
I have written the query for the same is
in my query i m getting the duplicate values .
How can I solve it please help me.
thanks
How To Resolve The Personalization Problem In Remote Server
Jan 30, 2008Hello friends! Actually we are using personalization concept in our application. We got the below error while running our application from remote server. We have uses ASP.Net 2.0 framework and MySQL server 2005. If anybody knows solution about this, please give us reply. The error is 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.
Exception Details: System.Data.SqlClient.SqlException:
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)
Source Error:
Line 58: }Line 59: set {Line 60: this.SetPropertyValue("firstname", value);Line 61: }Line 62: }
Source File: c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Filesproflick1590573e66ec62App_Code.nudj4ngy.13.cs Line: 60
Stack Trace:
[SqlException (0x80131904): 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)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735203 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197 System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) +766 System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) +428 System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) +410 System.Configuration.SettingsBase.SetPropertyValueByName(String propertyName, Object propertyValue) +170 System.Configuration.SettingsBase.set_Item(String propertyName, Object value) +89 System.Web.Profile.ProfileBase.SetInternal(String propertyName, Object value) +139 System.Web.Profile.ProfileBase.set_Item(String propertyName, Object value) +71 System.Web.Profile.ProfileGroupBase.SetPropertyValue(String propertyName, Object propertyValue) +27 ProfileGroupUserRegPersonalDetails.set_firstname(String value) in c:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Filesproflick1590573e66ec62App_Code.nudj4ngy.13.cs:60 UserRegPersInf.RegisterPersInf_Click(Object sender, EventArgs e) in e:hostingannasgroupProflickUserRegPersInf.aspx.cs:25 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Thanks in advance.By,Pavani
Could Not Resolve Service Name - Oracle 8 And MS OLEDB Provider
Jan 23, 2001Trying to used DTS from SQL Server 7 to import a table from Oracle 8 DB to SQL Server. SQL Server has Oracle 8 connectivity installed and a database instance setup. Connection using the instance works from Oracle Enterprise manager and all Oracle tools. The MS OLEDB provider for Oracle fails to connect : could not resolve service name. I have this working on a different server with the same setup (SQL Server 7 SP 2 + All Oracle 8.0.5 connectivity).
WHAT AM I MISSING?
Cannot Resolve Collation Conflict For Equal Operation
Aug 10, 2004Hi, I´m having a critical problem.
I have two databases: persons and cars
In database persons I have a table
named persons_class1 as follows:
person_id char(13) not null [primary key]
name varchar(20) not null
In database cars I have a table
named cars_blue as follows:
car_id char(13) not null [primary key]
model varchar(20) not null
person_id char(13) not null
In my program the initial catalog is: cars
and I´m executing the following SQL:
select cars_blue.model,persons.dbo.persons_class1.name
from cars_blue,persons.dbo.persons_class1
where cars_blue.person_id=persons.dbo.persons_class1.person_id
and the following error occurs:
Cannot resolve collation conflict for equal operation
I also tried like instead of = and the error is:
Cannot resolve collation conflict for like operation
what can I do?
Thanks in advance
Roland
Cannot Resolve Collation Conflict For Equal To Operation.
Jul 1, 2004Anyone bang into that?
RESTORED a 7.0 Datbase CP 52 to a 2k box
Built a "Privacy" db from scratch on 2k box
Looked at some table scripts and got
OHM - sql 7
SQL_Latin1_General_CP1_CI_AS NULL
Privacy - sql 2k
Latin1_General_CI_AS NULL
[
Ran this and got the error...any help?
select * from privacy..privacy_column p inner join ohm.information_schema.tables t ON p.table_name = t.table_name
Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.
Any ideas?
Cannot Resolve Collation Conflict For Equal Operation
Aug 10, 2004Hi, I´m having a critical problem.
I have two databases: persons and cars
In database persons I have a table
named persons_class1 as follows:
person_id char(13) not null [primary key]
name varchar(20) not null
In database cars I have a table
named cars_blue as follows:
car_id char(13) not null [primary key]
model varchar(20) not null
person_id char(13) not null
In my program the initial catalog is: cars
and I´m executing the following SQL:
select cars_blue.model,persons.dbo.persons_class1.name
from cars_blue,persons.dbo.persons_class1
where cars_blue.person_id=persons.dbo.persons_class1.per son_id
and the following error occurs:
Cannot resolve collation conflict for equal operation
I also tried like instead of = and the error is:
Cannot resolve collation conflict for like operation
what can I do?
Thanks in advance
Roland
Cannot Resolve Collation Conflict For Equal To Operation.
Oct 19, 2004Hi, I have this error when running a query:
Cannot resolve collation conflict for equal to operation.
but the situation is that I can run the query perfectly with one user (windows user) but using an standar user, I've got the error, as far as I know the collation feature applies to db's an objects not to users, what can I do to run this query with the standar user? both users have the same permission on the db. Below is the query attached.
Thank you
Gabriel
SELECT B.EmpSupervisorId, a.Info_ID as A_Info_ID,
ISNULL(a.Owner_SSO_ID,'') as A_Owner_SSO_ID,
ISNULL(a.Ref_SSO_ID,'') as A_Ref_SSO_ID,
ISNULL(a.Prev_Owner_SSO_ID,0) as A_Prev_Owner_SSO_ID,
ISNULL(a.MgmtTypeFlag,0) as A_MgmtTypeFlag,
ISNULL(a.CurrentStatusID, 1) as A_CurrentStatusID,
ISNULL(a.PrevStatusID,0) as A_PrevStatusID,
ISNULL(C.ConTypeOption, '') as C_ConTypeOption,
ISNULL(CAST(a.Last_Status_Update_Date AS VARCHAR),'') as A_Last_Status_Update_Date,
ISNULL(B.sfBUID,'0') as A_Bus_Group_ID, 0 AS A_Bus_Group_Seg_ID,
ISNULL(a.Organization,'') as A_Organization,
ISNULL(a.ContactName,'') as A_ContactName,
ISNULL(A.Title, '') as A_ContactTitle,
ISNULL(a.ContactComName,'') as A_ContactComName, ISNULL(a.Phone,'') as A_Phone,
ISNULL(a.NatureOfOppID,'') as A_NatureOfOppID, ISNULL(a.DealAmount,0) as A_DealAmount, ISNULL(CAST(a.Cust_Contacted_Date AS VARCHAR),'') A_Cust_Contacted_Date, ISNULL(CAST(a.Lead_Qualified_Date AS VARCHAR),'') A_Lead_Qualified_Date, ISNULL(CAST(a.Tran_Processed_Date AS VARCHAR),'') A_Tran_Processed_Date,
ISNULL(CAST(a.Quote_Accepted_Date AS VARCHAR),'') as A_Quote_Accepted_Date,
ISNULL(CAST(a.Approved_By_HFS_Date AS VARCHAR),'') as A_Approved_By_HFS_Date,
ISNULL(CAST(a.Funded_By_HFS_Date AS VARCHAR),'') as A_Funded_By_HFS_Date,
ISNULL(a.Lead_In_Amount,0) as A_Lead_In_Amount,
ISNULL(a.Cust_Contacted_Amount,0) as A_Cust_Contacted_Amount,
ISNULL(a.Lead_Qualified_Amount,0) as A_Lead_Qualified_Amount,
ISNULL(a.Tran_Processed_Amount,0) as A_Tran_Processed_Amount,
ISNULL(a.Quote_Accepted_Amount,0) as A_Quote_Accepted_Amount,
ISNULL(a.Approved_By_HFS_Amount,0) as A_Approved_By_HFS_Amount,
ISNULL(a.Funded_By_HFS_Amount,0) as A_Funded_By_HFS_Amount,
ISNULL(CAST(CreationDate AS VARCHAR),'') as A_CreationDate,
ISNULL(a.BusType,'') as A_BusType,
ISNULL(a.NonHFS_XLink_ContactName,'') as A_NonHFS_XLink_ContactName,
ISNULL(a.NonHFS_XLink_Bus_ID,'0') as A_NonHFS_XLink_Bus_ID,
ISNULL(a.Comments,'') as A_Comments, ISNULL(a.ExistCustomerID,'') as A_ExistCustomerID,
ISNULL(a.FinancialNeedID,'') as A_FinancialNeedID,
ISNULL(a.CampaignID,'') as A_CampaignID,
(GEC_HFS_CORE.dbo.GetBusinessDays(Last_Status_Upda te_Date, getdate())-1) as BusDateDiff, ISNULL(A.DealTypeID, '') as A_DealTypeID
FROM tblInformation a
LEFT OUTER JOIN GEC_HFS_LM_SSOInfo B ON A.Owner_SSO_ID = B.sfSSOID
LEFT OUTER JOIN tblContactType C ON a.Bustype = C.ConType_ID
WHERE a.CurrentStatusID NOT IN (9, 10, 11) AND
ISNULL(a.isNoChange,'NO') = 'NO' AND ISNULL(a.IsDeleted,'NO') = 'NO' AND
DATEDIFF(DAY, Last_Status_Update_Date, GETDATE())<> 0 AND
ISNULL(a.isNonHFS, 'NO') = 'NO'
ORDER BY A_Info_ID
Error In Procedure - Cannot Resolve Collation Conflict
Dec 19, 2013while 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.
Unable To Resolve Column Level Collations
May 14, 2008Hi,
When I try to execute a package I get this following error. I have a bunch of similar packages which runs fine on the same source(sybase) and destination(sqlserver 2005), just different tables. Only few of them fails and all of them have the same error of "Unable to resolve column level collations. Bulk-copy cannot continue". I checked for the dtatatype and length between source and destination, both are same. The user have all the required rights on the objects in both source and destination.
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to resolve column level collations. Bulk-copy cannot continue.".
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: Failed to open a fastload rowset for "testdb..tbl2". Check that the object exists in the database.
On further trial and error I found that if I remove the fast load option, it works without glitch.
Its kind of wierd though....
Any body has faced this situation?
Thanks
Karunakaran
Cannot Resolve Collation Conflict For Equal To Operation.
Dec 16, 2005I've stumbled across the above error and am a little stuck.I've just installed SQL2000 (sp3) on my PC and restored a database fromour Live server. On a simple Update statement on a #temp table, itfails with the above message. I think I understand what it means andfound some old posts suggesting using the following :select name, databasepropertyex(name, 'collation')from master..sysdatabasesselect serverproperty('collation')All of the databases that are there by default are set to'Latin1_General_CI_AS' and the restored db is'SQL_Latin1_General_CP1_CI_AS'.The live server has all of these set to the 'SQL...' version, but astandard install points to the other. So, how do I change mine to the'newer' setting ? All I need to do is mimic the live environment fortesting and development. There is only me using it, and it's not aproblem to bin it and re-install, or tweak if I need to.I've tried using :ALTER DATABASE Northwind COLLATE SQL_Latin1_General_CP1_CI_ASas a test (thought this was the best example to show), but it failsstating the following :Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_Products_UnitPrice' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_ReorderLevel' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_UnitsInStock' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_UnitsOnOrder' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_Discount' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_Quantity' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_UnitPrice' is dependent on database collation.Server: Msg 5075, Level 16, State 1, Line 5The object 'CK_Birthdate' is dependent on database collation.Server: Msg 5072, Level 16, State 1, Line 5ALTER DATABASE failed. The default collation of database 'Northwind'cannot be set to SQL_Latin1_General_CP1_CI_AS.On the Live server, the Northwind database is set to the 'SQL...'version, so it MUST be do-able somehow.Any pointers would be appreciated.Thanks in advanceRyan
View 7 Replies View RelatedHow To Fix An Error:Cannot Resolve The Collation Conflict Between Latin1_General_CI_AI And...?
Dec 12, 2007Hello,
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.
Could I Resolve A KPI's Data Value In A SSIS Script Task?
Dec 11, 2006
Hi, thanks.
I could rosolve a KPI's Data Value by ADOMD.net from any .net application. Now I want to do the same thing from the SSIS Script Task. Could I do that?
SSIS Script Task use a VBA Script. I could use ADO.net in it, by imports the XML.dll.
Thanks.
Runnaway UPDATE Statement In SQL2005 - How To Resolve?
Jun 16, 2006I am in the process of moving a SQL2000 database to a SQL2005 database.
Porting from: SQL200, Windows Server 2000(SP4) (32 bit dual processor 4GB RAM)
to:SQL2005, Windows Server 2003(SP1) (x64 bit dual processor 4GB RAM)
After porting the database from SQL2000 to SQL2005 (no changes)
I then compare an update statement running from Management Studio on the 2003 Server and and Query analiser on the 2000
Server.
SQL2000 completes the command in 2 minutes SQL2005 is still running after 60 minutes.
SQL2000 is the live/production system with users connected, the SQL2005 is in a test environment with no other processors
running.
The SQL2005 activity monitor shows:
-----------------------------------
3 suspended processes in CXPACKET wait state and
2 runnable process high CPU counts (SQLServer running at 100% cpu).
All processes with the same Process ID.
Wait time is 0
High CPU counts for the runnable processes.
Low physical IO
No lock conflicts
When I add the "option (maxdop 1)" to the update statment then:
The activity monitor shows:
---------------------------
1 runnable process with a high CPU count (SQLServer running at 50% cpu).
Wait time is 0
High CPU count for the runnable processe.
Low physical IO
No lock conflicts
How do I debug this situation?
Why is the SQL2005 unable to complete the task?
The update statement is as follows...
-------------------------------------
update BI_LENDING_TRANSACTIONS
set [Balance Movement Month] = M.[Balance Movement Month]
from BI_LENDING_TRANSACTIONS as T,
BI_BALANCE_MOVEMENT_DATES as M,
BI_COMPANIES as C
where (T.[Transaction Date] >=
(SELECT DATEADD(d, - 70, minDate) from (select min([Transaction Date]) minDate
from p_BI_LENDING_TRANSACTIONS) t1)
OR
T.[Transaction Date] >= C.[MostRecentSnapShotDate] or
T.[Value Date] = T.[Balance Movement Month] ) and
T.[Value Date] <= C.[MostRecentSnapShotDate] and
T.[Value Date] >= T.[Transaction Date] and
T.[Company_Code] = M.[Company_Code] and
T.[Value Date] > M.[SnapShotFromDate] and
T.[Value Date] <= M.[SnapShotToDate] and
C.[Company_Code] = M.[Company_Code]
Cannot Resolve Collation Conflict For Equal To Operation
Nov 27, 2007
Hi All,
I have collation defined on one column and default on another being used in where clause. How can I resolve this problem without changing collation in table.
Thanks in advance
Niraj
Error With Membersip And Roles Cannot Resolve The Collation Conflict Between
May 21, 2007I'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,
Error:Cannot Resolve Collation Conflict For Equal To Operation.
Sep 2, 2004Exception information:System.Data.SqlClient.SqlException: Cannot resolve collation conflict for equal to operation.
Who can tell me how to resolve this problem?
Thx
SQL 2012 :: How To Resolve IP Conflict Error While Server Clustering
Dec 8, 2014I 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?
Error In Store Procedure - Cannot Resolve Collation Conflict
Dec 19, 2013While 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.
SQL 2012 :: How To Resolve Data Inconsistent Errors In Merge Replication
Aug 12, 2015How to resolve the data inconsistent errors in merge replication ?
View 0 Replies View RelatedCannot Resolve The Collation Conflict Between SQL_Latin1_General_CP1_CI_AS And Latin1_General_CI_AS In The Equal To Operatio
May 20, 2008I hit this error when I tried to do something like:
Code Snippet
create table #tempTable (ID int IDENTITY(1,1) NOT NULL ,
column1 varchar(200) NOT NULL)
INSERT INTO #tempTable
Values('test')
create table tempTable (ID int IDENTITY(1,1) NOT NULL ,
column2 varchar(200) NOT NULL)
INSERT INTO tempTable
Values('test')
Select * from #tempTable t
inner join tempTable p
on t.ID = p.ID
where t.column1 = p.column2
After a thorough search on sysobjects and syscolumns, I found the collation problem on database "temp" is set to "Latin1_General_CI_AS"
since it's an system db, I cannot alter. My db also cannot alter because some SP is encrypted, though I cna de-crypt it.
Is that anyway to solve it by running the script??
I tried to do something like:
start /wait setup.exe /qb INSTANCENAME= {my MSSQL 2005 insatnce name} REINSTALL=SQL_Engine REBUILDDATABASE=SAPWD= {my sa password} SQLCOLLATION=SQL_Latin1_General_CP1_CI_AI
but i get no error at Summamry.txt:
Setup succeeded with the installation, inspect the log file completely for status on all the components.
while at "SQLSetup0011__Core.log":
Error: Action "LaunchPatchedBootstrapAction" threw an exception during execution. Error information reported during run:
"C:Program FilesMicrosoft SQL Server90Setup Bootstrapsetup.exe" finished and returned: 0
Aborting queue processing as nested installer has completed
Message pump returning: 0
Anyone have any idea?