Is it possible to convet a string with characters and numbers to an all numeric value (e.g. '010-112d3') and then perform arithmatic on the converted value?
Here is what I need to do:
given the original value of: 01011201
I need to order a result set by asc order of the difference between the original and each individual record.
If this was all numeric I would simply:
select...,(01011201 - column1) as difference from table1 order
by difference
BUT my recordset looks like this [010-112d3, 01011202, 0a511345...] so I get
I get arithmatic overflow errors (due to the characters). Ive tried casting as numeric and binary and even converting the string to ascii value but that only works for single characters. Any Ideas would be greatly appreciated.
BTW, This search will be the most common hit on the database so a cursor is not really an option.
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
If I install SQL Server using a certain Windows Login, does that login assume some sort of "sa" alias? (I'm trying to explain the behaviour that when I login in to a server using the same username that I used when i installed the SQL Server instance, using trusted authentication, then use a database within which I haven't created any users, I seem to get full permissions, as if I had gone in as "sa")
In the scenario where you are installing MSDE/SQL Server at a customer site (not your own) is this true:
Assuming the customer has a (Windows) login with admin rights (or is a member of the admins group) over the PC which the server was installed on, there is no way of locking the customer out of any part of the SQL Server installation and its databases as Mixed Authentication (which you can't turn off) will always allow members of the (Windows) admins group to also be a member of the SQL Server admins group???
Hi All,We have started to get the strangest errors with our SQL Server. We useWindows and SQL Server authentication, and have done successfully forthe last year or so. However last week i had trouble connecting to theenterprise manager with my domain credentials. Users connecting to QueryAnalyser have no problems when using their SQL Server logins, the issueonly seems to arrise when a Domain account is provided forauthentication. If we reboot the server all is well for about 1 - 2hours and then we loose the Domain credentials again. Even SQL Agentfails to work when stated with the DOMAINAdministrator account!As i said this has worked for a long time, and we have not made anychanges to the Domain or installed any patches for any of the servers.The error messages normally involve something along the lines of "NoUser 'NULL' blah blah" and "No Trusted SQL Server". We also have anexcel report that is failing with the SQL Server error number 18452. Ihave looked this up on MSDN, and this seems to fall in to the samecategory as our other errors.Any Ideas?We can't reboot the server every 2 hours!*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
After installing SQL Server 2005 I found that I inadvertantly used Windows Authentication Mode and I would like to change to Mixed Mode (Windows Authentication and SQL Server Authentication) - what is the best way to do this? In Microsoft SQL Server 2000 you could simply go into Enterprise Manager, edit the SQL Server Registration Properties, edit the Properties of the SQL Server and edit the Properties of the SQL Server Agent.
Ok guys, I€™ve summarized doubts regarding SSIS as well as odd behaviour that sometimes I suffer for my lack of knowledge or for other reasons. So this way, I€™m a pain and bore individual only one time€¦
Question 1:
When you have a ForEach container, i.e using as enumeraton ADO and you are debugging and seeing how is going everything, whats tasks are executing and so on, you never knows if those that were in green (executed succesfully) now are executing again or not. Well, I€™ll try to explain better: In every iteration of the loop, tasks previously executed keep its colour (green) instead of turn into white.
Question 2:
Parameter Size property is a new property for Sql Task when you have installed sp2. Well, what is it for really? I mean I€™ve got some .dtsx with Sql Tasks and its values are simply -1
Question 3:
When you€™re debugging a dtsx you have in the bottom locals showing you variables and so on. Problems come when you have more than 40 variables€¦ is there any search tool? Ok, you can use
Question 4:
Outline for the tasks is lost all the time. What a nuisance! Concretely it happens in workstations running either sp1 or sp2. Amazingly it works fine in a server (2003). Memory is not related, I€™m afraid, in all the scenarios 2Gb.
Question 5:
Is it possible disallow math case for variables? Sometimes when you€™re using lots of variables, it€™s easy commit errors when you write code.
Question 6:
Assemblies 1.0/1.1 will be allow in VSA in a future??
Question 7:
Is it possible to change the scope for a variable in run-time? Devious question, I admit€¦
Question 8:
When you€™re writing a Script Task you can export the script into VB extension and then be imported from a project or whatever. What does make sense?
Question 9:
ScriptTask_32dcba2feae9428386d768f38922e162 (that€™s an example) is really stored anywhere? (When you save a script task inside the IDE) Root NameSpace is too symbolic??
Question 10:
I€™m developing against Windows 2003 english version. The whole Sql25k too. No spanish at all but sometimes I receive spanish messages related with SSIS. I don€™t understand how it can be. Nothing to do with for the fact I€™m using Terminal Server, I guess.
Hi! I have quite strange problem, that I haven't seen before. When I use update command: UPDATE categorys SET banner_valid= '0', section_id= '1', main_cat_default= '0', banner= '', b_link= '', external_text= 'NÄ?kotnes parks ', in_frontpage= '1', name = '100. pants' WHERE (id = 130) Then the field EXTERNAL_TEXT should have value NÄ?kotnes parks but instead of this it makes it Nakotnes parks. I changed collation to Latvian and this did not work. But!!! When I open Enterprise manager and just type in NÄ?kotnes parks and save it then it is ok, but it does not work with Update/add script Any help or ideas???
I would like to set 'SQL Server and Windows Authentication mode' of Sql Server 2005 so when user connects to Server he must supply username and password. Here are steps I make :
1. Open Microsoft SQL ServerManagement Studio Express 2. In Object Explorer right click on first (Server) node 3. Select 'Properties' 4. Select Page 'Security' 5. set 'Server authentication' to 'SQL Server and Windows Authentication mode' 6. press OK 7. in popup window fill password ******** and press OK 8. get error message ''operation is not valid due to the current state of the object. (Microsoft.SqlServer.Express.SqlManagerUI)
I program in C# with SQL Server 2005. I need to connect to Server in security mode.
In connection string I supply UserID = myuser Password = mypassword Persist Security Info = True
In Server I set mixed authentication mode,create login 'mylogin' with password = mypassword after that in my database I create user with username = myuser and loginName = mylogin
There are two problems :
1. in login creation after I fill password, close window and open it again there is old (default) password but not my password 2. Application fails to connect to DB
First post, so greetings!I've been using ASP.NET 2 authentication from an MS SQL 2k5 database now in the web application for my company's customer accounts (file serving, custom webreports, etc). There is currently no support for employees through this web app, but in our plan to go paperless it's become of interest to let the employees access much of their information through the same web portal. The catch is, I'd rather not make them have to use two accounts (one for their domain authentication and one for their web authentication). It's already taken a lot of conditioning to get them to memorize a single username and password.So what I really need is the single Login.aspx page to accept both ASP.NET logins and Domain digest logins, and the Domain digest login needs to be HTTP based (not named pipes). Is this possible at all, and if so, is it possible without making an isapi filter? After all, Microsoft Office Outlook Web Access is served by the exchange server and accepts active directory passwords. So how could I do authentication in this way, and if so, is it possible to have the Outlook Web Access form be accessible from the same session as that of my web server? Thanks, I surely hope someone can help. . . -Brandon
I am trying to create a graph that consists of a bar chart and line graph - both to appear on the same graph.
They will show different information, but will essentially come from the same dataset.
So far I have not been able to achieve this and it would seem that you cannot mix chart types. This is very disappointing given the plaudits associated with this product.
Can anyone restore my faith in this product by telling me that the above is possible and, if so, how do I go about it.
Hi AllI am trying to switch from Windows to Mixed mode authentication on SQLServer 2000. I am right-clicking the server instance in EnterpriseManager and selecting the correct option from the Security tab.Unfortunately, the change I make does not persist when I click OK. Iget no error message, but when I re-open the server properties, theoption has returned to being Windows authentication.Any ideas?Thanks,Kulgan.
Let's say I have a result set (records) containing numbers such as:01239162145100101102103104105106How might someone write a procedure to get the next lowest number inthis sequence? In this case it would be: 4. Some combination ofSelect, Min & When, I am sure, but it escapes me at the moment.Thoughts? Thanks...
I have a field in my database which stores product codes. This fieldis a varchar. Some users use characters in their codes but most justuse numbers. Whenever I go to sort the products by code the order ofthe numbers go something like: 1, 100, 101, 2, 210, 220, 2500, 3, ...I understand why this is sorted this way but is there anyway I canhave it sort the numbers in their proper numeric order (1,2,3,100,....) and just have them always appear before any codes that containcharacters? Right now I just have users put zeros in front of theirnumbers like: 0001, 0100, 0101, 0002, ...
Hi, I'm seeing confusing results coming back from a query and I want to make sure my joins are working as expected.
I have 3 tables, tbl_family, tbl_familyPhone, and tbl_phone. tbl_FamilyPhone is a linking table between families and phones that specifies if it's the primary number.
So a family has many familyPhones and a phone has many family phones. I'm trying to get all the families and their home phone only, if they have one. I don't want families to duplicate and I don't want any left out. Here's what I've got
Code Snippet select [whatever]
from tbl_family LEFT OUTER JOIN tbl_familyphone on tbl_family.pk_familyid = tbl_Familyphone.fk_familyid inner JOIN tbl_phone on tbl_familyphone.fk_phoneID = tbl_phone.pk_phoneid and tbl_phone.fk_phonetypeid = 'E6F1688E-015B-481D-8C41-DCC1FEA5D5AB'
My thinking is the inner join between tbl_Phone and tbl_FamilyPhone will cause any FamilyPhone record without a phone record to be left out (fk_phonetypeid is the id of a home phone). Though some FamilyPhone records may be left out, I will not lose any families because it is left outer joined to familyphone. Is this right? Because if I just do select count(*) from families I get 4517 records, but when running the query above with the joins, I get 4383 records.
Currently in production we have a publisher, remote distributor, and subscriber all running 2000.
We want to upgrade the subscriber and possibly the remote distributor to 2005 while leaving the publisher 200 for now, i need a good link or article about the proper proceudre to accomplish this.
I am trying to create a query that can determine if a user id is using mixed mode/windows/both authentication. I need to do this so that it can run on both sql server 2000 and 2005, meaning I can't use any of the sys.* views. Is there a single query could use for both systems? -Kyle
I have a mixed CLR assembly (including native C++ and manage C++). Can I use this assembly in SQL SERVER 2005 or 2008? I know pure managed assemblies work fine. Thanks.
I have a question for ya’ll. Our company plans on migrating to active directory soon. I would like to use mixed mode for security. My question is, will this work? Can SQL 6.5 pick up on Active directory or is this handled at the OS layer?
We are upgrading a production database server to new hardware. The server is currently running SQL Server 2000 Standard Edition. We are thinking about installing SQL Server 2000 Enterprise Edition, however that would mean the test server (2000 Standard) and production server (2000 Enterprise) have different edtions of SQL Server. How much of a risk does this present? Later in the year we would upgrade test to SQL Server 2000 EE, but for a couple of months the environments would be different.
I am trying to convert all my client first and last names in my table to uppercase. They are currently listed as mixed case. Also I wanted to know what is the best way to force the data to UPPERCASE hwen a end user tries to insert or update the clients name. I am thinking about trying a trigger, but I am unsure how to set it up. Thanks for all the help.
Yeah, it's pretty simple. Maybe it'll help someone out.
-- USAGE: fn_extract_chars(string_to_search, 'letters' -or- 'numbers') CREATE FUNCTION fn_extract_chars (@x varchar(128), @y char(7)) RETURNS varchar(128) AS BEGIN DECLARE @chars varchar(128) DECLARE @pos int DECLARE @action varchar(32) SET @pos = 0 SET @chars = ''
IF @y = 'numbers' SET @action = '[0-9]' ELSE IF @y = 'letters' SET @action = '[a-zA-Z]'
WHILE @pos < (DATALENGTH(@x) + 1) BEGIN IF PATINDEX(@action,SUBSTRING(@x, @pos, 1)) > 0 BEGIN SET @chars = @chars + (SELECT SUBSTRING(@x, @pos, 1)) END SET @pos = @pos + 1 END RETURN(@chars) END
Hi there, can anyone tell me how to setup or enable mixed mode authentication in SQL 2005? also is it possible to transfer views or stored procedures from 1 database to another? If so where do I go to do this. I thought a DTS may do it but cant find the correct option in the Management Studio to do this.
Su writes "I'm trying to use a stored procedure to dynamically update a table whenever other staff in other departments update their do any changes to their databaseds. and thanks for your web site taught me how to pass table names as parameters. But I still have problems withe sql command. You have an example in your article ('dynamic sql 2'), showing how to do a sql SELECTION using a table name and a local variable. But the sql command only use a local variable of varchar type. I'm trying to do INSERT with local variables with different data types. For example:
CREATE PROCEDURE KPISU_F_TotalByF @inputT_From varchar(10), @inputT_To varchar(10), @TableName varchar(1000) AS ----------------------------------------------------- --------input variable------------------------------- DECLARE @inputTerm_From varchar(10), @inputTerm_To varchar(10), @sql_empty varchar(2000), @sql_refresh varchar(2000) ---------------------------------------------------- IF EXISTS (select * from tempdb.dbo.sysobjects where id LIKE object_id('tempdb..#tmpOTLTotalByF')) DROP TABLE #tmpOTLTotalByF
Server: Msg 245, Level 16, State 1, Procedure KPISU_F_TotalByF, Line 256 Syntax error converting the varchar value 'INSERT KPISU_F_OTLTotalByF06 VALUES (14-19 Academy, 2005, ' to a column of data type int. -----------------------------------------------------------
I guess I could change all the columns in the table to data type of varchar. But are there any other way to solve this problem?
Hi, I have setup reportserver with default settings (non https) and afterwards in IIS added a certificate and https support. Know I am able to work really well and secure over the internet, but some links in report manager pointing to http://server.domain.com/reports instead of https: For example if i call a report and then in the top there are (view, properties,...) - View is HTTPS - Properties is HTTPS - History is HTTP - Subscription is HTTP
I have read some articles - mentions I should configure https, but - in Report Configuration you could not setup HTTPS - in rsreportserver.config i have entered the https link version but did not change anything - in RSWebApplication.config the Reportserverurltag is blank - so i did not change anything
I need help changing the authentication mode from Windows Authentication to Mixed Mode (for Project Server 2003). I am unable to find the Enterprise Management area. Evidently in this area, I can make this change. Is this the place to make these changes, or is there a better place? I am a novice, only days into this process. I need to setup a Project Server, and it needs Sql to breathe. Please help me!!
We have recently migrated quite a databases around 20 from SQL 2000 and 2005 to SQL server 2008R2.
I am using Ola's script for index maintenance for those with compatibility level above 80 as i heard it supports that way.
Hence separated in 2 way job where for those with compatibility level 80, we are running job with below query on each database with 80 as compared
USE ABC GO EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GO EXEC sp_updatestats GO
I am not sure if this is the only way in for those databases, because we are seeing the database getting because of that somewhere using above query.( seems log file filling very rapidly).
But above is not the case with those databases , with compatibility 90 and above.
:: REGEDIT:::Â HKEY_LOCAL_MACHINESoftwareMicrosoftOffice14.0Access Connectivity EngineEnginesExcelTypeGuessRows ::TypeGuessRows value to zero (0) IMEX=1 Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:destination.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1";
But SQL Table Last 39 Records Dumped as NULLÂ whichever is Alphanumeric. Why? Dynamically How Can I import without doing Text to column in Excel on that column ?
