Tables And Stored Procedures
Jul 20, 2005
SQL Server 2000:
Question 1
If you drop / rename a table and then recreate the table with the SAME
NAME, what impact does it have on stored procedures that use these tables?
From a system perspective, do you have to rebuild / recompile ALL the stored
procedures that use this table?
I had a discussion with someone that said that this is a good idea, since
the IDs of the tables change in sysobjects and from a SQL SERVER query plan
perspective, this needs to be done...
Question 2
If you Truncate a Tables as part of a BEGIN TRANSACTION, what happens if an
error occurs? Will it Rollback? The theory is that it won't because
Truncate doesn't utilize the logs where as Delete From uses the SQL Logs?
Thanks!
View 3 Replies
ADVERTISEMENT
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
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
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
Jun 3, 2006
There are two ways to create a temporary tables in stored procedures
1: Using Create Table <Table Name> & then Drop table
ex. Create Table emp (empno int, empname varchar(20))
at last : drop table emp
2. Using Create table #tempemp
( empno int, empname varchar(20))
at last : delete #tempemp
---which one is preferrable & why.
what are the advantages & disadvantages of the above two types.
View 5 Replies
View Related
Sep 3, 2007
I am learning T SQL and SQL queries and have limited VB knowledge, and have a some simple queries to run on a table with parameters, and would like verification of the proposed methodology and suggestions.
Simply put, I have a [Transactions] table with columns [Price], [Ticker], [TransDate], [TransType] and calculated columns for [Days] and [Profit].
There are two parameters, [@Dys] (to query a the table for transactions within a certain period[Days = 30] and [@TT] to query only the closed transactions ie... [TransType='C']
I have been studying Stored Procedures and will be writing a Stored Procedure, but need verification if the following will work... Getting the SUM and AVG calcluations for the fields above is not a problem but I need to display SUM and AVG information also for those transactions where [Profit >0] and [Profit <0], which is easy enough by creating a subquery. But the problem is:
1. If I use a SubQuery for [Profit <0] and for [Profit>0], can I create an alias for [Count(*)] (to get a row or transaction count for each, and then divide that into the Total [Count(*)] alias for the Transactions table to get a value for % profitable or Probability (% total Profitable trades versus % total Unprofitable trades)?
2. Or, do I need to create either temporary tables or views to have 3 distinct tables (1 table for Transactoins and 2 temp or Views for [Profit >0] and [Profit <0])?
Any suggestions and advice or examples on how to do this would be appreciated.
Craig
My questions are:
View 2 Replies
View Related
Oct 5, 2004
Hi My friends
I have develop big Database System in Microsoft SQL Server 2000 and my DB System content more then 30 Table and also more then 150 Stored procedure and from that stored procedures I have call my the tables
But the problem when I change the name of any table, SQL Server don’t prompt my about the stored procedure that use that table .. so I cant remember witch Stored procedure use that table
By example if I am in programming language like C# or VB.NET when I try to change any name of any class the compiles catch all the error that coming from changing the name of the class and simply I will fix the problem because the compiler help me and list all the object content the error
But in SQL Server how can I search for stored procedure that use un existing table like compiler in any programming language ??
Or is there any why to solve my problem ??
And the above problem also there when I change the filed name
And thanks with my best regards
Fraas
View 1 Replies
View Related
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
View Related
Feb 20, 2007
Hi all--I used the following type of script to migrate tables, views and stored procedures from 'dbo' to 'hqmsuser':
alter authorization on dbo.PK_T_PQ_MASTER to HQMSUSER
alter schema HQMSUSER transfer dbo.PK_T_PQ_MASTER
go
This was per recommendations from the BOL to use instead of the 'sp_changeobjectowner' procedure. I returned the following error:
Msg 15346, Level 16, State 1, Line 3
Cannot change owner for an object that is owned by a parent object. Change the owner of the parent object instead.
Msg 15347, Level 16, State 1, Line 4
Cannot transfer an object that is owned by a parent object.
How do I find and change the parent object on this from 'dbo' to a non-dbo schema ownership?
View 1 Replies
View Related
May 21, 2008
Hi Folks,
I am writing a program that transforms a generic MS SQL database to make it compatible with our application. In order to make the transformation, I have to access all the elements of the generic database programmatically.
I know that the Master database contains all this information. Is there a query that allows me to access the "non-system" tables, sps, views, and functions?
For Tables, I need to know the Name(s) of the tables, the column names, the column type, ALLOW Nulls, Primary Key, Identity Seed settings, and Triggers.
For SPs, I need to know the Name(s) and access the SP source code (assuming it's unencrypted).
For Views, I need to know the Name(s) and access the Views Source
For functions, I need to know the Name(s) and access the function source.
I can see various tables in the Master database from management studio, like sys.objects, sys.tables - which look like they have the info I need. However, when I run a query against master like:
select * from sys.objects .. I get an error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Thank you in advance.
View 13 Replies
View Related
Nov 9, 2007
Hi,
I've a doubt regarding deploying and creating package (MSI and EXE files) for SQL Stored procedures,views,tables,functions,triggers etc.,just like ASP.NET application by publishing and deploying..Is it possible in the same way for sql objects.If so,pls help me how to do it?
Thanks in advance.
View 1 Replies
View Related
Jun 15, 2015
But it doesn't explicitly tell wherther Interpreted (disk-based) tables can be accessed by Natively compiled stored procedures.And SQL Server Express edition doesn't allow creating Memory-optimized objects to very this.
View 2 Replies
View Related
Feb 13, 2006
Hi all,
I am using Visual web developper 2005 with sql server express 2005 and i have also sql server management studio express. it's all free now .
my web site is ready
I didn't have problem to upload my site to my hoster.
Now I want to upload all my tables and my stored procedure create locally with VWD express
How can i do it ?
NB: I know i can't design DB (create/modify tables and stored proc) with express edition
thank's for your help
View 1 Replies
View Related
Sep 10, 2014
I have created one table and one stored procedure for to insert/delete/update the data in that table.
So,I was trying to move the scripts from one database to another by Generating Scripts options in SQL Server.
Generating Scripts:
Object Explorer --> Databases --> Database --> Tasks --> Generate Scripts
The generated script output is in a order of stored procedure first and then table.
REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.
Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.
View 6 Replies
View Related
Apr 3, 2015
Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?
View 4 Replies
View Related
Jul 23, 2005
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!!
View 11 Replies
View Related
Mar 5, 2008
This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn
View 9 Replies
View Related
Sep 30, 2006
Hi,
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.
Thank you in advance for any help on this matter
View 1 Replies
View Related
