|
AlexeiXX3 wrote: Do i have to delete and recreate with new name?
That's the best way to do it in my view, but you should be able to use sp_rename if you don't like the idea of DROP/CREATE. Here's an example of both:
sp_rename
<br />
CREATE PROCEDURE myTestProc<br />
@Id int<br />
as<br />
<br />
SELECT @@SERVERNAME<br />
<br />
GO<br />
<br />
EXEC sp_rename 'myTestProc', 'myRenamedTestProc', 'OBJECT'<br />
<br />
IF OBJECT_ID(N'myTestProc') IS NOT NULL<br />
BEGIN<br />
SELECT 'Rename failed'<br />
DROP PROCEDURE myTestProc<br />
END<br />
IF OBJECT_ID(N'myRenamedTestProc') IS NOT NULL<br />
BEGIN<br />
SELECT 'Rename worked'<br />
DROP PROCEDURE myRenamedTestProc<br />
END<br />
DROP/CREATE
<br />
CREATE PROCEDURE myTestProc<br />
@Id int<br />
as<br />
<br />
SELECT @@SERVERNAME<br />
<br />
GO<br />
<br />
IF EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'myTestProc')) BEGIN<br />
DROP PROCEDURE myTestProc<br />
END <br />
GO<br />
<br />
CREATE PROCEDURE myRenamedTestProc<br />
@Id int<br />
as<br />
<br />
SELECT @@SERVERNAME<br />
<br />
GO<br />
<br />
IF EXISTS(SELECT * FROM sys.objects WHERE [object_id] = OBJECT_ID(N'myTestProc')) BEGIN<br />
DROP PROCEDURE myRenamedTestProc<br />
END <br />
GO<br />
<br />
|
|
|
|
|
Thanks for your answer
I cant believe all that one has to do to make sql work correctly 
I hadnt noticed that behavior until now that im exporting my DB using the generate sql script
By the way, sql script generator also has a lot of problems, doesnt it??
When dropping tables and other objects it doesnt care about dependencies and neither does it when creating them
Im having a hard time with generating the scripts for my DB
sql 2000 enterprise manager (Doesnt care about the deletion or creation order)
sql 2005 express ed (Doesnt care about the deletion or creation order)
Database Publishing Wizard (This one fails when trying to generate the script for a function that return a table, and i dont think that it generates scripts in the right order)
Do you have any sugestions for this?
Thanks again
Alexei Rodriguez
|
|
|
|
|
Renaming objects will always cause problems. If you need to find dependencies here are a couple scripts to allow you to search the source of views and stored procs. Just enter the name of the object you're looking for and you'll get a list of objects which contain your search phrase.
Search stored procedure:
DECLARE @find VARCHAR(1000)SET @find = 'enter your search phrase here'<br />
<br />
SELECT sp.name,<br />
ISNULL(smsp.definition, ssmsp.definition) AS [Definition]<br />
FROM sys.all_objects AS sp<br />
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id<br />
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id<br />
WHERE(sp.type = N'P' OR sp.type = N'RF' OR sp.type='PC')<br />
AND ISNULL(smsp.definition, ssmsp.definition) LIKE '%' + @find + '%'
Search view:
<br />
DECLARE @search VARCHAR(1000)SET @search = 'enter your search phrase here'<br />
<br />
SELECT c.[Text]<br />
FROM dbo.sysobjects AS vINNER JOIN dbo.syscomments c ON c.id = v.id<br />
AND CASE WHEN c.Number > 1 THEN c.Number ELSE 0 END = 0<br />
WHERE v.type = 'V' AND c.[Text] LIKE '%' + @search + '%'<br />
As far as dropping and recreating objects as long as your script creates tables before your other objects. What I always do when writing my scripts is follow the IF EXISTS DROP then CREATE pattern. That way when I have a large script which creates a large number of objects if I get errors due to dependencies the first time I run it then I just run it again. The first time creates all the objects, the second time will create any that failed on the first attempt.
If you don't like this, then when you generate your scripts run them on a test database. Create 2 script files - one with DROP statements and one with CREATE statements. Run your CREATE script and look for dependency errors. If you get any, run your DROP script then rearrange your CREATE statements to fix the dependency errors and run the CREATE script again. Rinse, wash, repeat.
|
|
|
|
|
I just saw your answer to the other post about the tool to generate the scripts
Ill give it atry
Thanks
Alexei Rodriguez
|
|
|
|
|
I want to encrypt my stored procedure. How can i do this and revert back also.
anybody help..
|
|
|
|
|
Here's how to encrypt the procedure:
<br />
CREATE PROCEDURE myTestProc<br />
@Id INT<br />
WITH ENCRYPTION<br />
AS<br />
<br />
SELECT @@SERVERNAME<br />
<br />
GO<br />
But you have to save the source code in a script file somewhere. There is no way to decrypt the source. Not even for sysadmin or dbowner.
|
|
|
|
|
|
Dear All,
I want to export data from Access into SQL Server 2000, but some data contains Arabic Text words, and it is not exported correctly as Arabic Language, it is stored as symbols like "??????". And this problem also happens when I use an Insert statement using SQL Query Analyzer.
Is there a way to insert these Arabic Data as it is in Arabic??
The following is such an example of my insert statement:
INSERT INTO Table( HOTCODE, HMONTH, HYEAR, HOT_NAME, OWNER_MNGR, STREET, BUILDING_OWNER,
BUILDING_NO, POBOX, PHONE, FAX, E_MAIL, LEGAL_STATE, LICE_NO, YEAR_ESTAB, YEAR_START, CLASS, HLOCAL,
SEX_OWNER, MINGR_NAME, SEX_MINGR, WEB, OWNER_BUILD, OWNER_HOTEL, RESULT ) VALUES (
'3009', '4', '2007', '???? ??', '??? ?????? ????', '????? ???? ???????', '??? ??????'
, NULL, '2169', '2409191', '2409189', 'cityinn@p-01.com', '5', '252', '1994', '1997'
, '4', '301790', '1', '????? ??? ????', '1', '1', '2', '1', '2');
Note: The key words in the insert statement are some Arabic words, but they are converted to "????"
when I post the current message.
Thanks
Kind Regards
OBarahmeh
Palestinian Central Bureau of Statistics (PCBS)
Ramallah-Palestinian Territory
|
|
|
|
|
|
Hello!
I need to write a stored procedure that will make a query, and then return a value depending
on the query result.
My question is how can I store the query results inside the stored procedure for me to be able
to examine them and then return an appropriate result?
Thanks in advance!
Have a nice day!
|
|
|
|
|
You want to store your query and then query that in a stored procedure? Can you not just do a bigger better query?
You can use cursors to do iterative stuff if need be but its best avoided.
Regards,
Rob Philpott.
|
|
|
|
|
Declare a temp table and save the result set inside that table. Make sure to drop it before you exit the stored procedure.
|
|
|
|
|
Can you give me a simple example? I would be very thankful...
Have a nice day!
|
|
|
|
|
Found an example by googling. Thanks!
Have a nice day!
|
|
|
|
|
Hi,
I'm Using Asp.Net with C# & Sql Server 2005..
I'm Generating Reports in SSRS 2005..
I want to Generate the Reports DYNAMICALLY in Local Mode at web-based applications..
Here I'm having One Stored Procedure which Returns UserID's.. I want to Display all the ID's in One DROPDOWNLIST Control.. By Selecting the ID's from Dropdownlist control, I want to Generate the Reports(Dynamically)..
How to Do this..
Please give me the Suggestions...
Thank You
|
|
|
|
|
in my source table i have '1530' as char data type which is actually a time (HHMM)
in my destination table the column is datetime. how could i convert '1530' and insert into destination table of type datetime.
please help
|
|
|
|
|
Hey there!
It is a bit hard to convert it to dateTime from just 24hrs Time format. From the little knowledge you have, you could play around with the following syntax but I am sure that it won't return you the proper result.
Syntax: convert(varchar(24),cast(1530 as datetime),8)
It would be wise to just do it on the front end if you are using it. In C#, you can achieve this by:
DateTime _dteDateTime = Convert.ToDateTime("1530").ToString("hh:mm");
If you are using VB.Net, I think the syntax was:
FORMAT(Node, "yyyy-mm-dd")
Hope that this helps.
------------------------------------------------------------------------
If there was no SQL, there would be no sequels to programming.
|
|
|
|
|
Hi,
MAX() function returns the max value from a specific column of the table, but i want to get the max value from a row.
Assume the following table.
col1 col2 col3 col4 col5 col6
0 7 9 3 4
5 6 10 8 1
I want to update the col6 whith the max value in that row. For the above example col6 will have 9 in first row and 10 in second.
I don't know the exact number of columns in the table. There can be any number of columns and i have to find the max from all columns.
Can anybody help me in this regard.
Thanks in advance.
Regards,
Sulaman Riaz
|
|
|
|
|
I think its kind of a given in SQL that you know how many columns you're dealing with and the name of each one. If you're a blindingly clever lunatic you maybe be able to query the metadata of the table and produce some uber proc to do this.
Personally, I'd have a go with a bit of code.
Regards,
Rob Philpott.
|
|
|
|
|
Can anyone recomend me a good SQL Script generator (A free one)?
Ive tryed:
SQL 2000 Sript generator and SQL Express Generator (Both of them create scripts in the wrong worder, and when executing the script, it fails) I CANT BELEIVE IT
Ive also tried Database publishing wizard (This one doesnt works because of functions that return table results i havent tryed removing those functions but i guess that it will create scripts in the wrong order)
Thanks for any suggestions
Alexei Rodriguez
Alexei Rodriguez
|
|
|
|
|
|
Hi All,
I have 2 reports, one a main report and the other a subreport.
My question is, Will i be able to change or refer to a control(item) in the main report depending on the value of an item in the subreport, when the subreport is being run? If yes how?
Any comments are appreciated.
Thanks in advance.
Payal
|
|
|
|
|
Hello....
I am trying to understand this parameter "Connection LifeTime" in the connection string.
The definition says "Connection Lifetime - When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime.
A value of zero (0) causes pooled connections to have the maximum connection timeout. "
Connection Lifetime - if I set the value for this to be 5 secs, does it mean that after I close my connection, and gets returned to the pool, and if the difference of the creation time and the returned time is greater than 5 secs, will the connection be available for use immediately, from the connection pool? Or do I loose this connection from the pool?
Or am I reading it incorrectly? What is the maximum connection timeout?
Please advice...
Thank You
|
|
|
|
|
Simply, connection timeouts refer to the time it takes the Server to run a query and generate a result. If you reach connection timeout, your conenction to the database will be terminated and you will need to re-initialise the connection to the database.
When we talk about Pooling, what that refers to is that when you try and make a connection to the database, the server tries to make contact with the database engine. If you set the timeout value to 5, it will try for 5 seconds and if no connection has been established, it will return you an error (Connection Timeout). Which means that you didnt have a connection to begin with. If you do establish a connection and then leave the connection (Close()), you will again lose the connection and you will need to go through the same process of establishing the connection.
Hope that this helps.
Regards,
Ali
|
|
|
|
|
Hello
This is the statement, which I am use in Sql Base
select IF (@SCAN (@UPPER(enota),'MI') ,1 , 60 ) , 2
from
members;
How can I execute similiar statement in SQL Server?
Thanks
lp
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
