Collation problem: hii

Showing posts with label hii. Show all posts

Thursday, March 29, 2012

Combining transactional and snapshot replication

Hi

I am in the process of setting up replication between two SQL boxes - the first being the production SQL Server and the second being a secondary server which is to be used as part of a web portal solution. (In fact there are two pairs of SQL boxes - SQL 7 and SQL 2k, one of each on the LAN and one of each at the hosting site.)

We need to have near realtime replication, so transactional replication is the desired mechanism. The trouble is that not all tables have primary keys defined. Oh yes, and the main database is 20G in size and has >900 user tables! :-0 (The other databases are much better behaved!)

When I set up transactional replication, I am allowed only to include tables which have PKs defined. AFAIK, the initial transactional replication snapshot will also only include these same tables.

If I set up snapshot replication (separately), I can include all the tables in the database. However, I cannot then replicate in real time.

Can I combine the two replication schemes to deliver updates to the same target database:
- transactional replication delivering realtime updates to the tables with PKs during the day and
- snapshot replication updating all the tables once per 24hrs at night?

Or is there a better way of doing this?

I am not sure whether I can modify the existing schemas, as some of the databases are 'maintained' by an external provider. Even if I could, if I had to add a column to have a PK, I would potentially be adding to my diskspace requirement rather significantly...

TIACertainly, you can setup two publication - one for those tables with no PK and one for those tables with PK. They will have the same subscriber and destination but the one will be snapshot and one will be transactional. The other option you have is to create a surrogate key for each table without PK. I don't know how feasible it is since your database schema is controlled by the person other than you. You can also consider Log Shipping if the function of the subscriber is read-only.|||Thank you for your quick response. I'll set up two publications as you suggest. For some reason I was hung up on including the PK tables in both publications and I was wondering why I was getting errors... <doh!>

Regarding log shipping, I've had a quick look around for info. It seems that it is not as easy as "standard" replication to set up and keep tabs on. Also, in my environment, although the replicated database will be read only on the target, one of the target SQL boxes will have a database which will capture input from web-connected users. Will this make a difference? Do you think that log shipping will be the better solution for me?

I appreciate your help

Tuesday, March 27, 2012

Combining multiple tables - please help!

Hii all.

quite urgent... I have 3 matix tables.. all the same row headings. I need to be able to make these visible/invisible depending on the user parameters. The reason for this, if a user hides table on the left.. the middle table needs to show the row headings. and last table must not.

The problem:
When making the row headings invisible it doesnt exactly chop off the textboxes... so the tables look disjoint.. and a big gap appears between both tables...

Is there a way to join these tables without leavings gaps ?

any help is appreciated.
Neil
Try putting each matrix inside a rectangle, then hide the rectangle containing the table based on the user parameter.

Additionally, put all 3 rectangles within one parent rectangle. Should help with the spacing.|||Hi Andy

thanks for the reply!

While I like that idea and provides a lot more control over the positioning of the tables.. the problem still exits...

It seems that making a textbox "visibility = true" just produces an empty textbox..but still leaves the space allocated to textbox in place... and therefore the gap.. the rectangles dont seem to help.. unless im doing something wrong..

Does this seem right ?
|||I also just tried making the middle rectangle hidden = true and it didnt close the gap as expected.. very confused.. I just draggged the rectangles into the parent rectangle... ?

very strange.. any idea's why this may not be working for me ?
|||Well now I'm confused about what you're doing. One partyou're talking about matrixes, the next you're talking about tables. Which are you using? Matrixes or tables? Do you have 3 separate matrixes or 3 rows in one matrix?

Have you tried setting the visibility property for the whole row (or column) instead of the individual textboxes?|||

Using Matrix

Select appropriate textbox in matrix

Right click

Select Edit Group...

Visibility tab

Select Expression

Add your expression

It will not work if you put it on the textbox properties.

Using Table

Select appropriate column

Go to properties

Go down to visibility tag and select expression

Add your expression

