SQL 2012 :: How To Query Remote Procedure Or View

Sep 5, 2015

I have 2 server A and Server B(remote 3rd party) both SQL server 2012. We need to query the data from remote Server B, we are planning to use either view or stored procedure. The query contain multiple joins to fetch data from around 10 tables(minimum 10 tables is there in initial analysis may be more tables will be added in future).If we are going to access the data through VIEW then we are going to write the below query

SELECT * FROM view-name WHERE Column1 ='abc' My first question

Q1. I have a doubt whether this query will load all the data from table joined in the view and then the WHERE condition implement as in the VIEW definition we are not implementing the filter condition.


Q2. Which will has better performance a stored procedure or view.

Q3. How to call a remote stored procedure or View. linked server is not allowed.

Does the 4 part server.DB.Schema.SPorView name will work.

I had search and find about OPENROWSET/OPENQUERY does it works without linked server.

View 3 Replies


ADVERTISEMENT

DB Engine :: Remote Procedure Call Failed After 2012 Installation

May 8, 2015

We have server with Sql2008 R2 RTM 64 bit standard edition.We installed 2012 sp1 64 bit standard edition  in win2012sp1 64 bit as side by side setup.After installation, i found the below error when i opened SSCM:

Remote Procedure Call Failed:

I fixed this error by renaming the MSC file.

At event log i am observing continuously below error:

Source:        Application Error    Event ID:      1000

Faulting application name: wmiprvse.exe, version: 6.3.9600.16384, time stamp: 0x5532e9c9

Faulting module name: svrenumapi100.dll, version: 2009.100.1600.1, time stamp: 0x4bb681be

Exception code: 0xc0000005

Fault offset: 0x00000000000514e6

Faulting process id: 0x17e8

Faulting application start time: 0x01d0873d9d37c382

Faulting application path: C:Windowssystem32wbemwmiprvse.exe

Faulting module path: C:Program FilesMicrosoft SQL Server100Sharedsvrenumapi100.dll

Report Id: afe88c1-330-11e4-80c4-008786656bda

Faulting package full name: 

Faulting package-relative application

is there any other way to get rid of this error without installing a SP1 or later on sql 2008 R2 RTM version.

View 7 Replies View Related

SQL 2012 :: What Is A Remote Connection And Query

Jun 10, 2015

What is a remote connection and query ? And what is a local connection and query? If i'm on management studio on my pc connected to a SQL Server 2012 that is on a server, am I remote ? I didn't had a timeout in SSMS for the following query. Considering the remote query timeout setting is 600

USE Database_name
go

BEGIN
WAITFOR DELAY '00:11';
select * from [dbo].[Table_Name]
END;
GO

View 3 Replies View Related

SQL Server 2012 :: Creating A View Or Procedure From Dynamic Pivot Table

May 29, 2015

I have written a script to pivot a table into multiple columns.

The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.

Here is a copy of the script below

-- Dynamic PIVOT
IF OBJECT_ID('#External_Referrals') IS NULL
DROP TABLE #External_Referrals;
GO
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

[Code] ....

View 7 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

Remote Access Server Configuration Option And Remote Query Timeout?

Jun 2, 2015

- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?

- what is the impact of changing the remote query timeout (on the same page) from default value?

View 4 Replies View Related

SQL Server 2012 :: Write A Loop On Result Of First Query Inside A Stored Procedure

Jan 23, 2015

I have to write a Stired Procedure with the following functionality.

Write a simple select query say (Select * from tableA) result is

ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4

Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?

View 2 Replies View Related

SQL 2012 :: Check Query Execution Plan Of A Store Procedure From Create Script?

Jun 17, 2015

Is it possible to check query execution plan of a store procedure from create script (before creating it)?

Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?

View 1 Replies View Related

Remote View Security

Oct 21, 2007

We have a Foxpro database on our local machine and a shared sql server database on our remote machine. We have created a remove view that updates or inserts data into the sql server db using the local foxpro data.

What is the recommended way to secure these updates or inserts? We're worried that since our data is not encrypted, it is a security risk.

Thanks in advance.

View 3 Replies View Related

Remote View Question

Jul 20, 2005

I am connecting through EM to another SQL server for my website, is there a way to limit it so that I only see my database andnot all the 100's of others that are listed.Also, sorry if this sounds dumb but I applied the sp3 sevicepack and I dont know how to verify this, normallly one would go tohelp>about and see it there, there was a sp3 and a sp3a, but afriend told me to only apply the sp3, needless to say it seems slowconnecting and I would like to verify that everything is in workingorder.Thank YouReply to newsgroup as email address is bogus due to spamMichael

