Collation Conflict (DHL)

Moving code from Sybase to SQL Server 2005. I have two tables. One table (vINID) is reset and populated right before this code runs. The other table (vINPA) is a table in the database used by the Front End App. This is the error I get when I try and run on our SQL Server 2005.

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_BIN" in the equal to operation.

Here is the SQL code:

SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT

FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated

LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID

WHERE vINID.BLIV_ID = '071600007594'

Thank you for your help.
DavidIdeally, you would change the collation on the oddball table. Was there a reason to make one of these tables with a collation different from the database default collation?|||Just noticed one of the tables is actually on a remote server. Try this:

SELECT DISTINCT
vINID.BLEI_CK,
vINID.BLIV_ID,
ISNULL(vINPA.RCPT_AMT,0) AS RCPT_AMT
FROM Reporting_DEV.dbo.RPT_04597_INID AS vINID -- table re-populated
LEFT JOIN fauafpr0_pids.dbo.CDS_INPA_PMT_ACT AS vINPA
ON vINPA.BLIV_ID = vINID.BLIV_ID COLLATE database_default
WHERE vINID.BLIV_ID = '071600007594'|||Code fix works. Thank you. Not sure what "Collation" is or how to change it or what the database default collations is? New to SQL Server 2005.sqlsql

Collate

