Peer to Peer difficulty when adding a column

I'm trying to add a column to my peer to peer replication. According to the
help I've found:
Adding Columns
a.. To add a new column to a table and include that column in an existing
publication, execute ALTER TABLE <Table> ADD <Column>. By default, the
column is then replicated to all Subscribers. The column must allow NULL
values or include a default constraint.
b.. To add a new column to a table and not include that column in an
existing publication, disable the replication of schema changes, and then
execute ALTER TABLE <Table> ADD <Column>.
c.. To include an existing column in an existing publication, use
sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or
the Publication Properties - <Publication> dialog box.
For more information, see How to: Define and Modify a Column Filter
(Replication Transact-SQL Programming) and How to: Define and Modify a
Column Filter (SQL Server Management Studio). This will require
subscriptions to be reinitialized.
d.. Adding an identity column to a published table is not supported,
because it can result in non-convergence when the column is replicated to
the Subscriber. The values in the identity column at the Publisher depend on
the order in which the rows for the affected table are physically stored.
The rows might be stored differently at the Subscriber; therefore the value
for the identity column can be different for the same rows.
However, while I am able to add the column to the publisher and it is
replicated to the subscriber, I cannot update this column at the subscriber
and have it replicate to the publisher. However when I update at the
publisher it IS pushed to the subscriber.
Outside of the new column, my updates bi-directionally work. Can someone
tell me how I can get updates on newly added columns to be bi-directional?
Thanks,
Adam P. Cassidy
Did you quiesce the system as outlined in this link?
http://msdn2.microsoft.com/en-us/library/ms147385.aspx
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
news:eoBB5en4GHA.1200@.TK2MSFTNGP02.phx.gbl...
> I'm trying to add a column to my peer to peer replication. According to
> the help I've found:
> Adding Columns
> a.. To add a new column to a table and include that column in an existing
> publication, execute ALTER TABLE <Table> ADD <Column>. By default, the
> column is then replicated to all Subscribers. The column must allow NULL
> values or include a default constraint.
> b.. To add a new column to a table and not include that column in an
> existing publication, disable the replication of schema changes, and then
> execute ALTER TABLE <Table> ADD <Column>.
> c.. To include an existing column in an existing publication, use
> sp_articlecolumn (Transact-SQL), sp_mergearticlecolumn (Transact-SQL), or
> the Publication Properties - <Publication> dialog box.
> For more information, see How to: Define and Modify a Column Filter
> (Replication Transact-SQL Programming) and How to: Define and Modify a
> Column Filter (SQL Server Management Studio). This will require
> subscriptions to be reinitialized.
> d.. Adding an identity column to a published table is not supported,
> because it can result in non-convergence when the column is replicated to
> the Subscriber. The values in the identity column at the Publisher depend
> on the order in which the rows for the affected table are physically
> stored. The rows might be stored differently at the Subscriber; therefore
> the value for the identity column can be different for the same rows.
> However, while I am able to add the column to the publisher and it is
> replicated to the subscriber, I cannot update this column at the
> subscriber and have it replicate to the publisher. However when I update
> at the publisher it IS pushed to the subscriber.
> Outside of the new column, my updates bi-directionally work. Can someone
> tell me how I can get updates on newly added columns to be bi-directional?
> Thanks,
> Adam P. Cassidy
>
|||Yes. That is the same article I used as a reference originally.
Adam P. Cassidy
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%234SYrOq4GHA.1188@.TK2MSFTNGP05.phx.gbl...
> Did you quiesce the system as outlined in this link?
> http://msdn2.microsoft.com/en-us/library/ms147385.aspx
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Adam Patrick Cassidy" <apcassidy@.aol.com> wrote in message
> news:eoBB5en4GHA.1200@.TK2MSFTNGP02.phx.gbl...
>

Peculiar problem with BCP data load

