Create and manage Store Procedures from inside VS

With a local conn to SQL2000 I can edit sprocs no problem, but if I change the connection to a remote computer (to which I have all permissions) I cannot create or manage stored procedures from within VS IDE? Is there a work around?

I noticed that a procedure written for the localhost accepts CREATE PROCEDURE and changes it to ALTER PROCEDURE. When I script the sprocs from my development machine to the server, those scripts with ALTER in them do not work. I change them to CREATE and they work fine.

I haven't found anything yet on MSDN about this, but will continue to look.

Thanks in advance,

_EHi _E

You may have better luck with this question in the SQL Server Tools - General forum.
Allan|||Thanks, I'll try there. Didn't know if it was an IDE or SQL thing.

_E

create an Indexes/Keys Property with T-SQL

is there a function that i can use in a store procedure that allow me to create an Indexes/Keys Property
thanxYes. CREATE INDEX.

-PatP|||Yes. CREATE INDEX.

-PatP

excuse me while I climb back on my barstool...um office chair...

LNHockey...seriously though... alittle more background on what you're trying to do...

"create index"....why I outta.....|||Yes ok..excuse me.

i do this
<b>
ALTER TABLE TblSalle ADD [IdTypeTaxe] [int] NOT NULL default(0)<br>
CREATE INDEX PK_TblSalle ON TblSalle (IdTypeTaxe)</b>

and would like to assign is "Selected index value" to IX_IdTypeTaxe that i userly have in a combobox when i use the sql manager

but i want to do it in a store proc..

or how can i modify that value after i add the new column to my table|||Yup...I'm lost..

Huh?|||You know when you go to design mode of the table and go properties of the selected column and select the tab "Indexes/keys". under that you can switch the type "Primary key to Index" ?? !!!

just wondering if we can do the same thing in a Store proc using a function kind of thing

thanx !

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itsel
f), and a SELECT
statement read data from tables or views, it doesn't execute stored procedur
es. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I
wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...seagreen">
> How can I create a view which based on a store procedure and then sp will
return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/d...n-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

Create a table with a field with only Time datatype

Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?Hello experts,
I want to create a table to store only time in a fileld. There is "DateTime" for my purpose but i dont want to save the Date part only the time part as "12:30:44 AM". I know i can select only time part from Datetime field but i want to save only time.Can anybody help me how can i create that kinda table ?

See this enigma's post link (http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk)
Its a good one ...|||i dont understand why you are not thinking in terms of a datetime/smalldatetime field by ignoring the date part and whats wrong in it. however, if you are rigid you can store it either as char field or as numeric field (float/decimal). remember you do not actually save anything by doing so.

storing as char field is straight forward. if you want to store as number (remember that each date+time has a numeric representation) can take hints from the following queries

--1 minute as number
select cast(cast('20060101 11:01 AM' as smalldatetime) as float)- cast(cast('20060101 11:00 AM' as smalldatetime) as float)
--it returns approx 0.0006944

--use that to get a date time 20060101 11:00 AM back
select cast( cast(cast('20060101' as smalldatetime) as float) + (0.0006944*60*11) as smalldatetime)

quite unnecessary...;-)sql

Create a database with unicode characers

Hi,

My database i have to store arabic as well as english. I read in the forum, it says we have to use nchar or nvarchar as data type for the column to store unicode data.

But i have to create only the database with unicode character set.Because the software am gng to install itself creates the table for the database.

Any solutions?

Regs,

Priya

unfortunately, there isn't a way. if the table is created with varchar, the column is defined as varchar.|||

English and Arabic characters are included in the 1256 codepage and you don’t have to use “Unicode” if the target machines have Arabic support correctly. Make sure your regional settings are set to Arabic and that the language of non-Unicode programs are Arabic. For more details check Arabic support in Windows.

Create a database with unicode characers

Hi,

My database i have to store arabic as well as english. I read in the forum, it says we have to use nchar or nvarchar as data type for the column to store unicode data.

But i have to create only the database with unicode character set.Because the software am gng to install itself creates the table for the database.

Any solutions?

Regs,

Priya