Hi,
Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL/NULL do in the following code?
CREATE TABLE [TABLE1] (
[ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL )
Why do I need COLLATE? What does COLLATION mean in table creation?
Thanks a lot!
MikeThanks a lot!
RLoski wrote:
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
> > Hi,
> >
> > Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL/NULL do in the following code?
> >
> > CREATE TABLE [TABLE1] (
> > [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL )
> >
> > Why do I need COLLATE? What does COLLATION mean in table creation?
> >
> > Thanks a lot!
> > Mike
> >
> >|||RLoski,
While generating your script, use unicode option. Then it will not generate
the collate... statements. so you don't need to go and delete them manually.
Venkat
"RLoski" wrote:
> Collate sets the order that items are compared with. It determines how an
> order by is handled. It also determines whether two items are the same or
> different. The collation you have there states that "APPLE" = "Apple" A
> different collation will make these different.
> This is one of the annoying quirks of the tool to create SQL. Since
> individual columns can have their own collation, the tool exports all of the
> collations even if the collation is the default for the database.
> Unless you have a good reason to use a specific collation, I would delete
> the whole collate clause.
> --
> Russel Loski, MCSD.Net
>
> "Michael" wrote:
> > Hi,
> >
> > Could anyone tell me what does COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL/NULL do in the following code?
> >
> > CREATE TABLE [TABLE1] (
> > [ProjectID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> > NULL )
> >
> > Why do I need COLLATE? What does COLLATION mean in table creation?
> >
> > Thanks a lot!
> > Mike
> >
> >

CodeAssist Err:35602 Key is not unique in collection

hi
i use CodeAssist application from Shredian Company.
its good application to make VB/SQL Code from any Database (Code Generator).
but now when i connect to SQL2000 Database by ODBC and open any table in
database i have this error:
35602 Key is not unique in collection
any one know why? or how i can solve this problem?
if someone have code generator application like codeAssist please tell me.
--
Tarek M. SialaHi ,
I found the following article for the cause of this error Err:35602 Key is
not unique in collection. Hope it help
POTENTIAL CAUSES :
1. The Integrity Wizard has been run on a database that has already
completed the Integrity Checks.
Resolution - Delete the Integrity Wizard Upgrade tables from the Application
database directory.
CORRECTION STEPS:
1. Using Explorer locate and delete the following .DAT files from the
Application database directory:
DATECHECK.DAT DATECKERR.DAT INTCKFLOW.DAT
Sylvana Mounir
Devloper Support Engineer
Micorosft MEA Developer Support Center
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:e3nO5cIbGHA.2372@.TK2MSFTNGP03.phx.gbl...
> hi
> i use CodeAssist application from Shredian Company.
> its good application to make VB/SQL Code from any Database (Code
> Generator).
> but now when i connect to SQL2000 Database by ODBC and open any table in
> database i have this error:
> 35602 Key is not unique in collection
> any one know why? or how i can solve this problem?
> if someone have code generator application like codeAssist please tell me.
> --
> Tarek M. Siala
>|||Some times is better to buy books and learn how to write the code from your
self. You save lot of money and time
Need more help? Search the web http://search.elakbar.net
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message news:e3nO5cIbGHA.237
2@.TK2MSFTNGP03.phx.gbl...
hi
i use CodeAssist application from Shredian Company.
its good application to make VB/SQL Code from any Database (Code Generator).
but now when i connect to SQL2000 Database by ODBC and open any table in
database i have this error:
35602 Key is not unique in collection
any one know why? or how i can solve this problem?
if someone have code generator application like codeAssist please tell me.
--
Tarek M. Siala

Code.SafeDivide Expression

What does =Code.SafeDivide mean in an expression? I have an example below:
=Code.SafeDivide (Sum(Fields!revenue.Value), Sum(Fields!volume.Value))
Thanks, DeborahThe only time I've seen something like that is when you write a custom
code function called "SafeDivide". This will do a check on the divisor
(the second parameter) and, if it is 0, it will not perform the
division. It will just return 0. Here is an example I found (this
example will actually take a 3rd parameter - the "value if undefined"
parameter):
The following was snipped from this thread. Read it to see the full
explanation:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/b66002620ec40e52/ed9544519ff38234?q=safedivide&rnum=2#ed9544519ff38234
Public Function SafeDivide(pi_dblNumerator As Double, pi_dblDenominator
As
Double, pi_dblUndefined As Double)
If pi_dblDenominator = 0 Then
SafeDivide = pi_dblUndefined
Else
SafeDivide = pi_dblNumerator / pi_dblDenominator
End If
End Function
Regards,
Dan

Code works without transaction but not with transaction?

C#
VS 2003
.Net Framework V1.1 SP1
SQL Server 2000 SP3
Enterprise Library June 2005
I'm working with some code {not of my creation} that performs the
following sequence of actions:
- Open SqlConnection using SQL authentication with UID & PWD in connect
string
- BeginTransaction
- Call stored procedure that updates 1 table {OK}
- Call stored procedure that updates 1 table {FAIL}
- Call stored procedure that updates 1 table
- Call stored procedure that updates 1 table
- Commit
- Dispose
All calls to the stored procedures are coded like this:
SqlHelper.ExecuteNonQuery(transaction,
CommandType.StoredProcedure,
"StoredProcedure",
param, param ...)
SYMPTOMS:
-If I take away the transaction the code works Ok
-If I use the transaction the code always fails on the second stored
procedure call with a "Login failed for user xxx" exception bubbled up
from ADO.NET's ConnectionPool.GetConnection() method
-If I shuffle the order of procedure calls the second call still always
fails
-If I put a breakpoint before the second procedure call the
transaction's connection is Open and the Isolation level is the default
{ReadCommitted}
As the natives were gathering outside my cube with pitchforks and
torches I hacked out the transaction and put something that works back
online. But, what the heck is happening - any ideas?Crash,
Well, you obviously have an error with the second stored procedure. You
need to track that down.
As for why everything fails, once there is a fail vote in the
transaction, SQL Server will not let you back it out. You have to redo the
whole thing.
Hope this helps.
- Nicholas Paldino [.NET/C# MVP]
- mvp@.spam.guard.caspershouse.com
"Crash" <sourcenexus@.san.rr.com> wrote in message
news:1138833840.836442.96230@.z14g2000cwz.googlegroups.com...
> C#
> VS 2003
> .Net Framework V1.1 SP1
> SQL Server 2000 SP3
> Enterprise Library June 2005
> I'm working with some code {not of my creation} that performs the
> following sequence of actions:
> - Open SqlConnection using SQL authentication with UID & PWD in connect
> string
> - BeginTransaction
> - Call stored procedure that updates 1 table {OK}
> - Call stored procedure that updates 1 table {FAIL}
> - Call stored procedure that updates 1 table
> - Call stored procedure that updates 1 table
> - Commit
> - Dispose
> All calls to the stored procedures are coded like this:
> SqlHelper.ExecuteNonQuery(transaction,
> CommandType.StoredProcedure,
> "StoredProcedure",
> param, param ...)
> SYMPTOMS:
> -If I take away the transaction the code works Ok
> -If I use the transaction the code always fails on the second stored
> procedure call with a "Login failed for user xxx" exception bubbled up
> from ADO.NET's ConnectionPool.GetConnection() method
> -If I shuffle the order of procedure calls the second call still always
> fails
> -If I put a breakpoint before the second procedure call the
> transaction's connection is Open and the Isolation level is the default
> {ReadCommitted}
> As the natives were gathering outside my cube with pitchforks and
> torches I hacked out the transaction and put something that works back
> online. But, what the heck is happening - any ideas?
>|||I switched the order of the procedure calls around - it doesn't matter
what order I call them in the second one always fails. If I remove the
transaction all of the procedure calls succeed...
What disturbs me is that I am in the middle of performing steps in an
active transaction on an open connection - and the exception is
bubbling up from the connection pool' Why would it try to fetch a
connection from the pool while I'm in the middle of a transaction'|||I forgot to add:
The transaction is NOT distributed - all stored procedures are
executing in the same database...|||On 1 Feb 2006 14:44:00 -0800, Crash wrote:

>C#
>VS 2003
>.Net Framework V1.1 SP1
>SQL Server 2000 SP3
>Enterprise Library June 2005
>I'm working with some code {not of my creation} that performs the
>following sequence of actions:
>- Open SqlConnection using SQL authentication with UID & PWD in connect
>string
>- BeginTransaction
>- Call stored procedure that updates 1 table {OK}
>- Call stored procedure that updates 1 table {FAIL}
>- Call stored procedure that updates 1 table
>- Call stored procedure that updates 1 table
>- Commit
>- Dispose
>All calls to the stored procedures are coded like this:
>SqlHelper.ExecuteNonQuery(transaction,
> CommandType.StoredProcedure,
> "StoredProcedure",
> param, param ...)
>SYMPTOMS:
>-If I take away the transaction the code works Ok
>-If I use the transaction the code always fails on the second stored
>procedure call with a "Login failed for user xxx" exception bubbled up
>from ADO.NET's ConnectionPool.GetConnection() method
>-If I shuffle the order of procedure calls the second call still always
>fails
>-If I put a breakpoint before the second procedure call the
>transaction's connection is Open and the Isolation level is the default
>{ReadCommitted}
>As the natives were gathering outside my cube with pitchforks and
>torches I hacked out the transaction and put something that works back
>online. But, what the heck is happening - any ideas?
Hi Crash,
Since it's always the second procedure that fails, regardless of the
order of execution, I suspect that the stored procedures somehow mess up
the transaction nesting level. Maybe they start a transaction that they
don't end, or maybe they end (commit/rollback) the open transaction that
the next procedure expects to be still open?
What happens if you execute a script like the one below from Query
Analyzer:
BEGIN TRANSACTION
EXEC Proc1 param, param, ...
EXEC Proc2 param, param, ...
EXEC Proc3 param, param, ...
EXEC Proc4 param, param, ...
COMMIT TRANSACTION
Do all procedures execute or do you get errors? If the latter, please
copy and paste the error messages.
It might also help to post the code of the stored procedures themselves.
Hugo Kornelis, SQL Server MVP|||- FYI this code is running under ASP, but it is logging into the
database as a specific user. The user that ASP logs in as is not dbo
but does have rights on all of the tables & procedures involved {I
checked}...
- None of the tables involved have any triggers on them {I checked}...
- The entire database has no indexes, no constraints, or any other of
those pesky referrential integrity nuisances {not the way I'd do it
either but I got called in to help on this one}...
- Forgot to mention it earlier but had already tried it - yes - within
query analyzer I can succesfully execute any combination of sproc calls
within a transaction just as you recommended... No errors from Query
analyzer within a transaction no matter what order of execution...
- None of the stored procedures does anything with transactions in and
of themselves - they each insert or update 1 row in a single table per
call... The stored procedures do different things but here's a look at
one of them:
CREATE PROCEDURE dbo.WT_UserInfo_Update
(
@.UserID int =Null,
@.Password varchar(64) = NULL,
@.FirstName varchar(30) = NULL,
@.LastName varchar(30) = NULL,
@.JobTitle varchar(100) = NULL,
@.PhoneNumber varchar(50) = NULL,
@.EmailAddress varchar(100) = NULL,
@.AdminUserID int = NULL
)
AS
BEGIN
--Update Existing User
Update WT_User
set Password=@.Password,
PasswordChangeDate=getdate(),
FirstName=@.FirstName,
LastName=@.LastName,
JobTitle=@.JobTitle,
PhoneNumber=@.PhoneNumber,
EmailAddress=@.EmailAddress,
ModifiedBy= @.AdminUserID
Where UserID=@.UserID
IF @.@.ERROR <> 0 GOTO ExitProcError
return @.UserID
END
ExitProcError:
Return(-1)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
GRANT EXECUTE ON [dbo].[WT_UserInfo_Update] TO [<<same user as used
in connect string>>]
GO
The other procedures do not do anything fancier; 1 deletes from a
{different} table where @.UserID=xxx, and a third procedure is called x
times to insert 1 row into the other table per call.
I'm thinking this is more of an ADO issue of some sort, but it is
bizarre. Flawlessly reproducable, but very bizarre...|||On 3 Feb 2006 11:50:06 -0800, Crash wrote:

>- FYI this code is running under ASP, but it is logging into the
>database as a specific user. The user that ASP logs in as is not dbo
>but does have rights on all of the tables & procedures involved {I
>checked}...
>- None of the tables involved have any triggers on them {I checked}...
>- The entire database has no indexes, no constraints, or any other of
>those pesky referrential integrity nuisances {not the way I'd do it
>either but I got called in to help on this one}...
>- Forgot to mention it earlier but had already tried it - yes - within
>query analyzer I can succesfully execute any combination of sproc calls
>within a transaction just as you recommended... No errors from Query
>analyzer within a transaction no matter what order of execution...
>- None of the stored procedures does anything with transactions in and
>of themselves - they each insert or update 1 row in a single table per
>call... The stored procedures do different things but here's a look at
>one of them:
>
(snip)
>The other procedures do not do anything fancier; 1 deletes from a
>{different} table where @.UserID=xxx, and a third procedure is called x
>times to insert 1 row into the other table per call.
>I'm thinking this is more of an ADO issue of some sort, but it is
>bizarre. Flawlessly reproducable, but very bizarre...
Hi Crash,
Yeah, I have to agree. Since a logically identical series of statements
from Query Analyzer executes flawlessly, it's probably not a SQL Server
issue.
I have no ADO experience, so I can't help you further. Maybe you should
take this to an ADO group. (I think they'll want to see more of the
front--end code than you posted here, though).
Hugo Kornelis, SQL Server MVP

Code works differently based on the provider

Hello

The following code does not function if I use SQLOLEDB if I omit the provide and default to ODBC OLE DB it works correctly. I am assume I am coding something wrong for a SQLOLEDB provide. Any help is greatly appricated.

VB Code

Public Function SqlExecuteResult(xSQL As String, sServer As String, sDatabase As String, sUserName As String, sPassword As String, sCaller As String, Optional bLog As Boolean = False) As Object

Dim oDB As Object
Dim oRS As Object

Set oDB = CreateObject("adodb.connection")
Set oRS = CreateObject("adodb.recordset")

oDB.open "driver={SQL Server};provider=sqloledb;server=" & sServer & ";database=" & sDatabase & ";uid=" & sUserName & ";pwd=" & sPassword & ";"
oRS.CursorLocation = adUseClient
oRS.CursorType = adOpenStatic
Set oRS.ActiveConnection = oDB
oRS.open xSQL
Set oRS.ActiveConnection = Nothing
Set SqlExecuteResult = oRS
oDB.Close
Set oDB = Nothing

End Function

Private Sub Form_Load()
Dim rs As Object
Set rs = SqlExecuteResult("exec NextEntry 'SentMessages'", "surecomp-bob", "pmsureus33", "sa", "", "")
MsgBox rs.fields(0)
End Sub

SQL proceedure

CREATE PROCEDURE NextEntry @.CounterName Varchar(20) AS

begin
declare @.counter int
select @.counter = counter from counters where countername = @.counterName
select @.counter = @.counter + 1
update counters set counter = @.counter where countername = @.countername
select counter from counters where countername = @.counterName
End
GO

Thanks

Bob Jenkin

Are you having both "driver=" and "provider=sqloledb" in the connection string?
If you specified sqloledb you should have removed "driver=" part.

Code with color

Hi

I have three tables in my db called Products, Receives, Issues.

Products

ProductID, ProductName

Receives

ProductID, ReceiveQty

Issues

ProductID, IssueQty

I want to pass an ProductID and display columns from a sproc like this -

ProductName, ReceiveQty, IssueQty, BalanceQty ( ReceiveQty - IssueQty )

A record may exist or not in the Issues table for a given ProductID. What will be the syntex to write this sproc?

Regards

Kapalic

Something like this:

Code Snippet

SELECT
p.ProductName,
isnull( r.ReceiveQty, 0 ),
isnull( i.IssueQty, 0 ),
BalanceQty = ( isnull( r.ReceiveQty, 0 ) - isnull( i.IssueQty, 0 ))
FROM Products p
JOIN Receives r
ON p.ProductID = r.ProductID
LEFT JOIN Issues i
On p.ProductID = i.ProductID

I must note: this seems like a very odd design, and may be troublesome to properly 'tune'.

|||

Try the following, it may be helpful for you.

Code Snippet

select ProductName, ReceiveQty, IssueQty, (ReceiveQty-IssueQty) as BalanceQty
from
(
select Products.ProductName,
isnull((select sum(ReceiveQty) from Receives where Receives.ProductID=Products.ProductID),0) as ReceiveQty,
isnull((select sum(IssueQty) from Issues where Issues.ProductID=Products.ProductID),0) as IssueQty
from Products where Products.ProductID = yourProductId
) as temp

|||to:Arnie Rowland

what will happen if i recieve a product 2 times or more?

|||

You make a good observation. I should have included a [GROUP BY].

I think that this revision will be more in line with an acceptable solution:

Code Snippet

SELECT
p.ProductName,
p.ProductID,
dt.ReceiveQty,
dt.IssueQty,
dt.BalanceQty
FROM Products p
JOIN (SELECT
p.ProductID,
ReceiveQty = sum( isnull( r.ReceiveQty, 0 )),
IssueQty = sum( isnull( i.IssueQty, 0 )),
BalanceQty = sum( isnull( r.ReceiveQty, 0 ) - isnull( i.IssueQty, 0 ))
FROM Products p
LEFT JOIN Receives r
ON p.ProductID = r.ProductID
LEFT JOIN Issues i
On p.ProductID = i.ProductID
WHERE p.ProductID = @.ProductID
GROUP BY ProductID
) dt
ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID

|||but, what will happen if i recieve a kind of product but never issue?|||

IssueQty will be 0 (zero) if no rows exist in the Issues table.

ReceiveQty will be 0 (zero) if no rows exist in the Receives table.

|||

Arnie

I m getting incorrect result - while I enter multiple receive record, every of three qty become double of actual result! Whats the prob?

Kapalic

|||

In order by continue assisting you, it would be useful to have sample data to test.

Please include table DDL and sample data in the form of INSERT statements.

|||

Kapalic,

First, my apologies. I was quickly tossing code together without thoroughly thinking through your situation. After your last post, I realized that I had made a significant error in what I previous posted.

Hopefully, this code will get you what you want.

-Arnie

Code Snippet

DECLARE @.Products table

( ProductID int,

ProductName varchar(25)

)

DECLARE @.Receives table

( ProductID int,

ReceiveQty int

)

DECLARE @.Issues table

( ProductID int,

IssueQty int

)

INSERT INTO @.Products VALUES ( 1, 'Product1' )
INSERT INTO @.Products VALUES ( 2, 'Product2' )
INSERT INTO @.Products VALUES ( 3, 'Product3' )
INSERT INTO @.Products VALUES ( 4, 'Product4' )
INSERT INTO @.Products VALUES ( 5, 'Product5' )

INSERT INTO @.Receives VALUES ( 1, 5 )
INSERT INTO @.Receives VALUES ( 2, 12 )
INSERT INTO @.Receives VALUES ( 3, 10 )
INSERT INTO @.Receives VALUES ( 4, 25 )
INSERT INTO @.Receives VALUES ( 1, 10 )
INSERT INTO @.Receives VALUES ( 2, 10 )
INSERT INTO @.Receives VALUES ( 1, 12 )

INSERT INTO @.Issues VALUES ( 1, 2 )
INSERT INTO @.Issues VALUES ( 2, 5 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 3, 10 )
INSERT INTO @.Issues VALUES ( 2, 1 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 5, 5 )

DECLARE @.ProductID int

SET @.ProductID = 1

-- This code could become a FUNCTION to return only BalanceQty,
-- or a STORED PROCEDURE to return all current values
SELECT
p.ProductName,
p.ProductID,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )),
IssueQty = ( isnull( dt.IssueQty, 0 )),
BalanceQty = ( isnull( dt.ReceiveQty, 0 ) - isnull( dt.IssueQty, 0 ))
FROM (SELECT
ProductID = @.ProductID,

ReceiveQty = ( SELECT sum( isnull( ReceiveQty, 0 ))

FROM @.Receives

WHERE ProductID = @.ProductID

),

IssueQty = ( SELECT sum( isnull( IssueQty, 0 ))

FROM @.Issues

WHERE ProductID = @.ProductID

)

) dt

JOIN @.Products p

ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID

|||

Dear Arnie,

Thank you very much for your great help! This sproc is now working allright. Will you please tell me what was wrong with the sproc?

And I need a bit more help. I can't understand how to modify the sproc so that it displays all the products which are listed in the product table. Please help!!

Regards

Kapalic

|||

Code Snippet

SELECT
p.ProductName,
p.ProductID,
p.AdditionalProductsColumnsHere,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )),

