How To Write A Procedure That Has To Execute On A Different SQL Server

Nov 1, 2005

Can I write a procedure that should connect to a different SQL server and execute a series of SELECT statement on that SQL SERVER.Reason being is I do not have permission to create procedure on that destination server while I have permission to execute a SELECT statement.  Please clarifyThanks,

View 3 Replies


ADVERTISEMENT

Is It Possible To Write Clr Stored Procedure With Out Using Adomd Server.

Dec 5, 2006

hi,

I need to write a clr stored prodeure.i have to call that stored procedure from my prediction query.My Application is going to make use of that query to get the prediction output.

Is it possible to write clr stored procedure with out using adomd server.How?

For example:

In My Assembly i am having a function PredictPerformance(),Which is having my DMX Query.I need to execute that function from my analysis service by calling like this,

select aly. PredictPerformance() FROM [Model].

how to do this?

View 1 Replies View Related

How To Write And Execute?

Jun 18, 2008

 
Hi all,
      i want to write a stored procedure to get the user details from userprofile table based on the time schedule from another table called reminders.
   the structure of reminder table is called
       jobtype - type of job that is sending mails to user or sending mails to employee like that
       frequency - how frequenctly sending mails that is daily or weekly or monthly
       weekday    -  if the above option is weekly then we have to take which day on weekly to send mail
       dayofmonth - if the frequency option is monthly then we have to take this field for which day on month
      jobtime - At what time to send mails it may be daily or weekly or monthly
so i want to write the stored procedue based on above values to execute some task( that is getting userdetails and sending mails).
please help me.
Thanks!

View 6 Replies View Related

How Should I Write Stored Procedure And Pass It Into Sql Server Express?

Feb 28, 2008

 Hi i m using vwd2005 express and sql express.1. I want to write stored procedure in sql express using sql server management studio express. I m not sure where to write and how to execute the stored procedure in sql server management studio express. Can any one explain the steps required to achieve this? 2.And what is the difference between creating database using:-       a. right click the project in solution explorer-> add new item->SQl database(creating .mdf file)and b.clicking a database explorer->data connection->Add connection ....       thanks.  jack.  

View 4 Replies View Related

T-SQL (SS2K8) :: Write A Stored Procedure That Will Be Used Registered Server?

Mar 14, 2014

Is it possible to write a stored procedure that will be used registered server?

For example I have registered server named 'Test Servers' and I want to use it in stored procedure

View 3 Replies View Related

Transact SQL :: Write Parameter Stored Procedure In Server?

Aug 4, 2015

I am having table on which i want to fire a query on.

table structure is as follows :

Table1 

Table1ID    bigint PK
City  nvarchar(10)
FirstName  nvarchar(10)
LastName  nvarchar(10)
MiddleName  nvarchar(10)
State  nvarchar(10)
FirstName  nvarchar(10)
LastName  nvarchar(10)
LLDCode     nvarchar(20)
MMDCode    nvarchar(20)
LastModified    datetime
CreatedDateTime       datetime
LastUpdatedDateTime       datetime
SSN#  nvarchar(20)

I have to write a parameterised stored procedure where multiple values will be pass as input to SP.

E:g:  1) SSN# if no record found by SSN# then 2) Find by LLDCode OR MMDCode

This logic can be implemented using UNION to join output of 2 queries and selecting by LastUpdatedDateTime this query will return the Table1ID.

Now we will fire the query on table2 where this Table1ID as FK in Table2 and we will return the filed SSXML from Table2.How to do this ?

Table2 structure.

Table2ID PK
Table1ID FK
Name
Department
SSXML

View 9 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

Ugrent Help: How To Write Code To Execute A Batch File

Apr 15, 1999

hi, I need to run a batch file in specific time of the week. can anyone show me the code to run a batch file with both ways: window nt schedualer and ms sql server task manger.... I do appreciate your help

Ali

View 5 Replies View Related