I have table in database with total column length about
225 bytes and have about 2.7 mil rows. When the data is
loaded using bcp some days it takes up more then 8 to 16
GB and other days it takes about 800 MB. Not consistent
as to how it behaves on any give days.
It's a row table with no index no primary key. I see from
statistics that on whole page only one to two rows are
stored and rest of the space is free. Any one has seen
this issue before.
DBCC SHOWCONTIG scanning 'PSN' table...
Table: 'PSN' (548197003); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1003989
- Extents Scanned.......................: 126702
- Extent Switches.......................: 126701
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.05%
[125499:126702]
- Extent Scan Fragmentation ...............: 2.61%
- Avg. Bytes Free per Page................: 7441.4
- Avg. Page Density (full)................: 8.06%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
There are many solutions to make it work. But just curious
as to what is causing this. Any pointers are greatly
appreciated.
Regards,
Don
Try creating a clustered index on the table, preferably one that is in order
of the data to some degree.
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:0c2c01c4abba$f0313540$a401280a@.phx.gbl...
> I have table in database with total column length about
> 225 bytes and have about 2.7 mil rows. When the data is
> loaded using bcp some days it takes up more then 8 to 16
> GB and other days it takes about 800 MB. Not consistent
> as to how it behaves on any give days.
> It's a row table with no index no primary key. I see from
> statistics that on whole page only one to two rows are
> stored and rest of the space is free. Any one has seen
> this issue before.
> DBCC SHOWCONTIG scanning 'PSN' table...
> Table: 'PSN' (548197003); index ID: 0, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1003989
> - Extents Scanned.......................: 126702
> - Extent Switches.......................: 126701
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.05%
> [125499:126702]
> - Extent Scan Fragmentation ...............: 2.61%
> - Avg. Bytes Free per Page................: 7441.4
> - Avg. Page Density (full)................: 8.06%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> There are many solutions to make it work. But just curious
> as to what is causing this. Any pointers are greatly
> appreciated.
> Regards,
> Don
>

Peculiar problem with BCP data load

I have table in database with total column length about
225 bytes and have about 2.7 mil rows. When the data is
loaded using bcp some days it takes up more then 8 to 16
GB and other days it takes about 800 MB. Not consistent
as to how it behaves on any give days.
It's a row table with no index no primary key. I see from
statistics that on whole page only one to two rows are
stored and rest of the space is free. Any one has seen
this issue before.
DBCC SHOWCONTIG scanning 'PSN' table...
Table: 'PSN' (548197003); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 1003989
- Extents Scanned.......................: 126702
- Extent Switches.......................: 126701
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.05%
[125499:126702]
- Extent Scan Fragmentation ...............: 2.61%
- Avg. Bytes Free per Page................: 7441.4
- Avg. Page Density (full)................: 8.06%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
There are many solutions to make it work. But just curious
as to what is causing this. Any pointers are greatly
appreciated.
Regards,
DonTry creating a clustered index on the table, preferably one that is in order
of the data to some degree.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:0c2c01c4abba$f0313540$a401280a@.phx.gbl...
> I have table in database with total column length about
> 225 bytes and have about 2.7 mil rows. When the data is
> loaded using bcp some days it takes up more then 8 to 16
> GB and other days it takes about 800 MB. Not consistent
> as to how it behaves on any give days.
> It's a row table with no index no primary key. I see from
> statistics that on whole page only one to two rows are
> stored and rest of the space is free. Any one has seen
> this issue before.
> DBCC SHOWCONTIG scanning 'PSN' table...
> Table: 'PSN' (548197003); index ID: 0, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 1003989
> - Extents Scanned.......................: 126702
> - Extent Switches.......................: 126701
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.05%
> [125499:126702]
> - Extent Scan Fragmentation ...............: 2.61%
> - Avg. Bytes Free per Page................: 7441.4
> - Avg. Page Density (full)................: 8.06%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> There are many solutions to make it work. But just curious
> as to what is causing this. Any pointers are greatly
> appreciated.
> Regards,
> Don
>

pdf-files in full-text-search

