I'm attempting to move data from a text field to a numeric field, but I need to be able to remove any non-numeric characters in that field first, but I do not want to lose the numeric data.
In the code below, the Value table is the table that will be converted.
INSERT INTO IntegerValues (FK_ReferenceID,FK_ReferenceType,FK_FieldID,FieldProperty,Value,CreationDate,CreatedBy,Revision)
SELECT FK_ReferenceID,FK_ReferenceType,FK_FieldID,FieldProperty,Value,CreationDate,CreatedBy,Revision
FROM StringValues WHERE FK_FieldID in (select id from ##tmpFields)
Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!
Still new to SQL and having fun learning it. Here's my latest question.
I have a table 'Fields' that has a column named 'label'. In label, I have values like 'Total Number of Orders' I want to replace the text Number with a #. How can I do that for all records that contain 'Number'?
I am unfamiliar with the built-in functions in SQL Server. I would like to display a numeric field with leading zeros. In oracle the sql stmt would be:
As part of a data search project I need to be able to strip all non numeric characters from a text field. The field contains various forms of phone number in various formats. In order to search on it I am going to remove all non numeric characters from the input criteria and from the data being searched.
In order to do this I decided on using a SQL Server custom function: Pass in field. Loop through all chars, test against asci values for number range. return only numernic data concatenated into a string.
Are there any other more efficient ways of going about this?
I am looking for the fastest way to strip non-numeric characters from a string.
I have a user database that has a column (USER_TELNO) in which the user can drop a telephone number (for example '+31 (0)12-123 456'). An extra computed column (FORMATTED_TELNO) should contain the formatted telephone number (31012123456 in the example)
Note: the column FORMATTED_TELNO must be indexed, so the UDF in the computed column has WITH SCHEMABINDING.... I think this implicates that a CLR call won't work....
Hi to all, I am having a string like (234) 522-4342. i have to remove the non numeric characters from the above string. Please help me in this regards. Thanks in advance. M.ArulMani
Hi to all, I am having a string like (234) 522-4342. i have to remove the non numeric characters from the above string. Please help me in this regards. Thanks in advance. M.ArulMani
I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.
Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)
I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:
An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).
I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.
The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.
The only thing that changed is that there was this fresh install of SQL Server Express.
I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.
So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.
Any suggestions would be appreciated.
Thanks!
EDIT----TRIED SOME THINGS:
Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...
Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.
So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.
EDIT --- TRIED SOME MORE THINGS:
I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.
I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.
Any pointers on this last piece would be helpful.
EDIT - MORE INFO
The error details
[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
I'm getting the above when trying to populate a variable. The values in question are : @N = 21 @SumXY = -1303765191530058.2251000000 @SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right When I try the following ie to populate a variable with that value I get the error - SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message. How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Hi everybody, I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
I am using a function to fill a listbox in asp.net If I run it using the sql statement in the code, it works. if I use the sp I get an error saying the sp expects @strBookingNo which wasn't supplied. I have stepped through the function and there is a value in txNewBookingNo.text. Here's my function - the part commented out is the part that works,but I'd rather do it through an sp. I've included my sp too
Function GetFacilitiesBookings()
Dim MySQL As String = "spRB_GetFacilitiesBookings" Dim MyConn As New SqlConnection(strConn) Dim Cmd As New SqlCommand(MySQL, MyConn) Cmd.CommandType = CommandType.StoredProcedure Cmd.Parameters.Add(New SqlParameter("@strBookingNo", CInt(Me.txtNewBookingNo.Text))) Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQL, strConn) Dim ddlDataSet As New DataSet
'strSQL = "Select * from tblRB_FacilitiesBookings where FB_BookingNo = " & CInt(Me.txtNewBookingNo.Text) & " and FB_Cancelled =0" 'Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, strConn) 'Dim ddlDataset As New DataSet 'ddlDataset.Clear()
I have an sp which I should return the latest date in my sql table. Howver I am getting this error 'Implicit conversion from data type datetime to bigint is not allowed. Use the CONVERT function to run this query. ' Here's my sp and my code which calls the sp.
CREATE PROCEDURE spRB_MaxDate ( @MaxDate datetime OUTPUT ) AS SELECT @MaxDate =MAX(BD_DateRequired) FROM tblRB_BookingDates
RETURN GO
Here's how I call it
Dim MySQL1 As String = "spRB_MaxDate" Dim myConn1 As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim Cmd1 As New SqlCommand(MySQL1, myConn1) Cmd1.CommandType = CommandType.StoredProcedure
'Add the parameters
Dim outParm As SqlParameter = New SqlParameter("@MaxDate", 0)
I have installed SSRS and created a .net application... now when I try to open the report viewer, I get the error " the permission granted to user 'domainuser' are insufficient for performing this operation. (rsAccessDenied)" I have searched the net hi and low and cant seem to find an adequate answer... something to do with roles or permissions? I have assigned permissions to this user on the ssrs database, yet I still cant seem to view the report. It works fine in my test environment (as it usually does ) any help on this would be greatly appreciated as to be honest, I dont really know what im doing here.
I am selecting from two tables, with a one-to-many relationship.
Select t2.* From Table1 t1 inner join Table2 on t1.id = t2.id where t2.Field2 in (1,3)
There are times when there are 2 or more entries in table2 that relate to table1. I want to only display 1 of these entries, (either will do), I just dont want to display all.
Hi we recently depoyed SSRS 2005 on two servers along with our web application.
the extranet setup
IIS Web server (app server) in DMZ linked to database server sat behind our corporate firewall. SSL installed on IIS server. SSRS app is on the IIS, with its db on the main database server behind corproate firewall.
we have a simple link going from our app to the reporting services link
however when we enter a login (incl admin) it fails with the message 'The remote name could not be resolved: "ourdomain.com"
Hi, I am pretty new to harcore ASP.NET and .NET in general but I know the basics of the language and stuff like that. Basically I used to be a hardcore object pascal Delphi developer doing Windows Applications but have now moved to .NET world. The reason I posted this question here is I couldn't find any other specific place to ask so here it goes. To get me started with good programming source and practices I downloaded the ASP.NET TimeTracker Starter Kit and after some modificaion in connection string (using SQL 2005 Developer edition not the Express one) I have managed to make it work. Things done in there are pretty interesting way by using a delegate to retrieve data and present it using databound controls. As much simple as it sounds I am more of a fan of not using too many databound controls except in dropdown box, list box and stuff like that. So basically I need to develop DataAccessLayer (DAL) in such a way that it's useful for both Windows and Web application. So with my research I found that I should be using DataSets in the DAL because they are serializable and thatz what Web Applications & Services love over using SqlDataReader. Other advantage of using DataSet is to use ForEach syntex over While Loop in SqlDataReaders. So below is some code I extracted from the TimeTracker Starter Kit to get me started. I also noticed that the return result is a list array of certain object, in this case Category. So does it mean that it can be used in frontend using ForEach syntex? How can I convert the code below to use DataSets over SqlDataReader? Private Delegate Sub TGenerateListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef tempList As List(Of T))
Public Overrides Function GetAllCategories() As List(Of Category) Dim sqlCmd As SqlCommand = New SqlCommand() SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETALLCATEGORIES)
Dim categoryList As New List(Of Category)() TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)
Return categoryList End Function
Public Overrides Function GetCategoryByCategoryId(ByVal Id As Integer) As Category If Id <= DefaultValues.GetCategoryIdMinValue() Then Throw New ArgumentOutOfRangeException("Id") End If
Dim sqlCmd As SqlCommand = New SqlCommand() AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, Id) SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYID)
Dim categoryList As New List(Of Category)() TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)
If categoryList.Count > 0 Then Return categoryList(0) Else Return Nothing End If End Function
Private Sub TGenerateCategoryListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef categoryList As List(Of Category)) Do While returnData.Read()
Dim actualDuration As Decimal = 0 If Not returnData("CategoryActualDuration") Is DBNull.Value Then actualDuration = Convert.ToDecimal(returnData("CategoryActualDuration")) End If
Dim category As Category = New Category(CStr(returnData("CategoryAbbreviation")), actualDuration, _ CInt(returnData("CategoryId")), CDec(returnData("CategoryEstimateDuration")), CStr(returnData("CategoryName")), _ CInt(returnData("ProjectId"))) categoryList.Add(category) Loop End Sub Hope this makes sense. Sorry if I have posted this in a wrong section..! Cheers,Nirav
Ultimately I want to be able to filter every field that is listed. However, for now I am just learning this stuff and would like to just filter the by the name in the "offense" field.
However, it is not binding the selection thus not filtering it out. Do you see any problems in my code?
SELECT dbo.Job.CompanyJobId, dbo.Item.UnitOfMeasure, dbo.Job.Name, dbo.Job.ChangeDate, dbo.Region.CompanyRegionID, dbo.Job.Active, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material, sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
FROM dbo.job inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.Item.UnitOfMeasure = 'TN' and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, fMeasure, dbo.Region.CompanyRegionID, dbo.Job.Name, dbo.Job.ChangeDate, dbo.Job.Active
Now I need to inser another join like this:
dbo.Event ON dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid INNER JOIN
and I get error:
Server: Msg 1013, Level 15, State 1, Line 15 Tables or functions 'dbo.Event' and 'dbo.Event' have the same exposed names. Use correlation names to distinguish them.
I am reading over a job description and I am unsure of that the employer is refering to when they say "Instance Parameters".
Here is the context that it is used in. * Tune and optimize SQL statements, Instance Parameters, and Data Storage including the identification of performance related issues.
You can't have but one default contraint in a DB named df_revisiondate Default getdate()
I thought that sense they were being created on different Tables that it would not matter. It was a way to maintain a type of consistency by giving them the same name. However, it SEEMS to have caused all my tables to crash.
Let say i have 3 tables. Let's name it Main, table2, table3. in table Main, there are 4 colums(M_id,M_name M_tbl2, M_tbl3). In table2 and table3, each has 3 colums. For table2 the colums are id, tbl2Name, tbl2Desc and for table3(id, tbl3Name, tbl3Desc).
M_tbl2 will refer to id in table2 and M_tbl3 will refer to id in table3. How am i going to get M_id, M_name, tbl2Name, tbl3Name in one query?
I am creating a view in SQL using the View tab in the database and adding the table and the fields I need. For criteria I need to have 28 'or' s . How do I insert more columns on the end of my view ?
