In my new installation of MS SQL Server 2005 Management Studio, i open the DB i want, then i go to Programmability/Stored Procedures i click right button "New Stored Procedure".
I can write a stored procedure but when i save it does not record the sp in the dababase but in the file system without any relation with the database!
The database was originally created with SQL Server 2000 and now attached to a 2005 version (may be is something to do with it)
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
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'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I Imported a table over to sql 2000 server(through the DTS), then I exported the table to access 2000. After creating my forms my users were trying ot add data but couldnt, they kept getting an error message saying these records are not updateable. After some research I found that I never appointed a primary key, so after doing so the records became updateable. constant learning process adn I love it :)
Is there a way to restore just the data.mdf file and accept an existing log?
Current state: A client has a backup where the data file is 400mb and the log is 4.99gb. The avaiable hhd space is 6gb.
Restoring the backup to a test database fails.
Objective: Check the calendar file for missing data, and if found, update the live calendar with the missing items.
We don't need the database to be fully operational, we only need a particular table. I'll write a cursor if needed to update the live cal from the restored backup.
I suspect that the available diskspace is causing the retore to fail.
I wanted to create a table with an exsisting table, then create a relationship between the two. The table being created needs a DocID autonumbered primary key, is that possible to create through sql. An autonumber like access has, through a query or something. should I just have a insert into query or something?? how would I go about doing that.
Yes this is a silly question, but I don't know the answer!
I have developed a database using SQL Server 2K. I am now upgrading to SQL Server 2005. Can I still use my current database files in SQL Server 2005? If I can, do u have any idea how I can make SQL Server 2005 load up the old files and start working?
Also my hosting provider has NOT upgraded to SQL Server 2005. He will only accept the old SQL Server 2K files. Can SQL Server 2005 save files that will work on a SQL Server 2K server?
Hi all. I think its a silly question to ask. What is better to use? I mean in terms of performance. c.* or c.field1, c.field2, c.field3, c.lastfield..... ?
HiI've not used SQL Server for a while, and I've forgotten how you hide allthose system procedures (beginning with dt_) in Enterprise Manager.Could some kind person please refresh my memory?ThanksCaptain Nemo
In a situation when you have a power cut, and then sometime later 'most' of your sql servers come back on line, is it better to leave them all down unless they all come back online, or is it better to let some of them come back up knowing that the ones that do come up will have job failure issues with the ones that are down. I pose this question purely from the perspective of scheduled job problem as we do not have people on site when we have intermittent power cuts at weekends. What would scheduled jobs which are due to run , but miss their run time as we leave the servers down after a power cut till we get back in on Mondays do when we do actually re-power them up, would they just resume from their next scheduled point, or would they try to run as often as they should have run?
I will give you the simplest version of this I know if.
I have 3 tables.
Person Table PersonID, Forename, Surname
Event Table EventID, EventName
Involvment Table PersonID, EventID
In this, the Person table's primary key is PersonID, the Event table's primary key is EventID and the Involvment table's primary key is PersonID, EventID. There is also a foreign key constrant between Person.PersonID and Involvment.PersonID and a foreign key constraint between Event.EventID and Involvment.EventID.
The sql to create this would be
CREATE TABLE [dbo].[Person](
[PersonID] [int] NOT NULL,
[Forename] [nchar](30) NOT NULL,
[Surname] [nchar](30) NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Event](
[EventID] [int] NOT NULL,
[EventName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Involvment](
[PersonID] [int] NOT NULL,
[EventID] [int] NOT NULL,
CONSTRAINT [PK_Involvment] PRIMARY KEY CLUSTERED
(
[PersonID] ASC,
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Involvment] WITH CHECK ADD CONSTRAINT [FK_Involvment_Event] FOREIGN KEY([EventID])
REFERENCES [dbo].[Event] ([EventID])
GO
ALTER TABLE [dbo].[Involvment] CHECK CONSTRAINT [FK_Involvment_Event]
GO
ALTER TABLE [dbo].[Involvment] WITH CHECK ADD CONSTRAINT [FK_Involvment_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[Involvment] CHECK CONSTRAINT [FK_Involvment_Person]
As so obviously stolen from SSMS.
Now what I am having problems with is if someone is involved with more than one event, then I only want them to get listed once.
If we have the following in the Person table.
0 John Doe
1 Jane Doe
the following in the events table
0 This
1 That
and this in the Involvment table
0 0
0 1
1 1
then when doing any select using the Involvment to get name and event information from their respective tables, there will be two entries for John Doe. I don't want this if it is possible. Although the same event multiple times is ok in this case.
So, if someone could help with this then it will be greatly appreciated. I'm still not that great with SQL so this is a problem which has been annoying me.
I have typically done any ETL style manipulations I needed to do to data stored in SQL Server in VB.NET. I would use the IMPORT?EXPORT DTS wizard to import flat files, or mabe something from ACCESS every now and then.
I am looking at a situation in my current contract where I will be pulling flat files from a mainframe and quasi relational stuff from a DB2 instance via an ODBC connection. I will be using this stuff to build a datawarehouse for a manufacturing client.
My question is this. Is there really enough good stuff in SSIS for what I will be doing to justify my learning it or if I'm comfortable doing the manipulations in VB.NET will that work just as well for my client? After all, I can schedule VB.NET apps to write results to log files and to run at specified times, etc. It just sort of always seemed to me that DTS was for people that needed to manipulate data without necessarily having to know a lot about programming per se.
I'm looking for opinions from people that know SSIS well. Is there enough meat to make cooking the SSIS meal worth the trouble?
As i am inserting data from textfile to sql server database i happened to have space Like a square appears in front of data for certain columns.How do i remove that????I use Trim(column) for all incoming columns but it does not work.
What is the function of SQL Server Manager Studio Express? Create Database and tables? It seems that Visual Studio is already including the functions. We can create database and table in Visual Studio 2005.
I have come across this term (YUKON or something like that). Is this the 'code name' for the next version of SQL server which is yet to be launced??? Can someone help me provide links to some articles on it.
I have come across this term (YUKON) or something like that and assume that this is the "code name" of thenext version of SQL server (i.e. 2005) thatis yet to belaunched. Isthis correct? If not, can someone please explain what YUKON means/stands for.
Gurus,Here is what I ma trying to do. I have numeric expression stored in atable column. for e.g. @a + @b + @c. I supply values to the variablesat run time and want them to be computed at run time as per theexpression in the column.the stored procedure works fine but it gives a silly error.Any help greatly appreciated. Below is the code.--drop procedure proc_bkrcreate procedure proc_bkr ASdeclare @expr nvarchar(2000)declare @sql nvarchar(2000)declare @temp_exp nvarchar(3000)declare @ans integerdeclare @QFAAPAC02_1 integerdeclare @QFAAPAC02_2 integerdeclare @QFAAPAC02_3 integerdeclare @QFAAPAC02_4 integer-- Assigning values to variables -- Startset @QFAAPAC02_1 = (Select QFAAPAC02_1 from fa_ap_stage where recordid= 3)set @QFAAPAC02_2 = (Select QFAAPAC02_2 from fa_ap_stage where recordid= 3)set @QFAAPAC02_3 = (Select QFAAPAC02_3 from fa_ap_stage where recordid= 3)set @QFAAPAC02_4 = (Select QFAAPAC02_4 from fa_ap_stage where recordid= 3)-- Assigning values to variables -- Endset @temp_exp = (select num from translation where processid = 'AP' andlabel = 'C1')-- This is how num looks: @QFAAPAC02_1 + @QFAAPAC02_2 + @QFAAPAC02_3 +@QFAAPAC02_4--select @expr = '@QFAAPAC02_1 + @QFAAPAC02_2 + @QFAAPAC02_3 +@QFAAPAC02_4'-- Above line works fine but below one does not. though both are same.select @expr = @temp_expselect @sql = 'select @ans = ' + @exprexec sp_executesql @sql, N'@QFAAPAC02_1 integer, @QFAAPAC02_2 integer,@QFAAPAC02_3 integer, @QFAAPAC02_4 integer, @ans integer OUTPUT',@QFAAPAC02_1,@QFAAPAC02_2,@QFAAPAC02_3,@QFAAPAC02_ 4,@ans OUTPUTset @cc = @ansError Message: Server: Msg 137, Level 15, State 2, Line 1[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare thevariable '@QFAAPAC02_'.Thanks in Advance!Bkr
