Showing posts with label user. Show all posts
Showing posts with label user. Show all posts

Sunday, February 19, 2012

.......A floating point exception occurred in the user process.........

I'm getting this error:
System.Data.SqlClient.SqlException: A floating point exception occurred in the user process. Current transaction is canceled. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at ...etc etc
when updating one particular table, but not when updating another using nearly the same code.
I've searched and searched on the inet, but with no joy.
Microsoft have several different manifestations of it, under variousdifferent circumstances, but the solutions all seem to involveinstalling SP4, which by the looks of it is a massive procedure thatyou have to go to university to be competent to do.
In any case, according to Enterprise Manager, I already have SP4included. On the other hand, the one file of mine I checked the date ofagainst MS's hotfix file list was 2002 not 2003, so who knows?
My Service Manager is v 8.00.760, and it's the Development Edition (ie it's all running on the one machine).
According to the info on sqlDataAdaptor.update, thesqlError that gets returned as part of the sqlException containsfields Class, which gives the seriousness of the error, and State,which identifies the exact error. I get Class=16 (user-fixable)and State=1, which we have to go to SQL Books Online to discover themeaning of. SQL BO probably sounded like a good idea to W.Gates,but it took me half an hour to get anywhere near finding out what that'1' meant. When I did, it was (guess): 'A floating pointexception occurred in the user process. Current transaction iscanceled.' !!!
Can anyone please tell me what exactly might be causing the error?
Graham Rounce

