Need help creating a temporary table in MS SQL Server.

Hello,

I am working on a webapp using VB.net

Right now I am writing to a sql table during a process where the end userstarts entering the contents for a file that is going to be generated once hefinishes entering the data, but the problem is that if more than one user isdoing the same process the data would get mixed up. To avoid this Ithought in creating a temporary table (its name will consist of a stringand the current date time).

I would like to see any tutorialabout creating and working with temp tables. Or if you have anysuggestions, I will appreciate them. Thanks

There are two types of temp tables in SQL Server # local temp table scope very limted but also the most used because it uses less resources and SQL Server may clean up if you forget it, the other ## global temp table longer scope but you must drop it explicitly or it can harm your application. Try the link below for some tricks of how to use the temp tables, I am assuming you know they are created in the Temp Db. Hope this helps.

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1

Need Help creating a SSIS Package (Newbie)

Following is what I would like to do, so I can keep updating my central SQL Server database with latest updates from the field. I like to use SSIS 2005 to create a package that could do this. Any help to get me started would be appreciated. I need some help soon, pls give me something to get started. APpreciate it. Thanks.

Open connection and read client location table on the local SQL Server database called PODO
For each location id in the table do the following:
Store locationid/clientid in a variable called CLLOC_ID
Construct file name with mdb extension and store in a variable MDB_FILE
Establish connection to the data import folder
Search for that MDB_FILE in the folder on the file system
If there is a file where match = true then do this:
1) Open the access database
2) Read and import the data from the customer experience table
3) Write that data to the SQL Server tables where location = CLLOC_ID
4) Exit process
IF there is no match, exit process
Keep looping until all the client ids/loc ids are read from the SQL Server client location table.

MA

SSIS won't "search" for files as such. You have to tell it where the files are. If you have a collection of .mdb files in a folder then you could loop over them using the ForEach loop.

Import data from Access can be done in a data-flow

There are a number of ways of discovering if the incoming data has a matchinng PK in the destination (which I think is what you are trying to do). Have a look at the LOOKUP component.

-Jamie

|||

Jamie:

Thanks for the advice. I would like to loop thru a SQL Server table and build file names for the mdb files , and then use those file names to match with what i have in the folder. For each file i construct, i would like to establish a connection to that file, and import data. How would i implement this functionality?

MA

|||

Here's how: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

-Jamie

|||

Hi Jamie:

I went ahead and added the ForEach Loop container, and would like to see if that is the right starting point for what I am trying to achieve. Can you give me like a step by step, on how to do this, since I am having a real tough time figuring out how to do things like build a filename from the data retrieved from the SQL execute task. Also then how do i pass that file name further down the process, and how do i create something that would take that built filename and compare it with the file names found in the actual folder that i am connecting to on my local hard drive. I have setup two connections using the connection manager, one is for the SQL Server, while the other one is right now pointing to one dummy access file in the folder. I would need to somehow make the connection manager dynamically look for filenames, and once a match is found, import data. The import data part is pretty straight forward, but i havent yet gotten there. The bigger issue is that i need to build a file name list on the fly by reading thru a sql server table, and then comparing the filenames with what i have in the folder on the local drive. If a match exisits, then i would like to continue with a data import process. Since I am extremely new to SSIS, and I am not a DBA/Database developer, i am more of an application developer on the front end, can you please give me a quick step by step directions. This will help me get started, and if i can figure out things like how to pass output from one sql statement to another task, and how to do comparisons within SSIS, and then how to dynamically read thru files with those variables, this will help in the future, as I will be building more of these type or ETL processes with much more advanced setup. I appreciate your time, and so far I really appreciate your help. Thanks and I look forward to your reply, and I know I am asking too much quesitons, but this is a real project, and I am up against a deadline. Thank you.

MA

sql

Need help converting date format.

Hi and thanx for reading my post..

I have a reg_date field in my MSSQL DB which is formatted like this :

dd.mm.yy tt:mm:ss (eks. 24.12.03 18:00:03)

What i want to do is get the 8 first chars from this string so i end up with only : 24.12.03

Have tried different variations of : convert(char(8) but not sure how i do this really..

Have already searched the net for a solution but had to post it since i didn't find anything useful..

Hope someone can help me out

Best regards
Mirador-/select ((CONVERT(CHAR(6),getdate(),104)) + RIGHT(CONVERT(CHAR(10),getdate(),104),2))|||select (CONVERT(CHAR(10),getdate(),4))

using 104 adds the century to the year, which then has to be eliminated in the first response. Just using 4 returns only the last two digits of the year. Be carefule though, as this type of date format will be a problem in your code in 96 years ;-)|||Sorry, make that CHAR(8).|||Hi and thanx for the quick reply !

Have tried the code but still got trouble with this errormsg i get :

--> Incorrect syntax near '.03.'

This is the code :

SELECT bla1, bla2, bla3, bla4 FROM TABLE WHERE blabla = 1 and blabla = 0 and CONVERT(char(8), reg_date, 4) LIKE " & strTheDate.

ps!.. (strTheDate is asp variable..)

I have debugged the strTheDate and i know it's the correct format : 24.12.03 ex.

ps!.. the field is a DateTime format, just to ensure that :)

Best reagards
Mirador|||Can;t say I think this is the best way of doing this, but I haven't had enough coffee to do a re-write (and blindman will undercut me anyway).

Try this (note the single quotes!):

SELECT bla1, bla2, bla3, bla4 FROM TABLE WHERE blabla = 1 and blabla = 0 and CONVERT(char(8), reg_date, 4) = CONVERT(char(8), CAST('" & strTheDate & "' AS DATETIME),4)

This will convert strTheDate to the same format.

Need help converting a select query into a case statement

I have the following query:

(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')

I need to convert this into a case statement.
I tried various ways but did not get the result that I was after

Thanks,
LauraConvert a SELECT statement into a CASE statement?

I haven't a clue what you are intending to do.|||This is the query that I am dealing with:

SELECT PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice AS InvoiceNumber,
CASE WHEN (AR.InvoiceDate = '')
THEN '0'
ELSE DATEDIFF(day, AR.InvoiceDate, ********* need to insert case statement to find the end of the period ************** ) END AS DaysOld,
SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount WHEN LedgerAR.TransType = 'CR' AND
LedgerAR.SubType = 'T' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) AS InvoiceBalance,
SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'I' THEN - 1 * LedgerAR.Amount ELSE 0 END) AS Interest,
SUM(CASE WHEN LedgerAR.TransType = 'IN' AND LedgerAR.SubType = 'R' THEN LedgerAR.Amount ELSE 0 END) AS Retainage,
SUM(CASE WHEN (LedgerAR.WBS2 <> '9001' AND AR.Period = '200408') THEN - 1 * LedgerAR.Amount ELSE 0 END) AS total
FROM PR LEFT OUTER JOIN
AR ON AR.WBS1 = PR.WBS1 AND PR.WBS2 = '' AND PR.WBS3 = '' LEFT JOIN
LedgerAR ON AR.WBS1 = LedgerAR.WBS1 AND AR.WBS2 = LedgerAR.WBS2 AND AR.WBS3 = LedgerAR.WBS3 AND
AR.Invoice = LedgerAR.Invoice LEFT JOIN
LD ON PR.WBS1 = LD.WBS1 AND PR.WBS2 = LD.WBS2 AND PR.WBS3 = LD.WBS3 LEFT JOIN
CFGDates ON CFGDates.Period = AR.Period
WHERE (LedgerAR.TransType = 'IN') AND (LedgerAR.SubType <> ' X ' OR
LedgerAR.SubType IS NULL) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND (LedgerAR.Amount <> 0) AND
(AR.PaidPeriod > 200408) AND (PR.WBS1 = '001-298') OR
(LedgerAR.TransType = ' CR ') AND (LedgerAR.SubType IN (' R ', ' T ')) AND (LedgerAR.AutoEntry <> ' Y ') AND (LedgerAR.Period <= 200408) AND
(LedgerAR.Amount <> 0) AND (PR.WBS1 = '001 - 298 ')
GROUP BY PR.WBS1, PR.Name, AR.InvoiceDate, AR.Invoice, Ar.Period, CFGDates.AccountPdEnd
HAVING SUM(CASE WHEN LedgerAR.TransType = 'IN' THEN - 1 * LedgerAR.Amount ELSE LedgerAR.Amount END) <> 0

Within the DateDiff function I need to find the end of the period using a case statment such as

Case When AR.Period='200408' Then Min(CFGDates.AccountPdEnd) Else '0' End As EndPeriod

The problem is that I am having a hard time integrating this into the top query. By using the case statement that I wrote above, I get an error stating that there is a problem near 'As' . I am wondering if there is a way to perform a case statement within a case statement.

Hope that clears things up.

Thanks,
Laura|||that's right, you cannot assign an alias to a value if that value is inside a function

just drop the As EndPeriod

oh, and note that you are looking for the difference in days between AR.InvoiceDate and the value of this case expression, so make sure not to use quotes around the zero if indeed you want december 31, 1899, which seems rather unlikely, but that is, i think, the date that corresponds to zero|||Here is a CASE statement that incorporates your logic:

CASE WHEN (AR.InvoiceDate = '') THEN '0'
WHEN AR.Period='200408' THEN DATEDIFF(day, AR.InvoiceDate, Min(CFGDates.AccountPdEnd))
ELSE DATEDIFF(day, AR.InvoiceDate, 0)
END AS DaysOld

...but whether it will integrate with the rest of your SQL statement, I do not know.|||blindman, that's the same as the nested version (you are allowed to nest CASEs, you know)

that date equal to an empty string gives me the creeps

and the quotes around the zero don't make me feel too hot either

also, i just noticed, CFGDates.AccountPdEnd is in the GROUP BY, so why even bother finding the MIN for it in the DATEDIFF, eh|||Oh, I certainly didn't mean to imply that was ALL that was wrong with her code.

What gives me the heebie-jeebies about it is all the hard-coded values.

Need help converting a CHAR into a Datetime

I have a Database which is having a Counterdate time Column stored in the form of Char(24)

But i need it to be in form of datetime so that i can use the datetime functions on it..When i use the cast or convert inside the function where i am passing this character it gives me error

"Conversion failed when converting datetime from character string."

I am done all permutatiions and combinations for this used

Set @.DE = convert ( datetime, @.ts,121)

Set @.de = cast( @.ts as datetime)

BUT ALWZ give me same error.... also when i copy the whole of the data table into some other database the error doesnt come.. i converts the character into the datetime..

I DONT understand why the Server is behaving wiered..

Hoping to get an answer soon.

With regards

Sharad

Database Developer ,

UIC

Hi Sharad,

Can you post a sample of the (character) data that is causing the issue?

Thanks,

Rob

|||

Sir,

I am just using the performance Logs of the a server and in that the database get automatically generated and hence..

I get the data of datetime column in Char(24) form.. and when i use an function to convert that into a datetime form it gives me error..

take anytime .e.g of the form

"2006-11-15 17:33:22.015"

if this is the time stamp and stored in the char(24) format and I want it to be datetime ...

What any sensible person would do

1. cast it into datetime

2. Convert into datetime..

Both not working ..

With regards

Sharad

|||

PLZ reply if u could Help with this simple STUFF

This CODE I RUN

DECLARE @.DS char(24)

SEt @.ds = '2006-10-27 20:01:13.297'

print @.ds

declare @.de datetime

Set @.de = convert(datetime, @.ds, 121)

print @.de

Output is

:::

2006-10-27 20:01:13.297

Oct 27 2006 8:01PM

But i want output to be exactly same

as 2006-10-27 20:01:13.297

Plz help me with this..

|||

change your statement as below,

DECLARE @.DS char(24)

SEt @.ds = '2006-10-27 20:01:13.297'

print @.ds

declare @.de datetime

Set @.de = convert(datetime, @.ds, 121)

print convert(varchar, @.ds, 121)

Need help constructing stored procedure

Hi,

I need to construct a stored procedure that will accept a set of comma seperated numbers. What I would like to do is something like this

create procedure shopping_cart(@.contents as varchar) as
select dvd_title
from movie_dvd
where dvd_detail_id in (@.contents)

dvd_detail_id is defined as int in the table.
The problem is if I declare @.contents as varchar, the procedure only recognizes the first number and ignores the others. Does anyone know how to get around this?

Thanks

Dan@.array is your @.contents...you need to pass the separator too..it can be a ',' or '-' etc

Create procedure shopping_cart (@.Array varchar(1000), @.separator char(1) ) AS
set nocount on
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
declare @.separator_position int -- This is used to locate each separator character
declare @.array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
while patindex('%' + @.separator + '%' , @.array) <> 0
begin
 -- patindex matches the a pattern against a string
 select @.separator_position = patindex('%' + @.separator + '%' , @.array)
 select @.array_value = left(@.array, @.separator_position - 1)
 -- This is where you process the values passed.
 -- Replace this select statement with your processing
 -- @.array_value holds the value of this element of the array
 select Array_Value = @.array_value
 -- This replaces what we just processed with and empty string
 select @.array = stuff(@.array, 1, @.separator_position, '')
end
set nocount off
GO

hth|||I did the following on some other project

