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 a string with carriage return and line feed

Hello,
Is it possible to create a variable that has carriage return and line feed
embed inside. Say I have 3 fields: field1, field2, field3. I want to return
a
string with carriage returns and line feeds between field1, 2 and field2, 3.
Thanks in advanceLOOK in BOL for CHAR,
TAB Char(9)
LineFeed Char(10)
CarriageReturn Char(13)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Ed Chiu" <EdChiu@.discussions.microsoft.com> schrieb im Newsbeitrag
news:462D3FCC-28E9-4660-A5F7-559D5CB3CA33@.microsoft.com...
> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to
> return a
> string with carriage returns and line feeds between field1, 2 and field2,
> 3.
> Thanks in advance|||> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to
return a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
Include CHAR(13)+CHAR(10) when you are concatenating the strings.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Try,
select 'Microsoft' + char(13) + char(10) + 'SQL' + char(13) + char(10) +
'Server' + char(13) + char(10) + '2000'
AMB
"Ed Chiu" wrote:

> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to retur
n a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
> Thanks in advance|||DECLARE @.var VARCHAR(50)
SET @.var = 'joe' + CHAR(13) + CHAR(10) + 'blow'
SELECT @.var
It's up to the consumer of the data to display it properly. For example
in Query Analyzer, if you choose to display results in a grid, it
replaces the carriage return, line feed with spaces. If you choose to
display in text mode, it'll put the carriage return, line feed in there
for you.
If you're returning this to a .net dataset, you can display it properly.
HTH...
Joe Webb
SQL Server MVP
~~~
Get up to speed quickly with SQLNS
http://www.amazon.com/exec/obidos/t...il/-/0972688811
Ed Chiu wrote:
> Hello,
> Is it possible to create a variable that has carriage return and line feed
> embed inside. Say I have 3 fields: field1, field2, field3. I want to retur
n a
> string with carriage returns and line feeds between field1, 2 and field2,
3.
> Thanks in advancesql

create a function

