Ristricted User Permissions on Services

Hi!
I have a user with restricted permissions on his account but still he
wants to start and stop SQL server services for MSDE (runtime SQL
server) installed on his computer. So whenever he tries to start or
stop the service he gets a message "Access Denied", so can anyone guide
me how can I enable atleast this feature on his machine still using his
restricted account.
cheers!!!
You should have admin rights to start or stop services.
Regards
Amsh
sql

Ristricted User Permissions on Services

Hi!
I have a user with restricted permissions on his account but still he
wants to start and stop SQL server services for MSDE (runtime SQL
server) installed on his computer. So whenever he tries to start or
stop the service he gets a message "Access Denied", so can anyone guide
me how can I enable atleast this feature on his machine still using his
restricted account.
cheers!!!You should have admin rights to start or stop services.
Regards
Amsh

reverse merge repliction

hi

I have notice that i missed few columns in my merge repliction and i wonder is it possiblie to reverse a replicated database or copy to normal SQL database?

after that i would do a new Publication.

I need to have the data that is in the Database.

If this is SQL Server 2005, you can always use alter table and add the new columns to the table. They should automatically propagate to the subscriber.

If you wish you can drop the publication, add the columns and reset the publication too.

The data in the tables will be intact.

reverse merge repliction

hi

I have notice that i missed few columns in my merge repliction and i wonder is it possiblie to reverse a replicated database or copy to normal SQL database?

after that i would do a new Publication.

I need to have the data that is in the Database.

If this is SQL Server 2005, you can always use alter table and add the new columns to the table. They should automatically propagate to the subscriber.

If you wish you can drop the publication, add the columns and reset the publication too.

The data in the tables will be intact.

Returning/storing a value for use in a stored proc.

Hi:
I have a stored procedure which performs an insert statement. At the
conclusion of the insert, I have SELECT SCOPE_IDENTITY(). Now, I want to be
able to use this value for the next stored procedure (which will then perfor
m
an update on the newly inserted record). Here's an example:
--Step #1:
Execute spCCD @.COMPANY_ID
--which looks like this:
CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
@.Company_ID
Declare @.STMNT_ID int
AS
Insert Into STMNT (
COMPANY_ID,
Tran_Fee1,
Tran_Fee2
)
Values (
@.COMPANY_ID,
0.15,
0.25
)
Set @.STMNT_ID = (SELECT SCOPE_IDENTITY())
--Step #2
Execute spVoid @.STMNT_ID
---
So calling the stored procedures will look like this:
Execute spSTMNT_CCDPPD @.COMPANY_ID --this will return @.STMNT_ID
Execute spVoid @.STMNT_IDEric,
Use an output parameter.
CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
@.Company_ID,
@.STMNT_ID int output
AS
set nocount on
Insert Into STMNT (
COMPANY_ID,
Tran_Fee1,
Tran_Fee2
)
Values (
@.COMPANY_ID,
0.15,
0.25
)
Set @.STMNT_ID = SCOPE_IDENTITY()
go
declare @.STMNT_ID int
Execute spSTMNT_CCDPPD @.COMPANY_ID, @.STMNT_ID output
Execute spVoid @.STMNT_ID
go
Remember to handle errors.
AMB
"Eric" wrote:

> Hi:
> I have a stored procedure which performs an insert statement. At the
> conclusion of the insert, I have SELECT SCOPE_IDENTITY(). Now, I want to
be
> able to use this value for the next stored procedure (which will then perf
orm
> an update on the newly inserted record). Here's an example:
> --Step #1:
> Execute spCCD @.COMPANY_ID
> --which looks like this:
> CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
> @.Company_ID
> Declare @.STMNT_ID int
> AS
> Insert Into STMNT (
> COMPANY_ID,
> Tran_Fee1,
> Tran_Fee2
> )
> Values (
> @.COMPANY_ID,
> 0.15,
> 0.25
> )
> Set @.STMNT_ID = (SELECT SCOPE_IDENTITY())
> --Step #2
> Execute spVoid @.STMNT_ID
> ---
> So calling the stored procedures will look like this:
> Execute spSTMNT_CCDPPD @.COMPANY_ID --this will return @.STMNT_ID
> Execute spVoid @.STMNT_ID
>|||Correction,

