Enable/disable Extende Stored Procedure In MSSQL2005

Sep 20, 2007



Hi,

Anybody now how to this programmaticly ? in registry or TSQL statement ?

View 3 Replies


ADVERTISEMENT

MSsQL2005; OPENROWSET, BLOB/IMAGE And STORED PROCEDURE Problems

Oct 7, 2006

All,

I work with Microsoft SQL Server 2005 on windows XP professional.
I'd like to create stored procdure to add image to my database (jpg file).
I managed to do it using VARCHAR variable in stored procedure
and then using EXEC, but it don't work directly.

My Table definiton:
CREATE TABLE [dbo].[Users](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Login] [char](10),
[Password] [char](20),
[Avatar] [image] NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

My working solution using stored procedure:
ALTER PROCEDURE [dbo].[AddUser]
@Login AS VARCHAR(255),
@Password AS VARCHAR(255),
@AvatarFileLocation AS VARCHAR(255),
@UserId AS INT OUTPUT
AS
BEGIN
SET @Query = 'INSERT INTO USERS ' + CHAR(13)
+ 'SELECT '''+ @Login + ''' AS Login, ' + CHAR(13)
+ '''' + @Password + ''' AS Password,' + CHAR(13)
+ '(SELECT * FROM OPENROWSET(BULK ''' + @AvatarFileLocation + ''', SINGLE_BLOB) AS OBRAZEK)'
EXECUTE (@Query)
SET @UserID = @@IDENTITY
END

I'd like to use statement in the stored procdure:
ALTER PROCEDURE [dbo].[AddUser]
@Login AS VARCHAR(255),
@Password AS VARCHAR(255),
@AvatarFileLocation AS VARCHAR(255),
@UserId AS INT OUTPUT
AS
BEGIN
DECLARE
@Query AS VARCHAR(MAX)

SET @AvatarFileLocation = 'C:hitman1.jpg'
INSERT INTO USERS
SELECT @Login AS Login,
@Password AS Password,
(SELECT * FROM OPENROWSET(BULK @AvatarFileLocation, SINGLE_BLOB) AS OBRAZEK)


SET @UserID = @@IDENTITY

END


It generates error:
Incorrect syntax near '@AvatarFileLocation'.

My question is:
Why it does not work and how to write the stored procedure code to run this code without errors.

Thanks for any reply

View 7 Replies View Related

How To Enable And Disable The Identity

Nov 17, 2006

Hello there :-)

Kindly help me find the answer on how to enable and disable the identity of a field on a table via SQL Script?

Thanks :-)

View 1 Replies View Related

Enable And Disable Websync.log

Jan 9, 2007

Hi, is there a way how to enable / disable logging on web merge synchronization into the file websync.log? The file exists in the ISAPI folder, on some machines there are some messages, on others there is nothing. There are Timeout messages on the client during synchronization and we need to find where is the problem, if it is on the server side or if it is a network error or something else.
Thanks, Pavel

View 8 Replies View Related

URGENT: Disable And Enable The Constraints

Dec 12, 2000

Hi everybody,

Is there any procedure or option to disable and enable constraints on database.the problem is we are having lot master and child tables. when i am trying to move the entire data from original db to another same db, there i am getting constraints problem. child tables are not accepting data with out loading the master tables.

is there any to disable and enable constraints? please give me reply asap


Thanks in advance
Giri

View 3 Replies View Related

Enable And Disable Case Sensitivity

Feb 20, 2004

Hi,

I use a SQL Server 2000.

Is there a chance to change the case sensitivity option for a running instance of sql server?

I know, during the install process, i can activate the case sensitivity. But can I change this option on an already installed server (to case sensitiv or back)?

Thanks

View 4 Replies View Related

SQL 2012 :: Enable / Disable AG Listener

Jul 3, 2014

How can one enabledisable Availability Group listener? I know AG listener can be deleted and added but didnt find anything to disable or enable that.

View 1 Replies View Related

SQL 2012 :: Disable And Enable Trigger?

May 25, 2015

I have a trigger that I disable so I can do some operations on a table.

After the operations are completed I do the enable using the enable trigger comand.

Do I need to do something else to put back the trigger working as it was before the disable comand?

View 3 Replies View Related

Enable/disable Logging Dynamically

May 11, 2007

Is there a way to turn on/off a given logger dynamically across the board, and still enable the logging of task specific events, like "ExecuteSQLExecutingQuery" from the ExecuteSQL task or "WMIDataReaderOperation" from the WMI Data Reader task?

The use case is one where multiple loggers are defined in a package (custom and stock) and I'd like to toggle the execution tracing on a per logger basis.

I realize "logger toggling" can be done programmatically, but I'm wondering if that's the only way to accomplish the objective.

View 3 Replies View Related

Parameters Binding Enable/disable

Sep 19, 2007

Hello,

I am a beginner in SQL Server Reporting Services.
My report has three parameters; all with the same characteristics (combobox (multiple values, datatype: String, Available values: from query, default value:null).

When i am in the Preview Tab of the Report, the combo box of the first parameter is enabled, the combo box of the second parameter is disabled.

Once i select a value for the first parameter, the combo box of the second parameter becomes enabled.

I would like to select a value for the the second parameter in spite i did not select a value for the first parameter.
I do not find a solution in the properties of the parameters.

How can I do it ?
Thanks and sorry for my english

View 1 Replies View Related

Enable/Disable Constrains In Schema

May 29, 2008



Hello, is possible Enable or disable constrains in a defined schema? or Alter constrains in a defined schema.

For example in schema1 for user1 the constrains definitions are enabled.

There are another schema called schema2 for user2 and constrains definitions are disabled.

Thanks.

View 1 Replies View Related

Trigger On Enable / Disable SQL User

Mar 31, 2006

Is it possible to run a trigger whenever a SQL user is disabled or enabled? From what I've seen of various sysusers and syslogins tables there isn't a column that represents enabled or disabled.

View 6 Replies View Related

Enable/Disable SQL Server Login

Oct 13, 2005

Hi,

View 4 Replies View Related

How To Disable/enable An Identity Column In A Table

Jan 3, 2002

hi folks,
How to disable/enable an Identity Column in a table with the help of an sql
statement.
Thanks in advance.
Vineet Agarwal

View 1 Replies View Related

Disable/Enable A Trigger On A Replicated Table.

Apr 9, 2002

I have transaction replication setup on two SQL7 boxes and a nightly job runs a procedure that need to alter a table and disable a trigger. Since replication has been set up the disable doesn't work. Any way around this.

View 2 Replies View Related

SQL Server 2008 :: Enable And Disable Trigger

Oct 25, 2015

the disable trigger will be enable again.Because I already disable the trigger on last month, but when I check through the database, it enable again.I understand that, when we restore the database, all the trigger will be enable.How about SQL Cluster? will it enable the trigger??

View 3 Replies View Related

Disable And Enable A Task At RunTime In SSIS

Apr 24, 2008



Hi,

I have a package with 3 different tasks.
Like:

1. Execute Sql Task
2. Execute Sql Task
3. Execute Sql Task
4. Execute Sql Task

ESQL1 will get max(id) from table and then based on the ID the remaining needs to be work.

Ex:
If the ID=AUS-Sdy-1
then ESQL2 will update the tbl1 and return one IDkey based on this return key the ESQL3 will
update a tbl2. ----------*( i want to disable ESQL4)
else
If the ID=AUS-Sdy-2
then ESQL3 will update the tbl1 and return one IDkey based on this return key the ESQL4 will
update a tbl2. ----------- *(i want to disable ESQL2)


Thanks
Thiru

View 5 Replies View Related

Reporting Services :: Enable And Disable Checkbox?

Sep 16, 2015

How can we create a checkbox in SSRS and how can it be enabled disabled based on the value.

View 5 Replies View Related

How To Disable/enable Jobs For Database That Has Failed Over

Nov 22, 2006

Hi,

I currently have serverA importing data from an AS400 oledb connection and serverB on hot-standby as a mirror. The data import is being performed via an SSIS scheduled job.

I've yet to implement any solution in the following case:
If the database on serverA fails, it will failover to the mirror. But the jobs will still be on serverA. Ideally, I'm assuming the jobs should not run on serverB since it will be attempting to update the mirrored database. So, I am wondering if both servers can have identical copies of SSIS packages and jobs and simply (?) enable or disable them based on which server will be hosting the principal database.

Is there any solution where the jobs can automatically be disabled on the server with the mirrored database and enabled on the principal database depending on its status?

View 4 Replies View Related

Enable And Disable User In MsSql 2000 Database

May 25, 2007

Hi,




I'm using MsSql 2000 data base, how to enable and disable user in mssql 2000 version

View 3 Replies View Related

Is It Possible To Decide Whether To Enable/disable Interactive Sort At Runtime?

Jun 26, 2007

I have a parameter for each field that lets me know if a field can be sorted or not.
What I want to be able to do is turn on or off interactive sorting for that column based on that parameters value.

In the dialog you have a check box that says enable interactive sorting. When that is clicked it appears that you get the two arrows no matter what you put in the expression.

I have tried

=IIF(allowSorting,Fields!myColumn.Value,"")

and

=IIF(allowSorting,Fields!myColumn.Value,Nothing)

but both result in the arrows still being there, just the sort doesn't work.

Is it possible to put an expression on the sort arrows appearing at all?

View 3 Replies View Related

Want To Enable/disable Report Builder Option For Set Of Users

Feb 8, 2008


I want to restrict report builder access only to a reporting alias and block for other users.
Please suggest the solution

View 3 Replies View Related

DB Engine :: Trace Database Audit Specification Enable And Disable

Nov 10, 2015

How can i Trace Database Audit specification Enable and Disable. i want to maintain log forĀ  enable or disable database audit specification.

View 4 Replies View Related

Reporting Services :: Disable / Enable View Report Button

Sep 14, 2015

Is there a way to make the View Report disabled or enabled if certain condition exists?

View 3 Replies View Related

SQL Server 2005 Enable Sa Login && Disable Windows Authentication

Aug 29, 2007

HI all,

I would like to ONLY allow using SQL Server authentication and restrict Windows Authentication. At the moment, i set the security to "SQL server and windows authentication". Now, even though i set the password for "sa", i can ignore the SQL Server authentication and just use Windows authentication to manipulate database objects???


can anybody give some suggestions to restrict windows authentication and allow sql server authentication?.

Thanks in Advance

Best Regards
Ihsan


View 1 Replies View Related

DB Engine :: Shutdown Or Disable All Connections From Stored Procedure?

May 12, 2015

I have an auto exec stored procedure that needs to complete successfully or:

- the server should shutdown, or

- disable remote connections

Officially I cannot issue a Shutdown from a Stored Procedure. In addition, I can't see how to programatically disable remote connections.

View 6 Replies View Related

SQL 2012 :: SSDT Database Project - Cross DB Trigger Enable / Disable Gives Warning 71502

Jul 13, 2015

I have 2 dbs (SQL 2012) - one contains a trigger that is enabled/disabled by a procedure in the other database. This all works fine.

If I create a Database Project solution in Visual Studio 2012 SSDT (or 2013) for both databases, the stored procedure generates a SQL71502 stating that my trigger name can't be resolved.

To recreate the issue:

CREATE DATABASE DbWithTrigger
GO
USE DbWithTrigger
GO
CREATE TABLE dbo.TblWithTrigger(
Id int NULL,
SomeValue varchar(30) NULL

[code]....

-- Test to confirm

EXEC CrossDbTriggerCall
INSERT DbWithTrigger.dbo.TblWithTrigger VALUES(1, 'Blah blah')

In Visual Studio 2012:

1. Create a new solution with a project named DbWithTrigger
2. In project settings set the Target platform to SQL 2012
2. Import the DbWithTrigger db into this project
3. Create a new project named DbCallsTrigger
4. In project settings set the Target platform to SQL 2012
5. Import the DbCallsTrigger db into this project
6. Add a Database Reference in DbCallsTrigger for DbWithTrigger

When you build the solution both dbs build successfully, however there are two warnings. One is easily resolved by replacing DbWithTrigger in the body of the procedure with [$(DbWithTrigger)] (db variable name for the reference) but I can't find out how to get rid of the other. Is it a bug?

View 1 Replies View Related

How To Enable Or Disable A Task Programmatically Using Script Task

Mar 6, 2006

I have made one package which extracts data from the source does transformation and submits the data to destination. Subsequently it also updates the required control files.

Now I want to add a functionality :

If the package is executed again it should check the status of previous execution in control file if success mark all tasks disable and stop

if failure mark all tasks at enable and start extracting data and continue further with execution.

I was able to attain similar functionality in SQL Server 2000 using activeX script. What code do I need to write as a part of Script Task in order to attain above functionality.

View 3 Replies View Related

Drillthrough In Calculated Fields Enable When Drillthrough Option Is Disable In Original Fields, Is This A BUG?

Jan 21, 2008

Hi people
My users are having troubles with link to default drillthrough report when reports are exported to excel (they REALLY don't like this behavior ), so I decided set all of them disabled in report model, this work fine, but calculated field in reports has this drillthrough link.


Let me show you the situation. Entity Product has an UnitaryCost field, I set the EnableDrillthrough Property in False so when I export a report with this field, no link is shown.

But if I create in the report a calculated field Round(UnitaryCost) this field has a Drillthrough Link

Is this the standard and expected behavior? or its simply a BUG?

Have I done something wrong in my model? and in this case, How I can correct this?

regards.
Julio Diaz.

View 1 Replies View Related

/3GB /Userva=2900 /PAE (AWE Disable) -vs- /3GB /Userva=2900 /PAE (AWE Enable)

Nov 22, 2007

Hi Everyone,

I'd like to know the advantages and disadvantages of /3GB /PAE (AWE Disable) and /3GB /PAE (AWE Enable).

Server is Windows 2003 Enterprise is currently configured as /3GB /PAE (AWE Disable) and SQL Server Ent 2000 and AS2000 is up and running. Server has 8GB of physical ram.

Reading the blog: http://blogs.msdn.com/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx

From my understanding "/3GB /PAE (AWE Disable)" will make a 32bit User-Mode Process (say SQLServer or Analysis Services) to address "above 4GB of physical memory" by using its 32-bit Virtual Address Space (3GB because of /3GB switch) as a pointer mapping function to access data that is stored in the "above 4GB of physical memory".

This will allow the OS more physical memory space for storing "swapping" data instead of having to page this data to disk.

Now for "/3GB /PAE (AWE Enable)", this is the "above statements" plus, the capability of the process (SQL Server) to reserve the memory outside it's VAS for storage.

So in summary,
"/3GB /PAE (AWE Disable)" will make the OS use the additional memory <4GB for "paging/swapping" purposes, due to the VAS limitation of 3GB for User-Mode process.
and
"/3GB /PAE (AWE Enable)" will make SQLServer "reserve in advance" w/ agreement with the MAX Memory setting, memory above 4GB.

So if this is all true, I'd like to get opinons on the advantages and disadvantages of enabling or disabling AWE. Noting that Analysis Services has a Max Memory of around 2.5GB and Min Memory of around 2.0GB and SQL Server has Min Memory of 0 and Max Memory of 3.5GB (to be increased? - even w/o AWE?)

Thanks,
Henry

View 6 Replies View Related

SQL Security :: How To Disable User Access To The Critical And System Stored Procedures

Sep 22, 2015

disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?

View 5 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved