|
Thanks Mika,
I have found the store procedures from sys.sql_modules! Cool! But I think using Script Stored Procedure as/Create To/File should be more convenient than using sys.sql_modules to achieve my goal of sync with source control system. Any comments or ideas?
regards,
George
|
|
|
|
|
Absolutely use scripting abilities provided by Management Studio.
The reason I explained the sys.sql_modules was to give you a basic understanding how SQL Server stores information about database objects in the system tables. This was based on your question "where is the physical location of stored procedures".
System tables are very handy in several situtations but in this particular case scripting using Management Studio is both easier and more reliable.
|
|
|
|
|
Thanks Mika!
I find I could only export one store procedure at one time? No quick export command from menu to export all store procedures from a database?
regards,
George
|
|
|
|
|
Have a look at my article here[^], it explains the System Management Object and gives you examples of how to generate scripts for all database objects - you may have to tweak it to do exactly what you want, but its a good starting point.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks Bob!
Good start point!
regards,
George
|
|
|
|
|
George_George wrote: only export one store procedure at one time? No quick export command from menu to export all store procedures from a database
use. your. initiative. we were all newbie's once you know!
Right click your database > Tasks > Generate Scripts...
I assume you can follow a simple wizard, if not im sure you'll head back here asking something benign like "How to I click on OK to proceed?"
|
|
|
|
|
Hi J4amieC,
I did not find an option to script all store procedures.
Any more hints or ideas?
regards,
George
|
|
|
|
|
So the answer was no, you cant follow a wizard.
Seriously George, change career paths - you're not cut out to be a developer.
|
|
|
|
|
|
Good stuff, thanks Hamid!
regards,
George
|
|
|
|
|
Hello everyone,
I have a table and a couple of columns in this table. For some specific column, it is not primary key, but I want to accept only unique value for this specific column for each row.
Currently, my naive solution is to use a store procedure to handle table insertion and if in the store procedure I find there is already a row whose existing value for the specific column is the same as the new column value of the new row to be inserted, I will return error from the store procedure and refuse to insert the whole new row.
Any other smarter ways?
thanks in advance,
George
|
|
|
|
|
|
Thanks Colin,
Good idea!
regards,
George
|
|
|
|
|
|
I am new to SQL Server 2005. I need to generate a report in XML format using SSRS 2005. Is it possible by using Rs.exe? Please give me the step-by-step solution for this.
Thanks.
|
|
|
|
|
|
hi,
Is there any way to find the execution time of a query? I simply don't want to perform time consuming queries,
My small attempt...
|
|
|
|
|
I'm not sure what you're asking. Are you asking if its possible to know the execution time of a query before you run it?
If you just want to know how long a query took you can use statistics.
<br />
SET STATISTICS TIME ON<br />
-- Query here<br />
SET STATISTICS TIME OFF<br />
Ryan
|
|
|
|
|
yes, i want to know the execution time before i run the query
My small attempt...
|
|
|
|
|
It's not possible to say the execution time reliably beforehand since the actual execution time depends on many unpredictable factors (workload, I/O amounts etc.)
However, you can use query governor to prevent the execution for those operations that are estimated to take more time than is acceptable. The limit is defined in seconds. To set the limit for a connection use SET QUERY_GOVERNOR_COST_LIMIT[^]. It can also be limited system wide using sp_configure but that may not necessarily be what you want.
|
|
|
|
|
anyone can help me solve the following problem say i have the following rows for a table:
Col1 Col2 Col3
1 3 20
2 6 512
1 2 23
4 9 429
3 8 92
4 7 35
2 9 1
I would like to group by Col1 and then get the minimum of Col2 and the corresponding value in Col3 for those minimum so I get the following:
Result Set:
1 2 23
2 6 512
3 8 92
4 7 35
----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list
|
|
|
|
|
select * from
(
select ROW_NUMBER() over (partition by col1 order by col2 asc) as rowNumber,*
from your_table
)base
where base.rowNumber=1
Human knowledge belongs to the world
|
|
|
|
|
If you're using sql2k...
<br />
declare @temp table (col1 int, col2 int, col3 int)<br />
insert into @temp values(1,3,20)<br />
insert into @temp values(2,6,512)<br />
insert into @temp values(1,2,23)<br />
insert into @temp values(4,9,429)<br />
insert into @temp values(3,8,92)<br />
insert into @temp values(4,7,35)<br />
insert into @temp values(2,9,1)<br />
<br />
select t.*<br />
from @temp t<br />
inner join<br />
( select col1, min(col2) as col2<br />
from @temp <br />
group by col1<br />
) d on d.col1 = t.col1 and d.col2 = t.col2<br />
Ryan
|
|
|
|
|
I need to create a table dynamically, preferably via stored procedure. I've gotten a bit through what I'm sure I need to do, but now I'm getting lost.
CREATE PROCEDURE CreateNewPersonTable
@tableName NVARCHAR(100),
@ColumnList NVARCHAR(MAX)
AS
Begin
Declare @CreateTableCommand NVARCHAR(MAX)
SET @CreateTableCommand = '
CREATE TABLE [dbo].[' + @tableName + 'SampleTable](
[RecordID] [bigint] IDENTITY(1,1) NOT NULL,
[CheckedOutBy] [nvarchar](50) NULL,
[Completed] [nvarchar](5) NOT NULL,
[FirstName] [nvarchar](50) NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Suffix] [nvarchar](5) NULL,
[Address1] [nvarchar](100) NULL,
[Address2] [nvarchar](100) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](50) NULL,
[ZipCode] [nvarchar](50) NULL,
[Disposition] [nvarchar](50) NULL' +
@ColumnList
+
') ON [PRIMARY] '
exec(@CreateTableCommand)
END
GO
Where the @ColumnList is will be a list that is generated from column names in a CSV file. The column names vary from file to file, then the data is looked up, and updated to the columns that are non-dynamic.
I had planned on making the @ColumnList a comma seperated list of the columns( IE col1,col2,col3, etc.. ) and then make all the columns NVARCHAR(MAX) NULL. The final column would look something like
[O_Col1] [nvarchar](MAX) NULL
Is there a simple way of doing this or would I be better off just doing this from within my code and pass the full value?
|
|
|
|
|
If the problem is in reformatting the columns, you could use REPLACE function. Something like:
DECLARE @collist varchar(1000)
DECLARE @formatted varchar(1000)
SET @collist = 'col1,col2,col3';
SET @formatted = '[O_'
+ REPLACE(@collist, ',', '] [nvarchar](MAX) NULL,[O_')
+ '] [nvarchar](MAX) NULL';
PRINT @formatted;
This results into:
[O_col1] [nvarchar](MAX) NULL,[O_col2] [nvarchar](MAX) NULL,[O_col3] [nvarchar](MAX) NULL
However, I'm wondering if instead of creating the table could you use SELECT INTO statement to create and populate the table. It could make the task easier.
|
|
|
|