How Do I Define Memtoleave Parameter With -g Switch?

Jun 8, 2007

hi out there
On our Windows 2003 servers w. sp1 and running MS SQL Server 2000 w. sp4 we
see from time to time that we get this error "cannot allocate 64k
continous memory" or "SQL Server could not spawn process_loginread thread"
which could be caused by nothing left in the "Memtoleave" pool - I have now
search for advice on how to determine the values for the -g-switch - but
without much success - and if I just go for the "try&error" concept my
sql-server just allocates less and less ??? - ehh - in which units are the
parameters for the -g option specified - bytes, kilobytes, mbytes - 4k block
???? Any suggestions for measuring the actual running value of this pool -
memtoleave ??

best regards /ti

View 5 Replies


ADVERTISEMENT

Memtoleave And -g-switch

Jun 7, 2007

hi out there
On our Windows 2003 servers w. sp1 and running MS SQL Server 2000 w. sp4 we
see from time to time that we get this error "cannot allocate 64k
continous memory" or "SQL Server could not spawn process_loginread thread"
which could be caused by nothing left in the "Memtoleave" pool - I have now
search for advice on how to determine the values for the -g-switch - but
without much success - and if I just go for the "try&error" concept my
sql-server just allocates less and less ??? - ehh - in which units are the
parameters for the -g option specified - bytes, kilobytes, mbytes - 4k block
???? Any suggestions for measuring the actual running value of this pool -
memtoleave ??

best regards /ti

View 7 Replies View Related

How To Define A Search Parameter Value

Nov 8, 2013

I'm working on a SQL report and am returning a list of values for a column/field, "STATUS", to be used as one of the search parameter.

SELECT DISTINCT STATUS FROM [table]
ORDER BY STATUS

Values returned are [blank], approved, onhold, etc.How can I get the [blank] value to be set as "All" within the report parameter for STATUS?

View 3 Replies View Related

Define Default For Date Report Parameter / Analysis Services

May 30, 2007

I have a report which will one day display some data from an analysis services cube. my first step is to create a drop down parameter enabling the user to choose the date. I'd like to display only dates that have data, and I'd like it to default to today.



So I've created a dataset that will be the datasource for the dropdown displaying the available non-empty dates, which works fine.



SELECT measures.turnover ON COLUMNS,

nonempty([TBL DIM DATE].[DATE_ONLY].[DATE_ONLY].ALLMEMBERS ) ON ROWS

FROM [Itdev1 Hk]



I've also set the report parameter up to be a queried paramter,and to use the above dataset as it source, with [DATE_ONLY] displayed. and [DATE_ONLY] as the value.

Now, how do I get it to default to the last valid member in the list?

View 6 Replies View Related

/3GB Switch

Sep 2, 2007

