I am about 1 day old when it comes to undertanding stored procedures so I'm at your mercy for a kick start once again :)
For my next trick I shall be attempting to create a stored procedure that will accept a whole bunch of paramaters but will not work until one paramater is tested against the database. I have no idea how to do "If/then/else" style code in a stored procedure even though I was reading up on Drop_Procedure.
I want to pass in nickname, fullname, address but if nickname already exists then return appropriate error otherwise return success symbol (maybe new rowid or num records affected).
If Not exists nickname in MyTable then
Insert nickname, fullname, address
Return Success
else
return error "Nick name already exists"
End if
The second part my problem is how do I handle this in ASP.NET? I can call a stored procedure with paramaters and collect the return value but how do I deal with errors?
Any tips on sites for Sql Server as good as www.ASP.net would also help me out.
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?
Does anybody know where I can download the samples ? What I mean is not the Northwind or pubs database but samples that are usually installed in "C:Program FilesMicrosoft SQL Server90Samples" directory; I am missing them.
I still can not get the samples databases installed. I am tring to following step 3 from Ten Things You Need To Know To Get Started with SQL Server 2005 Sampleswhich is
3 €“ To install the samples and sample databases from the SQL Server 2005 Setup, you need to click the Advanced button on the Components to Install page, then expand Books Online and Samples and select Samples. This only installs the Samples MSI on your machine. To complete the installation, you need to run the Samples MSI. You can do this from the Start menu: click All Programs, click Microsoft SQL Server 2005, click Documentation and Tutorials, click Samples, and then click Microsoft SQL Server 2005 Samples.
By copying the SQLEXPR.EXE to the directory containing the SQL .exe I can get to "SQL Server 2005 Express Setup" (note it is not SQL Server 2005 Setup) using CHANGE from ADD/REMOVE programs; however, there is no Advanced button so I can't complete step three.
All the databases and tuturials seem to be in the right directories. I have explored SSME and the config utility and do not see anyway to do this using them.
Has anyone installed and used the sample databases?
I am trying to run the Readme_HelloWorld sample on the SQL server 2005 Books online. But don't know where I can get the setup.sql, SendMessage.sql, ReceiveMessage.sql scripts.
I followed two examples on the blog, but they did not work at all. Here are the scripts:
/* example 1 */ create database TestSB go use TestSB go create queue TxQ create queue RxQ create message type Msg create contract MsgContract(Msg SENT BY ANY) create service TxSve on Queue TxQ Create service RxSve on Queue RxQ declare @h uniqueidentifier begin dialog conversation @h from service TxSve to Service 'RxSve' on contract MsgContract; send on conversation @h Message type Msg ('<hello>World</hello>') end conversation @h with CLEANUP;
select * from RxQ
/* example 2 */ use testsb go
create message type HelloMessage validation = NONE go
/** 2. create contract **/ create contract HelloContract (HelloMessage SENT by initiator)
begin begin transaction; begin dialog conversation @handle from service sender to service 'receiver' on contract HelloContract set @message = N'Hello, World'; send on conversation @handle message type HelloMessage(@message) end conversation @handle commit transaction end go
Receive convert(nvarchar(max), message_body) as message1 from ReceiverQueue
select convert(nvarchar(max), message_body) as message1 from ReceiverQueue
select * from SenderQueue
select * from Receiverqueue
Any assistance or WORKING samples are greatly appreciated.
I have installed SQL Server 2005 on Windows Server 2003 Standard SP1. THe SAMPLES folder within the C:/Program Files/Microsoft SQL Server/90 folder is missing. I installed SQL Server 2005 SP2 thinking that this might create the SAMPLES folder, but it didn't get created.
Please let me know what should I do to create it. Thanks in advance.
Ultimately, the above query will need to be exported to Excel using VB and I looked at pivot tables, but no result will allow me to generate a query in the following fashion.
The analyte needs to be listed only once, and if there are results, they will need to be displayed from left to right since the analyte will be listed just on a row, the number of samples go from left to right. Please note, I have no way of knowing how many samples will be listed it could be 3 or 20. Thanks kindly for your help on this, I have worked on it for a week already and looked at doing it from VB, but that got more complicated than it needs to be.
Hi every body! I'm doing a topic about "Merge raplication". But i can't find a sample script in : C:Microsoft SQL Server80ToolsDevtoolsSamplessqlrepldeflt_sp
If anybody has it, please sent it to me! Please! Thanks a lot! My enail: vinhphuocptit@gmail.com
"By default, the Integration Services samples are installed onto the local hard disk drive in the following folder: C:Program FilesMicrosoft SQL Server90Samples"
But there is not "Samples" dir inder C:Program FilesMicrosoft SQL Server90
the book "microsoft sql server 2005 integration services" by kirk haselden claims you can download examples and source code at www.samspublishing.com
er no
what happens is this
go to the site look for the 'downloads' link see there isn't one search for authors h etc.... lose a few minutes of your life find the book
yay
it forces you to sign up for an account in order to get the samples - outrageous
prepare for endless spam from sams
sign up
click on download samples find the configedit folder load into vs run it it doesn't work!!
thanks
Warning 1 The referenced component 'SourceLibrary' could not be found. Warning 2 The referenced component 'SourceGrid2' could not be found. Error 3 Unable to find source file 'K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigurationsEditorinDebugConfigurationsEditor.exe' for assembly 'ConfigurationsEditor.exe', located in '[TARGETDIR]' K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditSetupConfigEditSetupConfigEdit.vdproj SetupConfigEdit Error 4 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 9 7 ConfigEdit Error 5 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 10 7 ConfigEdit Error 6 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 87 40 ConfigEdit Error 7 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 11 15 ConfigEdit Error 8 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 24 17 ConfigEdit Error 9 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.cs 25 11 ConfigEdit Error 10 The type or namespace name 'SourceGrid2' could not be found (are you missing a using directive or an assembly reference?) K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlConfigEdit.Designer.cs 318 17 ConfigEdit Error 11 Metadata file 'K:MASSoftware and ToolsSamplesSSISKirk Haselden bookSamplesSRCUtilitiesConfigEditConfigEditControlinDebugConfigEdit.dll' could not be found ConfigurationsEditor Warning 12 Could not resolve this reference. Could not locate the assembly "SourceGrid2". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. ConfigEdit Warning 13 Could not resolve this reference. Could not locate the assembly "SourceLibrary". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors. ConfigEdit
one could argue that ssis should have a config editor supplied with it, instead of having to go into notepad
I'm looking for the sample application : IBuySpy Delivery Sample Application (SQL Server Compact). But I could not find this sample file anywhere. The only sample which is available in my local directory below is Northwind.sdf file C:Program FilesMicrosoft SQL Server Compact Editionv3.5Samples
The link in the Book Online is pointing to this URL below : http://www.microsoft.com/downloads/details.aspx?FamilyID=1ff0529a-eb1f-4044-b4b7-40b00710f7b7&displaylang=en
I have installed the documentation and samples and the management studio for sql 2005 express edition, but when trying to work through the samples, I get this error message, for some reason I can't include the Adventure works db. Whatn I'm I not doing right? Please can someone help!
1> CREATE DATABASE [AdventureWorks] ON 2> (AdventureWorks = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData < AdventureWorks>.mdf' ), 3> (AdventureWorks = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData < AdventureWorks>.ldf' ) 4> FOR ATTACH ; 5> GO Msg 153, Level 15, State 1, Server YOUR-V7OY5L24PGSQLEXPRESS, Line 2 Invalid usage of the option AdventureWorks in the CREATE/ALTER DATABASE statemen t. 1> USE [master] 2> GO Changed database context to 'master'. 1> CREATE DATABASE [AdventureWorks] ON 2> (AdventureWorks = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData < AdventureWorks>.mdf' ), 3> (AdventureWorks = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData < AdventureWorks>.ldf' ) 4> FOR ATTACH ; 5> GO Msg 153, Level 15, State 1, Server YOUR-V7OY5L24PGSQLEXPRESS, Line 2 Invalid usage of the option AdventureWorks in the CREATE/ALTER DATABASE statemen t. 1> select name from sys.databases 2> go name
What are the AdventureWorksDBCI.ms and AdventureWorksBICI.msi used for? I try to run it, but it return the message "Another version of this product is already installed" and ask me remove the old version of this product. Anybody know what I should remove?
I would like to have more info on UserInstances concept of SQLExpress 2005.
Can i find any code samples for this feature in MSDN.
I have tried using the following links.. http://msdn2.microsoft.com/en-us/library/ms143401(SQL.90).aspx http://msdn2.microsoft.com/en-us/library/ms143684(SQL.90).aspx http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp http://msdn2.microsoft.com/en-us/library/ms254504.aspx http://msdn2.microsoft.com/en-us/library/ms165690(SQL.90).aspx http://msdn2.microsoft.com/en-us/library/ms143446(SQL.90).aspx
any other links will be helpful for me in this regard.
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
ok, I've installed sql server, run the install samples and database, but when I go into SQL Server, the database and samples are not there. How do I connect the two?