Can't Execute Stored Procedure Of Remote Server

Sep 20, 2015

I've established linked server but can't execute stored procedure of remote server .In bother end SQL Server 2014 is using.

View 4 Replies View Related

SQL Server 2008 :: Using Execute As And Encryption In Stored Procedure

Oct 7, 2013

I am search for coding criteria I need create a stored procedure with execute as and along with encryption. How can I use the same ? My main motive is to create proc with execute as a user also at the same time I need to encrypt the same from other users seeing the code.

The below query is getting errors:

Create procedure testproc
with execute as 'user' and with encryption
as truncate table some table

View 3 Replies View Related

How Do You Execute An Oracle Stored Procedure From A SQL Server DTS Package?

Jul 23, 2005

I've tried several different way to execute a oracle storedprocedure from a DTS package but to no avail.I have a Linked Server setup which does bring back Oracle tables from theserver when I click on the Tables icon.Here's my DTS statement:exec omsd..OMS_TECO.SP_Callback_Update_Pkg(116);omsd is the linked serveroms_teco is the owner of the oracle stored procedureSP_Callback_Update_Pkg is the oracle stored procedure(116) is the parameter passed to the oracle stored procedureI put the above exec statement in a DTS Execute SQL Task using a Connectionthat I tried using several OLE and ODBC Data Sources. I can't seem to findthe right combination.Please Help!!!!!!!!--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

How To Execute Stored Procedure Differently Based On SQL Server Version

Oct 16, 2007

My product was developed for and works correctly on SQL Server 2000. However, when we upgraded to 2005, we found that certain system stored procedures were different, causing our product to break.

We can easily change our stored procedures to work in 2005, but we have a large client base, some of whom will be using each version. Our current solution is to check the version of SQL Server during installation and choose which script to use at that time in order to have an appropriate stored procedure for that version, but we are concerned about users who install our product with SQL Server 2000 and then upgrade to SQL Server 2005.

How can I make a stored procedure that will run differently depending on the version? I tried something like:


if (select charindex('2000', @@version)) > 0

begin -- SQL Server 2000


SELECT

...
FROM

...
WHERE
end
else -- SQL Server 2005

SELECT

...
FROM
...
WHERE
end

Unfortunately, the system tables I'm selecting from have different stuctures in the different versions (one example is msdb.dbo.sysjobschedules and msdb.dbo.sysschedules), and even though the code never gets into the SQL Server 2000 section on 2005, it parses the whole procedure for errors before allowing it to be saves and will not allow this.

Any thoughts?

View 3 Replies View Related

Integration Services :: Execute Several Data-flow Tasks In Parallel And Write To Single Excel File?

Jul 2, 2015

Is it possible to do? I'm getting lock violations in I try to execute several tasks in parallel.

View 4 Replies View Related

SQL Server 2008 :: Running Batch File To Execute A Stored Procedure

May 11, 2015

I have create a batch file to execute a stored proc to import data.

When I run it from the server (Remote Desktop) it works fine, but if I share the folder and try to run it from my pc, it doesn't do anything. I don't get an error, it just doesn't do anything. My windows user has admin rights in SQL. Why is it not executing from my PC?

View 9 Replies View Related

SQL Server 2014 :: Execute Stored Procedure To Update A Table / Invalid Object Name

Jan 21, 2015

I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.

