I am using dtswizard to export my db but it doesn't export triggers and all stored procedures. Am I doing anything wrong or is there a better way to do an exact copy of my DB? I am not able to use backup/restore because my db is on a server to which I don't have admin-right, and I have to pay my hoster to create a backup and email me.
TIA
Thomas
I developed a search stored proc that searches all orsome of Procs, Views, Triggers and functions. Would anyone be interestedto see it posted here?Do you have any suggestions about other places, websites forums ...., toshare something I have developed?Thanks you for your response in advancePLease send me emailJoin Bytes! {Remove ### before responding}
View 1 Replies View RelatedI would like to copy the following 3 stored procedures from Database1 to database2 on the same server:
USP_UpdateDocRepository
USP_GetUA
USP_GetCN
Is there a copy command available via query window.
Thanks.
I've installed SQL Express SP1 on a Windows 2003 server and I'm trying to get a new MySQL datasource to be recognized in the DTSWizard. A MySQL database needs to be moved over to a MS SQL server due to software requirements (which weren't looked at before the site was created). I've installed the ODBC Connector/MySQL software and created the System DSN (named "MySQL DB Import) in the Administrative Tools/Data Sources (ODBC) tool. When I start up the DTS wizard for SQL Express however, I do not get my new data source in the selection options for data sources. I am following the instructions on this page: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx#ERJAE What am I missing?
View 2 Replies View RelatedI've downloaded the enterprise evaluation copy of sql server 2005, patched it to sp2. I've fired up visual studio 2005 and opened a connection to my local database okay, (I've used windows authenication - though I'm not sure how to check whether I've got SA rights..).
When I open a stored procedure up, the breakpoints options are greyed out.
When I look in control panel/add programs, the version of Visual Studio is "Premier Partner Edition - ENU", if I look in the about of vs: it has installed products of sql server analysis services, integration services and reporting services
Looking on the web it says that I need visual studio 2005 professional to be able to debug sp's but on the features list for Premier Partner Edition, under the debug section it says "SQL"
Can anyone shed some light on this? Or is it the case that the eval copy of sql server 2005 ships with a version of vs2005 that precludes sp debugging !!!
ta
Hi Folks,
I've been looking through these pages at posts regarding the installation and use of the DTSWizard for importing/exporting files to/from SQL Server Express.
Lots of good, if sometimes contradictory, advice.
I had SSEE installed on my previous PC and (once I was told where to look for it!) also had the use of the dts wizard which made working with SSEE a breeze.
Unfortunately, that PC is no longer with us care of a lightning strike (yes, I have surge protection, no I forgot to unplug the modem...).
I've installed SQL Server Express with Advanced Features SP1 on the new PC and lo and behold - no DTS Wizard!
I've also tried installing the ToolKit and still no wizard.
I know it is supposed to be installed to the following folder:
"C:Program FilesMicrosoft SQL Server90DTSBinn"
However, despite repeated uninstalls/installs my folder structure is:
"C:Program FilesMicrosoft SQL Server90Binn"
Note, no "DTS" folder and no DTSWizard!
Could someone from MS please confirm whether this item is still available (somewhere) in the downloads for SQL Server Express and if so, how the heck can I install it please?
If it isn't available this has to be a huge shoot-yourself-in-the-foot gotcha!
How do I transfer/copy the stored procedures in my Test DB to my LIVE DB? IT won't allow me to export keeps giving me an error.
View 4 Replies View RelatedIs 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.
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.
I have been asked to copy a store procedure from one database to another and also copy triggers from one table to another. Can someone tell me how to go about it or how I can do that? I look forward to hearing from you.
Regards,
Is it possible that when a trigger fires (insert) I can have a file moved from one compter/folder to another?
If so how?
TIA
Dave
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Hi,
I had developed a simple report, that has header, body and footer. In footer i have copyright.
Now the problem is that, when i export it to excel the copyright text i had in the footer doesn't export.
any one had any idea.
Chinna.
Hi all,
I have a parent-child table, and i want to copy subtrees of it, so for instance this would be the starting point:
(id, parentId, label)
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London
now i want to copy object 3 (UK) and it's children, so i would get
0, null, World
1, 0, US
2, 1, NY
3, 0, UK
4, 3, London
5, 0, UK_copy
6, 5, London_copy
I have this sproc:
Code Snippet
alter proc CopyObject
(@ObjectId int,
@NewParentId int)
as
declare @NewId int,
@NewName varchar
select @NewId = max(Id) + 1 from Object
select @NewName = [Name] + 'copy' from [Object] where Id = @ObjectId
-- copy object
INSERT INTO [Object]
([Id]
,[Name]
,[ParentId]
select @NewId,
@NewName,
@NewParentId
from [Object]
where Id = @ObjectId
-- copy children and set their parent to the newly created object
declare c cursor fast_forward for
select Id
from [Object]
where ParentId = @ObjectId
declare @ChildId int
open c
fetch next from c into @ChildId
while @@fetch_status = 0
begin
exec CopyObject
@ObjectID = @ChildId,
@NewParentId = @NewId
fetch next from c into @ChildId
end
close c
deallocate c
But htis throws an error that the cursor already exists:
Msg 16915, Level 16, State 1, Procedure CopyObject, Line 66
A cursor with the name 'c' already exists.
Msg 16905, Level 16, State 1, Procedure CopyObject, Line 72
The cursor is already open.
I've tried to think of an approach without cursors, but i can't figure it out. Because on the first pass, the new parentId will be the same as the parentId of the object to be copied. But the copies of the children of this first original object should have the parentid set to id of the copied object, and so all the way down the tree.
Any ideas?
Thanks in advance,
Gert-Jan
Hi there,
I got this weird problem and I was wondering whether anyone has an idea of how to resolve this.
I have a working report on the RS server, ran the report and tried to print it straight from the page by clicking on the Print button.
When it printed, it gave me additional pages as though the margins were incorrect. I have additional space on the left of the report, which pushes it out towards the right (very slight but noticable) which ended up printing "blank pages" ( basically only the page header). Moreover it didn't print any of the page footers ( which has the page numbers).
The weird part is that, I tried to export it to PDF format, and when I take a look at the pdf version, it looked fine with no formatting errors. Even printing the pdf works great.
I was wondering whether there was some bug with the RS Server or do I need to do somethin with the config file ? please advise. thanks !
Bernard
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!
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
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
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?
can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!
View 10 Replies View RelatedAll,
I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:
(
--define the parameters that are needed
@userID char(32),
@userName varchar(50),
@status char(10),
@type char(10),
@password varchar(50),
@firstName varchar(100),
@lastName varchar(100),
@email varchar(200),
@domain varchar(50),
@pwdExpiry int,
@badLogin int,
@lastLogin datetime,
@full varchar(8000),
@read varchar(8000),
@noaccess varchar(8000)
)
AS
BEGIN TRAN tmp1
--First, insert the user into the system
INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate)
values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);
--Now, we need to add the function access edges for the user
declare @arrayValue char(32)
declare @rightID char(32)
declare @sepPos int
while patindex('%,%',@full)<>0
BEGIN
select @sepPos = patindex('%,%' , @full)
select @arrayValue = left(@full, @sepPos - 1)
-- replace the value with an empty string
select @full = stuff(@full, 1, @sepPos, '')
--create and parse the new id
declare @strID char(32),@tmpStr varchar(40)
set @tmpStr = newid();
set @strID = REPLACE(@tmpStr,'-','')
--get the access right id for full control
select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write')
if(@rightID IS NOT NULL)
--insert the records that are full access
INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID)
VALUES(@strID, @arrayValue, @rightID, @userID)
else
RAISERROR(50100,15,1)
END
COMMIT TRAN tmp1
IF @@TRANCOUNT > 0
RAISERROR(50101,15,1)
ROLLBACK TRAN tmp1
The transaction does not rollback when any errors occur at all. Any advice/help?
Hi all!
I am not an expert in Stored Procs. I would like to build one for a product list that would return a default value without using output parameters, if possible. Ultimately, I wouldn't be opposed to it.
It currently looks like this:
CREATE PROCEDURE ProductsByVendorNo
(
@VendorNum nvarchar(24)
)
AS
SELECT
P.ProductID AS ProductID,
P.ProductShortName AS ProductName,
P.ProductDesc AS ProductDesc,
U.UnitDesc AS Unit,
P.VendorProductNumber AS VendorNumber,
V.VendorName AS VendorName,
P.Price AS Price,
P.ImageThumb AS ImageThumb
FROM
tblProducts AS P
INNER JOIN tblUnitCodes AS U ON P.UnitCode=U.UnitCode
INNER JOIN tblVendors AS V ON P.VendorID=V.VendorID
WHERE
V.VendorsVendorNo = @VendorNum
AND P.Inactive = 0
ORDER BY
ProductName,
VendorNumber
I would like for it to return a default, constant value for the URL in the ImageThumb field, if this one is empty. I could not find good documentation of how to use IF statements for this case, i.e. to alter the return of just one field.
Suggestions?
Any input is highly appreciated.
Thanks in advance,
Mili Skikic
I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?
can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?
this is sql7, sp3, development box.
thanks,
I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:
select store_name
from store (server A)
where date_created >= (select max(date_created) from store (server B)
I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine.
Unfortunately, there is no functionality for a linked server connection.
I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.
declare @maxdate datetime
exec serverB.dbo.sp_max_date_from_store @maxdate
select store_name
from store --(server A)
where date_created >= @store
Any help or suggestions would be greatly appreciated!
trevorb
I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors)
Management is looking at stored procs to improve performance, but I am wondering
if the db is in this state will there really be a gain. I am pushing for normalization
first, but if anybody has any ideas or opinions I would appreciate
I have a stored proc that is run periodically which I execute with a DTS package. The problem is that I have to update a field for each record selected by this stored proc with a specific date. I need to prompt the operator for this date so I can use this value.
View 6 Replies View RelatedOk, I've read somewhere(which I'm looking for again : ) that said that there are errors like DeadLock that kills the execution of a stored proc and there are other errors that do not necessarily kill the rest of the execution of the stored proc. Is that true? If so does anyone have any links I can read. What I'm seeing is a bad id in the foreign key and I think what is happening is that there was a unique constraint error on the first insert but the stored proc continued executing and used the bad id later on in the stored proc.
I do know I can use the @@error and will start using it but I need more proof to agree or not agree with my theory.
Thanks ahead of time for any information you can give me either way.
DMW
Is there any performance loss by setting up a view and creating a stored procedure to return a recordset from a view versus setting up the stored procedure to return the recordset from the tables directly?
What way should I do this?
Mike B
Hi all.
First post on here, so please excuse any ignorant parts of my question :)
We've recently had to migrate an application from one server to another, and had lots of problems getting it to work (NT4/SS7 -> 2K3/SS2K5). After much digging around I found the info on SQL server 2k5 changing the SUSER_NAME to SUSER_SNAME (I think from reading the change was actually from SQL Server 2000 - that correct ?).
Anyway I updated some of our stored procedures to correct that issue, and things *seem* to be working again (<-- famous last words, I know :eek: ), but I've been trying to find details of any other changes, particularly command changes, that I may need to make to our stored procedures.
To be honest I've found the info on Microsoft's main site and technet site next to useless - all seems to discuss very generic high-level procedures for database migration etc.. I didn't even find the SUSER change on there.
Could anyone point me to some useful information on other changes to look out for (within the SP's) ??
Thanks in advance for your help, and for taking the time to read this :beer:
Hey yall
I ran into something interesting today and was wondering how one would do this. I have some 3rd party stored procs and one was kicking out a truncate error so I took it upon myself to investigate the stored proc that was kicking out this error. So when I tried viewing the sp, I received an error:*****Encrypted object is not transferable, and script could not be generated.***** and then it brings up a blank editing screen.
First I believe this was a custom error message as it just doesnt seem like the way SQL Server would have presented it.
So how would someone prohibit viewing of a stored proc like this?
Thanks
Hi all
I have a stored proc that runs every 4 hours - as a job. The stored proc takes about 3-5 minutes to comple, depending on number of records.
To do testing we run that stored proc manually, also.
Sometimes 2 or more people may run the same stored proc without knowing that the other person is running. This may create duplicates entries once processed.
What I want to know is, Is there a way to check if that stored procedure is currently running. If so it wont run second time concurrently.
(May be semapohres,mutex or something like that?)
(I am trying not to use a table to store whether the stored proc is running or not)
Thanks in advance.
Rochana
I wrote a stored procedure and set it as a schedule. it has no problem when I execute this SP in query analyzer. But it will show error when I execute it in schedule job.
I think the main reason is I have created a DDL trigger that will fire when DDL_DATABASE_LEVEL_EVENTS happens. Any solution?
SELECT failed because the following SET options have incorrect settings: 'ARITHABORT'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. [SQLSTATE 42000] (Error 1934). The step failed.
Can someone please point me in the right direction (guidance or url to a utility or article) as to how we can write a script to scan all of the stored procedures in our database for any reference to a field name were are about to change? Also, can we use such a script to do the actual change from the old field name to the new one?
Thanks!