What is your create table statement like is float one of your data types? Another thing most T-SQL quantitative functions are in float but we usually don't use float as data type for rounding problems. Post your stored proc and create table statement and I may be able to help you.|||That would be very terrific. Actually, the update command was made by Visual Studio and looks like this:
'SqlUpdateCommand2
'
Me.SqlUpdateCommand2.CommandText= "UPDATE prices SET price_type = @.price_type, price_code_number =@.price_code_numbe" & _
"r, price_code = @.price_code, price_description = @.price_description, price_rrp =" & _
" @.price_rrp, price_icibuysatxpercentdiscount = @.price_icibuysatxpercentdiscount," & _
" price_icibuysatx = @.price_icibuysatx, price_markupper = @.price_markupper, price" & _
"_absmargin = @.price_absmargin, price_up_icibuysatxpercentdiscount = @.price_up_rh" & _
"lbuysatxpercentdiscount, price_up_sellatxdiscounttofullprice = @.price_up_sellatx" & _
"discounttofullprice, priceoff_rrp = @.priceoff_rrp, priceoff_percofnewbussellingp" & _
"rice = @.priceoff_percofnewbussellingprice, priceoff_icibuysatxpercentdiscount = " & _
"@.priceoff_icibuysatxpercentdiscount, priceoff_icibuysatx = @.priceoff_icibuysatx," & _
" priceoff_markupper = @.priceoff_markupper, priceoff_absmargin = @.priceoff_absmar" & _
"gin, pricehot_rrp = @.pricehot_rrp, pricehot_percofnewbussellingprice = @.pricehot" & _
"_percofnewbussellingprice, pricehot_icibuysatxpercentdiscount = @.pricehot_icibuy" & _
"satxpercentdiscount, pricehot_icibuysatx = @.pricehot_icibuysatx, pricehot_markup" & _
"per = @.pricehot_markupper, pricehot_absmargin = @.pricehot_absmargin, price828_rr" & _
"p = @.price828_rrp, price828_percofnewbussellingprice = @.price828_percofnewbussel" & _
"lingprice, price828_icibuysatxpercentdiscount = @.price828_icibuysatxpercentdisco" & _
"unt, price828_icibuysatx = @.price828_icibuysatx, price828_markupper = @.price828_" & _
"markupper, price828_absmargin = @.price828_absmargin, price_installtime = @.price_" & _
"installtime, price_quotetext = @.price_quotetext, price_defaultnote_1 = @.price_de" & _
"faultnote_1, price_defaultnote_2 = @.price_defaultnote_2, priceoff_calcrrp = @.pri" & _
"ceoff_calcrrp, pricehot_calcrrp = @.pricehot_calcrrp, price828_calcrrp = @.price82" & _
"8_calcrrp, priceoff_calccost = @.priceoff_calccost, pricehot_calccost = @.pricehot" & _
"_calccost, price828_calccost = @.price828_calccost, priceoff_calcsellprice = @.pri" & _
"ceoff_calcsellprice, pricehot_calcsellprice = @.pricehot_calcsellprice, price828_" & _
"calcsellprice = @.price828_calcsellprice, priceoff_calcmargin = @.priceoff_calcmar" & _
"gin, pricehot_calcmargin = @.pricehot_calcmargin, price828_calcmargin = @.price828" & _
"_calcmargin, priceoff_calcmarginper = @.priceoff_calcmarginper, pricehot_calcmarg" & _
"inper = @.pricehot_calcmarginper, price828_calcmarginper = @.price828_calcmarginpe" & _
"r, price_calccost = @.price_calccost, price_calcsellprice = @.price_calcsellprice," & _
" price_calcmargin = @.price_calcmargin, price_calcmarginper = @.price_calcmarginpe" & _
"r, price_up_calccost = @.price_up_calccost, price_up_calcsellprice = @.price_up_ca" & _
"lcsellprice, price_up_calcmargin = @.price_up_calcmargin, price_up_calcmarginper " & _
"= @.price_up_calcmarginper, price_hw_mxcode = @.price_hw_mxcode, price_order = @.pr" & _
"ice_order, price_mxmandatory = @.price_mxmandatory, price_variable = @.price_varia" & _
"ble, price_notes = @.price_notes WHERE (price_ID = @.Original_price_ID) AND (price" & _
"828_absmargin = @.Original_price828_absmargin OR @.Original_price828_absmargin IS " & _
"NULL AND price828_absmargin IS NULL) AND (price828_calccost = @.Original_price828" & _
"_calccost OR @.Original_price828_calccost IS NULL AND price828_calccost IS NULL) " & _
"AND (price828_calcmargin = @.Original_price828_calcmargin OR @.Original_price828_c" & _
"alcmargin IS NULL AND price828_calcmargin IS NULL) AND (price828_calcmarginper =" & _
" @.Original_price828_calcmarginper OR @.Original_price828_calcmarginper IS NULL AN" & _
"D price828_calcmarginper IS NULL) AND (price828_calcrrp = @.Original_price828_cal" & _
"crrp OR @.Original_price828_calcrrp IS NULL AND price828_calcrrp IS NULL) AND (pr" & _
"ice828_calcsellprice = @.Original_price828_calcsellprice OR @.Original_price828_ca" & _
"lcsellprice IS NULL AND price828_calcsellprice IS NULL) AND (price828_markupper " & _
"= @.Original_price828_markupper OR @.Original_price828_markupper IS NULL AND price" & _
"828_markupper IS NULL) AND (price828_percofnewbussellingprice = @.Original_price8" & _
"28_percofnewbussellingprice OR @.Original_price828_percofnewbussellingprice IS NU" & _
"LL AND price828_percofnewbussellingprice IS NULL) AND (price828_icibuysatx = @.Or" & _
"iginal_price828_icibuysatx OR @.Original_price828_icibuysatx IS NULL AND price828" & _
"_icibuysatx IS NULL) AND (price828_icibuysatxpercentdiscount = @.Original_price82" & _
"8_icibuysatxpercentdiscount OR @.Original_price828_icibuysatxpercentdiscount IS N" & _
"ULL AND price828_icibuysatxpercentdiscount IS NULL) AND (price828_rrp = @.Origina" & _
"l_price828_rrp OR @.Original_price828_rrp IS NULL AND price828_rrp IS NULL) AND (" & _
"price_absmargin = @.Original_price_absmargin OR @.Original_price_absmargin IS NULL" & _
" AND price_absmargin IS NULL) AND (price_calccost = @.Original_price_calccost OR " & _
"@.Original_price_calccost IS NULL AND price_calccost IS NULL) AND (price_calcmarg" & _
"in = @.Original_price_calcmargin OR @.Original_price_calcmargin IS NULL AND price_" & _
"calcmargin IS NULL) AND (price_calcmarginper = @.Original_price_calcmarginper OR " & _
"@.Original_price_calcmarginper IS NULL AND price_calcmarginper IS NULL) AND (pric" & _
"e_calcsellprice = @.Original_price_calcsellprice OR @.Original_price_calcsellprice" & _
" IS NULL AND price_calcsellprice IS NULL) AND (price_code = @.Original_price_code" & _
" OR @.Original_price_code IS NULL AND price_code IS NULL) AND (price_code_number " & _
"= @.Original_price_code_number OR @.Original_price_code_number IS NULL AND price_c" & _
"ode_number IS NULL) AND (price_defaultnote_1 = @.Original_price_defaultnote_1 OR " & _
"@.Original_price_defaultnote_1 IS NULL AND price_defaultnote_1 IS NULL) AND (pric" & _
"e_defaultnote_2 = @.Original_price_defaultnote_2 OR @.Original_price_defaultnote_2" & _
" IS NULL AND price_defaultnote_2 IS NULL) AND (price_description = @.Original_pri" & _
"ce_description OR @.Original_price_description IS NULL AND price_description IS N" & _
"ULL) AND (price_hw_mxcode = @.Original_price_hw_mxcode OR @.Original_price_hw_mxco" & _
"de IS NULL AND price_hw_mxcode IS NULL) AND (price_installtime = @.Original_price" & _
"_installtime OR @.Original_price_installtime IS NULL AND price_installtime IS NUL" & _
"L) AND (price_markupper = @.Original_price_markupper OR @.Original_price_markupper" & _
" IS NULL AND price_markupper IS NULL) AND (price_mxmandatory = @.Original_price_m" & _
"xmandatory) AND (price_notes = @.Original_price_notes OR @.Original_price_notes IS" & _
" NULL AND price_notes IS NULL) AND (price_order = @.Original_price_order OR @.Orig" & _
"inal_price_order IS NULL AND price_order IS NULL) AND (price_icibuysatx = @.Origi" & _
"nal_price_icibuysatx OR @.Original_price_icibuysatx IS NULL AND price_icibuysatx " & _
"IS NULL) AND (price_icibuysatxpercentdiscount = @.Original_price_icibuysatxpercen" & _
"tdiscount OR @.Original_price_icibuysatxpercentdiscount IS NULL AND price_icibuys" & _
"atxpercentdiscount IS NULL) AND (price_rrp = @.Original_price_rrp OR @.Original_pr" & _
"ice_rrp IS NULL AND price_rrp IS NULL) AND (price_type = @.Original_price_type OR" & _
" @.Original_price_type IS NULL AND price_type IS NULL) AND (price_up_calccost = @." & _
"Original_price_up_calccost OR @.Original_price_up_calccost IS NULL AND price_up_c" & _
"alccost IS NULL) AND (price_up_calcmargin = @.Original_price_up_calcmargin OR @.Or" & _
"iginal_price_up_calcmargin IS NULL AND price_up_calcmargin IS NULL) AND (price_u" & _
"p_calcmarginper = @.Original_price_up_calcmarginper OR @.Original_price_up_calcmar" & _
"ginper IS NULL AND price_up_calcmarginper IS NULL) AND (price_up_calcsellprice =" & _
" @.Original_price_up_calcsellprice OR @.Original_price_up_calcsellprice IS NULL AN" & _
"D price_up_calcsellprice IS NULL) AND (price_up_icibuysatxpercentdiscount = @.Ori" & _
"ginal_price_up_icibuysatxpercentdiscount OR @.Original_price_up_icibuysatxpercent" & _
"discount IS NULL AND price_up_icibuysatxpercentdiscount IS NULL) AND (price_up_s" & _
"ellatxdiscounttofullprice = @.Original_price_up_sellatxdiscounttofullprice OR @.Or" & _
"iginal_price_up_sellatxdiscounttofullprice IS NULL AND price_up_sellatxdiscountt" & _
"ofullprice IS NULL) AND (price_variable = @.Original_price_variable) AND (priceho" & _
"t_absmargin = @.Original_pricehot_absmargin OR @.Original_pricehot_absmargin IS NU" & _
"LL AND pricehot_absmargin IS NULL) AND (pricehot_calccost = @.Original_pricehot_c" & _
"alccost OR @.Original_pricehot_calccost IS NULL AND pricehot_calccost IS NULL) AN" & _
"D (pricehot_calcmargin = @.Original_pricehot_calcmargin OR @.Original_pricehot_cal" & _
"cmargin IS NULL AND pricehot_calcmargin IS NULL) AND (pricehot_calcmarginper = @." & _
"Original_pricehot_calcmarginper OR @.Original_pricehot_calcmarginper IS NULL AND " & _
"pricehot_calcmarginper IS NULL) AND (pricehot_calcrrp = @.Original_pricehot_calcr" & _
"rp OR @.Original_pricehot_calcrrp IS NULL AND pricehot_calcrrp IS NULL) AND (pric" & _
"ehot_calcsellprice = @.Original_pricehot_calcsellprice OR @.Original_pricehot_calc" & _
"sellprice IS NULL AND pricehot_calcsellprice IS NULL) AND (pricehot_markupper = " & _
"@.Original_pricehot_markupper OR @.Original_pricehot_markupper IS NULL AND priceho" & _
"t_markupper IS NULL) AND (pricehot_percofnewbussellingprice = @.Original_pricehot" & _
"_percofnewbussellingprice OR @.Original_pricehot_percofnewbussellingprice IS NULL" & _
" AND pricehot_percofnewbussellingprice IS NULL) AND (pricehot_icibuysatx = @.Orig" & _
"inal_pricehot_icibuysatx OR @.Original_pricehot_icibuysatx IS NULL AND pricehot_r" & _
"hlbuysatx IS NULL) AND (pricehot_icibuysatxpercentdiscount = @.Original_pricehot_" & _
"icibuysatxpercentdiscount OR @.Original_pricehot_icibuysatxpercentdiscount IS NUL" & _
"L AND pricehot_icibuysatxpercentdiscount IS NULL) AND (pricehot_rrp = @.Original_" & _
"pricehot_rrp OR @.Original_pricehot_rrp IS NULL AND pricehot_rrp IS NULL) AND (pr" & _
"iceoff_absmargin = @.Original_priceoff_absmargin OR @.Original_priceoff_absmargin " & _
"IS NULL AND priceoff_absmargin IS NULL) AND (priceoff_calccost = @.Original_price" & _
"off_calccost OR @.Original_priceoff_calccost IS NULL AND priceoff_calccost IS NUL" & _
"L) AND (priceoff_calcmargin = @.Original_priceoff_calcmargin OR @.Original_priceof" & _
"f_calcmargin IS NULL AND priceoff_calcmargin IS NULL) AND (priceoff_calcmarginpe" & _
"r = @.Original_priceoff_calcmarginper OR @.Original_priceoff_calcmarginper IS NULL" & _
" AND priceoff_calcmarginper IS NULL) AND (priceoff_calcrrp = @.Original_priceoff_" & _
"calcrrp OR @.Original_priceoff_calcrrp IS NULL AND priceoff_calcrrp IS NULL) AND " & _
"(priceoff_calcsellprice = @.Original_priceoff_calcsellprice OR @.Original_priceoff" & _
"_calcsellprice IS NULL AND priceoff_calcsellprice IS NULL) AND (priceoff_markupp" & _
"er = @.Original_priceoff_markupper OR @.Original_priceoff_markupper IS NULL AND pr" & _
"iceoff_markupper IS NULL) AND (priceoff_percofnewbussellingprice = @.Original_pri" & _
"ceoff_percofnewbussellingprice OR @.Original_priceoff_percofnewbussellingprice IS" & _
" NULL AND priceoff_percofnewbussellingprice IS NULL) AND (priceoff_icibuysatx = " & _
"@.Original_priceoff_icibuysatx OR @.Original_priceoff_icibuysatx IS NULL AND price" & _
"off_icibuysatx IS NULL) AND (priceoff_icibuysatxpercentdiscount = @.Original_pric" & _
"eoff_icibuysatxpercentdiscount OR @.Original_priceoff_icibuysatxpercentdiscount I" & _
"S NULL AND priceoff_icibuysatxpercentdiscount IS NULL) AND (priceoff_rrp = @.Orig" & _
"inal_priceoff_rrp OR @.Original_priceoff_rrp IS NULL AND priceoff_rrp IS NULL); S" & _
"ELECT price_ID, price_type, price_code_number, price_code, price_description, pr" & _
"ice_rrp, price_icibuysatxpercentdiscount, price_icibuysatx, price_markupper, pri" & _
"ce_absmargin, price_up_icibuysatxpercentdiscount, price_up_sellatxdiscounttofull" & _
"price, priceoff_rrp, priceoff_percofnewbussellingprice, priceoff_icibuysatxperce" & _
"ntdiscount, priceoff_icibuysatx, priceoff_markupper, priceoff_absmargin, priceho" & _
"t_rrp, pricehot_percofnewbussellingprice, pricehot_icibuysatxpercentdiscount, pr" & _
"icehot_icibuysatx, pricehot_markupper, pricehot_absmargin, price828_rrp, price82" & _
"8_percofnewbussellingprice, price828_icibuysatxpercentdiscount, price828_icibuys" & _
"atx, price828_markupper, price828_absmargin, price_installtime, price_quotetext," & _
" price_defaultnote_1, price_defaultnote_2, priceoff_calcrrp, pricehot_calcrrp, p" & _
"rice828_calcrrp, priceoff_calccost, pricehot_calccost, price828_calccost, priceo" & _
"ff_calcsellprice, pricehot_calcsellprice, price828_calcsellprice, priceoff_calcm" & _
"argin, pricehot_calcmargin, price828_calcmargin, priceoff_calcmarginper, priceho" & _
"t_calcmarginper, price828_calcmarginper, price_calccost, price_calcsellprice, pr" & _
"ice_calcmargin, price_calcmarginper, price_up_calccost, price_up_calcsellprice, " & _
"price_up_calcmargin, price_up_calcmarginper, price_hw_mxcode, price_order, price" & _
"_mxmandatory, price_variable, price_notes FROM prices WHERE (price_ID = @.price_I" & _
"D) ORDER BY price_description"
Pretty lengthy?*!$!
The Create statement I don't have, as the database was converted fromAccess. Other tables from it work ok, though, after they had theprimary key put back in. The data types are a mixture ofmoney & real - some of those were empty, and I did tryputting 0's in them prior to the update in case it was that, but Ithought it shouldn't really be necessary and it had no effect. Also, there are some ints and nvarchars and bits, and one each ofvarchar and ntext.
Is that enough? It would be, as I said, extremely terrific if you could help.
Thanks,
Graham Rounce
|||

