Getting Webservice Results Into A View
Mar 25, 2008
We have a large number of applications that use various sql databases. They need to validate and do lookups of information such as employees and addresses. Currently, we load data from oracle into our main sql server and push the data out to various servers. This could perhaps be simplified with replication, but is now done with some dts packages.
We are wondering if there could be a better way to do this. We would like to go directly against oracle for the address data and to sql for the employee info.
An idea I had was to have our programmers create a web service that could be called from our .net applications. That works fine for in house applications, but some vendor applications need to see a table or view. We can write a CLR proc that can connect to the web service and provide data in table format I believe.
How could we get this into a view? I've tried creating a function that calls the proc and returns the table-valued variable to the view, but it seems the functions doesn't like using 'exec myProc' in the select_statement parameter.
I suppose we could have them make an additional connection to the lookup information, stored on a central database...they are probably doing this anyway to get to the validation db on the same server.
View 5 Replies
ADVERTISEMENT
Nov 15, 2004
I have a table in a database that has very old and not very relational and I want to create a quick view to show the information in a better way. Let's say that the table has 4 fields : id , child1, child2, child3. I want to create a view from this table that will show two fields : id and child. So, my table currently looks like this:
id child1 child2 child3
1 sam bob chris
and i would like it like this......
id child
1 sam
1 bob
1 chris
Can anybody help me? Thanks in advance,
Bob
View 3 Replies
View Related
Mar 10, 2006
Sigh, probably simple, but somehow I just can't get it to work..
I have a complex view which generated about 9000 results, and I use
a SP to select certain results from that view. How can I modify the
results of the SP? for example I want to add a zero to every single
companynumber the SP gives me...
*it's friday, i know*
Any help would be appreciated!
/Erwin
View 1 Replies
View Related
Jul 20, 2005
Bit of an obscure one here, so please bear with me. I have two copiesof a database which should be identical. Both have a complex viewwhich is identical. I can open the views and the data is as expectedand match. I can query it in several ways as detailed below. The 5thversion of the simple query below based on the second copy of the viewfails, but works under the first copy./*1 Statement below works*/SELECT *FROM AgentHierarchyWHERE AdviserId = 6069819/*2 Statement below works*/SELECT *, AH.AdviserLastName, AH.AdviserFirstNameFROM AgentHierarchy AHWHERE AdviserId = 6069819/*3 Statement below works*/SELECT *, AH.AdviserLastName + ', '+ AH.AdviserFirstNameFROM AgentHierarchy AHWHERE AdviserId = 6069819/*4 Statement below works*/SELECT AH.AdviserLastName + ', '+ AH.AdviserFirstNameFROM AgentHierarchy AH/*5 Statement below fails*/SELECT AH.AdviserLastName + ', '+ AH.AdviserFirstNameFROM AgentHierarchy AHWHERE AdviserId = 6069819The error I get is to do with conversion of data within the view. It'sa little complex, but the view works fine. It looks to me like when Irun the 5th statement above, it re-runs the view and then finds anerror.So, I took the complex view and ran that with the data output into atemporary table with the queries above run against that, and it worksfine. The problem is that the statement I need is based around the 5thone above (part of an update statement).I'm struggling to understand why some of the queries above work andone doesn't. If you look at 3 and 5 I'd expect them both to fail. Ifit failed consistently I could get further into it.The problem is that it's a little difficult to get the view itselfchanged as it was supplied by a third party, but if it hasn't changedand the data hasn't changed then it's got to be something else causingthe problem.Anyway, as I said, it's a bit obscure, but if this sounds familiar I'dbe interested in your opinion.Thanks in advance.
View 4 Replies
View Related
Nov 4, 2014
I have a view over 5 tables that has started giving unreliable results. There are three records that should be different, but in a production Access database, the view is giving three identical records where there should be three unique records. I have tested the view within SQL Server Management Studio and it gives the correct records there. But, I have attached this same view into the same Access database with two separate names. One instance of the view within Access database gives the correct records, and the other gives the incorrect (duplicated) records. I have attached screen shots that show these two separately named incarnations of the same SQL View, with the duplicated data, or the unique data highlighted.
I have also included the SQL query specs for this view.
what I can do to this view in order for it to always give us the unique records that we need, rather than sometimes the correct records, and sometimes the incorrect records.
Correct 3 records:
Incorrect 3 records:
[URL]
View 4 Replies
View Related
Sep 10, 2007
I have 2 Table
Authors
ID Name
1 Clint
2 Voke
Books
BookID ID BookName Price
1 1 Book1 10
2 1 Boo21 12
3 2 Book3 6
4 1 Book4 13
5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.
So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).
I want to Create NEw Views Which Shows my required Results.
thanks,
View 15 Replies
View Related
May 9, 2005
I have a sproc in my database that when editing in VS 2003 shows different results.
The sproc code is:
ALTER PROCEDURE dbo.VMUsage_GetRaw
@VMBox nvarchar,@StartDate datetime,@EndDate datetime
AS
SET NOCOUNT ON
SELECT *FROM VMRawUsageWHERE (ACCOUNT = @VMBox) AND (CONVERT(datetime, DATE + ' ' + TIME) > @StartDate) AND (CONVERT(datetime, DATE + ' ' + TIME) < @EndDate)
When I open the SQL statement in the designer and run (and enter my parameters) I get a recordset returned, but when I just "Run Stored Procedure" and enter the same parameters I get no results. The same occurs when I run the sproc from my website (no results).
Any ideas what is happening between the two?
View 1 Replies
View Related
Apr 29, 1999
Wendy,
I do not know if you can see my reply after yours. Any way I try it here again.
In the DTS Export Wizard, after select the source (SQL Server)and destination (Excel),
I have 2 choices:Table copy or query.
When I use query, I can place my sp there, and the export works fine exactly as you recommended.
Thanks a lot.
But when I selected a table copy, I was given a "Select Source Tables" popup form, I can
see all the table names there, but can not find any views in the database. I do not know why.
Am I in the right place this time?
Thank you very much for your quick help.
Charlie
View 1 Replies
View Related
Aug 7, 2014
I have created a view thats pulling data from two different tables to combine them into one report.
table 1 lists the client code and table 2 lists the client partner and they're linked by a variable.
When running the report the result shows the client codes with their respective partner however any client codes that didn't have a partner are not displaying in the report and I need all client codes to be displayed even if there's no partner.
Is there a way I can make this display all results and if the client partner doesn't exist for it to still display as 'Null' for the partner but still display the client code?
Script:
SELECT TOP (100) PERCENT C.cltCode AS ClientCode, C.cltSortName AS SortName, C.cltTerminationDate AS [Term date], dbo.vcltAttrib6.ainTVal AS Department,
C.objInstID AS ClientID
FROM dbo.cdbClient AS C INNER JOIN
dbo.vcltAttrib6 ON C.objInstID = dbo.vcltAttrib6.ainObjectInstID
GROUP BY C.cltSortName, C.cltTerminationDate, dbo.vcltAttrib6.ainTVal, C.objInstID, C.cltCode
ORDER BY ClientID
View 2 Replies
View Related
Dec 21, 2005
Hello,I am trying to create a view that shows the followingField1: Sum of Amounts from Table AField2: Count of Amounts from Table AField3: Sum of of Amounts from Table BField4: Count of Amounts from Table B......Field3: Sum of of Amounts from Table HField4: Count of Amounts from Table H......Things are a bit more complex but this is the gist.I am using SQL 2000.I know how to do this pretty easily using a stored procedure. But howcan I do it in a view? A SQL server won't meet my needs in thissituation.I tried OpenQuery ('myserver', 'exec myprocedure') but get the messagethat my server is not configured for data access. I tried the systemstored procedure to set data access to true but nothing seemed tohappen.I also tried Select * from (Select Statement1, select statement2)but got syntax error at the comma between statement1 and statement2.Trying to use select Statement1 as ABC to does not seem to work either.Is there a way to do what I want without making 15 views and then afinal view that shows them all together? I know I could probably dosomething by creating a ton of functions, but it really seems thisshould not be that hard...I am definitely open to any easy suggestions!Thanks,Ryan
View 3 Replies
View Related
Sep 17, 2006
Hi All,I have two tables, one is about member infomations, the other is thecatergoriesmember_info(id,name,email,phone)member_categories(id,category)how can create a view like this (id, name, category1, category2,category3) with high performance?Thanks in advance.Joshua
View 2 Replies
View Related
Mar 4, 2007
somebody help me??
View 4 Replies
View Related
Sep 10, 2007
I have 2 TableAuthorsID Name1 Clint2 Voke
BooksBookID ID BookName Price1 1 Book1 10 2 1 Boo21 12 3 2 Book3 6 4 1 Book4 13 5 1 Book5 2
Now I want to List All Authors and only show Most Expensive book Name of each Author.So I need this Fields :ID,Name,BookName,BookID,Price.
How could I Write SQL query For It (I want to show results in DB Without Using SP).I want to Create NEw Views Which Shows my required Results.
thanks,
View 2 Replies
View Related
Apr 29, 1999
Hi,
Does anyone know the method to transfer result data of a view or stored procedure
in MS SQL 7.0 to MS Access or Excel , Manually or automatically? I did not find the
right tool in SQL 7 to do so.
I notice we can transfer table from SQL 7 to MS Access / Excel through DTS. But did not
find any tool/menu to transfer result of view / stored procedure.
I am new to SQL 7. Maybe this is a silly ?. Any help will be appreciated.
Charlie
View 2 Replies
View Related
Mar 11, 2004
This is the same issue as deinfed in http://www.dbforums.com/t987543.html
I've done some additional testing and got it down to the below
SQL2000 DB
Linked Server to DB2 using client access odbc, and MS OLE DB for ODBC
Varied results depending on the row size of the views.
See the below examples.
CREATE VIEW BLHDR_TEST
AS
SELECT STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNST CHAR(25)
TOTAL ROW WIDTH (25)
SELECT * FROM BLHDR_TEST
Returns 20971 rows * 25 = 524,725
CREATE VIEW BLHDR_TEST
AS
SELECT STNSHTNAM, STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNSHTNAM CHAR(12)
STNST CHAR(25)
TOTAL ROW WIDTH (37)
SELECT * FROM BLHDR_TEST
Returns 14169 rows * 37 = 524,253
CREATE VIEW BLHDR_TEST
AS
SELECT STNADD1, STNSHTNAM, STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNADD1 CHAR(40)
STNSHTNAM CHAR(12)
STNST CHAR(25)
TOTAL ROW WIDTH (77)
SELECT * FROM BLHDR_TEST
Returns 6808 rows * 77 = 524,216
CREATE VIEW BLHDR_TEST
AS
SELECT STNCTY,STNADD1, STNSHTNAM, STNST
FROM LURCH_PARADB.S102D4LM.PARADB.BLHDR
STNCTY CHAR(25)
STNADD1 CHAR(40)
STNSHTNAM CHAR(12)
STNST CHAR(25)
TOTAL ROW WIDTH (102)
SELECT * FROM BLHDR_TEST
Returns 5140 rows * 102 = 524,280
Test #1 Returns 20971 rows * 25 = 524,725
Test #2 Returns 14169 rows * 37 = 524,253
Test #3 Returns 6808 rows * 77 = 524,216
Test #4 Returns 5140 rows * 102 = 524,280
With the similarity of the total byte count returned, I would assume that a buffer or something is being overrun, is this a configuration parameter possibly.
If I perform the select against the linked table as such
Select * (for fields list)
From LURCH_PARADB.S102D4LM.PARADB.BLHDR
I get all rows returned. The issue only exist when accessing the views that are created against the linked server.
Any thoughts or ideas are appreciated.
View 1 Replies
View Related
Sep 5, 2014
I have a view I've created which displays client sortname, partner and date added which displays 7 results.
When I add another table to this view to display the Industry it then only gives me 4 results as the other 3 results have no Industry instead of giving me the 7 results and showing the Industry column as empty for the other 3.
Is there a way I can make it show all 7 results and havethe column where the industry is empty display the other results instead of not displaying any results at all for them?
Script:
SELECT dbo.cdbClient.cltSortName AS ClientName, dbo.vcltAttrib4.ainTVal AS ClientPartner, dbo.vcltAttrib422.ainDVal AS [Date Added],
dbo.cdbAttribInst.ainTVal AS Inudstry
FROM dbo.cdbClient LEFT OUTER JOIN
dbo.cdbObject ON dbo.cdbClient.cltCategoryID = dbo.cdbObject.objID LEFT OUTER JOIN
[Code] ....
In the above script the cbdAttribInst table has the Industry column I need which is 'ainTVal'...
View 8 Replies
View Related
Feb 17, 2014
I am looking for an example of a store procedure that will run a existing view and copy the results to a table. Every time it runs the table needs to be truncated. Will run once a day.
My view is a follows
SELECT PClass
FROM mydatabase.dbo.ProductClassDes
WHERE (ProductClass <> '_RBS') AND (ProductClass <> '_EDT') AND (ProductClass <> '_BMS') AND (ProductClass <> '_PAZ') AND (ProductClass <> '_PBC')
View results need to be copied to a table tblCurrentProductClasses that will only contain one field PClass.
View 2 Replies
View Related
Mar 29, 2008
Hi,
I have a query written in SQL 2000 which returns incorrect result. The query uses left outer join and a view. I read an issue related to this in one of microsoft bug report in this article http://support.microsoft.com/kb/321541.
However, there's a slight difference in the sympton second bullet wherein instead of a expression the query returns a fixed string for one of the column value.
Although the issue mentioned in article seems to be fixed. The later one still seems to be reproducible even with Service Pack 4. However, this issue doesn't appear in SQL Server 2005.
Here's the query to reproduce this error.
Code Snippetcreate table t1 (pk1 int not null,primary key (pk1))
create table t2 (pk1 int not null,label1 varchar(10) not null,primary key (pk1))
go
insert into t1 values (1)
insert into t2 values (2, 'XXXXX')
go
create view V as
select pk1, 'ZZZZ' as label1 from t2
go
select A.pk1 as A_pk1, B.pk1 as B_pk1, B.label1 as B_label1
from t1 as A left outer join V as B on A.pk1 = B.pk1
go
This query is similar to the one mentioned in the article except that in the SELECT clause of CREATE VIEW statement I am passing a fixed value for column "label1".
I just want to confirm that this is an issue and no fix is available for this so far.
Regards,
Naresh Rohra.
View 2 Replies
View Related
Sep 21, 2015
I am building a view to be used to drill down into a Lightswitch app I'm building and to drive this I want to use a view based off the selection of that value several other values will be given to the user to choose from related to the first selection. I've created a view using the following statement:
SELECT DISTINCT TOP (100) PERCENT ARSFamily, ARS_Index
FROM dbo.csr_standards_cmsars
ORDER BY ARSFamily
 but the results come back with ALL the records of the source table (509 rows) when there should have only been 29 rows returned (the appropriate number of families or unique groups).  The index is necessary to have Lightswitch use the view as a data source.what I'm doing wrong here?