Hi All
Server 2003 standard edition, MSsql 2005 standard edition
As I understand it server 2003 standard only supports 4gb. Does this mean I can only use up to 4gb as the maximum memory for server 2005?
If that is the case I would like to use the 3GB switch, and leave the other gig for other apps. If I set the 3GB switch in boot.ini do I then also have to enable AWE (which I can't seem to do) or can I forget about AWE altogether?

Many thanks
Martin

View 5 Replies View Related

3gb Switch And AWE

Apr 10, 2007

in configuring AWE whether i need use both 3GB in boot.ini and AWE Enabling option in SQL Server

View 3 Replies View Related

Adding 3 Gig Switch

Dec 12, 2002

Hi,

We have a standard Win 2K server and SQL2000 Enterprise edition on our DELL server.

Will it be possible to add a /3 Gig switch to boot.ini and make sql server to use 3 gig of RAM and the OS with 1 Gig of RAM.

This a live server website with heavy transactions more than 100 Transactions per minute.

We are planning to do a Transactional replication against 4 databases (publisher) with remote ditributor.

The reason why I am asking about this RAM increase is for this replication. Or is it Ok to have the 2 Gig RAM for this environment +replication.

Please give me a suggestion, how to proceed with this issue as i am concerned about memory especially for replication.

(4 Database sizes are 20 gig,7 gig,5 gig,5 gig)

Thanks,
Anu

View 10 Replies View Related

Date Switch

Nov 9, 2005

How do I replace month with day value and day value with month.
Example:
2005-02-09 00:00:00.000
replace with
2005-09-02 00:00:00.000

View 1 Replies View Related

Switch Case Is There

Oct 4, 2007

Dear all,
is there any switch case like we have in C language....
in sql server?

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 13 Replies View Related

Switch To Another User

Jul 20, 2005

Hi,Is it possible to switch to another user's credentials after logging in? Iseem to remember that there is a stored procedure for this, but I can't finddetails anywhere. I want to temporarily suspend my login's admin privilegesand become an "ordinary" user, for testing purposes.Thanks,Peter

View 1 Replies View Related

Context Switch

Sep 28, 2007


There are some concept about context switch block me .

---executor: dbo
create user u1 without login


go

create table t

(

col int

)

go


create proc proc1

with execute as 'u1'
as

insert into t values(1)


go

exec proc1

I think proc1 can't be run successfully beacuse the executor of proc1 is u1 and
I didn't grant any permission to user u1. So it will return a error message like The INSERT permission was denied on the object 't', database 'tempdb', schema 'dbo'

To my surprise, the "exec p1" command could run successfully..

Why?

View 12 Replies View Related

Table Switch Won't!

Aug 17, 2007

I am trying my first sliding partition scheme with 2005, and I am getting the following error...


ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table "ScanStoreAggregateLoad' allows values that are not allowed by check constraints or partition function on target table "ScanStoreAggregateTest'.


I have gone over the functions and constraints a gazillion times. The columns are set to Not Null. The constraints are set to AND NOT NULL. The constraint values align and align with the function. But still I get the failure. Any help would be greatly appreciated. Here are the definitions:


CREATE PARTITION FUNCTION pfn_ScanStoreAggregate(int)

AS

RANGE LEFT FOR VALUES

(19224,

19225,

19226,

19227)


CREATE TABLE dbo.ScanStoreAggregateTest(

ProductID int NOT NULL,

DateKey int NOT NULL CONSTRAINT ck_ScanStoreAggregateRange CHECK ((DateKey BETWEEN 19224 AND 19227) AND (DateKey IS NOT NULL)),

Span tinyint NOT NULL,

LocationID smallint NOT NULL,

WeeksTY tinyint NOT NULL,

WeeksLY tinyint NOT NULL,

UnitsTY numeric(12, 2) NOT NULL,

UnitsLY numeric(12, 2) NOT NULL,

SalesTY money NOT NULL,

SalesLY money NOT NULL,

CostTY money NOT NULL,

CostLY money NOT NULL,

ACVTY money NOT NULL,

ACVLY money NOT NULL,

SalesPerMMACVTY money NOT NULL,

SalesPerMMACVLY money NOT NULL,

) ON ps_ScanStoreAggregate (DateKey)


CREATE TABLE dbo.ScanStoreAggregateLoad(

ProductID int NOT NULL

,DateKey int NOT NULL

,Span tinyint NOT NULL

,LocationID smallint NOT NULL

,WeeksTY tinyint NOT NULL

,WeeksLY tinyint NOT NULL

,UnitsTY numeric(12, 2) NOT NULL

,UnitsLY numeric(12, 2) NOT NULL

,SalesTY money NOT NULL

,SalesLY money NOT NULL

,CostTY money NOT NULL

,CostLY money NOT NULL

,ACVTY money NOT NULL

,ACVLY money NOT NULL

,SalesPerMMACVTY money NOT NULL

,SalesPerMMACVLY money NOT NULL

) ON DataAggregates



ALTER TABLE dbo.ScanStoreAggregateLoad

WITH CHECK

ADD CONSTRAINT ck_ScanStoreAggregate19227_DateKey

CHECK ((DateKey = 19227)

AND (DateKey IS NOT NULL))

View 6 Replies View Related

Booi.ini Switch

Dec 10, 2006

Hello,



I read a lot of artical but still can't understand.

I have SQL 2005 ent edition 32BIT on Windows 2003 Ent R2 32 bit.

I have 4GB on my machine.

What switch I need ton the boot.ini

/3GB

/Userva=2900

/PAE (is this only if I have more than 4GB?)



Thanks

View 11 Replies View Related

Help With A Switch Statement.

Jun 18, 2007

Hey again,



So here's what I'm trying to do: I have three columns of data. Sometimes only one of the columns will contain a value while others may contain a null. If two or three contain a value it will be the same. So if I'm building a table in the layout designed and I want the value of the table to be the value stored in these columns. In pseudocode it looks like this:



Switch(column1 and column2 are null, value = column3, if column1 and column3 are null, value = column2, otherwise, value = column1)



Something like that where column1 is the default so if column 1 has a value then set the textbox value to it otherwise find a column that has it. I know that at least one column will definitely have a value. Anyone that can provide guidance on how to execute these I would appreciate it greatly.



Thank you!

View 10 Replies View Related

Case / Switch Function Help

Jun 9, 2004

Starting to play around with SQL server at work and this is my question:

In the query design mode in access I can make one of the fields an expression that is driven by a built-in switch function.
i.e. Switch([CategoryName] Like "Beverage","Drink",[CategoryName] Like "Cheese","Dairy")
This results in the additional column field I created to display "Drink" for each record that has the CategoryName = "Beverage", and "Dairy" for "Cheese".

Can I do something like this in SQL server in the view designer itself, or do I need to make a user defined function and call it?

Thanks in advance for any help.

View 3 Replies View Related

How I Use The N' Switch In Stored Procedure

Jun 26, 2004

Any One Tell me how i can use N'Switch in SQL Server Stored Procedure .
When I use Such a this syntax i am encountered and stoped with a error.
the is below(Stoerd Prdocedure Code)
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
CREATE proc sp_check_userpass(@username nvarchar(50),@password nvarchar(50),@first nvarchar(50),@last nvarchar(50),@email nvarchar(50),@stage int )as
declare @cnt int
begin
select @cnt=count(*) from member_info where (@username=username and @password=password)
if(@cnt=0)
begin
insert into atable(first,last,username,password,stage) values(@first,@last,@username,@password,@stage)
end
else
return @cnt
end
GO
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Other thing is that when exec this store proc it must be return a value return back to the @cnt but it is return only This( The command(s) Completed succefully).
Any One guide me how i can must be do .
i must clear it that i am using this query in ASP.NET SqlClient Object To Execute Query.
Thanx In Advance. ArQa

View 1 Replies View Related

.rdlc Table Use Of SUM And SWITCH Together?

Jan 19, 2007

Hello everyone, thanks in advance for reading. I'm new to reports and have tried searching for my answer with no luck. Any direction would be great.

Here is the issue, I have 3 colums in the dataset which display String data ("High", "Medium", or "Low")

I've added a column to the table on the report where I would like to convert the value of "High", "Medium" and "Low" to 3,2,1 respectively then SUM these numbers.

Is this possible? I've tried several variations of the code below--maybe it's not possible to do what I want? Thanks again for your time and help.



Fields!PP.Value is set to:
=Switch(Fields!Avail_Rank.Value = "High", 3, Fields!Avail_Rank.Value = "Medium", 2, Fields!Avail_Rank.Value = "Low", 1)

=Switch(Fields!Volume.Value = "High", 3, Fields!Volume.Value = "Medium", 2, Fields!Volume.Value = "Low", 1)

=Switch(Fields!Integ_Rank.Value = "High", 3, Fields!Integ_Rank.Value = "Medium", 2, Fields!Integ_Rank.Value = "Low", 1)

=Sum(Fields!Avail_Rank.Value + Fields!Volume.Value + Fields!Integ_Rank)

View 3 Replies View Related

SQL Edition And Licensing Switch

Aug 16, 2007

I have two SQL servers. One licensed for 2000 Standard Edition licensed under the processor model. The other is SQL 2000 Enterprise Edition licensed under the user cal model.



I want to switch these Editions and licensing models. My production server needs the Enterprise Edition and I want to put the processor licensing model with it. I want to put the Standard Edition on my reporting server with the user cal licensing model.



I have the instructions to upgraded Editions and it is straight forward. Can I use this procedure for both servers and not have issues?


Thanks

View 1 Replies View Related

How To Use Switch - Case Statement In T-SQL..?

Apr 5, 2007

Hi,

I want to use switch - case statement in T-SQL stored procedure.

Can any one help regarding the same..?



for e.g.

switch (exp)

{

case 1 : stmt 1; break;

case 2 : stmt 2; break;

case 3 : stmt 3; break;

& so on.......

}



View 8 Replies View Related

Define CTE Once And Use Every Where

Mar 31, 2008

I have to use a CTE based on conditions. But I am not able to write a common CTE and access it on different conditions.
Now I am duplicating the CTE definition inside each if condition. But I expects a solution like bellow to reduce code duplication.

For example

---- I need a common definition of CTE like this.
with CustomGroup( id)
as
(
Select id from Groups g
Inner join GroupStatus s on s.Id = g.Id
Where s.Status = €˜Active€™
)

IF @DisplyStatus=€?UserDetail€?
begin
<!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here.
select u.* from users u
inner join userGroups ug on ug.UserId = u.Id
inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE
end
else if @DisplyStatus=€?UserSimple€?
begin
<!--[if !supportLists]-->- <!--[endif]-->at present CTE is defined here.
select u.FirstName,u.LastName,u.DOB from users u
inner join userGroups ug on ug.UserId = u.Id
inner join CustomGroup cg on cg.Id =ug.GroupId -- Accessing the CTE
end
else if @DisplyStatus=€?UserWithAddress€?
begin

end

Please suggest possibilities, and your suggestions.

View 4 Replies View Related

Proc To Switch Two Values In Table

May 29, 2008

I need to switch two int values in my table, but I have no clue how to do it.  If I switch the 6 to a 5, then I can't distinguish between the 5 that's supposed to be a 5 and the 5 that needs to be a 6. Any help is greatly appreciated - thanks in advance! 

View 1 Replies View Related

Need A Way To Switch Specific Data From Columns

Jul 13, 2005

Basically I have 635k records in a table with a person's first name, and date of birth (other stuff but it's not relavent). I imported all the data from excel files, but somehow a bunch of records got the first name and date of birth mixed up, so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort. Now records are in fact repeated so another possible but more time taking solution is to write a stored procedure that I give the date of birth and it does the switching around for the respective date of birth when it's found inside the First name. Any suggestions? All the code I've written has proved useless :/