There is little difference between Float and Real in SQL Server because the synonym for Real is Float. Try to change that to Decimal or Numeric and I have problem with your ISNULL because it replaces NULL values with 0. Try the links below to see how to deal with NULL values and see if you can use a CASE statement. One more thing COUNT (*) is the only aggregate function in SQL Server that calculates NULL values but you don't have to use ISNULL all th time. Post again if the problem continues and the SQL Server BOL (books online) is the best in the business but you will do better with Boolean searches. Hope this helps.
http://www.craigsmullins.com/ssu_0899.htm

http://www.akadia.com/services/dealing_with_null_values.html

|||

Hiagain, and thanks for the reply. Having slept on it, I thought I'd tryreducing the size of the query by not including variouscolumns when configuring the DataAdaptor in Visual Studio. Iun-included half of them, and it worked fine. Then Iprogressively re-included blocks of columns until it stopped workingagain. Below is the column list. The ones marked '?'are the ones which, when included, cause the error to happen (and when re-un-included stop it happening again!).
They don't look very different to other columns in the table, and I'mthinking it was just the sheer size of the generated query thatdefeated the server.
Unless you disagree, this looks like a bug? Of course, onewouldn't expect to be able to use infinite-sized tables, but I'd expect a relevant warning, not an inexplicable error?

