"User does not have permission to perform this action." problem

hey people

having a nightmare getting asp.net 2.0 to work with sql server 2005 express, bit of a newbie with it. trying to display a table from my sql database and every time i run the aspx table im getting this error.

 User does not have permission to performthis action.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack tracefor more information about the error and where it originatedin the code.Exception Details: System.Data.SqlClient.SqlException: User does not have permission to performthis action.Source Error:An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identifiedusing the exception stack trace below.Stack Trace:[SqlException (0x80131904): User does not have permission to performthis action.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

 keep getting the user permission error, even though it says on the management console that i have access to the database

any ideas?

rob 

ok the error doesnt tell you that you cant login

the problem is you already login but you cant do action.

can you look your connection string and find out what account are you using.

then check that account's right in your SQL 2005 database,

maybe the right in that account is very small

|||

check if you user have not only rights to connect to database but also right to select from tables you use or execute rights to stored procedures so check database security and your user effective permissions on database objects

Thanks

|||

(having a nightmare getting asp.net 2.0 to work with sql server 2005 express, bit of a newbie with it. trying to display a table from my sql database and every time i run the aspx table im getting this error.)

If you don't have Management Studio you can download it in the first link below and the second link covers how to add object permissions for the Asp.net account post again if you still have question. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/

http://forums.asp.net/thread/1492092.aspx

"upload only" tables in merge subscription?

Is it possible to upload data in a single table from all subscribers to the
publisher in a merge subscription? All subscribers and the publisher are
running SS2005 (Express edition for subscribers). I'm familiar with the
download only option from publisher to subscribers, but need to do just the
opposite. If merge replication can't do this, how about transactional
replication, using a publication with just this one table? Creative
suggestions welcome.
TIA,
Eric Hall
Paul -
Thanks for the pointer. Since I only want to perform the "upload only" on 2
articles, it looks like I have to create a seperate publication for just
those tables. Odd that the upload only option was left out of the
sp_addmergearticle @.subscriber_upload_options parameter options. Download
only is there!
Thanks,
Eric
"Paul Ibison" wrote:

> Eric,
> although the 'modern' way of doing this is to use
> "@.subscriber_upload_options", you can still use -EXCHANGETYPE (please see
> http://www.replicationanswers.com/MergeChangesArticle1.asp).
> Cheers,
> Paul Ibison
>
>
|||Quite understnad - at first I thought it was a replacement for the
commandline parameter but then I realised there is some overlap but they
aren't the same thing. The "@.subscriber_upload_options" is pretty nice
addition though - it vastly reduces the amount of metadata involved, as the
triggers simply don't record the changes. I guess that's why it couldn't
replace the upload only option as this sin't really controlled in the
triggers.
Cheers,
Paul Ibison

"unable to load MDT2G.DLL" (sql 7.0)?

I'm using sql server 7.0 - on "SQL Server Enterprise Manager" screen,
when I right click a table and select "Design Table" on the menu list,
I get error message box "Unable to load MDT2G.DLL".
I never had this before and don't know what happend.
What caused this and how to fix it ?
THANKS !
>
> I'm using sql server 7.0 - on "SQL Server Enterprise Manager"
> screen, when I right click a table and select "Design Table" on
> the menu list, I get error message box "Unable to load MDT2G.DLL".
> I never had this before and don't know what happened.
> What caused this and how to fix it ?
> THANKS !
>
The mdt2g.dll file could be corrupt. Try copying over it from your SQL CD
and see if it works for you.
Regards,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

"unable to load MDT2G.DLL" (sql 7.0)?

I'm using sql server 7.0 - on "SQL Server Enterprise Manager" screen,
when I right click a table and select "Design Table" on the menu list,
I get error message box "Unable to load MDT2G.DLL".
I never had this before and don't know what happend.
What caused this and how to fix it ?
THANKS !>
> I'm using sql server 7.0 - on "SQL Server Enterprise Manager"
> screen, when I right click a table and select "Design Table" on
> the menu list, I get error message box "Unable to load MDT2G.DLL".
> I never had this before and don't know what happened.
> What caused this and how to fix it ?
> THANKS !
>
--
The mdt2g.dll file could be corrupt. Try copying over it from your SQL CD
and see if it works for you.
Regards,
Eric Crdenas
Senior support professional
This posting is provided "AS IS" with no warranties, and confers no rights.

"TRUNCATE TABLE" in a sproc w/locked-down user

Hullo folks, I'm having what I assume is a fairly mundane security issue.

I have a SQL login that I am trying to restrict as much as possible. This account's sole goal in life is to hit the server, return some usage statistics, then truncate the table it received the statistics from. I would like to refrain from granting this login permissions on the physical target table if possible.

Usually I can wrap up "protected" operations in a stored procedure, then grant exec permissions for my user and I'm good to go. However, TRUNCATE TABLE gets cranky with me when I attempt the above method. I suspect that has to do with the fact that TRUNCATE TABLE is effectively modifying the table itself as opposed to merely deleting data.

Is it possible to grant this login ONLY execute permission on a stored proc that TRUNCATE's tables without giving the user any physical permissions? Am I going about this the wrong way?The only people who can use 'truncate' are the table owner, sysadmin, db_owner, and db_ddladmin; so if you have a user whose permissions are severely restricted I would not think 'truncate' would work, or if it would it would be a phaff to set up.

Why not just DELETE FROM, or use a temp table to create the statistics and return them?|||This is effectively what I ended up doing after making the same permissions determination. I had my heart set on Truncate do to the nature of the process itself, but I was able to work around the restriction using DELETE FROM and a local scheduled maintenance job.|||Is there a way he can call an osql command via xp_cmdshell that connects with a trusted connection that can truncate the table ? I tried some stuff with a server link to itself, but cannot truncate table link.db.owner.table, same goes with openquery (truncate not supported).|||That would present a larger security issue then outright granting the user required permissions. I definately don't want to expose trusted command line functionality to this login.

What I have now works, it's just not exactly the way I want it to. All of my research indicates that it will just have to be good enough as I cannot get around that pesky permissions requirement for TRUNCATE.|||In the past I have allowed a user to own a single table for a similar purpose. But that was for an import table, where the data was truncated, reloaded, diced up, and distributed to other tables. No other process even knew the table was there. It gets you out of having to have db_owner rights, but there is no other way around the requirement of owning the table.|||why dont you leave the table there and run a nightly process that truncates any table that had statistics grabbed from it

have an admin account run the nightly process, but the restricted user still gets their statistics

just a thought|||why dont you leave the table there and run a nightly process that truncates any table that had statistics grabbed from it

have an admin account run the nightly process, but the restricted user still gets their statistics

just a thought
That's the current working solution. The process runs a couple times a day, but same idea.

"Tracking"

OK. For DDL, please refer to the classical Northwind ORDERS table,
problem/challenge, find the longest duration (start_date and
end_date), during which, no orders were placed.

FYI, column names by the order of colid per syscolumns:
OrderID
CustomerID
EmployeeID
OrderDate
RequiredDate
ShippedDate
ShipVia
Freight
ShipName
ShipAddress
ShipCity
ShipRegion
ShipPostalCode
ShipCountry

Any idea/approach? TIA.Does this help?

select o1.OrderDate, o2.OrderDate, DATEDIFF( day, o1.OrderDate,
o2.OrderDate) as days_between
from dbo.Orders o1, dbo.Orders o2
where o1.OrderDate < o2.OrderDate
and not exists
(select * from dbo.Orders o3
where o3.OrderDate < o2.OrderDate
and o1.OrderDate < o3.OrderDate)
group by o1.OrderDate, o2.OrderDate
order by days_between desc

"DonLi" <donli@.yahoo.com> wrote in message
news:9a172893.0404051228.4a675061@.posting.google.c om...
> OK. For DDL, please refer to the classical Northwind ORDERS table,
> problem/challenge, find the longest duration (start_date and
> end_date), during which, no orders were placed.
> FYI, column names by the order of colid per syscolumns:
> OrderID
> CustomerID
> EmployeeID
> OrderDate
> RequiredDate
> ShippedDate
> ShipVia
> Freight
> ShipName
> ShipAddress
> ShipCity
> ShipRegion
> ShipPostalCode
> ShipCountry
> Any idea/approach? TIA.

"Top 10" query

How do I do this in SQL?
I have a table I'm querying with the following fields (simplified
here):
facility_name char(15), drug_product char(64), total_cost money
How can I produce the following in SQL so my result can answer the
question
What are the top 10 drugs dispensed by facility_id when looking at
total_cost?
output should look like this:
facility_id drug_product total_cost
houston Simvastatin 500
houston Sertraline 400
...
next 8 for houston
...
chicago Epoetin 1000
chicago Atorvastatin 800
...
next 8 for chicago
...
etc.
I'm thinking this has to done in more than one statement since TOP will
not work here (as far as I can see) I miss the sequential record
"file" environment of foxpro/access here. I can do this in either of
those 2 apps with about 1 min of coding but I want to learn how to do
this in a SQL tbl.Without DDLs, here is a skeleton query:
SELECT * -- use column names
FROM tbl t1
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.facility_id = t1.facility_id
AND t2.total_cost >= t1.total_cost ) <= 10 ;
You have not specified how you'd want to resolve the ties. If ties are
involved, change the correlation like:
WHERE t2.facility_id = t1.facility_id
AND ( t2.drug_product = t1.drug_product
AND t2.total_cost >= t1.total_cost )
OR t2.total_cost >= t1.total_cost )
In t-SQL you can easily use a TOP clause with ORDER BY to achieve similar
results. You may also want to refer to www.aspfaq.com/2120 for some similar
ideas
Anith|||Supposing there are no ties, then:
select a.*
from t as a
where (select count(*) from t as b where b.facility_name = a.facility_name
and b.total_cost <= a.total_cost) <= 10
AMB
"mwrobe" wrote:

> How do I do this in SQL?
> I have a table I'm querying with the following fields (simplified
> here):
> facility_name char(15), drug_product char(64), total_cost money
> How can I produce the following in SQL so my result can answer the
> question
> What are the top 10 drugs dispensed by facility_id when looking at
> total_cost?
> output should look like this:
> facility_id drug_product total_cost
> houston Simvastatin 500
> houston Sertraline 400
> ...
> next 8 for houston
> ...
> chicago Epoetin 1000
> chicago Atorvastatin 800
> ...
> next 8 for chicago
> ...
> etc.
> I'm thinking this has to done in more than one statement since TOP will
> not work here (as far as I can see) I miss the sequential record
> "file" environment of foxpro/access here. I can do this in either of
> those 2 apps with about 1 min of coding but I want to learn how to do
> this in a SQL tbl.
>|||Thank you both gentlemen. It looks like I need to read up and
experiment more on subqueries. Thanks for the link as well.

"Timeout Expired" on large table change

I have a table containing several hundred thousand rows. Once of the
fields in the table is named EODFeedDate. The field is nullable. I've
decided that I do NOT want that field to be nullable. I've unchecked the
"allow nulls" checkbox for the field in Management Studio. I then tried to
save my schema change. I'm getting the following error from Management
Studio:
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
I can't figure out what's causing the problem. It's worth nothing that:
- Of the current entries in this table, none of them have EODFeedDate
set to null so making the field non-nullable should cause a conflict.
- No one else is connected to the database. It's a private development
machine -- so it isn't a contention problem.
Any ideas?
David
> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> I can't figure out what's causing the problem.
I did a google search on this. You have to write a query to workaround this
bug. I've never written query code to modify a table before; I've always
used the UI, so I had some learning to do.
I learned about the ALTER TABLE statement but my execution of that statement
failed. The error message stated that the column could not be made
non-nullable because there is an index on the table that made use of the
column in question. That's when I learned about the DROP INDEX statement. By
using a DROP INDEX in tandem with an ALTER TABLE, I was able to make the
field non-nullable. I then re-created the index using the UI.
I've never spent so much time trying to do something so simple.
David
|||You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:-MOdnXQku9doHYXZnZ2dnUVZ_t-dnZ2d@.giganews.com...
> I did a google search on this. You have to write a query to workaround
> this bug. I've never written query code to modify a table before; I've
> always used the UI, so I had some learning to do.
> I learned about the ALTER TABLE statement but my execution of that
> statement failed. The error message stated that the column could not be
> made non-nullable because there is an index on the table that made use of
> the column in question. That's when I learned about the DROP INDEX
> statement. By using a DROP INDEX in tandem with an ALTER TABLE, I was able
> to make the field non-nullable. I then re-created the index using the UI.
> I've never spent so much time trying to do something so simple.
> David
>
|||"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uIYUXTHSGHA.4456@.TK2MSFTNGP14.phx.gbl...
> You can have Management Studio generate the script by making the change,
> then right clicking the window and selecting "Generate Change Script"
Doh! I didn't realize that!! Thanks for the tip. I'll be sure to make use of
that in the future.

