I have a tabkle that I want to use for NT authnetication. It was a list of domain lan usernames. I want to use NT authentication in ASP but in order to do so I need to add the domain to each of the lan ds. So my username, kssensalo needs to be DOMAINkssensalo. I need to do this for 5,000+ plus records. What SQL fucntion would I use? I know I can use LTRIM to remove, but I need to add or append.
Trying to update part of a field. Currently using ColdFusion 4.0 and SQL Server 7.0. My field looks something like this: ABC.DEF.GHI and I just want to update the last 3 characters, GHI. The length of the field may change so it's not going to be 11 characters long. Any help would be appreciated.
I have a very beginer's question. I am trying to compare datetime field with time only. how can i just compare with time?
Select start_time,end_time from tblvouchers as sv,tblvoucherlines as svl where sv.voucher_no = svl.voucher_no and sv.equip_no = '01325' and svl.line_date ='03/14/2004' and svl.start_time = '18:00' and svl.end_time = '00:00'
I want to set a filter in my statement. Normally it is no problem, but this time I want to set a filter only on a part of the field.
For example: In my table with ordernumbers, I have orders with are starting with 'FO', VO' (FO0001, FOO0002, VO0001, VO0002, ect etc) I want to set a filter in my statement that I only wish to see the ordernumbers starting with 'FO'.
I got a problem which looks like the same with a futher topic of me (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1156355&SiteID=1), but this one is a little bit more difficult.
I want to set a filter on a field, but the it must set a filter on a part of the field. I think it will be more clear with an example:
I have a list with ItemNumbers and ItemDescriptions. ItemNo ItemDescription 1 Pineapple yellow 2 Cauliflower size 6 3 Orange sweet yellow etc. etc.
Now I want that the user of this report can set a filter just by entering a part of the description. So, if he enters 'flower' he only see the cauliflower. If he enters 'yellow' he only see the pineapple and the orange, etc. etc. If a blank value is left, all the item must be shown.
Can some one help me out by solving this problem??
new question about an old topic (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1232451&SiteID=1):
I want to set a filter on a field, but the it must set a filter on a part of the field. I think it will be more clear with an example:
I have a list with ItemNumbers and ItemDescriptions. ItemNo ItemDescription 1 Pineapple yellow 2 Cauliflower size 6 3 Orange sweet yellow etc. etc.
Now I want that the user of this report can set a filter just by entering a part of the description. So, if he enters 'flower' he only see the cauliflower. If he enters 'yellow' he only see the pineapple and the orange, etc. etc. If a blank value is left, all the items must be shown.
After this, Jarret, suggest to me to insert the next code into my query:
where ItemDescription like '%' + @ItemDescParam + '%'
This helped, but I want to have 2 options extra: 1) The part of the description which the user inserts must be independent of capitals 2) It also have to be possible that the user inserts more than one part. For example "pine" and "fellow"
I have about 200 records with a field (fileName) that have an ampersand(&) somewhere in them. I need to update the fileNames and remove all occurances of "&" and replace it with "-"; without modifying any other part of the file name.
Is there a SQL command I can use to update only part of a field?
I'm trying to extract the time part of a smalldatetime field in a usprocedure to return just the time. I've used Datepart() to get the hours or minutes but you can't get both without getting messy. Anyone know a way to keep it neat & simple?
I would like to know the best way of returning just the Date part (dd/mm/yyyy) of a DateTime field using T-SQL. I know that some methods mean that you lose the indexing if the field is indexed. Which is the best way to do this in T-SQL whilst preserving the indexing? I'm using SQL Server 2005.
hi, I have a field named city_state that contains city and state together (Warren, OH) in the same field. I need to create two seperate columns one city, another state. how can I extract the state out of the city_state column and have two different column. thanks
From the table above the current running shift is Id = 1
Based on that I have following question:
1 - Is there a way to specified for a DateTime field that it will recieved only Time part ?
2 - When a shift change occurs, I need to compare the Current time with the time shift in the table. Then according to that I set the IsCurrent flag to proper new Shift ID.. how to perform this ?
Hi all I was wondering whether it was possible to remove or delete part of a String in a Field? Lets take for example I have:
- A Table called: Table_1 - A Field called: MyField - MyField contains the value: Hello I am on the msdn forums
Is it possible to perform an UPDATE Query which deletes part of that sentence? If this was hard coded it would be rather simple but if the phrase was changing which would lead to the sentence also changing is it possible to do this?
Lets say I wanted to remove the part which said msdn forums. Then UPDATE the field again which should leave out the bit msdn forums.
I have a very large CSV file containing name-and-address information which I am reading in a Visual Basic project using the Microsoft.JetOLEDB.4.0 provider.
The key field on which the CSV file is to be filtered is the PostCode field. This is a UK-format PostCode "XXnn Nxx" where "XX" is one or more letters denoting a geographical area within the UK and "nn" is one or more characters (starting with at least one numeric digit) which when combined with the area code denotes a specific district within the geographical area. My aim is to identify all the unique UK postal districts held within my address CSV file.
Because I do not know how to use SQL to filter on the partial contents of a database field I am presently reduced to extracting unique full PostCodes using "SELECT DISTINCT PostCode,City,County FROM [ADDRESSES.csv]" into a DataTable object, then sequentially reading that DataTable using the operation of a dictionary object to identify unique PostCode areas, to finally construct the DataTable I need.
Is it possible in SQL to select records where the value of a varying number of characters before a space character in a given (PostCode) field is unique?
I have a table that holds a large amount of text in a field that is the body of the email. For example, it might say something like:
Quote: Email tech support at thisemail@email.com if you have any questions about the results of this test.
I need to change the email address in this field. Using this example I need to change thisemail@email.com to thatemail@email.com; however I do not want to change the other text in that field.
It is also important to note that the rest of the body of the emails stored here is different depending on the email.
So basically what I need is a statement that would look at a particular field, search for an email address, and replace that email address with another one without disturbing the rest of the text in that field. I already checked the w3 update tutorial and the update there is for the entire field.
I have a table with a datetime field (named "lnDateDone") and an integer field (named "PlannedHour"). I want to build a new datetime field with the same value as lnDateDone and replacing the hour part by the PlannedHour field. Example:
lnDateDone = '2014-04-09 13:22:31.544' HourPlanned = 14 new datetime field = '2014-04-09 14:00:00'
I tried the following:
CAST(SUBSTRING(CAST(lnDateDone AS VARCHAR(25)),1,12) + CAST(PlannedHour AS VARCHAR(2)) + SUBSTRING(CAST(lnDateDone AS VARCHAR(25)), 15, LEN(lnDateHeureFin)) AS SMALLDATETIME) AS DatePrevue
but it won't work when casted in DateTime format, only in varchar, but I want it in datetime as I need to do compute some values using date manipulation functions.
from this, circdate being a datetime field:SQLQuery = "select distinct circdate from circdata order by circdate"I need the distinct date portion excluding the time part.this has come about when I discoveredI am inserting and updating some datetime values with the same value,but for some reason, the values are always off by a few seconds. I seta variable called SetNow assigned to NOW and then set the datetimefields to this SetNow variable. Then when I collect the distinct datetime I am assuming they will have the same values recorded incircdate, but no, they are off by several seconds. Makes no sense to meat all. I tried renaming the variable several times but it makes nodifference at all.any help appreciated, thanks.
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID = U.FeedID
WHERE M.FeedID = ?
But following error:
[Execute SQL Task] Error: Executing the query " UPDATE Property SET ImageList = U.ImageList FROM Property M INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID = U.FeedID WHERE M.FeedID = ?" failed with the following error: "The multi-part identifier "M.FeedID" could not be bound.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
ByPassPrepare is set to TRUE and ParameterName = 0 to variable User::Feed_ID
HOWEVER - following query executes fine:
UPDATE Property SET
ImageList = U.ImageList
FROM Property M
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID = U.FeedID
WHERE M.FeedID = 11
Beats me - any help with explaining this to me please?
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
Hi Everyone This is the query and I am getting follwoing error message
"The multi-part identifier "InvDate.Account Reference" could not be bound."
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE, CC.[COMPANY], CC.[ACCOUNT REFERENCE], INVDATE.[LASTORDERDATE] FROM CUSTOMERCONTACTNOTES AS CCN, (SELECT * FROM CUSTOMER) AS CC, (SELECT MAX([INVOICE DATE]) AS LASTORDERDATE, [ACCOUNT REFERENCE] FROM INVOICEDATA GROUP BY [ACCOUNT REFERENCE]) AS INVDATE WHERE CCN.[COMPANY] = CC.[COMPANY] AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE]) FROM INVOICEDATA) AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE] GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE] ORDER BY CC.COMPANY ASC
By the way its SQL Server 2005 Environment. Mitesh
I have SSRS 2005 SP2 configured to work in Sharepoint integration. Everything works fine except that I am not able to programmatically change any property of report viewer web part (instance) that I have added on on home page of my sharepoint site. I can do same thing via sharepoint UI but not through program. When my programs runs it fetches all web parts been added on home page, then I need to iterate through each one and find report viewer web part. While iterating, as soon as I arrive to report viewer web part it is named as "Error web part" with error message as "Windows SharePoint Services cannot deserialize the Web Part. Check the format of the properties and try again"
If someone has a solution, please respond at your earlist.
I have a table with a column named measurement decimal(18,1). If the value is 2.0, I want the stored proc to return 2 but if the value is 2.5 I want the stored proc to return 2.5. So if the value after the decimal point is 0, I only want the stored proc to return the integer portion. Is there a sql function that I can use to determine what the fraction part of the decimal value is? In c#, I can use dr["measurement "].ToString().Split(".".ToCharArray())[1] to see what the value after the decimal is.
What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config The CCWW is my PC's name, normally I can connect to the database ASPNETDB.MDF correctly either Part 1 or Part 2 in a web page,After I open Database Explorer panel and browse ASPNETDB.MDF, I can't connect to database using Part 2 when I open a webpage in Microsoft Visual Web Developer 2005 Express Edition,but I can correctly open a webpage using Part 1 after I open Database Explorer panel. What different are there between connectionString (Part 1) and connectionString (Part 2) in web.config? I guess while I use Part 1 to connect, maybe it will be cancel exclusive method of the database ASPNETDB.MDF first, but when I connect to database using Part 2, maybe two programms both Part 2 and Database Explorer visit ASPNETDB.MDF at the same time!
In a recent post, I've read that Jet Provider is only provided with 32-bit.
I wonder, is there any problem if you run a SSIS package 32-bit which read from Access and then call another SSIS package 64 bit?? We've got quite 2000 dts which pulling data from MDB and within a time it'll disappear in favour 2005 environment .
Another question, what sort of Providers don't offer us 64-bit versions???