|
OK, I've already answered this in the C# forum, but I'll answer it here as well. As you are aware, the _ is a wildcard character, so it matches any character. If you need to match the _ as a valid character, then you need to escape it with the \ character, which means that your query becomes:
UPPER(DOC.ORIGINAL_FILE_NAME) like '%TEST\_%'
|
|
|
|
|
Another alternative is to enclose it using []
Example
like '%TEST[_]%'
string str = "TEST_"
str= str.Replace("_", "[_]");
|
|
|
|
|
I am having problems with the following SQL command:
SELECT * FROM Classes
---Works like a charm
SELECT * FROM Classes WHERE Class_Number = 2222
---Fails with this error:
System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '11-1111' to data type int.
---I can understand that. Without quotes, sql thinks the Class_Number column is of type int (its actually nvarchar). So let me add quotes...
_______________________________
SELECT * FROM Classes WHERE Class_Number = "2222"
---Fails with this error:
System.Data.SqlClient.SqlException (0x80131904): Invalid column name '2222'.
Now that error has me stumped!!!
What am I missing here?
|
|
|
|
|
Use single quote '2222'
david
|
|
|
|
|
Thanks, that worked. Kind of funny how simple that was. In the time it took to get a response, I wrote a complicated Regular Expression Validator control to get around the issue..... Let me go delete that. lol
|
|
|
|
|
Hi,
anyone which database LinkedIn is using to store thier huge amount of records?
Thanks,
Jassim
|
|
|
|
|
Why not just ask them?
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
LinkedIn use a variety of database technologies, including Oracle, MySQL and their own Voldermort. You can find more information in this[^] thread.
|
|
|
|
|
Damnit Pete, you said his name. You know it's cursed right?!
Craigslist Troll: litaly@comcast.net
"I have a theory that the truth is never told during the nine-to-five hours. "
— Hunter S. Thompson
|
|
|
|
|
Hi,
anyone tried installing mySQL on Windows 2008 Server R2 64bit? what's your feedback?
|
|
|
|
|
Thanks in advance,
I have a SQL Server 2005 standard Edition installed on a Win XP Professional OS , and i want to connect a client having Win 7 home OS , but i am unable to connect to the Server , i tried all the options like Remote connections etc are enabled also but it is not connecting.
I tired to check Connection through ODBC with Name Pipes options it worked but did not work with TCP/IP while in my surface area connections its ticked to connect through both Named PIPES and TCP/IP. other machines are working fine
but on this PC i am unable to connect. Any suggestions.
I installed SQL Express and tried to connect to that Instance on Remote PC (XP) but its not connecting even through SQL Express.
SOFTDEV
Luck in life always exists in the form of an abstract class that cannot be instantiated directly and needs to be inherited by hard work and dedication.
|
|
|
|
|
Firewall on Win7 blocking port TCP/1433 maybe?
Look in the Win7 event logs and/or the firewall logs.
|
|
|
|
|
Good evening all
i have an image in my select fields, and i know that the Group by clause cant take the Image field, i have a query that looks like this
SELECT
K.KIDID,
K.STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SC.SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SC.SCHOOLID,
k.TEMP_BARCODE,
PHOTO,
A.ACCOUNTID
,SUM(TRANSACTION_AMOUNT) AS [BALANCE]
FROM
KIDS_DETAILS K
INNER JOIN SCHOOL SC
ON K.SCHOOLID = SC.SCHOOLID
INNER JOIN PARENTKID PK
ON PK.KIDID = K.KIDID
INNER JOIN USERS U
ON U.USERID = K.USERID
INNER JOIN ACCOUNTS A
ON A.USERID = U.USERID
INNER JOIN ACCOUNTRANSATIONS AT
ON AT.ACCOUNTID = A.ACCOUNTID
LEFT OUTER JOIN KIDSPHOTO KP
ON KP.KIDID = K.KIDID
GROUP BY
K.KIDID,
K.STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SC.SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SC.SCHOOLID,
k.TEMP_BARCODE,
A.ACCOUNTID,
PHOTO
now as you can see there is a part where i sum , but now i have a PHOTO column, how can i remove this with out getting the common exception that will tell me that Photo is not in
Column 'KIDSPHOTO.PHOTO' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
and if i include it i will get the group by Clause limitation of the Type Text , image
Msg 306, Level 16, State 2, Line 40
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
I got a Solution by casting the Field
Select
KIDID,
STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SCHOOLID,
TEMP_BARCODE,
Cast(PHOTO as Image) as [PHOTO],
ACCOUNTID,
Balance
From
(
SELECT
K.KIDID,
K.STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SC.SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SC.SCHOOLID,
k.TEMP_BARCODE,
Cast(PHOTO as Varbinary) Photo, ---- Some Changes
A.ACCOUNTID
,SUM(TRANSACTION_AMOUNT) AS [BALANCE]
FROM
KIDS_DETAILS K
INNER JOIN SCHOOL SC
ON K.SCHOOLID = SC.SCHOOLID
INNER JOIN PARENTKID PK
ON PK.KIDID = K.KIDID
INNER JOIN USERS U
ON U.USERID = K.USERID
INNER JOIN ACCOUNTS A
ON A.USERID = U.USERID
INNER JOIN ACCOUNTRANSATIONS AT
ON AT.ACCOUNTID = A.ACCOUNTID
LEFT OUTER JOIN KIDSPHOTO KP
ON KP.KIDID = K.KIDID
Group by
K.KIDID,
K.STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SC.SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SC.SCHOOLID,
k.TEMP_BARCODE,
Cast(PHOTO as Varbinary),
A.ACCOUNTID
) X
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Alternatively, you can use this. I'm assuming that the column PHOTO is in the USER table. I see that some of the columns in the query have not been prefixed with an alias, it is always a better practise to prefix all column names with alias, it improves performance too...
SELECT
K.KIDID,
K.STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SC.SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SC.SCHOOLID,
k.TEMP_BARCODE,
(SELECT PHOTO FROM USER UP WHERE U.USERID = UP.USERID) AS PHOTO,
A.ACCOUNTID
,SUM(TRANSACTION_AMOUNT) AS [BALANCE]
FROM
KIDS_DETAILS K
INNER JOIN SCHOOL SC
ON K.SCHOOLID = SC.SCHOOLID
INNER JOIN PARENTKID PK
ON PK.KIDID = K.KIDID
INNER JOIN USERS U
ON U.USERID = K.USERID
INNER JOIN ACCOUNTS A
ON A.USERID = U.USERID
INNER JOIN ACCOUNTRANSATIONS AT
ON AT.ACCOUNTID = A.ACCOUNTID
LEFT OUTER JOIN KIDSPHOTO KP
ON KP.KIDID = K.KIDID
GROUP BY
K.KIDID,
K.STUDENTNUMBER,
KIDNAME,
KIDLASTNAME ,
SC.SCHOOLNAME ,
KIDGRADE ,
KIDCLASS ,
SC.SCHOOLID,
k.TEMP_BARCODE,
A.ACCOUNTID
|
|
|
|
|
Hi,
To get some more structure in my window form application, I want
to move a DataSet in a custom "DAL" folder.
For some reason I cannot reopen the Dataset in the designer.
Get following error :
"Value does not fall within the expected range".
When I place it in the root folder everything works fine.
Why ?
TIA
|
|
|
|
|
paper67 wrote: To get some more structure in my window form application, I want
to move a DataSet in a custom "DAL" folder.
Did you move it using Windows Explorer, or did you move the file from within the VS IDE?
paper67 wrote: When I place it in the root folder everything works fine.
I'm guessing that VS cannot update the path to the files when you copy it using the Explorer. You could try to drag&drop it in the 'Solution Explorer' in the IDE, that should update all references during the move.
Bastard Programmer from Hell
|
|
|
|
|
I am using Openrowset function of sql server to read data from excel files.
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=E:\IGM\Files\cis overview.xls;HDR=No;IMEX=1;','SELECT * FROM [CIS Overview$]')
But all columns are not showed by openrowset.
below is just sample, the excel file which i am reading having 28 columns, and 27 having no data in it, just heading, and 28 is ignored by Openrowset.
col1 col2 col3
--------------
A X
B X
C X
. .
. .
Openrowset is not returning col3 in resultset?
Its only showing col1, col2.
But if i insert some data in col2(any cell),
result set will have all three columns.
It is ignoring last column if previous has no data?
i also include HDR=NO option in query, but no luck?
Is their any way to get all columns in result set?
any help?
regards,
|
|
|
|
|
Try putting headers in the first row of the spreadsheet and use HDR=Yes option.
|
|
|
|
|
For info: I'm using BlogEngine 1.6
I dunno what's happening with my blog, but removing any comment is so damn slow... like half an hour for the page to refresh after deleting 10 comment!!!
Desperate, I open the Sql Server Management studio and connected to my blog database and ran the following SQL:
DELETE FROM [codeblog].[dbo].[be_PostComment]
WHERE [IsApproved] = 0
Now If I open another query and ran
SELECT *
FROM [codeblog].[dbo].[be_PostComment]
ORDER BY IsApproved
I code plenty of result with IsApproved = 0
Worst the number of result return by such statement change at an alarming rate!
Right after my delete statement I got, say, 50 comment. A few second later (if re-run it) I get 150, and then 350, etc...
Argh.. what's going here!?!?! what COULD BE going on!
A train station is where the train stops. A bus station is where the bus stops. On my desk, I have a work station....
_________________________________________________________
My programs never have bugs, they just develop random features.
|
|
|
|
|
Maybe there is a spambot posting comments quickly, like say around 200 a second.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
What will happen if 1000 users trying to execute the stored procedure at a time?
How it will work? Single thread or multi thread or FIFO manner?
A person might be able to play without being creative, but he sure can't be creative without playing.
|
|
|
|
|
When multiple users try executing the same stored procedure, multiple instances will start running asynchronously. What happens next depends on the body of the stored procedure: if different instances of the stored procedure only read the data, or if they update distinct rows, all instances would run asynchronously to completion. Otherwise, some of them would block, following the usual rules of locking for your transaction isolation level. As a consequence, you can also force synchronization in the body of a stored procedure by selecting for update (or selecting with rowlock, etc.)
|
|
|
|
|
When a task request is received, SQL Server will attempt to allocate the task to an available worker thread. If no worker thread is available, a new one will be created to handle the task, up to the configured maximum number of threads. Once that maximum limit has been reached, task requests will queue until a thread becomes available.
As you might guess, it's quite a lot more sophisticated than that, but that is essentially what it boils down to. If you want to know more detail, there are plenty of articles on Google that will explain it in as much detail as you want.
|
|
|
|
|
I thought that I understood them, but they aren't working as I expected. Perhaps someone can help dispel my confusion?
I have a table that is comprised of an incrememnting, numeric primary key, a date field, and several other fields (that are irrelevant to the problem). It is range partitioned by day on the date field, thus creating a new table partition for every 24 hours of data.
The problem comes when I need to drop an old partition. I created the PK index as LOCAL, which (as I understood from the documentation) should have made it equipartition with the table. However, when the old partition(s) are dropped, it becomes unusable, and rebuilding the index can take hours (it is a VLDB).
Any ideas what I am doing wrong..? Thank you.
|
|
|
|
|