can we increment the column --autimaticallyis there any possiblity with tiggers

hi,
i am a beginner to ms sql server2000
i have a table
create table ddd (a int, b int)
by table structure is a b
now when i enter a value in b column suppose '2' in column b
bext time when i insert a value in the column a i have to get the value
in b as 3 is thi spossible with triggers
insert into gdg values (1,2)
a b
1 2
insert into gdg (a) values(2)
a b
2 3--> i have to get this 3 automatically
is there any method to get this
pls help me
satishis this homework?sql

Can we include a variable field value on the table header.

Can we include a variable field value on the table header.

I am doing a page break on every date change and also set the property of he table header to repeat on each page. By doing this ,I am able to get the table header on each page even on page break. I want the changed date to be displayed on the every page break and in the table header.

Please help.

You should be able to do this without issue. Assuming you have a field which you are pulling from that shows the date, you could do something like this as an expression in the table header.

="Report Header - " & Fields!Date.Value

Hope this helps.

can we have temporary table in store procedure when using BCP ?

hi, good day,

when i run query in query analyzer

exec sp_test '2006-07-21'

it show result , however when i run it using BCP tools

exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '

it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure

how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,

thank you :eek:Hmph. I would have thought that would work. Post the code for sp_test.|||thanks for reply, the query was constructed in this way

CREATE PROCEDURE [SP_TEST]

--------CREATE TEMP TABLE----------
SELECT
*
INTO #TMP_TBL1
FROM
TABLE A, TABLE B

SELECT
*
INTO #TMP_TBL2
FROM
TABLE C, TABLE D

----------BEGINNING OF PROCESS------

SELECT *
FROM
(
SELECT * FROM
#TMP_TBL1
UNION
#TMP_TBL2

)|||You are posting skeleton code. Post the actual code you are using, and the actual error message you receive.|||hi, blindman, sorry , i can't expose my company query and it would be few page query, but i use this simple store procedure to test on , it doesn't work as well

CREATE PROCEDURE [SP_TEST]

AS

SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER

----------------------
SELECT DISTINCT
COUNT(CUST_CD)

FROM
#TMP_CUSTOMER
GO

when i run in query analyzer , using "exec SP_TEST" , it work

but when run
Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:\TEST.TXT -c -Slocalhost -Usa -Ppassword'

it give error "SQLState = S0002, NativeError = 208"
and show
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.

do u have any idea on it ?|||I tried you code and was able to duplicate your error. I tried a few work-arounds, but none of them were succesful. BCP seems to be failing to recognize any table created within the procedure, temporary or permanent.

I have not run into this before, but I have not used BCP very frequently. I am surprised, none the less.

Wait until Monday, and lets see if any other forum members recognize this error.|||Hi

Did a quick RTFM:

Copying Data To or From a Temporary Table