Thanks,

Graham Rounce

int 4 included
nvarchar 50 included
int 4 included
nvarchar 50 included
nvarchar 255 included
money 8 included
real 4 included
money 8 included
real 4 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
money 8 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
money 8 included
money 8 included
real 4 included
real 4 included
money 8 included
real 4 included
money 8 included
real 4 included
ntext 16 included
nvarchar 50 included
nvarchar 50 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
money 8 included
real 4 ?
real 4 ?
real 4 ?
money 8 ?
money 8 ?
money 8 ?
real 4 not included
money 8 not included
money 8 not included
money 8 not included
real 4 not included
nvarchar 20 included
int 4 included
bit 1 included
bit 1 included
varchar 8000 included

|||It is not the size of your query but change all the Real and Money to Decimal or Numeric. The numeric data in the Real column is over flowing, and I know there are existing issues with money rounding to less than a dollar going back to 1999. In Decimal and Numeric remember to set the precission and scale. Post again and be patient. Hope this helps.|||Hi again, and thanks. Just one more q if you don'tmind. Can I just change those types in 'Design Table' in theEnterprise Manager? I mean, it looks like I can, but will thecontents still be ok? I'm using the table at the momentwithout about a third of the columns as they didn't really need to bethere anyway, but it seems like it would be good practice to sort itout.
Can I just go in and change the types? It can't really be as easy as that? (Smiley thing)
Graham Rounce
|||I don't mind at all just open the BOL (books online) search pane and enter ALTER table, the first answer covered all you need. Hope this helps.|||It looks like Alter Table does everything except make the tea! (Andthat could be in there somewhere, too). I'm quite happy to changethe Types in Enterprise Manager, as it's only a one-offconversion. Is that likely to cause any problems, as long as Ichange the prog to suit?
Thanks again,