View 1 Replies View Related

Switch From 7.0 To 6.5 Using Tape Install Method

Mar 7, 2000

Is it possible to switch back to SQL 6.5 after upgrading to SQL 7.0 using the tape method? During the tape upgrade the 6.x devices are deleted. Is there no backout plan?

View 1 Replies View Related

SQL Cluster Failover/switch Notification

Nov 29, 2004

Hi all,

I need help to send notificaiton mails as soon as my active sql switch to passive one..

sp3a : 2000 Ent Ed.
windows 2003 Ent Ed.
Cluster: A/P


thanks in advance..

Regards
Deepak

View 1 Replies View Related

Cluster /AWE / 3GB Switch /Memory Usage

Jul 23, 2005

Dear all,i am planing to implement a Windows 2003 Cluster with MS SQl 2000Enterprise Edition. I have 2 Nodes (4 * XEON MP Processors) with 8 GBRAM per Node.I have the need for 6 SQL instances and would like to implement aactive-active cluster. What do you think it the best way to configurethe memory for the Servers ?I would like to run 3 instanced per Node but can i allocate 7 GB perNode for SQL (and 1 for the Operating System) ?What is happening then i one server fails ?Should i plan to allocate only 3 GB per Server for SQL to make surethat one server can handle the load for all instances if one serverfails ?Should i use the /AWE switch only in the boot.ini to allow more than 4GB Memory ?or should i use the /3 GB switch as well ?Maybe somebody can give me a hint.Best regards,Walter