You can use a variety of expressions to hide the columns like parameters or counts.

=IIF(Fields!City.Value=@.City, FALSE, TRUE)

=IIF(Trim(Fields!City.Value) = "", TRUE, FALSE)

Hope this helps

|||Sorry, I mean matrix...

My problem is not making the whole matrix invisible... I have 3 of them lined from left to right.
All three have the same row (group headings - sorry I should have made that clear). I dont need all the to show the same headings on the row groups. So, I just want to beable to hide these headings depending on the users selection.

The problem is that making the row headings + total header hidden, leaves a gap between the matrices as if the headers were there but just with no text or borders in them (invisible to the human eye but as if its like a brick stopping the matrix from moving closer to the other ones)

Sorry for the confusion.. thanks for helps !
But any idea.. anyone ?
|||Hi Brady,

Thanks for the suggestion.. while I have been trying that.. the problem there is that bigger Textbox located in the top left of the matrix.. that wont be invisible and therefore will stop my matrix from moving closer to the other matrix..

Is there a way to get rid of that Textbox...?
I have tried deleting it , but does not go away...

Thanks!sql sql

Thursday, March 22, 2012

combing cloumns

I am having one table.

in that columns are trid and remarks.

the data is:

trid | remarks

-------

1 travel:comment1

1 travel:comment2

1 emp:emp comment1

1 emp:emp comment2

so i want to display query like in this below format:having 3 columns,i.e trid, travel remarks, empremarks and travelremarks should display data start with 't' , empremarks data should display data starts with 'e'.

I am getting data start with 't' and 'e',but how to combine travel data, emp data. and it should display in one row itself instead of 4 rows for every trid '1'.

trid | travelremarks | emp remarks

-------------------------------------------

1 travel:comment1,travel:comment2 emp:emp comment1,emp:emp comment2

please help me.

Thanks,

swapprose.

SQL Server 2005 solution:

SELECT pvt.trid, pvt.traas'travelremarks', pvt.empas'emp remarks'FROM

(SELECT pt.trid, pt.remarks_left3, pt.[1]+coalesce(','+ pt.[2],'')as Merged

from(SELECT trid,LEFT(remarks, 3)AS remarks_left3, remarks, ROW_NUMBER()OVER(PARTITIONBYLEFT(remarks, 3)ORDERBY remarks)as seqFROM yourTable_2)as t

PIVOT(min(remarks)FOR seqin([1], [2]))as pt)as t1

PIVOT(MIN(t1.Merged)FOR t1.remarks_left3IN([tra], [emp]))AS pvt

|||

Thank you very much.

but i want query in sql server 2000.

its giving error like rownumber() not existing.

so can u plz make query in sql server 2000.

and the data which i gave was only sample data.

i think u done query by considering only 4 rows .i want in generalised way.

i am not getting where r u finding out travel remarks column should display data starts with 't'

and emp rmarks column data should display data starts with 'e'. b'coz i want to split remarks into travel and emp remarks given by travel and emp's.

i want query in a generalised way,b'coz table contains so many trid's ,so many travel remarks,emp

remarks data.

the table is like,just i m giving sample data.so it contains so many trid's ,remarks

trid | remarks

---------

1 travel:option given
1 emp :some comments
2 emp :trid select
2 travel:chosen
1 emp :option1
3 travel:set id
4 emp :choose
5 emp :abc

so i want in generalised way. the order of remarks may come in any way.
please consider all my points.
i m waiting for ur reply.

Thanks,

swapprose.

|||

try this one for sql server 2000:

SELECT t3.trid,max(case t3.remarks_left3when'emp'then t3.remarksend)as emp_category

,max(case t3.remarks_left3when'tra'then t3.remarksend)as travel_category