You can add additional columns from the Products table in the outer query. Prefix each column with the alias 'p'.
|||

I didn't want to show another column. I wanted to show all the records from the products table, not just a single record about a product. I couldn't make myself clear.

Thnx

Kapalic

|||

Arnie Rowland wrote:

Kapalic,

First, my apologies. I was quickly tossing code together without thoroughly thinking through your situation. After your last post, I realized that I had made a significant error in what I previous posted.

Hopefully, this code will get you what you want.

-Arnie

Code Snippet

DECLARE @.Products table

( ProductID int,

ProductName varchar(25)

)

DECLARE @.Receives table

( ProductID int,

ReceiveQty int

)

DECLARE @.Issues table

( ProductID int,

IssueQty int

)

INSERT INTO @.Products VALUES ( 1, 'Product1' )
INSERT INTO @.Products VALUES ( 2, 'Product2' )
INSERT INTO @.Products VALUES ( 3, 'Product3' )
INSERT INTO @.Products VALUES ( 4, 'Product4' )
INSERT INTO @.Products VALUES ( 5, 'Product5' )

INSERT INTO @.Receives VALUES ( 1, 5 )
INSERT INTO @.Receives VALUES ( 2, 12 )
INSERT INTO @.Receives VALUES ( 3, 10 )
INSERT INTO @.Receives VALUES ( 4, 25 )
INSERT INTO @.Receives VALUES ( 1, 10 )
INSERT INTO @.Receives VALUES ( 2, 10 )
INSERT INTO @.Receives VALUES ( 1, 12 )

