Hey guys, has anyone ever seen this happen:
Try to move stored proc from one DB to another using DTS, errors on create proc. Create proc manually.
Three tables referenced by that stored proc have been dropped and re-created with the same table structure.
I'm not 100% certain that it happened at exactly the same time, but it seems to be around the same time. Any ideas? Anyone seen this happen before?
Hi
ONLY ONE of our many stored procedure is getting dropped in SQL 7.0. We were not able to find thru SQL Profiler. Let me know any alternate methods to find it or the possible reasons why/how the sp is getting dropped.
Thanks
Hi everybody, I am having trouble how to fixed this code. I am trying to supply the parameterinside a stored procedure with a value, and displays error message shown below. If I did not supply the parameter with a value, it works. How to fix this?Error Message:Procedure or function <stored proc name> has too many arguments specified.Thanks,den2005
Stored procedure:
Alter PROCEDURE [dbo].[sp_GetIdeaByCategory]
@CatId <span class="kwd">int</span> = 0
AS
BEGIN
SET NOCOUNT ON;
Select I.*, C.*, U.* From Idea I inner join IdeaCategory C on I.CategoryID = C.IdeaCategoryID inner join Users U on I.UserID = U.UserID Where I.CategoryID = @CatId Order By LastModifiedDate Desc
End
oDataSource.ConnectionString = constr;
oDataSource.SelectCommand = storedProc;<span class="cmt">//storedproc - sp_GetIdeaByCategory</span>
oDataSource.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
oDataSource.SelectParameters.Add(<span class="st">"@CatId"</span>, catId);
gdvCategories.DataSourceID = oDataSource.ID;
gdvCategories.DataBind(); <<--- Error occured here
I'm running SQL server 2000 sp1. I created a stored procedure that (1) drops a table, (2) recreates it with a "select into" statement, (3) alters the table by adding a field, and then (4) updates that field.
The trouble I'm having is that when I execute the stored procedure I get an error stating that I have an "invalid column name" between steps (2) and (3). It seems as though when I drop the table in step (1), the entire procedure wants to re-compile and it can't get past step (4) because the table hasn't been altered yet.
I've noticed a similar problem in editing stored procedures when they refer to tables or fields that don't exist yet because WITHIN the procedure they are created/modified. I'm not able to get a successful syntax check and therefore not able to save my work.
I must be missing something. Any suggestions?
We have around 5 SP’s which are inserting data into Table A,and these will run in parallel.From the temp tables in the SP,data will be loaded to Table A. We are getting deadlock here.No Begin and End Transaction used in the stored procedure.
What could be done to avoid deadlock.
I've got a weird one here. I'm running a DTS package on SQL Server2005. It copies a bunch of stored procedures. I renamed them on theoriginating server and ran the DTS again.The came over with the old name and code!I deleted the DTS and built it from scratch, and the same thinghappened.I ran SELECT * FROM sys.objects where type = 'P' on the source serverand the names were correctI'm explicitly checking which sp to copy rather than using Copy all. Ican see the sp namesI've deleted and recreated the sp on the source server using scriptsI've checked the source server nameI've Refreshed everywhereNothing worksWhy is up_Department_GetAllBySchool trying to be be pulled over whenit doesn't exist?Why is up_Department_GetBySchool not being pulled over when it doesexist?I've heard that SQL 2005 pre-SP2 has a problem where renaming anobject that has a text definition (like sprocs, functions, triggers,views) doesn't update the definition. So if you pull that objectdefinition and run it into your new database, it will use the originalscript, which has the original name.I ransp_helptext 'up_Department_GetBySchool'and checked the CREATE statement at the top. Sure enough, it had theold textI asked our NetAdmin to install SP2 on our server. Then I ransp_refreshsqlmodule 'up_Department_GetForSchool'and got this error:Invalid object name 'up_Department_GetAllForSchool'.So even the code which was supposed to fix it, doesn't. Has anyoneelse had this problem, and managed to fix it?--John Hunter
View 1 Replies View RelatedI'm using SQL Serv 6.5. I've got a stored procedure that is DEFINITELY not in use but i can't drop it because it's telling me it is ???
I don't want to stop and start SQL Serv. Any ideas ??
thanks
Paul
How can i delete a stored procedure if it exist
like "Drop stored procedure 'sp1' if exist"
We have a secuiry review and they have recommended dropping several xp_ stored procedures. I have tried the drop procedure with no luck, Error 3701, or right click delete in Man Studio, same error. I have granted the user alter permission to the master database and when I try to delete get Error 4606. I try to grant CONTROL permission of the stored proc to the user and get another 4606 error.
Do I just have to control the permissions of these procedures tightly?
Thanks In Advance
MPM
Hi,I found this SQL in the news group to drop indexs in a table. I need ascript that will drop all indexes in all user tables of a givendatabase:DECLARE @indexName NVARCHAR(128)DECLARE @dropIndexSql NVARCHAR(4000)DECLARE tableIndexes CURSOR FORSELECT name FROM sysindexesWHERE id = OBJECT_ID(N'F_BI_Registration_Tracking_Summary')AND indid 0AND indid < 255AND INDEXPROPERTY(id, name, 'IsStatistics') = 0OPEN tableIndexesFETCH NEXT FROM tableIndexes INTO @indexNameWHILE @@fetch_status = 0BEGINSET @dropIndexSql = N' DROP INDEXF_BI_Registration_Tracking_Summary.' + @indexNameEXEC sp_executesql @dropIndexSqlFETCH NEXT FROM tableIndexes INTO @indexNameENDCLOSE tableIndexesDEALLOCATE tableIndexesTIARob
View 2 Replies View RelatedI was doing a DTS import and accidently imported into the wrong database, dropping very critical tables. Please let me know how I can restore these transaction back. My last backup was 24 hours ago, and I just need to restore a few hours back.
Thanks,
KJ
If a stored procedure creates a temporary table and then has a batch-terminating error -- either a fatal error or a non-fatal error in "xact_abort on" mode -- the temp table does not get dropped even though it goes out of scope:
create proc px as
create table #z(z1 int primary key)
insert #z values ('x')
go
exec px
go
select * from tempdb.sys.tables where name like '#z%' --returns row for #z
drop table #z --fails because #z out of scope
or:
create proc px as
set xact_abort on
create table #z(z1 int primary key)
insert #z values (0)
insert #z values (0)
go
exec px
go
select * from tempdb.sys.tables where name like '#z%' --returns row for #z
drop table #z --fails because #z out of scope
Is this behavior by design? It does not appear to be documented properly if so, since BOL states that "Temporary tables are automatically dropped when they go out of scope".
In any case, is there a way to delete leftover temp tables from tempdb without restarting SQL Server?
Thanks,
Ron
I have writen a nested stored procedure when i am passing a datetime paramter from the first procedure into the internally called procedure it is dropping the time part.
EX;
Declare @pdate datetime
Exec sp1 @pDate<this is datetime> = '2013-10-19 14:00:00'
Create proc sp1
(@pdate datetime)
as
Begin try
Exec sp2 @pdate <this is the same parameter for sp1>
End try
Begin Catch
Select 'error'
end catch
Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
I have a very rubbish connection!! And have been having prodlems with "Protocol error in TDS stream" when trying to save a copy locally through a dts. Basically i have a table with about 11,000 rows, but this table had 28 columns. I've now normalised to a certain extent and now have 2 tables one of 11 columns mostly ints and one of 18 columns, but they will both be excess of 11,000 rows.
So, on to the question ... Will these 2 tables likely ease my network issues... The application works fine on 1 big table. It's just copying to my local machine that's always been the issue since the table hit 10,000 rows
If only I knew what I was doing ... Thanks in advance of any guidence
Does anyone have a script that can drop the Identity columns from all the tables in a database? Thanks
View 1 Replies View RelatedI am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you
ok, ill lay it out like this, and try not to be too confusing.
I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,
i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item
so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.
well i tried not to be confusing, but i think i failed
Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu
View 1 Replies View RelatedHi
I have one stored procedure which uses temp tables in it .
I am trying to use this stored procedure in my SSRS report as the source.
I am getting an error which says that the temporary table doesnot exist while validating the sql syntax in report.
Also, this stored proc executes in SSMS ithout any problem and giving the result as desired.
Can we use Temp tables in Stored proc and call that in SSRS?
If not, what is the workaround for that.
Thanks
Hi,
I was wondering if there was a way to create two temp tables within the same stored procedure.
Can we have two create statements one after the other?
Thanks
Hi,
I want write STORED PROCEDURE to join TWO tables.
I am displaying all the details here. Please help me.
INPUT DATA:
Table1
(OLD DATA)
SYMBOL CATEGORY SHARES Prev.Close Last Trade
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma
211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746
1181.25
799.00
115.25
843.00
1850.70
382.60
256.20
226.60
676.00
613.35
1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55
Table2
(NEW DATA)
SYMBOL LAST TRADE
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20
OUTPUT DATA:
(OLD DATA) (NEW DATA)
as
SYMBOL CATEGORY SHARES Prev.Close Last Trade
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma
211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746
1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55
1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20
Hi all,
I have created several global temp tables to cache some intermediate results ...
However, it seems that after a while those tables will be dropped by SQL Server 2005 automatically (I have not restarted the server and no drop table statement ever executed against those tables). Is this a feature by design? How to make those global temp tables persistence to next service restart?
Thanks,
Ning
I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReason� and “@WIP�.� I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.
I made up the two tables just for testing, they are:
tbltest
ECID – int (PK)
Im currently in the process of developing a new system in ASP.net. The system uses multiple tables from a SQL database. To link information from say the products table to the suppliers table a unique key from the suppliers table is stored in the products table to show which products each supplier has bought...simple so far.
From this I can then pull out any of the information relating to that product instead of just displaying the ID. Currently this is done by creating a stored procedure and then dragging the stored procedure onto the dataset layer to create the data adapter.
Im not 100% this is the best way to be doing this. Is it possible to simply just link the tables in the dataset layer to display a string from another table instead of an ID referencing number?!
any help on this would be much appreciated as I cant really continue untill I have sorted out the data structure problems.Thanks in advancedmowfo
I have two sets of related tables: Quote - QuoteDetail and Order - OrderItem
I need to copy Quote - QuoteDetail records to Order - OrderItem tables
I have the stored procedure up to this point: Insert a Quote in the Order table and get the new Order @@Identity.
I need to insert the QuoteDetail records into the OrderItem table using the new OrderID
Thank you for your help.
What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.
I am currently using SQL Server 2000 backend. Each one of these queries are
pretty complex so I created each query as a Stored Procedure. These queries are
dynamic by each user, so the results will never be the same globally.
What I have done so far was created a master stored procedure passing the
current user's username as a parameter. Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory. Each of the sub stored procedures all
have the same columns. After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable. I then bind the DataTable to a
Repeater.DataSource.
Problem: When the page is rendered, it returns nothing. I
tested the master SP in the data environment and it works fine.
Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource? Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?
Here is an example of the code from the master SP:
CREATE PROCEDURE dbo.TM_getAlerts @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )
INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username
INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username
SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts
GO
It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled. I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly. But the query in one of the
sub SP is a normal but complex select.
I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how.
View 1 Replies View RelatedI know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?
View 2 Replies View RelatedHi everyone
I am totally confused, please help me. I am new to this
I am trying to split one DB (A) into two databases original (A) and new-Blank(B) by moving some tables from DB (A) to the new DB (B) however some of the tables has FK and Stored procedures referencing other tables that need to stay in DB (B), The questions are
1. after scritping these tables while they are in DB (A), and runing the script in DB (B) to re-create them, do I delete these table from DB (A), and the FK that references them.
2. What shall I do with the stored procedures. Turn them into trigers or else if turn them into trigers, in which DB should the triggers run (DB A of DB B),if these are becoming triggers do I delete the stored procedures from DB (A)
Any reply is appreciated
I have several tables that are related with the same primary key that I need to make a consolidated list of values for. The schema of each table is similar but some have more or less fields than others. I want to make a list of all table names, field names, and the value of that field. I can select all tables included from sysobjects. The existence of the key is probable but not guaranteed in all tables. The basic table format is: Table1------ID Field1 Field2 Field31 0.0 4.1 3.92 0.5 1.3 0.23 7.1 8.8 9.3 Table2------ID Field10 0.41 3.32 2.73 5.7 Table3------ID Field1 Field22 2.4 4.63 4.3 8.1 Format of the result set:(specifying ID = 2) Table_Name Table_Field ValueTable1 Field1 0.5Table1 Field2 1.3Table1 Field3 0.2Table2 Field1 2.7Table3 Field1 2.4Table3 Field2 4.6
View 8 Replies View RelatedHello
I wonder if I can generate the "CREATE TABLE" sentence given the name of a table by means of a stored procedure.
Thanks a lot.