FROM(select tr.trid, tr.remarks_left3,max(case tr.seqwhen 1then tr.remarksend)

+max(case tr.seqwhen 2then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 3then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 4then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 5then', '+ tr.remarkselse''end)as remarks

from

(select trid,LEFT(remarks, 3)AS remarks_left3, remarks,

(selectcount(*)from yourTable_2as t2

whereLEFT(t2.remarks, 3)=LEFT(t1.remarks, 3)

AND t2.remarks<= t1.remarks)as seqfrom yourTable_2AS t1)as tr

groupby tr.trid, tr.remarks_left3)AS t3

GROUPBY t3.trid

|||

Thank you very much for ur solution.When my table is having only trid no. 1 , its coming properly.

But the thing is when my table is having trid's like 1,2,3,4, then its not working.

when i tried by using trid's no. 1,then its coming.Fine.

if i have trid no's 1,2,3,4 its not coming.

i want in generalised way.plz assume table is having so many trid's,remarks and so many rows.

suppose my table is having data like:

trid remarks

--------
1 emp:aa
1 emp:bb
1 travel:cc
1 travel:dd
2 travel:ee
2 travel:ff
2 travel:gg
2 emp:hh
3 emp:ftgfdg
3 emp:cvgxcv
4 travel:rrtt

can you plz try it once by table having trid's like 1,2,3-- so on.
plz help me.
i m waiting for ur solution.
thanks a lot for ur immediate reply and help.

|||

Hi,

Show the exact result set you want for your sample data set. Do you combine all ids together for one big row?

|||

I'll explain my problem clearly.

assume this is my table having sample data.

trid remarks

--------
1 emp:aa
1 emp:bb
1 travel:cc
1 travel:dd

this works fine .result is like

but when i include data in table like below:

trid travel remarks emp remarks

1 travel:cc,travel:dd emp:aa,emp:bb

--------------------------------------------------------------

when i included more trid's,its not working

trid remarks

--------
1 emp:aa
1 emp:bb
1 travel:cc
1 travel:dd
2 travel:ee
2 travel:ff
2 travel:gg
2 emp:hh
3 emp:ftgfdg
3 emp:cvgxcv
4 travel:rrtt

now table contains trid's like 1,2,3,4 ,then result is not proper.

result is SHOWING like:

trid travel remarks emp remarks

1 travel:cc,travel:dd emp:aa,emp:bb

2 NULL NULL

3 NULL NULL

4 NULL NULL

could u plz tell me the solution.

bye

swapprose.

|||

Here are the fixed solutions for both SQL Server 2000 and 2005:

--SQL Server 2000

SELECT t3.trid,max(case t3.remarks_left3when'emp'then t3.remarksend)as emp_category

,max(case t3.remarks_left3when'tra'then t3.remarksend)as travel_category

FROM(select tr.trid, tr.remarks_left3,max(case tr.seqwhen 1then tr.remarksend)

+max(case tr.seqwhen 2then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 3then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 4then', '+ tr.remarkselse''end)

+max(case tr.seqwhen 5then', '+ tr.remarkselse''end)as remarks

from

(select trid,LEFT(remarks, 3)AS remarks_left3, remarks,

(selectcount(*)from yourTable_2as t2

where t1.trid=t2.tridANDLEFT(t2.remarks, 3)=LEFT(t1.remarks, 3)

AND t2.remarks<= t1.remarks)as seqfrom yourTable_2AS t1)as tr

groupby tr.trid, tr.remarks_left3)AS t3

GROUPBY t3.trid

--SQL Server 2005

SELECT pvt.trid, pvt.traas'travelremarks', pvt.empas'emp remarks'FROM

(SELECT pt.trid, pt.remarks_left3, pt.[1]+coalesce(','+ pt.[2],'')as Merged

from(SELECT trid,LEFT(remarks, 3)AS remarks_left3, remarks, ROW_NUMBER()OVER(PARTITIONBY trid,LEFT(remarks, 3)ORDERBY remarks)as seqFROM yourTable_2)as t

PIVOT(min(remarks)FOR seqin([1], [2]))as pt)as t1