hi, I try to do insert a stored procedure's result into a temp table,
then I try to put it inside the function, but it doesn't work.
The reason I want to do this is because we have 200 stored procedures which
need to reuse the same code,
so by putting these logics into a function, the code would be cleaner..
How can I get around this?
CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
@.LId INT)
RETURNS TABLE
AS
CREATE TABLE #tempList
(
UId INT,
CompanyId NVARCHAR(10),
LId INT,
CompanyName NVARCHAR(50)
.....
)
INSERT INTO #tempList
Exec GetListInfo @.UId, @.CompanyId ,@.LId
RETURN (SELECT * FROM #tempList)You can't execute a stored proc within a function -- you'll have to do this
within another stored proc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uxpTfkAIFHA.2936@.TK2MSFTNGP15.phx.gbl...
> hi, I try to do insert a stored procedure's result into a temp table,
> then I try to put it inside the function, but it doesn't work.
> The reason I want to do this is because we have 200 stored procedures
which
> need to reuse the same code,
> so by putting these logics into a function, the code would be cleaner..
> How can I get around this?
>
> CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
> @.LId INT)
> RETURNS TABLE
> AS
> CREATE TABLE #tempList
> (
> UId INT,
> CompanyId NVARCHAR(10),
> LId INT,
> CompanyName NVARCHAR(50)
> .....
> )
> INSERT INTO #tempList
> Exec GetListInfo @.UId, @.CompanyId ,@.LId
>
> RETURN (SELECT * FROM #tempList)
>|||You can't call an stored procedure from within a function. Put the SP
logic into the body of the function instead if you can.
David Portas
SQL Server MVP
--|||you can't put temp table in the function also.
correct me if I'm wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23bBHPtAIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> you can't put temp table in the function also.
> correct me if I'm wrong.
You can use a table variable in a function -- logically the same thing.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||That's correct. However you can use table variables in a function.
Table variables provide a lot of the same functionality as temp tables
but you can't insert the result of an SP into a table variable even if
it was possible to execute the SP.
David Portas
SQL Server MVP
--|||Hi David,
Well, I know that will work, but if I make a change in the logics in
the future,
then I will have to change all 200 procedures. I try to avoid this problem.
If function works, then all I need to do is to change the logic in the
function. Too bad there is no way to get around it.
=(
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||Oh never mind.. ignore the previous question,
I misunderstood it. you're right, put the sp logic into function will work.
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uOGLGtBIFHA.572@.tk2msftngp13.phx.gbl...
> Hi David,
> Well, I know that will work, but if I make a change in the logics
in
> the future,
> then I will have to change all 200 procedures. I try to avoid this
problem.
> If function works, then all I need to do is to change the logic in the
> function. Too bad there is no way to get around it.
> =(
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
>

counting pages etc

i'm trying to make certian stuff inside a table header be shown on the
first page ONLY ,and other stuff hidden on the first page..
I'm having a lot of problems
in the end i want to put hidden.visibility to be based on some logic,
but i've found no way to differentiate the pages..
for example i try this custom code, to test something out, figure that
i should get a incremental increase everytime it is called
public shared thecount as integer = 0
Public Function docount as Integer
thecount = thecount + 1
return thecount
End Function
but inside the tables header i have
=Code.docount
and it shows 0 on every page..
anybody else pulled their hair out on this one, and has come through
the other side?I have an example of putting items in the footer on the first page only at
www.MSBICentral.com
It does not try to count pages, but can tell if this is the first
page...The File is called PageFooterOnFirstOnly.RDL
It is in the downloads section... Hope this helps..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Karl Prosser" <klumsy@.xtra.co.nz> wrote in message
news:7156880c.0502151713.2432cd30@.posting.google.com...
> i'm trying to make certian stuff inside a table header be shown on the
> first page ONLY ,and other stuff hidden on the first page..
> I'm having a lot of problems
> in the end i want to put hidden.visibility to be based on some logic,
> but i've found no way to differentiate the pages..
> for example i try this custom code, to test something out, figure that
> i should get a incremental increase everytime it is called
> public shared thecount as integer = 0
> Public Function docount as Integer
> thecount = thecount + 1
> return thecount
> End Function
> but inside the tables header i have
> =Code.docount
> and it shows 0 on every page..
> anybody else pulled their hair out on this one, and has come through
> the other side?|||thanks for the example, but making a footer only show on the first page
was not my problem since headers and footers can look at the global
page counter, it was something in the body, in the header of a table..
i could use repeatonnewpage, however i needed some header stuff to show
sometimes and some not to. and i found that that even though a table
header can have many rows and each row has a repeat on new page
property, if you change one row, it changes them all, anyhow i managed
to do it with created another group, a dummy group and having my logic
in there..
also i found out why my example code above didn't work, it seems RS
only calculates the table header once (even though it repeats it on
subsequent pages, it doesn't recalculaste it, just reuses whatever was
last calculated)
Karl
Wayne Snyder wrote:
> I have an example of putting items in the footer on the first page
only at
> www.MSBICentral.com
> It does not try to count pages, but can tell if this is the first
> page...The File is called PageFooterOnFirstOnly.RDL
> It is in the downloads section... Hope this helps..
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Karl Prosser" <klumsy@.xtra.co.nz> wrote in message
> news:7156880c.0502151713.2432cd30@.posting.google.com...
> > i'm trying to make certian stuff inside a table header be shown on
the
> > first page ONLY ,and other stuff hidden on the first page..
> > I'm having a lot of problems
> > in the end i want to put hidden.visibility to be based on some
logic,
> > but i've found no way to differentiate the pages..
> >
> > for example i try this custom code, to test something out, figure
that
> > i should get a incremental increase everytime it is called
> >
> > public shared thecount as integer = 0
> > Public Function docount as Integer
> > thecount = thecount + 1
> > return thecount
> > End Function
> >
> > but inside the tables header i have
> > =Code.docount
> >
> > and it shows 0 on every page..
> >
> > anybody else pulled their hair out on this one, and has come
through
> > the other side?

counter inside select statement?

Hi, can you add a counter inside a select statement to get a unique id line of the rows?

In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order.

This is the normal way:

SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @.topicID) ... some more where/order by statements

This is what i want:

DECLARE @.tempCounter bigint
SET @.tempCounter = 0
SELECT @.tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @.topicID)... some more where/order by statements and at the end.. (SELECT @.tempCounter = @.tempCounter + 1)

Anyone know if this can be done?

Use Row Num ( if you are using SQL SErver 2005 ) as

Select ROW_NUMBER() OVER(ORDER BY Some_Field ) AS rownum,
* from Table_Nane

|||

yes i am using MS SQL 2005, it worked for half of the problem, ill display the SP below, what the SP does is to select the posts from a single user, by ordering them first by topicID then by date created.
In order to bring back just what the client need, i incorperated a custom paging system.

DECLARE @.first_idint, @.startRowint
SET @.startRowIndex=(@.startRowIndex- 1)* @.maximumRows+ 1
IF @.startRowIndex= 0
SET @.startRowIndex= 1

SETROWCOUNT @.startRowIndex

SELECT @.first_id=ROW_NUMBER()OVER(ORDERBY forum_answer.topicidASC, forum_answer.idDESC)FROM forum_answerINNERJOIN forum_topicsON forum_topics.id= forum_answer.topicidINNERJOIN forum_boardON forum_board.id= forum_topics.boardidAND forum_board.hidden= 0INNERJOIN forumON forum.id= forum_board.forumidAND forum.hidden= 0RIGHTOUTERJOIN profile_publicinfoON profile_publicinfo.username= forum_answer.usernameWHERE(forum_answer.username= @.UserName)ORDERBY forum_answer.topicidASC, forum_answer.idDESC

SETROWCOUNT @.maximumRows

DECLARE @.tempVarint
SELECT @.tempVar=ROW_NUMBER()OVER(ORDERBY forum_answer.topicidASC, forum_answer.idDESC), forum_answer.topicid, forum_answer.id, forum_answer.username, forum_answer.answer, forum_answer.created, profile_publicinfo.signatureFROM forum_answerINNERJOIN forum_topicsON forum_topics.id= forum_answer.topicidINNERJOIN forum_boardON forum_board.id= forum_topics.boardidAND forum_board.hidden= 0INNERJOIN forumON forum.id= forum_board.forumidAND forum.hidden= 0RIGHTOUTERJOIN profile_publicinfoON profile_publicinfo.username= forum_answer.usernameWHERE(forum_answer.username= @.UserName)AND(@.first_id<= @.tempVar)ORDERBY forum_answer.topicidASC, forum_answer.idDESC

SETROWCOUNT 0

The first thing we do, is to get the id for the first post that should be returned. This works with the ROW_NUMBER method, the second select statement, takes xx number of rows with start from the position it recieves from the first select statement. So if @.first_id is 5 and maximumRows is 5, then the second select statement will only take rows 5->10 from the table.

The problem is the ROW_NUMBER inside the second select statement, sql screams if i put it at the end like (@.first_id <= ROW_NUMBER...

Msg 4108, Level 15, State 1, Procedure Forum_GetUserAnswers, Line 32
Windowed functions can only appear in the SELECT or ORDER BY clauses.

SQL also screams if i put it in the beginning (as shown above), then i get the following error:

Msg 141, Level 15, State 1, Procedure Forum_GetUserAnswers, Line 33
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Thats why i wanted a way to calculate the rows and get a ID according to that. Any idea of how to proceed?