INSERT INTO @.Issues VALUES ( 1, 2 )
INSERT INTO @.Issues VALUES ( 2, 5 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 3, 10 )
INSERT INTO @.Issues VALUES ( 2, 1 )
INSERT INTO @.Issues VALUES ( 1, 5 )
INSERT INTO @.Issues VALUES ( 5, 5 )

DECLARE @.ProductID int

SET @.ProductID = 1

-- This code could become a FUNCTION to return only BalanceQty,
-- or a STORED PROCEDURE to return all current values
SELECT
p.ProductName,
p.ProductID,
ReceiveQty = ( isnull( dt.ReceiveQty, 0 )),
IssueQty = ( isnull( dt.IssueQty, 0 )),
BalanceQty = ( isnull( dt.ReceiveQty, 0 ) - isnull( dt.IssueQty, 0 ))
FROM (SELECT
ProductID = @.ProductID,

ReceiveQty = ( SELECT sum( isnull( ReceiveQty, 0 ))

FROM @.Receives

WHERE ProductID = @.ProductID

),

IssueQty = ( SELECT sum( isnull( IssueQty, 0 ))

FROM @.Issues

WHERE ProductID = @.ProductID

)

) dt

JOIN @.Products p

ON p.ProductID = dt.ProductID
WHERE p.ProductID = @.ProductID