Hallo, I tryed to add pdf-files that are stored in a image-column to
full-text-search. As mime-type in the "data type column" I used
"application/pdf". So, I can see at the "unique key count" that the
files are read from the index but I can't find anything in the files by
searching with contains(). Whats wrong? Is the data type not the right.
Is there a generally problem with pdf? Thanks in advance, Silvio
Hi Hilary, thanks for your help. I still found the right way to do this
here: http://weblogs.asp.net/wallym/archiv...28/382060.aspx
Now it works fine.

PDF Full Text Search

Hi all,

I have a table configured to support Full-Text Search and in this tables a column stores documents of three types, Word, Excel and PDF. Everything works fine with Word and Excel documents.

In order to search PDF documents I've installed "IFilter 5.0", which is published in Acrobat's page, but nothing. What else should I do, is there any other solution?

I've read that I can create a particular filter, so the question is where can find information about creating customized filters? But first, is it a good business to create a new filter?

Thanks in advance.Dear mvargasp,
I have a problem about FTS. I think you could help me...
I wrote to forum but nobody reply to me...

I need your help,
Thanks alot
Yusuf
--------
I've a problem. I'm inexperienced about FTS.
I have a TEXT datatype field in my table and I store MS Word documents.

I want to do full text search on this TEXT datatype field.
So I build catalog and run

"Select * from table where Contains (textfield,'Yusuf')" sql in analyzer.

but null value returns. however I know the table has 3 records.

Anyway, I insert a varchar field in the table and edit my fts catalog.
and then insert 3 records in the table.than rebuild catalog and I try
"Select * from table where Contains (varcharfield,'Yusuf')"
this sql returns true records.

I know that I can FTS in TEXT datatype field but I can't. I don't know why.
I need your help.
thanks alot

Yusuf|||Hi Yusuf,

It seems that you have created a catalog and rebuild it then, but you have not populate it. So What you must do after you create or rebuild a catalog is to populate it. I order to do so, you must right click your catalog in the Enterprise Manager and then click Populate.

After this the FTS must work.

God Bless.

PC SQL Server table (with a column classified "unique") synchronizes (using merge) wit

Hi, I have a doubt about the behaviour of SQL Server 2005 in the situation I'm going to describe you.
Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column).
Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs.
Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC).
At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...

WHAT HAPPENS? Does an error raise?
Must you give a publication setting in which you say that, if this situation occurs, PC SQL Server holds the last (or the first, as you decide) record acquired? Is it possible?

Thank you very much

Of the two subscribers, the 1st will successfully sync with the publisher. The 2nd subscriber will then try to upload their change, and it should fail due to constraint violation. So you now have one conflict.

If the row at the publisher needs to be downloaded to the subscriber, this will also fail due to constraint violation. So you now have two conflicts, one from the upload, one from the download.

With these types of insert conflicts, you have to manually resolve the problem: you can delete the row at the 2nd subscriber, update the row at the publisher or 2nd subscriber so column contains a new unique value.

PC SQL Server table (with a column classified "unique") synchronizes (using merge)

