COLUMNS_UPDATED()

SQL Server 2000
BOL says:
The COLUMNS_UPDATED function returns the bits in order from left to right,
with the least significant bit being the leftmost. The leftmost bit
represents the first column in the table; the next bit to the right
represents the second column, and so on.
But in the example, bitmask to check the colums 2,3,4 calculated as 14,
in which rightmost bit is the first column in the table. is there something
wrong here?Yeah. what you are asking makes sense.
Looks like the bit stream is looked at as a string than a binary number, if
thats what you concern is.
Say for a 8 column table the first 4 are updated, then the columns_updated()
will read as
1111
and if 2 and 3 are updated its going to be
011
All it means is that the 0 has a significance to give out the position of
the column being updated or not and we cannot say 011 and 11 are equal in
this context.
and coming to your question,
14 is read is 0111 rather than 1110.
Its using a reverse binary system.. I believe.. But a good point you pointed
out nevertheless.
Lets wait for the other's comments though :)|||prefect a crit :
> SQL Server 2000
> BOL says:
> The COLUMNS_UPDATED function returns the bits in order from left to right,
> with the least significant bit being the leftmost. The leftmost bit
> represents the first column in the table; the next bit to the right
> represents the second column, and so on.
NOT AT ALL !
The bit calculate is based on the ordinal position deliver by
INFORMATION_SCHEMA.COLUMNS
Dmo :
CREATE TABLE T_TEST_BITCOLS_TBC
(COL1 INT,
COL2 INT,
COL3 INT,
COL4 INT,
COL5 INT)
GO
INSERT INTO T_TEST_BITCOLS_TBC VALUES (1, 2, 3, 4, 5)
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL2
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL3
GO
ALTER TABLE T_TEST_BITCOLS_TBC
DROP COLUMN COL5
GO
ALTER TABLE T_TEST_BITCOLS_TBC
ADD COL2 INT
GO
ALTER TABLE T_TEST_BITCOLS_TBC
ADD COL6 INT
GO
INSERT INTO T_TEST_BITCOLS_TBC VALUES (10, 20, 30, 40)
GO
CREATE TABLE T_TRIGGER_COLS_UPDATED_TCU
(TABLE_NAME SYSNAME,
BIT_COLS INT)
GO
CREATE TRIGGER E_U_TCU
ON T_TEST_BITCOLS_TBC
FOR UPDATE
AS
INSERT INTO T_TRIGGER_COLS_UPDATED_TCU
SELECT 'T_TEST_BITCOLS_TBC', COLUMNS_UPDATED()
GO
UPDATE T_TEST_BITCOLS_TBC
SET COL2 = 0
GO
SELECT *
FROM T_TRIGGER_COLS_UPDATED_TCU
TABLE_NAME BIT_COLS
-- --
T_TEST_BITCOLS_TBC 32
SELECT COLUMN_NAME, ORDINAL_POSITION,
POWER(2, ORDINAL_POSITION - 1) AS BIT_COL
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'T_TEST_BITCOLS_TBC'
COLUMN_NAME ORDINAL_POSITION BIT_COL
-- -- --
COL1 1 1
COL4 4 8
COL2 6 32 <====
COL6 7 64

> But in the example, bitmask to check the colums 2,3,4 calculated as 14,
> in which rightmost bit is the first column in the table. is there somethi
ng
> wrong here?
YES !
>
A +
--
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||thanks , i guess rightmost bit of every byte corresponds to first one of
the every 8 column regarding the ordinal of column.
surely , bit order of columns is read from system tables.
"SQLpro [MVP]" <brouardf@.club-internet.fr> wrote in message
news:OeCvlFFaGHA.1228@.TK2MSFTNGP02.phx.gbl...
> prefect a crit :
> NOT AT ALL !
>
> The bit calculate is based on the ordinal position deliver by
> INFORMATION_SCHEMA.COLUMNS
> Dmo :
>
> CREATE TABLE T_TEST_BITCOLS_TBC
> (COL1 INT,
> COL2 INT,
> COL3 INT,
> COL4 INT,
> COL5 INT)
> GO
> INSERT INTO T_TEST_BITCOLS_TBC VALUES (1, 2, 3, 4, 5)
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL2
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL3
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> DROP COLUMN COL5
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> ADD COL2 INT
> GO
> ALTER TABLE T_TEST_BITCOLS_TBC
> ADD COL6 INT
> GO
> INSERT INTO T_TEST_BITCOLS_TBC VALUES (10, 20, 30, 40)
> GO
> CREATE TABLE T_TRIGGER_COLS_UPDATED_TCU
> (TABLE_NAME SYSNAME,
> BIT_COLS INT)
> GO
>
> CREATE TRIGGER E_U_TCU
> ON T_TEST_BITCOLS_TBC
> FOR UPDATE
> AS
> INSERT INTO T_TRIGGER_COLS_UPDATED_TCU
> SELECT 'T_TEST_BITCOLS_TBC', COLUMNS_UPDATED()
> GO
> UPDATE T_TEST_BITCOLS_TBC
> SET COL2 = 0
> GO
> SELECT *
> FROM T_TRIGGER_COLS_UPDATED_TCU
> TABLE_NAME BIT_COLS
> -- --
> T_TEST_BITCOLS_TBC 32
>
> SELECT COLUMN_NAME, ORDINAL_POSITION,
> POWER(2, ORDINAL_POSITION - 1) AS BIT_COL
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'T_TEST_BITCOLS_TBC'
> COLUMN_NAME ORDINAL_POSITION BIT_COL
> -- -- --
> COL1 1 1
> COL4 4 8
> COL2 6 32 <====
> COL6 7 64
>
>
>
> YES !
>
> A +
> --
> Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modlisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************