|||I don't think it will cause any problem and I am glad to see you are close to a solution. Hope this helps.|||Yes indeed. Thanks very much.

Thursday, February 16, 2012

**SQL-DMO Error 21776

Hi
I want to change the sa password ,but following error appearred,
"Error 21776; SQL-DMO the name 'dbo' was not found in the user collection
,if the name is a qualified name,use [] to seperate various parts of the
name, and try again."
I'd be grateful if anybody can help me.Hi
Will these help:
http://support.microsoft.com/defaul...kb;en-us;218172
or possibly:
http://support.microsoft.com/defaul...kb;en-us;239133
John
"maryam rezvani" wrote:

> Hi
> I want to change the sa password ,but following error appearred,
> "Error 21776; SQL-DMO the name 'dbo' was not found in the user collection
> ,if the name is a qualified name,use [] to seperate various parts of the
> name, and try again."
> I'd be grateful if anybody can help me.
>
>
>

**login problem**

Hi
I am working with SQL 2000 and I have a resotred database with these users
in branch of its users :
DB1
user login
-- --
dbo X
L1 L1
L2 L2
but in other databases I have the following list:
DB2
user login
-- --
dbo sa
X X
L1 L1
L2 L2
I always create login names and user names with the same name, so in
related databases they will appear like above and a row of (dbo,sa) which
is default in each database.now I want to change the list of my first
database in order to be similar like the second list. how does it
possible? in other word,how can I have the row of (dbo,X) in 2 rows of
(dbo,sa) , (X,X)?
Thank you in advance.Hi
Lookup sp_changedbowner stored procedure in the BOL
<R> wrote in message news:ops47g3iu4mw7tkz@.system109.parskhazar.net...
> Hi
> I am working with SQL 2000 and I have a resotred database with these users
> in branch of its users :
> DB1
> user login
> -- --
> dbo X
> L1 L1
> L2 L2
> but in other databases I have the following list:
> DB2
> user login
> -- --
> dbo sa
> X X
> L1 L1
> L2 L2
> I always create login names and user names with the same name, so in
> related databases they will appear like above and a row of (dbo,sa) which
> is default in each database.now I want to change the list of my first
> database in order to be similar like the second list. how does it
> possible? in other word,how can I have the row of (dbo,X) in 2 rows of
> (dbo,sa) , (X,X)?
> Thank you in advance.|||Uri's answer was correct but you may find these useful for stuff along those
lines:
http://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://msdn2.microsoft.com/en-us/library/ms345408(en-US,SQL.90).aspx Moving
system dbs 2005
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system DB's 2000
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=221465 Using WITH MOVE in a Restore
http://www.sqlservercentral.com/columnists/cBunch/movingyouruserswiththeirdatabases.asp
Moving Users
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after
a Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=320125 Moving a Diagram
http://www.support.microsoft.com/?id=274463 Copy DB Wizard issues 2000
http://www.sqlservercentral.com/scripts/contributions/1598.asp Script
Roles and Permissions
--
Andrew J. Kelly SQL MVP
<R> wrote in message news:ops47g3iu4mw7tkz@.system109.parskhazar.net...
> Hi
> I am working with SQL 2000 and I have a resotred database with these users
> in branch of its users :
> DB1
> user login
> -- --
> dbo X
> L1 L1
> L2 L2
> but in other databases I have the following list:
> DB2
> user login
> -- --
> dbo sa
> X X
> L1 L1
> L2 L2
> I always create login names and user names with the same name, so in
> related databases they will appear like above and a row of (dbo,sa) which
> is default in each database.now I want to change the list of my first
> database in order to be similar like the second list. how does it
> possible? in other word,how can I have the row of (dbo,X) in 2 rows of
> (dbo,sa) , (X,X)?
> Thank you in advance.

