Consolidate records

I consider security A to be equivalent to C so I want to consolidate these
positions. Three scenarios:
If an account holds both A and C I want to add the C quantity to the A
position and delete the C position.
If the account holds A but not C then no action.
If the account holds C but not A I want to update the C position to A.
My requirements are such that I need UPDATE and DELETE statement not a
SELECT statement so that my desired results would be produced by SELECT *
FROM #Positions
Any help would be appreciated.
CREATE TABLE [dbo].[#Positions] (
[AccountID] [int] NOT NULL ,
[SecurityID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Quantity] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'A',20)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'B',25)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'C',25)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'A',15)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'B',5)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'B',5)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'C',10)
INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (4,'C',15)
Expected results
1,A,45
1,B,25
2,A,15
2,B,5
3,A,10
3,B,5
4,A,15Please explain NULL quantity to me; I understand >=0, but not a NULL.
Create a synonym table,then create a VIEW with the base name and totals
and use it.|||Terri
The following UPDATE/DELETE statemenst should help you out:
-- Update SecurityID A with the values for SecurityID C where the AccountID
is the same
UPDATE a
SET a.quantity = a.quantity + b.quantity
FROM #Positions a JOIN #Positions b
ON a.accountid = b.accountid
AND a.securityid = 'A'
AND b.securityid = 'C'
-- Delete any of the SecurityID C rows that were used in the previous update
DELETE b
FROM #Positions a JOIN #Positions b
ON a.accountid = b.accountid
AND a.securityid = 'A'
AND b.securityid = 'C'
-- Update SecurityID C to SecurityID A
UPDATE #Positions
SET securityid = 'A'
WHERE securityid = 'C'
- Peter Ward
WARDY IT Solutions
"Terri" wrote:

> I consider security A to be equivalent to C so I want to consolidate these
> positions. Three scenarios:
> If an account holds both A and C I want to add the C quantity to the A
> position and delete the C position.
> If the account holds A but not C then no action.
> If the account holds C but not A I want to update the C position to A.
> My requirements are such that I need UPDATE and DELETE statement not a
> SELECT statement so that my desired results would be produced by SELECT *
> FROM #Positions
> Any help would be appreciated.
> CREATE TABLE [dbo].[#Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'A',20)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'B',25)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (1,'C',25)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'A',15)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (2,'B',5)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'B',5)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (3,'C',10)
> INSERT INTO #Positions (AccountID,SecurityID,Quantity) VALUES (4,'C',15)
> Expected results
> 1,A,45
> 1,B,25
> 2,A,15
> 2,B,5
> 3,A,10
> 3,B,5
> 4,A,15
>
>sqlsql

Consolidate multiple databases via merge replication

Hi
I am trying to merge a set of tables from 5 separate databases (4 on
separate servers and 2 on the same server) into one 'master' database.
The tables being replicated all have unique ID ranges
How to I organise the replication so that the subscriber database can allow
inserts which fit into the ID ranges for the related Publishers database.
ie All inserts with a Lab_ID = 'L' should ONLY be replicated back to the
associated publisher.
I am not familiar with the automatic identity range handling .. can anyone
point me to an article about how this works ?
any help would be appreciated
cheers
mike
Mike,
as you've got multiple publishers, just ordinary filters will work to
restrict the flow of data to each publisher. For identity range handling,
you'll need to design each publisher's range yourself. The subscriber table
can be created during the first initialization, and all others are nosync
ones. So, the subscriber's identity range is defined after the first
initialization, and subsequent publications need to be sure not to overlap
with this range. Make the ranges large, because assignment of another range
might cause an overlap, so if the range is so big there will never be any
need to reset then this is best.
Alternatively, all subscriptions can be nosync ones.
These might help:
http://www.replicationanswers.com/No...alizations.asp
http://www.replicationanswers.com/ManualIdentities.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Console.WriteLine generates HostProtectionException

Gotcha!

It took a few minutes of staring at my CLR method (it's actually a ServiceBroker service), trying to figure out why I was getting an exception on something that looked pretty innocuous.

It turned out to be the Console.WriteLine(...) statement. In hindsight, not really much of a surprise . However, for debugging purposes, I'd still like to use Console.WriteLine. Is there a HostProtectionAttribute I can apply that will allow it?

Josh

Yes, you are right, Console.WriteLine is not the appropiate output for debugging in SQL Server. What about using any trace source or even to keep it simple the System.Diagnostics namespace ? This one has some method for the output during the debbuging process.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Console apps work for SA but no other user

Hi

My console applications work forSA and no other user. I can run the Stored procedures used in the console application from Query analyser when logged in with username/password that I am attempting to use for console applications. I am using SQL server authenication. User access permissions look ok in Enterprise Manager. Access is permit for my user.

Any suggestions?

Thanks

Permissions in SQL are much more than just access permit. You should also grant EXECUTE permssion for a stored procedure to a user if you want the user to execute the stored procedure; or you can create a role and add the user as member, then grant proper permissions to the role. To understand permissions related concepts in SQL, you can start from here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_03_8q2b.asp

sqlsql

console apps only work if user is SA

Hi

When I try to use a user other then SA my console apps don't work.

I can run the Stored Procedures used in console application from Query analyser when logged in with the username/password that
I'm attempting to use for the console applications.

Under Users in Enterprise Manager Database access is 'permit' for my user.

By the way my web application which uses the same user name and password as in console applications is working. I also have dts packages running using dtsexec accessing the database with the same user name and password and they work fine.

MDAC 2.8 SP2 on windows server 2003 spi

C:\Program Files\Microsoft SQL Server\80\Tools\Binn>ODBCPING.EXE -S xxx.xxx.xxx.xxx
-U myusername -P mypassword

CONNECTED TO SQL SERVER

ODBC SQL Server Driver Version: 03.86.1830

SQL Server Version: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

If the connection is available then why is the adapter.fill method failing?

I tried it using a text sql statement and that doesn't work either.

The problem isn't database specific as I did a test .bat on Northwind sample database and got same 'general network error'

Here's the error:

apps\Exports>exporter.bat

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.ConnectionPool.CreateConnection()
at System.Data.SqlClient.ConnectionPool.UserCreateRequest()
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransactio
n)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, Conne
ctionState& originalState)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startR
ecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior be
havior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)