Hi, I have a doubt about the behaviour of SQL Server 2005 in the situation I'm going to describe you.
Suppose that you have a SQL Server 2005 database on your PC, and suppose that this database has a table with a column classified as "unique" (so it's impossible for this table to contain 2 records having the same value in this column).
Suppose that you publish this database and you create 2 SQL Server Mobile 2005 subscriptions on 2 Pocket PCs.
Suppose now that the first PPC (using an embedded program) creates a record with a certain value for the column (and adds it to the table), and the second PPC makes the same thing (it inserts a record with the same column value of the first PPC).
At this point, you connect the 2 PPCs to your PC (one by one, of course), to synchronize (using merge replication) the databases...

WHAT HAPPENS? Does an error raise?
Must you give a publication setting in which you say that, if this situation occurs, PC SQL Server holds the last (or the first, as you decide) record acquired? Is it possible?

Thank you very much

Of the two subscribers, the 1st will successfully sync with the publisher. The 2nd subscriber will then try to upload their change, and it should fail due to constraint violation. So you now have one conflict.

If the row at the publisher needs to be downloaded to the subscriber, this will also fail due to constraint violation. So you now have two conflicts, one from the upload, one from the download.

With these types of insert conflicts, you have to manually resolve the problem: you can delete the row at the 2nd subscriber, update the row at the publisher or 2nd subscriber so column contains a new unique value.

Pattern matching with multiple values

I want to check for multiple patterns in a particular column.
For one pattern I can write e.g.
SELECT * FROM <TablName> WHERE ColumnName LIKE '%abcd%'.

My requirment is to select all rows for which column value matches with many patterns.I will fetch the patterns in a subquery
e.g. (SELECT '%'+name+'%' FROM <TableName>)

Any thoughts?

Hi
It might work if you move your like into Exists , like

SELECT * FROM <TableName> T1
WHERE EXISTS (SELECT * FROM T2
WHERE T1.column LIKE '%' + T2.NAME + '%'
)

NB.|||

Thanks for your solution !

|||

Thanks for your solution !

However my problem won't get solved this way. Actually I am building anSSRS report and the subquery was meant to be the 'Available Values ' of a multi-valued Input parameter.
When the user selects one or more params SRS will form a string of the values like " 'val1','val2','val3'...."
In my case SRS is forming a WHERE clause like
.........
.........
WHERE colname in ('val1','val2','val3'). This would search for the exact string, whereas I would like it to only match with patterns like '%val1%','%val2%','%val3%'.

|||

i think you need to build it as

WHERE colname like '%val1%' or colname like '%val2%' or colname like '%val3%'.

|||

Thnx. Shallu.....That is correct syntax, but you need to see that the number of values is not static and all of them are stored as one parameter by ssrs.

To simplify it for you, say SSRS is providing me with a string series like " '%val2%', '%val2%' ,'%val2%' ...". Now I need to use this to do my comparison.

|||Hi

There might be a better solution from SSRS comunity , you should try your question there as well. From T_SQL point of view you could try following solution:
Create 2 store procedures : one - master that recived your string of parameters , parses a string into a list of single parameters and collects whatever children return , the other one - child , that can process one parameter at a time

CREATE PROCEDURE MultipleParameterSearch_procedure
@.intString varchar ()

as
CREATE TABLE #reultset (<your columns>)

DECLARE @.PARAMETER
-- parse your string into separate parameters , one at a time
...
...

WHILE @.PARAMETER is not nul
BEGIN
-- accumulate your single parameter procedure results
INSERT INTO #reultset (<your columns>)
EXEC SingleParameterSeach_Procedure @.PARAMETER
-- get next parameter
END

SELECT * FROM #reultset
RETURN
GO

CREATE PROCEDURE SingleParameterSeach_Procedure @.Parameter VARCHAR()
AS
SELECT <your columns>FROM <Table> WHERE Column LIKE '%' + @.Parameter + '%'
RETURN
GO|||Hi
Just one last suggestion.
LIKE %val%' is a very expensive operation, as no indexes can be used to help QueryOptimiser to make a quick search. Adding multiple LIKE parameters are going to decrease performance of your query. If you are doing your Report for production , explain to the user implications and pesweid them to use a single-parameter select .

:-).NB|||Thanks for taking the pains .I'll follow this up |||

Please take a look at the link below:

http://www.sommarskog.se/arrays-in-sql.html

It discusses various techniques to process lists using SQL. You can use one of those methods to generate a table that contains the individual values and then use it as source in the EXISTS sub-query.

password storage

hai all!
i am going to store my user details to login from vb.net in a user table in sql.i would like to disguise the password column with dots or stars.how do i do that?kindly help.its urgent.i also need to compare the password right.so it should be readable by the program

Quote:

Originally Posted by pangsans

hai all!
i am going to store my user details to login from vb.net in a user table in sql.i would like to disguise the password column with dots or stars.how do i do that?kindly help.its urgent.i also need to compare the password right.so it should be readable by the program

i doubt if you can revert back to the original string from "dots or stars", what you could is to use an encrypting function...you may create your own or find one on the net|||

Quote:

Originally Posted by ck9663

i doubt if you can revert back to the original string from "dots or stars", what you could is to use an encrypting function...you may create your own or find one on the net

thanks yaar
give me some more info yaar|||That would depend on how you want to encrypt it. one way or 2 way?
and syntax will differ from language to language :)
cheers

Password column in a database table

I need to "mask" the password entries in a table. Access has a simple solution but I can not find a way to do this in SQL. This does not have to be encrypted, just hid
from the few users we have internally with SQL logins to the database. Any helpful tips or help files would be much appreciated!
No masking available. Hashing, encryption are at your disposal, but better
is you have the option of column level permissions or just simply create a
view without that column and provide these users access to that view.
That's exactly how it is supposed to be done.
hth
Eric
CRouse wrote:
> I need to "mask" the password entries in a table. Access has a
> simple solution but I can not find a way to do this in SQL. This does
> not have to be encrypted, just hid from the few users we have
> internally with SQL logins to the database. Any helpful tips or help
> files would be much appreciated!
|||In SQL Server 2000, you can apply column-level permissions or deny those
users select access to the table.
There is no "mask" functionality unless you actually encrypt or encode the
data.
http://www.aspfaq.com/
(Reverse address to reply.)
"CRouse" <CRouse@.discussions.microsoft.com> wrote in message
news:690C479A-498A-470E-B8F2-A8912A5D42EC@.microsoft.com...
> I need to "mask" the password entries in a table. Access has a simple
solution but I can not find a way to do this in SQL. This does not have to
be encrypted, just hid
> from the few users we have internally with SQL logins to the database.
Any helpful tips or help files would be much appreciated!

Password column in a database table

I need to "mask" the password entries in a table. Access has a simple solut
ion but I can not find a way to do this in SQL. This does not have to be enc
rypted, just hid
from the few users we have internally with SQL logins to the database. Any
helpful tips or help files would be much appreciated!No masking available. Hashing, encryption are at your disposal, but better
is you have the option of column level permissions or just simply create a
view without that column and provide these users access to that view.
That's exactly how it is supposed to be done.
hth
Eric
CRouse wrote:
> I need to "mask" the password entries in a table. Access has a
> simple solution but I can not find a way to do this in SQL. This does
> not have to be encrypted, just hid from the few users we have
> internally with SQL logins to the database. Any helpful tips or help
> files would be much appreciated!|||In SQL Server 2000, you can apply column-level permissions or deny those
users select access to the table.
There is no "mask" functionality unless you actually encrypt or encode the
data.
http://www.aspfaq.com/
(Reverse address to reply.)
"CRouse" <CRouse@.discussions.microsoft.com> wrote in message
news:690C479A-498A-470E-B8F2-A8912A5D42EC@.microsoft.com...
> I need to "mask" the password entries in a table. Access has a simple
solution but I can not find a way to do this in SQL. This does not have to
be encrypted, just hid
> from the few users we have internally with SQL logins to the database.
Any helpful tips or help files would be much appreciated!

password

Hi,
Is there any way to show one column(password) values as ***.
One of my table has password field, when you query the table it will give actual value, i wanted to show them as **** or with junk charecters.
FYI..column data type is varchar(50).
Thanks in advance.You could use Pwdencrypt and pwdcompare:

Pwdencrypt and pwdcompare are internal, undocumented functions that SQL Server uses to manage passwords. Pwdencrypt uses a one-way hash that takes a clear string and returns an encrypted version of that string. Pwdcompare compares an unencrypted string to its encrypted representation to see whether they match.

Script below shows how to save and compare passwords:

create table users(
id int identity,
username nvarchar(128) not null unique,
userpassword nvarchar(128) not null
)

insert users(username,userpassword)
select 'tom',pwdencrypt('tom2')

insert users(username,userpassword)
select 'brett',pwdencrypt('brett2')

select Id from users
where pwdcompare('tom2',userpassword)=1
and username='tom'

Id
----
1

(1 row(s) affected)