View 2 Replies View Related

One Remote User Can View My Report Another Cannot?

Dec 4, 2007

I have Developer's Edition of SQL Server installed on my PC. I want my users to run and view my new RS report from their PCs across the network without installing SS on their machines.

We experimented a little bit by asking one user to use these URLs...http://mymachine/reports$myinstance and http://mymachine/reportserver$myinstance) after giving her browse rights in report manager from my PC.

When she uses the first URL, she gets a popup asking for sign in info, but gets nowhere when typing in her windows sign in info. When she uses the second, she gets thru to the report choices but gets an error like...


* An error has occurred during report processing. (rsProcessingAborted)

* Cannot create a connection to data source 'xxxxx'. (rsErrorOpeningConnection)

* For more information about this error navigate to the report server on the local server machine, or enable remote errors

...after choosing my report. xxxxx is the name of the only datasource in the report.

Before we began, we also turned off 'enable anonymous access' and instead clicked 'integrated windows authentication' in IIS on my pc.

My user is on a different domain controller than I am. I asked a colleague who is on the same domain controller to try the URLs and he had no problems running my report from either. He wasnt given any explicit permissions/rights at either the reporting or db level.

Does anyone have a clue?

View 6 Replies View Related

Different Query Plans For View And View Definition Statement

Mar 9, 2006

I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?

View 10 Replies View Related

Grid And Details View Not Showing Remote Database Data

Oct 27, 2006

hi,i have an sql database on my server on the world wide web.i can make a connection  to the database in visual web developer and all the tables etc are shown in the 'database explorer' of visual web developerwhen i make the query in visual web developer it does retrieve the data froom the remote server database when i 'test query'.... so looking great!the connection string in the webconfig file is left as the default when i run the prgram on my LOCAL HOST with the inbult server that comes with visual web developer it runs fine......so.....running on my local pc it will connect to the database on my www host server and does display the data from that database.i need to change the webconfig files path to the connection  by default it is |DataDirectory|if i change this to the path i suspect is correct as this is the connection i use to the database  C:Inetpubvhostsarcvillage.comhttpdocsApp_DataTestdatabase.mdfwhen i change the path in webconfig then it stops working on my localhost and the error when i ftp it to my host is:Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. any ideas? 

View 2 Replies View Related

SQL 2012 :: Remote Access To Set Value Zero

Feb 21, 2015

Version SQL 2012.

Exec sp_configure remote access running values is "1" by default. If set the value "0", what will be impact on both server and client side?

1. Client side: remote access option value is "1", it mean to allows executing stored procedure in remote client side by using SSMS. server itself does working all SP which was passed by through application(remote application server even SQL port enabled between application DB and App servers).

Impact: value is "0" developer does not able execute SP their Client SSMS side after connecting established in DB server.

2. Server side
Impact: Does it working Linked server if value is "0?

View 0 Replies View Related

Difference Between Remote Login Timeout And Remote Query Timeout?

Mar 28, 2008

I just want to find out what the difference between a remote login and a remote query is. I'd have thought that a remote query inherently requires a remote login.

View 6 Replies View Related

SQL 2012 :: Can't Execute SSIS Package From Remote Machine

Mar 19, 2013

OS: Windows 2012, standard, 64-bit
SQL: 2012 + SP1 + CU2 = 11.0.3339

We recently decided to "break apart" our BI environment. We used to have everything on one box, DB Engine, SSIS, SSAS & SSRS. Everything has been running fine, but we now have other projects using these services, so we decided to break them apart into their own boxes.

We now have DB Engine on one Server, SSIS & SSRS on another server and SSAS on yet another server, so we now have three boxes that replaced one box. All are Windows 2012, standard, 64-bit with SQL Server 2012 + SP1 + CU2.

Since some of our SSIS packages have to access external resources, we used a domain account for it's service account. The DB Engine and SSAS box are using the default service accounts when installed. I can execute the packages fine on the SSIS server, I can even execute them via SQL Agent jobs on the SSIS box (we did install a default instance of SQL on the SSIS box), however when I try to execute a package from my laptop, it fails with the ugly "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'".

I immediately double checked my SPNs and they all looked correct for the SSIS server and the service account we are using (and we had no duplicates). I also double checked the User Rights Assignment in the Local policy editor and all the correct Rights have been assigned (Log on as a service, Bypass traverse checking, Impersonate a client after authentication).

View 3 Replies View Related

SQL 2012 :: Linked Server And Remote Login Password

Oct 7, 2014

