Hash Buckets (sp_configure)
May 15, 2000
Hi, I've been through Books Online, Technet, MSN and the archives on this site trying to get a difinite answer on this. Everything except Technet indicates a maximum configurable value of 265003. However in Technet, it says that if you are running the enterprise edition with more than 2GB of memory that you can configure this to a much higher number (their example is 700000). Using the calculation involving memory, and based on several dozen performance tests, we would like to set our value to around 666000 (using the prime number of course). However we cannot get SQL to accept any value over the default maximum.
Is Technet wrong? Are we missing something? I cant find any reference other than in Technet on being able to use the higher number.
Thanks in advance,
Chris
View 1 Replies
ADVERTISEMENT
Jul 11, 2015
Recently I've been stumbling with a way to properly produce a result that provides me a count of sales orders that fall in a range of sales order totals.
I want to take the Sales Order Total and then count how many times it falls into a specific bucket.
I've included the script where I've left with sample data so you can run in into a tempdb.
I hope to have the end result look like the below summary if you use my sample data.
Count Less Than 5K: 5
Count Greater Than 5K Less Than 10K: 0
Count Greater Than 10K Less Than 25K: 3
Count Greater Than 25K: 2
Here is the script to set up the tempdb
use tempdb
go
/*
drop table #salesinvoice;
drop table #salesinvoicedetail;
[Code] ....
Below is my query, I change it to perform a count on the summarized totals.
select
si.OrderID as OrderNum
,sum(case when sid.LineAmount < 5000 then 1 else 0 end) as LT5K
,sum(case when sid.LineAmount >=5000 and sid.LineAmount < 10000 then 1 else 0 end) as GT5kLT10k
,sum(case when sid.LineAmount >=10000 and sid.LineAmount < 25000 then 1 else 0 end) as GT10kLT25k
[Code] ....
Here are the order totals.
SI201 = 41450
SI202 = 11600
SI203 = 1210
SI204 = 16001
SI205 = 2750
SI206 = 12500
SI207 = 25450
SI208 = 4500
SI209 = 3850
SI210 = 2575
And the desired result would tell me the below summary. The final query can be column based. I just write this in rows for clarity.
Count Less Than 5K: 5
Count Greater Than 5K Less Than 10K: 0
Count Greater Than 10K Less Than 25K: 3
Count Greater Than 25K: 2
******/
View 3 Replies
View Related
Sep 29, 2015
What I'm looking to do is very much the image below. I need to bucket the DOS. I cannot do a calculation in a column because the DOS needs to recalculate based on filter changes or how graphs are setup. My DOS is a calculated field. When I try to create a calculated field to create the buckets I can't use it in a pivot table or powerview.
View 3 Replies
View Related
Aug 2, 2013
I have a table of orders. I was asked to count the orders that fall into specific dollar buckets. Normally I would use a CASE statement for this, but in this case, there are over 100 different buckets!
For example, I need to count the orders in $5 increments up to $400. The CASE statement would look like this:
Code:
CASE
WHEN sum(revenue) BETWEEN 0.01 AND 5.00 THEN [0.01 to 5.00]
WHEN sum(revenue) BETWEEN 5.01 AND 10.00 THEN [5.01 to 10.00]
...
WHEN sum(revenue) BETWEEN 395.01 AND 400.00 THEN [395.01 to 400.00]
Is there an easier way to do this, maybe with a loop?
View 11 Replies
View Related
Mar 2, 2004
I am running the following command:
exec sp_configure 'allow updates', '0'
I get:
Server: Msg 15247, Level 16, State 1, Procedure sp_configure, Line 169
User does not have permission to perform this action.
What's causeing this?
Thanks!
Ken
View 1 Replies
View Related
Aug 1, 2007
Please some help. I had visual basic express installed on my computer and it worked. After I have been watching the video about the reportviewer I wanted it in my toolbox but it was not there. I put it on the forum and I was suggested to download SQL Server 2005 Express Edition with Advanced Services SP2 and Microsoft SQL Server 2005 Express Edition Toolkit. The reportvieuwer was there but it did not work. Now nothing is working anymore. I can't connect anymore to a database because off the message sp_configure etc. Also the option Create new Sql database is not availeble anymore. I'me working wth Vista. I have already removed everything en reinstalled it according microsofts visual express website but with the same result. Also when I try to install these programs I get a message:
- IIS Feature Requirement (Warning)
Messages
* IIS Feature Requirement
* Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.
I have enabled all IIS components.
View 3 Replies
View Related
Apr 11, 2008
Hi,i am using vwd2005 express and sql express.i have downloaded a web application and trying to run it. But when i run this application its showing some error in the page. like this:-Server Error in '/ProfileSample' Application.
Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
SQLExpress database file auto-creation error: The
connection string specifies a local Sql Server Express instance using a
database location within the applications App_Data directory. The
provider attempted to automatically create the application services
database because the provider determined that the database does not
exist. The following configuration requirements are necessary to
successfully check for existence of the application services database
and automatically create the application services database:
If the applications App_Data directory does not already exist, the
web server account must have read and write access to the applications
directory. This is necessary because the web server account will
automatically create the App_Data directory if it does not already
exist.If the applications App_Data directory already exists, the web
server account only requires read and write access to the applications
App_Data directory. This is necessary because the web server account
will attempt to verify that the Sql Server Express database already
exists within the applications App_Data directory. Revoking read access
on the App_Data directory from the web server account will prevent the
provider from correctly determining if the Sql Server Express database
already exists. This will cause an error when the provider attempts to
create a duplicate of an already existing database. Write access is
required because the web server accounts credentials are used when
creating the new database.Sql Server Express must be installed on the machine.The process identity for the web server account must have a local user profile. See the r But same application is running well in my friend system.I dont know whats wrong here.does anyone has a answer?Thanks.Jack
View 2 Replies
View Related
Apr 14, 2008
1.could any one tell me the difference between sp_dboption and sp_configure.
View 1 Replies
View Related
Dec 23, 2007
Another day in paradise. I updated to C# 2008 Express and the 2005 sql express.
Now I get the error in the Database Explorer of both 2005 and 2008 editions:
"Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances"
A search turned up this link which does address it:
http://forums.microsoft.com/msdn/showpost.aspx?postid=294798&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=0
Their Answer:
Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.
Not very clear to me and what I did seems to have no effect. Please advise.
I found setting using user instances in the properties window to false does correct this but causes other problems.
View 1 Replies
View Related
Jan 7, 2008
Can someone help? I am new to Visual basic programming and have down-loaded Visual Basic Express. I followed the instructions for creating my first database but when I got to making the SQL database I got the following error message. " Generating user instances in SQL server is disabled, use sp_configure "user instances enabled" to generate user instances" Where do I find sp_configure and how do I start it.
View 1 Replies
View Related
Aug 22, 2006
I updated my default (only) instance of sql2k to 2005. I'm now working on fixing backward compatibility issues, among these sp_configure 'allow updates'.
According to the BOL, reconfigure will throw an error if 'allow updates' is set to 1. And updates to system tables is disallowed entirely. However, when I started my work on a script that does these things, it ran without error on the newly updated server.
This code works, and if I understand the BOL correctly, it should not:
sp_configure "Allow Updates", 1
GO
reconfigure with override <-- works without complaint
GO
update sysusers set name='283c' where uid=31 <-- works without complaint
update sysusers set name='283c' where uid=31 <-- works without complaint
go
sp_configure "Allow Updates", 0
GO
reconfigure
GO
Did I miss something in my upgrade? I used the wizard. The management tool reports my version as 8.0.760. This database was present during the upgrade. I have not restored it from backup.
View 1 Replies
View Related
May 23, 2007
Hi.
I right click the (root) Solution Explorer to add an SQL object (something.mdf) but I get an error message
that "Generating user instances in SQL Server is disabled. Use sp_configure 'user_instances enabled' "
WHERE DO I WRITE THE SP_CONFIGURE CODE IN VISUAL WEB DEVELOPER ?
(I don't see anything like new query of server and the one in the SQL SERVER management studio is already done).
10x
View 7 Replies
View Related
May 6, 2002
Can Enterprise Manager under certain circumstances do sp_configure 'show advanced options' without the knowledge of the user ? And could this action lead to locking/blocking in the actual server ?
Brgds,
Odd
View 1 Replies
View Related
Aug 21, 2007
HIiiiiiii all
I have installed both sql server 2000 and sql server 2005 Developer Edition in my system as well as .net 2003 and 2005. Now i want to enable clr integration in sql server 2005. so when i write
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
In Sql Statement then it gives me error
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78
The configuration option 'clr enabled' does not exist, or it may be an advanced option.
Valid configuration options are:
I have created a database user which have all the rights and roles. and while i check the stored system procedure sp_configure it is affacting mainly two system tables in the master tables
master.dbo.spt_values
master.dbo.sysconfigures
and this table does not contains field like ''clr enabled'' so tht we can change the state
so plz plz help me to enable clr in the sql server 2005.
I need it so muchhh...
Thanking you in advance
View 7 Replies
View Related
Mar 13, 2008
I am running Windows Server 2003 R2 standard Edition with SP1, using IIS, and SQL Server 2000 Enterprise Edition. Server running 4 core processors with 4CGB of RAM. I have not changed any entries to the boot.ini or with SQL Server to take advantage of the memory. I am looking for the best way to utilize the hardware in the machine????
This is dedicated DB server under split server configuration
Would it be useful if swith to /3g for AWE enabled option for sp_configure?
Please suggest me the best configuration methods if any
Thanks in advance,
View 4 Replies
View Related
Apr 5, 2007
Hi,
I need to take the Social Security number and hash it into a unique identifier because I can't display Social Security numbers on a report.
Does Reporting Services have any encrypting/hashing routines that I can use?
Any suggestions?
Thanks.
View 2 Replies
View Related
Jun 7, 2008
Hello,
I would like use hash function from C# in my SQL procedure. But I don't know how.
Martina
View 4 Replies
View Related
Jul 20, 2005
It seems like there is no built in procedure for making a password hash inSQL2000. Am I wrong.Do I have to make it from scratch myself or is there samples out there?/Jens Ulrik
View 3 Replies
View Related
Mar 12, 2008
Hi!
I want to know wether SQL Server 2005 (that I use) has a built in support for creating MD5 hashes and if it has then can someone give me an example how to use it.
I need to create hash of image field and then compare it to another one that has been sent from application. The hash from my app is sent as string so it would be great if the hash result would be returned as 32 character string
The Software is wrintten in visual basic .net 2005 and it has a built in MD5 hash function so I have taken care of that part.
P.S. If SQL Server 2005 doesn support MD5 maybe someone know other hash alghoritms that Server does support (maybe one that also is supperoted by VB.NET 2005).
View 6 Replies
View Related
Aug 17, 2006
Many articles on sql server security make reference to Hash functions. Do you know of a simple example of a hash function that I could show to others?
For example, would taking the first eight bytes of the mathematical "sin" of a number be a good function? I don't know. Or is a hash "function" actually an involved algorithm, so the "simple" formula I was looking for really doesn't exist.
TIA,
Barkingdog
View 6 Replies
View Related
Apr 17, 2007
Do we have a hash structure in t-sql ? I want that a stored procedure accept a hash table as input (which contains 'column names => new_value' as keys and values). A small piece of code demonstrating the use can be useful.
View 4 Replies
View Related
May 8, 2008
Hiya,
I am wondering, in theory, how I would use a typical one-way hash function to protect customer password on my website server (which is a small e-commerce server)?
TRhanks MSDN, as always!
View 3 Replies
View Related
Apr 8, 2008
I have implemented a script to perform a MD5 hash on each row processed by the SSIS package so that it can be compared with a stored value to see if there has been a change in the record. This package processes over 1 million rows. In 12 of these rows I get a hash value that is different than the stored value despite the fact that the rows "look" identical. Curious about this, I used the both the CheckSum and Binary_Checksum feature from t-sql to check the rows and they both show the identical checksum value. I have exported the rows into text and did a compare and the records are identical. I assume there must be some hidden characters that is causing the hash to be different, has anyone else run into this issue? Any help is much appreciated.
View 5 Replies
View Related
Mar 30, 2007
Ok, here is what i'm trying to do and its driving me nuts.
ok,
1) I have a proc that runs and needs to validate the user prior to running - this proc is called from an hand held device
2) the id and password are being passed as "clear text" but the password is stored in the database table hashed.
Is there anything on the db side that can get the hash value from the password column of the aspnet_membership table and compare it to the password being passed in to this proc? I have suggested several options to the handheld developer but nothing. This has to be done on the database side.
so,
username and password are passed to proc from handheld.
proc needs to validate ther user in the aspnet_membership table
if user id and password are valid execute the stored procedure
is this possible? if so can ANYONE point me to some examples of this being done?
View 4 Replies
View Related
Jan 29, 2008
In my example I join two tables DimCustomer and FactInterSales from AdventureWorksDW database
In T-SQL it's simply query
Code Snippet
select a.CustomerKey,
a.FirstName,
b.SalesOrderNumber
from AdventureWorksDw.dbo.DimCustomer a,
AdventureWorksDw.dbo.FactInternetSales b
where a.CustomerKey=b.CustomerKey
In SSIS it's simple task too
€˜As input two queries stored in variable ADO.Recordset
Code Snippet
Public Sub Main()
Dim SrcAd As New OleDbDataAdapter
Dim SrcA As New Data.DataTable("DimCustomer")
Dim SrcB As New Data.DataTable("FactInterSale")
Dim DstC As New Data.DataTable("Output")
Dim TabA As New Hashtable()
SrcAd.Fill(SrcB, Dts.Variables("varFactInternetSales").Value)
SrcAd.Fill(SrcA, Dts.Variables("varDimCustomer").Value)
€˜ create destination product
Dim col01 As DataColumn = New DataColumn("CustomerKey")
col01.DataType = System.Type.GetType("System.Int32")
DstC.Columns.Add(col01)
Dim col02 As DataColumn = New DataColumn("FirstName")
col02.DataType = System.Type.GetType("System.String")
DstC.Columns.Add(col02)
Dim col03 As DataColumn = New DataColumn("SalesOrderNumber")
col03.DataType = System.Type.GetType("System.String")
DstC.Columns.Add(col03)
€˜ populate hash table based on PrimaryKey
For Each row As DataRow In SrcA.Rows
TabA.Add(row.Item("CustomerKey").GetHashCode(), row)
Next (row)
Dim myNewRow As DataRow
Dim tmpRow As DataRow
€˜ make hash join
For Each row As DataRow In SrcB.Rows
tmpRow = CType(TabA(row.Item("CustomerKey").GetHashCode()), DataRow)
myNewRow = DstC.NewRow()
myNewRow("CustomerKey") = tmpRow.Item("CustomerKey")
myNewRow("FirstName") = tmpRow.Item("FirstName")
myNewRow("SalesOrderNumber") = row.Item("SalesOrderNumber")
DstC.Rows.Add(myNewRow)
Next (row)
€˜ write DataTable in SSIS variable for other processing
Dts.Variables("varOutput").Value = DstC
Dts.TaskResult = Dts.Results.Success
End Sub
View 4 Replies
View Related
Nov 4, 2015
Here what I want to accomplish, I have table that has 15 column one of the columns is ClientID that is about max 45char. I want to be able to hash on the column and return the hash value in a new column call ClientID_Hash. Is this possible?
View 2 Replies
View Related
Jul 20, 2005
Hi there,I have created a hash table. After using it, somehow the primary keyconstraint of this hash table still exist in database. Which causeerror.When I delete this constraint with Alter table Drop con....It gives no table exist error.Can anybody give any idea.Thanks in Adv.,T.S.Negi
View 2 Replies
View Related
Dec 20, 2007
I have two queries that seem to be the same, but perform very differently. The first query runs very fast (7000+ records returned in <1 sec.). The execution plan shows that it uses a nested loop with index seeks on both tables.
select *
from t_loadbasic
where ld_nbr in (select ld_nbr from t_tripcombined where comp_date between '11/1/07' and '11/05/07')
The second query is almost the same, save the fact that it uses date variables instead of hard dates. The execution plan shows that it uses a hash match instead of a nested loop with an index scan on the main table (t_loadbasic). This query takes about 12 seconds to run.
declare @startdate datetime
,@enddate datetime
set @startdate = '11/1/07'
set @enddate = '11/5/07'
select *
from t_loadbasic
where ld_nbr in (select ld_nbr from t_tripcombined where comp_date between @startdate and @enddate)
I'm trying to figure out why the database executes these two statements so differently. BTW, I've tried switching the order of the tables. I've tried using joins instead of a subquery. The execution plan seems completely dependant on the use of variables. I can attach the execution plans if necessary.
I apologize if this is too simple a question, but I couldn't find an answer on any forums, web searches or BOL. Thanks in adavance.
View 1 Replies
View Related
Jun 21, 2006
Could Any body Please give me one example of each of three types of joins that is
Merge Join
Hash Join
Nested join
View 5 Replies
View Related
Jul 25, 2007
Hi,
We are using binary_checksum in some of instead of update trigger. The problem came into the knowledge when update falied without raising any error. We came to know after research that checksum returns same number for two different inputs and thats why update failed.
We are using following type of inside the trigger.
UPDATE [dbo].[Hospital]
SET
[HospitalID]= I.[HospitalID],
[Name]= I.[Name],
[HospitalNumber]= I.[HospitalNumber],
[ServerName] = I.[ServerName],
[IsAuthorized]= I.[IsAuthorized],
[IsAlertEnabled]= I.[IsAlertEnabled],
[AlertStartDate]= I.[AlertStartDate],
[AlertEndDate]= I.[AlertEndDate],
[IsTraining]= I.[IsTraining],
[TestMessageInterval]= I.[TestMessageInterval],
[DelayAlertTime]= I.[DelayAlertTime],
[IsDelayMessageAlert]= I.[IsDelayMessageAlert],
[IsTestMessageAlert]= I.[IsTestMessageAlert],
[IsUnAuthorizedMessageAlert]= I.[IsUnAuthorizedMessageAlert],
[IsWANDownAlert]= I.[IsWANDownAlert],
[IsWANUpAlert]= I.[IsWANUpAlert],
[CreateUserID]= Hospital.[CreateUserID],
[CreateWorkstationID]= Hospital.[CreateWorkstationID],
[CreateDate]= Hospital.[CreateDate] ,
/* record created date is never updated */
[ChangeUserID]= suser_name(),
[ChangeWorkstationID]= host_name(),
[ChangeDate]= getdate() ,
/* Updating the record modified field to now */
[CTSServerID]= I.[CTSServerID]
FROM inserted i
WHERE
i.[HospitalID]= Hospital.[HospitalID]
AND binary_checksum(
Hospital.[HospitalID],
Hospital.[Name],
Hospital.[HospitalNumber],
Hospital.[ServerName],
Hospital.[IsAuthorized],
Hospital.[IsAlertEnabled],
Hospital.[AlertStartDate],
Hospital.[AlertEndDate],
Hospital.[IsTraining],
Hospital.[TestMessageInterval],
Hospital.[DelayAlertTime],
Hospital.[IsDelayMessageAlert],
Hospital.[IsTestMessageAlert],
Hospital.[IsUnAuthorizedMessageAlert],
Hospital.[IsWANDownAlert],
Hospital.[IsWANUpAlert]) !=
binary_checksum(
I.[HospitalID],
I.[Name],
I.[HospitalNumber],
I.[ServerName],
I.[IsAuthorized],
I.[IsAlertEnabled],
I.[AlertStartDate],
I.[AlertEndDate],
I.[IsTraining],
I.[TestMessageInterval],
I.[DelayAlertTime],
I.[IsDelayMessageAlert],
I.[IsTestMessageAlert],
I.[IsUnAuthorizedMessageAlert],
I.[IsWANDownAlert],
I.[IsWANUpAlert]) ;
Here is the checksum example which produces same results for two different input.
DECLARE @V1 VARCHAR(10)
DECLARE @V2 VARCHAR(10)
SELECT @V1 = NULL, @V2=NULL
SELECT binary_checksum('KKK','San Jose','1418','1418SVR ',0,1,@V1,@V2,0,30,180,1,0,1,1,1),
binary_checksum('KKK','San Jose','1418','1418SVR ',1,1,@V1,@V2,0,30,180,1,1,1,1,1)
Lookat the two binary_checksum above, they are different and should not match, but they both return same value.
Can someone please provide some info on these.
View 4 Replies
View Related
Nov 11, 2006
Hi Guys,
I'm importing logs from multiple servers from CSV format into a table. Inside the loop the 1st thing I do is insert the FileName into a table to ensure that I never process the same file twice.
The problem is that multiple servers produce the same filename, so if server A produces Log20061110.csv - when I import the log from server B ( also called Log20061110.csv ) into the table, it of course runs into a problem.
If there was some way for me to get an MD5 Hash of the file as a variable that would be solve my problem.
Does anyone know how I could accomplish that?
Thanks
Warrick
View 2 Replies
View Related
Mar 13, 2014
I would like to perform an audit of weak passwords, which is well documented for sql users. Using the same methodology, I should be able to audit weak passwords for contained users. To accomplish this, I must be able to find the location of the password hashes for the contained users.
I have looked at sys.syslogins and sys.sql_logins, which both have the password hash for server level users, but not contained users. I was able to find sys.sysusers, which does contain contained users, but no password hash.
What is the location of the password hashes for contained users?
View 0 Replies
View Related