**let user to backup a DB and not allowed to restore it**

Hi
I defined following properties to let a user to backup and restore the DB
which he is the owner of it.
in server role tab: - database creator (selected)
in database access tab(for DB1): - public(selected)
- db_owner(selected)
- db_backupoperator (selected)
now ,how can I set property in a way that just let the owner user, to
backup from DB1 but not able to restore one the backup over DB1?
Any help would be thankful.
hi,
u can set a trigger on restorebackup table.
hope this help
from
doller
|||Hi,
With a Db_owner and db_backupoperator role assigned an user can not restore
the SQL Server database. For
Restore database the user either needs to be iin one of the server sides
role - db_creator or Sysadmin role.
But any way by using Db_owner and db_backupoperator role you can Backup the
database.
Thanks
Hari
SQL Server MV
<R> wrote in message news:opswvowel3mw7tkz@.system109.parskhazar.net...
> Hi
> I defined following properties to let a user to backup and restore the DB
> which he is the owner of it.
> in server role tab: - database creator (selected)
> in database access tab(for DB1): - public (selected)
> - db_owner (selected)
> - db_backupoperator (selected)
> now ,how can I set property in a way that just let the owner user, to
> backup from DB1 but not able to restore one the backup over DB1?
> Any help would be thankful.

**let user to backup a DB and not allowed to restore it**