When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:
bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -PpasswordHowever, do not specify the database name when using global temporary tables because temporary tables exist only in tempdb. It is possible to use a local temporary table (for example, #temp_authors) only when bulk copying data using the BULK INSERT statement.The key point appears to be the last line. A rather interesting turn of phrase that says (in a roundabout way) - "you can't".

HTH

EDIT - in fact Nakata that is so poorly written (and assuming English isn't your first language - believe it or not it is my first language and I had to read it a couple of times) it basically says that local temp tables work with BULK INSERT but not (by implication) bcp.|||thanks pootle flump and blindman,
this is bad for me with the answer, i may need to re-write the whole query, my god , that really killing me ...any other alternative i can have if possible no need to re-write all query that involve temporary table :(|||Not a big user of bcp... I wonder if it has the same objection to Table variables...|||Hi,

This is a shortcoming of the way that bcp works, particularly when a stored procedure is used as the command for a "queryout" operation.

bcp needs to understand the format of the results it is about to receive, before it issues the query. If you use the "out" operation, giving a table name, it issues the following command:

SET FMTONLY ON
SELECT * FROM <table name>
SET FMTONLY OFF

If you use the "queryout" operator with an exec statement, it issues the following command:

SET FMTONLY ON
exec ...
SET FMTONLY OFF

Unfortunately, this technique only works with the simplest of stored procedures, where the format of the ouput can be determined WITHOUT ACTUALLY RUNNING THE CODE IN THE PROCEDURE. Your case does not work because #TMP_CUSTOMER does not exist unless you actually run the code.

FYI, a solution with a table variable WILL work, e.g.

<code>
create proc dbo.bills_test
as
begin

declare @.output table (col1... , col2 ... , ...)

insert @.output
select ...

select col1, col2 ...
from @.output

end
<\code>

you can play with this in query analyser, to get a stored proc that will work with bcp...

HTH,

Bill|||thanks everyone for contribution , i really learn a lot from this issue , i finally found that if i replace #tmp_tbl to tempdb..tmp_tbl , it work

will tempdb object will causing any performance issue , i really not sure ..

:beer:|||That may cause problems if more than one user executes your process simultaneously. You should consider using thompbil's suggestion and implementing a table variable.|||but I have not used BCP very frequently.

Oh, for the love of god....

In any case just put the results of the sproc into a table and bcp that out|||Oh, for the love of god....You got a problem wit dat, joisey-boy?|||tell me you use DTS...come on, you can tell us|||USE Northwind
GO

CREATE PROC mySproc99
AS
SET NOCOUNT ON
DECLARE @.myTabVar99 table (Col1 int)
INSERT INTO @.myTabVar99(Col1)
SELECT OrderId FROM Orders

SELECT * FROM @.myTabVar99
GO

EXEC mySproc99

exec master..xp_cmdshell 'bcp "exec Northwind.dbo.mySproc99" queryout c:\test.txt -c -T -S<servername>'

DROP PROC mySproc99
GO|||I do use DTS. To transfer data to staging tables. It's fine for migrating data. I don't do any processing within DTS.
DTS may not have the cache' of command-line utilities, then again DTS would have handled this poster's problem easily.sql

Can we do a double Cursor?

Can we do a double Cursor where we select the data form one table using a
cursor and then use the key data such as ORDER number to select data from
another table using a cursor?
regards,
RonYou can. You can also try to take your bike on the freeway at rush hour and
challenge a street racer...
Any reason you can't do this with a simple join? What exactly are you
trying to accomplish?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
<Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
> Can we do a double Cursor where we select the data form one table using a
> cursor and then use the key data such as ORDER number to select data from
> another table using a cursor?
> --
> regards,
> Ron|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I’m trying to simulate SQR program. Do you have an example where you use
double cursor.
"Aaron [SQL Server MVP]" wrote:

> You can. You can also try to take your bike on the freeway at rush hour a
nd
> challenge a street racer...
> Any reason you can't do this with a simple join? What exactly are you
> trying to accomplish?
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> <Ron>; "hayim" <Ronhayim@.discussions.microsoft.com> wrote in message
> news:A22E9344-5385-4A00-92CD-B9EF611E6C47@.microsoft.com...
>
>|||I have absolutely no idea what an "SQR program" is. Do you mean square
root?
Anyway, a double cursor would look like this:
CREATE TABLE #pk
(
id INT PRIMARY KEY
)
CREATE TABLE #fk
(
id int FOREIGN KEY REFERENCES #pk(id),
foo VARCHAR(2)
)
SET NOCOUNT ON
INSERT #pk
SELECT 1
UNION SELECT 2
INSERT #fk
SELECT 1, 'a'
UNION SELECT 1, 'b'
UNION SELECT 2, 'c'
DECLARE @.pkID INT, @.foo VARCHAR(2)
DECLARE c1 CURSOR FOR
SELECT id FROM #pk
OPEN c1
FETCH NEXT FROM c1 INTO @.pkID
WHILE @.@.FETCH_STATUS = 0
BEGIN
DECLARE c2 CURSOR FOR
SELECT foo FROM #fk WHERE id = @.pkID
OPEN c2
FETCH NEXT FROM c2 INTO @.foo
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Outer loop: '+RTRIM(@.pkID)
PRINT 'Inner loop: '+@.foo
FETCH NEXT FROM c2 INTO @.foo
END
CLOSE c2
DEALLOCATE c2
FETCH NEXT FROM c1 INTO @.pkID
END
CLOSE c1
DEALLOCATE c1
DROP TABLE #fk, #pk
Please don't do this on a production system. If you do, don't tell them I
told you how to do it. I will deny it and tell them it was Celko, spoofing
my IP and all.
On 3/22/05 8:03 PM, in article
9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
<Ronhayim@.discussions.microsoft.com> wrote:

> Im trying to simulate SQR program. Do you have an example where you use
> double cursor.|||On Tue, 22 Mar 2005 15:41:02 -0800, Ron wrote:

>Can we do a double Cursor where we select the data form one table using a
>cursor and then use the key data such as ORDER number to select data from
>another table using a cursor?
Hi Ron,
Even if you really do need a cursor (which I doubt - and the statistics
are on my side), there is really no need to use two of the beasts.
Why not create one query that joins the two tables the way you want them
to be joined, filters rows you don't need and returns only the columns
you need? Then, if you really must, you can always use that query for
your cursor...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The script was a lot of help. SQR is a program that used by Peoplesoft.
This was just a special need. All of you right, just joining the tables
should do the it.
"Aaron [SQL Server MVP]" wrote:

> I have absolutely no idea what an "SQR program" is. Do you mean square
> root?
> Anyway, a double cursor would look like this:
>
> CREATE TABLE #pk
> (
> id INT PRIMARY KEY
> )
> CREATE TABLE #fk
> (
> id int FOREIGN KEY REFERENCES #pk(id),
> foo VARCHAR(2)
> )
> SET NOCOUNT ON
> INSERT #pk
> SELECT 1
> UNION SELECT 2
> INSERT #fk
> SELECT 1, 'a'
> UNION SELECT 1, 'b'
> UNION SELECT 2, 'c'
> DECLARE @.pkID INT, @.foo VARCHAR(2)
> DECLARE c1 CURSOR FOR
> SELECT id FROM #pk
> OPEN c1
> FETCH NEXT FROM c1 INTO @.pkID
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DECLARE c2 CURSOR FOR
> SELECT foo FROM #fk WHERE id = @.pkID
> OPEN c2
> FETCH NEXT FROM c2 INTO @.foo
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> PRINT 'Outer loop: '+RTRIM(@.pkID)
> PRINT 'Inner loop: '+@.foo
> FETCH NEXT FROM c2 INTO @.foo
> END
> CLOSE c2
> DEALLOCATE c2
> FETCH NEXT FROM c1 INTO @.pkID
> END
> CLOSE c1
> DEALLOCATE c1
> DROP TABLE #fk, #pk
>
> Please don't do this on a production system. If you do, don't tell them I
> told you how to do it. I will deny it and tell them it was Celko, spoofin
g
> my IP and all.
>
> On 3/22/05 8:03 PM, in article
> 9E814E08-0578-4953-8BD4-E0CEB718ADD2@.microsoft.com, "Ron,hayim"
> <Ronhayim@.discussions.microsoft.com> wrote:
>
>

Can we declare a Table in a FUNCTION.................?

Hi,
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.
> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.
|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................
|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?

> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.

> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?

> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.

> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.

> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.

Can we declare a Table in a FUNCTION.................?

Hi,
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?

> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.

> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?

> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.

> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.

> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.sql

Can we declare a Table in a FUNCTION.................?

Hi,
I have a doubt regarding functions in MS SQlServer 2000.
Can we declare ,use and drop a temperory table in the function body?
Its showing suntax error.
Thanks & Regards.> I have a doubt regarding functions in MS SQlServer 2000.
> Can we declare ,use and drop a temperory table in the function body?
> Its showing suntax error.
In the future, you should always include the code and the complete text of
the error message when posting. This will avoid any possible confusion.
To answer your question - No. You can declare a table variable, however.
There are many restrctions on functions. Please have a look at the
documentation.|||Hi Scott,
This is the code.
CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
RETURNS NULL AS
BEGIN
CREATE TABLE pmlist (pmcol VARCHAR(30))
DECLARE @.pmstring VARCHAR(50)
DECLARE @.len INT
SET @.len = LEN(@.ipstring)
SET @.pmstring = @.ipstring
WHILE(@.len<>0)
{
INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
',',@.pmstring ,1 )-1))
SET @.pmstring=RIGHT(@.pmstring , @.len
-CHARINDEX(',',@.pmstring,1) )
SET @.len=LEN(@.pmstring)
}
SELECT * FROM pmlist
DROP pmlist
END
ErrorMessage: Error 0 : Syntax Error or Access Violation
Could you please review this........................|||> This is the code.
> CREATE FUNCTION dbo.ListParameters (@.ipstring varchar(30))
> RETURNS NULL AS
What is "NULL"?
> BEGIN
> CREATE TABLE pmlist (pmcol VARCHAR(30))
Look closely at what you coded. What does the above do? It attempts to
create a permanent table, not a table variable.
> DECLARE @.pmstring VARCHAR(50)
> DECLARE @.len INT
> SET @.len = LEN(@.ipstring)
> SET @.pmstring = @.ipstring
> WHILE(@.len<>0)
> {
Curly braces are not valid tsql.
Are you certain you want to loop if @.len is negative?
> INSERT INTO pmlist VALUES (LEFT(@.pmstring,CHARINDEX (
This is poor practice. Always include the column list.
> ',',@.pmstring ,1 )-1))
> SET @.pmstring=RIGHT(@.pmstring , @.len
> -CHARINDEX(',',@.pmstring,1) )
> SET @.len=LEN(@.pmstring)
> }
> SELECT * FROM pmlist
This is poor practice. Always include the column list.
> DROP pmlist
> END
Again, there are restrictions with functions, and there are specific things
that must be coded (and in a specific manner). Please review the
information in BOL. Assuming you want to write a valid function, I would
recommend that you write a batch first that does what you want, and then
convert that to a function. There are examples of functions in BOL and you
can find many examples that have been posted in the newsgroups. I assume
that you are trying to decompose a csv into a table structure. I'm certain
that examples have been posted - you might want to avoid reinventing this
wheel.

