Cross Referance DB Objects Accross Store Procs
Sep 25, 2006
We are in the midst of cleaning up database01. There are objects in database01 that are widely used in store procedures that reside in database02.
Problem is: I need to know which tables and view from database01 are in use by store procedures in database02. This is important because if I accidently decommission tables that are currently in use by production class s-procedures, it will not be pretty. A sample output could look like:
StoreProc UsingObject
sp001 ctblCodeName
sp002 tblUnitSales
so003 tblSalesTrans
.......... .................
I am not a programmer and none of our programmers here claim that there is any solution to this problem without spending thousands on s/w licenses. Any simple solution + code snippet that will help me resolve this problem by myself would be incredibly valuable.
In disstress and frustration . . . .
JJOSHI
View 4 Replies
ADVERTISEMENT
Feb 19, 2008
I donot know how to use SQL NOCOUNT . I have written store procs for select and insert , Below are the store procs ,Can anyone tell me how to use SQL NOCOUNT in this StoreProcs???
Select:
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[GetAllLinks]') )drop Procedure [dbo].[GetAllLinks]
GO
CREATE Procedure [dbo].[GetAllLinks]
AS
Select * from UsefulLink
Go
Insert Storeproc:
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[InsertLink]') )drop Procedure [dbo].[InsertLink]
GOCREATE Procedure [dbo].[InsertLink]
@Title varchar(100),
@Location Varchar(100),@ID int OUTPUT
AS
Set @ID = -1Begin Transaction
Insert Into UsefulLink(Title, Location) Values
(@Title,@Location)if @@error <> 0
RollBack Transaction
Else
BEGIN
Commit Transaction
Select @ID = @@identity
END
Go
View 3 Replies
View Related
Jan 22, 2008
Dear all,
I am not so in database administration and I wold like to get some glue how to do what I am looking for.
I have a set of procedure and view in my Database HISTORY, then I define Grant the Execute rights to all those procedure for user NTAuthorityNETWORKSERVICE.
Then I have a second database named CONFIG where table might be accessed by store procedure of the database HISTORY. So first of all, in addition to set the EXECUTE grant access to my strore proces of database HISTORY, I need to set the SELECT grant access to all tables from database CONFIG on which are access by my storeprocs of database HISTORY.
First of all is it correct to set rights like this ?
Second, is there a way to set the access right of all my desire procedure in one shot ( script or something else ) becasue I have doen it manually and its painfull ?
If yes how to do that
thanks for help
regards
serge
View 4 Replies
View Related
Sep 25, 2006
I am new to SS2005, and I've just started working on a small test/devdatabase.I recently read that one should store things like tables, views,constraints, etc. in the *.ndf file rather than in the *.mdf file.Does this make it any easier to transfer/copy files or databases orother items from test/dev to production??If I have a database already with items in the *.mdf file, how do Itransfer things like tables, constraints, views, etc. to an *.ndf filein another database??I also read that one can make it the default in SS2005 to store thingsin the *.ndf file, how is this done??Thank you,Tom
View 5 Replies
View Related
May 20, 2015
I have two inline selects against a table with a nonclustered columnstore on SQL 2014 (12.0.2000). Both execute in batch mode and when I inner-join the two, they continue to execute in batch mode. When I cross join them, one executes in row mode. Below is some SQL to simulate the issue.
-- The purpose of this script is to demonstrate that
-- two queries against a columnstore index that each execute in batch mode
-- will continue to execute in batch mode when inner joined.
-- However, one of the queries will execute in row mode when cross-joined.
-- Create function to return 0 to n rows
IF OBJECT_ID('dbo.IntCount') IS NOT NULL
DROP FUNCTION dbo.IntCount;
[Code] .....
View 6 Replies
View Related
May 2, 2008
I have the following:omegalove.comIf you go to the search portion u will notice that there are three dropdownlist that are populate using sqldatasource object.I do not want these two post back. Is there a way we can cache this so it is faster.I would like to learn. I appreciate any help. Please don't give me links. I much rather learn from someone who has hands on.Thank you
I will have to separate the code from the ui?---------------------------------------------------------------------
Country:<asp:Image ID="FlagImg" runat="server" Height="12px" Width="20px" />
<asp:DropDownList ID="ddlCountry" runat="server" DataSourceID="SqlDSCountry" DataTextField="Country"
DataValueField="CountryID" AutoPostBack="True" OnDataBound="ddlCountry_DataBound" OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDSCountry" runat="server" ConnectionString="<%$ ConnectionStrings:omegaloveConnectionString %>"
SelectCommand="SELECT [CountryID], [Country] FROM [CountryCodes] ORDER BY [Country]">
</asp:SqlDataSource>
----------------------------------------------------------
State/Province:<br />
<asp:DropDownList ID="ddlRegion" runat="server" AutoPostBack="True"
DataSourceID="SqlDSRegion" DataTextField="Region" DataValueField="RegionID" OnDataBound="ddlRegion_OnDataBound" OnSelectedIndexChanged="ddlRegion_SelectedIndexChanged">
</asp:DropDownList><asp:SqlDataSource ID="SqlDSRegion" runat="server" ConnectionString="<%$ ConnectionStrings:omegaloveConnectionString %>"
SelectCommand="SELECT [RegionID], [Region] FROM [SubCountryCode] WHERE ([CountryID] = @CountryID)">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCountry" Name="CountryID" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
---------------------------------------------------------------
City :<br />
<asp:DropDownList ID="ddlCity" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlCity_SelectedIndexChanged" DataSourceID="DSCity" DataTextField="City" DataValueField="City">
</asp:DropDownList>
<asp:SqlDataSource ID="DSCity" runat="server" ConnectionString="<%$ ConnectionStrings:omegaloveConnectionString %>"
SelectCommand="SELECT [City] FROM [WorldCities] WHERE (([CountryID] = @CountryID) AND ([RegionID] = @RegionID))">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCountry" Name="CountryID" PropertyName="SelectedValue" Type="String" />
<asp:ControlParameter ControlID="ddlRegion" Name="RegionID" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
---------------------------------------------------------------------------------------------------------------------------------------------------------------
This is my behind code.
using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using OmegaLove;using OmegaLove.UI;namespace OmegaLove.UI.Controls{ public partial class Search : BaseWebPart { protected void Page_Load(object sender, EventArgs e) { } protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e) { FlagImg.ImageUrl = "../images/flags/" + ddlCountry.SelectedValue + ".gif"; ResetRegion(); ResetCity(); } protected void ddlRegion_SelectedIndexChanged(object sender, EventArgs e) { ResetCity(); } protected void ddlCountry_DataBound(object sender, EventArgs e) { ddlCountry.SelectedIndex = 39; FlagImg.ImageUrl = "../images/flags/" + ddlCountry.SelectedValue + ".gif"; } protected void ddlRegion_OnDataBound(object sender, EventArgs e) { ResetCity(); } private void ResetRegion() { ddlRegion.Items.Clear(); // ddlRegion.DataBind(); if (ddlRegion.Items.Count == 0) { ListItem item = new ListItem("Not Available"); ddlRegion.Items.Add(item); } } private void ResetCity() { ddlCity.Items.Clear(); // ddlCity.DataBind(); if (ddlCity.Items.Count == 0) { ListItem item = new ListItem("Not Available"); ddlCity.Items.Add(item); } } protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e) { }}}
View 6 Replies
View Related
Oct 25, 2004
How can we store native .NET objects like arraylists, hashtables in Sql Server? Is this possible? If so, can anybody provide some insight on how to do this?
Thanks
View 4 Replies
View Related
Oct 11, 2006
I'm sure this has been asked plenty of times before, so I'm after a link to a good answer.
I have tens of thousands of milk crates, holding dozens of different types of milk in hundreds of locations. I am used to working with objects but not databases. For this situation however I want the security of SQLServer transactions to track, for example, when a robot moves a crate from one location to another.
I am thinking of using SQLServer as a store. On startup I want to get my ecosystem of objects out of the store. While I am running, I'll just use objects. When I change an object property I want it to securely persist. I don't want to snapshot the whole menagerie of object states, just update the values that changed. Which will sometimes include the addition or deletion of objects. How do I do this? Is there an example somewhere that does this (or approximately this)?
I use VB and have Visual Studio 2005. (Which, by the way, is stunning. I thought all that "you will use less time and code more and better" talk was just hype. But its for real. Amazing product.)
tia
John
View 1 Replies
View Related
Dec 6, 2007
Can any help me regarding the following
When you are pulling the data from PPV
SELECT
[MATERIAL]
,[IR_AMOUNT]
FROM [LATCUBDAT].[dbo].[ppv]
where [MATERIAL] = 'MR004' AND [IR_AMOUNT] = 53728.85
Result
material
IR_Amount
MR004
53728.85
When you want to join ppv to rmu and then pull the data then
select R.[PRODL]
,P.[MATERIAL]
,P.[IR_AMOUNT] AS 'IR_AMOUNT'
FROM [LATCUBDAT].[dbo].[ppv]P
INNER JOIN [LATCUBDAT].[dbo].[rmu] R ON R.[RAW MATERIAL] = P.[MATERIAL]
where P.[MATERIAL] = 'MR004' AND P.[IR_AMOUNT] =53728.85
PRODUCT LINE
MATERIAL
AMOUNT
30000
MR004
53728.85
32000
MR004
53728.85
36000
MR004
53728.85
50000
MR004
53728.85
52000
MR004
53728.85
54000
MR004
53728.85
When I use avg in the query I get this report
select R.[PRODL]
,P.[MATERIAL]
,avg (P.[IR_AMOUNT]) AS 'IR_AMOUNT'
FROM [LATCUBDAT].[dbo].[ppv]P
INNER JOIN [LATCUBDAT].[dbo].[rmu] R ON R.[RAW MATERIAL] = P.[MATERIAL]
where P.[MATERIAL] = 'MR004' AND P.[IR_AMOUNT] =53728.85
GROUP BY R.[PRODL],P.[MATERIAL]
ORDER BY R.[PRODL],P.[MATERIAL]
PRODUCT LINE
MATERIAL
AMOUNT
30000
MR004
53728.85
32000
MR004
53728.85
36000
MR004
53728.85
50000
MR004
53728.85
52000
MR004
53728.85
54000
MR004
53728.85
I will like to see
PRODUCT LINE
MATERIAL
AMOUNT
30000
MR004
8954.808
32000
MR004
8954.808
36000
MR004
8954.808
50000
MR004
8954.808
52000
MR004
8954.808
54000
MR004
8954.808
If there where 4 product line then avg distributed between 4 and like wise
View 5 Replies
View Related
Jan 15, 2008
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
View 1 Replies
View Related
Mar 30, 2000
Is there anyway that I can run a procedure that will give me all the indexes on tables that are accross multiple servers?
Thanks for any help.
Dianne
View 2 Replies
View Related
Aug 15, 2007
I have 1 table in my db that I have imported from a txt file using DTS.
In my database "DOJ" I have a table named "DOJGRAB" with ALL my data fresh from a import using DTS.
Also in "DOJ" are 6 other tables (NAME, PERSON, etc...)
How can I parse the columns out from my DOJGRAB table to fill existing columns in my other 6 tables? Basically a copy of specific columns from DOJGRAB to their proper places in the other tables.
Can somebody post an example of the syntax I would use to copy a column from one table to another? Thanks!
View 7 Replies
View Related
Mar 14, 2008
I have to DBs. One is the original and the other is for testing. They both have similar data structure. Wanted to know if there is a way to update tables contents in one db with data that is in the other one.
View 4 Replies
View Related
Nov 28, 2006
First thanks for your time.
We have 4 clustered SQL2000 Servers each contains information specific to its application related to customer information in a casino player tracking database. My problem is as follows On the Playertracking database I can join and return information from the tables there with no problems the performance accross the decently sizable transactional based table is pretty decent. The problem is I need to filter this query down by the Type of machine the customer plays. The child key exists in the playertransaction table the parent key is on another server. Here is the lay out of the tables unecessary information from the tables were truncated for brevity.
CREATE TABLE [dbo].[PlayerSession] (
[PlayerId] [int] NOT NULL ,
[Mnum] [int] NOT NULL ,
[CoinIn] [money] NOT NULL ,
[CoinOut] [money] NOT NULL ,
[Games] [int] NOT NULL ,
[Jackpot] [money] NULL ,
[Win] [money] NULL ,
[TheoWin] [money] NOT NULL ,
[PlayerMod] [tinyint] NOT NULL
) ON [PRIMARY]
-- Player Demographics information
CREATE TABLE [dbo].[Player] (
[PlayerId] [int] NOT NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MiddleName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
--Machine Information that links to Machine Type table
CREATE TABLE [dbo].[Machine] (
[MNum] [int] NOT NULL ,
[MachineTypeId] [smallint] NOT NULL ,
) ON [PRIMARY]
-- Machine type code table
CREATE TABLE [dbo].[MachineType] (
[MachineTypeId] [smallint] NOT NULL ,
[Denom] [int] NOT NULL ,
[Par] [decimal](6, 2) NOT NULL ,
[GameType] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]
From the server where all the player information is ran I can query the linked database for the machine and machine type information like this.
SELECT m.MNum, mt.MachineTypeId, mt.GameType, mt.DisplayType
FROM ACCTV.Accounting.dbo.Machine m INNER JOIN
ACCTV.Accounting.dbo.MachineType mt ON m.MachineTypeId = mt.MachineTypeId
This is the information I am trying to get out but the query times out on me.
SELECT Player.PlayerId, Player.FirstName, Player.LastName, SUM(PlayerSession.CoinIn) AS sumCI, SUM(PlayerSession.CoinOut) AS SumCO,SUM(PlayerSession.TheoWin) AS SumTheo, AVG(PlayerSession.TheoWin) AS AvgTheo, SUM(PlayerSession.Win) AS SumWin, AVG(PlayerSession.Win) AS AvgWin, mt.GameType
FROM Player INNER JOIN
PlayerSession ON Player.PlayerId = PlayerSession.PlayerId INNER JOIN
ACCT.Accounting.dbo.Machine M ON PlayerSession.Mnum = M.MNum INNER JOIN
ACCT.Accounting.dbo.MachineType mt ON M.MachineTypeId = mt.MachineTypeId
GROUP BY Player.PlayerId, Player.FirstName, Player.LastName, mt.GameType
The other option would be some sort of SubQuery but I dont know how to return results from the subqueries to the root query to be returned to the restulting recordset. I am not necessarily looking for an answer more of looking for a direction to go to find my solution.
Thanks again for your help.
View 1 Replies
View Related
Aug 10, 2007
Hi,
I am newbie in replication,
I have a requirement where need to set upo replication with db such as both dbs are in different domain.
I have created a merge publication with create pulblisher wizard.
I want to use FTP as default location for the snapshot so that from both the domain database can accesss this path and subscriber will take the snapshot from it.
Issue facing like to run the snapshot agent we need to provide windows domain user or machine user but we can't add this user in FTP machine as domain is differnt will not get access on this machine.
I am getting following error:
Message: The replication agent failed to create the directory '\192.168.7.57ftprootuncPARAG-TEST_NORTHWINDSUB_PARAG_PUB20070810185320'.
Stack: at Microsoft.SqlServer.Replication.Utilities.CreateDirectoryWithExtendedErrorInformation(String directory)
at Microsoft.SqlServer.Replication.Snapshot.SnapshotProvider.CreateSnapshotFolders()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.CreateSnapshotFolders()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQL_REPL, Error number: MSSQL_REPL52026)
Get help: http://help/MSSQL_REPL52026
Source: mscorlib
Target Site: Void WinIOError(Int32, System.String)
Message: Logon failure: unknown user name or bad password.
Stack: at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.Directory.InternalCreateDirectory(String fullPath, String path, DirectorySecurity dirSecurity)
at System.IO.Directory.CreateDirectory(String path, DirectorySecurity directorySecurity)
at Microsoft.SqlServer.Replication.Utilities.CreateDirectoryWithExtendedErrorInformation(String directory) (Source: mscorlib, Error number: 0)
Any help regarding this is appreciable.
Please can put more light on it if I am doing anything wrong.
Thanks & Regards
Amit Harhare
View 5 Replies
View Related
Jul 23, 2005
"David Portas" <snipped for brevity> wrote:Example 1:[color=blue]>> UPDATE table_a> SET col = ? /* Unspecified */> WHERE EXISTS> (SELECT *> FROM table_b> WHERE table_b.key_col = table_a.key_col)>[/color]<snip again>Many thanks. I have used this sample extensively since you posted it. Hopeyou (or someone) can help me with one more thing: How would it be writtento update several fields in table A with data from table B, where as youhave shown, a column matches the records?TIA!~ Duane Phillips.
View 4 Replies
View Related
Sep 21, 2007
Are there any perfmonace or query optimization limitations or issues that arise when issueing a fully qualified query across multiple databases on the same Instance of SQL Server. In other words are all features of query optimization fully supported in queries that span databases on the same Instance.
View 3 Replies
View Related
Nov 1, 2007
Hello, all
I have developed a SSIS package using a transaction
1. Users upload excel file to web server(139.223.15.xxx)
2.Excute SSIS package in web server to import excel file to SQL SERVER 2005(139.223.3.xxx)
If web server and SQL server are in the same network zone, the package will work successfully.
However, the two servers are in the different network zone, it encounters an error :
DTS can't enlist OLE DB in distribution transaction. Error Code: 0x8004D00E
How do I set any configurations?
Environment:
Web server:windows 2003 server sp1 ent
SQL server 2005 ent:windows 2003 server sp1 ent
SSIS: isolation level-->read committed
View 1 Replies
View Related
Apr 20, 2007
Hi,
Is there a way to take a snapshot of a table at precisely the same time on multiple servers and write the table snapshot to another 'work' database? I'm asking in this forum hoping that perhaps SSIS has a built in tool to help with this. I'm wanting to audit table(s) accross seven different servers. In order to do this I want to make sure that I have the table at the same moment in time on all the servers. I plan on using the tablediff utility to then compare each table on each server against one another. If somebody knows a better method to do this please let me know.
Thanks,
Phil
View 1 Replies
View Related
Dec 27, 2006
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies
View Related
Nov 9, 2007
Hello,
Not sure if data minimg can help me solving this type of inquiries. Thanks in advance for any pointers you could provide me.
Problem:
We have many dimensions with many levels. Sales directors will sometimes note a drop or a spike when looking at one of the dimensions members, most of the time at a high level.
Example, For Q4 07, the aggregation of Q4 Billing plus remaining Q4 Backlog dropped by $10M in America for the Automotive business from week 4 to week 5 while it was forecasted to remain stable.
The immediate question that will be sent to the guys in charge of this business is "What happened?".
Obviously evyone struggle to answer these questions eveytine there is a drop anywhere.
Using classic tools, they will try to spot a single anomaly or determine if this is accross the board and what are the concurent factors like lower than expected shipments combined with some orders cancellations.
The drop-off can lie in a single product or customer location, in which case it is fairly easy while time consuming to spot the anomaly using classic drill-down approach.
The drop-off can also be more wide spread or caused by a combination of factors like unable to ship in time, orders cancellations and sales credits in which case it is more difficult to spot where is the anomaly and explain it.
So I wonder if there is any way that I could use data mining to make the entire process of detecting anomalies accross dimensions levels, members and measures more straight-forward for end-users.
What do you think?
Thanks,
Philippe
View 1 Replies
View Related
Nov 20, 2013
passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
View 1 Replies
View Related
Dec 8, 2006
Hi,
We have SAN for our SQL server and all of DB backup copy pointing to one the SAN volume(ex. T). We are moving the bkp copy from this SAN volume into remoteserver for restoring the backup. We are getting a lot of Time out during this time.
Is that copy process affect production time out?.
Thanks,
View 1 Replies
View Related
Apr 19, 2007
Hello,
We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.
In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?
Thanks,
Phil
View 4 Replies
View Related
May 7, 2004
HI,
CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC PROC1
ST......1,
ST......2,
END.
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC MY_PROC
ST......1,
ST......2,
END.
BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?
THANKS.
View 4 Replies
View Related
Sep 13, 2007
Hi,
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.*
FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************',
'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
Thanks
View 8 Replies
View Related
Feb 5, 2005
almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --
Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)
i don't think i can do justice to how utterly stupid that stored procedure is
read the comments and have a laugh
one of the points made was that stored procedures are bad
this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --
Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)
enjoy!
View 6 Replies
View Related
Jun 28, 2007
Hey guys.
I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.
Thanks
tibor
View 14 Replies
View Related
Jan 13, 2004
Does anyone know how to tell which procedures are called when SQL Server 2000 starts up if scan for startup procs is turned on?
View 11 Replies
View Related
Mar 3, 2004
Can somebody provide me an argument on how nicely formatted and indented procs perform better. I am currently formatting a 1000 line proc just to understand what it does ... Maybe your argument will help convince my development team to write neat procs :)
View 8 Replies
View Related
Apr 8, 2004
First off, this is a cross post which is also located here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073
Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.
What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.
The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.
The code is below, just copy / past, execute and you will see exactly what I mean.
Any thoughts?
USE NORTHWIND
GO
CREATE FUNCTION RECTANGULAR_XSECTION
(@fWidth float, @fHeight float)
RETURNS float
AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO
CREATE PROCEDURE usp_shapes_GetRectangularXSection
@fResult float OUTPUT
AS
declare @fWidth float, @fHeight float
SELECT @fWidth = 108, @fHeight = 10
SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
SELECT @fResult AS CalledProcedureOkHere
GO
CREATE PROCEDURE usp_shapes_GetXSection
@fResult float OUTPUT
AS
EXECUTE usp_shapes_GetRectangularXSection @fResult
SELECT @fResult AS CallingProcedure_NULL?
GO
declare @fResult float
EXECUTE usp_shapes_GetXSection @fResult
SELECT @fResult as OutsideCallingProcedure_NULL?
GO
DROP FUNCTION RECTANGULAR_XSECTION
GO
DROP PROCEDURE usp_shapes_GetRectangularXSection
GO
DROP PROCEDURE usp_shapes_GetXSection
GO
Mike B
View 3 Replies
View Related
Jul 2, 2007
Hi all,
I have to write SPs in SQL 2005 based on procs in Oracle DB.How do I go abt this? Any help will be welcome.
Thank you
Necessity is the mother of all inventions!
View 19 Replies
View Related
Nov 13, 2007
I am trying to improve my SQL and reduce the number of connections my website is making to the server.
Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.
I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.
Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?
View 5 Replies
View Related
