Addition to the above.

I forgot to add, you'll see that ZONE has less records than STATE, thats
because the current system is storing NAMES not keys, and a ZONE name can be
in more than on STATE, e.g. STATE:"New York" can have a zone called
ZONE:"AGRICULTURE" and STATE:"California" can have a zone called
ZONE:"AGRICULTURE"Sorry, another thing... the record counts in the first post are DISTINCT
counts...

Addition of strings

How can i make a sum (concatenation) of strings of one column in a table.

for example i have a table like this

field1 field2

1 abc

1 bcd

2 sdf

2 sdd

I want to get these strings added group by field 1

Thanks

This cannot be done in a single query unless you employ some tricky hack. There is no obvious and reliable solution to do this in a single query. You'll have to use a cursor and a table variable for this purpose.

|||

Hi,

with .Net 2.0 it is very easy. You can write "User defined aggregate" called for example StringConcat and use it in your queries like this

SELECT dbo.StringConcat(field2)FROM MyTableGROUP BY field1

and here is the code for your aggregate

C#

using System;using System.Data;using System.Data.SqlClient;using System.Data.SqlTypes;using Microsoft.SqlServer.Server;using System.Text;using System.IO;using System.Runtime.InteropServices;[Serializable][SqlUserDefinedAggregate( Format.UserDefined,// user-defined serialization IsInvariantToDuplicates =false,// duplicates matter IsInvariantToNulls =true,// don't care about NULLs IsInvariantToOrder =false,// order matters (ignored) IsNullIfEmpty =false,// don't yield NULL if empty set MaxByteSize = 8000)]// maximum size in bytespublic struct StringConcat : IBinarySerialize{private StringBuilder sb;public void Init() {this.sb =new StringBuilder(); }public void Accumulate(SqlString s) {if (s.IsNull) {return;// skip NULLs }else {this.sb.Append(s.Value); } }public void Merge(StringConcat Group) {this.sb.Append(Group.sb); }public SqlString Terminate() {return new SqlString(this.sb.ToString()); }public void Read(BinaryReader r) { sb =new StringBuilder(r.ReadString()); }public void Write(BinaryWriter w) {if (this.sb.Length > 4000)// limit sb to 8000 bytes w.Write(this.sb.ToString().Substring(0, 4000));else w.Write(this.sb.ToString()); }}// end StringConcat

VB

Imports SystemImports System.DataImports System.Data.SqlTypesImports Microsoft.SqlServer.ServerImports System.TextImports System.IOImports System.Runtime.InteropServices' user-defined serialization' duplicates matter' don't care about NULLs' order matters (ignored) ' don't yield NULL if empty set' maximum size in bytes is 8000<Serializable(), _ SqlUserDefinedAggregate( _ Format.UserDefined, _ IsInvariantToDuplicates:=False, _ IsInvariantToNulls:=True, _ IsInvariantToOrder:=False, _ IsNullIfEmpty:=False, _ MaxByteSize:=8000)> _Public Structure StringConcatImplements IBinarySerializePrivate sbAs StringBuilderPublic Sub Init()Me.sb =New StringBuilder()End Sub Public Sub Accumulate(ByVal sAs SqlString)If s.IsNullThen Return' skip NULLsElse Me.sb.Append(s.Value)End If End Sub Public Sub Merge(ByVal GroupAs StringConcat)Me.sb.Append(Group.sb)End Sub Public Function Terminate()As SqlStringReturn New SqlString(sb.ToString())End Function Public Sub Read(ByVal rAs BinaryReader) _Implements IBinarySerialize.Read sb =New StringBuilder(r.ReadString())End Sub Public Sub Write(ByVal wAs BinaryWriter) _Implements IBinarySerialize.WriteIf Me.sb.Length > 4000Then' limit sb to 8000 bytes w.Write(Me.sb.ToString().Substring(0, 4000))Else w.Write(Me.sb.ToString())End If End SubEnd Structure' end StringConcat

I hope this helps.

Let me know if this worked for you.

|||

thanks, But I am looking for something within the sql

|||

Hi,

After compiling the class into a DLL, you can import the DLL as a SQL Server assembly using either the Visual Studio 2005 Deploy option or manually using the CREATE ASSEMBLY statement and CREATE AGGREGATE statement as is shown in the following listing:

 
CREATE ASSEMBLY StringConcatFROM'C:\StringConcat.dll'GOCREATE AGGREGATE StringConcat(@.inputnvarchar)RETURNSnvarcharEXTERNALNAME StringConcat.StringConcatGO
 

Using CLR objects (functions, aggregates...) for string manipulations or complex calculations is way faster than using nested queries or Cursors.

I have used this aggregate in my projects and it works fine.

|||

Hi, jiju-kj-

Of course it is possible to achieve this within the SQL. But you will need nested cursors the first cursor will iterate through distinct values in field1 and the inner cursor will iterate through all values from field2 for the current value in field1. I don't recommend you using cursors. But will decide which way is better for you. Let me know if you have problems with creating the cursors.

Cheers,

Paul

sql

Addition of article in Runnung MERGE Replication

Can any help me addition of article in runnning merge replication in MSSQL SERVER 2000 with SP3.
Any step by step help is realy great for me.
Thanks
R.MallSp_addMergeArticle is one system procedure, which may be workable, but I don't know detail step by step methods for same.

Addition of a number to an INT column

Hi,
I have several INT columns in a table that I need to update.
For example, in column 'aa' I need to add 2 to all of the values in that column.
I'm using Query Analyzer - what update statement should I write?Hmm - no answers - should I be in a different forum for this question?

I guess I could do:

UPDATE table_name SET aa = 16 WHERE aa = 14
UPDATE table_name SET aa = 15 WHERE aa = 13
UPDATE table_name SET aa = 14 WHERE aa = 12

and keep going like this until I have all the values updated.

Note that I've done it from highest number first, otherwise all of the data would get adjusted to the two highest numbers.

I was looking for a more elegant solution If anyone can think of one as I have several columns to update all with slightly different increases.|||UPDATE table_name SET aa = aa + 2

:D|||Of course - thank you - how could I miss it??

Woods for the trees and all...

Adding permissions to an AD user on a remote database

I was wondering if someone can point me in the right direction. I am lookin
g
for a way to automate (in a script format) the addition of a user to a
database on a remote server. We have an application that requires that the
user have Owner permissions on 3 databases and 1 store procedure. Manually
connectiing to 400 workstations will be a huge pain..I'm not sure what you mean by 400 workstations - if a login
needs access to databases and stored procedures, you would
set that at the server level in SQL Server and in whatever
databases.
Unless there is something in the application itself that has
to be coded which would be an application issue
If you are just trying to add the login and user to the
databases, you can script these using t-sql commands. See
books online topics for sp_grantlogin, sp_grantdbaccess,
sp_addrolemember.
-Sue
On Mon, 27 Feb 2006 13:11:27 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:

>I was wondering if someone can point me in the right direction. I am looki
ng
>for a way to automate (in a script format) the addition of a user to a
>database on a remote server. We have an application that requires that the
>user have Owner permissions on 3 databases and 1 store procedure. Manually
>connectiing to 400 workstations will be a huge pain..|||Just to clarify... the 400 workstations have sql running on it for an
application that has an offline mode...
The previous post mentioned to use: sp_grantlogin, sp_grantdbaccess,
sp_addrolemember which got me to here <see working code below>
How can I verify or check to see a user is already a member of the role ONE
database?
Example: If I am trying to add a user to the 'db_owner' role on the DB1
database and they are already listed as a member I would like to skip the
sp_addrolemember command.
This is all I could find:
IF SUSER_SID('domain\userid') IS NULL begin
but this only seems to find out if the user has a sid, not if the user is
listed in the DB1 database as a db_owner
Any help would be greatly appreciated.
This is my code so far...
'++++++++++++++++++++++++++++++++++++++
Domain = "DDDDDDDD"
Userid = "HHHHHHHH"
RemoteSQL = "WWW"
full_login = domain & "\" & userid
strconn = "Provider='SQLOLEDB'; Data Source='"&remotesql&"'; Initial
Catalog='master'; User Id='XXXXXX'; Password='''';"
Set conn = CreateObject("adodb.connection")
conn.Open strconn
m = m & "USE DB0" & vbCrLf
m = m & "EXEC sp_grantlogin '"& full_login &"'" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "USE DB1" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
m = m & "USE DB2" & vbCrLf
m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
vbCrLf
m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
'for the store procedure
m = m & "Use DB3" & vbCrLf
m = m & "GRANT EXECUTE ON CustOrdersOrders TO ["& full_login &"]" & vbCr
Lf
conn.execute(m)
Conn.close
set conn = nothing
"Sue Hoegemeier" wrote:

> I'm not sure what you mean by 400 workstations - if a login
> needs access to databases and stored procedures, you would
> set that at the server level in SQL Server and in whatever
> databases.
> Unless there is something in the application itself that has
> to be coded which would be an application issue
> If you are just trying to add the login and user to the
> databases, you can script these using t-sql commands. See
> books online topics for sp_grantlogin, sp_grantdbaccess,
> sp_addrolemember.
> -Sue
> On Mon, 27 Feb 2006 13:11:27 -0800, Chad T
> <ChadT@.discussions.microsoft.com> wrote:
>
>|||So the last piece you are looking for is the IS_MEMBER
function. That will tell you if a user is a member of the
specified database role.
-Sue
On Mon, 27 Feb 2006 20:47:26 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Just to clarify... the 400 workstations have sql running on it for an
>application that has an offline mode...
>The previous post mentioned to use: sp_grantlogin, sp_grantdbaccess,
>sp_addrolemember which got me to here <see working code below>
>How can I verify or check to see a user is already a member of the role ONE
>database?
>Example: If I am trying to add a user to the 'db_owner' role on the DB1
>database and they are already listed as a member I would like to skip the
>sp_addrolemember command.
>This is all I could find:
>IF SUSER_SID('domain\userid') IS NULL begin
>but this only seems to find out if the user has a sid, not if the user is
>listed in the DB1 database as a db_owner
>Any help would be greatly appreciated.
>
>This is my code so far...
>'++++++++++++++++++++++++++++++++++++++
>Domain = "DDDDDDDD"
>Userid = "HHHHHHHH"
>RemoteSQL = "WWW"
>full_login = domain & "\" & userid
>strconn = "Provider='SQLOLEDB'; Data Source='"&remotesql&"'; Initial
>Catalog='master'; User Id='XXXXXX'; Password='''';"
>Set conn = CreateObject("adodb.connection")
>conn.Open strconn
>m = m & "USE DB0" & vbCrLf
>m = m & "EXEC sp_grantlogin '"& full_login &"'" & vbCrLf
>m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
>vbCrLf
>m = m & "USE DB1" & vbCrLf
>m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
>vbCrLf
>m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
>m = m & "USE DB2" & vbCrLf
>m = m & "EXEC sp_grantdbaccess '"& full_login &"', '"& full_login &"'" &
>vbCrLf
>m = m & "EXEC sp_addrolemember 'db_owner', '"& full_login &"'" & vbCrLf
>'for the store procedure
>m = m & "Use DB3" & vbCrLf
>m = m & "GRANT EXECUTE ON CustOrdersOrders TO ["& full_login &"]" & vbC
rLf
>conn.execute(m)
>Conn.close
>set conn = nothing
>"Sue Hoegemeier" wrote:
>|||I am having issues with IS_Member...
"Indicates whether the current user is a member of the specified Microsoft
Windows group or Microsoft SQL Server database role. "
I did find this:
sp_helplogins 'domain\userid'
The problem with this is that the records that I want are in the second
record set.
Is there a way I can loop through the second record set instead of the first
one?
"Sue Hoegemeier" wrote:

> So the last piece you are looking for is the IS_MEMBER
> function. That will tell you if a user is a member of the
> specified database role.
> -Sue
> On Mon, 27 Feb 2006 20:47:26 -0800, Chad T
> <ChadT@.discussions.microsoft.com> wrote:
>
>|||Okay...so you actually want to know if the login exists -
not if it's a member of a database role. You can check if
the login exists before you add it using something like:
if not exists (select * from master.dbo.syslogins where
loginname = N'domain\userid')
exec sp_grantlogin N'domain\userid'
-Sue
On Tue, 28 Feb 2006 21:42:26 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I am having issues with IS_Member...
>"Indicates whether the current user is a member of the specified Microsoft
>Windows group or Microsoft SQL Server database role. "
>I did find this:
>sp_helplogins 'domain\userid'
>The problem with this is that the records that I want are in the second
>record set.
>Is there a way I can loop through the second record set instead of the firs
t
>one?
>
>"Sue Hoegemeier" wrote:
>|||Thank you so much for your help Sue.
I was looking at the second record set for "sp_helplogins" and it has
multiple columns (Login Name, DB Name, UserName, UserorAlias) It appears
that when I run it the columns tell me: (UserID, Database Name, Permission
Type, Member/User)
Is that right?
"Sue Hoegemeier" wrote:

> Okay...so you actually want to know if the login exists -
> not if it's a member of a database role. You can check if
> the login exists before you add it using something like:
> if not exists (select * from master.dbo.syslogins where
> loginname = N'domain\userid')
> exec sp_grantlogin N'domain\userid'
> -Sue
> On Tue, 28 Feb 2006 21:42:26 -0800, Chad T
> <ChadT@.discussions.microsoft.com> wrote:
>
>|||If you are executing this in query analyzer, there will be
two results in the results pane. You need to use the scroll
for the results pane to see the other set of results.
-Sue
On Thu, 2 Mar 2006 07:48:27 -0800, Chad T
<ChadT@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thank you so much for your help Sue.
>I was looking at the second record set for "sp_helplogins" and it has
>multiple columns (Login Name, DB Name, UserName, UserorAlias) It appears
>that when I run it the columns tell me: (UserID, Database Name, Permission
>Type, Member/User)
>Is that right?
>
>"Sue Hoegemeier" wrote:
>

Adding addition indexes on the subscriber tables

We have snapshot tables which are regenerated each night. Is there a way to
create additional indexes on the subscriber side since the vendor won't allow
us to create the indexes on the publisher?
Tom,
you could use a post-snapshot script for this.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||other than the excellent advice Paul has given you, you can use
sp_addscriptexec to create them on all subscribers deployed through a UNC.
Keep in mind that you want to minimize the indexes on your subscriber(s) to
improve performance of your replication solution.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uSx7mh6PFHA.3336@.TK2MSFTNGP09.phx.gbl...
> Tom,
> you could use a post-snapshot script for this.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Sp_addscriptexec works for transactional and merge, but not snapshot
Rgds,
Paul
|||stop making me look bad, Paul. I am perfectly capable of doing that on my
own.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com