unfortunately, there isn't a way. if the table is created with varchar, the column is defined as varchar.|||

English and Arabic characters are included in the 1256 codepage and you don’t have to use “Unicode” if the target machines have Arabic support correctly. Make sure your regional settings are set to Arabic and that the language of non-Unicode programs are Arabic. For more details check Arabic support in Windows.

create / alter a store procedure

hello,
i want to create a stored procedure which i can use to create / alter a
other stored procedure.
The store procedure should have 2 parameters name and content of the stored
procedure
I think this could be made with dynamic sql...
Any ideas how to do this.
I want to use the this to alter stored procedures from inside asp.net
calling the new procedures with the adequate parameters:
thanksHi
> The store procedure should have 2 parameters name and content of the
> stored
> procedure
Why? Don't you know a name of SP nor parameter's name?
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:3CEB6694-87D2-40D5-A53B-E84410E35B5F@.microsoft.com...
> hello,
> i want to create a stored procedure which i can use to create / alter a
> other stored procedure.
> The store procedure should have 2 parameters name and content of the
> stored
> procedure
> I think this could be made with dynamic sql...
> Any ideas how to do this.
> I want to use the this to alter stored procedures from inside asp.net
> calling the new procedures with the adequate parameters:
> thanks
>|||hi Xavier,
are you think over the possibility to use templates from QA?
regards,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:

> Hi
>
> Why? Don't you know a name of SP nor parameter's name?
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:3CEB6694-87D2-40D5-A53B-E84410E35B5F@.microsoft.com...
>
>|||i try to explain better
If i want to create a new SP -
Name:SalesReport
SPContent: is the string value of a tested StoredProcedures in the query
analyser
example
CREATE PROCEDURE MyNewSP
--@.ParSlotid int
AS
Declare @.PSlot int
Declare @.actionNr int
Declare @.slotid int
Declare @.actionId int
DECLARE @.StartDate Varchar(10)
DECLARE @.EndDate Varchar(10)
DECLARE @.NrRows int
set @.actionNr=1254
SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
.....
After i know the name and the content of the SP i want to call al a SP
example
exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that the
'SalesReport 'sp is created
My question is what must be the content of "CreateNewSP " to realize this
thanks
"Xavier" wrote:

> hello,
> i want to create a stored procedure which i can use to create / alter a
> other stored procedure.
> The store procedure should have 2 parameters name and content of the store
d
> procedure
> I think this could be made with dynamic sql...
> Any ideas how to do this.
> I want to use the this to alter stored procedures from inside asp.net
> calling the new procedures with the adequate parameters:
> thanks
>|||Xavier
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
>
What version are using?
The above statement you cannot use in SQL Server 2000 and you are about to
replace it with dynamic sql , I'm sure you are aware ofsome drawbacks using
dymanic sql in the production enviroment.
I don't know your business requiremnts ,so I make a guess , why not to
have a stored proedure for reporting? I mean , why do you want to supply a
DDL for stored procedure as a parameter? Or perhpas I did not understand
you, did I?
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that
> the
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||Rather than doing this from your ASP.NET application directly by connecting
to SQL Server, use SQLDMO. This provides a decent object model over the
database and you can use it to create/modify and delete objects of various
types. Here is an article that should help you:
http://www.sqljunkies.com/Article/1...BCA1D8C97D.scuk
By using SQLDMO you avoid creating a procedure in the database to churn out
other procedures. This way your solution can move easily between databases
if need be.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that
> the
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||No vull tocar el collons però segueixo sense entendre-ho
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Xavier" wrote:
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that t
he
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||hello SriSamp,
exactly that i want to do for administrative scopes...
Because on my IIS - server there is no instance of SQL - i can not use
SQLDMO?!
So my idea was to create a SP - which creates new SPs
thanks
"SriSamp" wrote:

