Editing Tables & Writing Custom Stored Procedures
Feb 26, 2007
Hi all,
I’m currently writing a web application on student exam timetables, I’m using SQL Server 2005 as the back-end for the database.
At present, the case states that if a student is in one examination, he/she can’t attend, or be allocated another examination while the first examination is in place, which would result in a clash
The way I’m going to target this is by writing a stored procedure in SQL Server 2005 to return an error code, which I’ll translate using ASP,NET, however at present I’m having difficulty writing the SQL code. This is because…
I’m using SQL Server 2005 Management Studio; I created the tables using MS Access and upsized them using the wizard. I can now access my database, but having difficulty editing my tables and with code…
Any ideas??
Thank-you
View 2 Replies
ADVERTISEMENT
Mar 29, 2006
I'm not a "real" programmer. Excuse my ignorance.
I need to edit a stored procedure. I simply want to change the text that it displays. I do not intend to change its function.
I am using SQL Express and SQL Server Management Studio Express. I can select Modify for my stored procedure, but when I save it, it saves it as an SQL file to my hard disk. How do I affect the edits to the actual stored procedure?
View 1 Replies
View Related
Jan 5, 2004
Am i the only one who is forced to open multiple instances of SQL Server Enterprise Manager in order to edit more than one stored procedure at a time? A lot of times i'll be comparing two sprocs or writing one based off of a "template" i wrote earlier. But i have to open two instances and alt-tab between them. Is there an easier way? (aside from pasting it into a notepad and using that)
View 5 Replies
View Related
Nov 28, 2007
Greetings
Running SQL Server 2005. The developers on the project can see and edit stored procedures from within the Visual Studio IDE (via Server Explorer) but when they connect through management studio, the stored procedures do not show up at all.
Is there a seperate security setting specifically for management studio?
The user has:
The dbCreator Server Role
Is mapped to the development database as dbo with datareader/datawriter/db owner/public role
Is mapped to master reader/writer/public
Is mapped to model reader/writer public
Is mapped to msdb reader/writer public
Is mapped to tempdb reader/writer publuc
This is probably more security than the user needs, but was grasping at straws to let them edit stored procedures...
View 4 Replies
View Related
Jun 17, 2008
Sorry, not sure if this is the right forum for this but hopefully you may be able to give me an answer.I have a website which fetches data from a SQL Server database using stored procedures, so I only grant execute rights to the proceduresto avoid granting select rights to the tables. This has been working fine but I would like to try writing this code in C# instead. I have writtenstored procedure that runs a select to test, and I am getting the error that the user has no rights to do select over the table.My question is, is the code writen this way (in C#) considered equivalent to dynamic SQL (which would require select rights over the tables)?Would this mean that I want to code my procedures in C# I need to grant select privileges over the tables to the db user?Thanks for your time
View 12 Replies
View Related
Nov 9, 2006
My TableAdapter wizard will not write the update and delete stored procedures and I do not know why. Any thoughts?
View 1 Replies
View Related
Jul 23, 2005
Dear All,Please suggest some of the best practices for writing SQL serverstored procedures?I'm writing a business function (stored procedure), which callsmany-stored procedure one after another.I want this to be best optimized, so that speed can be very good.Suggestion in this regard will be appreciated.Thanks in advance,T.S.Negi
View 1 Replies
View Related
May 25, 2006
I have a database schema that has an Address table used to store addresses for different entities such as Customers and Employees. I want to reuse the same Address record between different Customers and Employees without duplicating any address information. I'm not sure what the best approach might be.
Should have I have seperate stored procedures on the Address table that update and insert new addresses, where each Address record remains immutable once created? (So the update stored procedure actually creates a new Address record if the data changes). These stored procedures would then be invoked by business logic and used in tandem with stored procedures that act on Customers and Employees to ensure that no address records are duplicated.
Or should I create a view on a Customer joined with Address, and similarily with Employee and Address, and have stored procedures that act on these views and ensure that no Address records are duplicated. Should I use instead of triggers to override the behavior of insert and update on the view to achieve these?
I'm rather lost as to what direction I should take. Any help would be much appreciated, thanks!
View 1 Replies
View Related
May 14, 2008
I just got all of my data ported from DBF Files to my database in SQL. Now I'm writing stored procedures (trying).
I have a table with the following Columns. I'm using SQL Management Studio.
EmployeeName, Date, Punch(minutes after midnight), Type of Punch, Department
EmployeeA, 5/1/08, 479, RI, Finance
EmployeeA, 5/1/08, 720, MO, Finance
EmployeeA, 5/1/08, 780, MI, null
EmployeeA, 5/1/08, 1020, RO, null
EmployeeA, 5/2/08, 480, RI, Finance
EmployeeA, 5/2/08, 722, MO, Finance
EmployeeA, 5/2/08, 770, MI, null
EmployeeA, 5/2/08, 1019, RO, null
EmployeeA, 5/3/08, 477, RI, Finance
EmployeeA, 5/3/08, 730, MO, Finance
EmployeeA, 5/3/08, 800, MI, null
EmployeeA, 5/3/08, 1031, RO, null
EmployeeB, 5/1/08, 492, RI, Finance
EmployeeB, 5/1/08, 722, MO, Finance
EmployeeB, 5/1/08, 776, MI, null
EmployeeB, 5/1/08, 1022, RO, null
EmployeeB, 5/2/08, 482, RI, Finance
EmployeeB, 5/2/08, 730, MO, Finance
EmployeeB, 5/2/08, 760, MI, null
EmployeeB, 5/2/08, 1011, RO, null
EmployeeB, 5/3/08, 480, RI, Finance
EmployeeB, 5/3/08, 725, MO, Finance
EmployeeB, 5/3/08, 775, MI, null
EmployeeB, 5/3/08, 1020, RO, null
Type of punch is going to be RI - Regular IN, RO - Regular Out, MI - Meal In, or MO - Meal Out. I need a stored procedure that for each date it takes that person's ((RO - MO)+(MI - RI))/60 to get their Hours worked so that in the end, I have something that looks like.
EmployeeA, 5/1/08, 8(hrs)
EmployeeA, 5/2/08, 8.1(hrs)
EmployeeA, 5/3/08, 8.3(hrs)
EmployeeB, 5/1/08, 7.7(hrs)
EmployeeB, 5/2/08, 7.9(hrs)
EmployeeB, 5/3/08, 8.1(hrs)
I'm new to SQL Stored Procedures, but not to coding. Any hints, links, code that can get me on the right track?
Thanks in advance!!
Alternatively I think it's bad practice, but for ease of display, is it possible to set the columns up such that:
Employee, Department, 5/1/08, 5/2/08, 5/3/08
EmployeeA, Finance, 6, 6.1, 5.2
EmployeeA, Marketing, 2, 1.9, 3.8
EmployeeB, Finance, 4.9, 2, 6.1
EmployeeB, Operations, 3.1, 6, 1.9
In this example each employee can work in multiple departments. Also, each day that the database is updated, the new date is added as a column. Thanks!
-Curt
View 18 Replies
View Related
Oct 20, 1998
Has anyone used custom stored procedures with multiple tables? or can you only use them with one.
View 1 Replies
View Related
Apr 29, 2008
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'
View 1 Replies
View Related
Mar 26, 2008
Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
View 9 Replies
View Related
Jul 20, 2005
SQL Server 2000:Question 1If you drop / rename a table and then recreate the table with the SAMENAME, what impact does it have on stored procedures that use these tables?From a system perspective, do you have to rebuild / recompile ALL the storedprocedures that use this table?I had a discussion with someone that said that this is a good idea, sincethe IDs of the tables change in sysobjects and from a SQL SERVER query planperspective, this needs to be done...Question 2If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if anerror occurs? Will it Rollback? The theory is that it won't becauseTruncate doesn't utilize the logs where as Delete From uses the SQL Logs?Thanks!
View 3 Replies
View Related
Nov 27, 2006
Hello everybody,
I am new to .NET programming and the C# language. I'm coming from a ColdFusion/Java background, but am trying to learn .NET to further my abilities as a programmer, and take advantage of the CLR functionality in SQL Server.
What I am wondering is: is it possible to write an assembly which has stored procedures and functions that each utilize a custom class written within that assembly?
I am having some trouble with this concept. I am writing a UDF that will compile various data together and build a string from it. To help simplify the code, I want to build a custom class which will represent the primary object I am running the compilations against.
I would like to have the class be able to load its data directly from the database using a load() method. However, I am getting the following error when trying to run the UDF:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user defined routine or aggregate 'CompileList':
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at vtrenz_listmanager_package.lib.iMAList.load()
at UserDefinedFunctions.CompileList(Int32 ownerid, Int32 userid, Int32 listid)
Any help and/or insight would be greatly appreciated.
Thanks,
JD
View 6 Replies
View Related
Oct 19, 2007
I have been stuck in a problem for a while now. I am trying to count the number of groups. I tried soo many things and nothing worked . Now I want to try to write a function that does the counting for me.
Can somebody write me an example of a cutom code that can be called from expression.
So my custome code should be something like that
Public int CountFun(int counter)
{
Return counter= counter +1;
}
Also how do I call this method from my expression?
i am going to call it from a field inside the groups, so each time the group is implemented i add one to the counter.
thanks
View 4 Replies
View Related
Mar 28, 1999
When replicating a table which has an identity column I get the error: "Procedure cp_insert_tblname expects parameter @C1, which was not supplied.". The stored procedure appears to be called without any parameters so my insert stored procedure does not work. I know I'm missing something basic here!! Do I have to add the field names when telling replication to use a custom stored procedure. If not, how do arguments get passed to my SP, as globals somehow?
Any info greatly appreciated!!
Thanks,
Jay
View 1 Replies
View Related
Apr 28, 2007
Hi,I'm trying to insert some values into 2 tables using stored procedures (which should be pretty straight forward) but I'm running into problems.Given below are my 2 sp that I'm using:
View 5 Replies
View Related
Feb 14, 2008
hi I'm working in VS2005 with SQL server Express database.How can I copy some tables, stored procedures and diagrams from one database to another? thanks
View 3 Replies
View Related
Feb 14, 2006
Hello all!
What I need to do is take a site that I have and make 3 copies of it so I will have 4 separate sites with 4 separate DB's but running on the same server. One of the sites is complete but what I need to know is how do I make a complete copy of the DB including all stored procedures and populate a blank DB that has a different name with contents from the master DB???
So if DB1 is complete and I want to now populate DB2, DB3 and DB4 with everything from DB1 (tables, stored procedures, data etc.) what would be the best way to do this?
The new sites are already setup in IIS and I have already transferred the files to each sites root, so all that is left is to setup the new DB's. They are all running on the same server… I think that is about everything someone would need to know to help me!
Any help would be greatly appreciated!!!
View 3 Replies
View Related
Mar 2, 2006
Hi,
I am hoping someone can help me with creation of a stored procedure. What i would basically like to do is have a SELECT stored Procedure that gets values from one table and then uses these values to retrieve data from another table.
i.e. I would like to retrieve 2 dates from another table (the table has a single record that holds the 2 dates) then I would like to use those two dates to retrieve all records of another table that fall between those 2 dates.
As I know nothing about stored procedures I'm not even sure how to go about this??
Regards.
Peter.
View 2 Replies
View Related
Jan 17, 2008
Is there any oen can tell me how i can run some system sp or exec something to list all tables or stored procedures in a database, like i use select statement to list some coulmn data? thanks in advance.
View 3 Replies
View Related
Sep 3, 2007
Hi,
I am using multiple stored procedures which are using same set of tables at a time .
As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing.
My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.
as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins
where i want to take just 1 min which is the time took by second sp
I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time
Please suggest me how to proceed here.i am stuck
what should i do with the tables or stored procedures?
Thank you
Raj Deep.A
View 4 Replies
View Related
Nov 23, 2007
If you use a stored procedure that references one or more temp tables as data dource for a report, you get an error saying that the temp tables cannot be found when you click on the Layout tab. This happens even if you have executed the query in the Data tab before going to the Layout tab. The work around is to simply ignore the error but it is a distraction for the user. Is this a known big that is going to be fixed in a future release?
View 3 Replies
View Related
Sep 3, 2007
Hi ,
I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.
So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.
what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable
can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.
please help me here,i am stuck
Thank you
View 2 Replies
View Related
Jan 23, 2007
Does anyone know
if the following sql view is possible to write and execute as a view script?
**********
find employee matching the given UserID************* SELECT * FROM Employees WHERE EmployeeID=@UserID
***********
find client matching the given ClientID********** SELECT *
FROM Clients WHERE ClientID=@ClientID
**********find
all contacts and events associated with ClientID********* SELECT *
FROM Contacts WHERE Contact.ClientID=@ClientIDSELECT *
FROM Events WHERE Event.ClientID=@ClientID
*********select
all audits with Key values matching the primary keys of each client, contact or
event*********SELECT *
FROM Audit Where Key In (Client.ClientID, Contact.ContactID, Event.EventID)
I basically
need to find a employee based on its ID. Then I need to find any records from the table Audit
with Key values matching the given fields in the results of any clients, contacts events that were returned from the previous select statements. Is this possible?
View 5 Replies
View Related
Sep 12, 2006
Hi,Is there a way I can change schema name on tables and stored procedures? How do I do this?I´m very news to SQL and .netThanks
View 1 Replies
View Related
Dec 20, 2007
Can a SqlDataSource or a TableAdapter be attached to a stored procedure that returns a temporary table? I am using Sql Server 2000.
The SqlDataSource is created with the wizard and tests okay but 2.0 controls will not bind to it.
The TableAdapter wizard says 'Invalid object name' and displayes the name of the temporary table.ALTER PROCEDURE dbo.QualityControl_Audit_Item_InfractionPercentageReport
@AuditTypeName varchar(50), @PlantId int = NULL,
@BuildingId int = NULL, @AreaId int = NULL,
@WorkCellId int = NULL, @WorkShift int = NULL,
@StartDate datetime = NULL, @EndDate datetime = NULL,
@debug bit = 0 AS
CREATE TABLE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable (ItemHeader varchar(100), Item varchar(250), HeaderSequence int, ItemSequence int, MajorInfractionPercent money, MinorInfractionPercent money)
DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000)
SELECT @sql = '
INSERT INTO #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
(ItemHeader, Item, HeaderSequence, ItemSequence, MajorInfractionPercent, MinorInfractionPercent)
SELECT DISTINCT QualityControl_Audit_Item.Header,
QualityControl_Audit_Item.AuditItem,
QualityControl_Audit_Item.HeaderSequence,
QualityControl_Audit_Item.AuditItemSequence,
NULL, NULL
FROM QualityControl_Audit INNER JOIN
QualityControl_Audit_Item ON QualityControl_Audit.QualityControl_Audit_Id = QualityControl_Audit_Item.QualityControl_Audit_Id
WHERE (QualityControl_Audit.AuditType = @xAuditTypeName)'
IF @PlantId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.PlantId = @xPlantId'
IF @BuildingId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.BuildingId = @xBuildingId'
IF @AreaId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.AreaId = @xAreaId'
IF @WorkCellId IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkCellId = @xWorkCellId'
IF @WorkShift IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.WorkShift = @xWorkShift'
IF @StartDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed >= @xStartDate'
IF @EndDate IS NOT NULL SELECT @sql = @sql + ' AND QualityControl_Audit.DateAuditPerformed <= @xEndDate'SELECT @sql = @sql + '
ORDER BY QualityControl_Audit_Item.HeaderSequence, QualityControl_Audit_Item.AuditItemSequence'
IF @debug = 1 PRINT @sqlSELECT @paramlist = '@xAuditTypeName varchar(50), @xPlantId int, @xBuildingId int, @xAreaId int, @xWorkCellId int,
@xWorkShift int, @xStartDate datetime, @xEndDate datetime'
EXEC sp_executesql @sql, @paramlist, @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDateDECLARE my_cursor CURSOR FOR
SELECT ItemHeader, Item, MajorInfractionPercent, MinorInfractionPercent FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
OPEN my_cursor
--Perform the first fetchDECLARE @ItemHeader varchar(100), @Item varchar(250), @MajorInfractionPercent money, @MinorInfractionPercent money
FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent
--Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--Major
EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Major', @debug, @InfractionPercent = @MajorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
SET MajorInfractionPercent = @MajorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item))
--Minor
EXEC dbo.QualityControl_Audit_Item_InfractionPercentageReport_CalculateForMajorOrMinor @AuditTypeName, @PlantId, @BuildingId, @AreaId, @WorkCellId, @WorkShift, @StartDate, @EndDate, @ItemHeader, @Item, 'Minor', @debug, @InfractionPercent = @MinorInfractionPercent OUTPUTUPDATE #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
SET MinorInfractionPercent = @MinorInfractionPercentWHERE (((ItemHeader IS NULL) AND (@ItemHeader IS NULL) OR (ItemHeader = @ItemHeader)) AND (Item = @Item))
FETCH NEXT FROM my_cursor INTO @ItemHeader, @Item, @MajorInfractionPercent, @MinorInfractionPercent
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT * FROM #QualityControl_Audit_Item_SelectDistinctByFilters_TempTable
View 8 Replies
View Related
Apr 8, 2008
Hello there!
I've been sitting here thinking how to work through this problem but I am stuck. First off I have two tables I am working with.
Request TablerqstKey (this is the primary key and has the identity set to yes)entryDte datetimesummary nvarchar(50)etryUserID nvarchar(50)rqstStatusCdeKey bigint
ReqStatusCode TablerqstStatusCdeKey bigintstatusCode nvarchar(50)
I have webforms that are using Session variables to store information from the webpages into those variables and insert them into these tables. However, I am wanting to insert the rqstStatusCdeKey (from the ReqStatusCode Table) value into the Request Table. The rqstStatusCdeKey value i am wanting to insert is '3' which stands for "in Design" (((this is going to be the default value))). I need help :-/ If you need me to explain more/ clarify, and show more information I can. Thank you so much!!!
View 4 Replies
View Related
May 19, 2004
Hi,
I was just wondering if something could be explained to me.
I have the following:
1. A table which has fields with data types and lengths / sizes
2. A stored procedure for said table which also declares variables with datatype and lengths/ sizes
3. A function in written in VB .net that uses said stored procudure. The code used to add the parameters to the sql command also requires that i give a data type and size.
How come i need to specify data type and length in three different places? Am i doin it wrong?
Any information is greatly appreciated.
Thanks
Im using SQL Server 2000 with Visual Studio .Net using Visual Basic..
View 1 Replies
View Related
Feb 12, 2008
Are they unique to a user/session? Like if 2 users simultaneously run the stored procedure?
TIA!
View 13 Replies
View Related
Nov 18, 2005
Hello, I am a beginner using MSDE & Visual Basic.NET standard version (not Visual Studio.NET) . MSDE was downloaded from microsoft.com yesterday, which is sql2ksp3.exe.
View 15 Replies
View Related
Feb 12, 2008
Hello Friends!!
I have a great problem !!
I have a database having around 50 tables and around 70 t0 100 stored procedures.
due to certain reason name of all the table and procedure was prefixed with "cls" ,
now i want to rename them by "tbl_" using some query !! in bulk ,
and tables have relation ships.
so how to rename them !!
is there any way that I can rename them by modifying data in any system table !?
View 6 Replies
View Related
Apr 21, 2006
I converted a program from SQL 2000 to SQL 2005 all went well. I created a number of tables and stored procedures after the conversion. I backed up my .mdf and .idf files. I was having problems with SQL so I uninstalled and re-installed it. Once I re-installed it I could no longer display some tables and files. Since I am the dbo, I think I should be able to access them. There obviously is something I am missing, hopefully not the tables and sps.
I would appreciate any suggestions.
Thank you.
LitePipe
View 4 Replies
View Related