Can we change the Colloation setting for an existing database?

same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helping
Pu,
Assuming you are talking w.r.t a SQL2000 database...
Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
section.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Pu Gong" <lpug@.hotmail.com> wrote in message
news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> same as the subject.
> I have a database have a different collation setting from the temp db. In
a
> SP, I use a temp table, and later, I use the temp table to compare
nvarchar
> data with an physical table in the database, then I meet an error like
that
> Msg 446, Level 16 ...
> Cannot resolve collation conflict for equal to operation
> I think I should change the collation setting of the database to the
> collation of tempdb and master, but how can I change the collation
setting?
> I have read the BOL, but can't find a way to change the collation
> Thanks a lot for helping
>
|||When you create the temporary table you can also specify the collation used in your user database on the textfields.
create table #abc
(description varchar(100) COLLATE latin1_general_cs_as)
-- Pu Gong wrote: --
same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helping
|||Thanks a lot
use
Alter database collate collation_name
to change the collation
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23qdTsnfTEHA.204@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Pu,
> Assuming you are talking w.r.t a SQL2000 database...
> Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
> section.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>
> "Pu Gong" <lpug@.hotmail.com> wrote in message
> news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
In
> a
> nvarchar
> that
> setting?
>

Can we change the Colloation setting for an existing database?

same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helpingPu,
Assuming you are talking w.r.t a SQL2000 database...
Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
section.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Pu Gong" <lpug@.hotmail.com> wrote in message
news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> same as the subject.
> I have a database have a different collation setting from the temp db. In
a
> SP, I use a temp table, and later, I use the temp table to compare
nvarchar
> data with an physical table in the database, then I meet an error like
that
> Msg 446, Level 16 ...
> Cannot resolve collation conflict for equal to operation
> I think I should change the collation setting of the database to the
> collation of tempdb and master, but how can I change the collation
setting?
> I have read the BOL, but can't find a way to change the collation
> Thanks a lot for helping
>|||When you create the temporary table you can also specify the collation used in your user database on the textfields
create table #ab
(description varchar(100) COLLATE latin1_general_cs_as
-- Pu Gong wrote: --
same as the subject
I have a database have a different collation setting from the temp db. In
SP, I use a temp table, and later, I use the temp table to compare nvarcha
data with an physical table in the database, then I meet an error like tha
Msg 446, Level 16 ..
Cannot resolve collation conflict for equal to operatio
I think I should change the collation setting of the database to th
collation of tempdb and master, but how can I change the collation setting
I have read the BOL, but can't find a way to change the collatio
Thanks a lot for helpin|||Thanks a lot
use
Alter database collate collation_name
to change the collation
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23qdTsnfTEHA.204@.TK2MSFTNGP10.phx.gbl...
> Pu,
> Assuming you are talking w.r.t a SQL2000 database...
> Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
> section.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>
> "Pu Gong" <lpug@.hotmail.com> wrote in message
> news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > same as the subject.
> > I have a database have a different collation setting from the temp db.
In
> a
> > SP, I use a temp table, and later, I use the temp table to compare
> nvarchar
> > data with an physical table in the database, then I meet an error like
> that
> >
> > Msg 446, Level 16 ...
> > Cannot resolve collation conflict for equal to operation
> >
> > I think I should change the collation setting of the database to the
> > collation of tempdb and master, but how can I change the collation
> setting?
> > I have read the BOL, but can't find a way to change the collation
> >
> > Thanks a lot for helping
> >
> >
>

Can we change the Colloation setting for an existing database?

same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helpingPu,
Assuming you are talking w.r.t a SQL2000 database...
Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
section.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Pu Gong" <lpug@.hotmail.com> wrote in message
news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
> same as the subject.
> I have a database have a different collation setting from the temp db. In
a
> SP, I use a temp table, and later, I use the temp table to compare
nvarchar
> data with an physical table in the database, then I meet an error like
that
> Msg 446, Level 16 ...
> Cannot resolve collation conflict for equal to operation
> I think I should change the collation setting of the database to the
> collation of tempdb and master, but how can I change the collation
setting?
> I have read the BOL, but can't find a way to change the collation
> Thanks a lot for helping
>|||When you create the temporary table you can also specify the collation used
in your user database on the textfields.
create table #abc
(description varchar(100) COLLATE latin1_general_cs_as)
-- Pu Gong wrote: --
same as the subject.
I have a database have a different collation setting from the temp db. In a
SP, I use a temp table, and later, I use the temp table to compare nvarchar
data with an physical table in the database, then I meet an error like that
Msg 446, Level 16 ...
Cannot resolve collation conflict for equal to operation
I think I should change the collation setting of the database to the
collation of tempdb and master, but how can I change the collation setting?
I have read the BOL, but can't find a way to change the collation
Thanks a lot for helping|||Thanks a lot
use
Alter database collate collation_name
to change the collation
"Dinesh T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:%23qdTsnfTEHA.204@.TK2MSFTNGP10.phx.gbl...
> Pu,
> Assuming you are talking w.r.t a SQL2000 database...
> Refer 'ALTER DATABASE' in BooksOnLine and especially read the 'remarks'
> section.
> --
> Dinesh
> SQL Server MVP
> --
> --
> SQL Server FAQ at
> http://www.tkdinesh.com
>
> "Pu Gong" <lpug@.hotmail.com> wrote in message
> news:u6DLdifTEHA.3512@.TK2MSFTNGP12.phx.gbl...
In[vbcol=seagreen]
> a
> nvarchar
> that
> setting?
>

Can we change table structure in Mirroring

Can we change table structure in Mirroring. Like adding or deleting columns and tables and how can we do it. Thank you very much.Yes, you can change table structure in Database Mirroring. Changing the structure in the primary database is reflected over the mirror database. Think of database mirroring as "first class" log shipping with automatic failover. Initially, I thought only the data is mirrored since the mirror database is on read-only mode. But testing showed that DDL changes also get "mirrored."|||

Database Mirroring was initially called "Real Time Log Shipping." It is basically taking the log stream and "restoring" it on the mirror server in real time.

So, yes, you can change the table structure in database mirroring.

Thanks,

Mark

Can we change table structure in Mirroring

Can we change table structure in Mirroring. Like adding or deleting columns and tables and how can we do it. Thank you very much.

Yes you can, and you do it just like you would if Mirroring was not being used (i.e. ALTER TABLE, CREATE TABLE, etc.)...DB mirroring moves logged transactions to the mirror as they are applied to the principle, and this includes DDL statements.

HTH,

sql

Can we change table structure in Mirroring

Can we change table structure in Mirroring. Like adding or deleting columns and tables and how can we do it. Thank you very much.

Yes you can, and you do it just like you would if Mirroring was not being used (i.e. ALTER TABLE, CREATE TABLE, etc.)...DB mirroring moves logged transactions to the mirror as they are applied to the principle, and this includes DDL statements.

HTH,

Can we ask English Query to show a Graph ?

Hi
My EQ model dispalys a normal table when you ask a question such as;
show me the amount of goods solds for last 5 years.
it shows the amount grouped in to 5 years.
It would be nice if I can show it in a graph rather than the data.
I have seen a GRAPH verb in EQ but can't find a single help on how to make
it draw a graph.
Have anybody done this?
rgds
KK
Within English Query you can use something called commands which can run a
proc, shell out and run a batch file, etc.
Depending on your programming skill you should be able to use this to
generate a graph.
IIRC - Adam Blum (Not sure if I have his name correct), who is/was the PM
(Program Manager) for English Query did a demonstration at TechEd or PDC
where he demonstrated something similar.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"KK" <kk@.kk.com> wrote in message
news:uGyC1RiaEHA.2408@.tk2msftngp13.phx.gbl...
> Hi
> My EQ model dispalys a normal table when you ask a question such as;
> show me the amount of goods solds for last 5 years.
> it shows the amount grouped in to 5 years.
> It would be nice if I can show it in a graph rather than the data.
> I have seen a GRAPH verb in EQ but can't find a single help on how to make
> it draw a graph.
> Have anybody done this?
> rgds
> KK
>
|||Hi Hilary,
*Phew*
About time btw, thanks for the reply,
Well, coding is not at all a problem. I am already messing up with
the command object. can get smalll commands like sending
generated custom emails and such is possible...
however, trying to create relationship for graphs always
gives me errors (thats my bad) Do you have any idea
where I can find this guys demo ? link to a web page
where he might have information with a sample?
Amazing thing is, can't find a single help regarding this
on the web!!
I would greatly appreciate if you can send any links if u have
meanwhile, i'll dig for his name on the web
KK
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ePN7tjnaEHA.384@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Within English Query you can use something called commands which can run a
> proc, shell out and run a batch file, etc.
> Depending on your programming skill you should be able to use this to
> generate a graph.
> IIRC - Adam Blum (Not sure if I have his name correct), who is/was the PM
> (Program Manager) for English Query did a demonstration at TechEd or PDC
> where he demonstrated something similar.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "KK" <kk@.kk.com> wrote in message
> news:uGyC1RiaEHA.2408@.tk2msftngp13.phx.gbl...
make
>

Can we allow NULL value to nested key?

Hi, all here,

I am having a problem with NULL value for the nested key of the nested table. Is it possible to allow ''null'' value to nested key? If so, how can we deal with that? As the column I am using for nested key contains null values, thus the process failed. Could please any expert here give me any advices for that?

Really need help for that.

With best regards,

Yours sincerely,

You cannot have a NULL value as nested key. The simple way to work around is to replace your nested table, in the data source view, with a named query which filters out the NULL columns. I think replacing the table with a query is an option if you right click on the table in the Data Source View Designer

|||

Hi, Bogdan, thank you very much for your guidance.

With best regards,

Yours sincerely,

Can update in SQL 2000 but not express beta 2

I have come across a interesting problem when creating a datagrid view in Sql express beta 2.

I created a database shop and table customers using sql manager qeries

create database shop;

use shop
create table customers(customerID int);
use shop
insert into customers VALUES ('1');

Pathetically simple I know!!, I created the same table in SQL 2000 using enterprise manager.

When I create a new C# windows project in VS2005, create a new data source and use the express data base by dragging the datagrid straight from the data sources window, I run it and it fails to update 1 to 2
the code it failing at is

return this.Adapter.Update(dataTable);

in dataset1.designer.cs

However simply creating a new project and adding the SQL 2000 instance of the database works fine

I was just wondering if anybody else has come across this problem

Regards Ross

So I worked out the problem,

in SQL express manager when I was making the tables using sql statements I neglected to set a primary key, when I made the tables in sql 2000 Enterprise manager I added primary keys out of habbit, so basically make sure primary keys are set in the tables.

Can update accumulate?

I need to write an UPDATE statement that adds to a field from data in
another table. Can someone help? below is sample:
UPDATE TableA
SET Total = Total + TableB.Amount
FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo
WHERE TableB.PrdYr = 2005
When I do this, it does not add in the incremented Total field and I end up
with the last TableB.Amount value.
Thanks.
David>> I need to write an UPDATE statement that adds to a field from data in
Yes, but you will have to provide sufficient information for others to
understand your problem. Pl. read www.aspfaq.com/5006 and post your DDLs,
sample data & expected results
Anith|||Try this, it will keep a running total in TableA each time the query is
run. If this is going to be run and needs all of the values to start
out 0 (no running total), then remove the 'Total + ' part of the query.
UPDATE TableA
SET Total = Total +
( SELECT ISNULL(SUM(TableB.Amount),0)
FROM TableB
WHERE TableB.PrdYr = 2005
and TableB.EmpNo = TableA.EmpNo
)
Kalvin|||David,
An UPDATE statement will only make one assignment
to each column. UPDATE .. FROM is a T-SQL extension
to standard SQL that allows poorly defined statements, and
while it can be handy, it can also cause confusion. I wish an
error were raised in situations like this, but that's not the case.
To do what you want, you probably need something like
update TableA set
Total = Total + (
select sum(TableB.Amount)
from TableB
where TableB.EmpNo = TableA.EmpNo
and TableB.PrdYr = 2005
)
Steve Kass
Drew University
David wrote:

>I need to write an UPDATE statement that adds to a field from data in
>another table. Can someone help? below is sample:
>UPDATE TableA
>SET Total = Total + TableB.Amount
>FROM TableB JOIN TableA ON TableB.EmpNo = TableA.EmpNo
>WHERE TableB.PrdYr = 2005
>When I do this, it does not add in the incremented Total field and I end up
>with the last TableB.Amount value.
>Thanks.
>David
>
>|||Kalvin caught one thing I didn't. This needs either COALESCE
or a WHERE condition on the update, to avoid NULLing out
Total values when there's no match in TableB. Here's a WHERE
condition that ought to do it.
update TableA set
Total = Total + (
..
)
where exists (
select *
from TableB
where TableB.EmpNo = TableA.EmpNo
and TableB.PrdYr = 2005
and TableB.Amount is not null
)
SK
Steve Kass wrote:
> David,
> An UPDATE statement will only make one assignment
> to each column. UPDATE .. FROM is a T-SQL extension
> to standard SQL that allows poorly defined statements, and
> while it can be handy, it can also cause confusion. I wish an
> error were raised in situations like this, but that's not the case.
> To do what you want, you probably need something like
> update TableA set
> Total = Total + (
> select sum(TableB.Amount)
> from TableB
> where TableB.EmpNo = TableA.EmpNo
> and TableB.PrdYr = 2005
> )
>
> Steve Kass
> Drew University
> David wrote:
>|||1) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.
2) Would you like to learn REAL SQL or only some proprietary kludges
that have unpredicatable results, as you have posted?
Did you actually split out a year as a temporal column'!! Surely not
!
Why don't you know that column and field are **totally** different?
Why don't you know that there is no such thing as a generic, magical
"amount" -- it has to be the amount of something. Have you ever had a
BASIC -- repeat BASIC in capital letters -- data modeling class?
You can probably get enough kludges in a newsgroup to slip past your
boss unitl you get to the next job to screw up them too.
I got an email tonight form a kid who volunteered to do a DB for an
African Relief agency and seriously screwed it up. I got the consult
after things got messed up and I posted this in some newsgroups as an
example. I guess he found me via those postings.
I know his design crippled some children; I am not sure about causing
deaths and a part of me does not want to know. Please care enough not
to do that. To other people. To other people.sql

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satishIt is recommended to never do SELECT * or INSERT without column list in prod
uction code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-cre
ating the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegroups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satish
It is recommended to never do SELECT * or INSERT without column list in production code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-creating the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegr oups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>
|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish
sql

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satishIt is recommended to never do SELECT * or INSERT without column list in production code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-creating the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegroups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish