Forcing Data Format Mask Without Modifting Code

Sep 24, 2004

I have statement which is comparing a smalldatetime column to literal string as follows:

sales_date ='21-9-2004 0:0:0.000'

when I run the statement in query analyzer it bombs out with:

Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.

Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?

View 4 Replies


ADVERTISEMENT

Enter Data In A Mask Format

Aug 20, 2006

sorry for my question, maybe it seems un professional but I need to know the answer,

is it possible to enter the data in a sql table in a specific format and how it could be?

for example I want to have a mask in the table to enter the data in this format ##.## so it will not accept any other data to be typed in without this format.



Regards

View 4 Replies View Related

The Affinity Mask Specified Does Not Match The CPU Mask On This System.

Nov 17, 2007

We are having a problem with one of our SQL servers, and in comparing it to the backup server which is working fine, I noticed some differences. I attempted to correct the differences, but no luck.
The dell server has 4 dual-core processors and at one point hyper-threading was enabled. One of our DBAs recommended that it be turned off. We didn't have any major problems until recently and it seems that getting this setting right is the lynchpin. Any suggestions?

John




EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'affinity mask', N'0'

GO

EXEC sys.sp_configure N'affinity I/O mask', N'0'

GO

RECONFIGURE WITH OVERRIDE

GO

EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE

GO

-----------------------------------------


Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Msg 5832, Level 16, State 1, Line 1

The affinity mask specified does not match the CPU mask on this system.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'affinity mask' does not exist, or it may be an advanced option.

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51

The configuration option 'affinity I/O mask' does not exist, or it may be an advanced option.

Msg 5832, Level 16, State 1, Line 1

The affinity mask specified does not match the CPU mask on this system.

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

Msg 5832, Level 16, State 1, Line 1

The affinity mask specified does not match the CPU mask on this system.

View 7 Replies View Related

SELECT - BETWEEN &<mask&> AND &<mask&>

May 10, 2004

I have a table into which users can enter a To and From range, and an application returns all the values in between. Naturally the users can enter to and from values that don't exist, as all they need to do is satisfy a "between" expression. eg they can enter '1100' to '1399' and even though neither value actually exists in the data, the query will return all the values that fall into the desired range.

However the application now demands a bit more of a sophisticated approach. The target data may have the form 'nn-nnnn-nn' where n is '0' to '9', and the user wants to select all the values (in pseudo-code) which satisfy a pattern like '%-7040-%' and '%-7400-%'

A simple LIKE expression with a mask containing a RegEx won't work - ie LIKE '%-7[0-4][0-4]0-%' doesn't work because (eg) '00-7150-00' won't satisfy the mask because of the 5 falling outside [0-4], although it is within the range I want.

I have come up with a solution to this problem by using MIN and MAX aggregate functions on LIKE [From] and LIKE [To] respectively and this works fine. eg

"SELECT A.<something> from <target_table1> A, <table_with_ranges> B WHERE A.<something> BETWEEN (SELECT MIN(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[From]) AND (SELECT MAX(X.<something>) FROM <target_table1> X WHERE X.<something> LIKE B.[To])"

It's actually quite a bit more complicated than that - I have simplified the SQL a bit, but that is the basic algorithm.

The problem is that for the BETWEEN expression to work, both the MAX and MIN functions have to return a value that is not NULL- ie there has to be a value in the target table which satisfies the mask. In the application, this is not always going to be the case - Users will want to create large ranges to allow for future growth so every time they enter new data into target_table1 the don't then have to go out and redfine the [From] and [To] ranges to satisfy the new data. What I really need is an SQL expression along the lines of "SELECT .... FROM ... WHERE A BETWEEN <pattern1> AND <pattern2>" where <pattern1> and <pattern2> contain wildcards.

Anyone got any suggestions??? By the way, using SUBSTRING or other string functions is NOT an option for a variety of reasons.

View 3 Replies View Related

How To Mask Password Text In SQL Field Data Type.

Mar 12, 2007

AnupG writes "hi,
the most common feature that any RDBMS provides should be that if we want to store the data in the MSSQL tables then the data should be presented in masked/formatted like for eg.the password text in field should be like "*****". I am using SQL 2000 but i unofrtunately i couldn't find any such feature in defining the data type in SQL server 2000 ...?"

View 2 Replies View Related

What Happens If Affinity Mask And Affinity I/O Mask Are Not Mutually Exclusive?

Jan 21, 2008



I have a dual 64 bit quad core server with 16 GB of memory. We are going to run an application server and SQL Server 2005 SP2 CU4 64 bit on this hardware, but we only want to purchase a single CPU license for SQL Server. The obvious choice is to use the affinity settings to prevent SQL Server from using one of the CPUs.

Initially, the development team simply went into SSMS and unchecked affinity mask and affinity io mask for the first four processors. This appeared to work fine in their testing. A problem arose when we started monitoring the maintenance plan and saw that the database integrity check was failing. The root problem was this invalid state that the affinity masks were in.

I have seen a lot of documentation stating the SQL Server will ignore an invalid mask setting, but in our testing, it appears that SQL Server respects the setting. For example, when we set CPU 7 to be available for processing and IO, Performance Monitor showed that only CPU 7 was used during a load test.

So from our preliminary testing, it looks like SQL Server will use a single CPU for both processing and IO if you tell it to. Is there some other reason why these affinity settings need to be mutually exclusive? Is there a test I can run that can illustrate why?

View 7 Replies View Related

Forcing A Value If No Data Is Returned?

Jan 6, 2008

In the following procedure i write the results to a temp table called #temp1I now want to count the results of #temp1, if the count of #temp1 = 0
I want to insert 'No Records Found' into #temp.ERRORMSG else return what is in the table

any idea on how to do this?


ALTER PROC [dbo].[SPU_RPT_Savings_AnomalyDispatches] 40,'04/01/07|06/30/07'
@PropertyID varchar(4000),
@DropDown varchar(50)