Can you tell me how to post a sample code with color?

|||

It's very easy. Copy the code to MS Word, and copy and paste it from Word to post. It will appear colorful.

Kapalic

code view access

Hello all, I need to prevent all users (other than sysadmin/dbo) privs to access the database code (SP/Views/UDF). What would be the best way to achieve that? TIA

In SQL Server 2005 this should be the default behavior. Please refer to BOL for further details:

http://msdn2.microsoft.com/en-us/library/ms191507.aspx

http://msdn2.microsoft.com/en-us/library/ms188371.aspx

If you have further question please let us know, we will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Thanks Raul, we are actually using SQL 2000 SP4 (8.00.2040 build) in all our environments.

Do we need to DENY access from sp_helptext/syscomments or any other objects? Any thoughts are appreciated. Thanks.

|||

Unfortunately, there is really not a way to do that with SQL 2000 -I know, big time bummer.

If a user has permissions to execute the object, the user can see the code for the object -unless it is encryped (and SQL 2000 code encryption is very lame...) The concept of separating permissions for viewing the code and executing the code came about in SQL 2005.

|||Thanks, wouldn't protecting sp_helptext and syscomments resolve this? I guess whenever code is opened through any util, it must be querying syscomments either using sp_helptext / DMO in SQL 2000. Am I incorrect on this assumption?