PIVOT(MIN(t1.Merged)FOR t1.remarks_left3IN([tra], [emp]))AS pvt

--The result is:

1 travel:cc,travel:dd emp:aa,emp:bb
2 travel:ee,travel:ff emp:hh
3 NULL emp:cvgxcv,emp:ftgfdg
4 travel:rrtt NULL

|||

Thanks a lot.

Now its working fine for everything.

if u don't mind can u explain me logic.

once again Thank you very much for your immediate help.

Bye,

swapprose.

|||

suppose my table is having data like.

trid pnrno isoptionchosen
------------------
1 100 null
1 101 1
1 102 null
2 200 1
2 201 null

i want query like below.

--------------------------------------------------------------
100 null 101 1 102 null 101 1
200 1 201 null null null 200 1

so i want to display fields from same table accroding to trid's.
first trid='1' : it is having 3 rows,that 3 rows data should display and last isoptionchosen=1 that row data should display.
for trid=2 :its is having 2 rows.that 2 rows data should display and whatever ischosenoption=1 that row should display

Please help me.

Thanks,

bye

swapprose.

|||

SELECT tr.trid,max(case tr.seqwhen 1then tr.pnrnoend)AS pnrno1,max(case tr.seqwhen 1then tr.isoptionchosenend)AS isoptionchosen1,

max(case tr.seqwhen 2then tr.pnrnoend)AS pnrno2,max(case tr.seqwhen 2then tr.isoptionchosenend)AS isoptionchosen2,

max(case tr.seqwhen 3then tr.pnrnoend)AS pnrno3,max(case tr.seqwhen 3then tr.isoptionchosenend)AS isoptionchosen3,

max(case tr.isoptionchosenwhen 1then tr.pnrnoend)AS Chosenpnrno, 1AS Chosenisoptionchosen

FROM(SELECT t2.trid, t2.pnrno, t2.isoptionchosen,(selectcount(*)from yourTable_2as t1where t1.trid=t2.tridAND t1.pnrno<= t2.pnrno)as seq

FROM(select trid, pnrno, isoptionchosenfrom yourTable_2) t2) tr

GROUPBY tr.trid

-- Or

SELECT tt.trid, tt.pnrno1, tt.isoptionchosen1, tt.pnrno2, tt.isoptionchosen2, tt.pnrno3, tt.isoptionchosen3, t4.chosenpnrno, t4.chosenisoptionchosenFROM(SELECT tr.trid,max(case tr.seqwhen 1then tr.pnrnoend)AS pnrno1,max(case tr.seqwhen 1then tr.isoptionchosenend)AS isoptionchosen1,

max(case tr.seqwhen 2then tr.pnrnoend)AS pnrno2,max(case tr.seqwhen 2then tr.isoptionchosenend)AS isoptionchosen2,

max(case tr.seqwhen 3then tr.pnrnoend)AS pnrno3,max(case tr.seqwhen 3then tr.isoptionchosenend)AS isoptionchosen3

FROM(SELECT t2.trid, t2.pnrno, t2.isoptionchosen,

(selectcount(*)from yourTable_2as t1where t1.trid=t2.tridAND t1.pnrno<= t2.pnrno)as seq

FROM(select trid, pnrno, isoptionchosenfrom yourTable_2) t2) tr

GROUPBY tr.trid) tt

INNERJOIN

(SELECT trid, pnrnoas chosenpnrno, isoptionchosenas chosenisoptionchosenFROM yourTable_2WHERE isoptionchosen=1)AS t4

ON t4.trid=tt.trid

|||

Thanks a lot.

i have one more query.i.e

can u show seq.number like 1,2 ,3 for this result.

Thanks,

swapprose.

|||

SELECT tr.trid,max(case tr.seqwhen 1then tr.pnrnoend)AS pnrno1,max(case tr.seqwhen 1then tr.isoptionchosenend)AS isoptionchosen1, 1as seq1,