select Id from users
where pwdcompare('brett3',userpassword)=1
and username='brett'
Id
----

(0 row(s) affected)|||Thanks for your reply.
And is there any way to decrypt the password, if you loose your actual password?
Thanks|||Thanks for your reply.
And is there any way to decrypt the password, if you loose your actual password?
Thanks
Nope ;) just set up new one|||Yeah, the point is to encrypt the password before it is stored in your table. And if it could be decrypted, it wouldn't be very secure, right?

Actually, SQL Server's encryption method has been cracked, and decryption algorythms are available on the web. If you want more security, I have a one-way encryption function you are welcome to use.|||Any leads would be greatly appreciated!!
Thanks|||Here is my pasword encryption algorythm.

The other disadvantage of using SQL Server's PWD_ENCRYPT function is that Microsoft can and has changed the algorythm in subsequent releases of SQL Server, rendering all existing passwords useless.|||Just curious...what sql server security model are you using?

I'm always leary of Application level security that store passwords in the database.|||Mixed mode.
thanks

passing table/column name as parameter

I know passing table/column name as parameter to a stored procedure is
not good practice, but sometimes I need to do that occasionally. I
know there's a way can do that but forget how. Can someone refresh my
memory?
Thanks.
Saiyouwangc@.alexian.net (Saiyou Anh) wrote in message news:<a51d3ca7.0407271253.91fae7@.posting.google.com>...
> I know passing table/column name as parameter to a stored procedure is
> not good practice, but sometimes I need to do that occasionally. I
> know there's a way can do that but forget how. Can someone refresh my
> memory?
> Thanks.
> Saiyou

See here, which will also refresh your memory about why you shouldn't do this... :-)

http://www.sommarskog.se/dynamic_sql.html

Simon|||Saiyou Anh (wangc@.alexian.net) writes:
> I know passing table/column name as parameter to a stored procedure is
> not good practice, but sometimes I need to do that occasionally. I
> know there's a way can do that but forget how. Can someone refresh my
> memory?

All about it, including why shouldn't do it on
http://www.sommarskog.se/dynamic_sql.html

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

passing table name and order by parameter to stored procedure

can i pass the name of the table and the "order by" column name to stored procedure?

i tried the simple way