Code to verify date as Valid

Is there code to establish if a date is valid
so that I can send an error message if i get
the 31st of February for instance
im thinking of something like the following but don't know the code
@.feedback = case when (@.date is ok? ) then 'DateGood' else 'DateBad' end
regards EarnieI assume that @.date is a character string? If it was of a datetime datatype,
it would not have accepted an invalid date.
So, if @.date is a character string, you can use the ISDATE() function:
declare @.validdate as varchar(30), @.invaliddate as varchar(30);
set @.validdate = '20050212';
set @.invaliddate = '20050231';
select isdate(@.validdate), isdate(@.invaliddate)
-- --
1 0
BG, SQL Server MVP
www.SolidQualityLearning.com
"Earnie" <Earnie@.discussions.microsoft.com> wrote in message
news:D7CB4DC0-D8B0-433F-9D77-E40D3D147866@.microsoft.com...
> Is there code to establish if a date is valid
> so that I can send an error message if i get
> the 31st of February for instance
> im thinking of something like the following but don't know the code
> @.feedback = case when (@.date is ok? ) then 'DateGood' else 'DateBad' end
> regards Earnie|||ISDATE function
e.g. from BOL:
DECLARE @.datestring varchar(8)
SET @.datestring = '12/21/98'
SELECT ISDATE(@.datestring)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Earnie" <Earnie@.discussions.microsoft.com> wrote in message
news:D7CB4DC0-D8B0-433F-9D77-E40D3D147866@.microsoft.com...
> Is there code to establish if a date is valid
> so that I can send an error message if i get
> the 31st of February for instance
> im thinking of something like the following but don't know the code
> @.feedback = case when (@.date is ok? ) then 'DateGood' else 'DateBad' end
> regards Earnie|||use isdate(expr)

code to return a dataset

Our database is normalized to the point that I need to build a dataset to
render a report from. The code to build the dataset has logic that I would
like to keep in a custom assembly. What is the best way to call that code
and have it pass the dataset back for use by the report?
Thanks,
ShawnYou'd make life alot easier for yourself if you build the code using a
stored procedure... Any reason not to? You can encrypt it if that is your
concern.
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:AA8B7D2A-4A54-4BA8-B7F5-598FFF1BBCF1@.microsoft.com...
> Our database is normalized to the point that I need to build a dataset to
> render a report from. The code to build the dataset has logic that I
> would
> like to keep in a custom assembly. What is the best way to call that code
> and have it pass the dataset back for use by the report?
> Thanks,
> Shawn|||Thanks, but the users that will be implementing the reports won't have the
ability to make stored procedures. Is there another way?
"Mary Bray [MVP]" wrote:
> You'd make life alot easier for yourself if you build the code using a
> stored procedure... Any reason not to? You can encrypt it if that is your
> concern.
> --
> Mary Bray [SQL Server MVP]
> Please reply only to newsgroups
> "sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
> news:AA8B7D2A-4A54-4BA8-B7F5-598FFF1BBCF1@.microsoft.com...
> > Our database is normalized to the point that I need to build a dataset to
> > render a report from. The code to build the dataset has logic that I
> > would
> > like to keep in a custom assembly. What is the best way to call that code
> > and have it pass the dataset back for use by the report?
> >
> > Thanks,
> > Shawn
>
>

Code to populate a table

Can someone help me with a code to read my database backup
time and populate it to a table for querying and
reporting.
Hi,
The backup informations are already stored in MSDB database. The table name
is backupset.
select database_name,type,backup_start_date,backup_finish _date from
msdb..backupset
regarding backups you could alsee the below tables in msdb database.
backupfile
backupmediafamily
backupmediaset
Thanks
Hari
MCDBA
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:0a4d01c46e75$185d8e70$a601280a@.phx.gbl...
> Can someone help me with a code to read my database backup
> time and populate it to a table for querying and
> reporting.

Code to populate a table

Can someone help me with a code to read my database backup
time and populate it to a table for querying and
reporting.Hi,
The backup informations are already stored in MSDB database. The table name
is backupset.
select database_name,type,backup_start_date,bac
kup_finish_date from
msdb..backupset
regarding backups you could alsee the below tables in msdb database.
backupfile
backupmediafamily
backupmediaset
Thanks
Hari
MCDBA
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:0a4d01c46e75$185d8e70$a601280a@.phx.gbl...
> Can someone help me with a code to read my database backup
> time and populate it to a table for querying and
> reporting.

Code to populate a table

Can someone help me with a code to read my database backup
time and populate it to a table for querying and
reporting.Hi,
The backup informations are already stored in MSDB database. The table name
is backupset.
select database_name,type,backup_start_date,backup_finish_date from
msdb..backupset
regarding backups you could alsee the below tables in msdb database.
backupfile
backupmediafamily
backupmediaset
Thanks
Hari
MCDBA
"Aboki" <hcokoli@.yahoo.com> wrote in message
news:0a4d01c46e75$185d8e70$a601280a@.phx.gbl...
> Can someone help me with a code to read my database backup
> time and populate it to a table for querying and
> reporting.

Code to Import Bank Files (BAI, BAI2)