> Rather than doing this from your ASP.NET application directly by connectin
g
> to SQL Server, use SQLDMO. This provides a decent object model over the
> database and you can use it to create/modify and delete objects of various
> types. Here is an article that should help you:
> http://www.sqljunkies.com/Article/1...srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
>
>|||
> What version are using?
i use SQL2k amd ASP.Net on a separat server
>you are about to replace it with dynamic sql
but how looks the SP which can use dynamic sql - have you a simple example
> why not to have a stored proedure for reporting?
because this SP are - not very complex and only valid for 2-6 ws - and
maintened by some user who did not have access over QA - but only over
Web-frontend with impersonation.
thanks|||have you any example how can this be done?
thanks
"Enric" wrote:
> hi Xavier,
> are you think over the possibility to use templates from QA?
> regards,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Uri Dimant" wrote:
>

CPU and MEMORY usage

Can some one tell me where can I get the CPU and Memory Usage of my server on the Sql Tables or a script or store proc that i could run to get the above. I need that and to store its results in history to show how are our servers perfomingexecute sp_who2 active

this will tell you whos is on your db and how much cpu (CPUTime) and mem (DISKIO) each user is taking

Cheers|||Actually gives the user activity accross the server you run it on. Not just the db activity - Sorry|||should use Performance Mointor to assess your server activity over a period of time.

you can log this info in .csv then import into SQL Server

have a look at :

www.sql-server-performance.com/performance_monitor_tips.asp

cheers|||On sp_who2 does the CPUTime equals to the CPU usage on the task manager and what about the Memory because i also want this memory to be the same as the one on the Task Manager|||Not sure about that. I think cputime that a sp_who will show the diskIO for each user and system query that occurs at the time of the SP execution. This does not take into consideration the O/S resources getting used.

I guess you could find out how much mem is allocated to SQL by looking at the properties of the server (right click on the server in Enterprise Manger) . From this figure you can work out how much resources are getting used in terms of how much mem has been allocated to SQL Server.

Cheers|||I 100% agree with you when you say we can manually look at properties and again on the task manager properties, the thing is we want a script that will do this for us automatically every hour to check the memory and cpu usage.|||Use performance mointor as it is the easiest way to shedule.

Look over the link above to see what counters you want to look at.

i.e Memory -- Pages/Sec, Memory Manager -- Total Server Memory (KB)
etc

What you could do is shedule it to output a csv file on the hour then set up a dts task to import this into SQL Server for anyalsis.

Once you know how much mem you allocated to SQL Server then you have constant that you can do calculations with.

Cheers|||Thanks, I think this will work perfect

CPU and Duration don't match in SQL Server Profiler

Hello All,

I have one store proc that is really slowing down my SQL server, at least according to the SQL Profiler. When I run the profiler and choose to show store procs that take longer then 3000 ms to execute, this is the only precedure that shows up. The CPU time and Duration is both in the 3000 ms mark. When I open up Query Analyzer and run that same store proc it takes less then a second, but when I check the Profiler to see the time, it's Blank (yellow) .. I'm using SQL 2005 Profiler. Any ideas why

1. I don't see a time when I execute the query from the profiler?

2. It says it takes 3 seconds when I only see it taking less then one?

Thank you

This is probably 3000 microseconds (3 milliseconds) :)

"In SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10^-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10^-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds."

Books Online:
http://msdn2.microsoft.com/en-us/library/ms175848.aspx

SQL Server Enthusiast:
http://weblogs.sqlteam.com/tarad/archive/2006/10/04/13621.aspx

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

Counting Rows

Is there a way to get a SQL Stored Procedure to count the number of rows returned and then store that total in a variable?A procedure? Number of rows returned? From where/what?
@.@.rowcount - Returns the number of rows affected by the last statement.

counting distint records

i have an sql statement like this:

SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition

i simply want to modify it to give me the count, i tried this, but it doesn't work:

SELECT count (distinct store, dept, sku)
FROM some_table
WHERE some_condition

what am i doing wrong? thank you very much for your help.This is a pure guess, but do you want something like:SELECT Count(*), store, dept, sku
FROM some_table
WHERE 1 = 1 -- or some other condition of your choosing
GROUP BY store, dept, sku-PatP|||thanks for the help, yeah i think that will work, but i ended up doing something like this:

SELECT count(1)
FROM ( SELECT distinct store, dept, sku
FROM some_table
WHERE some_condition
)