max(case tr.seqwhen 2then tr.pnrnoend)AS pnrno2,max(case tr.seqwhen 2then tr.isoptionchosenend)AS isoptionchosen2, 2as seq2,

max(case tr.seqwhen 3then tr.pnrnoend)AS pnrno3,max(case tr.seqwhen 3then tr.isoptionchosenend)AS isoptionchosen3, 3as seq3,

max(case tr.isoptionchosenwhen 1then tr.pnrnoend)AS Chosenpnrno, 1AS Chosenisoptionchosen

FROM(SELECT t2.trid, t2.pnrno, t2.isoptionchosen,(selectcount(*)from yourTable_2as t1where t1.trid=t2.tridAND t1.pnrno<= t2.pnrno)as seq

FROM(select trid, pnrno, isoptionchosenfrom yourTable_2) t2) tr

GROUPBY tr.trid

|||

Thanks for ur reply.

but my question is something different.

suppose my table is having data like

empname course

-----------

john m.s

rechel b.e

jack p.g

so i wnat sequence no. for these rows.

sequence no. empname course

-----------------

1 john m.s

2 recehl b.e

3 jack p.g

Thanks.

swapprose.

Monday, March 19, 2012

Comapre two values in same resultsets

I want to compare values of two fields in same resultset. Like I have resultset whrre there are two fields Frequency and New_Frequency I want to compare this two like if

Frequency = New_Frequency then do some logic.

I tried with derived columun using Frequency == New_NewFrequency but it doesn't work.

Let me know is there any way to compare?

Dnyandeo

for derived column use in such a way

a = case when frequency = new_frequency then

begin

some logic

end

else

some logic

end

or can u send me the script code that u had used for derived column?

Wednesday, March 7, 2012

Column update structure change

hi
I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server

or any good recomendations of a action to accomplish this in the best and
fastest way.

thanks davepDave,

First of all, SQL Server maximum rowsize = 8,060 bytes. Make sure you keep
under that limit when you add the #bytes from all of the other columns.

Access to the table, and possibly other objects that are dependent on that
table will be blocked until the schema change is complete. Also, other
non-dependent processes will also be slowed down significantly. A million
row table on recent sever technology should not take an excessive amount of
time to alter.

-- Bill

"DaveP" <dvs_bis@.sbcglobal.netwrote in message
news:S3Jyh.59511$wc5.47614@.newssvr25.news.prodigy. net...

Quote:

Originally Posted by

hi
I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server
>
or any good recomendations of a action to accomplish this in the best and
fastest way.
>
>
thanks davep
>
>

|||DaveP (dvs_bis@.sbcglobal.net) writes:

Quote:

Originally Posted by

I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows....
What is the impact on the server

This is a metadata change, so it will be about instant.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||AlterEgo (alterego55@.dslextreme.com) writes:

Quote:

Originally Posted by

First of all, SQL Server maximum rowsize = 8,060 bytes. Make sure you
keep under that limit when you add the #bytes from all of the other
columns.

This applies to SQL 2000 only. On SQL 2005 rows can span pages. Not so
that you can have three char(8000) on the same page, but three varchar(8000)
is OK.

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98D24AF9ACFYazorman@.127.0.0.1...

Quote:

Originally Posted by

DaveP (dvs_bis@.sbcglobal.net) writes:

Quote:

Originally Posted by

>I want to increase a varchar(5000)
>to varchar(8000) on a table that
>has approximately million rows....
>What is the impact on the server

>
This is a metadata change, so it will be about instant.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Saturday, February 25, 2012

column names, table name, user name