Is there any way to find out the password for the remote login of the Linked server

View 1 Replies View Related

SQL 2012 :: Bulk Insert From Remote Folder Location

Jan 20, 2015

SQL Server 2012 running under a domain Managed Service Account. (Server A)
File located on a Windows 2012 server in a directory which has been shared to user A. (Server B).
User A is a domain account and is using his laptop, (laptop C) which is using SSMS to run a bulk insert command.

User A (Bulk Insert from laptop SSMS Client) --- > SQL Server (server A) --- > File Server (Server B)

The command fails and is returning Access denied to the file/folder share on Server B.

Running the same command on the SQL Server (Server A), the command works fine, so this is a double hop kerberos issue.

If I use a SQL Login from Laptop C, then the command works fine as the SQL Server will use the SQL's Managed service account to connect to the file share, which is set up for delegation and impersonation.

I am struggling to work out why a domain user cannot bulk insert a file from a remote location. I have checked that the user is connected with Kerberos authentication and they are. All articles seem to talk about setting up SPN's for the SQL Server so that SQL Login authentication can work over remote bulk insert, and just say to set up the file share properties properly if using a domain account.

What I am missing to allow domain accounts to bulk insert remotely, from a remote file share?

View 2 Replies View Related

SQL 2012 :: How To Copy Data From One Table On Remote Server A To B

Aug 12, 2015