(@.tablename varchar and then "select * from @.tablename)

but i get error massesges. the same for order by...

what is the right syntex for this task?

You have to use dynamic SQL for this task, unfortunately. You have to create a character string in your sproc that puts together the SQL statement and then you have to call exec(@.sql) on it.

Example:

@.sql = 'SELECT * FROM ' + @.tableName

exec(@.sql)

|||

well, i just started to use stored procedure, i think that i will leave dynamic sql to later on...

Passing table Column name as parameter to a stored procedure

I want to run a Stored Procedure which takes Column name( table column names ) as input, use some aggregate function and return the desired output.

I have tried passing a single column name as input parameter and also the entire SQL statement as input parameter. but i am not able to capture and return the output value

I have also tried using Table data type but fail to capture the value. I dont want to use Temporary table.

The syntax i tried is some thing like this

Declare @.stmt nvarchar(100)
Declare @.AcctCode Char(8)
Declare @.rtVal numeric(18,5)

Set @.AcctCode = 'An_Sales'
Set @.stmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'
Exec sp_executesql @.rtval = @.stmt

And also

Declare @.AcctCode Char(8)
Declare @.Ssql NVarchar(100)
Declare @.rtVal numeric (18,5)
Set @.AcctCode = 'An_Sales'

Set @.Ssql = 'Select ' +@.rtval + '=AVG(an_sales) into From T_Comp_Profile'
Exec SP_ExecuteSql @.Ssql
print @.rtval

Pls help me in this regard

Ramanbir Singhtry something like...

Declare @.mystmt nvarchar(100)
Declare @.AcctCode Char(8)
Declare @.rtVal numeric(18,5)

Set @.AcctCode = 'An_Sales'
Set @.mystmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'
Exec sp_executesql @.stmt= @.mystmt|||Dear Rockslide

U have wriiten me with the following code

Declare @.mystmt nvarchar(100)
Declare @.AcctCode Char(8)
Declare @.rtVal numeric(18,5)

Set @.AcctCode = 'An_Sales'
Set @.mystmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'
Exec sp_executesql @.stmt= @.mystmt

The stt "Exec sp_executesql @.mystmt" this returns a data set, so it is not going to be stored in a variable like u specified i.e
Exec sp_executesql @.stmt= @.mystmt

because when we print the value of @.stmt using "Print @.stmt" it returns nothing also we cant use a table data type here in place of @.stmt|||hi rjaj

I think I am a little confused.

sp_executesql takes (basically) 2 different parameters, see the syntax below.

sp_executesql [@.stmt =] stmt
[
{, [@.params =] N'@.parameter_name data_type [,...n]' }
{, [@.param1 =] 'value1' [,...n] }
]

When we say

exec sp_executesql @.stmt=@.mystmt

we are effectively saying

exec sp_executesql @.stmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'

if we do nothing else with the returned results they will be outputed.

if you want the results to return to a parameter then you would need to do something like

select @.results = sp_executesql @.stmt = 'Select AVG(' + @.ACCTCODE + ') From T_Comp_Profile'

at a guess (the line above hasn't been tested, in theory I think it should work).|||Sounds familiar

http://www.dbforums.com/t970045.html|||create table #tbl ([output] int null)
insert #tbl Exec (@.stmt)|||Originally posted by ms_sql_dba
create table #tbl ([output] int null)
insert #tbl Exec (@.stmt)

Using temporary tables work
but i dont want to use a temporary table
Is there any other way for that

Passing second column of table as index

We are working on C++ in eVC++ 3.0 environment (CE 3.0) with SQL CE 2.0.

We are trying with IRowsetIndex:: Seek method to access the data from the database.

We are facing problem when we try to access data from the table having composite index other than the first column.

For example:
The table ITEM contains the following columns:
1. ItemCode
2. PcNo
3. SubPcNo
4. BrandNo
5. DescText

6. ProductionKind

7. ProductionState

8. ClearingState

9. ST

10. TS

I’ve two indexes for this table where the two indexes are

1. ItemKey1 on ITEM (ItemCode).

2. ItemKey2 on ITEM (PcNo, SubPcNo, BrandNo).

If I mention ItemKey1 as the index then the Seek method works perfectly.

But if I mention ItemKey2 as the index, then I’m getting the error as “DB_E_BADBINDINFO”.

When we visted the MSDN for this problem we noted that:

1. When you use the Seek method on multiple fields, the Seek fields must be in the same order as the fields in the underlying table. If they are not, the Seek method fails.

2. When passing key values to an index rowset, the consumer performs these actions only for the number of key columns specified in the cKeyValues argument in IRowsetIndex:: Seek.

So we had the composite index such that columns of a composite index are physically aligned next to each other as in the table.

When the first column of the index is the first column of the table as in index ItemKey1 we found that the seek method works perfectly.

If we pass the index ItemKey2, we are again getting the error “DB_E_BADBINDINFO”.

What else have we missed out while passing the keys in IRowsetIndex:: Seek?

Thanks in advance.

Regards,

Sasi.

I saw this error when tried to use the table accessor for seeking. When you use IRowsetIndex you must create a separate accessor for the index columns and use that accessor for the Seek method, not the table's own accessor.

|||

Thanks for your reply Joao Paulo Figueira.

I was not able to understand the solution you provided, could you kinldy explain in detail.

Thanks & Regards,

Sasi.

|||

You must create a second memory buffer where to store the values to seek. This is built the same way as the memory buffer for accessing table data, but it must have the index columns and in the same order. You should use code similar to the one for binding the table columns in order to create the index accessor.

The only situation when you can share the same accessor between the table and the index is when the first N columns of the index are the same as the first N columns of the table. In all other situations you must create a different accessor for entering the data to seek.

|||

Thanks a lot Sir.

Its working now.

Thanks & Regards,

Sasi.