Hi;
I am new to SQL2000 & T-SQL
Is there a T-SQL SELECT statement I can use to query these info :
1) existing column names of a table (select * will give col name & all data
rows, I only wish to see columns names)
2) data types of columns (object viewer can do this, but is there a SELECT s
tatemtent to get these info?)
3) existing tables in a database (system & users' tables)
4) existing database users in a databaseFor the first three, you can use sp_help. For the last one, you can use
sp_helpuser.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||1) you could do - select * from table where 0=1
or to get all columns - select column_name from
INFORMATION_SCHEMA.columns
2) You can get all of this from INFORMATION_SCHEMA.columns
3) select table_name from INFORMATION_SCHEMA.tables will get you all user
tables
or select name from sysobjects where type = 's' or type = 'u' to get all
tables
4) select * from sysusers where issqluser = 1
HTH
Ray Higdon MCSE, MCDBA, CCNA
--
"pk" <anonymous@.discussions.microsoft.com> wrote in message
news:0AFFB3B7-03C6-411E-BBE6-C9C37B389928@.microsoft.com...
> Hi;
> I am new to SQL2000 & T-SQL
> Is there a T-SQL SELECT statement I can use to query these info :
> 1) existing column names of a table (select * will give col name & all
data rows, I only wish to see columns names)
> 2) data types of columns (object viewer can do this, but is there a SELECT
statemtent to get these info?)
> 3) existing tables in a database (system & users' tables)
> 4) existing database users in a database|||Column names and other pertinent info is stored in system table syscolumns.
You can get to your first two questions joining sysobjects with syscolumns.
Question 3 is sysobjects only. Question 4 is sysusers. But after replyin
g I would like to know what
exactly you're trying to learn.
Since you can't answer this question I'm curious what you're trying to do.
What you're trying to learn is in the system tables.

Friday, February 24, 2012

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
Group is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:

> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what?
> Mangesh
>
|||"Mangesh Deshpande" schrieb:

> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.
|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller
|||Thanks a lot.
"doller" wrote:

> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:

> Group is a reserved word. It's a best practice not to use column names th
at
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:

> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:

> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>

Thursday, February 16, 2012

Column Conflict error

Hi;

I am getting this error while I am trying to delete a record from "MediaTypes" table
Media Types table and DVBTestCOutputs table have related through MediaID (cascade on update only not delete)

MediaTypes DVBTestCOutputs
---- -------
MediaID int ....................
MType char(10) ....................
MediaType int

DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_DVBTestCOutputs_MediaTypes1'. The conflict occurred in database 'Test', table 'DVBTestCOutputs', column 'MediaType'.

can you help me please I do not want the related record deleted also from DVBTestCOutputs table so I didn't choose the cascade on delete checkbox is this the problem??If there is a PK-FK constraint, then you are not allowed to have child records that have no parent record. You will need to update the value of the ParentID in the child table to another ParentID, before deleting the parent record.

Otherwise, you need to remove your constraint.

Cheers
Ken

Tuesday, February 14, 2012

Colour Printing in CR 8.5

Hi
I'm using Crystal reports in My VB application. All but one of my reports are printing in colour. All my reports/designers are in the same project. Any ideas why this one report is printing in black & white?
any help would be appreciatedI do not know if this will apply to you because I am using Crystal 10.

I had a similar problem with users trying to print crystal reports in color, but they were printing in black and white. What I found was that since I created the crystal reports, all users who were accessing the crystal reports were printing from my printers default settings. I have a black and white printer in my office, so all the users were printing in balc and white.

What I had to do was, open the crystal report setup/creator, open the printer setup, select the color printer, and then save the report. After this was done, all users were able to print the crystal reports in color.

Let me know if this helps.

Colors in legend does not match bar graph

I have a bar graph with custom colors applied to it.
The custom colors displays nicely in the bar graph, but not in the legend.

The legend displays a mix of some of the "correct" custom colors, and some of the "wrong" standard colors. The colors in the legend vary, depending on the parameters passed to the report. Sometimes legend colors are all right, other times all wrong but must times a mix of right and wrongs.

The value for the custom color of the graph is retrieved from a field in the dataset. However I doubt that this is the cause of the error because, the bar graph colors are correct at all times, and I have validated thet correct color names are actually in the dataset.

Hope someone can help me out with this one...

/Henrik

The legend color always depends on the color of the first datapoint in a chart series.