create procedure shopping_cart(@.contents as varchar) as
select dvd_title
from movie_dvd
where CharIndex(',' + Cast(dvd_detail_id as varchar(3)) + ',', @.contents) > 0

In the above case you should be sure that the values passed in are going to have , next to it. For eample, it should be ,1,2,3,

Does the above make sense?

AP

Need help constructing Full Names

Hi,
I have three fields that make up employees' names in the database i.e.
FirstName, MiddleInitial and LastName fields. FirstName and LastName fields
are varchar(30) and varchar(50) respectively. MiddleInitial field is char(1)
.
When I use the FirstName + ' ' + MiddleInitial + ' ' + LastName AS
EmployeeName construct, I get no data because most records have a null value
for MiddleInitial field.
If I ask for FirstName + ' ' + LastName AS EmployeeName, I get results as
desired.
How can I take data from all three fields and return EmployeeName as single
field in a stored procedure? Even if I make it work, I'll have two spaces
between FirstName and LastName if the employee has no MiddleInitial due to
null value in that field. I'm sure there's a civilized way to do this. I'd
appreciate some pointers here.
Thanks,
SamYou need to use COALESCE, ISNULL or CASE in your SELECT query:
CREATE TABLE Names
(
FirstName nvarchar(20) NOT NULL
,MiddleInitial nchar(1) NULL
,LastName nvarchar(30) NOT NULL
)
INSERT INTO Names (FirstName, MiddleInitial, LastName)
VALUES ('John', NULL , 'Doe')
INSERT INTO Names (FirstName, MiddleInitial, LastName)
VALUES ('Alfred', 'E', 'Neuman')
SELECT
FirstName + ' ' + COALESCE(MiddleInitial + ' ', '') + LastName
AS EmployeeName
FROM Names
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:2F5162E6-A0C5-47F9-8252-A5EA8243C76F@.microsoft.com...
> Hi,
> I have three fields that make up employees' names in the database i.e.
> FirstName, MiddleInitial and LastName fields. FirstName and LastName field
s
> are varchar(30) and varchar(50) respectively. MiddleInitial field is char(
1).
> When I use the FirstName + ' ' + MiddleInitial + ' ' + LastName AS
> EmployeeName construct, I get no data because most records have a null val
ue
> for MiddleInitial field.
> If I ask for FirstName + ' ' + LastName AS EmployeeName, I get results as
> desired.
> How can I take data from all three fields and return EmployeeName as singl
e
> field in a stored procedure? Even if I make it work, I'll have two spaces
> between FirstName and LastName if the employee has no MiddleInitial due to
> null value in that field. I'm sure there's a civilized way to do this. I'd
> appreciate some pointers here.
> --
> Thanks,
> Sam|||Great... Thank you very much Tibor.
--
Thanks,
Sam
"Tibor Karaszi" wrote:

> You need to use COALESCE, ISNULL or CASE in your SELECT query:
> CREATE TABLE Names
> (
> FirstName nvarchar(20) NOT NULL
> ,MiddleInitial nchar(1) NULL
> ,LastName nvarchar(30) NOT NULL
> )
> INSERT INTO Names (FirstName, MiddleInitial, LastName)
> VALUES ('John', NULL , 'Doe')
> INSERT INTO Names (FirstName, MiddleInitial, LastName)
> VALUES ('Alfred', 'E', 'Neuman')
> SELECT
> FirstName + ' ' + COALESCE(MiddleInitial + ' ', '') + LastName
> AS EmployeeName
> FROM Names
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:2F5162E6-A0C5-47F9-8252-A5EA8243C76F@.microsoft.com...
>|||Also you might want to have a look at concat_null_yields_null parameter
in BOLsql

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakeHi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
--
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegroups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann Blake
Hi
I 'm not sure understood the question
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
How it can be done if the Category 3 does not exist? Can you show a
desired output ?
CREATE TABLE #Test
(
[id] INT NOT NULL PRIMARY KEY,
TextID INT NOT NULL,
LanguageID CHAR(2) NOT NULL,
CategoryID INT NOT NULL,
[Text] VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES (1,1,'en',1,'apple')
INSERT INTO #Test VALUES (2,1,'de',1,'Apfel')
INSERT INTO #Test VALUES (3,2,'fr',1,'Lundi')
INSERT INTO #Test VALUES (4,2,'de',1,'Montag')
INSERT INTO #Test VALUES (5,3,'en',2,'Food')
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>
|||Your question doesn't make much sense.
"I want to select ALL the records where the LanguageID is " but then later
you say "If more than one exists, I want to select ONLY
one of these LanguageIDs ". Which is it... ALL or ONLY?
Also you don't have any rows with categoryID of 3 so there's no data even to
select.
Nik Marshall-Blank MCSD/MCDBA
<johannblake@.yahoo.com> wrote in message
news:1127802103.883080.183460@.g47g2000cwa.googlegr oups.com...
>I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>

Need help constructing a SELECT statement

I am having difficulty coming up with the correct Select statement. I
have a table with 5 columns as follows:
ID int (primary key)
TextID int
LanguageID int
CategoryID int
Text nvarchar(260)
The table is filled with the following 5 rows of data:
ID TextID LanguageID CategoryID Text
1, 1, 'en', 1, 'apple'
2, 1, 'de', 1, 'Apfel'
3, 2, 'fr', 1, 'Lundi'
4, 2, 'de', 1, 'Montag'
5, 3, 'en', 2, 'Food'
I want to select all the records where the LanguageID is 'en' and the
CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
is found for a unique TextID, I want to select whatever LanguageID does
exist for the TextID. If more than one exists, I want to select only
one of these LanguageIDs (it doesn't matter which one). Using the above
data for an example, only the first and third records should be
returned (or possibly the 4th instead of the 3rd).
Any help would be appreciated.
Thanks
Johann BlakePlease post proper DDL with keys and constraints so that we don't have to
guess. I'm guessing that (textid, languageid) is unique just as I'm guessing
that ID is an IDENTITY value and is therefore probably not useful in the
solution.
SELECT id, textid, languageid, categoryid, text
FROM YourTable AS T
WHERE languageid =
(SELECT
COALESCE(MIN(CASE WHEN languageid = 'EN' THEN 'EN' END),
MIN(languageid))
FROM YourTable
WHERE textid = T.textid) ;
(untested)
David Portas
SQL Server MVP
--|||Hi Johann
Your post is inconsistent check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data as insert statements. It is also useful to give expected output.
Ignoring the categoryid as there is no value of 3 then you may want somethin
g
similar to:
CREATE TABLE #MyTable ( ID int NOT NULL primary key,
TextID int,
LanguageID char(2),
CategoryID int,
Text nvarchar(260) )
INSERT INTO #Mytable (ID, TextID, LanguageID, CategoryID, [Text] )
SELECT 1 , 1 , 'en' , 1 , 'apple'
UNION ALL SELECT 2, 1, 'de', 1,
'Apfel'
UNION ALL SELECT 3, 2, 'fr', 1,
'Lundi'
UNION ALL SELECT 4, 2, 'de', 1,
'Montag'
UNION ALL SELECT 5, 3, 'en', 2,
'Food'
SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #Mytable M
WHERE LanguageID = 'en'
UNION ALL SELECT M.Id, M.TextId, M.LanguageId, M.categoryId, M.[Text]
FROM #MyTable M
WHERE NOT EXISTS ( SELECT * FROM #MyTable D WHERE D.TextId = M.TextId AND
D.LanguageID = 'en' )
AND id = ( SELECT MIN(id) FROM #MyTable E WHERE E.TextId = M.TextId )
John
"Johann Blake" wrote:

> I am having difficulty coming up with the correct Select statement. I
> have a table with 5 columns as follows:
> ID int (primary key)
> TextID int
> LanguageID int
> CategoryID int
> Text nvarchar(260)
> The table is filled with the following 5 rows of data:
> ID TextID LanguageID CategoryID Text
> 1, 1, 'en', 1, 'apple'
> 2, 1, 'de', 1, 'Apfel'
> 3, 2, 'fr', 1, 'Lundi'
> 4, 2, 'de', 1, 'Montag'
> 5, 3, 'en', 2, 'Food'
> I want to select all the records where the LanguageID is 'en' and the
> CategoryID is 3 for each unique TextID value. If no LanguageID for 'en'
> is found for a unique TextID, I want to select whatever LanguageID does
> exist for the TextID. If more than one exists, I want to select only
> one of these LanguageIDs (it doesn't matter which one). Using the above
> data for an example, only the first and third records should be
> returned (or possibly the 4th instead of the 3rd).
> Any help would be appreciated.
> Thanks
> Johann Blake
>|||I believe by showing the example data that it is clear what is unique
and what isn't. Accordingly, the TextID and LanguageID fields are NOT
unique. Thanks for your example. I needed to add the AND section to
limit the records to the CategoryID. But it works! I'll need to read up
on the COALESCE since I've never come across that before.
Thanks again
Johann Blake|||> I believe by showing the example data that it is clear what is unique
> and what isn't. Accordingly, the TextID and LanguageID fields are NOT
> unique.
Non sequitur because in your example data that compound key IS unique.
Glad if I helped anyway.
David Portas
SQL Server MVP
--

need help connecting to sql sever

Hi

I am able to connect to an sql server from my server explorer. Can't seem to connect
to an sql server from my code.

this is what i am doing

String strConnection = "server=FRANK;database=Northwind;integrated security=true;";
SqlConnection objConnection = new SqlConnection(strConnection);
objConnection.Open
objConnection.Close();

error Login Failure for user FRANK

I know that my server name is FRANK because thats what it says on my Sql Service manager.

And i use that same server name when i connect from my Server Explorer(using Visual Studio.NET) and then i get a listing of the the Databases that are associated with my SQL Server.

I have no problems coonecting to an Access dataBase using OLedbConnectionI'm not sure if "server" is the same as "Data Source", but try using "Data Source" instead:

String strConnection = "Data Source=FRANK;database=Northwind;integrated security=true";sql

Need help connecting to server with DSN

Hi all,

I am used to connecting to my database using a querystring and a connection like this:

string connectionString = "server=\'myserver\'; user id=\'myuser\'; password=\'mypw;database=\'mydb\'";
System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);

I write in C# and don't know how to connect to a database using a DSN. I have this VB code that's supposed to connect with DSN:

<%
set conn = Server.CreateObject("ADODB.Connection")
conn.open"DSN=MyDSN;UID=user;PWD=password;DATABASE=databasename"
%>

But I need it in C#...

I tried using a query string like this one

string connectionString="DSN=mydsn;UID=myuser;PWD=mypw;DATABASE=mydb";

and got this error :Unknown connection option in connection string: dsn. :

Any help would be greatly appreciated... thanksPlease check the following :

 OdbcConnection cn;
 OdbcCommand cmd;
 string MyString;
 MyString="Select * from test";
 cn= new OdbcConnection("dsn=myDSN;UID=myUid;PWD=myPwd;");
 cmd=new OdbcCommand(MyString,cn);
 cn.Open();
 ......

|||that's classic ASP code in VBScript - not ASP.NET code in VB.NET

Anyway, is there a particular reason you NEED to use a DSN? it IS possible, but it's not the best option.

Need help connecting to a SQL 2005 server

Hoi,

Can someone help me? I have made a website with a database. Local on my pc all work fine. but now i need to set it op on my webhost.

all pages with no database access work but the ones with database controls i get:

Server Error in '/' Application.

---

Login failed for user 'username'..

I got from my webhost:

db server:

db name:

db user:

db ww:

How do i setup the connectionstring to workt with this?

I'm totaly new at this so all help is welcome.

Gr.

W.

You can have a look atconnectionstrings.com for an example on how to set up your connection string. Also read your web hosts documentation as they will probably tell you how to do this.

Need help connecting java to mssql, I'm even lost at install

Hello,

Hope anyone in here can help.
I have tried to install the SQL Server 5 Express, but can't seem to get it to work properly.
I got a terrible name set as default for my database, as well as a terrible name for my username.
I can't find a place to change this to something more manageable.

It is further set to windows auth, but trying to set it to both, and trying a username (and password) always gives me a log in error.

I've tried searching all over the net and in here for a simple tutorial, but can't seem to find one that helps me more than it confuses me.

*****
The second part of the problem is that I have a java program which I want to connect to the database and use the tables in it, but I am at loss to how I can find what username (and password) I am supposed to have, what "url" it needs etc.
I have the jdbc driver so that part should work, I can successfully log to other mssql databases (at work/school), but not my own.

Hope you can help, and thanks in advance.

Hi,

to change a database name

alter database testdb

modify name = TestTmpDB

go

and to chagne user name

alter user AbolrousHazem

with Name = TempTestUser

go

while connecting to SQL Server from Java which error you get? you have wrote that you have set it with Windows Authentication, have you cross checked with Connectilon String?

HTH

Hemantgiri S. Goswami

|||

Hi Murky,

I would encourage you to use the SQL Server 2005 JDBC Driver. You can download it from here: http://msdn2.microsoft.com/en-us/data/aa937724.aspx. To connect to SQL Server using the driver, please refer to the section of that page regarding building the Connection String http://msdn2.microsoft.com/en-us/library/ms378428.aspx. Note, connection string and url are synonyms. There are also many sample java applications available at the first link too.

Note that you can connection to SQL Server using two methods:

1. Username and Password Credentials

2. Windows Authentication.

To do a windows authenticated connection, the server should have been setup with "mixed-mode authentication". If you installed with all the default options, this feature was not installed then. If it was not then you need to use your username/password credentials or reconfigure the server to use mixed-mode authentication.

Hope this helps.

Regards,

Jaaved Mohammed

Need Help Configuring SQL Server Express

I have Visual Studio 2005 Professional installed and SQL Server Express installed. In following along with the Microsoft Visual C# Step by Step book, there is a procedure on page xxi to get access to SQL Server Express. At the 2> prompt, however, when I type in go and then press enter, I get the following message:

Msg 15247 Level 16, State 1, Server MyServerName\SQLEXPRESS, Procedure sp_grantlogin, Line 13

User does not have permission to perform this action.

The computer is my own personal computer, with Window Vista installed and no previous versions of MSDE installed. I also have administrator persmissions. The only guidelines in the procedure regarding what to do in the event of an error message is to ensure that the commands were typed in correctly. What could be causing this message, since I do have administrator permissions?

hi,

as you are dealing with Vista and UAC related issues, please verify your actual login has the minimal required permissions to execute sp_grantlogin system stored procedure, ALTER ANY LOGIN or higher...

regards

|||

Hi. When I go to Start->Control Panel->User Accounts, and then click on the Change Your Account Type link, the window that appears shows my present status, which is Administrator. A message in the window states:

"Administrators have complete access to the computer and can make any desired changes."

Following that sentence is a sentence which has advice about creating a password. I then cancel the task, since I really didn't want to change my status. Was that sufficient to verify that I've got the minimal required permissions to execute sp_grantlogin?

I'm not sure what UAC means (User Account Control?). But I do have a problem with Windows Defender not working...giving me a message that it cannot update the definitions. In following the help threads on Windows Defender problems, I discovered that there is no wuaucpl.cpl file in my windows directory. I've opened up an email help procedure with Microsoft on that.

Edit: I realize now that wuaucpl.cpl is no longer a file included in Windows Vista (though my SQL Server problem is probably not related to my problem with Windows Defender).

|||

hi,

this is correct at the OS level, but please verify "inside" SQLExpress that your login has appropriate permissions as well..

regards

|||Hi. Unfortunately, I am a complete neophyte when it comes to SQL Server. How do I verify that my login has the appropriate permissions? I've never actually started SQLExpress or done anything with it. I know that I have a SQL Sever configuration manager tool, and when I open it and look at the SQL Server 2005 Services folder, I can see that SQLExpress is running.|||

Vista has changed the playing field and now forces everyone to run as a non-administrative user, even if you're an administrator, until you explicity request to be elevated to administrative privleges. I've explained a bit about how this works and how to correctly configure SQL Express on Vista in this blog post. You already have SQL installed, so make sure you're using SP2 and then follow the instruction for running the Provisioning tool to correctly set your access level.

Mike

|||Mike, thanks for that info. I do have SP2 installed. I just want to clear up one more thing. When I open up the SAC tool, and click on Add New Administrator, the SQL Server 2005 User Provisioning Tool for Vista window opens. In it there is a text box that has MyServerName\UserName as the user to provision. In the available privileges in the left pane, there is SQLEXPRESS with one item named: Member of SQL Server SysAdmin role on SQLEXPRESS. This is the only availbable privilege showing. So I should transfer this privilege, and I should then be able to grant myself the sp_grantlogin permission? Just want to make sure about this before I do anything.|||

All you need to do is give yourself SysAdmin privledges and you're done, no need to use sp_grantlogin. Once you're a SysAdmin you have every permission that it is possible to have.

Mike

|||Thanks for all the help, Mike.

Need help configuring Report Server permissions

I can't seem to hit on the right combination of roles/persimssions to
set against the Report Server in order to allow my localhost
implementation to access/browse/upload reports. Is there some deFacto
setting(s) that I should be using?
After installation I was able to upload and browse. The trouble
started when I tried to instantiate a ReportingService object (I
wanted to be able to programatically browse the available reports -
previously uploaded). When I tried this I got an insufficient
privilege error for my generic internet user account. After having
tried a number of combinations I'm really stuck.
Any help appreciated! (how-to links, diagrams, recipes,...)
GlennGlenn,
The right question in this case is "Under what identity am I trying to call
the RS Web service?". For example, if it is from a WinForm app, you need to
set proxy credentials to DefaultCredentials, as follows:
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Please review the "Using the SOAP API in a Windows Application" and "Using
the SOAP API in a Web Application" for more info.
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
"Glenn Owens" <gowens@.nixonpeabody.com> wrote in message
news:bc4291aa.0408121414.5d18bff0@.posting.google.com...
> I can't seem to hit on the right combination of roles/persimssions to
> set against the Report Server in order to allow my localhost
> implementation to access/browse/upload reports. Is there some deFacto
> setting(s) that I should be using?
> After installation I was able to upload and browse. The trouble
> started when I tried to instantiate a ReportingService object (I
> wanted to be able to programatically browse the available reports -
> previously uploaded). When I tried this I got an insufficient
> privilege error for my generic internet user account. After having
> tried a number of combinations I'm really stuck.
> Any help appreciated! (how-to links, diagrams, recipes,...)
> Glenn|||Teo, thanks for the response. I have read several of these how-to
articles (and have attempted to implement the code for my
application/WEB page). That's where I encounter the problems.
The article, which you reference, suggests adding impersonation to the
web.config... - already did that. It suggests setting the
ReportingService object credentials to the Default... - already did
that. There's not much else in the article (or others). What I'm
looking for is a little expertise on WEB/IIS 6.0 setup for using the
SQL Reporting Services in a XP Pro, VB.Net 1.1, SQL 2000 environment.
This can't that big a deal - but I'm swamped with other project issues
and have not had the time to research. I'm hoping that someone has
already gone through this and is willing to do some knowledge-sharing.
Thanks,
Glenn
"Teo Lachev" <teo@.nospam.prologika.com> wrote in message news:<eNMBbwMgEHA.396@.TK2MSFTNGP12.phx.gbl>...
> Glenn,
> The right question in this case is "Under what identity am I trying to call
> the RS Web service?". For example, if it is from a WinForm app, you need to
> set proxy credentials to DefaultCredentials, as follows:
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> Please review the "Using the SOAP API in a Windows Application" and "Using
> the SOAP API in a Web Application" for more info.
> --
> Hope this helps.
> ---
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> http://www.prologika.com
>
> "Glenn Owens" <gowens@.nixonpeabody.com> wrote in message
> news:bc4291aa.0408121414.5d18bff0@.posting.google.com...
> > I can't seem to hit on the right combination of roles/persimssions to
> > set against the Report Server in order to allow my localhost
> > implementation to access/browse/upload reports. Is there some deFacto
> > setting(s) that I should be using?
> >
> > After installation I was able to upload and browse. The trouble
> > started when I tried to instantiate a ReportingService object (I
> > wanted to be able to programatically browse the available reports -
> > previously uploaded). When I tried this I got an insufficient
> > privilege error for my generic internet user account. After having
> > tried a number of combinations I'm really stuck.
> >
> > Any help appreciated! (how-to links, diagrams, recipes,...)
> >
> > Glenn|||Hi Glenn:
What is the exact error message you get? Is it access denied, or is it
'USERNAME does not have the required permissions' type of error? Do
you have authentication in web.config set to windows and deny
anonymous acess?
I have some information here:
Authentication, Role-based Security, and SQL Reporting Services Web
Services
http://odetocode.com/Articles/216.aspx
Introduction To Role-Based Security In SQL Server Reporting Services
http://odetocode.com/Articles/215.aspx
--
Scott
http://www.OdeToCode.com
On 13 Aug 2004 04:36:02 -0700, gowens@.nixonpeabody.com (Glenn Owens)
wrote:
>Teo, thanks for the response. I have read several of these how-to
>articles (and have attempted to implement the code for my
>application/WEB page). That's where I encounter the problems.
>The article, which you reference, suggests adding impersonation to the
>web.config... - already did that. It suggests setting the
>ReportingService object credentials to the Default... - already did
>that. There's not much else in the article (or others). What I'm
>looking for is a little expertise on WEB/IIS 6.0 setup for using the
>SQL Reporting Services in a XP Pro, VB.Net 1.1, SQL 2000 environment.
>This can't that big a deal - but I'm swamped with other project issues
>and have not had the time to research. I'm hoping that someone has
>already gone through this and is willing to do some knowledge-sharing.
>Thanks,
>Glenn
>sql

Need help configuring memory space for mssearch & SQL

We recently installed a new dual-CPU Xenon server with 4 GB of RAM. I have the
SQL server configured for "Automatic" memory space allocation. Currently, SQL
is using about 1.5 GB of memory and mssearch is using about 20 MB. mssearch is
being hit much more intensively then the SQL server, and it is paging heavily.
I know how to set a static memory space size for the SQL server, but I don't
know how to set how much memory mssearch will use. Should I just set a static
memory size for SQL to restrict its size, or is there some way to specify how
much mssearch should use?
What are reasonable memory space settings for SQL and mssearch on a 4GB system?
On our system, searching is much more intensive then other SQL operations.
Phil Sherrod
(phil.sherrod 'at' sandh.com)
http://www.dtreg.com (decision tree modeling)
http://www.nlreg.com (nonlinear regression)
http://www.LogRover.com (web traffic analysis)
http://www.NewsRover.com (Usenet newsreader)
Phil,
You can set how much memory the MSSearch service will use via
sp_fulltext_service 'resource_usage' <value>, where <value> is 1 - 5 with 3
being the default. If you set the resource_usage level to 5 (dedicated), the
MSSearch service will use up to a max of 512Mb of RAM. Note, this RAM is
below the 3Gb limit and the service will ONLY use this amount of RAM if no
other service is using or requesting additional RAM as it is passive in
nature. The max of 512Mb is a hard-coded limit and cannot be increased. This
is why SQL Server's memory needs to be set to "fixed memory" and not dynamic
memory as SQL Server will request more memory, if it needs it and therefore
the MSSearch will not be able to use that memory.
Q. Should I just set a static memory size for SQL to restrict its size, or
is there some way to specify how much mssearch should use?
A. No, you need to do both, set static memory for sql server as well as set
sp_fulltext_service 'resource_usage' to 5.
Q. What are reasonable memory space settings for SQL and mssearch on a 4GB
system?
A. For SQL Server that would depend upon your processes, but for the
MSSearch service the max is 512Mb.
Regards,
John
"Phil Sherrod" <phil.sherrod@.REMOVETHISsandh.com> wrote in message
news:c7WdnRoLcZRzNAfcRVn-3g@.giganews.com...
> We recently installed a new dual-CPU Xenon server with 4 GB of RAM. I
have the
> SQL server configured for "Automatic" memory space allocation. Currently,
SQL
> is using about 1.5 GB of memory and mssearch is using about 20 MB.
mssearch is
> being hit much more intensively then the SQL server, and it is paging
heavily.
> I know how to set a static memory space size for the SQL server, but I
don't
> know how to set how much memory mssearch will use. Should I just set a
static
> memory size for SQL to restrict its size, or is there some way to specify
how
> much mssearch should use?
> What are reasonable memory space settings for SQL and mssearch on a 4GB
system?
> On our system, searching is much more intensive then other SQL
operations.
> --
> Phil Sherrod
> (phil.sherrod 'at' sandh.com)
> http://www.dtreg.com (decision tree modeling)
> http://www.nlreg.com (nonlinear regression)
> http://www.LogRover.com (web traffic analysis)
> http://www.NewsRover.com (Usenet newsreader)

Need help comparing tables Query

I have two tables that I need to be compared. The first table <prams>
consists of many fields (DBName, TBName, IName, Other fields) The first
three (DBName, TBName, IName) work together to form the primary key.
The second table <temp> will also contain the same structure.
I need to take the first table <prams> and compare it to the second
table <temp>. If the Row is missing from the second table <temp>, I
need to copy the information form the first table <prams> into third
"Purgatory" table. If the row exists in the first table <prams> I
need to copy the other fields into the second table <prams>.
Once it has gone though and compared all the table entries the <temp>
table will replace the <prams> table.
Thanks
-Matt-Whoops forgot, I am running SQL2005, and would like to keep it SQL not
VB or C#.
Thanks again,|||INSERT INTO [Purgatory]
SELECT t1.[dbname],t1.[tbname],t1.[iname],t1.[other fields]
FROM [Prams] WHERE NOT EXISTS
(SELECT t2.[dbname] FROM [Temp] t2 WHERE t1.[dbname] = t2.[dbname]
AND t1.[tbname] = t2.tbname AND t1.[iname] = t2.[iname])
UPDATE [Temp] --is this what you meant? You repeated the same table name
SET [Other Field1] = t1.[Other Field1]
/* Repeat for other fields that need to be copied */
FROM [Prams] t1 INNER JOIN [Temp] t2 ON
t1.[dbname] = t2.[dbname]
AND t1.[tbname] = t2.tbname
AND t1.[iname] = t2.[iname]
If the temp table replaces the prams table after the update, shouldn't you
be keeping prams in sync with temp instead of the other way around?
"MKruer@.gmail.com" wrote:

> I have two tables that I need to be compared. The first table <prams>
> consists of many fields (DBName, TBName, IName, Other fields) The first
> three (DBName, TBName, IName) work together to form the primary key.
> The second table <temp> will also contain the same structure.
> I need to take the first table <prams> and compare it to the second
> table <temp>. If the Row is missing from the second table <temp>, I
> need to copy the information form the first table <prams> into third
> "Purgatory" table. If the row exists in the first table <prams> I
> need to copy the other fields into the second table <prams>.
> Once it has gone though and compared all the table entries the <temp>
> table will replace the <prams> table.
> Thanks
> -Matt-
>|||Maybe my logic is totally fired but what I am trying to do it rebuild
all the DBs, Tables, and Indexes, and only store those that are on the
system, any DBs, Tables, or Indexes that are no longer there get sent
to a new file.
So I take what was good <prams> compare it to what is known good <temp>
then once everything is processed, <temp> should become the new <prams>
correct?|||Not necessarily, because I am only running this comparison once a day
and I do not know what entities may have changed, but I still want to
know that they changed. So it does not need to be real time.
PS
Thanks you so much for this.

Need help comparing output

Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode = @.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for refurbished and most of these parts have regular part numbers that don't end in R. I am supposed to get the price for the regular one, discount it by 15% and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.This is a multi-part message in MIME format.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
You don't need a cursor for this. Try:
update InvPrice
set
SellingPrice = (select SellingPrice
from InvPrice
where StockCode = '03-18320-00') * 0.85
where
StockCode like '03-18320-00%R'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Andrei" <anonymous@.discussions.microsoft.com> wrote in message
news:42E31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...
Hi everyone. I am having difficulty with the following stored procedure:
CREATE procedure A_sp_UpdatePricing_R_parts_test as
/* Declare variables */
Begin
Declare @.SellingPrice decimal(9),
@.StockCode varchar(30)
/* Declare cursor and open for processing */
Declare System_Cursor Cursor for Select StockCode from InvMaster where
StockCode in ('03-18320-00')
Open System_Cursor
/* Fetch value into cursor */
Fetch Next from System_Cursor into @.StockCode
While @.@.Fetch_Status = 0
Begin
Set @.SellingPrice = Null
Select @.SellingPrice = SellingPrice from InvPrice where StockCode =@.StockCode
If @.SellingPrice >0
Update InvPrice set SellingPrice = 222
Where (StockCode = @.StockCode and StockCode
like '%R')
Fetch Next from System_Cursor into @.StockCode
End
/* Close cursor */
Close System_Cursor
Deallocate System_Cursor
End
GO
What it should do is we have part numbers that end with -R or R for
refurbished and most of these parts have regular part numbers that don't end
in R. I am supposed to get the price for the regular one, discount it by 15%
and use that to set the price for the refurbished part.
I don't know how to set the price i get for the part that is equal to the
part in question but has an R in the end, which stands for refurbished.
Any help would be much appreciated.
--=_NextPart_000_0028_01C3C8D7.32408980
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You don't need a cursor for =this. Try:
update InvPrice
set
SellingPrice ==3D (select SellingPrice
= from InvPrice
= where StockCode =3D '03-18320-00') * 0.85
where
=StockCode like '03-18320-00%R'
-- =Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Andrei" wrote in message news:42E=31D22-31B6-4BE5-AEBF-5DC5B34BD0A9@.microsoft.com...Hi everyone. I am having difficulty with the following stored procedure:CREATE procedure A_sp_UpdatePricing_R_parts_test as/* Declare variables */BeginDeclare @.SellingPrice =decimal(9),@.StockCode varchar(30) /* Declare cursor and open for processing */Declare System_Cursor Cursor for Select StockCode from =InvMaster where StockCode in ('03-18320-00')Open System_Cursor/* Fetch =value into cursor */Fetch Next from System_Cursor into =@.StockCodeWhile @.@.Fetch_Status =3D 0BeginSet @.SellingPrice =3D NullSelect @.SellingPrice =3D SellingPrice from =InvPrice where StockCode =3D @.StockCodeIf @.SellingPrice >0  =; = Update InvPrice set SellingPrice =3D 222 &nbs=p;  =; = &nb=sp; &nbs=p; Where (StockCode =3D @.StockCode and StockCode like '%R') Fetch Next from System_Cursor into @.StockCodeEnd/* Close cursor */Close System_CursorDeallocate System_CursorEndGOWhat =it should do is we have part numbers that end with -R or R for refurbished and =most of these parts have regular part numbers that don't end in R. I am supposed =to get the price for the regular one, discount it by 15% and use that to set =the price for the refurbished part.I don't know how to set the price i get =for the part that is equal to the part in question but has an R in the end, =which stands for refurbished. Any help would be much =appreciated.

---

--=_NextPart_000_0028_01C3C8D7.32408980--

Need help combining views; DDL included

I've solved this issue by creating 3 views but I'd rather do it in 1 SELECT
if possible.
Given my data I want to select duplicate securities based on the cusip field
in the Securities table where the cusip does not exist in the Positions
table. To rephrase I want duplicate securities that are not held.
Given my sample data I want to return one record with the cusip value 'E'
Thanks to anyone who could help.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Positions]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Securities]
GO
CREATE TABLE [dbo].[Positions] (
[AccountID] [int] NOT NULL ,
[SecurityID] [int] NOT NULL ,
[Quantity] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Securities] (
[SecurityID] [int] NOT NULL ,
[CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
(
[AccountID],
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
(
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] ADD
CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
(
[SecurityID]
) REFERENCES [dbo].[Securities] (
[SecurityID]
)
GO
INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)Try This:
Select CusIP, Count(*)
From Securities
Where SecurityID Not In
(Select SecurityID
From Positions)
Group By CusIP
Having Count(*) > 1
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||What sulld happen in case that you add this to your sample data:
INSERT INTO Securities (SecurityID,CUSIP) VALUES (8,'E')
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,8,10)
Would "E" still satisfy your request?
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||Anyway,
If your asnwer is Yes:
Solution 1:
select
sec.CUSIP
from Positions pos right outer join Securities sec
on pos.SecurityID=sec.SecurityID
where pos.AccountID is null
group by sec.CUSIP
having count(sec.CUSIP)>1
Solution 2:
select
sec.CUSIP
from Securities sec
where sec.SecurityID not in
(
select pos.SecurityID from Positions pos
)
group by sec.CUSIP
having count(sec.CUSIP)>1
If your answer is NO:
select CUSIP
from Securities
where CUSIP not in
(
select sec.CUSIP
from Positions pos join Securities sec
on pos.SecurityID=sec.SecurityID
)
group by CUSIP
having count(CUSIP)>1
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||Terri
See Itzik Ben-Gan's script about duplicates
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Terri" <terri@.cybernets.com> wrote in message
news:d3f1h9$ocg$1@.reader2.nmix.net...
> I've solved this issue by creating 3 views but I'd rather do it in 1
SELECT
> if possible.
> Given my data I want to select duplicate securities based on the cusip
field
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>

Need Help Coalescing

Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,DaveHi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:

> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>sql

Need Help Coalescing

Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,Dave
Hi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:

> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>

Need Help Coalescing

Hi,
Below is the code I am struggling with. I want to return only those
"item_id(s)" that pertain to each member. The current results are
giving me every "item_id" for every member.
What am I doing wrong.
Thanks!!!
declare @.Indicatorgroup varchar(8000)
select
@.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
varchar)
from #temp_BLAH
group by Member, item_id
select +@.Indicatorgroup, Member
from #temp_BLAH
group by Member
So the results now are like this:
A,B,C,D,E....Z Smith, Ben
A,B,C,D,E....Z Jones, Dave
But Ben only has A, C, and D.
So I'd like the results to be like:
A,C,D Smith,Ben
A,J,Q Jones,DaveHi
The safe way to do this is to use a cursor if using SQL 2000 see
http://tinyurl.com/yat5xr
John
"Dubs" wrote:
> Hi,
> Below is the code I am struggling with. I want to return only those
> "item_id(s)" that pertain to each member. The current results are
> giving me every "item_id" for every member.
> What am I doing wrong.
> Thanks!!!
> declare @.Indicatorgroup varchar(8000)
> select
> @.Indicatorgroup = coalesce(@.Indicatorgroup + ',','') + cast(item_id as
> varchar)
> from #temp_BLAH
> group by Member, item_id
> select +@.Indicatorgroup, Member
> from #temp_BLAH
> group by Member
> So the results now are like this:
> A,B,C,D,E....Z Smith, Ben
> A,B,C,D,E....Z Jones, Dave
> But Ben only has A, C, and D.
> So I'd like the results to be like:
> A,C,D Smith,Ben
> A,J,Q Jones,Dave
>

