Group By On The Text Colum Throws Error
Jul 20, 2005
Hi ,
I have this query
paprojnumber is varchar
patx500 is text
palineitemseq is int
select Paprojnumber,Patx500,max(palineitemseq) from pa02101,pa01601
where
pa02101.pabillnoteidx=pa01601.pabillnoteidx group by
paprojnumber,patx500
it throws this error
Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.
Thanks a lot for your help.
AJ
View 2 Replies
ADVERTISEMENT
Nov 20, 2015
I have concatenated the two columns in DB2, Concatenation query working fine. I am getting error if I use GROUP BY.
Query:
SELECT
SUM(A)as T
B||'-'|| C as T1
FROM TABLE
GROUP BY T1
Error message:
FROM INTO was not valid. Valid tokens:*N.SQLSTATE:42601,SQLCODE:-104
View 5 Replies
View Related
Jul 7, 2004
I have a large text colum I am trying to insert into a DB
This colum is about 800 chars longs
I have set the colum type in the table to text
I have set the table option for text in row to on
I have set the table option for text in row to 1000
But it is still chopping the text at the 256 char mark on insert.
Anyone have any ideas ?? This is SQL 2000.
Chris
View 4 Replies
View Related
Jul 18, 2006
Hi,
I have three script component A,B, C. A reads certain data from a table. B inserts records into a .txt file when the recordtype = 1. C inserts records into same .txt file when the recordtype = 0. Now I receive a script comp error for C as follows.
[Error Desc]
************************************************************
The process cannot access the file 'D:Documents and SettingsAdministratorDesktopAuditLog.txt' because it is being used by another process.
************************************************************
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at ScriptComponent_6bda9d13fce34f90ac6315546c8d0d54.ScriptMain.PreExecute() in dts://Scripts/ScriptComponent_6bda9d13fce34f90ac6315546c8d0d54/ScriptMain:line 19
at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PreExecute()
[/Error Desc]
Can anyone throw some light on it.
Thanks
View 3 Replies
View Related
Mar 18, 2008
Hello,
I have OLE DB Connection to Access database using Jet 4.0 provider.
I have a data flow task in package.
In this data flow task, I have an OLE DB source. This source uses above mentioned connection.
When this data flow task is not a part of transaction, it works fine. But when I set "TransactionOption" property to "Required", the OLE DB source returns following error.
[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "databasename" failed with error code 0xC001A004.
I have checked Distributed Transaction Cordinator running. I have some other package running fine which uses transactions but does have OLE DB source.
I have also tried setting "RetainSameConnection" property of a connection to TRUE. But still it is failing.
In short, I am not able to execute OLE DB source in side transaction.
Please guide.
Thanks,
View 3 Replies
View Related
Mar 4, 2008
I call the datareader using a sqlCommand that I programmatically build and it accesses the database fine. I can use the exact SQL command that I generate and I can get a result when i run it in SQL Management Studio. When I run the application and try to access the datareader, it throws an exception whose message is quite frustratingly, <column name>, or in this case "image_URL". Anyone have any idea what might be causing such an ambiguous error?
View 2 Replies
View Related
Apr 7, 2008
I try to update from datalist by using sqlcommand, it throws an error on the date field:
Mark up:
10/11/2008 12:00:00 AM <--- the format in the SQL database
Error:
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'DOB'.Dim strSQL As String = _
"UPDATE [Customers] SET [country] = @country, " & _"[nickName] = @nickname, [identityid] = @identityid " & _
"[DOB] = @DOB, [Hpno] = @Hpno " & _"[address] = @address, = @email " & _
"WHERE [CustomerID] = @CustomerID"
...Dim parameterdob As SqlParameter = _
New SqlParameter("@DOB", SqlDbType.DateTime)
parameterdob.Value = Request.Form("txtdob") & " 00:00:00"
myCommand.Parameters.Add(parameterdob)
myCommand.ExecuteNonQuery() <--- error here
...
<form id="form1" runat="server">
<asp:DataList ID="ProfileDataList" runat="server">
<ItemTemplate>
<asp:TextBox runat="server" ID="txtdob" Text='<%#DataBinder.Eval(Container.DataItem, "DOB", "{0:d}")%>' />
</ItemTemplate>
</asp:DataList>
View 5 Replies
View Related
Oct 24, 2007
Hi,
I have a small program to query a stored procedure in SQL Server 2000 and print out the contents of a returned date field. The program is as follows:
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbcqlserver://host:1433;database=db;user=u1;password=p1";
Connection con = DriverManager.getConnection(connectionUrl);
CallableStatement cbstmt = con.prepareCall(" {call stored_proc (?,?)}");
cbstmt.setString(1, "value1");
cbstmt.setString(2, "value2");
ResultSet resultSet = cbstmt.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getDate("end_date"));
}
The date field is nullable, and the records in the database are currently null. But when I run this program, I get the following exception when using version 1.2 of the MS JDBC Driver:
com.microsoft.sqlserver.jdbc.SQLServerException: The conversion from int to DATE is unsupported.
at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.DTV.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Column.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Column.getValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getDate(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.getDate(Unknown Source)
at jdbc.main(jdbc.java:38)
Exception in thread "main"
This started happening when we upgraded to the new driver. Any ideas why?
Thanks.
View 1 Replies
View Related
Mar 21, 2006
I have a table in sql server 2000 which has a column whose name starts with a number("2ndName").I have a c# code which updates the table by filling a Dataset.When I issue an update, it throws the following error:-"Incorrect Syntax near 2"Query I use to build the adapter is "SELECT id, Name, [2ndName] FROM MyTBL WHERE 1 = 3" and the statement issued to update is "objAdapter.Update(objDSDB, "MyTBL")" when it throws the error "Incorrect Syntax near 2".Any help to resolve this is appreciated.Thanks in advance.Jai
View 3 Replies
View Related
Feb 27, 2015
How to fetch records which throws error?
For example
I'm inserting records from a table to another table, having multiple columns
SampleQuery :
insert into tableB (col1,col2....col10)
select col1,col2....col10 from tableA
* while executing this query im getting error. (like varchar cant convert to numberic)
* Here I have no clue in which column it is
* also dont know which row causes it
View 3 Replies
View Related
Apr 13, 2007
Hi all,
we've been having this ancient database with old accounting data running in suspect mode since as long as I can remember (I started working here a year ago), and finally I had some time on my hands so I thought I'd try to get it online again. However I'm running in to problems:
DBCC CHECKDB (myDBName) gives this error:
Msg 926, Level 14, State 1, Line 1
Database 'myDBName' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Running sp_helpdb only does not display the suspect database and sp_helpdb 'myDBName' gives this error even though I'm a system administrator:
No permission to access database 'myDBName'.
It's possible that I might be able to dig up a backup but that would be quite tedious. Is it possible to bring the database to a state where I'm able to do a CHECKDB at least...?
--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
View 5 Replies
View Related
May 15, 2007
I am trying to execute a stored procedure on Server1 which creates an excel report on a share of Server2:
The following error message is thrown:
Saving of scheduled report(s) to Excel file failed : 203 SaveReportToExcel() in TlRptToFile.RptExcel failed in sproc_SaveReportAsFile -
TLRptXL::SaveReportToExcel - Connection to Database failed for Analytics DataBase
Server : TKTALSQL3, Application Server: and DataBase : tlAnalytics : Windows
Error - The system could not find the environment option that was entered.
A DCOM component on Server1 runs the stored procedure that creates the excel report. The account under which DCOM component runs is a member of 'Administrators' group on both Server1 and Server2. Checked permissions on the shares. Account is a local Admin on both Server1 and Server2. Account under which SQLServer and ServerAgent runs is also an Admin on these shares (implicitly as part of 'Administrators' group).
I am manually able to create an excel/text file on the Server2 share while accessing it from Server1, though.
Appreciate your help in resolving the issue.
View 4 Replies
View Related
May 15, 2008
Hi,
My server configuration is
Intel Xeon(R) X5355 @ 2.66GHz, 3.75 GB RAM
Win 2k3
I have been receiving the above mentioned error when I try to use the out of the box feature of "Export To Excel" provided in the Reporting Services, the reports are hosted on MOSS 2007 using the Reporting Services Add-In. The number of pages generated by the report is 500+, so I don't think exporting a report of around 500 - 1000 should give out a memory exception since we have sufficient RAM on the server.
Please advice a solution to solve the error.
Thanks in Advance.
Vikas Mestry.
View 4 Replies
View Related
Sep 21, 2007
Not sure if there's a GP 8.0 forum, so giving this one a go.
I've added an AFTER UPDATE trigger to the RM00101 table (customer master) in a Great Plains 8.0 SQL Server 2000 SP4 DB. The trigger assigns field values to variables, constructs an update query, and executes the query on a table in a linked SQL Server 2005 DB.
The trigger works fine when fired as a result of a direct field update made through Enterprise Manager. However, when the same update is made through the Great Plains GIU (customer card window), an exception error is thrown:
"Unhandled database exception:
A Save operation on table €˜RM_Customer_MSTR€™ failed accessing SQL data
EXCEPTION_CLASS_DB
DB_ERR_SQL_DATA_ACCESS_ERR€?
The odd thing is that if I drop the trigger from the RM00101 table, the exception error still occurs. Not just on the record originally updated, but on all records and all fields within the record.
Code for the trigger follows:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER CTrig_Logic_Update_Customer
ON RM00101
AFTER UPDATE
AS
IF UPDATE(CUSTNMBR) or
UPDATE(CUSTCLAS) or
UPDATE(CNTCPRSN) or
UPDATE(STMTNAME) or
UPDATE(SHRTNAME) or
UPDATE(TAXSCHID) or
UPDATE(ADDRESS1) or
UPDATE(ADDRESS2) or
UPDATE(ADDRESS3) or
UPDATE(CITY) or
UPDATE(STATE) or
UPDATE(ZIP) or
UPDATE(PHONE1) or
UPDATE(FAX) or
UPDATE(SLPRSNID) or
UPDATE(PYMTRMID) or
UPDATE(PRCLEVEL) or
UPDATE(SALSTERR) or
UPDATE(INACTIVE) or
UPDATE(HOLD) or
UPDATE(CRLMTAMT)
BEGIN
DECLARE @Server_Name Varchar(25),
@Logic_DB_Name Varchar(25),
@SQLStr nvarchar(1000),
@CustomerN int,
@SICCode int,
@ARContact Varchar(35),
@LongName Varchar(50),
@CustomerName Varchar(24),
@SalesTaxCode Int,
@AddrLine1 Varchar(40),
@AddrLine2 Varchar(40),
@AddrLine3 Varchar(40),
@City Varchar(30),
@StateProv Varchar(4),
@PostalCode Varchar(15),
@TelephoneN Varchar(25),
@FaxTelephoneN Varchar(25),
@SalespersonN Int,
@TermsCode Varchar(60), -- Put the customer terms into the CommentN1 field
@CustRateSched Int,
@SalesAreaCode Int,
@InactivePurge Tinyint,
@CreditStatus Tinyint,
@CreditLimit Int
------- Get the new Customer data from Inserted table
SELECT @CustomerN = CAST(RTRIM(i.CUSTNMBR) as Integer),
@SICCode = ISNULL((SELECT Dex_Row_ID FROM RM00201 WHERE RM00201.CLASSID = i.CUSTCLAS),0),
@ARContact = RTRIM(i.CNTCPRSN),
@LongName = RTRIM(i.STMTNAME),
@CustomerName = RTRIM(i.SHRTNAME),
@SalesTaxCode = ISNULL((SELECT Dex_Row_ID FROM TX00101 WHERE TX00101.TAXSCHID = i.TAXSCHID),0),
@AddrLine1 = RTRIM(i.ADDRESS1),
@AddrLine2 = RTRIM(i.ADDRESS2),
@AddrLine3 = RTRIM(i.ADDRESS3),
@City = RTRIM(i.CITY),
@StateProv = RTRIM(LEFT(i.STATE,2)),
@PostalCode = RTRIM(i.ZIP),
@TelephoneN = RTRIM(LEFT(i.PHONE1,10)),
@FaxTelephoneN = RTRIM(LEFT(i.FAX,10)),
@SalespersonN = RTRIM(i.SLPRSNID),
@TermsCode = RTRIM(i.PYMTRMID),
@CustRateSched = RTRIM(i.DEX_ROW_ID),
@SalesAreaCode = ISNULL((SELECT Dex_Row_ID FROM RM00303 WHERE RM00303.SALSTERR = i.SALSTERR),0),
@InactivePurge = i.INACTIVE,
@CreditStatus = i.HOLD,
@CreditLimit = i.CRLMTAMT
FROM inserted i
------- Get Logic server name and database name
SELECT @Server_Name = RTRIM(l.Server_Name),
@Logic_DB_Name = RTRIM(l.Logic_DB_Name)
FROM tbl_Logic_DB l
------- Insert new Customer record into Logic database
SET @SQLStr = 'UPDATE [' + @Server_Name + '].[' + @Logic_DB_Name + '].dbo.[Customer] ' + '
SET SICCode = ' + CAST(@SICCode as varchar(10)) + ', ' + '
ARContact = ''' + @ARContact + ''', ' + '
LongName = ''' + @LongName + ''', ' + '
CustomerName = ''' + @CustomerName + ''', ' + '
SalesTaxCode = ' + CAST(@SalesTaxCode as varchar(10)) + ', ' + '
AddrLine1 = ''' + @AddrLine1 + ''', ' + '
AddrLine2 = ''' + @AddrLine2 + ''', ' + '
AddrLine3 = ''' + @AddrLine3 + ''', ' + '
City = ''' + @City + ''', ' + '
StateProv = ''' + @StateProv + ''', ' + '
PostalCode = ''' + @PostalCode + ''', ' + '
FaxTelephoneN = ''' + @TelephoneN + ''',' + '
SalespersonN = ' + CAST(@SalespersonN as varchar(10)) + ', ' + '
CommentN1 = ''' + @TermsCode + ''', ' + '
CustRateSched = ' + CAST(@CustRateSched as varchar(10)) + ', ' + '
SalesAreaCode = ' + CAST(@SalesAreaCode as varchar(10)) + ', ' + '
InactivePurge = ' + CAST(@InactivePurge as varchar(10)) + ', ' + '
CreditStatus = ' + CAST(@CreditStatus as varchar(10)) + ', ' + '
CreditLimit = ' + CAST(@CreditLimit as varchar(10)) + ' ' + '
WHERE CustomerN = ' + CAST(@CustomerN as varchar(10))
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
EXEC sp_executesql @SQLStr
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 1 Replies
View Related
Aug 29, 2007
my asp.net application communicate with SQL database, but when I have single quote ( ' ) in my string then it throws error. and it does not insert that string in database. How can I solve this problem . or give me some suggestions on this issue.
thank you
maxmax
View 3 Replies
View Related
Mar 31, 2006
Hello Everybody / Anybody,
Sorry but exiting problem!
The Problem: OPENQUERY throwing error [Error 7357]when the source SP uses temporary table.
Description : Need to validate data against master list. My combo on UI has a source Stored Proc(contains a temp table in it).
I'm importing data from Excel. Before import, I want to validate it against my master list values.
[say field Priority has master values "High, Medium,Low".] and in excel user has added 'ComplexHigh' under priority field]
In this case, my import validator StoredProc should not accept value 'ComplexHigh' as it is not present in my Priority master list]
I'm preparing a temp table tabName containing o/p of SP, it works fine zakkas if my SP usp_SelectData does not contain temp table.
I think you got what the situation is!! Woh!
Note : I have searched net for this and found nothing! So its challenge for all of us. TRY OUT!!
------------------------------------- The Code ----------------------------
create proc usp_SelectData
as
create table #xx (FixedCol int)
insert into #xx select 1 union select 2
select * from #xx
drop table #xx
create proc usp_SelectData2
as
create table xx (FixedCol int)
insert into xx select 1 union select 2
select * from xx
drop table xx
-- Please replace MyDB with your current Database
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData')
-- Throws Error 7357 : [Could not process object 'EXEC MyDB.dbo.usp_SelectData'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.]
SELECT * INTO tabName FROM OPENQUERY('EXEC MyDB.dbo.usp_SelectData2') -- Works fine
Thanks in advance...
View 5 Replies
View Related
May 14, 2008
I've set up a report server on IIS on my local machine using SSRS 2005. All is well in the world--it shows up, and I can publish reports and have them display successfully.
However, I'm trying to publish a couple of reports based on an Access database--and this is where the problem lies. The reports run perfectly in Visual Studio, which is on the same machine as the report server. Thus, I can deduce that the ODBC connection to the Access database is set up properly. There are no errors on the report, which means that the reports are set up correctly. I've spent the last day researching the problem, but nothing I've tried thus far (from putting the Access database in a shared folder to changing security on the file and report server to rebuilding the reports from scratch) works. The exact error is given below, with the data source name changed:
An error has occurred during report processing.
Cannot create a connection to data source 'MyODBCDataSource.
ERROR [HY024] [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY024] [Microsoft][ODBC Microsoft Access Driver] '(unknown)' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
Can anyone suggest something I missed? I'm stumped on this one.
Edit: Just to clarify, EVERYTHING is on my local machine--the web browser I'm viewing from, IIS, SSRS 2005, the Access database, the ODBC connection (in the System DSN), everything.
View 3 Replies
View Related
Aug 8, 2007
Hi everybody.
I have a merge replication scenario between 2 servers.
Everything is OK when I write data in UP server (the server which does the replication).
But when I write data in any tables in the DOWN server throws an error saying that the table where I'm trying to insert data is updating or inicializating for merge replica. This table, where the error throws, isn't involved in the replica. In fact, that table is not involved in none replication.
Can you help me?
Thanks in advance
View 4 Replies
View Related
Apr 20, 2015
I have a SSRS report that parses RTF to TXT and displays it in a report.  The report works fine and everything displays correctly.  The subscription email, however, contains a report with errors: Request for the permission of type 'System.Security.Permissions.UIPermission, mscorlib, Version=2.0.0.0, Culture= neutral,PublicKeyToken=b77a5c561934e089' failed.The report contains embedded code as well as references to System.Security and System. Windows. Forms.  I've updated .....Reporting ServicesReportServer ssrvpolicy.config and ....Reporting ServicesReportManager smgrpolicy.config to change permissions to FullTrust.
View 2 Replies
View Related
May 21, 2015
I have created data model where I'm taking several sources of Point of Sale data (multiple retailers) and combing them using Power Pivot and a custom calendar. We get data retailer direct, which is mostly in weeks, and data from IRI which is in four week buckets. This does not allow me to use the date intelligence DAX functions. I'm brand new to DAX and my experience starts and ends with Excel. (Diagram view and link to file to come after account verification)Â .
The DAX code for calculating LY Sales is:
=
CALCULATEÂ (
    [Sales $],
    FILTER (
        ALL ( dCalendar ),
        dCalendar[IRIYearNumber]
            = VALUES ( dCalendar[IRIYearNumber] ) - 1
[Code] ....
The filters are to prevent items not on the item table from showing on the report, and the customer filter is to prevent all the sales being rolled together as and extra line (with blank customer) on the report.Â
The error happens when I select two periods that are in different years. When I select the 13 periods on 2014 all is well. But when I add a period from 2015 it throws the error below;
ERROR - CALCULATION ABORTED: Calculation error in measure 'dProducts'[LY Sales $]: A table of multiple values was supplied where a single value was expected.Â
View 5 Replies
View Related
Aug 3, 2015
I have a multi value  parameter called "Location" and this depends on another multi valued  parameter value. The default value for the parameter "Location" comes from the another another multi valued  parameter. Now say when the default value is set for the parameter Location like the below:
The Location parameter data set has values from the Query and default values has been set as shown below:
=Iif(array.IndexOf(Parameters!Program.Value,"A")>-1,nothing,"N/A")
I get an error on preview saying that . The Default Expression for the report parameter "Location" contains error:
Unable to cast object of type 'System.String' to type 'System.Array'.
View 2 Replies
View Related
Apr 19, 2006
I wanted to create a package to copy the objects from one database to another and replace those object if they already exist. Therefore, before the package executes you do not know whether all the objects exist on the target server or only some of them.
Using the 'Transfer SQL Server Objects Task' I have found that I cannot get this to execute cleanly by itself. If I set the 'DropObjectsFirst' to false then an error is thrown if the object exists and if I set it to true then an error is thrown if it does not exist.
In order to get round this I have had to create an 'Execute SQL Task' to list all the objects and then go through them dropping them on the target server in a for each loop before executing the 'Transfer SQL Server Objects Task' with 'Transfer SQL Server Objects Task' set to false.
However, is there a better way of achieving this or am I missing something in the 'Transfer SQL Server Objects Task'?
View 11 Replies
View Related
Sep 25, 2003
I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager
Now when I try to add Mikel, Im getting error 15401.
Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?
Can I go into the Master database and just change Mike login to Mikel ?
Thank you
View 3 Replies
View Related
Jun 23, 2008
SELECT OCRD.CardCode AS 'Customer No.', INV1.DocEntry as 'Doc En.', INV1.Text as 'Txt'
FROM INV1, OCRD
GROUP BY OCRD.CardCode, INV1.DocEntry, INV1.Text
This is the code that I expected to execute, but I am getting an error
When i put Text in the Group By its gives me an error
When i take Text out of the Group By it gives me an error
CardCode has numbers in it..DocEntry has numbers in it..Text has NULLs in it or blank values
Is there a way I can keep the group by and making this code work?
Any help is appreciated, thank you
View 8 Replies
View Related
Feb 20, 2008
Dear all,
The report I am trying to build are grouping the data by CATEGORY AND SUB-CATEGORY as shown below.
Product Category
Category1 Total: 5
Sub-Category1 2
Sub-Category2 3
Is it possible to put a static text above the Sub-Category group header row as shown below.
Product Category
Category1 Total: 5
Product Sub-Category (this is the line of text i want to added above the subcategory group header.)
Sub-Category1 2
Sub-Category2 3
Regards,
Yu
View 7 Replies
View Related
May 22, 2006
Hi,I have a problem on an old app. This application is a Help Desk, any Computer related problem (Motherboard Burn Out, Software Crash, dll) on my company will be recorded using this Help Desk application.One month ago, some programmer found a bug in this program lead to many duplicate data on database. But too late, This program has entered production stage and used for more than 2 years. This bug only happen at some condition so I found out after 2 years running (when my boss reported there are some duplicate data on "helpDesk" table).The bug is solved but helpDesk table need some "cleaning" operation. Here is structure of that table :helpDesk Table- id (primary key, integer auto increment)- reported_by (varchar 200)- problem_title (varchar 200)- problem_description (text)- date_summited (datetime)well usualy if I want to find some duplicate data I wil type :SELECT MAX(uid) AS uid, reported_by, problem_title, COUNT(uid) AS total_duplicateFROM problemsGROUP BY problem_title, problem_description,uidHAVING (COUNT(uid) > 1)But that query will not work because problem_description data type is "text" which not support "group by" statement.Any other Idea how to locate and cleaning up this duplicate data?thanks
View 2 Replies
View Related
Dec 27, 2006
Hi,
I have a situation where a tab limited text file is used to populate a sql server table.
The tab limited text file comes from a third party vendor. There are fixed number of columns we need to export to the sql server table. However the third party may add colums in the text file. Whenenver the text file has an added column (which we dont need to import) the build fails since the flat file connection manager does not create the metadata for it again. The problem goes away where I press the button "Reset Columns" since it builds the metadata then. Since we need to build the tables everyday we cannot automate it using SSIS because the metadata does not change automatically. Is there a way out in SSIS?
View 5 Replies
View Related
Nov 6, 2007
I have a matrix report with 4 row groups. I am experiencing a bug with the text that is displayed in the second group.
When it changes group 1, the text for second group is alway wrong for the first row in the list. It always shows the text that was displayed for the first row of the previous group1.
Here is what it looks like. Has anyone else seen this? Please tell me there is a solution and that matrix reports aren't just broken ...
Revenue
Income Type 1
Income A
Income B
Income C
Income Type 2
Income A
Income B
Expenses
Income Type 1
Expense A
Expense B
Expense C
Expense Type 2
Expense A
Expense B
Expense C
Expense Type 3
Expense A
Expense B
Expense C
View 5 Replies
View Related
Feb 13, 2008
Using SQL Server 2005 (9.0.3042), I can not get a full-text search query to work with order by or group by. For example, if I run the following query...
SELECT s.networkID, i.interID
FROM Interactions i
INNER JOIN CONTAINSTABLE(Interactions, text, 'ipo') t ON t.[key] = i.interID
INNER JOIN Sessions s ON i.sessionID = s.sessionID
WHERE i.startTime BETWEEN 1051772400000 AND 1054537199000
It returns two rows immediately.
If I add an "order by" or "group by" like...
SELECT s.networkID, i.interID
FROM Interactions i
INNER JOIN CONTAINSTABLE(Interactions, text, 'ipo') t ON t.[key] = i.interID
INNER JOIN Sessions s ON i.sessionID = s.sessionID
WHERE i.startTime BETWEEN 1051772400000 AND 1054537199000
ORDER BY s.networkID, i.interID
The CPU shoots to 100% and I have to kill the query. I have tried many variations of the above and I can not get the query to work with order by or group by. Is this a bug in SQL Server? Is there a fix or workaround?
View 1 Replies
View Related
Oct 19, 2007
Hello,
I am having a problem trying to do some conditional formatting on the text color.
This textbox is in the Group Footer so i essentially want change the color to red if the SUM of one field in the group is less than the SUM of another field in the same group.
I am doing this:
=Iif(Sum(Fields!YTDChargeHours)< Sum(Fields!YTDForecast), "Red", "Black")
but i am getting this error:
[rsRuntimeErrorInExpression] The Color expression for the textbox €˜YTDChargeHours€™ contains an error: Operation is not valid due to the current state of the object.
Thanks in advance!
View 4 Replies
View Related
Apr 25, 2006
I get this error:
The data types text and text are incompatible in the equal to operator.
when trying to execute this query
SELECT id FROM users WHERE username=@userName
Any Ideas?
View 3 Replies
View Related
Oct 2, 2005
i wonder what is the best approach to use !!!i have creditLimit column in Customer Table the default value will be 500 and this limit to allow users to send sms from my website ..... every month they will be allowed to send 500 sms referring to the credit limit column.... now !!if the user sent today some sms and after few days sent another 20 and after one week he sent 150 SMS as a total so that means he has only 350 SMS as credit to use this month!!!so what is the best approach to implement this solution ? shall i have another field in the table called 'CreditUsed' and that will be updated each time the user will send SMS and this value will be compared with the credit limit ORi use the formula for the column to calculate the credit left and do the maths !!!what is the best approach as you think ???thnaks for reading this question !!
View 7 Replies
View Related
Dec 9, 2005
Hi
I wonder if its possible to return a value at same row from another colum if col1 contains NULL (or other criteria)
ex:
the output is:
col1,col2,col3
text1,text10,text100
text2,text20,text200
text3,NULL,text300
Can i take a value from col3 and return it to col2 in the output if a criteria is meet (like NULL)
the DDL is nvarchar254 at thoose colums.
thx in advance //Mr
View 3 Replies
View Related