"Timeout Expired" on large table change

I have a table containing several hundred thousand rows. Once of the
fields in the table is named EODFeedDate. The field is nullable. I've
decided that I do NOT want that field to be nullable. I've unchecked the
"allow nulls" checkbox for the field in Management Studio. I then tried to
save my schema change. I'm getting the following error from Management
Studio:
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
I can't figure out what's causing the problem. It's worth nothing that:
- Of the current entries in this table, none of them have EODFeedDate
set to null so making the field non-nullable should cause a conflict.
- No one else is connected to the database. It's a private development
machine -- so it isn't a contention problem.
Any ideas?
David> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> I can't figure out what's causing the problem.
I did a google search on this. You have to write a query to workaround this
bug. I've never written query code to modify a table before; I've always
used the UI, so I had some learning to do.
I learned about the ALTER TABLE statement but my execution of that statement
failed. The error message stated that the column could not be made
non-nullable because there is an index on the table that made use of the
column in question. That's when I learned about the DROP INDEX statement. By
using a DROP INDEX in tandem with an ALTER TABLE, I was able to make the
field non-nullable. I then re-created the index using the UI.
I've never spent so much time trying to do something so simple.
David|||You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:-MOdnXQku9doHYXZnZ2dnUVZ_t-dnZ2d@.giganews.com...
> I did a google search on this. You have to write a query to workaround
> this bug. I've never written query code to modify a table before; I've
> always used the UI, so I had some learning to do.
> I learned about the ALTER TABLE statement but my execution of that
> statement failed. The error message stated that the column could not be
> made non-nullable because there is an index on the table that made use of
> the column in question. That's when I learned about the DROP INDEX
> statement. By using a DROP INDEX in tandem with an ALTER TABLE, I was able
> to make the field non-nullable. I then re-created the index using the UI.
> I've never spent so much time trying to do something so simple.
> David
>|||"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uIYUXTHSGHA.4456@.TK2MSFTNGP14.phx.gbl...
> You can have Management Studio generate the script by making the change,
> then right clicking the window and selecting "Generate Change Script"
Doh! I didn't realize that!! Thanks for the tip. I'll be sure to make use of
that in the future.

"Timeout Expired" on large table change

I have a table containing several hundred thousand rows. Once of the
fields in the table is named EODFeedDate. The field is nullable. I've
decided that I do NOT want that field to be nullable. I've unchecked the
"allow nulls" checkbox for the field in Management Studio. I then tried to
save my schema change. I'm getting the following error from Management
Studio:
"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."
I can't figure out what's causing the problem. It's worth nothing that:
- Of the current entries in this table, none of them have EODFeedDate
set to null so making the field non-nullable should cause a conflict.
- No one else is connected to the database. It's a private development
machine -- so it isn't a contention problem.
Any ideas?
David> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
> I can't figure out what's causing the problem.
I did a google search on this. You have to write a query to workaround this
bug. I've never written query code to modify a table before; I've always
used the UI, so I had some learning to do.
I learned about the ALTER TABLE statement but my execution of that statement
failed. The error message stated that the column could not be made
non-nullable because there is an index on the table that made use of the
column in question. That's when I learned about the DROP INDEX statement. By
using a DROP INDEX in tandem with an ALTER TABLE, I was able to make the
field non-nullable. I then re-created the index using the UI.
I've never spent so much time trying to do something so simple. :(
David|||You can have Management Studio generate the script by making the change,
then right clicking the window and selecting "Generate Change Script"
"The One We Call 'Dave'" <ghetto@.englewood.com> wrote in message
news:-MOdnXQku9doHYXZnZ2dnUVZ_t-dnZ2d@.giganews.com...
>> "Timeout expired. The timeout period elapsed prior to completion of the
>> operation or the server is not responding."
>> I can't figure out what's causing the problem.
> I did a google search on this. You have to write a query to workaround
> this bug. I've never written query code to modify a table before; I've
> always used the UI, so I had some learning to do.
> I learned about the ALTER TABLE statement but my execution of that
> statement failed. The error message stated that the column could not be
> made non-nullable because there is an index on the table that made use of
> the column in question. That's when I learned about the DROP INDEX
> statement. By using a DROP INDEX in tandem with an ALTER TABLE, I was able
> to make the field non-nullable. I then re-created the index using the UI.
> I've never spent so much time trying to do something so simple. :(
> David
>|||"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uIYUXTHSGHA.4456@.TK2MSFTNGP14.phx.gbl...
> You can have Management Studio generate the script by making the change,
> then right clicking the window and selecting "Generate Change Script"
Doh! I didn't realize that!! Thanks for the tip. I'll be sure to make use of
that in the future.