BEGIN
SET NOCOUNT ON;
WITH Darin_Import_With_Key
AS
(
SELECT [pra_id]
,[pra_ClientPracID]

[code]....

View 2 Replies View Related

Execute A Stored Procedure Which Calls SSIS Packages Using A Proxy Account For Non Sysadmins In Sql Server 2005

Sep 18, 2007



Hi all,
I have a problem while i create a proxy account.The situation is like this...There is a user who has an login in to the server.He has a stored procedure which calls some on the SSIS packages and XP_cmdshell...so this stored procedure basically load some data in to the tables .So for the login in order to execute the stored procedure as he is not a Sys admin I have created a proxy account in my account as Iam an SA and then in the proxies and in principals I selected his login name and this way I have created a credential and a proxy account.

Now the problem is if he logins with his id and password and try to execute the stored procedure it gives an error message

Server: Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.


....so this mean the login is not able to see the proxy account.So what I did is I created a job and then in the job owner tab I have selected his login and then created a step with the type operating system (CmdExec) as I need to just execute the stored procedure and used the proxy account that I have created.

so I gave the command -- exec <stored procedure> --.
But this job fails and gives the error message as
[298] SQLServer Error: 536, Invalid length parameter passed to the SUBSTRING function. [SQLSTATE 42000]....

So now ....first My question is am I doing in a right way....if its right then why Iam not able to execute the stored procedure.

If there is any other way through which I can execute the stored procedure using a proxy account for the logins who are not sys admins....please do let me know.....

Thanks
Raja.V

View 2 Replies View Related

Need To Write A Stored Procedure

May 10, 2007

Hi,I need to write a stored procedure to check if the string = "web_version" exists in another string called FromCode.The stored procedure accepts 2 parameters like this:Create proc [dbo].[spGetPeerFromCode]( @FromCode VARCHAR(50)) (@WebVersionString VARCHAR(50))as   please assist. 

View 1 Replies View Related

How To Write Stored Procedure?

Jan 28, 2008

 
Hi all,
   I want to write a stored procedure for the following,
  The table have the following field,
  CategoryID, CategoryName,                    Parent_categoryID
  123             Kids Dresses                        0
  321             Kids Boys                            123
  322             Kids Baby                            123
  401             Kids Boys school Uniform     321
  501            Kids Boys Formals                321
  541            Kids Baby school Uniform      322
  542            Kids Baby Formals                322
  601            Household Textile                  0
  602           Bathrobe                               601
  603           Carpet                                  601
  604           Table Cloth                           601
 
 From the above example the categoryID acts as a parent_categoryID for some products,
 when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID,
 subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID.
 For example when i pass categoryID as 123 it should return the following subcategoryIDs
 321,322,401,501,541,542 because these are all subcategory of categoryID 123.
 How to write stored procedure for this?
 
Thanks!
 

View 2 Replies View Related

How Can I Write This Store Procedure

Jan 11, 2004

sql result:

startdate enddate price
2004-1-2 2004-1-4 677
2004-1-5 2004-1-8 900
2004-1-9 2004-1-12 2000


i want display in page this format:
2004-1-2 2004-1-3 2004-1-4 2004-1-5 ... 2004-1-12
677 677 677 900 2000

how can i write store procedure

View 1 Replies View Related

How To Write Stored Procedure

Mar 21, 2007

Hai Friends,
I heard that stored procedures can be written using two servers
how to write stored procedures by using two servers
CAN ANYONE GIVE INFORMATION ABT THIS

Malathi Rao

View 4 Replies View Related

Tell Me How To Write A Nested Procedure

Jan 14, 2008

Hi,
Please tell me how can i write a stored procedure inside another stored procedure.

With Regards,
litu

litu

View 2 Replies View Related

Tell Me How To Write A Nested Procedure

Jan 14, 2008

Hi,
Please tell me how can i write a stored procedure inside another stored procedure.

With Regards,
litu

litu

View 1 Replies View Related

How To Write Store Procedure

Dec 25, 2005

Hi,

I am new to using store procedures Can anyone please check and tell me whats wrong in it

 

create procedure Poll_Grid
@top int,
@ofset int,
@orderby varchar(50)
as

if (@orderby = "question")
 select top @ofset substring(question,1,50) question, startdate, username,categoryname from polls
 join users on polls.userid= users.userid
 join category on polls.categoryid= category.categoryid
 where pollid not in (select top @top pollid from polls order by Substring(@orderby,1,50))
 order by substring(@orderby,1,50)
else
 select top @ofset substring(question,1,50) question, startdate, username,categoryname from polls
 join users on polls.userid= users.userid
 join category on polls.categoryid= category.categoryid
 where pollid not in (select top @top pollid from polls order by @orderby)
 order by @orderby

 

I want to check if @orderby is passed as question it should fire the first query other wise last and also I want to dynamically insert the Ofset and top value for my paging require ment

 

Please help..

View 1 Replies View Related

Where Do You Write A Stored Procedure?

Mar 21, 2008

Hi, where do you write an SQL stored procedure?

View 1 Replies View Related

How Do You Write An UPDATE Store Procedure?

Oct 11, 2006

I have these 3 tables that are related to each other. I am trying to change the brand name of a product and the type of the product. How can I do this? 1 CREATE TABLE Products
2 (
3 ProductID int IDENTITY (1,1) PRIMARY KEY,
4 ProductSKU nvarchar(100) NOT NULL UNIQUE,
5 ProductName nvarchar(255),
6 ProductDescription nvarchar(MAX),
7 isInStock bit,
8 SalePrice decimal (18,2),
9 UnitPrice decimal (18,2),
10 isOnSale bit,
11 isSpecial bit,
12 isActive bit,
13 ProductRating int,
14 ProductImageBig varchar(255),
15 ProductImageMedium varchar(255),
16 ProductImageSmall varchar(255)
17 );
18
19 CREATE TABLE Brands
20 (
21 BrandID int IDENTITY (1,1) PRIMARY KEY,
22 BrandName nvarchar(255) NOT NULL UNIQUE
23 );
24
25 CREATE TABLE Types
26 (
27 TypeID int IDENTITY (1,1) PRIMARY KEY,
28 TypeName nvarchar(150) NOT NULL UNIQUE
29 );
30
31 // The store procedure I am having trouble with.
32
33 CREATE PROC SetProductsSKU_Brand_Type
34 @ProductID int,
35 @BrandName nvarchar(255),
36 @TypeName nvarchar(150),
37 @ProductSKU nvarchar(100),
38 @ProductName nvarchar(255),
39 @isInStock bit,
40 @SalePrice decimal (18,2),
41 @UnitPrice decimal (18,2),
42 @isOnSale bit,
43 @isSpecial bit,
44 @isActive bit
45 AS
46 UPDATE Products, Types, Brands
47 SET [BrandName] = @BrandName, [TypeName] = @TypeName, [ProductSKU] = @ProductSKU, [ProductName] = @ProductName, [isInStock] = @isInStock,
48 [UnitPrice] = @UnitPrice, [isSpecial] = @isSpecial, [isActive] = @isActive
49 FROM Products prod INNER JOIN ProductsBrands pb
50 ON prod.ProductID = pb.ProductID INNER JOIN Brands b
51 ON pb.BrandID = b.BrandID INNER JOIN ProductsTypes tp
52 ON prod.ProductID = tp.ProductID INNER JOIN Types t
53 ON tp.TypeID = t.TypeID
54 WHERE prod.ProductID = @ProductID
55 AND [ProductSKU] = @ProductSKU
56 AND [ProductName] = @ProductName
57 AND [isInStock] = @isInStock
58 AND [UnitPrice] = @UnitPrice
59 AND [isSpecial] = @isSpecial
60 AND [isActive] = @isActive
61 AND b.BrandName = @BrandName
62 AND t.TypeName = @TypeName
  

View 6 Replies View Related

How To Write Stored Procedure With Two Parameters

Aug 23, 2007

 How to write stored procedure with two parametershow to check those variables containing  values or  empty in SP
 if those two variables contains values they shld be included in Where criteiriea in folowing Query with AND condition, if only one contains value one shld be include not other one       
Select * from orders  plz write this SP for me thanks  

View 3 Replies View Related

How To Write A Stored Procedure In SQL Server2000?

Jan 28, 2008

 
Hi all,
 
  I want to write a stored procedure for the following,
  The table have the following field,
  CategoryID, CategoryName,                    Parent_categoryID
  123             Kids Dresses                        0
  321             Kids Boys                            123
  322             Kids Baby                            123
  401             Kids Boys school Uniform     321
  501            Kids Boys Formals                321
  541            Kids Baby school Uniform      322
  542            Kids Baby Formals                322
  601            Household Textile                  0
  602           Bathrobe                               601
  603           Carpet                                  601
  604           Table Cloth                           601
 
 From the above example the categoryID acts as a parent_categoryID for some products,
 when i pass a categoryID the stored procedure should return all the categoryID which are subcategory to given categoryID,
 subcategory may contain some subcategory, so when i give a categoryID it should return all the subcategoryID.
 For example when i pass categoryID as 123 it should return the following subcategoryIDs
 321,322,401,501,541,542 because these are all subcategory of categoryID 123.
 How to write stored procedure for this?
 
Thanks!

View 6 Replies View Related

Can Anyone Help Me To Write A Stored Procedure For The Following Scenario...

Feb 7, 2008

Hai friends, 
The project i'm working on is an asp.net project with SQL Server 2000 as the database tool.
I've a listbox (multiline selection) in my form, whose selected values will be concatenated and sent to the server as comma separated numbers (Fro ex: 34,67,23,60).Also, the column in the table at the back end contains comma separated numbers (For ex: 1,3,7,34,23).
What i need to do is -
1. Select the rows in the table where the latter example has atleast one value of the former example (In the above ex: 34 and 23).
2. Check the text value of these numbers in another table (master table) and populate the text value of these numbers in a comma separated format in a grid in the front end.
 I've programmed a procedure for this. but it takes more than 2 minutes to return the result. Also the table has over 20,000 rows and performance should be kept in mind.
Suggessions on using the front-end (asp.net 2.0) concepts would also be a good help.
 Anybody's helping thought would be greatly appreciated...
Thanks lot...

View 2 Replies View Related

Read / Write Into .doc From SQL Stored Procedure

Jun 25, 2007

I need to create a flat file as word document, may i know how to write text from stored procedure if a file is already exist then the text will append, how to do it ?



Thank you.

View 1 Replies View Related

Hi I Want To Know How To Write Stored Procedure ..then I Have To Include (IF Condition ) With SP ..

Sep 20, 2007

hi i want to know how to write  stored procedure ..then i have to include (IF condition ) with SP ..
let me this post ..................anybody ??????????

View 5 Replies View Related

Could You Write A Special Stored Procedure For Me In SQL 2005?

Apr 6, 2006

Could you write a special stored procedure for me in SQL 2005?
When I execute the SQL "select TagName from Th_TagTable where IDTopic=@IDTopic" , it return several rows such as TagName= SportTagName= NewTagName= Health
I hope there is a stored procedure which can join all TagName into a single string and return,it means when I pass @IDTopic to the stored procedure, it return "Sport,New,Health"
How can write the stored procedure?

View 2 Replies View Related

Stored Procedure To Read And Write Between XML And SQLServer

Mar 28, 2004

Hi All,
I need some help from you experts.

I need to develop something that reads from xml files and writes in to sqlserver, also it should read from SQLServer and writes to xml.

I should be able to give this as a job to exectue daily ,etc.

Can we do this using stored procedures in SQLServer.
Pls paste some sample code, if any or direct me to any url with better info.

Thanks in advance

View 11 Replies View Related

Write To A Text File From A Stored Procedure

Oct 19, 2011

I have a sp which saves the necessary information regarding the status of action(whether success or failure, rows affected etc) to a log table say( StatusLog )After this, I was sending a database mail with information taken from the log table via sp_send_dbmail. Now I would like to write the status information to a 'txt' file instead of sending via mail.How can I write to a text file from a stored procedure in ms sql server 2005?

View 6 Replies View Related







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