View 2 Replies
View Related
Dec 1, 2014
I can create a string into a local "@" variable. It can include a date/time, and text for my timing testing.
I need to be able to send that string to the results window where table output goes, rather than to the message window where a PRINT statement goes.
How do I do that?
View 2 Replies
View Related
Jan 8, 2007
When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results.
The control exists in the Options > Query Results > Results to Grid > Include column headings etc.
My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.
The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there.
How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set?
Thank you,
Mike
View 1 Replies
View Related
Feb 7, 2007
I have a complex view in my sql 2005 database.
The view returns a column that could be null (as the result of a left outer join).
The coulmn that is returned is an integer.
Everything works fine if I run the view from SQL 2005 Management Studio.
My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable.
Has anybody seen this behaviour before?
Any help appreciated.
Regards,
Paul.
View 2 Replies
View Related
Feb 20, 2008
Please tell me why to use a webservice? Which factor outperform webservice?
View 2 Replies
View Related
Jan 10, 2008
hi
after connecting webservice with my vs.net2005 using sqldatasource in webservice for getting data's ,i faced following errors:-
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.Data.DataSet'.
Code which i used in Webservice is as follows:-
<WebMethod()> _Public Function getData() As DataSetDim ds As New DataSet
Dim sds As New SqlDataSourcesds.ConnectionString = ConfigurationManager.ConnectionStrings.Item("HRMSConnectionString").ToString
sds.DataSourceMode = SqlDataSourceMode.DataSet
sds.SelectCommand = "select * from EpmEmployeeDetail"ds = CType(sds.Select(DataSourceSelectArguments.Empty()), DataSet)
Return ds
End Function
Thnks
mic
View 1 Replies
View Related
Jul 23, 2005
I created a custom DTS object that does its work by calling awebservice. When you create the object in DTS, it lets you set theuser/pw you want to use to authenticate to the webservice. The problemI am having is that once I authenticate to the webservice within DTS,it always uses the same authentication when contacting the service.So, for example, I drop a new instance of my object into a package. Itconnects to the webservice as me. I set up the parameters of the task,including telling it to connect as User="test". If I run it, my codesets the credentials correctly on the webservice, but the webservicestill gets called as me.If I exit out of SQL Server and go back in, and run it, it correctlyconnects as User="test". But then if I try to edit the task, and givemy own user and password, it still always tries to connect as "test".It seems whoever I initially connect to the webservice as, that is theinformation that DTS will use for the entire session.Is there any way to do what I want to do?thanks
View 1 Replies
View Related
Aug 30, 2007
I have a webservice that I would like to call from SQL Server 2005. I have done alot of testing and am pretty familiar with how CLR works and how to create assemblies in SQL. My problem is that I don't know much about .net and am not sure what kind of project I need to create in Visual Studios to accomplish calling the webservice. I've tried googling and have not found much that is of use to me.
Any help would be appreciated
Aaron
View 1 Replies
View Related
Apr 30, 2008
Hi
I need to populate a table B by selecting a from table A but for every single record selected from Table A for some of the feilds i need to pass it to a WCF webservice and do some tasks.
Can somebody show me an example of this i am completely clueless on how to invoke a WCF webservice for every single row fetched from a database.
A step by step example would be of great help.
regards
Hrishy
View 6 Replies
View Related
Apr 8, 2008
After creating a WebService using SQL Server 2005 HTTP Endpoints I can only browse to the wsdl file using localhost address and not via the machine name
For example http://MACHINENAME/SomeWebService?wsdl will ask for some authentication that can never be determined while
http://localhost/SomeWebService?wsdl will successfully generate the WSDL file
This happens on the actual machine and on all other machines in the domain which pretty much defeats the purpose of using Web Services to expose functionality.
Does anyone have any idea regarding
(a) what security features are at work here,
(b) Where are they documented
(c) How to set or use them in such a manner as to actually be able to use web services.
While investigating I have noticed that all examples conveniently use localhost and so either avoid or never address this issue.
Any suggestions appreciated.
Nadreck
View 7 Replies
View Related
Apr 25, 2008
I have noticed what seems to be a bug in Reports using an XML Webservice for a datasource.
I have the data source setup and working to query an XML webservice. All fields aren't always populated - so after the intial step that automatically builds your dataset and sets up the field names - I went in and manually added the remaining missing fields (which were blank).
Then I setup the report - dragging/dropping fields from the dataset.
I thought everything was working fine, UNTIL i noticed that IF the FIRST record contains ANY blank fields - ALL of those fields (on every record returned) show blank on the report.
For example my webservice might return something like this:
Code Snippet
<Root>
<Parent>
<Child_Name>Joe</Child_Name>
<Child_ID></Child_ID>
</Parent>
<Parent>
<Child_Name>Tim</Child_Name>
<Child_ID>123456</Child_ID>
</Parent>
</Root>
But when my report actually runs, it doesn't show any value for the second record:
My Report
Child_Name Child_ID
Joe
Tim
In this case, Tim should actually show the Child_ID value, but apparently because the first record didn't have a value - the report assumes NO RECORDS will have a value and doesn't bother printing them.
But if I run the report so that the first record DOES contain that field - then everything seems fine.
I can see that my Dataset hasn't changed - as it still contains all the fieldnames (even the ones that sometimes return empty).
Am I doing something wrong?
View 3 Replies
View Related
Feb 15, 2006
Dear,
Can SSIS package be run from WebService?
P.S. SSIS package and Webservice are located in the same computer.
Thanks much!
View 9 Replies
View Related
Jun 26, 2007
Is it possible to call/fire a method in a webservice (.asmx) from a trigger in MS SQL 2005? I would like to send out a notification to all the admins whenever a new row is inserted into a table in our db. If possible, can someone show me an example of how to?
View 1 Replies
View Related
Apr 21, 2008
Hi,
Is there any way to delete folder in reportserver using RS webservice API?
I know there is a method: DeleteItem ( Item As string )
But I tried it and it didn't work for folders.
Thanks
JY
View 1 Replies
View Related