"SUM" of a varchar column?

I have a table that contains a number of columns containing either a varchar
value, or null. There are multiple rows per grouping. I would like to colapse
the multiple rows into a single row, either by appending the strings to each
other, or simply selecting the first (or last) one.
If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work, for
obvious reasons. I thought MAX would work, but MAX returns only one value for
all of the columns (as opposed to one per column) and the rest are left as
null.
Any suggestions?
Here's an example...
The table contains this data, all items are varchar
32611317
3261Non-Client
32612
3261mmarkowitz
I'd like to turn this into...
3261 1317 Non-client 2 mmarkowitz
|||This looks like a PIVOT. Can you flatten this table out on the client? If
not, see http://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7838E3D4-CD8D-46EB-A75F-D069B30EE9AB@.microsoft.com...
> I have a table that contains a number of columns containing either a
varchar
> value, or null. There are multiple rows per grouping. I would like to
colapse
> the multiple rows into a single row, either by appending the strings to
each
> other, or simply selecting the first (or last) one.
> If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work,
for
> obvious reasons. I thought MAX would work, but MAX returns only one value
for
> all of the columns (as opposed to one per column) and the rest are left as
> null.
> Any suggestions?
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DD2FA7A5-A0F2-4BFF-85E9-42533F4E423C@.microsoft.com...
> Here's an example...
> The table contains this data, all items are varchar
>
> 3261 1317
> 3261 Non-Client
> 3261 2
> 3261 mmarkowitz
> I'd like to turn this into...
> 3261 1317 Non-client 2 mmarkowitz
I'm not sure if this will help, but you may want to take a look at the
GROUP BY WITH ROLLUP and WITH CUBE commands. It may work for what you are
after.
SELECT Col1, Max(Col2)
FROM tablename
GROUP BY Col1
WITH ROLLUP
HTH
Rick Sawtell
MCT, MCSD, MCDBA
|||SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
FROM YourTable
GROUP BY col1
David Portas
SQL Server MVP
|||I don't think this will work, he wants to flatten one ofthe two columns in
his table out into multiple columns...
http://www.aspfaq.com/
(Reverse address to reply.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103306604.369012.58920@.z14g2000cwz.googlegro ups.com...
> SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
> FROM YourTable
> GROUP BY col1
> --
> David Portas
> SQL Server MVP
> --
>
|||OK. Looks to me like 5 columns but I guess that's just presentational ;-)
Tough to pivot without an explicit attribute for the column.
David Portas
SQL Server MVP
|||"Aaron [SQL Server MVP]" wrote:

> This looks like a PIVOT.
Actually it is the RESULT of a pivot, which is why it is spread out
vertically like that.
But I did figure out a "trick". After reading the page you sent, I combined
their technique of ISNULL (instead of CASE) with MIN, and presto.
Thanks!
(anyone interested in the code?)

"SUM" of a varchar column?

I have a table that contains a number of columns containing either a varchar
value, or null. There are multiple rows per grouping. I would like to colapse
the multiple rows into a single row, either by appending the strings to each
other, or simply selecting the first (or last) one.
If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work, for
obvious reasons. I thought MAX would work, but MAX returns only one value for
all of the columns (as opposed to one per column) and the rest are left as
null.
Any suggestions?Here's an example...
The table contains this data, all items are varchar
3261 1317
3261 Non-Client
3261 2
3261 mmarkowitz
I'd like to turn this into...
3261 1317 Non-client 2 mmarkowitz|||This looks like a PIVOT. Can you flatten this table out on the client? If
not, see http://www.aspfaq.com/2462
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:7838E3D4-CD8D-46EB-A75F-D069B30EE9AB@.microsoft.com...
> I have a table that contains a number of columns containing either a
varchar
> value, or null. There are multiple rows per grouping. I would like to
colapse
> the multiple rows into a single row, either by appending the strings to
each
> other, or simply selecting the first (or last) one.
> If it was a number field I could do a SUM, MAX or MIN. SUM doesn't work,
for
> obvious reasons. I thought MAX would work, but MAX returns only one value
for
> all of the columns (as opposed to one per column) and the rest are left as
> null.
> Any suggestions?|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:DD2FA7A5-A0F2-4BFF-85E9-42533F4E423C@.microsoft.com...
> Here's an example...
> The table contains this data, all items are varchar
>
> 3261 1317
> 3261 Non-Client
> 3261 2
> 3261 mmarkowitz
> I'd like to turn this into...
> 3261 1317 Non-client 2 mmarkowitz
I'm not sure if this will help, but you may want to take a look at the
GROUP BY WITH ROLLUP and WITH CUBE commands. It may work for what you are
after.
SELECT Col1, Max(Col2)
FROM tablename
GROUP BY Col1
WITH ROLLUP
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
FROM YourTable
GROUP BY col1
--
David Portas
SQL Server MVP
--|||I don't think this will work, he wants to flatten one ofthe two columns in
his table out into multiple columns...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1103306604.369012.58920@.z14g2000cwz.googlegroups.com...
> SELECT col1, MAX(col2), MAX(col3), MAX(col4), ...
> FROM YourTable
> GROUP BY col1
> --
> David Portas
> SQL Server MVP
> --
>|||OK. Looks to me like 5 columns but I guess that's just presentational ;-)
Tough to pivot without an explicit attribute for the column.
--
David Portas
SQL Server MVP
--|||"Aaron [SQL Server MVP]" wrote:
> This looks like a PIVOT.
Actually it is the RESULT of a pivot, which is why it is spread out
vertically like that.
But I did figure out a "trick". After reading the page you sent, I combined
their technique of ISNULL (instead of CASE) with MIN, and presto.
Thanks!
(anyone interested in the code?)

"string or binary data would be truncated" error message

I am getting the message ("string or binary data would be truncated") when trying to update a field in a table in my database, but am unclear as to why. I understand that the error usually occurs when the length of a string exceeds the limit allowed for a field in the database, but in this situation, that constraint should not apply. The length of the field for which I am trying to make the update is a varchar field of length 100. The value for which I am attempting to update is an e-mail address of length 42 of the format xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx@.yahoo.com. The initial insertion of the value (via my website) succeeded in inserting the first 40 characters of the address, but cut off the "om" at the end. It doesn't seem to matter how large I make the field in the database as it always gives me this error when I try to make the update. I have tried to change the value in both Query Analyzer and Enterprise Manager, but I invariably receive the same error.

Any ideas?I just posted my best guesses of causes for this here:

http://forums.asp.net/thread/1366165.aspx

Hope it helps,

Joe|||Thanks, your tips actually did help. There was a trigger on the table I was trying to insert into and it was attempting to insert into a table where the email field only allowed for addresses as large as 40 characters.

"Storing an image into Sql Server Database "

Is there any way of storing an image file into a specific Table .
It would be of great help for me if i come to know something about it.One simple way is to store the physical path of the image

Madhivanan|||(santosh_stb@.indiatimes.com) writes:
> Is there any way of storing an image file into a specific Table .
> It would be of great help for me if i come to know something about it.

You can use the image datatype. You can also opt to store the image
in the file system and store only the path in the database as
Madhivanan suggested. This latter is simpler to implement, in the
short-term at least, but is less reliable since you have poorer
transaction scope, the file can more easily disappear.

Thus using image is more robust, but admittedly it takes more code to
come there.

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

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

"Simple" query help

Hello!
I would like some help to figure out how to write a specific update query.
What I'm trying to do:
I have one table with these columns:
no (primary key or clustered?)
object (primary key or clustered?)
cid
usergroup1
usergroup2
Lets say the table I would like to upgrade is called obj1 and the table I'm
getting the value to set from is called obj2.
This is in real the same table and it's called objectx.
I would like to set cid for the records with no=7 in obj1 to the value of
the records in obj2 with:
obj2.no=1,
obj2.usergroup2=obj1.object
the value should be set to obj2.usergroup1
The obj2.usergroup2 values could be found several times.
Any idea how to write this query?
Regards MagnusOn Fri, 9 Dec 2005 12:16:41 +0100, Magnus Blomberg wrote:

>Hello!
>I would like some help to figure out how to write a specific update query.
>What I'm trying to do:
>I have one table with these columns:
>no (primary key or clustered?)
>object (primary key or clustered?)
>cid
>usergroup1
>usergroup2
>Lets say the table I would like to upgrade is called obj1 and the table I'm
>getting the value to set from is called obj2.
>This is in real the same table and it's called objectx.
>I would like to set cid for the records with no=7 in obj1 to the value of
>the records in obj2 with:
>obj2.no=1,
>obj2.usergroup2=obj1.object
>the value should be set to obj2.usergroup1
>The obj2.usergroup2 values could be found several times.
>Any idea how to write this query?
>Regards Magnus
>
Hi Magnus,
Before writing the query, the specifications should be clear. You say
that obj2.usergroup2 can be found several times. That means that there
might be more than one obj2.usergroup1. Which one of these should be
used to set obj1.cid'
If you need the lowest value, try if this works:
UPDATE objectx
SET cid = (SELECT MIN(obj2.usergroup1)
FROM objectx AS obj2
WHERE obj2.no = 1
AND obj2.usergroup2 = objectx.object)
WHERE no = 7
(untested - if you prefer a tested reply or if this doesn;t work, then
please check www.aspfaq.com/5006 to find out how to provide clear specs
and test data).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

"select" only if theres a same record in the table

i have two tables A and B. relation is one to many for A. i want to select from A only if there are more than two records of A in table B and also checking some condition in table B. if the question's not very clear please let me know.

Substitute A_PK for whatever A's PK is and try the following:

SELECT *
FROM A
WHERE EXISTS
(SELECT B.A_PK
FROM B
WHERE B.A_PK = A.A_PK
GROUP BY B.A_PK
HAVING COUNT(*) >= 2)

"Right" section of BorderStyle not working in last column

I have a multi-column report (with a header). I'm trying to draw a 1pt
border around the entire header. Not the report or table, just the header.
All of the sections of the border appear correctly except the sections in
the last column to the right. The "right" section of the last column does
not display in any mode.
Has anyone run into this? Is there a workaround?
Thanks!You can get a "clipped" or "missing" information in the last column when the
following is not true:
PageWidth - Left Margin - Right Marging >= (#Columns * ColumnWidth) +
((#Columns -1) * Column Spacing).
Please check how your multi column report is setup.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
message news:425BF87E-437C-4B97-88B2-28207E96C53D@.microsoft.com...
>I have a multi-column report (with a header). I'm trying to draw a 1pt
> border around the entire header. Not the report or table, just the
> header.
> All of the sections of the border appear correctly except the sections in
> the last column to the right. The "right" section of the last column does
> not display in any mode.
> Has anyone run into this? Is there a workaround?
> Thanks!

"requested conversion is not supported" error in DTS data copy.

I have a DTS that pulls data in from a table in MysQL and inserts it into a tablle in SQL Server. it's failing when I run the step with the "requestd conversion is not supported" error, but when testing each single column copy in the Data Transformation step, it passes.

I just want an exact copy of the data.

the source MySQL table is...

CREATE TABLE `entry` (
`web_id` int(11) NOT NULL auto_increment,
`web_name` varchar(40) NOT NULL default '',
`web_email` varchar(60) NOT NULL default '',
`web_cli` varchar(50) NOT NULL default '',
`web_pu_id` varchar(60) default NULL,
`web_answer` varchar(60) NOT NULL default '',
`web_ddi` varchar(30) default NULL,
`web_time_entered` datetime NOT NULL default '0000-00-00 00:00:00',
`web_ip_address` varchar(20) default NULL,
`web_entry_status` set('failed','success') NOT NULL default '',
`web_polled` enum('true','false') default 'false',
PRIMARY KEY (`web_id`)
) ENGINE=MyISAM;

and the SQl Server destination is...

CREATE TABLE [entry] (
[web_id] [int] NOT NULL ,
[web_name] [varchar] (23) NOT NULL ,
[web_email] [varchar] (37) NOT NULL ,
[web_cli] [varchar] (15) NOT NULL ,
[web_pu_id] [varchar] (15) NULL ,
[web_answer] [text] NOT NULL ,
[web_ddi] [varchar] (11) NULL ,
[web_time_entered] [datetime] NOT NULL ,
[web_ip_address] [varchar] (15) NULL ,
[web_entry_status] [varchar] (15) ,
[web_polled] [varchar] (15) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [temp_webentry] WITH NOCHECK ADD
CONSTRAINT [PK_tmpwebentry] PRIMARY KEY CLUSTERED
(
[web_id]
) ON [PRIMARY]
GO

sample line of data ...

352711, 'some name', 'email@.addre.ss', '0123456789', 'AA_XX-Y-012345-2', '', '098765432', '2005-10-19 23:17:42', '192.168.28.165', 'failed', 'false'scratch that.. in compairing the DDLs it was obvious..

varchar -> text doesn't work well :)

a

"Random IV"?

I just encrypted a bunch of rows in a table. I was a little surprised
that encrypting N'A' resulted in a 44-byte result. N'AAAA' resulted in
a 52-byte result. That indicates that 8-byte padding is being used.
So the actual encrypted text of N'A' is only 8 bytes in length. What
in the world are the other 36 bytes being used for? I noticed an
interesting 'prefix' for the encrypted data:
0x0074126478AFB34BA54BA6280E2335EB010000
003AE8D337EF2E5B55BABDE251B245C1FCCD
B21C770073B642
0x0074126478AFB34BA54BA6280E2335EB010000
0057252C84D4B6C9F3FFA40444BB7E5ADC50
9233F73548C7A8
0x0074126478AFB34BA54BA6280E2335EB010000
0077113F30B10CC2A8FD976628C98C06C5F7
0E47AEEF3BE86C
It appears the first 20 bytes of every encrypted string are exactly the
same, regardless of the row, column, or data being encrypted. Further
these 20 bytes appear to be generated when the OPEN ... KEY statement
is issued. In between OPEN ... KEY statements, these first 20
characters change, but for multiple encryptions after an OPEN ... KEY
they remain the same.
These first 20 - 36 bytes wouldn't happen to be the "Random IV" would
they? One would think that storing the IV with the data would be
potentially less secure than letting the user manage their own IV...
What do you think?The first 16 prefix bytes represent the key identifier (a GUID). They are
used to determine what key should be used for decryption. They're
particularly useful if you use multiple encryption keys per column. The
decryption builtins will automatically search for the proper key and use it
to decrypt the blob.
So, if you're doing something like
select decryptbykey(SSN) from customers
it will decrpyt all SSNs encrypted by keys that you currently have opened.
The IV is indeed prefixed to the data. IV's are not meant to be secret,
they're just used to randomize the encryption output, so that patterns in
the cleartext data are not disclosed.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
<coles.michael@.gmail.com> wrote in message
news:1151728397.102411.65150@.h44g2000cwa.googlegroups.com...
>I just encrypted a bunch of rows in a table. I was a little surprised
> that encrypting N'A' resulted in a 44-byte result. N'AAAA' resulted in
> a 52-byte result. That indicates that 8-byte padding is being used.
> So the actual encrypted text of N'A' is only 8 bytes in length. What
> in the world are the other 36 bytes being used for? I noticed an
> interesting 'prefix' for the encrypted data:
> 0x0074126478AFB34BA54BA6280E2335EB010000
003AE8D337EF2E5B55BABDE251B245C1FC
CDB21C770073B642
> 0x0074126478AFB34BA54BA6280E2335EB010000
0057252C84D4B6C9F3FFA40444BB7E5ADC
509233F73548C7A8
> 0x0074126478AFB34BA54BA6280E2335EB010000
0077113F30B10CC2A8FD976628C98C06C5
F70E47AEEF3BE86C
> It appears the first 20 bytes of every encrypted string are exactly the
> same, regardless of the row, column, or data being encrypted. Further
> these 20 bytes appear to be generated when the OPEN ... KEY statement
> is issued. In between OPEN ... KEY statements, these first 20
> characters change, but for multiple encryptions after an OPEN ... KEY
> they remain the same.
> These first 20 - 36 bytes wouldn't happen to be the "Random IV" would
> they? One would think that storing the IV with the data would be
> potentially less secure than letting the user manage their own IV...
> What do you think?
>|||The first 20 bytes are not part of the ciphertext, it is really some
metadata for internal use:
*The first 16 bytes is the key GUID used by the system to find the proper
key from the key ring (opened keys), if there is no key that matches the
ciphertext GUID, the operation fails immediately returning NULL.
*The next 4 bytes is an internal version number, this value is hardcoded
for now, but can be potentially used in the future to distinguish what
version of the product was used to generate any given ciphertext.
*Then we have the randomly generated IV (8 bytes in case of DES family,
16 in case of AES) followed by the rest of the ciphertext + padding (up to a
whole block of padding, again 8 or 16 bytes depending on the algorithm).
The real plaintext is not purely the plaintext given to the builtin. It
includes an 8 byte header that describes some attributes of the plaintext an
d
the presence of the authenticator parameter for the builtin.
Let’s take a look to a sample:
CREATE SYMMETRIC KEY key1 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD
= 'k3y_1 P@.zzw0rd'
CREATE SYMMETRIC KEY key2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY PASSWORD
= 'k3y_1 P@.zzw0rd'
go
OPEN SYMMETRIC KEY key1 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
OPEN SYMMETRIC KEY key2 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
go
SELECT key_name, convert(varbinary(100), key_guid) as key_guid_bin FROM
sys.openkeys
go
CREATE TABLE key_demo( key_guid_bin varbinary(100) , ciphertext
varbinary(8000) )
go
-- 3 times the same value with each key
INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
key_guid('key1'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
key_guid('key1'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
key_guid('key1'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
key_guid('key2'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
key_guid('key2'), 'AAAAAAAA' ))
INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
key_guid('key2'), 'AAAAAAAA' ))
go
SELECT * FROM key_demo
go
Let’s take a closer look to these results. In my case, I got the following
GUID on my demo key:
0x008FBF67275CF643BF623BC0422F7F5C
And the first ciphertext:
0x008FBF67275CF643BF623BC0422F7F5C010000
000EC0844ACA2346DB0FE2BD35FF93BEA656
B2A45296CB8E000F7094C2F21D0285
Notice that the GUID is indeed the prefix of the ciphertext, then we have
“01000000”, which is the hardcoded version.
0EC0844ACA2346DB – IV
0FE2BD35FF93BEA6 – 8 byte header
56B2A45296CB8E00 – cipher text (8 bytes)
0F7094C2F21D0285 – padding (8 bytes)
Now, let’s take a quick look and compare the next row:
0x008FBF67275CF643BF623BC0422F7F5C – same GUID (same key was used to encry
pt
this plaintext)
01000000 – same version
E4BA5A5141FE5842 – IV (different, and from this point the rest of the
ciphertext will be different as well)
0E4EEB8440877430 - header – (the plaintext for both headers should be the
same as the same options were used)
33C361724D74742F
26E6829B957E877F
I hope this helped to clarify the usage of the first 20 bytes of data as
well as the other 8 bytes used for the internal use header.
-Raul Garcia
SDE/T
SQL Server Engine
________________________________________
This posting is provided "AS IS" with no warranties, and confers no rights.
"coles.michael@.gmail.com" wrote:

> I just encrypted a bunch of rows in a table. I was a little surprised
> that encrypting N'A' resulted in a 44-byte result. N'AAAA' resulted in
> a 52-byte result. That indicates that 8-byte padding is being used.
> So the actual encrypted text of N'A' is only 8 bytes in length. What
> in the world are the other 36 bytes being used for? I noticed an
> interesting 'prefix' for the encrypted data:
> 0x0074126478AFB34BA54BA6280E2335EB010000
003AE8D337EF2E5B55BABDE251B245C1FC
CDB21C770073B642
> 0x0074126478AFB34BA54BA6280E2335EB010000
0057252C84D4B6C9F3FFA40444BB7E5ADC
509233F73548C7A8
> 0x0074126478AFB34BA54BA6280E2335EB010000
0077113F30B10CC2A8FD976628C98C06C5
F70E47AEEF3BE86C
> It appears the first 20 bytes of every encrypted string are exactly the
> same, regardless of the row, column, or data being encrypted. Further
> these 20 bytes appear to be generated when the OPEN ... KEY statement
> is issued. In between OPEN ... KEY statements, these first 20
> characters change, but for multiple encryptions after an OPEN ... KEY
> they remain the same.
> These first 20 - 36 bytes wouldn't happen to be the "Random IV" would
> they? One would think that storing the IV with the data would be
> potentially less secure than letting the user manage their own IV...
> What do you think?
>|||Thank you both for the info. That is quite a bit of extra storage when
encrypting small strings, but apparently that's just the cost of doing
business. I have another question for you - is it possible to invoke your
own implementation of an algorithm from SS 2005? For instance, if I created
my own CryptoAPI CSP can the T-SQL encryption functions recognize and use
it?
"Raul Garcia [MS]" <Raul Garcia [MS]@.discussions.microsoft.com> wrot
e in
message news:F7E85812-8C39-4E46-B8C0-C9B883BA06EC@.microsoft.com...[vbcol=seagreen]
> The first 20 bytes are not part of the ciphertext, it is really some
> metadata for internal use:
> *The first 16 bytes is the key GUID used by the system to find the
> proper
> key from the key ring (opened keys), if there is no key that matches the
> ciphertext GUID, the operation fails immediately returning NULL.
> *The next 4 bytes is an internal version number, this value is hardcoded
> for now, but can be potentially used in the future to distinguish what
> version of the product was used to generate any given ciphertext.
> *Then we have the randomly generated IV (8 bytes in case of DES family,
> 16 in case of AES) followed by the rest of the ciphertext + padding (up to
> a
> whole block of padding, again 8 or 16 bytes depending on the algorithm).
> The real plaintext is not purely the plaintext given to the builtin. It
> includes an 8 byte header that describes some attributes of the plaintext
> and
> the presence of the authenticator parameter for the builtin.
> Let's take a look to a sample:
> CREATE SYMMETRIC KEY key1 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY
> PASSWORD
> = 'k3y_1 P@.zzw0rd'
> CREATE SYMMETRIC KEY key2 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY
> PASSWORD
> = 'k3y_1 P@.zzw0rd'
> go
> OPEN SYMMETRIC KEY key1 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
> OPEN SYMMETRIC KEY key2 DECRYPTION BY PASSWORD = 'k3y_1 P@.zzw0rd'
> go
> SELECT key_name, convert(varbinary(100), key_guid) as key_guid_bin FROM
> sys.openkeys
> go
> CREATE TABLE key_demo( key_guid_bin varbinary(100) , ciphertext
> varbinary(8000) )
> go
> -- 3 times the same value with each key
> INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
> key_guid('key1'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
> key_guid('key1'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key1'), EncryptByKey(
> key_guid('key1'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
> key_guid('key2'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
> key_guid('key2'), 'AAAAAAAA' ))
> INSERT INTO key_demo VALUES( key_guid('key2'), EncryptByKey(
> key_guid('key2'), 'AAAAAAAA' ))
> go
> SELECT * FROM key_demo
> go
> Let's take a closer look to these results. In my case, I got the following
> GUID on my demo key:
> 0x008FBF67275CF643BF623BC0422F7F5C
> And the first ciphertext:
> 0x008FBF67275CF643BF623BC0422F7F5C010000
000EC0844ACA2346DB0FE2BD35FF93BEA6
56B2A45296CB8E000F7094C2F21D0285
> Notice that the GUID is indeed the prefix of the ciphertext, then we have
> "01000000", which is the hardcoded version.
> 0EC0844ACA2346DB - IV
> 0FE2BD35FF93BEA6 - 8 byte header
> 56B2A45296CB8E00 - cipher text (8 bytes)
> 0F7094C2F21D0285 - padding (8 bytes)
> Now, let's take a quick look and compare the next row:
> 0x008FBF67275CF643BF623BC0422F7F5C - same GUID (same key was used to
> encrypt
> this plaintext)
> 01000000 - same version
> E4BA5A5141FE5842 - IV (different, and from this point the rest of the
> ciphertext will be different as well)
> 0E4EEB8440877430 - header - (the plaintext for both headers should be the
> same as the same options were used)
> 33C361724D74742F
> 26E6829B957E877F
> I hope this helped to clarify the usage of the first 20 bytes of data as
> well as the other 8 bytes used for the internal use header.
> -Raul Garcia
> SDE/T
> SQL Server Engine
> ________________________________________
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "coles.michael@.gmail.com" wrote:
>|||No, right now, you cannot specify the CSP to be used for encryption. We're
looking at adding this capability in future versions of SQL Server.
Thanks
Laurentiu Cristofor [MSFT]
Software Design Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mike C#" <xyz@.xyz.com> wrote in message
news:u43EzXQoGHA.964@.TK2MSFTNGP05.phx.gbl...
> Thank you both for the info. That is quite a bit of extra storage when
> encrypting small strings, but apparently that's just the cost of doing
> business. I have another question for you - is it possible to invoke your
> own implementation of an algorithm from SS 2005? For instance, if I
> created my own CryptoAPI CSP can the T-SQL encryption functions recognize
> and use it?
>
> "Raul Garcia [MS]" <Raul Garcia [MS]@.discussions.microsoft.com> wr
ote in
> message news:F7E85812-8C39-4E46-B8C0-C9B883BA06EC@.microsoft.com...
>

"query notification" problem

I am using query notification and have the following problem:
I tegister once, then change the DB table manually to test. a msg is printed
to the log proving the call back function was called. I can repeat this
several time.
BUT, I then go away for 10 min', and come back. Now, when I change the data
in the DB nothing happens!
any clue how to fix and undertand this?
thanksUPDATE:
I used trace to see what is going on in the database. So I see that I get
"subscription fired" followed by "subscription registered" everytime I do
changes to the database.
After I stop doing any changes to the database for 5 minuts, and re-change
the data there, I see the database fire the "subscription fired" event, but
this time there is no "subscription registered" event. I also do not get the
log printout that I have in my web ASP.NET application, that is supposed to
write a line in the callback function.
So the hear is the deal:
The callback function gets the event fired for the first few times, so I am
doing the registration right. but, somehting happens to the ASP.NET
aplication, or the DB connection or something, that causes the callback
function not to be called after a certain time. anyone has an idea of what
am I up against? why is this happening?
thanks|||Any idea what is this error I get?
The query notification dialog on conversation handle
'{2FD61BF6-44B8-DA11-8B4E-00123F74CFF6}.' closed due to the following error:
'<?xml version="1.0"?><Error
xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Co
de><Description>Cannot
find the remote service
'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4'
because it does not exist.</Description></Error>'.
?|||It means that service named
'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4' does not
exist in the database where the notifications should be delivered to. Seems
like you are adding the broker instance at the end of the service name. The
service name you pass to the SqlNotificationRequest.Options must match the
service name you created in the database.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"csmba" <csmba@.nowhere.com> wrote in message
news:u4e2l3fTGHA.3192@.TK2MSFTNGP09.phx.gbl...
> Any idea what is this error I get?
> The query notification dialog on conversation handle
> '{2FD61BF6-44B8-DA11-8B4E-00123F74CFF6}.' closed due to the following
> error: '<?xml version="1.0"?><Error
> xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</
Code><Description>Cannot
> find the remote service
> 'SqlQueryNotificationService-657d36e5-7654-44a2-8137-5ec5e3417cc4&apo
s;
> because it does not exist.</Description></Error>'.
>
> ?
>