Hi
I defined following properties to let a user to backup and restore the DB
which he is the owner of it.
in server role tab: - database creator (selected)
in database access tab(for DB1): - public (selected)
- db_owner (selected)
- db_backupoperator (selected)
now ,how can I set property in a way that just let the owner user, to
backup from DB1 but not able to restore one the backup over DB1?
Any help would be thankful.hi,
u can set a trigger on restorebackup table.
hope this help
from
doller|||Hi,
With a Db_owner and db_backupoperator role assigned an user can not restore
the SQL Server database. For
Restore database the user either needs to be iin one of the server sides
role - db_creator or Sysadmin role.
But any way by using Db_owner and db_backupoperator role you can Backup the
database.
Thanks
Hari
SQL Server MV
<R> wrote in message news:opswvowel3mw7tkz@.system109.parskhazar.net...
> Hi
> I defined following properties to let a user to backup and restore the DB
> which he is the owner of it.
> in server role tab: - database creator (selected)
> in database access tab(for DB1): - public (selected)
> - db_owner (selected)
> - db_backupoperator (selected)
> now ,how can I set property in a way that just let the owner user, to
> backup from DB1 but not able to restore one the backup over DB1?
> Any help would be thankful.

**let user to backup a DB and not allowed to restore it**

Hi
I defined following properties to let a user to backup and restore the DB
which he is the owner of it.
in server role tab: - database creator (selected)
in database access tab(for DB1): - public (selected)
- db_owner (selected)
- db_backupoperator (selected)
now ,how can I set property in a way that just let the owner user, to
backup from DB1 but not able to restore one the backup over DB1?
Any help would be thankful.hi,
u can set a trigger on restorebackup table.
hope this help
from
doller|||Hi,
With a Db_owner and db_backupoperator role assigned an user can not restore
the SQL Server database. For
Restore database the user either needs to be iin one of the server sides
role - db_creator or Sysadmin role.
But any way by using Db_owner and db_backupoperator role you can Backup the
database.
Thanks
Hari
SQL Server MV
<R> wrote in message news:opswvowel3mw7tkz@.system109.parskhazar.net...
> Hi
> I defined following properties to let a user to backup and restore the DB
> which he is the owner of it.
> in server role tab: - database creator (selected)
> in database access tab(for DB1): - public (selected)
> - db_owner (selected)
> - db_backupoperator (selected)
> now ,how can I set property in a way that just let the owner user, to
> backup from DB1 but not able to restore one the backup over DB1?
> Any help would be thankful.

Saturday, February 11, 2012

(urgent) Help needed for this insert statement

Hi,

I am trying to insert the Source name and clientId to a table called clientSource...

The User will send in some Dbf File... So in a particular file called PlanDbf.. I have the following fields

PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name... SRC20Name

170234 Constructions Employee Deferral Employer Discretionary Employer Matching...

And in another table called SourceDBF i have the following fields with data

PlanNumber PARTID SOURCE_NUM etc...

170234 123456789 1

170234 123456789 3

170234 451231334 1

So how do i match the Source_NUM with SRCnames when i insert it into the table..

INSERT INTO Statement..ClientSource
(
@.ClientId,
SourceName
)
SELECT
s.SOURCE_NUM

 FROM SourceDBF ..

but i am stuck..

any help will be appreciated.

Regards

Karen

I cant see a connection between them. How are they related?

|||

The 2 tables are related by the plan number...

and will know by taking a look at the PlanDBF if its 1 then to pull ... SRC1Name...

|||

Sorry I still cant get it.. can you just post few rows from each tables and expected output so its easier to understand (at least for me)..

|||

this is how the table that i need to insert is ...

SourceId (int identity) ClientID SourceName

26 1 DEFERRAL27 1 EMPLOYER28 1 MATCHING29 1 ROLLUnre30 1 UNCASHED31 1 ROLL 32 5 DEFERRAL33 5 ROLL 34 5 EMPLOYER35 5 MATCHING

And i will be getting the Data the source name from the Table...

PlanDBF (which has one row of data)

PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name... SRC20Name

170234 Constructions Employee Deferral Employer Discretionary Employer Matching...

and then once i insert the data to the above table called source.. I am inserting the data from the Source Summary which is as follows

PlanNumber PartId Source_NUM begbal dep withdrawels etc..