Anyone have some code to import bank files to SQL Server 2000 tables in the formats BAI or BAI2? I'm using a DTS package currently, but I'd like to have more control over this by using T-SQL or vbscript or something.

These files have complex structures/multiple record types. Not to mention my main problem of trying to use a bulk insert which doesn't seem to work because of something to do with the row delimiters. Not too sure what's up with that because I usually do this for all my flat file imports. I'm certain vbscript will work, but I'd like to know if anyone out there has already built something for these particular files?
Thanks!

I have not worked on BAI in years, but what I ended up doing was importing the file into a table with 2 fields: linetype and linevalue, then parsing the line based on the type.

The problem with the stored proc is the string processing is very limited in TSQL. It is much easier to mainupliate in VB using Split().

You could create the same affect using SSIS in a Dataflow with conditional branching on linetype. I would have to think about the type 88 lines continuation. I am not sure how you would handle that.

Also, I wrote a preproceesor which converted BAI1 to BAI2, there is not much difference. Then my code only had to deal with BAI2 files.

Good luck.

Code to flatten a CellSet into a datatable

Hi,

Here's some code I've written which flattens a CellSet into a datatable. I looked for a long time to find similar code but could not find any. Can I please have some thoughts on optimisation/elegance etc... I'm a bit new to .NET so any advice would be great!

Thanks.

Code Snippet

Public Function GetFlatData() As DataTable

'If (Me.Cells.Count = 1) Then
' Return New DataTable()
'End If

Dim table As DataTable = New DataTable()
Dim headers As ArrayList = New ArrayList()

For a As Integer = 0 To (Me.Axes.Count - 1)
For i As Integer = 0 To (Me.Axes(a).Set.Hierarchies.Count - 1)
Dim h As Hierarchy = Me.Axes(a).Set.Hierarchies(i)
If (Not h.Name.Contains("Measure")) Then
table.Columns.Add(h.Name)
End If
Next i
Next a

Dim items As ArrayList = New ArrayList()
For x As Int32 = 0 To (Me.Axes(0).Set.Tuples.Count - 1)
Dim tx As Tuple = Me.Axes(0).Set.Tuples(x)
Dim itemBase As CellItem = New CellItem()
For Each mx As Member In tx.Members
If (mx.LevelName.Contains("Measure")) Then
If table.Columns.IndexOf(mx.Caption) = -1 Then
table.Columns.Add(New DataColumn(mx.Caption, GetType(Double)))
End If
itemBase.AddMeasure(mx)
Else
itemBase.AddAttribute(mx)
End If
Next
If (Me.Axes.Count > 1) Then
For y As Int32 = 0 To (Me.Axes(1).Set.Tuples.Count - 1)
Dim item As CellItem = itemBase.Clone(True)

Dim ty As Tuple = Me.Axes(1).Set.Tuples(y)
Dim memy As Member
For Each memy In ty.Members
If (memy.LevelName.Contains("Measure")) Then
item.AddMeasure(memy)
Else
item.AddAttribute(memy)
End If
Next

item.ValueCell = Me.Cells.Item((y * Me.Axes(0).Set.Tuples.Count) + x)
'item.ValueCell = Me.CellGrid(x, y)
items.Add(item)
Next
Else
' for 1 axis cellsets
itemBase.ValueCell = Me.Cells.Item(x)
items.Add(itemBase)
End If
Next
'' might have no measures need to cater
For i As Integer = 0 To (items.Count - 1)
Dim row As DataRow = table.NewRow()
Dim attributes As ArrayList = (CType(items(i), CellItem)).Attributes
For j As Integer = 0 To (attributes.Count - 1)
Dim member As Member = CType(attributes(j), Member)
row(j) = member.Caption
Next
Dim measures As ArrayList = (CType(items(i), CellItem)).Measures
If Not measures Is Nothing Then
' each CellItem should only have one measure
For j As Integer = 0 To (measures.Count - 1)
Dim member As Member = CType(measures(j), Member)

Dim value As Cell = (CType(items(i), CellItem)).ValueCell
If Not (String.IsNullOrEmpty(value.Value)) Then
'need to set appropriate type accroding to cell
'table.Columns[member.Caption].DataType = value.CellProperties.
row(member.Caption) = value.Value
Else
row(member.Caption) = DBNull.Value
End If
Next
End If
table.Rows.Add(row)
Next

Return table
End Function

#End Region

#Region " Private Classes "
Private Class CellItem
Private _measures As ArrayList
Private _attributes As ArrayList
Private _valueCell As Cell

Public Property Attributes() As ArrayList
Get
Return _attributes
End Get
Set(ByVal value As ArrayList)
Me._attributes = value
End Set
End Property

Public Property Measures() As ArrayList
Get
Return _measures
End Get
Set(ByVal value As ArrayList)
Me._measures = value
End Set
End Property

Public Property ValueCell() As Cell
Get
Return _valueCell
End Get
Set(ByVal Value As Cell)
_valueCell = Value
End Set
End Property

Public Sub New()
_attributes = New ArrayList()
_measures = New ArrayList()
End Sub

Public Sub New(ByVal attributes As ArrayList, ByVal measures As ArrayList, ByVal valueCell As Cell)
Me.Attributes = attributes
Me.Measures = measures
Me.ValueCell = valueCell
End Sub