//Testing using SELECT statement as command text

D\Test>Test.bat

D:\Test>TestDBAccess.exe "server=xxx.xxx.xxx.xxx;uid=xxxx;pwd=xxx;
database=Northwind;"

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.ConnectionPool.CreateConnection()
at System.Data.SqlClient.ConnectionPool.UserCreateRequest()
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransactio
n)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConn
ectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()

D:\Test>

Any ideas/help much appreciated!

Hi,

would be cool if you could show us your code.Sometimes people hardcode certain properties (I confess that I did that on my own one time :-) ) which will lead to an error where usally there shouldn′t be an error (especially in console apps where connection properties are passed via arguments which isn′t testable while debugging within VS)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

Here's the essentials of the code (without some try catch statements).

Thanks.

KH

.bat file

::arg 0 Connection String
::arg 1 Export Path //just a path where I want the output file to go

Exporter.exe "server=xxx.xxx.xxx.xxx;uid=username;pwd=password;database=DATABASENAME;" "d:\\xxx\\xxx\\ExportOut\\"

Source code

using System;

using System.Data;
using System.Security;
using System.Security.Permissions;
using System.Security.Policy;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;

namespace Exporter
{
class Export
{

private static String ConnectionString;

private static String ExportPath;

private static StreamWriter ExportLog;

[STAThread]
static void Main(string[] args)
{

ConnectionString = args[0];

ExportPath = args[1];

String DateString = System.DateTime.Today.Day.ToString() + "_" + System.DateTime.Today.Month.ToString();

ExportLog = new StreamWriter(ExportPath+"Export_Log_"+DateString+".txt");

DoExport();

ExportLog.Close();

}

private static void DoExport()
{

ExportLog.WriteLine("Beginning export");
GetData());

}

private static void GetData()
{

SqlCommand SelectCommand = new SqlCommand();

SelectCommand.CommandType=(System.Data.CommandType.StoredProcedure);

SelectCommand.CommandText="GetCSVOutput";

SqlConnection Conn = new SqlConnection(ConnectionString);

SelectCommand.Connection=Conn;

SqlDataAdapter ReportAdapter = new SqlDataAdapter();

FaultReportAdapter.SelectCommand=SelectCommand;

DataSet ReportData = new DataSet();

ReportAdapter.Fill(ReportData,"Report");

if (ReportData.Tables["Report"].Rows.Count == 0)
{
Conn.Close();
ExportLog.WriteLine("No reports to export");
return false;

}
else
{
Conn.Close();
return MakeFile(ReportData);

}

}

private static void MakeFile(DataSet FaultReportData)
{

/* Just prints out the results of Stored procedure to file and closes file */

}

|||Hi,

beside that the FaultReportDapater doesn′t exists (but I guess this is just a typo) you can try disabling the connection pool to see if it is based on this with adding the keywords "Pooling=False" to the connecting string.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Hi,

Yes that was just a typo. With pooling set to false I still get the same error.

Thanks

KH

|||

What is the call stack of the exception if you disable pooling?

|||

Here's the call stack with pooling set to false.

D:\apps\Export>exporter.bat

D:\content\apps>Exporter.exe "server=xxx.xxx.xxx.xxx;uid=xxxx;pwd=;database=xxxx;pooling=False" "d:\\Content\\xxxx\\ExportOut\\"

Unhandled Exception: System.Data.SqlClient.SqlException: General network error.
Check your network documentation.
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection connection
, SqlConnectionString connectionOptions)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, Conne
ctionState& originalState)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startR
ecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior be
havior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at Exporter.Export.GetData() in
\\xxxx\dotnet_dll_production
\xxxx\xxxxx\export.cs:line 536
at Exporter.Export.DoExport() in
\\xxxx\dotnet_dll_production\xxxx\xxxx\export.cs:line 143
at Exporter.Export.Main(String[] args) in \\xxxx\dotnet_dll_produc
tion\xxxx\xxxx\export.cs:line 95

D:\apps\Export>

console application for retrieving a large amount of data

i need to retrieve a large amount of data from the sql server database and make changes to one field and put the data back using a console application. how do i do it?Well, there are lots of ways, probably the easiest being to retrievethe data as a dataset, make the changes, and update the database.
But let's start with this. Why do you need to retrieve a large amountof data to only change one field? Do you mean one field in all or mostof the records, or one field in one record?
If you're doing an update that affects many rows and don't really needto pull the data off the server, you might be able to use a SQL UPDATEstatement that updates the data on the database server, saving thenetwork traffic of pulling it down to the client.
Tell us more about what you want to do and we'll try to help.
Don
|||well, i have to retrieve 3 million records from the sql server database. i need to encrypt one field present in all records and put that encrypted field back into the db.
thanks.|||Okay. Is this a one-time thing or will you be doing it regularly? If aone-time thing then you probably don't care too much about performanceor being inefficient with regards to memory usage. So I would probablypull down the data into a dataset, probably in ranges of data usingsome field that is reasonably well-distributed, make the changes, andthen update the database.
For example, if the data had a last name field, you could do it forlast names that begin with A to E, then F to M, and so on. Or whateverranges make sense. And bring down ONLY the data you need, presumablythe one field with the data to be encrypted, and perhaps the secondfield that is the destination for the encrypted data. Or does theencrypted data go into a different table? Then you'll need to generatethe insert statements or use a second data table in the data set.
This is going to be horribly inefficient, however, so you won't want togo this route if this is anything but a one-time thing. If it'ssomething you'll need to do regularly, I'd try to find a way to do thisentierly on the server. In that case you could write a stored procedurethat uses OLE Automation (the sp_OA* system stored procedures) to dothe encryption. Since that uses COM it's going to have its ownperformance issues, but at least you're not slepping three million rowsof data to the client across the network.
Depending on exactly how you need to do this, there are plenty of other ways to get it done.
Don

Consoldating data across databases

Thanks in advance for your help,
Is there an easy way to "link" data from one SQL database to another, preferably within a view?
More specifically, can one "link" to data from 2 seperate databases to a 3rd without importing the data?
Thanks,
LeeSure, if they are on the same server then just use 3-part naming convention in your queries:

select t1.f2, t2.f2, t3.f2
from db1.dbo.t t1
inner join db2.dbo.t t2 on t1.f1=t2.f1
inner join db3.dbo.t t3 on t1.f1=t3.f1 and t2.f1=t3.f1

...and if on different servers then create linked servers and use 4-part naming convention by preceeding the database name with the alias of the server.|||Thanks so much! It was so easy that I am embarrased