> @.Company_ID,
CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
@.Company_ID int,
@.STMNT_ID int output
AS
set nocount on
Insert Into STMNT (
COMPANY_ID,
Tran_Fee1,
Tran_Fee2
)
Values (
@.COMPANY_ID,
0.15,
0.25
)
Set @.STMNT_ID = SCOPE_IDENTITY()
go
declare @.COMPANY_ID int
declare @.STMNT_ID int
set @.COMPANY_ID = 123
Execute spSTMNT_CCDPPD @.COMPANY_ID, @.STMNT_ID output
Execute spVoid @.STMNT_ID
go
AMB
"Alejandro Mesa" wrote:
> Eric,
> Use an output parameter.
> CREATE PROCEDURE [dbo].[spSTMNT_CCDPPD]
> @.Company_ID,
> @.STMNT_ID int output
> AS
> set nocount on
> Insert Into STMNT (
> COMPANY_ID,
> Tran_Fee1,
> Tran_Fee2
> )
> Values (
> @.COMPANY_ID,
> 0.15,
> 0.25
> )
> Set @.STMNT_ID = SCOPE_IDENTITY()
> go
> declare @.STMNT_ID int
> Execute spSTMNT_CCDPPD @.COMPANY_ID, @.STMNT_ID output
> Execute spVoid @.STMNT_ID
> go
> Remember to handle errors.
>
> AMB
> "Eric" wrote:
>

Returning SSIS stored procedure name

Hi

I am currently trying to write a number of processe's to keep track of what information is held in my SSIS package. The package I have created is rather large and it would prove a long labourious process to look through every task to see what stored procedure has been used.

What I wanted to do was write a stored procedure in SQL Server 2005 that pick's up each package name and checks for any stored procedures used and returns the names of these stored procedures and any other relevant information i.e required variables.

So far I have managed to create a stored procedure that picks up the name(s) of the packages but I am stuck after this.

Declare @.Filename varchar(1000)
Declare @.cmd varchar(1000)