Public Sub AddAttribute(ByVal o As Object)
_attributes.Add(o)
End Sub

Public Sub AddMeasure(ByVal o As Object)
_measures.Add(o)
End Sub

Public Function Clone(ByVal isDeep As Boolean) As Object
If (isDeep) Then
Dim item As CellItem = New CellItem(Me.Attributes.Clone(), Me.Measures.Clone(), Me.ValueCell)
Return item
Else
Return New CellItem(Me.Attributes, Me.Measures, Me.ValueCell)
End If
End Function

AS 2005 can return query result as a flattened rowset instead of cellset. If you can take advantage of the feature then you don't have to do it yourself.|||The code transforms a cellset into a datatable that resembles a standard denormalized fact table.
How do you do that? Is there a feature in MDX to allow this?

|||Your code flattens a CellSet object. Given an AdomdCommand object, calling ExecuteCellSet method returns a CellSet object, calling ExecuteReader method returns an AdomdDataReader object which is a flattened recordset.

Code to calculate lat/lon given a lat/lon and distance?

My math skills are not up to it so I have to ask for it!
I need a procedure which accepts one lat, lon, and Distance(in feet? miles?)
and returns the lat's and lon's which describe a square around the given
lat/lon with 2 * Distance on a side. This will be used to search a database
for objects which fit within the given square. I can't draw it here, but the
given lat/lon would be at the center of the square. Any suggestions where I
might find an example? I have code to find the distance between two lat/lon'
s
but converting it is well beyond me.
Thanks,
MichaelPost DDL and some sample data, and we can help you find a solution.
ML|||> I need a procedure which accepts one lat, lon, and Distance(in feet? miles?)d">
> and returns the lat's and lon's which describe a square around the given
> lat/lon with 2 * Distance on a side.
Well, for starters, there's no such thing as a square on the surface of the
Earth. A square can only exist on a flat surface. So that leads us to...
How accurate does the result need to be?
And how large will the distance be, at max?
To get *really* accurate, you need to use spherical trig and a formula that
is really hairy.
But to get "reasonable" results--as in searching for all zip codes that are
within 50 miles of a given zip code--you can get away with approximating
things just using the pythagorean theorem ( a^2 + b^2 = c^2: square of the
hypotenuse of a right triangle is equal to the squares of the other two
sides).
Also, the number of miles per degree (or degrees per mile) will vary from
the North side of the "square" to the South side thereof. [Obvious when you
look at a globe: Near the poles, there are many fewer miles per degree than
there are at the equator.]

Code table maintenance

Hello,
I am working on the logical model for a database. I need to use a number of
code tables (tables that keep typically name value pairs. I need maintain da
ta like products, services etc).
I am wondering if I increase the abstraction and use one table to represent
the name value pairs but use a category to identify each type is there is an
y value in doing this?
The advantage with this I think is consolidating the data and probably minim
izing the administration
The disadvantages may be too many joins that need to be qualified by the cat
egory type. Also, I may end up having too many self-joins.
Any suggestions'?doesnt seem to make sense to me.
I would keep them seperate.
Greg Jackson
PDX, Oregon

Code Table Maintenance

Hello,

I am working on the logical model for a database. I need to use a number of code tables (tables that keep typically name value pairs. I need maintain data like products, services etc).
I am wondering if I increase the abstraction and use one table to represent the name value pairs but use a category to identify each type. Is there is any value in doing this?

The advantage with this I think is consolidating the data and probably minimizing the administration
The disadvantages may be too many joins that need to be qualified by the category type. Also, I may end up having too many self-joins.

Any suggestions???Personally I like this approach because then I don't have a bunch of hash tables scattered around the database. Adding new groups of name value pairs becomes a lot easier.

I haven't found the need to perform self-joins, but yes the large number of joins to the same table tends to be a pain. But, you'd still have to have the joins regardless (just to different tables).

Honestly, I am not sure of the performance benefits. But I think the "compactness" of the solution has value.

My 2 cents, maybe only 1 cent.
Terri

Code suggestions for database searches

I have a database containing several tables with many different fields. I need to create an admin section that lets me search on one field or the combination of several. Does anyone have links to pages that offer a general overview for inhouse database search strategy and admin edits.

Thank you

>> I need to create an admin section that lets me search on one field or the combination of several.

Do you means within a given table or across all tables?

>> database search strategy and admin edits

If across all tables, what about validation? A generic edit solution would bypasss data validation checks - not a good idea.

|||I need it across several tables and I am experimenting with the Multi_View control because although it only displays one view at a time all controls are accessible because the Views do not function as seperate containers. So far it seems to be meeting the major requirements however the displays are a little hard to figure out.|||

Have you considered how to handle data validation?

|||

I am doing that using Validation controls on the database submission form and in the View Edit template. At least I expect Validation will work in the Views.

Code Statistics

Hi !
I'm looking for a tool that can gather "statistics" of T-SQL code (SP,
triggers, UDF, Views)
By the word "statistics" I mean total number of code lines, number of
comment lines, number of constatant expression assignments (e.g. SET
@.A='Monday' or SET @.b=25)
Does anybody know such a tool or might advise some other solution to
get this information (but not too hard, like writing parcers)
Thank you in advance,
Alexey KudinovHi Alex
U can make use of sysobjects table or sp_help stored procedure.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Alex Kudinov" wrote:

> Community Message Not Available