SuperbHosting.net have generously
sponsored
dedicated servers to ensure
a reliable and scalable dedicated hosting
solution for BigResource.com.
|
|
Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang
View Complete Forum Thread with Replies
Related Forum Messages:
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all, I executed the following sql script successfuuly: shcInLineTableFN.sql: USE pubs GO CREATE FUNCTION dbo.AuthorsForState(@cState char(2)) RETURNS TABLE AS RETURN (SELECT * FROM Authors WHERE state = @cState) GO And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database. I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script: shcInlineTableFNresult.sql: USE pubs GO SELECT * FROM shcInLineTableFN GO I got the following error message: Msg 208, Level 16, State 1, Line 1 Invalid object name 'shcInLineTableFN'. Please help and advise me how to fix the syntax "SELECT * FROM shcInLineTableFN" and get the right table shown in the output. Thanks in advance, Scott Chang
View Replies !
Table-Valued Function Result Vs. Calculation Table
I need to return a table of values calculated from other tables. I have about 10 reports which will use approx. 6 different table structures. Would it be better performance wise to create a physical table in the database to update while calculating using an identity field to id the stored procedure call, return the data and delete the records. For Example: DataUserID, StrVal1,Strval2,StrVal4,IntVal1,IntVal2,FloatVal1... Or using a table-valued function to return a temp table as the result. I just dont know which overhead is worst, creating a table per function call, or using a defined table then deleting the result set per sp call.
View Replies !
Handling Large Queries In A Table Valued User Defined Function
Hello, We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions . What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out. Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran: Sec / Actual Rows Returned 0 0 10 237 20 447 30 481 40 585 50 655 60 725 70 793 80 860 90 940 100 1013 110 1081 120 1115 130 1151 140 1217 150 1250 160 1325 170 1325 180 1430 190 1467 200 1502 210 1539 220 1574 230 1610 240 1645 250 1679 260 1715 270 1750 280 1787 290 1822 300 1857 310 1892 320 1923 330 1956 340 1988 350 1988 360 2022 370 2060 380 2094 390 2094 400 2130 410 2160 420 2209 430 2237 440 2237 450 2274 460 2274 470 2308 480 2342 490 2380 500 2380 510 2418 520 2418 530 2451 540 2480 550 2493 560 2531 570 2566 It took 570 seconds (just over 9 1/2 minutes to return 2566 rows). The minute breakdown during my trial is as follows: 1 = 655 (+ 655) 2 = 1081 (+ 426) 3 = 1325 (+244) 4 = 1610 (+285) 5 = 1822 (+212) 6 = 1988 (+166) 7 = 2160 (+172) 8 = 2308 (+148) 9 = 2451 (+143) As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution. Allow me to explain the code in further detail: [SqlFunction(FillRowMethodName = "FillListItem")] public static IEnumerable DiscoverListItems(...) { ArrayList listItems = new ArrayList(); SPToSQLService service = new SPToSQLService(); [...] DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service //Load the DS to the ArrayList return listItems; } public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...) { ArrayList item = (ArrayList) obj; col1 = item.Count > 0 ? (string) item[0] : ""; col2 = item.Count > 0 ? (string) item[1] : ""; col3 = item.Count > 0 ? (string) item[2] : ""; [...] } As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once. Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set. Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions. Thanks, Dragan Radovic
View Replies !
Table-valued User-defined Functions
Is there a way to execute a table-valued user-defined function within SSIS - one that no only returns a "table" of values but also passes in variables to the udf? Example of it in query form: DECLARE @Var1 INT SET @Var1=ID FROM dbo.dbtable WHERE Status = 1 SELECT * FROM udf_Foo(@Var1) Thanks.
View Replies !
User Defined Functions, Passing Parameters From Another Udf's Results (end Result=Crosstab)
Hi All:I've read a whole slew of posts about creating temp tables using storedproceedures to get the crosstab ability, but I'm wondering if, for thisspecific case, there might be a more efficient way.What makes this question different from the others that I've read isthat I'm using user defined functions, not tables. I actually thinkthat I've got the crosstab thing down, it's just passing the parameterto the 2nd udf that's messing me up.I've got a people table and an address table. Each person can havemultiple addresses. I need to create a dataset that has in each rowthe name of the person, the first address, any second address, and anythird address. I only need to show the first 3, so if there's 100, Ican just ignore the rest.I created a user defined function to return the 1st, 2nd, or 3rdaddress for a given person.udf_ReturnAddress(PersonID,MatchNumber)Another user defined function returns the people that I'm looking for(potential duplicates for a person in this case).udf_ReturnPossibleDupsForAPerson(PersonID)SELECTMain.FoundPersonID, Main.LastName, A1.Street, A2.Street,A3.StreetFROMudf(ReturnPossibleDupsForAPerson(@PersonID) MainTableCROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,1) Adr1) A1CROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,2) Adr2) A2CROSS JOIN(SELECT Street1 FROMudf_ReturnAddress(Main.FoundPersonID,3) Add3) A3If, for the first parameter for the return address function, I replaceMain.FoundPersonID with the ID of a person, it works just fine. Iobviously don't want a static id as a parameter - I want to use the IDof the person that the first udf found. Leaving the variableMainTable.PersonID there causes an error in the query designer though.I get "Error in list of function arguments: '.' not recognized.So maybe my problem is that I just don't know how to pass the id of theperson that's found by the first UDF as the parameter of the functionto find the found person's 3 addresses.Any guidance would be greatly appreciated!ThanksKen
View Replies !
CASE Function Result With Result Expression Values (for IN Keyword)
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function. WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END ) I kept on getting errors, like Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
View Replies !
Using A Scalar Valued Function As A Parameter Of A Table Valued Function?
Ok, I'm pretty knowledgable about T-SQL, but I've hit something that seems should work, but just doesn't... I'm writing a stored procedure that needs to use the primary key fields of a table that is being passed to me so that I can generate what will most likely be a dynamically generated SQL statement and then execute it. So the first thing I do, is I need to grab the primary key fields of the table. I'd rather not go down to the base system tables since we may (hopefully) upgrade this one SQL 2000 machine to 2005 fairly soon, so I poke around, and find sp_pkeys in the master table. Great. I pass in the table name, and sure enough, it comes back with a record set, 1 row per column. That's exactly what I need. Umm... This is the part where I'm at a loss. The stored procedure outputs the resultset as a resultset (Not as an output param). Now I want to use that list in my stored procedure, thinking that if the base tables change, Microsoft will change the stored procedure accordingly, so even after a version upgrade my stuff SHOULD still work. But... How do I use the resultset from the stored procedure? You can't reference it like a table-valued function, nor can you 'capture' the resultset for use using the syntax like: DECLARE @table table@table=EXEC sp_pkeys MyTable That of course just returns you the RETURN_VALUE instead of the resultset it output. Ugh. Ok, so I finally decide to just bite the bullet, and I grab the code from sp_pkeys and make my own little function called fn_pkeys. Since I might also want to be able to 'force' the primary keys (Maybe the table doesn't really have one, but logically it does), I decide it'll pass back a comma-delimited varchar of columns that make up the primary key. Ok, I test it and it works great. Now, I'm happily going along and building my routine, and realize, hey, I don't really want that in a comma-delimited varchar, I want to use it in one of my queries, and I have this nice little table-valued function I call split, that takes a comma-delimited varchar, and returns a table... So I preceed to try it out... SELECT *FROM Split(fn_pkeys('MyTable'),DEFAULT) Syntax Error. Ugh. Eventually, I even try: SELECT *FROM Split(substring('abc,def',2,6),DEFAULT) Syntax Error. Hmm...What am I doing wrong here, or can't you use a scalar-valued function as a parameter into a table-valued function? SELECT *FROM Split('bc,def',DEFAULT) works just fine. So my questions are: Is there any way to programmatically capture a resultset that is being output from a stored procedure for use in the stored procedure that called it? Is there any way to pass a scalar-valued function as a parameter into a table-valued function? Oh, this works as well as a work around, but I'm more interested in if there is a way without having to workaround: DECLARE @tmp varchar(8000) SET @tmp=(SELECT dbo.fn_pkeys('MyTable')) SELECT * FROM Split(@tmp,DEFAULT)
View Replies !
Problem Passing UDF Scalar Result To UDF Table Function
I'm having difficulties invoking a user defined table function,when passing to it a parameter that is the result of anotheruser defined function.My functions are defined like so:drop function dbo.scalar_funcgocreate function dbo.scalar_func()returns intbeginreturn 1endgodrop function dbo.table_funcgocreate function dbo.table_func(@p int)returns tablereturn (select @p as id )goGiven the above, I can do the following:Select from the scalar function works:1> select dbo.scalar_func() as scalar_result2> goscalar_result-------------1Selecting from the table function works, if i pass aconstant value (or a variable)1> select id from dbo.table_func(1)2> goid-------------1But, if I try to pass the table function the return valueof the scalar function in one call, it doesn't work,producing the following error:1> select id from dbo.table_func( dbo.scalar_func() )2> goMsg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '.'.What am I missing here?Thanks kindly
View Replies !
User Defined Function: Convert String Value Of Table To Table Object
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
View Replies !
Verifying That A Task Completed Successfully
How can a tell if a tak completed succesfully from a stored procedure? I have a task which is executed from a stored procedure. The sp_runtask only returns whether the task started successfully. How can I tell if it completed successfully? Thanks
View Replies !
Completed Successfully, But Not Sending And No Error.
hi, i have a message queue system using sql 2005 service broker. the code and setup is the same on both dev and live database. but soon after i restored a live backup to dev. the queue stopped working on dev, live is ok thou. after some trouble shooting, i found that the server is not sending the message at all, but it says "Command(s) completed successfully" without any error messages. setup: ----------------------- create message type TestQueryMessage validation = none create contract TestQueryContract (TestQueryMessage sent by initiator) create queue TestSenderQueue create service TestSenderService on queue TestSenderQueue create queue TestQueueReceiver create service TestServiceReceiver on queue TestQueueReceiver (TestQueryContract) send message: ------------------------- declare @conversationhandle uniqueidentifier; begin dialog @conversationhandle from service [TestSenderService] to service 'TestServiceReceiver' on contract [TestQueryContract] with encryption = off; send on conversation @conversationhandle message type [TestQueryMessage] ('blah blah blah'); result: ---------------------------------- Command(s) completed successfully. but when i do "select * from TestQueueReceiver", there's nothing. and i sure nothing else had picked up the messages. please advise. thanks a lot.
View Replies !
Role Defined Wrong Grand Total Result
HI, In my cube I have defined a role where thet user can only browse certain dimention value. BUt in grand Total the result showing is for all the diemnsion values. for example The user restricted to browse only Australia and UK Country , But in grand total its showing the SUM of all the country. Any help will be appreciated. Thanks
View Replies !
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
View Replies !
Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View Replies !
Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.". Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64. Thanks!
View Replies !
[Q] Complex Output Result
Hello, I have three tables and would like to see the output result asbelow:--------------------- OUTPUT RESULT--------------------------------------index_id officeidofficenameofficecodeofficedescriptDescriptionattrcatitemiddepartmentIDdivisionID branchIDsectionIDunitID6 9 00012 02 NNNDepartment 133 666663 9 00012 05 CCCDepartment 133 333335 9 00012 03 AABDivision 134 355559 9 00012 10 jjjBranch 135 359994 9 00012 04 VVVDivision 134 344448 9 00012 08 lllBranch 135 3488810 9 00012 11 bbnSection 136 348101011 9 00012 12 vccUnit 137 348101112 9 00012 13 uuuSection 136 34812121 9 00012 09 AAADepartment 133 111112 2 00013 01 BBBDepartment 133 222227 2 00013 07 PPPDivision 134 27777--------------------- OUTPUT RESULT END-----------------------------------table relationship:A_Office.index_id = organization.officeidA_Type.index_id = organization.attrcatitemidorganization.index_id = organization.departmentIDorganization.index_id = organization.divisionIDorganization.index_id = organization.branchIDorganization.index_id = organization.sectionIDorganization.index_id = organization.unitID-------------------------------------------------------Generating tablesA_Office table Script:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[A_Office]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[A_Office]GOCREATE TABLE [dbo].[A_Office] ([index_id] [int] NOT NULL ,[officename] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL) ON [PRIMARY]GOINSERT INTO A_Office ([index_id], [officename])VALUES (1, '00011')GOINSERT INTO A_Office ([index_id], [officename])VALUES (2, '00012')GOINSERT INTO A_Office ([index_id], [officename])VALUES (3, '00014')GOINSERT INTO A_Office ([index_id], [officename])VALUES (9, '00013')----------------------------------A_Type Table:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[A_Type]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[A_Type]GOCREATE TABLE [dbo].[A_Type] ([index_id] [int] NOT NULL ,[description] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[sortorder] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO A_Type ([index_id], [description],[sortorder])VALUES (133, 'Department', 1)GOINSERT INTO A_Type ([index_id], [description],[sortorder])VALUES (134, 'Division', 2)GOINSERT INTO A_Type ([index_id], [description],[sortorder])VALUES (135, 'Branch', 3)GO------------------------------------------organization table script:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[organization]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[organization]GOCREATE TABLE [dbo].[organization] ([index_id] [int] NOT NULL ,[officeid] [int] NOT NULL ,[officecode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[officedescript] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[attrcatitemid] [int] NOT NULL ,[departmentID] [int] NOT NULL ,[divisionID] [int] NOT NULL ,[branchID] [int] NOT NULL ,[sectionID] [int] NOT NULL ,[unitID] [int] NOT NULL ,) ON [PRIMARY]GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (1, 9, '09', 'AAA', 133, 1, 1, 1, 1, 1)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (2, 2, '01', 'BBB', 133, 2, 2, 2, 2, 2)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (3, 9, '05', 'CCC', 133, 3, 3, 3, 3, 3)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (4, 9, '04', 'VVV', 134, 3, 4, 4, 4, 4)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (5, 9, '03', 'AAB', 134, 3, 5, 5, 5, 5)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (6, 9, '02', 'NNN', 133, 6, 6, 6, 6, 6)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (7, 2, '07', 'PPP', 134, 2, 7, 7, 7, 7)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (8, 9, '08', 'LLL', 135, 3, 4, 8, 8, 8)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (9, 9, '10', 'jjj', 135, 3, 5, 9, 9, 9)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (10, 9, '11', 'bbn', 136, 3, 4, 8, 10, 10)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (11, 9, '12', 'vcc', 137, 3, 4, 8, 10, 11)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (12, 9, '13', 'uuu', 136, 3
View Replies !
How Can I Output Result To A Log File?
Hi all, In case i have a script file containt tables, functions, ... when i use Query Analyzer to run this file, the result output in a window. Now i want this result output to a file named logfile.txt. How can i do that? Thanks first.
View Replies !
Problem With Output The Result
deepak bhardwaj writes "myself deepak . i have a problem with displaying output.i want to the output just like this 3 mths 6 mths 9 mths 1 yr Google Ad 8 6 3 0 Yahoo Ad, 4 6 3 3 Friend 3 45 3 1 Print Ad 2 3 3 2 here 3 mths etc. is the column name and 8,4,3,2 are the no of hits for perticuler add . plz give the solution. its urgent thanks deepak""
View Replies !
Saving Query Result To A File , When View Result Got TLV Error
HI, I ran a select * from customers where state ='va', this is the result... (29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record Thanks for your help Ali
View Replies !
End Result Is Main Query Results Ordered By Nested Result
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows: SHOWS showID showTitle SHOWACCESS showID remoteID VIDEOS videoDate showID SQL is as follows: SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC; I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong? thanks
View Replies !
Output Query Result To A CSV File
Hi, I want to export/output result of a query to a CSV file using SQL Server 2005. How can i do it ? I just want to do it all using SQL Server 2005 query window without having to use some 3rd Party control or software. Is it possible and how? Is there some SP which can convert the result to a CSV File ? Thanking you...
View Replies !
Query Result To Output File
Hi All I am using cursor in my SP, according to my condition I might be getting around 600 records all this records to send to text file, I have tried few option some how I am able to create file and I am geeting only the last record in my output file, I want to know how can I append records into output file(text file), If some one can give me some suggestion that will be great. Thanks in advance Regards Ram
View Replies !
Query Result To Output File
Hi All I am using cursor in my SP, according to my condition I might be getting around 600 records all this records to send to text file, I have tried few option some how I am able to create file and I am geeting only the last record in my output file, I want to know how can I append records into output file(text file), If some one can give me some suggestion that will be great. Thanks in advance Regards Ram
View Replies !
How Do I Write An Output Parameter (result) When..
..calling a stored procedure.SQL I am using the C programming language... I have two input variables and one output...Not sure how to do the output BEGIN EQUIPMENT_UTILITIES.MOVE_EQUIPMENT(EQNUM, MoveTo, ?); END; This is in a CSTRING format.... Thanks. Sherin
View Replies !
Output Result Of Query To Existing File
Hi,I need to output result of my query to txt file. So I'm using -oparameter, for example:osql.exe -s (local) -d database1 -U sa -P sa -i 'c:\queryFile.sql' -o'c:\output.txt'But it clears existing output.txt file first and then outputs theresult, while I need to append the result of my query without clearingexisting file content. Is it possible ?
View Replies !
Running A Stored Procedure Using Output Result.
Hey guys! I've come a huge ways with your help and things are getting more and more complicated, but i'm able to figure out a lot of things on my own now thanks to you guys! But now I'm REALLY stuck. I've created a hierarchal listbox form that drills down From Product - Colour - Year. based on the selection from the previous listbox. i want to be able to populate a Grid displaying availability of the selected product based on the selections from the listboxes. So i've written a stored procedure that selects the final product Id as an INPUT/OUTPUT based on the parameters PRODUCT ID - COLOUR ID - and YEAR ID. This outputs a PRODUCT NUMBER. I want that product number to be used to populate the grid view. Is there away for me to do this? Thanks in advanced everybody!
View Replies !
Getting SUM Function Result
Hello,In the project I'm working on, I need to add up all rows data for onecolumn. So, I have this code:$getprodcount = mysql_query("SELECT SUM(qty) FROM purchase");$numproducts=$getprodcount;Later on, I have this code: <?php print $numproducts; ?>What is being printed is Resource id #5...not the numeric value of what issupposed to be a sum. What is wrong? I am assuming taht resource id #5 is apointer of some sorts to the number I am looking for, but how do you getthe actual sum number?Thanks in advance!--Message posted via http://www.sqlmonster.com
View Replies !
Function And One Result Set
I have an function executed like that: select top 1 * from f_Function1('XXX',2099,99) ORDER BY ef DESC I have about 400 XXX values. I execute function individually for them but normally i get results in individual result sets. select top 1 * from f_Function1('XXX1',2099,99) ORDER BY ef DESC select top 1 * from f_Function1('XXX2',2099,99) ORDER BY ef DESC select top 1 * from f_Function1('XXX3',2099,99) ORDER BY ef DESC I want all 400 results in one result set. What should i do? Thanks in advance.
View Replies !
Trying To Set Output Variable To Row Count Result Of SQL Execute Task
I am building this as an expression, but it is not working. I am trying to return the row count into a variable to use later in an update statement. What am I doing wrong? I am using a single row result set. I have one variable defined in my result set. I am receiving an error stating: Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow". Any help is appreciated! SELECT count(*) FROM hsi.itemdata a JOIN hsi.keyitem105 b on a.itemnum = b.itemnum JOIN hsi.keyitem106 c on a.itemnum = c.itemnum JOIN hsi.keyitem108 d on a.itemnum = d.itemnum WHERE a.itemtypegroupnum = 102 AND a.itemtypenum = 108 AND b.keyvaluechar = " + (DT_WSTR,2)@[User::Branch] + " AND c.keyvaluechar = " + (DT_WSTR,2)@[User:epartment] + " AND d.keyvaluesmall = " + (DT_WSTR,7)@[User::InvoiceNumber] + ")
View Replies !
Result Output To File + Parameters In Query + Rownumbers
hi i am using sql server 2000. 1. can i save the sql result to an ascii file using tsql commands. i don't want to use the menu option. 2. how can i use parameters in query statements. pls give me some exampls 3.is there any way to get the row numbers along with the query results thanks & regards Sam
View Replies !
Index On Result Of Function
I have a table with about 28 million records in it. Each row has an ID (PK), logged (datetime), IP varchar(15) The data grows at about 14 million records per year. I'm going to be running queries on the table that extract the MONTH or YEAR from the logged column. In Foxpro tables I would have created indexes on YEAR(logged) and MONTH(logged) so my queries would run faster. Is this possible/necessary in SQL Server?
View Replies !
How To Get Result In Decimal Using AVG Function
Hello Sir I am working on a Teacher Evaluation Project. In my database I store results from 1-5 as evaluation indicators. I apply AVG() function on the result column. The result of the query is in integer values (i.e) 4, 3 2 or 5. I want the resutl up to two decimal places. How can i write the query to get the result in decimal form? Shahbaz Hassan Wasti
View Replies !
Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View Replies !
[Q] Complex Output Result Again: The End Of Script Was Cutoff In The Previous Posting
Hello, I have three tables and would like to see the output result asbelow:--------------------- OUTPUT RESULT--------------------------------------index_id officeidofficenameofficecodeofficedescriptDescriptionattrcatitemiddepartmentIDdivisionID branchIDsectionIDunitID6 9 00012 02 NNNDepartment 133 666663 9 00012 05 CCCDepartment 133 333335 9 00012 03 AABDivision 134 355559 9 00012 10 jjjBranch 135 359994 9 00012 04 VVVDivision 134 344448 9 00012 08 lllBranch 135 3488810 9 00012 11 bbnSection 136 348101011 9 00012 12 vccUnit 137 348101112 9 00012 13 uuuSection 136 34812121 9 00012 09 AAADepartment 133 111112 2 00013 01 BBBDepartment 133 222227 2 00013 07 PPPDivision 134 27777--------------------- OUTPUT RESULT END-----------------------------------table relationship:A_Office.index_id = organization.officeidA_Type.index_id = organization.attrcatitemidorganization.index_id = organization.departmentIDorganization.index_id = organization.divisionIDorganization.index_id = organization.branchIDorganization.index_id = organization.sectionIDorganization.index_id = organization.unitID-------------------------------------------------------Generating tablesA_Office table Script:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[A_Office]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[A_Office]GOCREATE TABLE [dbo].[A_Office] ([index_id] [int] NOT NULL ,[officename] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL) ON [PRIMARY]GOINSERT INTO A_Office ([index_id], [officename])VALUES (1, '00011')GOINSERT INTO A_Office ([index_id], [officename])VALUES (2, '00012')GOINSERT INTO A_Office ([index_id], [officename])VALUES (3, '00014')GOINSERT INTO A_Office ([index_id], [officename])VALUES (9, '00013')----------------------------------A_Type Table:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[A_Type]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[A_Type]GOCREATE TABLE [dbo].[A_Type] ([index_id] [int] NOT NULL ,[description] [nvarchar] (11) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[sortorder] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO A_Type ([index_id], [description],[sortorder])VALUES (133, 'Department', 1)GOINSERT INTO A_Type ([index_id], [description],[sortorder])VALUES (134, 'Division', 2)GOINSERT INTO A_Type ([index_id], [description],[sortorder])VALUES (135, 'Branch', 3)GO------------------------------------------organization table script:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[organization]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[organization]GOCREATE TABLE [dbo].[organization] ([index_id] [int] NOT NULL ,[officeid] [int] NOT NULL ,[officecode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[officedescript] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL ,[attrcatitemid] [int] NOT NULL ,[departmentID] [int] NOT NULL ,[divisionID] [int] NOT NULL ,[branchID] [int] NOT NULL ,[sectionID] [int] NOT NULL ,[unitID] [int] NOT NULL ,) ON [PRIMARY]GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (1, 9, '09', 'AAA', 133, 1, 1, 1, 1, 1)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (2, 2, '01', 'BBB', 133, 2, 2, 2, 2, 2)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (3, 9, '05', 'CCC', 133, 3, 3, 3, 3, 3)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (4, 9, '04', 'VVV', 134, 3, 4, 4, 4, 4)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (5, 9, '03', 'AAB', 134, 3, 5, 5, 5, 5)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (6, 9, '02', 'NNN', 133, 6, 6, 6, 6, 6)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (7, 2, '07', 'PPP', 134, 2, 7, 7, 7, 7)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (8, 9, '08', 'LLL', 135, 3, 4, 8, 8, 8)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (9, 9, '10', 'jjj', 135, 3, 5, 9, 9, 9)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (10, 9, '11', 'bbn', 136, 3, 4, 8, 10, 10)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (11, 9, '12', 'vcc', 137, 3, 4, 8, 10, 11)GOINSERT INTO organization ([index_id], [officeid],[officecode],[officedescript],[attrcatitemid],[departmentID],[divisionID],[branchID],[sectionID],[unitID])VALUES (12, 9, '13', 'uuu', 136, 3, 4, 8, 12, 12)
View Replies !
Multi-line File Output From Single Row Result Set Using ISQL
Is there a way to create a multiple line (records)flat file from ISQL? I basically need to break each column from a single row result set into a new line on the output file. Also, any message text, like the rowcount should not be included. I tried writing the output by selecting the ascii values CR% to force a line breaks between columns when the file is read. I don't have any tools available except for the SQL and NT. I also don't have permissions on the xp_cmdshell, so I am attempting this using ISQL from a .bat file. I can get a stored proc created if necessary. one of my many failed attempts example: isql /U user /P pw /S server /d database /r1 /h-1 /o c:empfile.txt /Q"set nocount on" -Q"select column1, 'CR%' from table where column - value" I realize that ISQL is not a text editor - so any suggestions would be appreciated.
View Replies !
SQL Query - Using Result Of Create Function
I created a function that will return from OpenDataSource('.....') tablename where ... is fully populated. However, I can't figure out how to use it? For example select functiona (parameter) as data_src this returns the "from" statement above I then try to run select * data_src So how do I reference the contents of data_src in the select? Thanks for any help
View Replies !
How To Return The Result Of An EXEC From A Function
Hi, I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute. This is my function: ALTER FUNCTION [dbo].[ReturnPickItemValue] ( -- Add the parameters for the function here @TypeID int, @CaseID int ) RETURNS varchar(max) AS BEGIN -- Declare the return variable here DECLARE @RTN varchar(max) IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @TypeID) = 1 BEGIN SET @RTN = 'SELECT PickItem I + CASE D.IsStartDateEstimated WHEN 0 THEN CAST(StartDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar) END + CASE D.IsEndDateEstimated WHEN 0 THEN CAST(EndDate as varchar) ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar) END FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND P.CaseID = ' + CAST(@CaseID as varchar) END ELSE BEGIN SET @RTN= 'SELECT I.PickItem FROM TBL_LU_PICK L INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID WHERE L.PickTypeID = ' + CAST(@TypeID as varchar) + ' AND CaseID = ' + CAST(@CaseID as varchar) END RETURN @RTN END Each time I try " RETURN EXEC(@RTN) " or something similar I get an error. I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error. Anyone with any ideas?
View Replies !
SQL Server 2005 JDBC Driver Output Parameter/Result Set Issue
I'm having an issue with the JDBC driver when I execute a stored procedure that both has a return value and also returns a result set. If I attempt to retrieve the return value (registered as an output parameter) after I execute the stored procedure, then any subsequent attempts to retrieve the result set always return null. Is this by design? If I use the result set first and then later get the return value that works; however, in my situation I need to first check the return value before I work on the result set. Am I'm I doing something wrong? Code: CallableStatement cs = connection.prepareCall("{? = call spGetCustomer(?, ?) }"); cs.registerOutputParameter(1, Types.INTEGER); cs.setString(2,"blahblahblah"); cs.setBoolean(3,false); cs.execute(); int retVal = cs.getInt(1); ResultSet rs = cs.getResultSet(); // Always returns null, even though the SP actually returns a result set.
View Replies !
Aggregate Function For Select Statement Result?
Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls. currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do. Problem: i need to call an aggregate count on the results of another aggregate function (sum) with a group by. *ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid. thanks, Tom Anderson Software Engineer Custom Business Solutions
View Replies !
Table-Valued Function
I am new to writing table-valued user defined function, so this might be a 'Duh' question. I am trying to write a table-valued UDF that has to return multiple rows. How do I do this? Thanks Mangala
View Replies !
Strange Result - Minus Result -1
help strange result whan i do this Code Snippet SELECT unit_date, unit, ISNULL(NULLIF ((unit + DATEDIFF(mm, GETDATE(), unit_date)) % 4, 0), 4) AS new_unit FROM dbo.empList i try to get next unit value to next month why i get this -1 on date 01/01/2008 1 -1 unit_date unit new_unit 01/02/2008 2 1 01/02/2008 1 4 01/01/2008 1 -1 01/02/2008 1 4 21/01/2008 1 -1 21/01/2008 1 -1 01/02/2008 1 4 TNX
View Replies !
How To Use A Function To Format And Display Result From Data Reader
Hi guys n gals ! I am having a few problems manipulating the results of my data reader,To gather the data I need my code is: // database connection SqlConnection dbcon = new SqlConnection(ConfigurationManager.AppSettings["dbcon"]); // sql statement to select latest news item and get the posters name SqlCommand rs = new SqlCommand("select * from tblnews as news left join tblmembers as members ON news.news_posted_by = members.member_idno order by news.news_idno desc", dbcon); // open connection dbcon.Open(); // execute SqlDataReader dr = rs.ExecuteReader(); // send the data to the repeater repeater_LatestNews.DataSource = dr; repeater_LatestNews.DataBind(); Then I am using: <%#DataBinder.Eval(Container.DataItem, "news_comments")%> in my repeater.What I need to do is pass the "news_comments" item to a function I created which will then write the result. The code for my function is: // prevent html public string StripHtml(string data) { // grab the data string theData = data; // replace < with &alt; theData = Regex.Replace(theData, "<", "<"); // return result return theData; } But I am having problms in doing this,Can anyone point me in the right direction on what I should be doing ???
View Replies !
Converting Result Of Aggregate Function Calculation To A Double (C#)
I've put a SelectCommand with an aggregate function calculation and AS into a SqlDataSource and was able to display the result of the calculation in an asp:BoundField in a GridView; there was an expression after the AS (not sure what to call it) and that expression apparently took the calculation to the GridView (so far so good). If I write the same SELECT statement in a C# code behind file, is there a way to take the aggregate function calculation and put it into a double variable? Possibly, is the expression after an AS something that I can manipulate into a double variable? My end goal is to insert the result of the calculation into a database. What I have so far with the SelectCommand, the SqlDataSource and the GridView is shown below in case this helps: <asp:GridView class="gridview" ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="lbsgalDataSource"> <Columns> <asp:BoundField DataField="Formulation" HeaderText="Formulation" SortExpression="Formulation" /> <asp:BoundField DataField="lbs" HeaderText="lbs" SortExpression="lbs" /> <asp:BoundField DataField="gal" HeaderText="gallons" SortExpression="gal" /> <asp:BoundField DataField="density" HeaderText="density" SortExpression="density" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="lbsgalDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT a.Formulation, SUM (a.lbs) AS lbs, SUM ((a.lbs)/(b.density)) AS gal, ( ( SUM (a.lbs) ) / ( SUM ((a.lbs)/(b.density)) ) ) AS density FROM Formulations a INNER JOIN Materials b ON a.Material=b.Material WHERE Formulation=@Formulation GROUP BY Formulation"> <selectparameters> <asp:controlparameter controlid="DropDownList1" name="Formulation" propertyname="SelectedValue" type="String" /> </selectparameters> </asp:SqlDataSource>
View Replies !
Passing MS SQL2005 Query Result Into Javascript Function
I'm selecting the last latitude & longitude input from my database to put into the Google maps javascript function. This is how I retrieve the longitude: <asp:SqlDataSource ID="lon" runat="server" ConnectionString="<%$ ConnectionStrings:LocateThis %>" SelectCommand= "SELECT @lon= SELECT [lon] lon FROM [location] WHERE time = (SELECT MAX(time) FROM [location] where year < 2008)"> </asp:SqlDataSource> I wish to input the latitude & longitude into the JAVASCRIPT function (contained in the HTML head before the ASP) something like this: var map = new GMap2(document.getElementById("map"));var lat = <%=lat%>;var lon = <%=lon%>;var center = new GLatLng(lat,lon);map.setCenter(center, 13); However, lat & long do not contain the retrieved result but rather a useless System.something string. How do I assign the retrieved results to these variables and port them over to Javascript as required? Many thanks!
View Replies !
Removing User Defined Functions And What Does This Output From SQL Profiler Mean?
I have a stored procedure that is currently utilizing a user defined function. I need to use these functions because I'm returning one row with general info (IE: Name, Title, etc), and within that same row I return all the fiscal years associated to the item as a single field in the result set. For example I would create the following SQL statement that calls the function below it. So for one row the data could look like "John Smith, Senior Developer, FY08 FY09 FY10". The function essentially concatenates multiple rows and returns a single VARCHAR with all of the FY's. Is it possible to acheive the desired functionality without using a User Defined Function here? This leads me into my next question. I notice in the SQL Profiler that any stored procedure that contains a User Defined Function actual runs the Select statement more than once. What I mean is for example say the SELECT statment below returns one row. The particular result has 3 Fy's associated to (John Smith, Senior Developer, FY08 FY09 FY10). In the SQL Profiler I would see the select statement listed 3 times, one time for ever fiscal year. The only event I am capturing is "SP:Starting". So my profiler window will look similar to the following: SP:Starting SELECT Name, Title, dbo.fnGetFiscalYears(ID) AS 'Fiscal Year' ..... SP:Starting SELECT Name, Title, dbo.fnGetFiscalYears(ID) AS 'Fiscal Year' ..... SP:Starting SELECT Name, Title, dbo.fnGetFiscalYears(ID) AS 'Fiscal Year' ..... SELECT Name, Title, dbo.fnGetFiscalYears(ID) AS 'Fiscal Year' FROM myTable1 CREATE FUNCTION fnGetFiscalYears 2 ( 3 @ItemID int 4 ) 5 RETURNS VARCHAR(100) 6 AS 7 BEGIN 8 DECLARE @FY VARCHAR(100),@sep VARCHAR(3) 9 SET @sep=', ' 10 --COALESCE all the FY's together to return them as 11 --a single VARCHAR 12 SELECT @FY = COALESCE(@FY + @sep,'') + 'FY' + 13 CONVERT(VARCHAR(2), FYID) 14 --FYID is an INT datatype corresponding to the last two digits of a year (EX: 8) 15 FROM tblItemsToFiscalYears 16 WHERE ItemID = @ItemID 17 18 RETURN @FY 19 END 20 21 I appreciate ANY help one may be able to give me...
View Replies !
|
|
TRACKER