Create table #dir (Filename varchar(1000))
Insert #dir
Exec master..xp_cmdshell 'dir /B C:\Development\SumColumn\SumColumn\*.dtsx'
delete #dir where Filename is null or Filename like '%not found%'
Select @.Filename = ''
While @.Filename < (select max(Filename) from #dir)

drop table #dir

Any help would be appreciated.

Thanks

Hi,

you can use a "SQL profiler" trace to trace whats happening in your server when the package is run.

anyway here's something that might help you. dtsx file of SSIS is stored in XML format

you can open the file with notepad and figure out from there. you can use the notepad "find" feature

if you have some XML background

or data processing skill you might be able to extract the information you need.

you may also use the Sql server 2005 XML datatype to do that.

regards

,joey

|||

Using the SSIS API, it's generally possible to load a package, find all the instances of some specific tasks, and retrieve the values of some of their properties. At the time that you wrote the code, you'd need to determine which types of tasks you wanted to scan. For each of these types, you'd need to write code to intelligently parse the values stored in the properties of the task for the name of the stored procedure. You can read up on the SSIS API, here: http://msdn2.microsoft.com/en-us/library/ms136025.aspx

In particular, you'll probably want to take a look at:

http://msdn2.microsoft.com/en-us/library/ms136090.aspx

http://msdn2.microsoft.com/en-us/library/ms135956.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.executables.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.executables.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.tasks.executesqltask.executesqltask.aspx

While it might be possible to scan through the xml, that approach would be unsupported.

Returning Scope_Identity from Stored Procedures

Hi:

I've seen a lot of posts about returning the identity after an insert but for every variation, the return value or output parameter is null. Below is the sp, SQLDataSource and vb code for the return value attempt.

Here is the stored procedure:

ALTER PROCEDUREdbo.sp_InsertPlayer

(

@.UserNamevarchar(64),

@.Emailvarchar(128)

)

AS

INSERT INTOtblPlayer ([UserName],)

VALUES(@.UserName, @.Email)

RETURN SCOPE_IDENTITY()

If the sp is executed in the SQL Express that is included in VS 2005, the return value is the last autogenerated PlayerId.

SqlDataSource:

<asp:SqlDataSourceID="SqlDataPlayer"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand ...

InsertCommand="sp_InsertPlayer"

InsertCommandType="StoredProcedure"

>

...

<InsertParameters>

<asp:ParameterName="RETURN_VALUE"Direction="ReturnValue"Type="Int32"/>

<asp:ParameterName="UserName"Type="String"/>

<asp:ParameterName="Email"Type="String"/>

</InsertParameters>

</asp:SqlDataSource>

code behind vb:

SqlDataPlayer.InsertParameters("UserName").DefaultValue = CreateUserWizard1.UserName

SqlDataPlayer.InsertParameters("Email").DefaultValue = CreateUserWizard1.Email

SqlDataPlayer.Insert()

Profile.PlayerId = SqlDataPlayer.InsertParameters("RETURN_VALUE").DefaultValue

The DefaultValue is Nothing.

Thanks for your help again.

arora

What I end up doing is something like this:

...
SELECT ScopeIdentity = SCOPE_IDENTITY()

That was back in the .NET 1.1 days, so I don't know if that would be of use with the SqlDataSource. Does that work?

|||

That doeswork (in fact that is my workaround) but just the integer identity value needs to be returned.

Using the SELECT, a result set with one column ScopeIdentity is returned.

I have to use code similar to below to access the identity value:

SqlDataPlayer.SelectParameters("UserName").DefaultValue = CreateUserWizard1.UserName

Dim argsAs DataSourceSelectArguments =New DataSourceSelectArguments()

Dim dvAs Data.DataView

dv = SqlDataPlayer.Select(args)

Session("PlayerId") = dv.Table.Rows(0).Item("PlayerId")

and the sp is:

ALTER PROCEDUREdbo.sp_PlayerIDFromUserName

(

@.UserNamevarchar(64)

)

AS

SELECTPlayerIDFrom[tblPlayer]

WHERE[UserName] = @.UserName

It just seems there has to be an easier way than returning a table for one value.

|||The return value is only available in the SqlDataSource_Inserted event.|||

Thanks Motley.

Here's a link with an example.

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserted.aspx

returning date without time part

Hi
I have a simple select like this
Select MyDate, MyID From Sales
The select is returning the date like this:
01/06/2005 12:00:00 a.m.
02/06/2005 12:00:00 a.m.
I'm showing it on a Datagrid in my app, my question is can I modify the
select to return only the date without the time part, like this
01/06/2005
02/06/2005
Thks.
Kenny M.If you're returning a datetime value, then no, you can't strip the date. if
you convert it to char, you can.
SELECT CONVERT(CHAR(10), MyDate, 101) AS MyDate, MyId FROM Sales
What you probably want to do, however, is set the format on the
GridColumnStyle in the client app so that it only displays the date.
"Kenny M." wrote:

> Hi
> I have a simple select like this
> Select MyDate, MyID From Sales
> The select is returning the date like this:
> 01/06/2005 12:00:00 a.m.
> 02/06/2005 12:00:00 a.m.
> I'm showing it on a Datagrid in my app, my question is can I modify the
> select to return only the date without the time part, like this
> 01/06/2005
> 02/06/2005
> Thks.
> --
> Kenny M.

Returning Date without

Hi
I have a simple select like this
Select MyDate, MyID From Sales
The select is returning the date like this:
01/06/2005 12:00:00 a.m.
02/06/2005 12:00:00 a.m.
I'm showing it on a Datagrid in my app, my question is can I modify the
select to return only the date without the time part, like this
01/06/2005
02/06/2005
Thks.See the CONVERT function as its arguments in SQL Server Books Online
Anith