170925 1296027 1 6211.63 325.00 0.00 211.03 0.00 6747.66 100.06747.66 True170925 1296027 3 2887.36 0.00 0.00 108.38 0.00 2995.74 80.002396.59 True170925 1486037 1 174.64 0.00 0.00 11.13 0.00 185.77 100.0185.77 True170925 1733690 1 2396.84 1625.00 0.00 181.37 0.00 4203.21 100.04203.21 True170925 1733690 3 770.72 0.00 0.00 49.89 0.00 820.61 0.000.00 True170925 2294760 1 4870.82 650.00 0.00 282.52 0.00 5803.34 100.05803.34 True170925 2294760 3 2972.46 0.00 0.00 148.26 0.00 3120.72 20.00624.15 True170925 2443743 1 1421.74 650.00 0.00 42.43 0.00 2114.17 100.02114.17 True170925 2443743 3 611.00 0.00 0.00 18.16 0.00 629.16 20.00125.83 True170925 2662592 1 2857.15 1300.00 0.00 131.71 0.00 4288.86 100.04288.86 True

to another table ParticipantSourceSummary which has the has the follow fields

Plan Number PartId SourceId (which will be got from the clientSource table) and the remaining fields from the SourceSummary table

So at the end while i generate a report i want Source_NUm 1 to be whats in SRCName and so on

Hope this is clear.

Regards

Karen

|||

CREATE VIEW vw_Sources AS

SELECT PlanNumber,Src1Name AS SourceName,1 AS Source_Num

FROM PlanDBF

UNION ALL

SELECT PlanNumber,Src2Name AS SourceName,2 AS Source_Num

FROM PlanDBF

UNION ALL

SELECT PlanNumber,Src3Name AS SourceName,3 AS Source_Num

FROM PlanDBF

{repeat 17 more times}

--

Not sure of all your relations, but then you can do something like this:

SELECT s.PlanNumber,s.PartID,s.Source_Num,vw_Sources.SourceName

FROM SourceDBF s

JOIN vw_Sources ON s.PlanNumber=vw_Sources.PlanNumber AND s.SOURCE_NUM=vw_Sources.SOURCE_NUM

|||

Alternatively, you can do this:

SELECT s.PlanNUmber,s.PartID,s.Source_Num,

(SELECT CASE s.Source_Num WHEN 1 THEN Src1Name WHEN 2 THEN Src2Name WHEN 3 THEN Src3Name ... END FROM PlanDBF p WHERE p.PlanNumber=s.PlanNumber) AS SourceName

FROM SourceDBF

|||

Motley,

Thanks a lot.. the solution works for me

Regards,

Karen

(This site might require the following ActiveX control: ‘Microsoft SQL Server 2000 Reporting Ser

When a user tries to print using client side printing it gives the following error:-

(This site might require the following ActiveX control: ‘Microsoft SQL Server 2000 Reporting Services’ from Microsoft Corporation.’ Click here to install.)

My client machines have ie 6 sp 2 already installed.

Do I need to install anything on my client machines as well? I have over 100 clients ?
Full fidelity printing requires downloading an ActiveX control to the client. You can turn off the print icon if this is not acceptable.|||Hi .

How can i turn off my print icon in report toolbar. i am using sql server 2000.

Thanks in advance,
Sanket.

(This site might require the following ActiveX control: ‘Microsoft SQL Server 2000 Reporting Ser

When a user tries to print using client side printing it gives the following error:-

(This site might require the following ActiveX control: ‘Microsoft SQL Server 2000 Reporting Services’ from Microsoft Corporation.’ Click here to install.)

My client machines have ie 6 sp 2 already installed.

Do I need to install anything on my client machines as well? I have over 100 clients ?
Full fidelity printing requires downloading an ActiveX control to the client. You can turn off the print icon if this is not acceptable.|||Hi .

How can i turn off my print icon in report toolbar. i am using sql server 2000.

Thanks in advance,
Sanket.

(This site might require the following ActiveX control: ‘Microsoft SQL Server 2000 Reporting

When a user tries to print using client side printing it gives the following error:-

(This site might require the following ActiveX control: ‘Microsoft SQL Server 2000 Reporting Services’ from Microsoft Corporation.’ Click here to install.)

My client machines have ie 6 sp 2 already installed.

Do I need to install anything on my client machines as well? I have over 100 clients ?
Full fidelity printing requires downloading an ActiveX control to the client. You can turn off the print icon if this is not acceptable.|||Hi .

How can i turn off my print icon in report toolbar. i am using sql server 2000.

Thanks in advance,
Sanket.