View 1 Replies View Related

[TRANSACT]Can't Switch From Master To Another Database

Jul 23, 2005

Hello,I encounter a problem with a small portion of sqlcode. I try to go ondatabase using "use dbname" but i always stay in master. I executescript with the sa user.declare @dbnamesysnamedeclare @ret_codeintDECLARE db_cursor CURSOR FORselectnamefrommaster..sysdatabaseswherename not in ('master', 'model', 'tempdb', 'pubs', 'Northwind')-- Open cursorOPEN db_cursorFETCH NEXT FROM db_cursor INTO @dbnameWHILE @@FETCH_STATUS = 0BEGINexecute ('use ' + @dbname)execute ('select db_name()')Thank's for help,Pierrot.

View 2 Replies View Related

Using The Global Variable Switch (/A) With Dtsrun

Jul 20, 2005

We are running a DTS package with the dtsrun utility and would like topass a variable through it. Inside our package we have a VB scriptthat references a table that contains the information about all of ourjobs and we would like to pass a variable to tell it which jobs torun. The reason we cannot just add the variable to the VB script,i.e. in a where clause, is because we would like more then 1 bat fileto run this package. We want some jobs to run with 1 bat file andother jobs to run with another. If we could pass through a variableof 1 that would refer to a column within our table then only thosejobs would run at that time. Make sense?Any help is appreciatedThanks