Also, is a series grouping present in the chart? In that case you have to make sure that e.g. instead of using =Code.PickColor( Fields!ResultSort.Value), you should use this expression that uses the First() aggregate: =Code.PickColor(First(Fields!ResultSort.Value, "chart1_SeriesGroup1")

The important part is the aggregate scope which has to be identical to the chart series grouping name. Just using the First aggregate without the scope will give you incorrect results, because the aggregate will be just scoped for every chart datapoint (and therefore null if you don't have any datapoints for a particular series group / category group combination).

Another option you may consider is using a "custom legend" which is not drawn inside the chart but created by a table besides the chart. My sample and instructions for this can be found in the following blog article:
http://blogs.msdn.com/bwelcker/archive/2005/05/20/420349.aspx

-- Robert

|||

Thanks a lot Robert.
It was the aggregate scope I was missing. It's all working fine now.

/Henrik

|||

Does this apply for Gray Scale? I am having trouble when I set a chart to use Gray Scale the legend shows the first set of patterns but the remaining patterns are all random. I tried adding the scope to the Data section of the chart but it doesn't change the behavior with Gray Scale. Also, if it's a bug, is there a way that I can specify fill patterns in case I write a function to pick the pattern based on the series? Thanks!

Colors in legend does not match bar graph

I have a bar graph with custom colors applied to it.
The custom colors displays nicely in the bar graph, but not in the legend.

Hope someone can help me out with this one...

/Henrik

The legend color always depends on the color of the first datapoint in a chart series.

-- Robert

|||

Thanks a lot Robert.
It was the aggregate scope I was missing. It's all working fine now.

/Henrik

|||

Sunday, February 12, 2012

collecting db statistics

Hi;
I came across this article in SQL Server Magazine (March2003),under Letters
section, pg7, but I don't understand how to go abt implement or use the tip:
" schedule a wrapper stored procedure that executs a procedure in master for
each application database, such as;
EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
This SP will apparently get info on DB growth, data-file, table stats, which
is what I want to get.
er....can someone show me the way what I need to do ?
tks much
I would suggest that you contact ed.parr@.royalmail.com to obtain the source
code for this procedure. Basically what you found was a letter explaining
how if my store procedure, which is described in SQL Server magazine article
Avoiding the Redzone December 2002 was place in master it would simplify my
code. I don't think Ed Parr has ever shared the code for his sp he was
referring to in his letter to SQL Server Magazine.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Hi;
> I came across this article in SQL Server Magazine (March2003),under
Letters
> section, pg7, but I don't understand how to go abt implement or use the
tip:
> " schedule a wrapper stored procedure that executs a procedure in master
for
> each application database, such as;
> EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> This SP will apparently get info on DB growth, data-file, table stats,
which
> is what I want to get.
> er....can someone show me the way what I need to do ?
>
> tks much
>
|||Also I should have said, I think if you go to InstantDoc ID 26874 you might
be able to down load my code for gathering space statistics, but I think you
might need to be a subscriber to actually read the article that explains the
process. If you go this route let me know if you have any questions about
the sp, and table, and I will be glad to answer them.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> I would suggest that you contact ed.parr@.royalmail.com to obtain the
source
> code for this procedure. Basically what you found was a letter explaining
> how if my store procedure, which is described in SQL Server magazine
article
> Avoiding the Redzone December 2002 was place in master it would simplify
my
> code. I don't think Ed Parr has ever shared the code for his sp he was
> referring to in his letter to SQL Server Magazine.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "pohkeat" <pohkeat@.hotmail.com> wrote in message
> news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Letters
> tip:
> for
> which
>
|||hi;
tks for the tip. Got my hands on the code,with it I've created
usp_get_dbstats in my user db, and also DBSTATS in the user db.
how do i execu it ? I ran usp_get_dbstats in QA, copy the results and pasted
in another QA window, ran it, DBCC printed no error for a couple of lines
until at the end, it printed Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Procedure:'.
I opened the DBSTATS table, and its empty.
where did I go wrong ?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eipLySzbEHA.3988@.tk2msftngp13.phx.gbl...
> Also I should have said, I think if you go to InstantDoc ID 26874 you
might
> be able to down load my code for gathering space statistics, but I think
you
> might need to be a subscriber to actually read the article that explains
the
> process. If you go this route let me know if you have any questions
about
> the sp, and table, and I will be glad to answer them.
> --
> ----
--
> ----
--[vbcol=seagreen]
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> source
explaining[vbcol=seagreen]
> article
> my
> ----
> --
> ----
> --
the[vbcol=seagreen]
master
>

collecting db statistics

Hi;
I came across this article in SQL Server Magazine (March2003),under Letters
section, pg7, but I don't understand how to go abt implement or use the tip:
" schedule a wrapper stored procedure that executs a procedure in master for
each application database, such as;
EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
This SP will apparently get info on DB growth, data-file, table stats, which
is what I want to get.
er....can someone show me the way what I need to do ?
tks muchI would suggest that you contact ed.parr@.royalmail.com to obtain the source
code for this procedure. Basically what you found was a letter explaining
how if my store procedure, which is described in SQL Server magazine article
Avoiding the Redzone December 2002 was place in master it would simplify my
code. I don't think Ed Parr has ever shared the code for his sp he was
referring to in his letter to SQL Server Magazine.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"pohkeat" <pohkeat@.hotmail.com> wrote in message
news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Hi;
> I came across this article in SQL Server Magazine (March2003),under
Letters
> section, pg7, but I don't understand how to go abt implement or use the
tip:
> " schedule a wrapper stored procedure that executs a procedure in master
for
> each application database, such as;
> EXEC @.retcode = <dbname>.dbo.sp_add_DBA_add_database_space_history "
> This SP will apparently get info on DB growth, data-file, table stats,
which
> is what I want to get.
> er....can someone show me the way what I need to do ?
>
> tks much
>|||Also I should have said, I think if you go to InstantDoc ID 26874 you might
be able to down load my code for gathering space statistics, but I think you
might need to be a subscriber to actually read the article that explains the
process. If you go this route let me know if you have any questions about
the sp, and table, and I will be glad to answer them.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> I would suggest that you contact ed.parr@.royalmail.com to obtain the
source
> code for this procedure. Basically what you found was a letter explaining
> how if my store procedure, which is described in SQL Server magazine
article
> Avoiding the Redzone December 2002 was place in master it would simplify
my
> code. I don't think Ed Parr has ever shared the code for his sp he was
> referring to in his letter to SQL Server Magazine.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "pohkeat" <pohkeat@.hotmail.com> wrote in message
> news:%23Gv1S7ybEHA.1644@.tk2msftngp13.phx.gbl...
> Letters
> tip:
> for
> which
>|||hi;
tks for the tip. Got my hands on the code,with it I've created
usp_get_dbstats in my user db, and also DBSTATS in the user db.
how do i execu it ? I ran usp_get_dbstats in QA, copy the results and pasted
in another QA window, ran it, DBCC printed no error for a couple of lines
until at the end, it printed Server: Msg 156, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'Procedure:'.
I opened the DBSTATS table, and its empty.
where did I go wrong ?
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:eipLySzbEHA.3988@.tk2msftngp13.phx.gbl...
> Also I should have said, I think if you go to InstantDoc ID 26874 you
might
> be able to down load my code for gathering space statistics, but I think
you
> might need to be a subscriber to actually read the article that explains
the
> process. If you go this route let me know if you have any questions
about
> the sp, and table, and I will be glad to answer them.
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
> news:O$hBoPzbEHA.3944@.tk2msftngp13.phx.gbl...
> source
explaining[vbcol=seagreen]
> article
> my
> ----
> --
> ----
> --
the[vbcol=seagreen]
master[vbcol=seagreen]
>

Friday, February 10, 2012