hi..i'm new in sql progaming,
i try to make make a query that in table field "match" return to "1"
if no member record in another table and return to "0" if there is any
record member :
ex
table member:
member id
A 12
B 14
Table Incoming.
member note match
C bla..bla 1
A bla..bla 0
D bla..bla 1
...... ....... .....
I created a function to call my CLR Object (See below). I keep getting the following error (failed because T-SQL and CLR types for return value do not match) no matter what data type I try in the function. The Dll is passing back a string data type. What am I doing wrong?
CREATE FUNCTION [dbo].[fnGenerateTheoreticalValue]
(@LiborSpread float,
@Maturity int,
@LGD float,
@OID int,
@ForwardEDF1Yr float,
@ForwardEDF2Yr float,
@ForwardEDF3Yr float,
@ForwardEDF4Yr float,
@ForwardEDF5Yr float,
@LIBOR float)
Returns nvarchar(Max)
AS
EXTERNAL NAME [SQLCLRTheoretical_Values].[SQLCLRTheoretical_Values.Theoretical].[CalcValue]
I am trying to create a CLR function to call a webservice, the CLR function return data type is double, whether I try to create this as a table valued funcion or a scalar to return a distance travelled value I am receiving the error below.
I've tried changing data types around in the CLR side and the SQL side but keep receiving the same error message:
[Microsoft.SqlServer.Server.SqlFunction(Name = "DistanceCalc")] public static Double DistanceCalc(Double SrcLat, Double SrcLong, Double DestLat, Double DestLong) { MileageWS ws = new MileageWS();
[Code] ....
Error received when try to Create function ... 1, Level 16, State 2, Procedure pcMiler, Line 6 CREATE FUNCTION for "pcMiler" failed because T-SQL and CLR types for return value do not match.
I need some store procedure script , from two table i want to pickup matching field and insert into main table ,give some sample store procedure coding...
If 1st table as cheque no and 2nd table as cheque no - matching then data would be inserted in to main table using two table...
I have a customer who wants to SELECT records based on a partial match in atext field. For example, in a list of telephone numbers they want to searchfor all records that contain the digits '777' in any part of the string. Howdo I formulate such a query?Many thanks.
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
If I have a column named "Login" in a SQL Table (I am sharing with another application) that I am using a stored procedure to acquire the information from, how can I trranspose its name to match code already written in a Web App to get the data. There is a web app already created that has the followig code to get the data from the database Dim strSQL ast string = "UsersSelectCommand" intLoginID = objDataReader("LoginID")
My stored procedure is the following: CREATE PROCEDURE UsersSelectCommand/* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT )*/AS Select Lastname, FirstName, Login from Users Order by LastName GO The stored procedure will return "Login" instead of "LoginID" that I am wanting. How can I modify the Stored Procedure to change the LoginID to Login.
Right this has to be a Micro$oft mess-up surely...?I'm running SQL 2k standard with SP3. I have a table which I'm tryingto query using a LIKE operator on a varchar field as follows....WHERE dbo.tbl_pm_projects.SeniorManagerID LIKE '%'....In actual fact the % is passed in by the application when the userselects "All managers" from the drop down list used to select theManager to filter by. If they select a manager's name from the listit becomes LIKE 'ajames' or whatever.BUT - the table currently contains 2972 records. If I take out theWHERE clause the SELECT returns all records - fine - but if I put thewhere clause in it returns only 1682!! I thought the % was meant tomatch, and I quote the SQL server Books Online files here; "Any stringof zero or more characters." Anyone explain to me whats going onhere?TIANiall
Could anyone help of how to match the exact characters in a data field in SQL 2005 Developer. For example, if one has a password "GooD", then when he or she enters "GOOD", "good", etc, the database will not match the password. And he or she must enter the exact characters, which is "GooD". Thanks.
Could anyone help of how to match the exact characters in a data field in SQL 2005 Developer. For example, if one has a password "GooD", then when he or she enters "GOOD", "good", etc, the database will not match the password. And he or she must enter the exact characters, which is "GooD". Thanks.
Howdy all,I'm wishing to write a stored proc to return only the field names from a table. What I've tried gets the field names but also returns all of the data in each row. I only want the field names. Is this possible?Thanks!JP
I have a field in a table that has sql statements as follows; 'Low_Quote_Mortgage_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 10),'-','') + '.txt'
I am trying to create a proc/view to return the data in an executed form ie; Low_Quote_Mortgage_021108.txt
I tried using a function ;
create FUNCTION [dbo].[DynamicFileConversion] ( @dynamic_filename VARCHAR(100) ) RETURNS VARCHAR(100) AS BEGIN DECLARE @Static_filename VARCHAR(100)
SET @Static_filename = (@dynamic_filename)
RETURN @Static_filename END
SELECT suptype_id, oen.dbo.DynamicFileConversion(REPLACE(dynamic_filename, '%day%', 'GETDATE()')) AS Link FROM abc.dbo.ondemand
Didn't work...the only way i can think to do this is by first inserting the data into a temp table then using a cursor to go through each record and linking it back to the table ...just seems really inefficient anyone know a better way?
I am in need of help to develop a query I have two tables Exams and Exams_lab, that are joined by a field id_exame. I want to return The Exams that has all the dependent rows in Exames_lab with the same value in the status_int field of Exames_lab. Can anyone Help Me? Lets see an example tb_exame
id_exame date_exame
1 07/01/2006
2 08/01/2006
3 09/01/2006 tb_exame_lab
id_exame_lab id_exame desc_exame status_exame
1 1 NORMAL 1
2 1
0
3 2 NORMAL 1
4 2 PROBLEMS 1
5 2 OK 1
6 3 OK 0 in this exemple my query must return only id_exame 2 and 3 because id_exame 1 has two different values on id_status on tb_exame_lab can anyone help me?
how do you un-salt the password field and return it ? and how can you view the password field inside sql server management studio express in the un-salted form ?
I am using a monitoring system where I can monitor a numeric SQL result assuming the result is one field and one row.I would like to do this to say monitor the free available space or percentage on say the Master database. DBCC SQLPERF gives me a few columns and results for all databases on the server.
SELECT table1.col1 ,€™n/a€™ _response FROM table1 INNER JOIN table2
This is the query that get the report data for my report. Now I need to replace the second column with an actual response like €˜accepted€™ and €˜rejected€™. And these values should be a result of evaluation of this form
If(Statusarray.count < 1) Set _response = €˜accepted€™ Else Get Statusarray[1] Compare this to Statusarray[0] Set _response = some result based on comparision.
The _response returned in the query should return the actual response based on this evaluation where ResponseStoredProcedure is sent the current row value for table1.col1.
How can this be done in sql(using SQL Server 2005)
I have a table in my MS SQL 2000 database called News which has a field caled NewsDate. This is a standard Date field which stores the info in this format: 3/1/2001.
I want to create a query that returns one row for each year that there is a story.
For example, if I had this data... 3/1/2001, 6/27/2003. 9/17/2003, 1/1/2006, 4/5/2006
the query would return this result:
2001 2003 2006
This is the query I've started with:
SELECT DISTINCT NewsDate FROM News ORDER BY NewsDate DESC
What modifier can I apply to the NewsDate field to extract JUST the year from the table? If this were ASP I would try something like Year(Date), but, of course, I can't do that here.
Is this even possible? I've been looking up date functions, but haven't found anything that will work in a select statement. ANY and ALL advice will be greatly appreciated.
I recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.
That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?
Code: create table #nullTest ( name varchar(50) ,revenue int)
INSERT INTO #nullTest Values ('Tim',100) ,('Andrew', 50) ,(null, 200)
SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
Code: SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'
Is there a way to return the fields collection of a dataset using the Reporting services Web Service? I can get to the reports but not the datasets. Thank you in advance for any support you can provide.
how can i insert a carriage return when i update the field?
say i want to put the following inside a field: firstline secondline
how can i update/insert a column to have a return carriage inside it? UPDATE table SET column = 'firstline secondline'
the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines
i tried doing UPDATE table SET column = 'firstline' + char(13) 'secondline'
but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline
I am trying to write a user defined function that will allow me tostrip off the last carriage return and line feed from a text field.We have address fields stored in a text field for our ERP system andsome of them have an extra carriage return and line feed at the end ofthem. This causes havoc when we sync between our ERP system and CRMsystem. If anyone knows a way to solve this problem the help would beappreciated.Examples:Existing Text field with CR:1234 Blah Street<CR>Suite 2345<CR>Corrected Text field:1234 Blah Street<CR>Suitr 2345
I have a problem, I store text into a nvarchar field (could be a 200 or 20,000 characters long string), inside the text there are several carriage returns which I would like to preserve to later presentation, but when retreiving the data from sql server I got the "cr" as "?", also I opened the database from Sql Managment and all cr's were saved as "?".
What can I do to preserve the cr inside each field ?
I get this error when I look at the state of my SQLresults object. Have I coded something wrong?Item = In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user. conn.Open() Dim strSql As String
I have table "Clients" who have associated records in table "Mailings" I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting. I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below: I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
