Checking Contents Of Column And Replacing Contents If First Character Is A Letter
Hi All,
I have come up against a wall which i cannot get over.
I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.
I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.
I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.
Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.
This is how far i have got: casting the varchar column to datetime and comparing.
SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"
Many thanks in advance!
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Updating /replacing Contents Of The Table
Is it possible to do an update * where in we update all the contents of the table after selecting specific rows of the same table. for ex: create table #Result(rowid int identity(1,1),cust_no int,companyname varchar(100),address varchar(200),zip varchar(9)); insert into #Result(cust_no,companyname,address,zip) where (customertype='r' and customer.comp_id ='11') group by customer.cust_no, customer.company select * from #Result where customerstatus='s'; after this i want to update/replace the #Result with whatever results I have from the select statment. Thanks for your replies.
View Replies !
View Related
Added Space Character On The Header/Footer Contents Of An Exported Report In Excel
A space was added as the first character of the contents in the Left, Center and Right section of the Report Header and Footer exported to Excel. Example : In the RDL, Header values are: Left = "Product Report" ; Center = "Confidential" ; Right = "Page n of n" In the exported report to Excel, Header properties (-->File.-->Page Setup--> Header/Footer Tab) are: Left = " Product Report" ; Center = " Confidential" ; Right = " Page n of n" Is there a way to eliminate that space character? Thanks in advance.
View Replies !
View Related
Replacing First Letter Only In Column
Hello. We have a list of 1000+ sku numbers. 90% of them start with u. I need to replace the u with T. Concerns: It has to only replace the first letter, if there are other "u"s in the sku, they need to remain. Not all skus start with u, those that don't should remain the same. I have searched and searched because I'm sure someone has requested the exact same thing but I can't find anything that is identical. I have this so far but I know its not correct: set sku = replace(left(sku,1),'u',right(rtrim(sku),1)+ 'T') Can someone help lead me down the right path? Thanks in advance!!!
View Replies !
View Related
How To See Contents Of Image Column
Hello,In my MS SQL SERVER database i have an column with datatype image. Somerecords do have data in this column, some others don't. When i look at thistable in Enterprise Manager then i see in every field '<image>' so no way tosee directly wheter a filed has data or not. Is there a way to changedisplay of this column so i can see the binairy data in it?Marcel
View Replies !
View Related
Printing Column Contents Vertically
Hi All, I would like to print a report that is 64x64 matrix. I want the columns to be narrow and the headings for the columns to be vertical like is possible in excel. The text for the heading fields also comes from a stored procedure. I have hunted and pecked around but I cannot seem to find an option that sets the orientation of the text for a text box to vertical. Is there one? If not, does anyone have a suggestion as to how I can make these column headings vertical and nicely fomatted all the time? Again, looking for the same effect as in excel. Thanks
View Replies !
View Related
Copying A Database And All Contents
Can anyone advise me of a convenient way to make a copy of a database in SQL2005. I need to make a complete copy - including all Stored procedures, functions tables and table contents. I want the place the copy ont he sae SQL server but obviously under a different name.
View Replies !
View Related
Need Help Splitting Contents Of A Field In T-Sql
I have a table with about 2 million records. One of the fields has data seperated by a comma. I need to be able to grap the data for each record and split those items into their own table in seperate rows. This is easy in Asp but the page will timeout before it can process all the records. Any Ideas? Thanks,Rick
View Replies !
View Related
Merging Contents Of One Db To Another Huge Db.
I am storing data temporarily in a database... and periodically needto update that huge database, so just has to copy that temp DB to theoriginal one.Using sql may take a few hours to finish this operation. I think youcan do "export" from temp database into a file and "import" into thehuge database. That will be binary data exort and import and hencewill be faster. But, I am not sure if all databases will support thatthough.So, is there any other solution?
View Replies !
View Related
Contents Of Image Fields
Hi,I have a SELECT FROM TABLE query and in that table there is a field of typeimage. Result of this select goes throug internet do its destination. But infact I need only to know if in this field is or not an image. Is there anyfunciotn which gives me information about contents of image fields?Regards, Paul
View Replies !
View Related
List The Folder Contents Using SQL
Hi all, I have built a Disaster Recovery Site for my DB.on periodic basis, trn files from production server reach DR server and DR setup will apply them locally. If the flow is smooth then no issues, if one of the file does not reach DR the entire setup will halt for the want of the file and I dn't have any means to know which file is missing. Both my production and DR site located remotely behind firewalls, i.e i can't physically access the servers or remotely login to the server. Can anyone tell me how to see the contents in a folder in the server using SQL query . Any help is highly appreciated Thanks and Regards Srinivas Varanasi
View Replies !
View Related
Masking Cell Contents
Is there a way to mask the contents of a cell or column? I have a table that stores passwords and I would like to mask the password much like Access does with asterisks. I know I can restrict the column based on user but that creates other problems on the frontend.
View Replies !
View Related
Ordering Rows For Contents
I have a series of records, that are displayed as contents in a .NET application,i know i can order the items to be dynamic, i.e I set the top item in the contents, However I know this will get quite messy at the application level. I would like to set up the default if I insert an item, into the table to make it the last in the list, select max(ordernum) + 1, i guess? How can I set an on insert constraint, or what its called for this in SQL Server?
View Replies !
View Related
Error.log Contents Interpetation
Where can I get a reference document that will help me troubleshoot and interpet the MS SQL Server ERROR.LOG file? Something that would describe in detail what the error is and various actions I could take to correct the errors. JLS
View Replies !
View Related
Read The Contents Of CD..(urgent)
Hai. Here i am sending my query.This is urgent to my job.Please give me solution as early as possible in SQL server. Thanks. My query is.. Check for CD drive on your system.If drive available ,display the name of the CD and read the contents of the CD,and search for given file in CD. I want search this on local machine and on remote system. Thanks. with regards. laxma P.reddy
View Replies !
View Related
Creating Table Of Contents
Hi, Ive yet to come across an easy solution for this even in Crystal reports. Is there a way of creating efficiently table of contents with page numbers and titles without having to run the report twice. Once to generate the table of contents to a database table and again to read the table of contents in and generate the report ? Thanks Stubey
View Replies !
View Related
Getting Datasource And DSV Contents Programmatically
Hello to everyone. I have two questions. 1) As you know it's possible to get information about models by using the schema rowsets in c#. I wondered that it is possible to get datasource and datasource views contents programmatically? And if you have some pieces of code please write here. 2) In the topic http://www.sqlserverdatamining.com/ssdm/Default.aspx?tabid=61&Id=6 it says that visio can help us to draw algorithms visual outputs. Could anyone send me or paste the links of screenshots of sucessful rendering. 3) i have an error in my sql server management studio. It gives an error message whenever i clicked on an of menu items in the left pane.(www.ahmetselman.com/error.jpg) in this url you can see my screenshot. Has anyone here, faced this problem? Why i got this error?
View Replies !
View Related
Report Snapshot Contents
I have a report that has a snapshot enabled. I made some changes to my report and redeployed it, but when I view the report from the Report Manager web site the old version is getting used. Apparently the snapshot includes the rdl and the data and not just the data. Can anyone else confirm this? If this is the case, a new snapshot must be created after the report format is modified. Dean
View Replies !
View Related
Print Table Contents
Hello I want to have a print of table records in sql server express. I went several locations, but File|Print item didn't on. How can i print table contents in sql express environment ? Thank you for helping me.
View Replies !
View Related
Looping Thru A Table And Updating The Contents
Hello, Its hard trying to explain this. I have 3 tables Table 1 is where the users are stored, each user has a username and a userrank Table 2 is where the points that decides the userrank are stored Table 3 contains the available userranks like this Table 1 (user_list) looks briefly like this:username nvarchar(20),userrank int, -- Reference to Table3 id... alot more fields Table 2 (settings_profile) looks like this:username nvarchar(20),total_active_points int,... some more fields Table 3 (data_ranks) looks like this:id int primary key auto inc,rankname nvarchar(20),min_pts int,max_pts int Points get added to table 2 whenever they do something that generates points on the site. Points also get withdrawn every 7 days, so a user can only collect points for 7 days, on the 8th day, all points he earned on the 1st day is reduced from the current points with this code: WHILE (SELECT @username = username, @id = id, @temp1 = ap_sentmails, @temp2 = ap_createdthreads, @temp3 = ap_createdanswers, @temp4 = ap_signguestbook, @temp5 = ap_blogcomment, @temp6 = ap_createblogentry, @temp7 = ap_profilefirsttime, @temp8 = ap_profilephoto, @temp9 = ap_activateguestbook, @temp10 = ap_addnewfriend, @temp11 = ap_superguruvote, @temp12 = ap_forumtopicvote, @temp13 = ap_labervote, @temp14 = ap_funstuffitemvote, @temp15 = ap_movievote, @temp16 = ap_actorvote, @temp17 = ap_money_new WHERE (created < Dateadd(dd, -7, @todaysdate))BEGINSET @sum = 0SET @sum = @temp1 + @temp2 + @temp3 + @temp4 + @temp5 + @temp6 + @temp7 + @temp8 + @temp9 + @temp10 + @temp11 + @temp12 + @temp13 + @temp14 + @temp15 + @temp16 + @temp17UPDATE settings_profile SET total_active_points = total_active_points - @sum WHERE (username = @username)DELETE FROM konto_daylist WHERE (id = @id)END Now my question is this, i want to loop thru the table A, collect all usernames inside of it, then run it against table b and table c to determine the current rank of the user.Something like this... DECLARE @username nvarchar(20)DECLARE @pts int, @rank int ...something that starts a loop thru table A (user_list) and get the username into @username... SELECT @pts = total_active_points FROM settings_profile WHERE (username = @username)-- Determine the rank here, by compairing the points the user have against the pointstabel in table data_ranksSELECT @rank = id FROM data_ranks WHERE (pts_min => @pts AND pts_max < @pts)UPDATE user_list SET rank = @rank WHERE (username = @username) ...next persion in the loop... This SP runs once a day and will first reduce the points from 8days ago, then it will run thru all the users and determine their new rank... But how do i loop thru all the users? with a cursor?
View Replies !
View Related
Move A Table (and Its Contents!) To Another Database
Hi, I am relatively new to this stuff. I am using Microsoft SQL Server Management Studio Express (9.00.1399.00) Can someone tell me the way to get a table and its content to another database (I use two at webhosts4life) Or perhaps a way to export the data of a table so I can do it at a later stage. Is that at all possible with this program or do I have to use the non-express version? Thanks in advance, Lex
View Replies !
View Related
How To Save Contents Of Text Box To Database?
Hi, For some reason I can't use the edit, update or insert features on my remote shared server, so I am looking to create a web page that has text boxes on it, that I can enter data into, that will be saved into my database. This is opposed to entering the data directly into the database itself. I want to be able to use a webpage, for simply adding new data, and saving it so that the new data updates and saves over the top of the old data. What are the steps involved in doing this? Any example code for just one text box would be appreciated, I could then extend it to suit my needs. Tia.
View Replies !
View Related
Compile/combine The Contents Of Several Records.
I have the following table;CREATE TABLE [x_Note] ([x_NoteId] [int] IDENTITY (1, 1) NOT NULL ,[Note] [varchar] (7200) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_x_NoteId] PRIMARY KEY CLUSTERED([x_NoteId],) WITH FILLFACTOR = 90 ON [USERDATA] ,) ON [USERDATA]GOMy clients want me to take the contents of the Note column for each rowand combine them. In other words, they basically want:Note = Note [accumulated from previous rows] + Char(13) [because theywant a carriage return] + Note [from current record].What is the most efficient and relatively painless way to do this? Ithink it might require a cursor, but I'm not sure if there is a moreelegant set-based method to make this happen.
View Replies !
View Related
Obtain Contents Of Stored Procedures
Hi all,I need to be able to obtain the full contents, i.e. sql statementsinside, of all procedures in SQl Server 2000. I am using MS type 4JDBC driver. I know that from the database metadata, I could get thenames of all stored procedures and their column structures. However,I did not find any thing that will help me to obtain contents of thestored procedures. Any help would be very much appreciated. Thanks:-)) Vien Nguyen
View Replies !
View Related
Search Contents Of Stored Procedures?
Hi All,I'm wondering if anyone can tell me if it's possible to search forstored procedures by their contents?I tend to leave very descriptive notes in stored procedures andthey're beginning to build up in number, so I'm wondering if it'spossible to search in Query Analyzer for stored procedures thatcontain certain words / phrases etc.Alternatively, is there a way to do a system-level select that lookssomething like the following pseudo-gibberish (actually, it's actualgibberish, I guess, rather than pseudo-gibberish):SELECT * FROM [all stored procedures] where [contents] like '%mysearch phrase%'Any help much appreciated!Much warmth,Murray
View Replies !
View Related
Contents Sort 3 Levels Of Data
I Have a table of Data (WikiData) WikiIDint ParentIDint sTitlevarchar(50) sDescriptionvarchar(MAX) There will be three levels of data imposed at the Application Layer Level 1: ParentID = 0 An Item Like Geography Level 2: ParentID = a Level 1 WikiID A sub Topic like Volcanoes Level 3: ParentID = Level 2 WikiID A bottom Topic like Pyroclastic Flows I Need a SQL statement that Will Produce the Output where The output will be produced like this: Level 1 Level 2 Level 2 Level 2 Level 1 Level 2 Level 2 I Built this but its wrong and has no order by Group by Statements Select * from WikiData where ParentID = 0 or ParentID IN (Select * from WikiData where ParentID = 0)
View Replies !
View Related
Obtaining Detail Of DTS Package Contents
I have quite a few DTS's to work through while analysing current SQL databases for a client. Problem is I can't seem to find a way in SQL (Enterprise Manager) to print the contents of a DTS package. This is quite frustrating in that I have to go an "Design" the package, then click on the properties for each step and copy and paste the contents into Notepad/Wordpad. Surely Microsoft have something to ease the pain? If anyone can help me on this one, I would be extremely grateful.
View Replies !
View Related
Config File Contents And Deployement
Hi all, I have few config files with db passwords in them. I have to change passwords daily into that(as my system is in UAT phase now). I have deployed my packages on SQL Server. My question is , do I need to deploy my packages daily after password updation, or it'll automatically taken care by the SQL Server ? Thanks in Advance syed
View Replies !
View Related
Import An XML File And Then Its Contents Into Tables
Hi Folks, One of our apps emits data out in the form of XML files that have to be downloaded. Now I need to import this XML file into a table with XML datatype column and then parse through the data of this XML and import that data into various tables. How do you think can I best accomplish this? All of this just needs to be done in SQL Server 2005. Is this possible to SSIS or a stored procedure in SQL such that I will have code the check the folder every couple of hours, and if there is a file in that folder, import the file into the XML datatype column and then parse through the file and then import the data into various tables. Response will be greatly appreciated and thanks in advance. Shubz
View Replies !
View Related
Understanding The Contents Of A Checkpoint File
Hi, I have a problem where my packahe isn't restarting from the point of failure and I'm tryig to understand why not. The following line came from the checkpoint file: <DTS:Container DTS:ContID="{36A685E1-049A-4E6C-9884-AADF8BE29577}" DTS:Result="0" DTS:PrecedenceMap="" /> Can anyone explain: What DTS:Result & DTS:PrecedenceMap are used for? What values can DTS:Result contain and what do they mean? [Today I have only seen "0".] What values can DTS:PrecedenceMap contain and what do they mean? [Today I have only seen "Y" & "".] Thanks Jamie
View Replies !
View Related
Read SSIS Package Contents Into SQL DB
Hi, We have an extensive set of SSIS packages that run daily to build our datawarehouse. We have run into a bit off a maintenance issue which I am hoping someone can help me with. The SSIS packages execute stored procedures. What I would like to know is, is there a quick way to determine what SSIS package is running a certain stored procedure? My first approach was to load the packages into a table in a database using various xml functions. Basically, the table would contain the package name, and then the stored procedures that package executes. I have run into some difficulties with this though. Has anyone tried anything similar or perhaps know of a tool which could provide similar functionality? Some help would be much appreciated.
View Replies !
View Related
How DO I Generate A Table Of Contents For A Report??
Hi!!! Can anybody help me with the generation of Table of Contents for a report using SQL Server 2005 Reporting Services. Let me ellaborate. The scenerio is...i m having a report of lets say 500 pages grouped on employees, showing the performance of each employee between specific date range. Now if the manager prints the report of 500 pages he will be more intersted to jump directly to a perticular employee's page which means my printed report had to have a Table of Contents on my grouped criteria (which in this case is employee number). I would really appriciate if someone can suggest me a solution for this.
View Replies !
View Related
Two Questions (email From SQL && Download Contents)
Hello All, Thanks in advance for any help. On the SQL Server Express 2005 download page there are two large download options: SQL Server 2005 Express Edition with Advanced Services & Microsoft SQL Server 2005 Express Edition Toolkit Is one a subset of the other? Or, are both unique? Just by reading it appears that the 'Toolkit' is an addition to the 'Express Edition with Advanced Services' ? Second question is: Is the any way to send an email(just text) based upon when an item is added to a table? From my reading it appears that Express does not include SQL Mail or Database Mail, so is there some other way to skin the cat? I found XPSMTP, but it appears to be limited to some other versions of SQL Server.... Thanks, John
View Replies !
View Related
Issue With Missing Field Contents
Hi there, I have a problem in SQL 2005, can someone help? I happened to create a new field, lets say: sSubject with type: nvarchar(50), null. then manually I go in and add this field with data and SAVE it. For some reason, few days later the apps cannot work. And I went into DB and found out that the mentioned field contents has become "NULL". What is going there in DB? Thanks....cheers
View Replies !
View Related
Write Contents Of All Xml Files In A Directory To A Database
Hi All. I am trying to put together a routine to take all the files in a sgpecified directory and map the elements to database fields. I have tried to do this with SSIS and keep getting errors over and over again regarding permissions when using XSD files. I have given up on this now and am wondering what others did to get the contents of multiple XML files into a SQL database. Cheers
View Replies !
View Related
How To Create INSERT Query For Contents Of Table
Using Sql Server 2005 Express and Management Studio I need to create a SQL insert statement for the contents of a table (FullDocuments) so that I can run the query on another server with that same table schema (FullDocuments) and the contents will automatically be inserted into the new instance of the FullDocuments table.In Management Studio I have used "Script Table as" for the create table query. The second instance of FullDocuments has been created on the remote server. Now how do I generate an insert query for the contents of FullDocuments so that the contents can be moved/inserted to the new instance of the table?Thanks for any help provided.
View Replies !
View Related
Help With Inserting Array Contents To SQL Server 2000
I've been doing this in Access, but cannot find the answer to how to do it with SQL Server. From a web form, a user can select a number of different dates. The selected dates are held as text (not DateTime) in an ArrayList. Clicking the Submit button writes the contents of the form to a database table. This works for Access: insSQL &= "VALUES (@typEvent, @starts, @ends, @starts, @ends, @attend, @title, @room, @department, @contact, @address, @telephone, @email, @telefax, " For i = 0 to datesArray.Count - 1 insSql &= datesArray.Item(i) Next i insSQL &= "VALUES (@typEvent, @starts, @ends, @starts, @ends, @attend, @title, @room, @department, @contact, @address, @telephone, @email, @telefax, " For i = 0 to datesArray.Count - 1 insSql &= "#" & datesArray.Item(i) & "#, " Next i It doesn't work for SQL Server, and when trying to insert the value "01/29/2007" I get the error message: "The name '#1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted." I have also tried the line: For i = 0 to datesArray.Count - 1 insSql &= satesArray.Item(i) Next i and get: "Incorrect syntax near the keyword 'VALUES'." I'm not sure where to find the information to correct my error. Any help would be appreciated. Tinker
View Replies !
View Related
|
TRACKER