Need help choosing Server Hardware

Hi,
I'm ussually just told what I will be working with, but today I was asked to
choose the hardware myself.
First off, it's a small company, so a $10,000 server is not an option. But
also, a $500 box isn't going to cut it either.
Basically, I am builing a web app which generates from 10-100 million
records per calculation (yes, that's the spec). I'm using SQLServer 2000
(possibly 2005 if my employer wants to risk it), and ASP.NET. The
calculations are all done in stored procedures.
Comments, suggestions, and URLs are much appreciated.
Thanks in advance,
John
huh ? well barnes and nobles thought it was good enough + 2 years ago
when it was still in early Beta so risk wich risk ? ,,,,,
Well first tip use a seperate Web and SQL server this is much better
scalable
for business servers i would go for the poweredge series of DELL maybe two
2850 `s ' one with 2 gb ( for the webserver ) and the other one with as
much memory as fits in the budget for the sql database
regards
Michel Posseth
"John" <pls@.dontspamme.com> wrote in message
news:eo1Vabg5FHA.2628@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> Hi,
> I'm ussually just told what I will be working with, but today I was asked
> to choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option.
> But also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>|||Your not going to get a one line answer, as database sizing and web server
sizing are entirely different things and you've not hinted at whether you
expect different servers, raid clusters, resilience, failover, SAN/NAS
storage, lots of web traffic - only that you expect the database to be quite
busy. You've neitehr said if your 100 mill records are 1 bit large, or 1
meg each split across many tables, 1 user per hour or 100,000 per second -
and of course it all counts.
This is not a simple job, and if I was you I would be going back to whoever
asked you to chose the hardware and tell them you need to speak to the
hardware vendor, as they can normally help you make some initial estimates
and suggest appropriate hardware. Dont be fooled by your company being
small, for a high performance database you will easily spent $10,000.
A bit of light reading to get you started
http://www.sql-server-performance.c...re_planning.asp
Regards
John Timney
ASP.NET MVP
Microsoft Regional Director
"John" <pls@.dontspamme.com> wrote in message
news:eo1Vabg5FHA.2628@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm ussually just told what I will be working with, but today I was asked
> to choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option.
> But also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>|||"John Timney ( MVP )" <timneyj@.despammed.com> wrote in message
news:uZnMnzg5FHA.3188@.TK2MSFTNGP15.phx.gbl...
> Your not going to get a one line answer, as database sizing and web server
> sizing are entirely different things and you've not hinted at whether you
> expect different servers, raid clusters, resilience, failover, SAN/NAS
> storage, lots of web traffic - only that you expect the database to be
> quite busy. You've neitehr said if your 100 mill records are 1 bit large,
> or 1 meg each split across many tables, 1 user per hour or 100,000 per
> second - and of course it all counts.
> This is not a simple job, and if I was you I would be going back to
> whoever asked you to chose the hardware and tell them you need to speak to
> the hardware vendor, as they can normally help you make some initial
> estimates and suggest appropriate hardware. Dont be fooled by your
> company being small, for a high performance database you will easily spent
> $10,000.
> A bit of light reading to get you started
> http://www.sql-server-performance.c...re_planning.asp
> --
> Regards
>
Thanks, I will look at the URL.
BTW-There will probably be about 10 users, who will all together, probably
run less than 20 different scenarios per week. So not a lot of load. And
the records will be over 4 tables.
Thanks again,
John|||My 2 cents:
1. By the fastest disk you can find.
2. A single processor will obviously be slower than 2, 4, etc. This
factors out to exponentially slower calculations.
3. $10,000 isn't even nearly adequate unless your willing to wait, and
wait, and wait, and wait.....
4. Given the limited information you've provided I'd assume a 4 processor
machine with 4 GB of ram would still be painfully slow.
5. I agree with waiting on SQL 2005. Let everyone else experiment for 6
months. Microsoft has done their due dilligence but I'd wager there are
things out there waiting to nail people. I'm already seeing things in the
news groups about why things aren't working (Security is enable by default),
patches bringing servers down (Saw that one today in the setup newsgroup),
etc.
"John" wrote:

> Hi,
> I'm ussually just told what I will be working with, but today I was asked
to
> choose the hardware myself.
> First off, it's a small company, so a $10,000 server is not an option. Bu
t
> also, a $500 box isn't going to cut it either.
> Basically, I am builing a web app which generates from 10-100 million
> records per calculation (yes, that's the spec). I'm using SQLServer 2000
> (possibly 2005 if my employer wants to risk it), and ASP.NET. The
> calculations are all done in stored procedures.
> Comments, suggestions, and URLs are much appreciated.
> Thanks in advance,
> John
>
>