how to copy a content of a table from one remote server to another,. server A does not see server B (B doesn't see A) - I cannot even ping to one from another.I do have SQL Studio installed on server C, which IT team configured to allow access to both A and B.So what I did so far is to periodically:

1. connect from the studio on server C to server A
2. run the following script on server A: SELECT * FROM A.myTable FOR XML PATH('ROOT')
3. copy the result
4. connect from the studio on server C to server B
5. to write something like

DECLARE @xmlData XML;
SET @xmlData = pasting here my result from item 3 above

6. INSERT INTO
SELECT

ref.value .....
FROM @xmlData.nodes('/myElemnet/ROOT')
xmlData( ref );

so it works. now there is a requirement to schedule this update to run periodically and I need to implement it..

View 9 Replies View Related

SQL 2012 :: How To View OR Retrieve All The Tabs

May 3, 2014

SQL query windows saves all the tabs in numerical sequence after you close each window. SO for example if you are typing query in window 1 and close it and open a new query windows it labels the new windows as SQL Query2 and so on.

What is the purpose of saving the SQLQuery windows like this? And how to retrieve all the windows.

View 9 Replies View Related

SQL 2012 :: Creating View With Calculations?

Aug 11, 2014

The view I am trying to create (crvKCLPRInsJob) is using another view (brvPRInsJob). I can provide a sample data, that's easy. I couldn't figure out how to get all the other info required.

I created the view below.
I created the following custom fields.
IncCode723Hours
IncCode748Hours
GrossEarn723
GrossEarn748

[Code]....

View 8 Replies View Related

SQL 2012 :: View And Obfuscating The Data

Sep 3, 2014

I need to create to create view on a table. Suppose if I have Firstname, Lastname & DOB in the physical table.

I need to mask the data from the original table. How can we do that?

Create view view_sample As
select ( null as Firstname, Hide as Lastname)

instead of the First name 'John', I just need to display null or something..

View 5 Replies View Related

SQL 2012 :: Partitioned View Over Two Databases

Sep 8, 2014

I have database with a large table (30 Billion rows) because it is so big I separated the data in quarterly tables and created a partitioned view (with hints for the date column) about 1 billions a quarter. (all in separated filegroups). The tables themselfes are partitioned by date again, so you slice out one day

However the full-backup of grows and grows and the mainpart of it is "old" but needed data.

So I was thinking to put the older data in a separate database (with separated backup) and then point to the table in my view.

While this is technical possible (leaving out the WITH SCHEMABINDING) I wonder what negative consequences it will have.

I already had to lose "with schemabing".

I have to use separate partioning functions - for each database its own - (partition schemas where already separated due to separated filegroups)

What about query optimization, does the optimizer care that there are two databases?

View 6 Replies View Related

SQL 2012 :: View Definition Of Objects?

Sep 8, 2014

I want to create a login with some restriction like the following...

1.I will create a login and ll mapped to a particular DB with the Database Role 'db_datarerader' only,
2.We wants to display the all objects under a DB but we don't want to provide the View Definition to that particular Login.
3.If we Deny the View definition option he can't able to see the Objects which are there under the DB.
4.So My Clear Question is we want to display the Object like tables ,Sps...etc and we don't want to allow him to view the definition of those objects....

View 3 Replies View Related

SQL 2012 :: View Giving Different Results?

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

SQL 2012 :: Grant View Definition

Nov 19, 2014

I'd like to find out whether or not people grant VIEW DEFINITION to their developers in UAT and PROD environments. My view is that a developer shouldn't be able to touch a PROD environment at all (we also include UAT as PROD), and any issue in a production environment should be investigated by a DBA and escalated to the dev if necessary.

View 1 Replies View Related

SQL 2012 :: View Resolution Order

Jun 11, 2015

I'm just wondering what is the resolution or execution order for a query like this (simplified version):

CREATE VIEW ClosedSales AS
SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99'
Select * from ClosedSales
where CustomerId = 10

The thing I want to know is:

- If first the complete dataset from the view is calculated and the the CustomerId=10 filter is applied
- Or the CustomerId=10 condition is added to the where clause in the view definition

My real case is much more complex but that is an interesting point in my performance analysis.

View 2 Replies View Related

SQL 2012 :: Clustered Indexing On View

Jun 15, 2015

I've created a table which will hold staffing data (name, grade, etc.) and any shifts that are going to be entered. I've got some test data in the table.

The base table looks like this:-

CREATE TABLE [dbo].[tbl_ForecastShifts_New](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Ward] [nvarchar](255) NULL,
[StaffBand] [real] NULL,
[StaffMemberName] [nvarchar](255) NULL,

[Code] ....

I've attached a spreadsheet with the test data from the table and the required layout on the second tab.

I've created a view which displays the data as I want it and I need to attach this to an MS Access front-end for the users to input/edit the data in the table with the use of a Access form.

The view I've created looks like this:-

CREATE view [dbo].[vw_Forecast_Staffing] with schemabinding
as
select
s.Ward
,s.StaffBand
,s.StaffName
,d1.Date1

[Code] ....

It displays all the staff members and any shifts they've been given for a 7-day period with day 1 begin supplied by the user.

The user will also supply the ward they are interested in viewing.

When I attached the view to Access it becomes read-only as it doesn't have any indexes on it.

I can't create a clustered index on the view as there are derived tables.

View 3 Replies View Related

SQL Server 2012 :: CLR Procedure Takes Ages To Pass TVP To Stored Procedure?

Jan 21, 2014

On SQL 2012 (64bit) I have a CLR stored procedure that calls another, T-SQL stored procedure.

The CLR procedure passes a sizeable amount of data via a user defined table type resp.table values parameter. It passes about 12,000 rows with 3 columns each.

For some reason the call of the procedure is verz very slow. I mean just the call, not the procedure.

I changed the procdure to do nothing (return 1 in first line).

So with all parameters set from

command.ExecuteNonQuery()to
create proc usp_Proc1
@myTable myTable read only
begin
return 1
end

it takes 8 seconds.I measured all other steps (creating the data table in CLR, creating the SQL Param, adding it to the command, executing the stored procedure) and all of them work fine and very fast.

When I trace the procedure call in SQL Profiler I get a line like this for each line of the data table (12,000)

SP:StmtCompleted -- Encrypted Text.

As I said, not the procedure or the creation of the data table takes so long, really only the passing of the data table to the procedure.

View 5 Replies View Related

SQL 2012 :: XMLA Script To Save File To Remote Drive

May 20, 2015

I am trying to run XMLA script to save .trc file to remote drive.

Anytime I try to save file it save it to the local drive where SSAS instance is installed.

Here what I am using it within XMLA script

<LogFileName>c:TraceTraceFileName.trc</LogFileName>

How can I save it to my local (c drive) or remote server

View 0 Replies View Related

SQL Server 2012 :: Restart Service On Remote App After Cluster Fail Over?

Oct 22, 2015

I need to be able to restart an application service after the SQL Cluster fails over. how to accomplish this as a SQL job?

View 1 Replies View Related

SQL Server 2012 :: How To Find Whether Object Is Used By Any SP / View

Nov 19, 2013

How to find whether an object is used by any sp , view ?

I need a query which will result the sp , view names which uses the given object name .

View 6 Replies View Related

SQL 2012 :: Get View Definition Using Modules With Linebreaks

May 6, 2014

SELECT [DEFINITION]
FROM SYS.SQL_MODULES WHERE [OBJECT_ID] = OBJECT_ID(@OBJECTNAME)

I know I can use SP_HELPTEXT to get linebreaks, The reason I want to use this is I can exclude comments section from SQL_Modules, but cannot exclude the comments section from SP_HELPTEXT.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved