Complex Math functions

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - dataware house..General. I am into starting a new dataware house project where i will be loading data from several sources to respective tables within databases.Are there any basic things which i need to set in doing this. Please let me know Tag: Complex Math functions
  - 2
    - Xml Ssis Data Flow Component? There is a table with a column that contains Xml documents. For each record from my Data Flow Source, I want to pass in the Xml document and the node to interrogate, and return the value contained in the node. Like the Crm component, this is probably one I will have to write from scratch in C#, but I would like to avoid having to create the custom component if it already exists in the public arena. Does anyone know of any Xml Ssis Data Flow Components that are downloadable for free Tag: Complex Math functions
  - 3
    - 'Identity Insert off' not detected, validation error still doesn't fix the original problem though Error: 0xC0202049 at Data Cleansing, Deal Insert [12194]: Failure inserting into the read-only column "DealID". Error: 0xC0202045 at Data Cleansing, Deal Insert [12194]: Column metadata validation failed. Error: 0xC004706B at Data Cleansing, DTS.Pipeline: "component "Deal Insert" (12194)" failed validation and returned validation status "VS_ISBROKEN". Error: 0xC004700C at Data Cleansing, DTS.Pipeline: One or more component failed validation. Error: 0xC0024107 at Data Cleansing: There were errors during task validation. Tag: Complex Math functions
  - 4
    - System.Security.HostProtectionException:The protected resources (only available with full trust) we All .... I didn't read the code very carefully, but it's likely failing at one of the calls to MsgBox in your Catch blocks. MsgBox and other UI components won't work when called under SQL Server, and are explicitly disallowed via HostProtectionAttributes under Safe and External_Access permission set. To simply report exception information for debugging you can use SqlContext.Pipe.Send. Steven Tag: Complex Math functions
  - 5
    - Ranking by One Field, Displaying Multiple Fields Looks like you have a typo in the measure - [LenderRankByVolume] is not the same as [Lender Rank By Volume] . Tag: Complex Math functions
  - 6
    - Receive "Procedure or function expects parameter..." error unexpectedly Maybe I am missing something completely obvious but as far as I can tell I aren't! I am trying to simply pass a parameter to a stored proc to return results. Easy So I thought so too... This is my stored procedure below (in it's ALTER PROCEDURE form): ALTER PROCEDURE [dbo] . [spd_Tester] -- Add the parameters for the stored procedure here @Check int AS BEGIN SELECT * FROM tblPerson WHERE PersonID = @Check END Now I know this is a SQL Server forum but I figure anyone who reads this probably does some application programming as well, so here is the code that calls this. It's in VBA using Access 2003. I figure the error is being returned from SQL Server Express so that's why I have come to this forum instead of an Access forum! I could be wrong though... Here is the code that calls this: Dim rs As ADODB.Recordset Dim cmd As ADODB.Command Set cmd = New ADODB.Command 'Run sql command With cmd .ActiveConnection = CurrentProject.Connection .CommandText = "dbo.spd_Tester" .Parameters.Append .CreateParameter("@Check", adInteger, adParamInput, , Me.txtFirstName) 'Execute, return recordset Set rs = .Execute End With Now I figure this should work. However, I get the error "Procedure or function 'spd_Tester' expects parameter '@Check' , which was not supplied." when I execute the procedure with the number 1 in the txtFirstName text box. Can someone tell me what I am doing wrong The tblPerson table has data in it, if I run the select from a query window, specifying a value for @Check, it works just fine. Thanks in advance! Tag: Complex Math functions
  - 7
    - Restore to a new database failed Hello, I have a database backup taken from SQL Server 2005. I originally had placed the backup into a file in My Documents, but when I went to locate the backup file there (C:/Documents and Settings...), the folders would not expand. So I moved the backup to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup directory and was able to select the file there. When I try to restore this file into SQL Server 2005 Express using the Management Studio, I get the following error message: Restore failed for Server 'DELL670DESKTOP\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo) ------------------------------ For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ Program Location: at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv) at Microsoft.SqlServer.Management.SqlManagerUI.SqlRestoreDatabaseOptions.RunRestore() =================================== System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\sequoia.mdf'. (Microsoft.SqlServer.Express.Smo) ------------------------------ For help, click: http://go.microsoft.com/fwlink ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476 ------------------------------ Can you please help me figure out what I am doing wrong Thanks so much, LC Tag: Complex Math functions
  - 8
    - SSIS Execution problems Could you turn on logging and try to see if you can get any information about what is happening in the runtime Thanks, Bob Tag: Complex Math functions
  - 9
    - "Report is being generated" Yes. Tag: Complex Math functions
  - 10
    - Ideal Event handling Technique mverma wrote: There is a properties of for a container named as "Fail Package on failure" if it is false then change it to "TRUE". and check package again. after this if any task in that container fail then contain flow will not move to next task. That's true, but I guess that property is there at the package level too, but the catch is that if we set the property, will the control move to the event handler to send out the mails, or end the package execution completely Tag: Complex Math functions
  - 11
    - order two by two successively, any way? I have a UDF which compare the similarity of of two strings. Now I need to use this to order a database table by similarity of all strings in a certain column. For example, the table column to order may look like this: This is sentence 1 That is sentence 2. This is sentence 3. That is sentence 4. The last sentence. The UDF, sim (@s varchar(1000), @s varchar(1000)) returns a fraction number between 0, for totally different, and 1, for identical . I need to compare the first sentence with all the other four sentences, to get the most similar one, that is the 3rd sentence. Then I need to compare this 3rd sentence with all the other three sentences, to get the most similar one to the 3rd sentence, that is the 2nd sentence. Similarly, we get the 4th sentence as the most similar sentence to the 2nd sentence. We do not really need to compare the 4th sentence with the last sentence. So, now the ordered column will look like (with the colors showing the differences): This is sentence 1 This is sentence 3 . Th at is sentence 2 . That is sentence 4 . The last sentence. The question is, how should I write the SQL query Tag: Complex Math functions
  - 12
    - From SQL Server 2005 to 2000 It would be nice if it were so simple as changing the compatibility level. The physical files are different between SQL 2000 and SQL 2005, and to my knowledge, there is no current mechanism to 'downgrade' a database. I think that you are left with my earlier suggestion about scripting out the database objects, and running those scripts in SQL 2000 to recreate a SQL 2000 database. Perhaps these links will help with scripting: DDL -Script Database to File http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx http://www.sqlteam.com/publish/scriptio http://www.aspfaq.com/etiquette.asp id=5006 http://www.codeproject.com/dotnet/ScriptDatabase.asp http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html http://rac4sql.net/qalite_main.asp DDL –Script Data to file ( Database Publishing Wizard) http://www.microsoft.com/downloads/details.aspx FamilyID=29B4FFD8-AC3A-4481-B352-9B185619A901 Tag: Complex Math functions
  - 13
    - Retrieving a datetime with a time of midnight (from a typical datetime) Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot. Tag: Complex Math functions
  - 14
    - Possible to use a Sharepoint List as a Datasource? Hi, In case you are interested we are selling a reporting services (both 2000 and 2005 version) data extension for sharepoint. This extension makes it possible to build report using sharepoint lists (including libraries). Several lists may be joined using SQL-like operators. Reports parameters may be used with the query string. An evaluation version is available on our site at http://www.enesyssoftware.com/Default.aspx tabid=56 If you prefer to do it by yourself, the article from Teun Duynstee is the way to go. Frederic LATOUR http://www.enesyssoftware.com Tag: Complex Math functions
  - 15
    - Sybase and SQLServer2005 Connection Problem Dear all, Installed sybase adaptive server Enterprize version 12.5.1 on Win2k3. Database is working fine and started on Port No 2638. While connecting to Sybase from Diver Sybase ASE ODBC Driver i am not able to connect to the database and getting error "connection time out" and "Verify Host Name and Port Number". When i create a Connection from Integration Services 2005 i can connect to the Sybase Database and retive the table name but when i try to preview the table information i get the following error. "Unable to retrieve column information from the DataSource". Do i need to any other ODBC Driver to connect to sybase database. regards, sufian Tag: Complex Math functions
  - 16
    - Canned SMSS reports How does one make the canned SMSS reports such as disk usage available from 1.) Local workstation for administrators of SQL can run then on SQL instances from the Object view of Management studio. All we see is the reports tab greyed out. 2.)From a reports server. My goal is to be able to run these reports on all of the SQL servers and not just a local instance. Do those servers need anything installed It was my thought that one should be able to highlight a server / database and run these reports. Have I errorred in my believes in this Yes, I know these seems rather basic but it seems there is a right way to make this occur and a wrong way. MDSN has yet to give up this information to me. If anyone knows the links or answers kindly post in the blog and also email me at brucehelp@yahoo.com . This link covers the discussion but does not have the methods to achieve the installation of the canned reports. http://blogs.msdn.com/sqlrem/archive/2006/05/16/SSMS_Reports_1.aspx "Did you know that there is a set of pre-made reports in Management Studio that can help database administrators gather information in many common areas There are reports for CPU, memory, disk, sessions, queries, transactions, and more baked right into Management Studio. You can access these reports in SQL Server 2005 and SQL Server 2005 SP1 by following these steps: " Tag: Complex Math functions