Column name or number of supplied values...

Greetings
Appologies in advance as this is a bit of a newbie question as I have just
started playing with 2005 replication.
I am getting the error
Column name or number of supplied values does not match table definition.
I began ok by setting up replication between 2 servers on the same network.
It replicated some data across ok but I noticed that some tables did not
replicate. When I looked deeper at the articles it said that they can't
replicate because they didn't have a primary key. Ok so I modified the table
in the GUI and added the primary key (it is an identity column) and then the
replication error appeared for the stored procedure that adds entries to
that table.
I then did some looking around and found others had had similar problems. It
looks like I went about it the wrong way (newbie.newbie), so I thought, ok
I'll make sure all the primary keys are there first and then replicate. I
did this by deleting the subscriptions and disabling Pub&Dist, added the PK
and setup replication again. Error appears again.
I have tried numerous ways to "clean up" the Publisher/Distributor data and
remove the replication. While it all looks like it has been removed, if I
setup the replication again it seems to "know" about the previous problem
and my error message keeps appearing. I have even completely removed the
database I am replicating from the publisher and subscriber machines, have
used the procedure in the BOL to disable Pub & Dist
(sp_removedbreplication,sp_dropdistpublisher,sp_dr opdistributiondb,sp_dropdistributor),
and done a full restore of the offending database, however next time I setup
the replication I get the same error.
I maybe off track and identity columns are a no-no?
Anyway sorry for the long winded story. Any help would be creatly
appreciated. I am thinking of doing a compete SQL Server reload as it is
only a test system, but if I don't have to....
Cheers
Peter Lock
PalaceSoftware, Canberra OZ
I think you need to either update your replication stored procedures for the
newly added pk, or update the tables at the subscriber. I think the best
thing to do would be to generate a new snapshot and redistribute it.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter" <palace@.internode.on.net> wrote in message
news:uFcN5QM7GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Greetings
> Appologies in advance as this is a bit of a newbie question as I have just
> started playing with 2005 replication.
> I am getting the error
> Column name or number of supplied values does not match table definition.
> I began ok by setting up replication between 2 servers on the same
> network. It replicated some data across ok but I noticed that some tables
> did not replicate. When I looked deeper at the articles it said that they
> can't replicate because they didn't have a primary key. Ok so I modified
> the table in the GUI and added the primary key (it is an identity column)
> and then the replication error appeared for the stored procedure that adds
> entries to that table.
> I then did some looking around and found others had had similar problems.
> It looks like I went about it the wrong way (newbie.newbie), so I thought,
> ok I'll make sure all the primary keys are there first and then replicate.
> I did this by deleting the subscriptions and disabling Pub&Dist, added the
> PK and setup replication again. Error appears again.
> I have tried numerous ways to "clean up" the Publisher/Distributor data
> and remove the replication. While it all looks like it has been removed,
> if I setup the replication again it seems to "know" about the previous
> problem and my error message keeps appearing. I have even completely
> removed the database I am replicating from the publisher and subscriber
> machines, have used the procedure in the BOL to disable Pub & Dist
> (sp_removedbreplication,sp_dropdistpublisher,sp_dr opdistributiondb,sp_dropdistributor),
> and done a full restore of the offending database, however next time I
> setup the replication I get the same error.
> I maybe off track and identity columns are a no-no?
> Anyway sorry for the long winded story. Any help would be creatly
> appreciated. I am thinking of doing a compete SQL Server reload as it is
> only a test system, but if I don't have to....
> Cheers
> Peter Lock
> PalaceSoftware, Canberra OZ
>
|||Hi Hilary
Thank you for the prompt reply. I had already tried numerous new snapshots
and reloads, however I think I have worked out where the problem is (at
least it now replicates all articles without error). It looks like lazy mans
T-SQL is the problem.
I had a table with 2 columns ID (tinyint) as identity and ComputerName as
varchar(50). I set the PK in ID (which I didn't have before because I didn't
need it), and my stored procedure for adding a new entry was
INSERT Computers VALUES (@.ComputerName)
Once I changed my stored procedure to
INSERT Computers (ComputerName) VALUES (@.ComputerName)
the errors disappeared.
I'll give it a good test tonight and see how it goes.
Cheers
Peter Lock
PalaceSoftware, Canberra OZ
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uA%23LjfN7GHA.2288@.TK2MSFTNGP05.phx.gbl...
>I think you need to either update your replication stored procedures for
>the newly added pk, or update the tables at the subscriber. I think the
>best thing to do would be to generate a new snapshot and redistribute it.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Peter" <palace@.internode.on.net> wrote in message
> news:uFcN5QM7GHA.1252@.TK2MSFTNGP04.phx.gbl...
>
|||OK, I take it the errors were occurring on the publisher side, and in this
case were probably not replication related.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter" <palace@.internode.on.net> wrote in message
news:ugK3chP7GHA.3452@.TK2MSFTNGP05.phx.gbl...
> Hi Hilary
> Thank you for the prompt reply. I had already tried numerous new snapshots
> and reloads, however I think I have worked out where the problem is (at
> least it now replicates all articles without error). It looks like lazy
> mans T-SQL is the problem.
> I had a table with 2 columns ID (tinyint) as identity and ComputerName as
> varchar(50). I set the PK in ID (which I didn't have before because I
> didn't need it), and my stored procedure for adding a new entry was
> INSERT Computers VALUES (@.ComputerName)
> Once I changed my stored procedure to
> INSERT Computers (ComputerName) VALUES (@.ComputerName)
> the errors disappeared.
> I'll give it a good test tonight and see how it goes.
> Cheers
> Peter Lock
> PalaceSoftware, Canberra OZ
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uA%23LjfN7GHA.2288@.TK2MSFTNGP05.phx.gbl...
>

column dependency search

I posted this yesterday with no luck. I thought Id repost but be a bit more
descriptive. I want to be able to figure out where a column is explicitly
being called.
create proc myproc
as
select MyColumn from Table1
create view myview
as
select * from Table1
In the above scenario, I could write a query to search for MyColumn.
Therefore, MyProc would be returned in the results, but not MyView as it
didn't specifically name the MyColumn column. Is there a way?
TIA,
ChrisRtry using sp_depends [object name]
"ChrisR" wrote:

> I posted this yesterday with no luck. I thought Id repost but be a bit mor
e
> descriptive. I want to be able to figure out where a column is explicitly
> being called.
> create proc myproc
> as
> select MyColumn from Table1
> create view myview
> as
> select * from Table1
> In the above scenario, I could write a query to search for MyColumn.
> Therefore, MyProc would be returned in the results, but not MyView as it
> didn't specifically name the MyColumn column. Is there a way?
> --
> TIA,
> ChrisR|||You would have to add logic to look for tables with an * in the select list.
I am not sure how to do that but just another of many good reasons whey you
should never use * in production code.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:4B53BD66-CA89-4C7D-AF7D-CC9D55D6969F@.microsoft.com...
>I posted this yesterday with no luck. I thought Id repost but be a bit more
> descriptive. I want to be able to figure out where a column is explicitly
> being called.
> create proc myproc
> as
> select MyColumn from Table1
> create view myview
> as
> select * from Table1
> In the above scenario, I could write a query to search for MyColumn.
> Therefore, MyProc would be returned in the results, but not MyView as it
> didn't specifically name the MyColumn column. Is there a way?
> --
> TIA,
> ChrisR|||sp_depends is for a whole table, not a column.
--
TIA,
ChrisR
"FredG" wrote:
[vbcol=seagreen]
> try using sp_depends [object name]
> "ChrisR" wrote:
>|||I want to ignore tables with an *.
TIA,
ChrisR
"Andrew J. Kelly" wrote:

> You would have to add logic to look for tables with an * in the select lis
t.
> I am not sure how to do that but just another of many good reasons whey yo
u
> should never use * in production code.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:4B53BD66-CA89-4C7D-AF7D-CC9D55D6969F@.microsoft.com...
>
>|||Chris,
You have me confused then as to what you are asking. Are you looking for
sample code for the query to execute to find the column? If so then how
about this:
CREATE PROCEDURE find_text
@.Search VARCHAR(200)
-- Search the stored procedures and Scheduled Jobs for the text string
passed
-- and show which objects it is found.
AS
SET NOCOUNT ON
SET @.Search = '%' + @.Search + '%'
-- Create a table var to store each part
DECLARE @.tmpSearch TABLE ([Object Name] VARCHAR(50),[Sub Level]
VARCHAR(40),[Object Type] VARCHAR(24))
INSERT INTO @.tmpSearch
SELECT DISTINCT a.Name,SPACE(40) AS 'Sub Level',
CASE WHEN a.xType = 'C' THEN 'Check Constraint'
WHEN a.xType = 'D' THEN 'Default constraint'
WHEN a.xType = 'F' THEN 'FK constraint'
WHEN a.xType = 'L' THEN 'Log'
WHEN a.xType = 'FN' THEN 'Scaler Function'
WHEN a.xType = 'IF' THEN 'Inline Function'
WHEN a.xType = 'P' THEN 'Stored Procedure'
WHEN a.xType = 'PK' THEN 'PK constraint'
WHEN a.xType = 'RF' THEN 'Replication Filer'
WHEN a.xType = 'S' THEN 'System Table'
WHEN a.xType = 'TF' THEN 'Table Function'
WHEN a.xType = 'TR' THEN 'Trigger'
WHEN a.xType = 'U' THEN 'User Table'
WHEN a.xType = 'UQ' THEN 'Unique constraint'
WHEN a.xType = 'V' THEN 'View'
WHEN a.xType = 'X' THEN 'Extended SP'
ELSE 'Other' END AS Object_Type
FROM dbo.sysObjects AS a WITH (NOLOCK)
INNER JOIN dbo.syscomments AS b WITH (NOLOCK)
ON a.ID = b.ID
WHERE ENCRYPTED = 0
AND Text LIKE @.Search
INSERT INTO @.tmpSearch
SELECT A.[Name],B.Step_Name,'Scheduled Job' AS Object_Type
FROM MSDB.dbo.SYSJOBS AS A WITH (NOLOCK) INNER JOIN
MSDB.dbo.SYSJOBSTEPS AS B WITH (NOLOCK)
ON A.JOB_ID = B.JOB_ID
WHERE B.Command LIKE @.Search
SELECT [Object Name],[Object Type],[Sub Level]
FROM @.tmpSearch
ORDER BY [Object Type],[Object Name]
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9744BE53-07DF-4427-96F5-D0C10EFBD388@.microsoft.com...[vbcol=seagreen]
>I want to ignore tables with an *.
> --
> TIA,
> ChrisR
>
> "Andrew J. Kelly" wrote:
>|||You can search syscomments.
Josh
declare @.keyword varchar(32)
set @.keyword = 'MyColumn'
select
o.[id] as sid,
o.[name] as procname,
c.colid,
cast(c.[text] as varchar(4000)) as proctext
into #mythingy
from sysobjects o
inner join syscomments c
on o.id=c.id
where xtype in ('p','if')
and left(o.[name],3) not in ('dmf','dt_')
--
and charindex(@.keyword ,c.[text])>1
order by o.[name], c.colid
----
select * from #mythingy
----
-- and then, for a few pennies more, you can parse the lines:
set nocount on
declare @.jx int, @.cmd varchar(255), @.t char(1), @.lf char(1), @.lx int
declare @.sid int, @.procname varchar(255), @.colid int, @.proctext
varchar(4000)
declare @.keyword varchar(32)
set @.keyword = 'MyColumn'
declare @.myhits table
(
sid int,
procname varchar(255),
colid int,
jx int,
cmd varchar(4000)
)
declare ix cursor for
select sid, procname, colid, proctext from #mythingy
----
set @.t = char(9)
set @.lf = char(10)
OPEN ix
FETCH NEXT FROM ix into @.sid, @.procname, @.colid, @.proctext
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.jx = charindex(@.keyword,@.proctext)
while @.jx > 0
begin
set @.cmd = substring(@.proctext,@.jx,60)
set @.lx = charindex(@.lf,@.cmd)
if @.lx > 0
set @.cmd = left(@.cmd,@.lx-1)
set @.cmd = replace(@.cmd,@.t,' ')
print str(@.sid) + ' ' + @.procname + ' ' + str(@.colid) + ' ' +
str(@.jx) + ' ' + @.cmd
insert into @.myhits
values (@.sid, @.procname, @.colid, @.jx, @.cmd)
set @.jx = @.jx + 8
set @.jx = charindex(@.keyword,@.proctext, @.jx)
end
FETCH NEXT FROM ix into @.sid, @.procname, @.colid, @.proctext
print ''
END
CLOSE ix
DEALLOCATE ix
select *
from @.myhits
order by cmd, procname, colid, jx

On Wed, 21 Dec 2005 07:45:03 -0800, ChrisR
<ChrisR@.discussions.microsoft.com> wrote:
>I posted this yesterday with no luck. I thought Id repost but be a bit more
>descriptive. I want to be able to figure out where a column is explicitly
>being called.
>create proc myproc
>as
>select MyColumn from Table1
>create view myview
>as
>select * from Table1
>In the above scenario, I could write a query to search for MyColumn.
>Therefore, MyProc would be returned in the results, but not MyView as it
>didn't specifically name the MyColumn column. Is there a way?

column dependency search

I posted this yesterday with no luck. I thought Id repost but be a bit more
descriptive. I want to be able to figure out where a column is explicitly
being called.
create proc myproc
as
select MyColumn from Table1
create view myview
as
select * from Table1
In the above scenario, I could write a query to search for MyColumn.
Therefore, MyProc would be returned in the results, but not MyView as it
didn't specifically name the MyColumn column. Is there a way?
TIA,
ChrisR
try using sp_depends [object name]
"ChrisR" wrote:

> I posted this yesterday with no luck. I thought Id repost but be a bit more
> descriptive. I want to be able to figure out where a column is explicitly
> being called.
> create proc myproc
> as
> select MyColumn from Table1
> create view myview
> as
> select * from Table1
> In the above scenario, I could write a query to search for MyColumn.
> Therefore, MyProc would be returned in the results, but not MyView as it
> didn't specifically name the MyColumn column. Is there a way?
> --
> TIA,
> ChrisR
|||You would have to add logic to look for tables with an * in the select list.
I am not sure how to do that but just another of many good reasons whey you
should never use * in production code.
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:4B53BD66-CA89-4C7D-AF7D-CC9D55D6969F@.microsoft.com...
>I posted this yesterday with no luck. I thought Id repost but be a bit more
> descriptive. I want to be able to figure out where a column is explicitly
> being called.
> create proc myproc
> as
> select MyColumn from Table1
> create view myview
> as
> select * from Table1
> In the above scenario, I could write a query to search for MyColumn.
> Therefore, MyProc would be returned in the results, but not MyView as it
> didn't specifically name the MyColumn column. Is there a way?
> --
> TIA,
> ChrisR
|||sp_depends is for a whole table, not a column.
TIA,
ChrisR
"FredG" wrote:
[vbcol=seagreen]
> try using sp_depends [object name]
> "ChrisR" wrote:
|||I want to ignore tables with an *.
TIA,
ChrisR
"Andrew J. Kelly" wrote:

> You would have to add logic to look for tables with an * in the select list.
> I am not sure how to do that but just another of many good reasons whey you
> should never use * in production code.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:4B53BD66-CA89-4C7D-AF7D-CC9D55D6969F@.microsoft.com...
>
>
|||Chris,
You have me confused then as to what you are asking. Are you looking for
sample code for the query to execute to find the column? If so then how
about this:
CREATE PROCEDURE find_text
@.Search VARCHAR(200)
-- Search the stored procedures and Scheduled Jobs for the text string
passed
-- and show which objects it is found.
AS
SET NOCOUNT ON
SET @.Search = '%' + @.Search + '%'
-- Create a table var to store each part
DECLARE @.tmpSearch TABLE ([Object Name] VARCHAR(50),[Sub Level]
VARCHAR(40),[Object Type] VARCHAR(24))
INSERT INTO @.tmpSearch
SELECT DISTINCT a.Name,SPACE(40) AS 'Sub Level',
CASE WHEN a.xType = 'C' THEN 'Check Constraint'
WHEN a.xType = 'D' THEN 'Default constraint'
WHEN a.xType = 'F' THEN 'FK constraint'
WHEN a.xType = 'L' THEN 'Log'
WHEN a.xType = 'FN' THEN 'Scaler Function'
WHEN a.xType = 'IF' THEN 'Inline Function'
WHEN a.xType = 'P' THEN 'Stored Procedure'
WHEN a.xType = 'PK' THEN 'PK constraint'
WHEN a.xType = 'RF' THEN 'Replication Filer'
WHEN a.xType = 'S' THEN 'System Table'
WHEN a.xType = 'TF' THEN 'Table Function'
WHEN a.xType = 'TR' THEN 'Trigger'
WHEN a.xType = 'U' THEN 'User Table'
WHEN a.xType = 'UQ' THEN 'Unique constraint'
WHEN a.xType = 'V' THEN 'View'
WHEN a.xType = 'X' THEN 'Extended SP'
ELSE 'Other' END AS Object_Type
FROM dbo.sysObjects AS a WITH (NOLOCK)
INNER JOIN dbo.syscomments AS b WITH (NOLOCK)
ON a.ID = b.ID
WHERE ENCRYPTED = 0
AND Text LIKE @.Search
INSERT INTO @.tmpSearch
SELECT A.[Name],B.Step_Name,'Scheduled Job' AS Object_Type
FROM MSDB.dbo.SYSJOBS AS A WITH (NOLOCK) INNER JOIN
MSDB.dbo.SYSJOBSTEPS AS B WITH (NOLOCK)
ON A.JOB_ID = B.JOB_ID
WHERE B.Command LIKE @.Search
SELECT [Object Name],[Object Type],[Sub Level]
FROM @.tmpSearch
ORDER BY [Object Type],[Object Name]
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9744BE53-07DF-4427-96F5-D0C10EFBD388@.microsoft.com...[vbcol=seagreen]
>I want to ignore tables with an *.
> --
> TIA,
> ChrisR
>
> "Andrew J. Kelly" wrote:
|||You can search syscomments.
Josh
declare @.keyword varchar(32)
set @.keyword = 'MyColumn'
select
o.[id] as sid,
o.[name] as procname,
c.colid,
cast(c.[text] as varchar(4000)) as proctext
into #mythingy
from sysobjects o
inner join syscomments c
on o.id=c.id
where xtype in ('p','if')
and left(o.[name],3) not in ('dmf','dt_')
and charindex(@.keyword ,c.[text])>1
order by o.[name], c.colid
select * from #mythingy
-- and then, for a few pennies more, you can parse the lines:
set nocount on
declare @.jx int, @.cmd varchar(255), @.t char(1), @.lf char(1), @.lx int
declare @.sid int, @.procname varchar(255), @.colid int, @.proctext
varchar(4000)
declare @.keyword varchar(32)
set @.keyword = 'MyColumn'
declare @.myhits table
(
sidint,
procnamevarchar(255),
colidint,
jxint,
cmdvarchar(4000)
)
declare ix cursor for
select sid, procname, colid, proctext from #mythingy
set @.t = char(9)
set @.lf = char(10)
OPEN ix
FETCH NEXT FROM ix into @.sid, @.procname, @.colid, @.proctext
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.jx = charindex(@.keyword,@.proctext)
while @.jx > 0
begin
set @.cmd = substring(@.proctext,@.jx,60)
set @.lx = charindex(@.lf,@.cmd)
if @.lx > 0
set @.cmd = left(@.cmd,@.lx-1)
set @.cmd = replace(@.cmd,@.t,' ')
print str(@.sid) + ' ' + @.procname + ' ' + str(@.colid) + ' ' +
str(@.jx) + ' ' + @.cmd
insert into @.myhits
values (@.sid, @.procname, @.colid, @.jx, @.cmd)
set @.jx = @.jx + 8
set @.jx = charindex(@.keyword,@.proctext, @.jx)
end
FETCH NEXT FROM ix into @.sid, @.procname, @.colid, @.proctext
print ''
END
CLOSE ix
DEALLOCATE ix
select *
from @.myhits
order by cmd, procname, colid, jx
On Wed, 21 Dec 2005 07:45:03 -0800, ChrisR
<ChrisR@.discussions.microsoft.com> wrote:
>I posted this yesterday with no luck. I thought Id repost but be a bit more
>descriptive. I want to be able to figure out where a column is explicitly
>being called.
>create proc myproc
>as
>select MyColumn from Table1
>create view myview
>as
>select * from Table1
>In the above scenario, I could write a query to search for MyColumn.
>Therefore, MyProc would be returned in the results, but not MyView as it
>didn't specifically name the MyColumn column. Is there a way?

column dependency search

I posted this yesterday with no luck. I thought Id repost but be a bit more
descriptive. I want to be able to figure out where a column is explicitly
being called.
create proc myproc
as
select MyColumn from Table1
create view myview
as
select * from Table1
In the above scenario, I could write a query to search for MyColumn.
Therefore, MyProc would be returned in the results, but not MyView as it
didn't specifically name the MyColumn column. Is there a way?
--
TIA,
ChrisRtry using sp_depends [object name]
"ChrisR" wrote:
> I posted this yesterday with no luck. I thought Id repost but be a bit more
> descriptive. I want to be able to figure out where a column is explicitly
> being called.
> create proc myproc
> as
> select MyColumn from Table1
> create view myview
> as
> select * from Table1
> In the above scenario, I could write a query to search for MyColumn.
> Therefore, MyProc would be returned in the results, but not MyView as it
> didn't specifically name the MyColumn column. Is there a way?
> --
> TIA,
> ChrisR|||You would have to add logic to look for tables with an * in the select list.
I am not sure how to do that but just another of many good reasons whey you
should never use * in production code.
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:4B53BD66-CA89-4C7D-AF7D-CC9D55D6969F@.microsoft.com...
>I posted this yesterday with no luck. I thought Id repost but be a bit more
> descriptive. I want to be able to figure out where a column is explicitly
> being called.
> create proc myproc
> as
> select MyColumn from Table1
> create view myview
> as
> select * from Table1
> In the above scenario, I could write a query to search for MyColumn.
> Therefore, MyProc would be returned in the results, but not MyView as it
> didn't specifically name the MyColumn column. Is there a way?
> --
> TIA,
> ChrisR|||sp_depends is for a whole table, not a column.
--
TIA,
ChrisR
"FredG" wrote:
> try using sp_depends [object name]
> "ChrisR" wrote:
> > I posted this yesterday with no luck. I thought Id repost but be a bit more
> > descriptive. I want to be able to figure out where a column is explicitly
> > being called.
> >
> > create proc myproc
> > as
> > select MyColumn from Table1
> >
> > create view myview
> > as
> > select * from Table1
> >
> > In the above scenario, I could write a query to search for MyColumn.
> > Therefore, MyProc would be returned in the results, but not MyView as it
> > didn't specifically name the MyColumn column. Is there a way?
> >
> > --
> > TIA,
> > ChrisR|||I want to ignore tables with an *.
--
TIA,
ChrisR
"Andrew J. Kelly" wrote:
> You would have to add logic to look for tables with an * in the select list.
> I am not sure how to do that but just another of many good reasons whey you
> should never use * in production code.
> --
> Andrew J. Kelly SQL MVP
>
> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
> news:4B53BD66-CA89-4C7D-AF7D-CC9D55D6969F@.microsoft.com...
> >I posted this yesterday with no luck. I thought Id repost but be a bit more
> > descriptive. I want to be able to figure out where a column is explicitly
> > being called.
> >
> > create proc myproc
> > as
> > select MyColumn from Table1
> >
> > create view myview
> > as
> > select * from Table1
> >
> > In the above scenario, I could write a query to search for MyColumn.
> > Therefore, MyProc would be returned in the results, but not MyView as it
> > didn't specifically name the MyColumn column. Is there a way?
> >
> > --
> > TIA,
> > ChrisR
>
>|||Chris,
You have me confused then as to what you are asking. Are you looking for
sample code for the query to execute to find the column? If so then how
about this:
CREATE PROCEDURE find_text
@.Search VARCHAR(200)
-- Search the stored procedures and Scheduled Jobs for the text string
passed
-- and show which objects it is found.
AS
SET NOCOUNT ON
SET @.Search = '%' + @.Search + '%'
-- Create a table var to store each part
DECLARE @.tmpSearch TABLE ([Object Name] VARCHAR(50),[Sub Level]
VARCHAR(40),[Object Type] VARCHAR(24))
INSERT INTO @.tmpSearch
SELECT DISTINCT a.Name,SPACE(40) AS 'Sub Level',
CASE WHEN a.xType = 'C' THEN 'Check Constraint'
WHEN a.xType = 'D' THEN 'Default constraint'
WHEN a.xType = 'F' THEN 'FK constraint'
WHEN a.xType = 'L' THEN 'Log'
WHEN a.xType = 'FN' THEN 'Scaler Function'
WHEN a.xType = 'IF' THEN 'Inline Function'
WHEN a.xType = 'P' THEN 'Stored Procedure'
WHEN a.xType = 'PK' THEN 'PK constraint'
WHEN a.xType = 'RF' THEN 'Replication Filer'
WHEN a.xType = 'S' THEN 'System Table'
WHEN a.xType = 'TF' THEN 'Table Function'
WHEN a.xType = 'TR' THEN 'Trigger'
WHEN a.xType = 'U' THEN 'User Table'
WHEN a.xType = 'UQ' THEN 'Unique constraint'
WHEN a.xType = 'V' THEN 'View'
WHEN a.xType = 'X' THEN 'Extended SP'
ELSE 'Other' END AS Object_Type
FROM dbo.sysObjects AS a WITH (NOLOCK)
INNER JOIN dbo.syscomments AS b WITH (NOLOCK)
ON a.ID = b.ID
WHERE ENCRYPTED = 0
AND Text LIKE @.Search
INSERT INTO @.tmpSearch
SELECT A.[Name],B.Step_Name,'Scheduled Job' AS Object_Type
FROM MSDB.dbo.SYSJOBS AS A WITH (NOLOCK) INNER JOIN
MSDB.dbo.SYSJOBSTEPS AS B WITH (NOLOCK)
ON A.JOB_ID = B.JOB_ID
WHERE B.Command LIKE @.Search
SELECT [Object Name],[Object Type],[Sub Level]
FROM @.tmpSearch
ORDER BY [Object Type],[Object Name]
--
Andrew J. Kelly SQL MVP
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:9744BE53-07DF-4427-96F5-D0C10EFBD388@.microsoft.com...
>I want to ignore tables with an *.
> --
> TIA,
> ChrisR
>
> "Andrew J. Kelly" wrote:
>> You would have to add logic to look for tables with an * in the select
>> list.
>> I am not sure how to do that but just another of many good reasons whey
>> you
>> should never use * in production code.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
>> news:4B53BD66-CA89-4C7D-AF7D-CC9D55D6969F@.microsoft.com...
>> >I posted this yesterday with no luck. I thought Id repost but be a bit
>> >more
>> > descriptive. I want to be able to figure out where a column is
>> > explicitly
>> > being called.
>> >
>> > create proc myproc
>> > as
>> > select MyColumn from Table1
>> >
>> > create view myview
>> > as
>> > select * from Table1
>> >
>> > In the above scenario, I could write a query to search for MyColumn.
>> > Therefore, MyProc would be returned in the results, but not MyView as
>> > it
>> > didn't specifically name the MyColumn column. Is there a way?
>> >
>> > --
>> > TIA,
>> > ChrisR
>>|||You can search syscomments.
Josh
--
declare @.keyword varchar(32)
set @.keyword = 'MyColumn'
select
o.[id] as sid,
o.[name] as procname,
c.colid,
cast(c.[text] as varchar(4000)) as proctext
into #mythingy
from sysobjects o
inner join syscomments c
on o.id=c.id
where xtype in ('p','if')
and left(o.[name],3) not in ('dmf','dt_')
--
and charindex(@.keyword ,c.[text])>1
order by o.[name], c.colid
----
select * from #mythingy
----
-- and then, for a few pennies more, you can parse the lines:
set nocount on
declare @.jx int, @.cmd varchar(255), @.t char(1), @.lf char(1), @.lx int
declare @.sid int, @.procname varchar(255), @.colid int, @.proctext
varchar(4000)
declare @.keyword varchar(32)
set @.keyword = 'MyColumn'
declare @.myhits table
(
sid int,
procname varchar(255),
colid int,
jx int,
cmd varchar(4000)
)
declare ix cursor for
select sid, procname, colid, proctext from #mythingy
----
set @.t = char(9)
set @.lf = char(10)
OPEN ix
FETCH NEXT FROM ix into @.sid, @.procname, @.colid, @.proctext
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.jx = charindex(@.keyword,@.proctext)
while @.jx > 0
begin
set @.cmd = substring(@.proctext,@.jx,60)
set @.lx = charindex(@.lf,@.cmd)
if @.lx > 0
set @.cmd = left(@.cmd,@.lx-1)
set @.cmd = replace(@.cmd,@.t,' ')
print str(@.sid) + ' ' + @.procname + ' ' + str(@.colid) + ' ' +
str(@.jx) + ' ' + @.cmd
insert into @.myhits
values (@.sid, @.procname, @.colid, @.jx, @.cmd)
set @.jx = @.jx + 8
set @.jx = charindex(@.keyword,@.proctext, @.jx)
end
FETCH NEXT FROM ix into @.sid, @.procname, @.colid, @.proctext
print ''
END
CLOSE ix
DEALLOCATE ix
select *
from @.myhits
order by cmd, procname, colid, jx
On Wed, 21 Dec 2005 07:45:03 -0800, ChrisR
<ChrisR@.discussions.microsoft.com> wrote:
>I posted this yesterday with no luck. I thought Id repost but be a bit more
>descriptive. I want to be able to figure out where a column is explicitly
>being called.
>create proc myproc
>as
>select MyColumn from Table1
>create view myview
>as
>select * from Table1
>In the above scenario, I could write a query to search for MyColumn.
>Therefore, MyProc would be returned in the results, but not MyView as it
>didn't specifically name the MyColumn column. Is there a way?

column as variable

I have a problem that I'm sure is very simple to answer for anyone that knows a bit of T-SQL. In a stored procedure, I simply want to concatenate a string variable containing a column name into a Select statement.

For example:
I want to execute the following statement but using a variable for the column name:

Select * from tblmet1araw where JulianDay = 1

JulianDay is an integer
This is how I have my code set up:

declare @.xxx as varchar(20)
set @.theday = 'JulianDay'

select * from tblmet1araw where @.theday = 1

I get the following error:
Server: Msg 245, Level 16, State 1, Line 4
Syntax error converting the varchar value 'JulianDay' to a column of data type int.declare @.col varchar(10)
set @.col='id'
exec('select * from sysobjects where '+@.col+'=1')|||Thanks. That works.

Collations problems !

Hi All..
I'm having a bit of a problem with using collations in my MS
SQLServer2000..
The problem I have, is that I have 1 DB in 1 country,but I need to
support multible languages in the same table.. where I have stored
standart text using a Varchar..
The current collations doesnt support the polish and Danish language..
so what Collation will enable me to do that on the the table.. ?
I have thought about using this :
SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
for me..
Any Help ?
Best Regards
Lars Roed
*** Sent via Developersdex http://www.codecomments.com ***
<Lars> wrote in message news:ew$wqONbFHA.1660@.tk2msftngp13.phx.gbl...
> I'm having a bit of a problem with using collations in my MS
> SQLServer2000..
> The problem I have, is that I have 1 DB in 1 country,but I need to
> support multible languages in the same table.. where I have stored
> standart text using a Varchar..
> The current collations doesnt support the polish and Danish language..
> so what Collation will enable me to do that on the the table.. ?
> I have thought about using this :
> SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
> for me..
> Any Help ?
Go for nvarchar (unicode)
Regards, Wojtek
|||Hi there..
So using an Nvarchar will override the use of the collation.. and make
sure that the polish and estern european letters will remain intact ?
Thanx in advance
*** Sent via Developersdex http://www.codecomments.com ***
|||Nvarchar(Unicode) might be the best solution as suggested, but be aware of
the differencies compare to non-unicode. Unicode needs tvice the space and
the maximum size of a nchar or nvarchar column is 4000 characters versus
8000 characters for char and varchar. This might not at all be an issue for
you, but I find it usefull to be aware of.
If you haven't done it already it might be worth reading about collations in
Books On Line
Regards
Steen
Lars wrote:
> Hi there..
> So using an Nvarchar will override the use of the collation.. and make
> sure that the polish and estern european letters will remain intact ?
> Thanx in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||<Lars> wrote in message news:OVyAOiPbFHA.2876@.TK2MSFTNGP10.phx.gbl...
> So using an Nvarchar will override the use of the collation.. and make
> sure that the polish and estern european letters will remain intact ?
You will be able to store characters that do not occur simultaneously in
specific code pages (collations).
Converting varchar to nvarchar AFAIK should not corrupt any characters.
Collations occur in nvarchar columsn also, because they are responsible not
only for code pages. See BOL for more details.
Regards, Wojtek

Collations problems !

Hi All..
I'm having a bit of a problem with using collations in my MS
SQLServer2000..
The problem I have, is that I have 1 DB in 1 country,but I need to
support multible languages in the same table.. where I have stored
standart text using a Varchar..
The current collations doesnt support the polish and Danish language..
so what Collation will enable me to do that on the the table.. ?
I have thought about using this :
SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
for me..
Any Help '
Best Regards
Lars Roed
*** Sent via Developersdex http://www.codecomments.com ***<Lars> wrote in message news:ew$wqONbFHA.1660@.tk2msftngp13.phx.gbl...
> I'm having a bit of a problem with using collations in my MS
> SQLServer2000..
> The problem I have, is that I have 1 DB in 1 country,but I need to
> support multible languages in the same table.. where I have stored
> standart text using a Varchar..
> The current collations doesnt support the polish and Danish language..
> so what Collation will enable me to do that on the the table.. ?
> I have thought about using this :
> SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
> for me..
> Any Help '
Go for nvarchar (unicode)
Regards, Wojtek|||Hi there..
So using an Nvarchar will override the use of the collation.. and make
sure that the polish and estern european letters will remain intact '
Thanx in advance
*** Sent via Developersdex http://www.codecomments.com ***|||Nvarchar(Unicode) might be the best solution as suggested, but be aware of
the differencies compare to non-unicode. Unicode needs tvice the space and
the maximum size of a nchar or nvarchar column is 4000 characters versus
8000 characters for char and varchar. This might not at all be an issue for
you, but I find it usefull to be aware of.
If you haven't done it already it might be worth reading about collations in
Books On Line
Regards
Steen
Lars wrote:
> Hi there..
> So using an Nvarchar will override the use of the collation.. and make
> sure that the polish and estern european letters will remain intact '
> Thanx in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***|||<Lars> wrote in message news:OVyAOiPbFHA.2876@.TK2MSFTNGP10.phx.gbl...
> So using an Nvarchar will override the use of the collation.. and make
> sure that the polish and estern european letters will remain intact '
You will be able to store characters that do not occur simultaneously in
specific code pages (collations).
Converting varchar to nvarchar AFAIK should not corrupt any characters.
Collations occur in nvarchar columsn also, because they are responsible not
only for code pages. See BOL for more details.
Regards, Wojtek

Collations problems !

Hi All..
I'm having a bit of a problem with using collations in my MS
SQLServer2000..
The problem I have, is that I have 1 DB in 1 country,but I need to
support multible languages in the same table.. where I have stored
standart text using a Varchar..
The current collations doesnt support the polish and Danish language..
so what Collation will enable me to do that on the the table.. ?
I have thought about using this :
SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
for me..
Any Help '
Best Regards
Lars Roed
*** Sent via Developersdex http://www.developersdex.com ***<Lars> wrote in message news:ew$wqONbFHA.1660@.tk2msftngp13.phx.gbl...
> I'm having a bit of a problem with using collations in my MS
> SQLServer2000..
> The problem I have, is that I have 1 DB in 1 country,but I need to
> support multible languages in the same table.. where I have stored
> standart text using a Varchar..
> The current collations doesnt support the polish and Danish language..
> so what Collation will enable me to do that on the the table.. ?
> I have thought about using this :
> SQL_Latin1_General_CP1_CI_AS collation, but I'm not sure it will do it
> for me..
> Any Help '
Go for nvarchar (unicode)
Regards, Wojtek

Collation problem after migration from 2000 to 2005

Hi. Wondered if I might get a bit of advice on a minor collation problem I've experienced after migrating a database form SQL Server 2000 to 2005?

Users reported an error appearing in pages in a web-based application and I traced the problem to a stored procedure that was using a temporary table. I retained the original collation - SQL_Latin1_General_CP1_CI_AS - after migration and the error showing up was 'Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.' This makes sense as I guess temporary tables are assuming tempdb's collation. Resolution in this case was fairly simple, which was to apply an explicit collation to one of the columns in the temporary table (it only had three columns, only one requiring the collation). However the longer-term question is should I: (a) resolve similar issues in the same way as and when they arise; (b) change the collation of the migrated database to that of the default for system and new databases under 2005 - i.e. Latin1_General_CI_AS; or (b) change the collation of tempdb and the other system databases to match that of the migrated databases (sounds risky)?

I would probably go for (a) or perhaps (b) but could use some expert advise on the matter.

Regards,

YaHozna.I would try to avoid working with different collations on your server/db/objects unless absolutely necessary. One option is to script your database and all objects contained within it without specifying the collation, run the script on your 2005 server so your db and all objects pick up that servers default collation, then export the data from the 2000 db into the 2005 db. Once complete, you should be all sync'd up.|||Thanks DBriles10. Is this something I can do post-migration? If so what would be the desired order of events? I'm assuming something like this:

1. script the database in its new location under 2005 without collation
2. back up and detach the database
3. run the script to create a new database

I was going to say next, restore the original database under a different name in order to be able to import data from it to the new database however wouldn't that cause conflicts with object names? I guess restoring the original database to a different server to do the data import. Any thoughts?

Regards,

YaHozna.|||Your plan looks good to me. The restore of the database as a new name will not cause conflicts with object names.

If we call the existing DB with the bad collation "FixMe", you will...

1. Script "FixMe" and be sure to not include collation settings
2. Backup and detach "FixMe" >> "FixMe" no longer exists on server
3. Run the script from step 1 to recreate "FixMe" >> "FixMe" now exists with the server's default collation settings
4. Restore database from step 2 as "FixMe_Import" >> No object name conflicts as the objects reside in separate DB's and therefore are unaware of each other.
5. Import data from "FixMe_Import" to "FixMe"

For the import, I suggest setting all contraints to NoCheck, disabling all triggers and be aware of which tables have identities so you can turn identity_insert on.

Good luck.|||Many thanks for the input.

Regards,

YaHozna.