View 1 Replies View Related

Switch Of Query Design Mode

Feb 13, 2007

Hi all,

I have a couple of reports with predefined queries. When I try to edit a data set query it goes directly to query design mode losing the query I already had. As you can see this is a real problem because I can't edit queries. How can I switch off design mode? any ideas are pretty much appreciated.

Cheers

View 3 Replies View Related

Get Original Login Name After Context Switch

Jan 22, 2008

Does anyone know how to get the original Login name, while a context switch is in effect.. For example during the use of Original login to SQL is Wayne
Then Execute as Test

Now I need to know that Wayne is the Login and not Test

none of the standard things work, they all use the current context (suser_sname, etc)... Books online includes the following text

SYSTEM_USER returns the name of the currently executing context. If the EXECUTE AS statement has been used to switch context, SYSTEM_USER will return the name of the impersonated context. Use the LOGIN() function to return the name of the original login during impersonation.


But I cannot find any function called LOGIN() anywhere?

Suggestions>?

View 4 Replies View Related

How To Switch Debugging Mode In SSIS?

Nov 29, 2007


I added an activeX package and then try to execute it, when I run it comes up with following error:-

Error 1 Validation error. DTSTask_DTSDataPumpTask_1: OLE DB Destination [181]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The Acquire Connection method call to the connection manager "Microsoft OLE DB Provider for SQL Server Copy" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the Acquire Connection method call failed. SearsCDCC_Transfer.dtsx 0 0


I did some research on line and I came to know that I need to switch the debugging mode to change the 64 bit to false. But I am too novice in SSIS, so I am not sure how to switch the debugging mode. Any idea about it?

View 1 Replies View Related

Still Truncating Even With Switch To VarChar(7000)

Jan 22, 2007

I have switched my datatype to varChar(7000). I set to "only" 7000 to allow for the addition of other fields and still stay under a 8000byte record limit. My data is still being truncated sometimes. I''m not even able to get up near 7000 characters on the big text field.

View 2 Replies View Related

Partition Switch And Non Unique Index

Aug 9, 2006

I have a table partitioned by month. There are no primary key. There is another table with the same structure that I use to load data for the current month. I can perform partition switch with no problem.

I created non unique index on integer field in both tables. After this change partition switch does not work anymore. Here is an error that I am getting:

'ALTER TABLE SWITCH' statement failed. The table 'dbo.test' is
partitioned while index 'IX_test' is not partitioned."

Any help?

View 18 Replies View Related

How To Switch Off Transaction Logging For Inserts/updates?

Nov 22, 1999

Is there a way to switch off transaction logging for insert and update statements and not only for select into bulk copy?

View 1 Replies View Related







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