- Next
  - 1
    - Default parameter = current year I would like to default my first parameter, which is year, to the current year. I'm querying against a cube. I've tried setting the default =Non-queried =year(today), tried a few MDX field names, and also tried setting the current year as the default in my data set. I can't seem to get the parameter to accept a default. Tag: Complex Math functions
  - 2
    - "XML Source" xsd relative path Hi all, Is it possible in the XML Source to specify a relative path for the xsd file If not, do you have any idea why Best regards, pc Tag: Complex Math functions
  - 3
    - Creating a blank row in a table between positive & negative values I have a table that displays a bunch of data based on their percentage changes. There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%. I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this Thanks, Steven Tag: Complex Math functions
  - 4
    - SQL Server 2005 rejects remote internet connections Thank you for your response, I had a friend from another city telnet successfully to the default 1433 port. Next I stopped the MSSQLSERVER instance and had my friend telnet again, and as expected the telnet connection failed. Finally, I restarted the MSSQLSERVER instance and my friend was again able to telnet successfull. While I was reviewing the MSSQLSERVER instance logs, I noticed thousands of failed login entries from a hacker using a brute force attack with userIDs "administrator" and "mssql" However my failed attempts from the ASP.net pages do not show up in these logs. From this I gather that the MSSQLSERVER instance is reachable from the outside. Tag: Complex Math functions
  - 5
    - DTS package Again, this is an SSIS forum. For DTS questions, please see the DTS group: http://groups.google.com/group/microsoft.public.sqlserver.dts lnk=srg&hl=en Tag: Complex Math functions
  - 6
    - Unable to Create New web Site in IIS 5.1 Hi, I need to create a new web site in Internet Information Service. This can be done by opening IIS manager. In IIS Manager, expand the local computer, right-click the Web Sites folder, point to New , and then click Web Site . The Web Site Creation Wizard appears. But if i right click the Web Site Folder. only Properties and Refresh option is showing. New option is not shown. I will appreciate if any one give solution to solve this problem. I am using Windows XP with SP2. Thank you Tag: Complex Math functions
  - 7
    - SQL Server 2005 Installation On Windows Vista Home Premium Since the original thread was a bit dated and the title was a bit generic, I started my own thread entitled "Can't install Reporting Services and Full-Text Search on Windows Vista". Jen Suessmeyer, an MVP, added the following message to that thread: You should , see the blog entry from Brian Welcker: http://blogs.msdn.com/bwelcker/archive/2007/03/19/candy-apple-grey-installing-ssrs-and-windows-vista.aspx "Note that IIS7 in Vista Home Premium does not include Windows Authentication functionality so it will not work with SSRS." Sure enough, that blog entry is crystal clear. It would have been nice for the SQL Server 2005 SP2 Readme (especially the "Installation Requirements" section and/or the "Reporting Considerations for Windows Vista" section) to mention this requirement/limitation. I reviewed that document numerous times, without improvement. I've been considering upgrading to Vista Ultimate; this makes my decision easier. Tag: Complex Math functions
  - 8
    - Exception from HRESULT: 0xC0048004 Hi, there; I created a SSIS package in a ASP.NEP application which importing data from some .dbf file. "Exception from HRESULT: 0xC0048004" happened to two tables. I had a look this exception at http://msdn2.microsoft.com/en-us/library/ms345164.aspx, it says: The index is not valid. But there is no index defined in my destination table. Does anybody how to resolve it Thanks Tag: Complex Math functions
  - 9
    - Changing destination database for SSIS Package I think, you should surround each part of the SET argument in double quotes. Embedded quotes can (should ) be escaped. /SET "parameter-to-set";"value-with-\"escaped double quotes\"" So, try: /SET "\Package.Connections[DestinationConnectionOLEDB].Properties[ConnectionString]";"Data Source=(local);Initial Catalog=TestDB;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;" Tag: Complex Math functions
  - 10
    - I4 to I8 in Aggregation I'm using an Aggregation task to summarize an input file by item and week before inserting it into a SQL table. Two of the fields I'm summing, because their totals per record can occasionally exceed 32k, are defined as int (I4) instead of smallint (I2). However, the summarized total never exceeds the value an int can hold. I ran into a problem on the insert, however, with SSIS telling me it couldn't insert an I8 value into an I4 table field. I discovered the metadata for the summed totals had automatically been set to bigint (I8), and the mapping was failing. I didn't see a way to change that metadata within the Aggregation task itself, so I added a Data Conversion task to convert the totals to four-byte signed integers and enable the mapping. Was that the proper workaround Tag: Complex Math functions
  - 11
    - Pass variables to an OPENQUERY Hi all, Does anyone know if it is possible to pass variabes to an openquery statement I am trying to do something like this: OPENQUERY(LinkServer, 'SELECT column1, column2 FROM table WHERE column3 ='' + @@variable1 +'' AND column4 = '' +@@variable2 +'') Many thanks in advance, -Vicky Tag: Complex Math functions
  - 12
    - Order By ignoring the hyphen character Hi, I'm using the following SQL script to return a list of part number and the order is not what I expect. Perhaps this is a collation problem but I have no idea where to look to modify that. Thanks in advance, John select part from transactions T where (T.transdate between '20070701' and '20070705') and (T.transtype = 'ISSU' or T.transtype = 'RTRN') order by part Here is the beginning of the Transactions table create script CREATE TABLE [Transactions] ( [RecNo] [int] IDENTITY (1,1) NOT NULL, [Part] [nvarchar] (30) NOT NULL , [TransDate] [nvarchar] (8) NOT NULL , [TransType] [nvarchar] (4) NOT NULL , [FromLoc] [nvarchar] (10) , The 'Part' column is an alphanumeric field. The problem I am having is that the Order By seems to ignore the hyphen character '-' when the returned rows are ordered by the Part (which can contain hyphens in any column). Here is an example of what I get. 130909N9 130909N9 130909N9 1-480698-0 * These two should not be here 1-480699-0 * 15-423 164-07700 164-07700 164-07700 1683 I was expecting this ( and I get this in and older database ). 068-03000 068-03000 06A19956 074-03200 077-367-0 08DU08 1-480698-0 * These should be here eariler in the data 1-480699-0 100-364072 100-364072 Tag: Complex Math functions
  - 13
    - can't replicate table with column name too long (from sql server 2005 to oracle) Microsoft has already done the mapping for you. Consult http://msdn2.microsoft.com/en-us/library/ms147856.aspx for more information. Tag: Complex Math functions
  - 14
    - Logical equivalent IN operator in a Conditional Split Transformation Phil Brammer wrote: Jamie Thomson wrote: Phil Brammer wrote: No such thing. Really What about FINDSTRING() Something like the following FINDSTRING("12345,12346,12347",[PatientZip],1) Yeah, that's what I had in mind. Tag: Complex Math functions
  - 15
    - SSIS package failing If you used SNAC provider, the SQL 2005 version of this provider is not installed with SQL 2008. You can either install it from SQL 2005 disk, or change the connection manager to use SNAC 2008 (this should happen automatically in future, but this code is not in CTP yet). You do it individually by clicking on the connection and editing the ConnectionString property, or bulk edit .dtsx files with Find/Replace. Replace Provider=sqlncli.1 with Provider=sqlncli10.1 And your connections will use SNAC for SQL 2008. Tag: Complex Math functions
  - 16
    - MDX Dynamic parameter syntax I have a SSRS report that I'm passing a dynamic parameter to another SSRS report. The datasourse is a SSAS cube. Can anyone tell me the if following syntax is correct for receiveing report. I'm having trouble creating the datasource for the second report. I'm new at SSRS so any help would be appreciated. When I parse this code in management Studio I get the following An MDX Statement was expected. An MDX expression was specified. '= [SELECT NON EMPTY { [Measures]].[Qty Paid]], [Measures]].[US Cost]], [Measures]].[Qty Recd]] } ON COLUMNS, NON EMPTY { ([Division_Plant]].[Hierarchy]].[Plant Desc]].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(([ + " Parameters ! DivisionPlantDivisionDesc.Value + "[), CONSTRAINED) ) ON COLUMNS FROM [New GOLD]]) WHERE ( IIF( STRTOSET(([ " + Parameters ! DivisionPlantDivisionDesc.Value + " [), CONSTRAINED).Count = 1, STRTOSET(([ " + Parameters ! DivisionPlantDivisionDesc.Value + " [), CONSTRAINED), [Division_Plant]].[Division Desc]].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS' Tag: Complex Math functions

Pikker1981

Hi there friends,

Can any of you please advice me as to how I am going to accomplish certain mathematical functions on numbers such as working out the n-th root of a number since the mathematical functions built in don't go past the square root (2nd root)

Regards
  <![endif]-->

Re: Complex Math functions

clintz

hi,
you can use the POWER function since the equivalent exponential function of an [n'th root of x] is [x power of 1/n]..
e.g.
if you want to get the 5th root of 100, use POWER(100, 1.0/5.0)

- clintz

Re: Complex Math functions

Pikker1981

Thank you very much clintz.

Re: Complex Math functions

[rh4m1ll3] Rhamille Golimlim

you can also create user defined function (UDF) for other mathematical functions that you need.. like factorial, permutations, etc.

Re: Complex Math functions

Pikker1981

It seems i have rejoiced to early:

if i do a simple query like select power(27,1/3),
the resulting value is 1 not 3. why is that

Regards

Re: Complex Math functions

Manivannan.D.Sekaran

bcs 1/3 = 0 (INTEGER DIVISION)

So anything power 0 = 1

To overcome this use the following expression

Select Power(27.0,1.0/3.0)

Re: Complex Math functions

Pikker1981

lol of course, thanks so much once again

Regards