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:
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.
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
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
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
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;
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" />
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?
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.)
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
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.
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!
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.
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.
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.
"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.
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.
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.
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
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
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"
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,
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?
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 ?
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 .
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)
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.
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 :)
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)
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?