AS
SELECT Client.CLIENT, Client.CLIENTID, ErrorEmailLog.ID, ErrorEmailLog.SITEID, ErrorEmailLog.PROPID, ErrorEmailLog.DISTINCTERRORS,
ErrorEmailLog.ERRORMSG, ErrorEmailLog.ERRORDATETIME, ErrorEmailLog.EMAILRECIPIENTS, Property.PROPERTY, Property.STREET,
Property.CITY, Property.STATE, Property.ZIP, Property.PHONE
INTO #TEMP1
FROM ErrorEmailLog INNER JOIN
Property ON ErrorEmailLog.PROPID = Property.PROPID INNER JOIN
Client ON Property.CLIENTID = Client.CLIENTID
WHERE (ErrorEmailLog.ERRORDATETIME BETWEEN SUBSTRING(CONVERT(VARCHAR(12), @DropDown), 0, 9)

View 3 Replies View Related

Affinity Mask And Affinity I/O Mask Configuration

Sep 17, 2007

I'm experiencing some problem with the affinity mask and affinity I/O mask configuration.

We are running SQL Server Enterprise Edition 2005 64bit on Windows 2003 Server 64bit with 4 dual core CPU. The actuelly configuration is:

Affinity Mask Affinity I/O Mask
CPU0 0 0
CPU1 0 0
CPU2 1 1
CPU3 1 1
CPU4 1 1
CPU5 1 1
CPU6 1 1
CPU7 1 1


We did it so, because we are running LiteSpeed Tool for the backup. LiteSpeed is configured to use CPU0 and CPU1.

Now, we know that the settings are wrong. If Affinity mask is 1 affinity I/O mask should be 0 and vice-versa.

My question is: how many CPU should I enable for affinity mask and how many for affinity I/O mask. Which are the correct criterias.

Thx in advence.
Laurent

View 2 Replies View Related

Char Format Of Zip Code

Apr 14, 2015

I have a character string for a zip code field. I am only looking for zip codes in the 01001 - 02791 range. Will where zip_code between '01001' and '02791' work on a character field? It seems to be dropping out some records but I cant find them.....

View 3 Replies View Related

Select And Format Zip Code Results

May 19, 2008

In my select statement I concatenate city+state+zipcode. In some cases the zipcode is 9 digits but is missing the '-'. How can I format zipcode to be '#####-####' when > 5 characters from the select statment.
Thanks,
Ken

View 1 Replies View Related

Format Code Issue In The Chart

Feb 28, 2008

Hi all,
I am trying to change the negatives to (100,000) instead of -100,000. I am using N0 as my format but I dont want to use currency because I dont want the dollar sign. How can I change this? Is there anyway to use currency without the $ sign?

This can be acheived by writing a format expression for a text box, but i wanted this to be acheived in the bar chart where there is no expression(Fx) for format option(Chart Properties ->Data Tab -> Values ->>Value1 -> Edit tab -> Point Labels -> Format code) in the chart.

Please help me if anyone knows the answer to this issue.


View 4 Replies View Related

Using Custom Code To Format Matrix Appearance

Feb 11, 2008



Hello,
I am attempting to modify an existing matrix report into something a bit more diverse and aesthetically appealing to our users. The problem is changing the size of columns, rows, border types, etc.
So far I have not yet been able to locate any custom code samples that refer to ways of changing the overall appearance and properties of the matrix. I am not concerned with the data but in some cases a column's width will need to be larger or smaller based on the size of the data inside it.
The strings can range from 3 characters to 15 or more and the number of columns can be just as dynamic in number. If that happens I need to shrink or expand a column to match.

I am familiar with using custom code just not in using it to access the matrix properties.


Thanks.

View 5 Replies View Related

I Want Ot Convert Xml Data Format Into The General Data Format

Jun 9, 2006

hi siri have table hh .it has two columnsone is hhno is integer datatype another hhdoc is xml data type likehh tablehhno hhdoc---------------------------------------------------------------------------------------------100<suresh>sfjfjfjfjf</suresh>....................................101<ramesh>hhfhfhf</ramesh>..................................how to convert the xml data format into the general data format plshelp me with examples

View 1 Replies View Related

Custom Code/function To Format Seconds To Hh:mm:ss With Ability To Go Over 24 Hours

Aug 4, 2007



Hello,

I am trying to get this to work - but it only returns minutes & seconds:

Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function

Can anyone help me out? I am not that familiar with VB.

Thanks,
Deb

View 2 Replies View Related

How Can I Mask A Column In A Sql Server Database

Sep 26, 2007

Hi i have a table in my database and i want to mask or encrypt a particular column in it? How can i do it.
 
Any help will be appreciated.
Regards
Karen

View 1 Replies View Related

SQL 2012 :: Config Value For Affinity Mask

Aug 7, 2014

I ran ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 2,3 that means the SQL server 2012 is using the 2nd and 3rd CPU's respectively..

Now, when I run sp_configure 'affinity mask' I get the config_value as 12, so how it is getting calculated?

is there a code for each CPU?? wanted to know about this.

View 4 Replies View Related

IP Address And Subnet Mask Calculations

Nov 12, 2007

-- Prepare sample data
DECLARE@Sample TABLE (ID INT, NetworkIP VARCHAR(15), SubnetMask VARCHAR(15))

INSERT@Sample
SELECT1, '192.168.1.0', '255.255.255.128' UNION ALL
SELECT2, '10.1.1.64', '255.255.255.240' UNION ALL
SELECT3, '172.16.11.0', '255.255.255.252'

-- Show the expected output
SELECTID,
NetworkIP,
SubnetMask,
dbo.fnIsInRangeIP('192.168.1.5', NetworkIP, SubnetMask) AS IsInRange
FROM@SampleAnd here is the function codeCREATE FUNCTION dbo.fnIsInRangeIP
(
@IP VARCHAR(15),
@NetIP VARCHAR(15),
@MaskIP VARCHAR(15)
)
RETURNS BIT
AS
BEGIN
RETURNCASE
WHENCAST(PARSENAME(@IP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT) = CAST(PARSENAME(@NetIP, 4) AS TINYINT) & CAST(PARSENAME(@MaskIP, 4) AS TINYINT)
AND CAST(PARSENAME(@IP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT) = CAST(PARSENAME(@NetIP, 3) AS TINYINT) & CAST(PARSENAME(@MaskIP, 3) AS TINYINT)
AND CAST(PARSENAME(@IP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT) = CAST(PARSENAME(@NetIP, 2) AS TINYINT) & CAST(PARSENAME(@MaskIP, 2) AS TINYINT)
AND CAST(PARSENAME(@IP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT) = CAST(PARSENAME(@NetIP, 1) AS TINYINT) & CAST(PARSENAME(@MaskIP, 1) AS TINYINT)
THEN1
ELSE0
END
ENDE 12°55'05.25"
N 56°04'39.16"

View 1 Replies View Related

ForEach File - Filename Mask

Dec 20, 2007



Hello

I'm using the ForEach File task and masking the files I want to process as ABC??.TXT.
It all works well until I drop a file there like ABC12 Copy.TXT, which I don't expect to be process, but the loop still picks it up. Am I using the wrong wildcards?

newstar1860

View 18 Replies View Related

How Can I Encrypt Or Mask A Column In The Database

Sep 26, 2007



How can i encrypt or mask a column in the database?

Any help will be appreciated
Regards
Karen

View 4 Replies View Related

Stripping Input Mask From Phone Numbers

Mar 28, 2006

Hello, I have this Access 2K query that I need to re-create in MS SQLServer 2000, so I'm using the Query Analyzer to test it.One of the Access fields stores the home phone number. In the Accessquery, if the phone number is null, it fills it up with zeroes"000000000." If the phone has an input mask, it only gets the 9 numbers(area code included) and if the phone number's good (all numbers) thenit leaves it alone. That Access query is using immediate ifs toaccomplish that task.Does anyone have any idea how to copy this behavior into SQL Server2000? I've using the CASE statement but so far my code is not correct.I get stuck in the input mask. This is the Access code:HomePhone:IIf(IsNull([HomePhone]),"0000000000",IIf(Left([HomePhone],1)="(",Right(Left([Homephone],4),3)& Right(Left([Homephone],9),3) & Right([HomePhone],4),[HomePhone]))Thanks for all your help.JR.

View 2 Replies View Related

Syntax Problem With Mask Special Characters

Sep 29, 2006

I have the following t-sql syntax

problem is masking information after like as special characters.

I will search for all entries in column filedirectory which begins with \

DECLARE @cmd varchar(255)
SET @cmd = 'SELECT id,name,filedirectory FROM MR_ReqPro.RQDOCUMENTS WHERE FILEDIRECTORY LIKE  ' \%'  '
print @cmd

EXEC master.dbo.xp_sendmail @recipients = 'kalleOO7@web.de',
   @query = @cmd , @subject = 'Fehler', @message = 'Fehler :', @attach_results = 'TRUE', @width = 2000

Thx

Kalle

 

here is the solution

DECLARE @cmd varchar(255)
SET @cmd = 'SELECT id, name, filedirectory FROM MR_ReqPro.RQDOCUMENTS WHERE filedirectory LIKE ''\%'' '
print @cmd

seems to work :-)

View 1 Replies View Related

How To Get The SQL Affinity Mask Value In Sql 2005, And Table That Store This Info

Aug 29, 2006



Hi All

Can u pls tell me How to Get the SQL Affinity Mask value in Sql 2005.

and what is the table/views name that store this info.

for SQL 2000 , we were using following query:

SQLStatement.printf("select c.value from master..sysconfigures c, master..spt_values v,"
"master..syscurconfigs r where v.type = 'C' and v.number = c.config and v.number >= 0 "
"and v.number = r.config and v.name ='affinity mask'");

lErrCode=SQLExecDirect(hStmt,(SQLCHAR*)pszSQLStatement,strlen(pszSQLStatement));

lSQLBindCol(nAffMask);

But With SQL 2005 , we are getting ZERO(0) ROWS SELECTED.

View 1 Replies View Related

Forcing Indexes...

Jan 4, 2008

Hi Experts,

In our production system, there are high number activities involving very huge tables ( around 250 million records ).

For performance benefits , we are using dynamic queries in the stored procedures. We are also using WITH clause to FORCE appropriate indexes.

Will forcing the indexes have any negative effects ? or Forcing the index would REALLY improve the performance.

Any inputs would be highly appreciated.

Thanks in advance.

Hariarul

View 1 Replies View Related

Forcing 32 Bit SSIS

Oct 24, 2006

I have an Itanium 64bit server to run SSIS packages on. I have one package with three parralell streams. When I run the package in 64 bit mode using dtexec, it runs through validation and exits with no reported errors, when I run it from a job, the job fails and says to see job log, which has no errors.

When I run it in 32 bit mode using the GUI, it runs all the way through.

Does anyone know how to launch SSIS in 32 bit mode from a job on an Itanium?

Thanks
Larry C

View 5 Replies View Related

Forcing DB Changes To Commit In SQL CE !!!

Sep 9, 2007

This is a really wide spread - more than a time discussed - on SQL CE MSDN Forums - Issue !!!
Is there any way i can commit changes which happens during runtime (when i am developing the application) such as inserts/updates and deletes to the .sdf DB on the machine ?????

View 34 Replies View Related

Forcing Primary Keys

Jun 1, 2006

Hi all,

As our DB has no primary keys or indexes ive taken a copy of all populated tables and tried to force primary keys within a new DB.

the problem is all off the tables have multiple datasets within them, a dataset for each year. This causes all instances of ID numbers to not be unique as they are replicated for every year they are active.

Its a school database so a student who has been here for 3 years will have 3 instances of his ID number, one for each years' data set.

So how do i force primary keys if there is no unique identifier? ive been highlighting both data set and ID columns and setting that combination as the primary key.

Essentially i need to analyse the relationships between the tabls in a diagram and also run some speed tests to see how fast the db works when it has indexes and primary keys.

the reason im writing is that ive done this on ten tables and with another 160 to do im just checking im doing the right thing?



greg

View 14 Replies View Related

Forcing Every Hour Of The Day To Show?

Dec 3, 2013

I have the following Case statement:

CASE
WHEN CAST(wo.start_date AS TIME) BETWEEN '00:00:00' AND '00:59:59' THEN 0
WHEN CAST(wo.start_date AS TIME) BETWEEN '01:00:00' AND '01:59:59' THEN 1
WHEN CAST(wo.start_date AS TIME) BETWEEN '02:00:00' AND '02:59:59' THEN 2
WHEN CAST(wo.start_date AS TIME) BETWEEN '03:00:00' AND '03:59:59' THEN 3
WHEN CAST(wo.start_date AS TIME) BETWEEN '04:00:00' AND '04:59:59' THEN 4

[code]....

The purpose is to take a row and set it to the hour of the day that it occurred in. This works fine, however I would like to force it to display every hour 0-23 regardless of whether or not it has a corresponding row.

So, if no row exists for 0, display 0 with null values for the rest of the columns.

View 2 Replies View Related

Forcing AutoNum To Generate A Number

Oct 4, 2001

Hello all,
Is there any way to force Autonum to generate a number before an entire record is created? Some of my forms will not work because it needs a number already listed in its index (which uses Autonum) and cannot add to the table until it is created.I really need it to have a number ready and waiting upon the last record's completion.

View 1 Replies View Related

Forcing A Timeout On A Stored Procedure

May 2, 2008

im testing an application change that should handle a timeout on a stored procedure being called from the application. thing is, the timeout that we experience in production that led to this fix is random. so is there some way for me to setup a test stored procedure or some way to call the SP so that i can test a timeout scenario?
im using MFC and the CDatabase::ExecuteSQL method to call this SP if you were wondering at all.
this app is running locally on the server that has an instance of SQL Server Express 2k5 on it. server is running win 2k3.

View 4 Replies View Related

Forcing Specific Distinct Columns....

Dec 6, 2006

Hi Guys,

I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...

Is their a way to force 1 column to always be unique?

Heres the query:


SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC


The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.

Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:

ItemID
83630Mathew Smith
8 366Tony Wattage

Which is kinda annoying.... is there anyway i can prevent this?

And also apart from the Where Joins... is there a more efficient way of writing this?

thx for reading :-)

--Philkills

View 14 Replies View Related

Forcing Dbsqlexec To Exit Cleanly

Jul 23, 2005

I am developing a simple DB-Library program in C calling SQL Server 2000 onwindows 2003 and NT 4. I have some T-SQL code that checks for the existenceof a table and want to abort the program if the table doesn't exist. I issuea raiserror if the table doesn't exist and then call RETURN.I construct the string using sprintf and pass it dbfcnd and dbsqlexec. Sincethe commands work, there is no error to halt the execution of the program.Is there an easy, clean way to force dbsqlexec to fail? Do I need a storedprocedure to return an error code and then deal with that?Thanks for any advice,-Gary

View 3 Replies View Related

Forcing Function Recompilation In SQL 2000

Sep 12, 2006

A stored procedure in the cache is automatically recompiled when a table it refers to has a table structure change. User defined functions are not. Here's a simplified code sample:

set nocount on
go

create table tmpTest (a int, b int, c int)

insert into tmpTest (a, b, c) values (1, 2, 3)
insert into tmpTest (a, b, c) values (2, 3, 4)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fTest]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fTest]
GO

CREATE FUNCTION dbo.fTest (@a int)
RETURNS TABLE
AS
RETURN (SELECT * from tmpTest where a = @a)
GO

select * from fTest(1)

CREATE TABLE dbo.Tmp_tmpTest
(
a int NULL,
b int NULL,
d int NULL,
c int NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.tmpTest)
EXEC('INSERT INTO dbo.Tmp_tmpTest (a, b, c)
SELECT a, b, c FROM dbo.tmpTest TABLOCKX')
DROP TABLE dbo.tmpTest
EXECUTE sp_rename N'dbo.Tmp_tmpTest', N'tmpTest', 'OBJECT'

select * from fTest(1)

drop table tmpTest

Running it, the output is:

a b c
----------- ----------- -----------
1 2 3

Caution: Changing any part of an object name could break scripts and stored procedures.
The OBJECT was renamed to 'tmpTest'.
a b c
----------- ----------- -----------
1 2 NULL


(I know that "select *" is bad, but it's a lot of legacy code that I'm working with here, and that's how it's written.)

The function doesn't detect that the table has changed in structure, or even that there is no longer a dependency on tmpTest. (Appending a column rather than inserting has the same effect, in that only the first 3 columns are returned.)

DBCC FREEPROCCACHE has no effect, not that I really expected it to, but you never know...

Is there any way, other than dropping and recreating, to force a recompilation of a particular function in memory, or perhaps all functions?

Thanks in anticipation.

Tom



View 6 Replies View Related

Forcing Regeneration Of Service Master Key

Feb 4, 2008

Due to a lack of planning during an Active Directory migration last year, I'm now stuck with an immutable service master key on one of my production servers. Since I'm posting here, I guess it's obvious that we have no backup from which to restore. The account that all of the SQL services used to run under no longer exists, so the WITH OLD_ACCOUNT workaround is not viable. And REGENERATE fails, as expected, with Msg 15329, Level 16, State 2, Line 2, "The current master key cannot be decrypted..."

After some research, including several of Laurentiu's blog entries, it seems that my only path at this point is to use the FORCE option to REGENERATE. (And then to immediately backup the service master key at several geographically disparate locations!!

Considering that:


We aren't actively using any of SQL Server's encryption capabilities, the closest we come is that one of our legacy applications calls the old PWDENCRYPT() function to hash passwords


##MS_ServiceMasterKey## is the only record in master.sys.symmetric_keys, and every other database's sys.symmetric_keys table is empty

What, if anything, am I likely to lose if I ALTER SERVICE MASTER KEY FORCE REGENERATE? My understanding is that since we don't have any database master keys and aren't using encryption, there's no real potential for corruption or loss. However, I want to be a little more confident about this before I give it a go.

Pointers appreciated.

View 4 Replies View Related







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