Adding a new VarBinaryMax column to an existing table using SMO.

Consider the following code.

Table applicationSettingsTable = database.Tables["application_settings"];

Column logoColumn = new Column(applicationSettingsTable, "logo", DataType.VarBinaryMax);

applicationSettingsTable.Columns.Add(logoColumn);

applicationSettingsTable.Alter();

I don't understand why - but when I execute this code I end up with a column that has the data type varbinary(1) in the database rather than varbinary(MAX). I can't see anything in the msdn documentation that tells me I need to do something special for varbinary(max) columns, but maybe I do?

Any help much appreciated.

This appears to work tho...

Column logoColumn = new Column(applicationSettingsTable, "logo", DataType.VarBinary(-1));

Adding a new table to a complex join statement

All help appreciated.

I currently use the following select statement as a custom query.

SELECT * FROM sponinfo RIGHT JOIN (setinfo RIGHT JOIN glinfo ON
[setinfo].[SET_ID] =[glinfo].[gl_set]) ON [sponinfo].[spon_id]
=[glinfo].[gl_spon] WHERE ....... (etc, etc, etc...)

Inventory info is stored in a fourth table called invinfo, key
[invinfo].inv_id], which equals [glinfo].[gl_id].

I'm having syntax trouble getting the inventory table joined. Can anyone
show me the syntax to add the invinfo table to this query?
Thanks!
Steveright outer joins and i do not get along -- they're not hard to understand, just backwards

here is your query written left to right -- select list, the, columns, you, want
from glinfo
left outer
join setinfo
on glinfo.gl_set = setinfo.SET_ID
left outer
join sponinfo
on glinfo.gl_spon = sponinfo.spon_idnow to bring in the other table, just addleft outer
join invinfo
on glinfo.gl_id = invinfo.inv_idordinarily one sees square brackets and parentheses only in microsoft access, but if you really want them --select list, the, columns, you, want
from ( ( glinfo
left outer
join setinfo
on [glinfo].[gl_set] = [setinfo].[SET_ID] )
left outer
join sponinfo
on [glinfo].[gl_spon] = [sponinfo].[spon_id] )
left outer
join invinfo
on [glinfo].[gl_id] = [invinfo].[inv_id]
rudysql

Adding a new report to VS 2005

When I try to add a new Report to a Report Server project I get the
following error and I can't add it to project.
TITLE: Microsoft Visual Studio
--
Exception of type 'System.Runtime.InteropServices.COMException' was thrown.
--
BUTTONS:
OK
--
===================================
Exception of type 'System.Runtime.InteropServices.COMException' was thrown.
(Microsoft Visual Studio)
--
Program Location:
at
Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.OnHierarchyNodeAppending(IHierarchyNode
node, IHierarchyNode parentNode)
at
Microsoft.DataWarehouse.VsIntegration.Hierarchy.Hierarchy.Add(IHierarchyNode
node, IHierarchyNode parentNode)
at
Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.AddExistingFileToProject(IFileProjectNode&
parentNode, String name, String fullPath, VSADDITEMOPERATION
dwAddItemOperation)
at
Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.ProjectItemsExt.AddFromFile(String
FileName)
at
Microsoft.ReportDesigner.Wizards.ReportWizardForm.OnFinish(CancelEventArgs
e)Don't know if this is related. In the RTM, there are issues with the add
report wizzard. If you use the add item, then select report everything seems
to works ok.
"Sorin Sandu" wrote:
> When I try to add a new Report to a Report Server project I get the
> following error and I can't add it to project.
> TITLE: Microsoft Visual Studio
> --
> Exception of type 'System.Runtime.InteropServices.COMException' was thrown.
> --
> BUTTONS:
> OK
> --
> ===================================> Exception of type 'System.Runtime.InteropServices.COMException' was thrown.
> (Microsoft Visual Studio)
> --
> Program Location:
> at
> Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.OnHierarchyNodeAppending(IHierarchyNode
> node, IHierarchyNode parentNode)
> at
> Microsoft.DataWarehouse.VsIntegration.Hierarchy.Hierarchy.Add(IHierarchyNode
> node, IHierarchyNode parentNode)
> at
> Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.AddExistingFileToProject(IFileProjectNode&
> parentNode, String name, String fullPath, VSADDITEMOPERATION
> dwAddItemOperation)
> at
> Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.ProjectItemsExt.AddFromFile(String
> FileName)
> at
> Microsoft.ReportDesigner.Wizards.ReportWizardForm.OnFinish(CancelEventArgs
> e)
>
>|||If I add a new item and select Report nothing happens !!!!
"Wickherm" <Wickherm@.discussions.microsoft.com> a scris în mesajul de
ºtiri:1C7124B2-4E9D-42D1-AABF-FFD029FC2276@.microsoft.com...
> Don't know if this is related. In the RTM, there are issues with the add
> report wizzard. If you use the add item, then select report everything
> seems
> to works ok.
> "Sorin Sandu" wrote:
>> When I try to add a new Report to a Report Server project I get the
>> following error and I can't add it to project.
>> TITLE: Microsoft Visual Studio
>> --
>> Exception of type 'System.Runtime.InteropServices.COMException' was
>> thrown.
>> --
>> BUTTONS:
>> OK
>> --
>> ===================================>> Exception of type 'System.Runtime.InteropServices.COMException' was
>> thrown.
>> (Microsoft Visual Studio)
>> --
>> Program Location:
>> at
>> Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.OnHierarchyNodeAppending(IHierarchyNode
>> node, IHierarchyNode parentNode)
>> at
>> Microsoft.DataWarehouse.VsIntegration.Hierarchy.Hierarchy.Add(IHierarchyNode
>> node, IHierarchyNode parentNode)
>> at
>> Microsoft.DataWarehouse.VsIntegration.Shell.Project.FileProjectHierarchy.AddExistingFileToProject(IFileProjectNode&
>> parentNode, String name, String fullPath, VSADDITEMOPERATION
>> dwAddItemOperation)
>> at
>> Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.ProjectItemsExt.AddFromFile(String
>> FileName)
>> at
>> Microsoft.ReportDesigner.Wizards.ReportWizardForm.OnFinish(CancelEventArgs
>> e)
>>
>>

Adding a Linked Server.

Dear all,
I am doing the following code
EXEC sp_addlinkedserver
'MYSERVER',
N'SQL Server'
GO
And getting this error
Server: Msg 18452, Level 14, State 1, Line 12
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
What am I doing wrong ?Hi
Most likely the server is under Window Authentication. If you change the
server settings to Mixed (SQL Server and Windows) the problem will be gone.
"Patricia" <Patricia@.discussions.microsoft.com> wrote in message
news:AFD3B597-5DA3-4229-A03D-7ADDED58296C@.microsoft.com...
> Dear all,
> I am doing the following code
> EXEC sp_addlinkedserver
> 'MYSERVER',
> N'SQL Server'
> GO
> And getting this error
> Server: Msg 18452, Level 14, State 1, Line 12
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> What am I doing wrong ?
>|||Hello & Thanks for you reply.
I have checked both server, and both already had 'mixed' i.e. allow the log
on for both SQL Server and Windows accounts.
Thanks for your time
"Uri Dimant" wrote:

> Hi
> Most likely the server is under Window Authentication. If you change the
> server settings to Mixed (SQL Server and Windows) the problem will be gone
.
>
>
>
> "Patricia" <Patricia@.discussions.microsoft.com> wrote in message
> news:AFD3B597-5DA3-4229-A03D-7ADDED58296C@.microsoft.com...
>
>

Adding a full text search across multiple tables (with text fields)

Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:

Documents:
- DocumentID (int, PK)
- Title (varchar)
- Content (text)
- CategoryID (int, FK)

Categories:
- CategoryID (int, PK)
- CategoryName (varchar)

I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:

CREATE VIEW vw_Documents
WITH SCHEMABINDING AS
SELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryName
FROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID

GO
CREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndex
ON vw_Documents(DocumentID)

But this gave me the error:

Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.

I tried converting the Content to a varchar(max) within my view but it still didn't like.

Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.

Hi jgd12345,

After talking with my colleagues i find a solution:

First you need to change Content datatype from text to varchar(max);

Then set the following options:

SET CONCAT_NULL_YIELDS_NULL ON

|||Cheers, i'll test this out when i get back to the office. Thanks again.

add time to datetime value and split into date and time

Hi

i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.

the timezone field has values in minutes that i should add to my datetime field so i have the actual time.

afterwards i split the datetime into date and time.

the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).

could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?

i do all the calculations in my datasource (sql).

Thanks

V.

at the end i found it myself

this is how i solved it.

to add the timezone value (in minutes) to my datetime i used following :

DATEADD(minute, TimeZone, DATETIMEVALUE) AS ACTUALDATETIME

this expression will add the timezone value (example 60) to my current date time value (12/06/2007 08:00:00) and store it in

actualdatetime value (12/06/2007 09:00:00).

from then on it's easy to extract date and time from the result.

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 103) AS DATEVAL for the DATE

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 108) AS TIMEVAL for the time.

i thank myself for my research .. lol

Greetings to all

Add sum column to query

What's the best way to include an amount sum in a query if I also need the individual amounts? For example, I need the following columns:

order number

order amount

total amount

I tried using "with cube ", but the total number of columns in the query exceeds the allowable limit of 10.

select a.ordernum, a.orderamt, b.totalamt
from orders a, (select ordernum, sum(orderamt) from orders group by ordernum) b
where a.ordernum = b.ordernum

This will return data like:

ORDERNUM ORDERAMT TOTALAMT
1234 12.33 56.23
1234 11.25 56.23
1234 32.65 56.23
2345 ...ETC...|||

Hello,

Can you list the schema and an example of required output briefly as I'm not 100% sure what you want to do: do you want to select an order amount and sum what amount?

Sounds as if you should be using a subquery/join.

Cheers,

Rob

|||

to complete Phil's answer, I think this should be a little more efficient to do this

SELECT a.ordernum, a.orderamt, b.totalamt, c.totalamt...
FROM orders a
inner join (select ordernum, sum(orderamt) from orders group by ordernum) b on a.ordernum= b.ordernum
inner join (select ordernum, sum(orderamt) from orders group by ordernum) c on a.ordernum= c.ordernum

|||So you've just added a duplicate total column. Why?|||

Could it look something like this?

create table #x( ordernum int not null, amt int not null )
insert #x
select 1, 20 union all
select 1, 15 union all
select 2, 35 union all
select 2, 10 union all
select 2, 70
go

select ordernum, amt, sum(amt) as total
from #x
group by ordernum, amt
with rollup
go

ordernum amt total
-- -- --
1 15 15
1 20 20
1 NULL 35
2 10 10
2 35 35
2 70 70
2 NULL 115
NULL NULL 150

(8 row(s) affected)

/Kenneth

|||I tried using With Cube and With rollup, but they have a limit of 10 on the number of columns returned. The actual report I'm writing has over 20.|||So what are you trying to achieve?

Give us an example output of what you'd like the data to look like.|||I think your first post will solve the problem. Haven't had time to try it yet.|||

sorry, i made a mistake...

SELECT a.ordernum, a.orderamt, b.totalamt
FROM orders a
inner join (select sum(orderamt) from orders) b on a.ordernum= b.ordernum

I thought inner join could be more efficient than using where statement...

|||

stephane - Montpellier wrote:

sorry, i made a mistake...

SELECT a.ordernum, a.orderamt, b.totalamt
FROM orders a
inner join (select sum(orderamt) from orders) b on a.ordernum= b.ordernum

I thought inner join could be more efficient than using where statement...

Your statement won't work there either. You didn't include ordernum in your subquery.

Never-the-less, inner join and the join method I used are identical.

Add static calculated column AFTER dynamic columns in a matrix?

Is it possible to add a calculated static column to a matrix, after the
dynamic ones?
I have the following matrix to create:
Indicator Units 2001 2002 2003 2004 %Change
----
Volume M3 23 33 44 55 25%
Energy KJ 33 34 35 36 3%
etc.
The year values come from a dataset and may vary (sometimes only 1 year,
sometimes 5 or 6 years). The last column is not depending on the dataset and
takes the values of the last 2 dynamic columns to calculate the % change.
1) is it possible to add a static column after the dynamic ones?
2) can I refer to the last dynamic column (and the one before) in an
expression?
Thanks,
VincentI am not really sure if you can filter the totals at the
end of the dynamic columns or not , but you can place a
textbox after the matrix and specify the expression as
follows :
=First(Fields!FieldName1.Value) OR =Last(Fields!
FieldName2.Value)
OR you can have a table with just the footer and only one
column and specify the filter expression in this one place
holder in the table.
>--Original Message--
>Is it possible to add a calculated static column to a
matrix, after the
>dynamic ones?
>I have the following matrix to create:
>Indicator Units 2001 2002 2003 2004 %Change
>----
>Volume M3 23 33 44 55 25%
>Energy KJ 33 34 35 36 3%
>etc.
>The year values come from a dataset and may vary
(sometimes only 1 year,
>sometimes 5 or 6 years). The last column is not depending
on the dataset and
>takes the values of the last 2 dynamic columns to
calculate the % change.
>1) is it possible to add a static column after the
dynamic ones?
>2) can I refer to the last dynamic column (and the one
before) in an
>expression?
>Thanks,
>Vincent
>
>.
>

Add Informix linked server

I used the following syntax and was able to add a linked server to an
Informix database.
EXEC sp_addlinkedserver
@.server = 'Server1', -- defined in
-- SetNet32 on tab 'Server information',
-- field 'Informix Server'
@.provider = 'MSDASQL', -- DO NOT CHANGE !
@.datasrc = 'jaco', -- name of the
-- ODBC connection defined in step 3
@.srvproduct = 'Informix-CLI 3.12 (32 bit)';
Server1 is the Informix host defined in Setnet32. When I run the script, the
linked server is named after the host, that is "server1" However, this link
points to a single database defined in the datasource "jaco"
now I need to rename the Linked server "Server1" to something else so that I
can add another linked server using another datasource (wfi, for example).
1. How can I rename a linked server?
2. I ran "SELECT * FROM Server1.jaco.owner.station and got this error
message:
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].
Any help is greatly appreciated.
Thanks
BillBill,
1. I think you can change the linked server name with sp_setnetname. Try it.
2. Try using OPENQUERY() instead.
Jon Jahren
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:#aRY0YFyEHA.1984@.TK2MSFTNGP14.phx.gbl...
> I used the following syntax and was able to add a linked server to an
> Informix database.
> EXEC sp_addlinkedserver
> @.server = 'Server1', -- defined in
> -- SetNet32 on tab 'Server information',
> -- field 'Informix Server'
> @.provider = 'MSDASQL', -- DO NOT CHANGE !
> @.datasrc = 'jaco', -- name of the
> -- ODBC connection defined in step 3
> @.srvproduct = 'Informix-CLI 3.12 (32 bit)';
> Server1 is the Informix host defined in Setnet32. When I run the script,
the
> linked server is named after the host, that is "server1" However, this
link
> points to a single database defined in the datasource "jaco"
> now I need to rename the Linked server "Server1" to something else so that
I
> can add another linked server using another datasource (wfi, for example).
> 1. How can I rename a linked server?
> 2. I ran "SELECT * FROM Server1.jaco.owner.station and got this error
> message:
> Server: Msg 7312, Level 16, State 1, Line 1
> Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
> four-part name was supplied, but the provider does not expose the
necessary
> interfaces to use a catalog and/or schema.
> OLE DB error trace [Non-interface error].
>
> Any help is greatly appreciated.
> Thanks
> Bill
>
>
>|||Jon;
I think I dis something wrong now that I can't even establish the linked
server.
I followed the instructions fromt his page:
[url]http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=243&lngWId=5[/
url]
This worked the first time in establishing the linked server. I was able to
click on Tables & Views tabs of the linked server and see all the tables in
the linked (Informix) server.
However, I wasn't able to extract data from it.
I went on the to next step following the example below:
5. Now, you have to map logins from SQL server to logins on Informix server.
Execute statement:
EXEC sp_addlinkedsrvlogin 'PNDON7', 'false', 'sa', 'login_name', 'password'
You have to put your machine login name and password of course.
I was confused as to which login_name and password to use, so I used the
login name & password I always used to access Informix database (on linked
server).
After executing this sp_addlinkedsrvlogin, I wasn't able to do anything.
Clicking on the linked server Tables or Views tab generated an error message
regarding 'sa" passowrd error.
How do I go to cleanup all this mess and start a new?
Thanks
Bill
"Jon Jahren" <jon.jahren.fightspam@.sqlkompetanse.no> wrote in message
news:%23dU888JyEHA.1396@.tk2msftngp13.phx.gbl...
> Bill,
> 1. I think you can change the linked server name with sp_setnetname. Try
it.
> 2. Try using OPENQUERY() instead.
> Jon Jahren
> "Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
> news:#aRY0YFyEHA.1984@.TK2MSFTNGP14.phx.gbl...
> the
> link
that[vbcol=seagreen]
> I
example).[vbcol=seagreen]
> necessary
>

Add Informix linked server

I used the following syntax and was able to add a linked server to an
Informix database.
EXEC sp_addlinkedserver
@.server = 'Server1', -- defined in
-- SetNet32 on tab 'Server information',
-- field 'Informix Server'
@.provider = 'MSDASQL', -- DO NOT CHANGE !
@.datasrc = 'jaco', -- name of the
-- ODBC connection defined in step 3
@.srvproduct = 'Informix-CLI 3.12 (32 bit)';
Server1 is the Informix host defined in Setnet32. When I run the script, the
linked server is named after the host, that is "server1" However, this link
points to a single database defined in the datasource "jaco"
now I need to rename the Linked server "Server1" to something else so that I
can add another linked server using another datasource (wfi, for example).
1. How can I rename a linked server?
2. I ran "SELECT * FROM Server1.jaco.owner.station and got this error
message:
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
four-part name was supplied, but the provider does not expose the necessary
interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].
Any help is greatly appreciated.
Thanks
Bill
Bill,
1. I think you can change the linked server name with sp_setnetname. Try it.
2. Try using OPENQUERY() instead.
Jon Jahren
"Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
news:#aRY0YFyEHA.1984@.TK2MSFTNGP14.phx.gbl...
> I used the following syntax and was able to add a linked server to an
> Informix database.
> EXEC sp_addlinkedserver
> @.server = 'Server1', -- defined in
> -- SetNet32 on tab 'Server information',
> -- field 'Informix Server'
> @.provider = 'MSDASQL', -- DO NOT CHANGE !
> @.datasrc = 'jaco', -- name of the
> -- ODBC connection defined in step 3
> @.srvproduct = 'Informix-CLI 3.12 (32 bit)';
> Server1 is the Informix host defined in Setnet32. When I run the script,
the
> linked server is named after the host, that is "server1" However, this
link
> points to a single database defined in the datasource "jaco"
> now I need to rename the Linked server "Server1" to something else so that
I
> can add another linked server using another datasource (wfi, for example).
> 1. How can I rename a linked server?
> 2. I ran "SELECT * FROM Server1.jaco.owner.station and got this error
> message:
> Server: Msg 7312, Level 16, State 1, Line 1
> Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A
> four-part name was supplied, but the provider does not expose the
necessary
> interfaces to use a catalog and/or schema.
> OLE DB error trace [Non-interface error].
>
> Any help is greatly appreciated.
> Thanks
> Bill
>
>
>
|||Jon;
I think I dis something wrong now that I can't even establish the linked
server.
I followed the instructions fromt his page:
http://www.planet-source-code.com/vb...d=243&lngWId=5
This worked the first time in establishing the linked server. I was able to
click on Tables & Views tabs of the linked server and see all the tables in
the linked (Informix) server.
However, I wasn't able to extract data from it.
I went on the to next step following the example below:
5. Now, you have to map logins from SQL server to logins on Informix server.
Execute statement:
EXEC sp_addlinkedsrvlogin 'PNDON7', 'false', 'sa', 'login_name', 'password'
You have to put your machine login name and password of course.
I was confused as to which login_name and password to use, so I used the
login name & password I always used to access Informix database (on linked
server).
After executing this sp_addlinkedsrvlogin, I wasn't able to do anything.
Clicking on the linked server Tables or Views tab generated an error message
regarding 'sa" passowrd error.
How do I go to cleanup all this mess and start a new?
Thanks
Bill
"Jon Jahren" <jon.jahren.fightspam@.sqlkompetanse.no> wrote in message
news:%23dU888JyEHA.1396@.tk2msftngp13.phx.gbl...
> Bill,
> 1. I think you can change the linked server name with sp_setnetname. Try
it.[vbcol=seagreen]
> 2. Try using OPENQUERY() instead.
> Jon Jahren
> "Bill Nguyen" <billn_nospam_please@.jaco.com> wrote in message
> news:#aRY0YFyEHA.1984@.TK2MSFTNGP14.phx.gbl...
> the
> link
that[vbcol=seagreen]
> I
example).
> necessary
>

ADD IDENTITY PROPERTY WHEN THERE IS ALREADY DATA

Dear all,
Keep in mind the structure of the following table I would need alter ID
field and add an IDENTITY property but when data are already loaded.
The source table begin from 4200 as value in the first row and if before of
that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from 1
.
And so that it's a disaster.
Let me know how would I work out this issue.
CREATE TABLE [dbo].[VIA_DebugInfo] (
[Id] [int] NOT NULL ,
[Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
(
[Id]
) ON [PRIMARY]
GO
This one doesn't work, I haven't idea!
SET IDENTITY_INSERT via_debuginfo OFFCheck out the seed in the BOL
this is what I have done
CREATE TABLE [Claim] (
[ClaimID] [int] IDENTITY (15621, 1) NOT NULL ,
...
...
...
I would assume that you would ALTER the table add the new column, setting
its seed to the number you want
RObert
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:4DEDB4D8-BDA2-4985-BE46-287F1FADB537@.microsoft.com...
> Dear all,
> Keep in mind the structure of the following table I would need alter ID
> field and add an IDENTITY property but when data are already loaded.
> The source table begin from 4200 as value in the first row and if before
of
> that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from
1.
> And so that it's a disaster.
> Let me know how would I work out this issue.
> CREATE TABLE [dbo].[VIA_DebugInfo] (
> [Id] [int] NOT NULL ,
> [Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
> CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> GO
> This one doesn't work, I haven't idea!
> SET IDENTITY_INSERT via_debuginfo OFF|||Enric donotspam wrote:
> Dear all,
> Keep in mind the structure of the following table I would need alter ID
> field and add an IDENTITY property but when data are already loaded.
> The source table begin from 4200 as value in the first row and if before o
f
> that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from
1.
> And so that it's a disaster.
> Let me know how would I work out this issue.
> CREATE TABLE [dbo].[VIA_DebugInfo] (
> [Id] [int] NOT NULL ,
> [Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
> CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> GO
> This one doesn't work, I haven't idea!
> SET IDENTITY_INSERT via_debuginfo OFF
You can't make an existing column an IDENTITY.
You could add a new IDENTITY column. Using the IDENTITY seed argument
you can start the sequence at 4200 but you can't control which row gets
which value. Failing that you have to create a new table. Wisest option
is to avoid ascribing any business meaning to an IDENTITY column. If
you stick to that principle you won't have this problem.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||tHANKS TO BOTH FOR YOUR HELP
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Enric" wrote:

> Dear all,
> Keep in mind the structure of the following table I would need alter ID
> field and add an IDENTITY property but when data are already loaded.
> The source table begin from 4200 as value in the first row and if before o
f
> that I enable IDENTITY when I load the data into VIA_DEBUGINFO begins from
1.
> And so that it's a disaster.
> Let me know how would I work out this issue.
> CREATE TABLE [dbo].[VIA_DebugInfo] (
> [Id] [int] NOT NULL ,
> [Msg] [varchar] (255) COLLATE Traditional_Spanish_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[VIA_DebugInfo] WITH NOCHECK ADD
> CONSTRAINT [PK_VIA_DebugInfo] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> GO
> This one doesn't work, I haven't idea!
> SET IDENTITY_INSERT via_debuginfo OFF

Add Identity Column to Table Type UDF

Using SQL 2000 I have the following UDF that returns a table of hierarchical
info from an adjacency list. Is is possible to add an identity column to
@.retFindComponents or otherwise sort the table in the order of the hierarchy
ie root first, followed by descendents?
Thanks, Tad
CREATE FUNCTION dbo.Get_Component_Parts_From_Assembly_Part(@.Root_I D
int,@.IncludeRoot bit)
RETURNS @.retFindComponents TABLE(
Part_Assembly_ID int,
Part_Component_ID int,
Part_Component_Number nvarchar(50),
Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
IF (@.IncludeRoot=1)
BEGIN
INSERT INTO @.retFindComponents
SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
Part_id = @.root_id
END
DECLARE
@.Part_Component_ID int,
@.Part_Assembly_ID int,
@.Part_Component_Number nvarchar(50),
@.Part_Component_Type nchar(4)
DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Type
FROM Parts_Usage u
INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
WHERE Part_Assembly_ID=@.Root_ID
OPEN RetrieveComponents
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
@.Part_Component_Type
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
INSERT INTO @.retFindComponents
SELECT * FROM
dbo.Get_Component_Parts_From_Assembly_Part(@.Part_C omponent_ID,0)
INSERT INTO @.retFindComponents
VALUES(@.Part_Assembly_ID,@.Part_Component_ID,@.Part_ Component_Number,@.Part_Component_Type)
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID,
@.Part_Component_Number,@.Part_Component_Type
END
CLOSE RetrieveComponents
DEALLOCATE RetrieveComponents
RETURN
END
I think I found a solution but it took some reworking. I added an identity
column to the table @.retFindComponents and then changed the INSERT INTO
statements to use explicit column names to avoid conflicting with the
identity column. Because the function is applied recursively it appears that
the leaf nodes in the hierarchy are inserted first and the internal nodes
that branch off the root are added last. Therefore I moved the optional
INSERT INTO statement for @.IncludeRoot=1 to the end of the function. The
resulting table returned by the function is now in an order although I have
to sort by descending ID to get a top-to-bottom view of the hierarchy.
Tad
CREATE FUNCTION dbo.Get_Component_Parts_From_Assembly_Part(@.Root_I D
int,@.IncludeRoot bit)
RETURNS @.retFindComponents TABLE(
ID int identity(1,1),
Part_Assembly_ID int,
Part_Component_ID int,
Part_Component_Number nvarchar(50),
Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
DECLARE
@.Part_Component_ID int,
@.Part_Assembly_ID int,
@.Part_Component_Number nvarchar(50),
@.Part_Component_Type nchar(4)
DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Type
FROM Parts_Usage u
INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
WHERE Part_Assembly_ID=@.Root_ID
OPEN RetrieveComponents
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
@.Part_Component_Type
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
INSERT INTO @.retFindComponents
SELECT Part_Assembly_ID, Part_Component_ID, Part_Component_Number,
Part_Component_Type FROM
dbo.Get_Component_Parts_From_Assembly_Part(@.Part_C omponent_ID,0)
INSERT INTO @.retFindComponents
VALUES(@.Part_Assembly_ID,@.Part_Component_ID,@.Part_ Component_Number,@.Part_Component_Type)
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID,
@.Part_Component_Number,@.Part_Component_Type
END
IF (@.IncludeRoot=1)
BEGIN
INSERT INTO @.retFindComponents (Part_Assembly_ID, Part_Component_ID,
Part_Component_Number, Part_Component_Type)
SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
Part_id = @.root_id
END
CLOSE RetrieveComponents
DEALLOCATE RetrieveComponents
RETURN
END
"Tadwick" wrote:

> Using SQL 2000 I have the following UDF that returns a table of hierarchical
> info from an adjacency list. Is is possible to add an identity column to
> @.retFindComponents or otherwise sort the table in the order of the hierarchy
> ie root first, followed by descendents?
> Thanks, Tad
> CREATE FUNCTION dbo.Get_Component_Parts_From_Assembly_Part(@.Root_I D
> int,@.IncludeRoot bit)
> RETURNS @.retFindComponents TABLE(
> Part_Assembly_ID int,
> Part_Component_ID int,
> Part_Component_Number nvarchar(50),
> Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
> )
> AS
> BEGIN
> IF (@.IncludeRoot=1)
> BEGIN
> INSERT INTO @.retFindComponents
> SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
> Part_id = @.root_id
> END
> DECLARE
> @.Part_Component_ID int,
> @.Part_Assembly_ID int,
> @.Part_Component_Number nvarchar(50),
> @.Part_Component_Type nchar(4)
> DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
> SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Type
> FROM Parts_Usage u
> INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
> WHERE Part_Assembly_ID=@.Root_ID
> OPEN RetrieveComponents
> FETCH NEXT FROM RetrieveComponents
> INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
> @.Part_Component_Type
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> INSERT INTO @.retFindComponents
> SELECT * FROM
> dbo.Get_Component_Parts_From_Assembly_Part(@.Part_C omponent_ID,0)
> INSERT INTO @.retFindComponents
> VALUES(@.Part_Assembly_ID,@.Part_Component_ID,@.Part_ Component_Number,@.Part_Component_Type)
> FETCH NEXT FROM RetrieveComponents
> INTO @.Part_Assembly_ID, @.Part_Component_ID,
> @.Part_Component_Number,@.Part_Component_Type
> END
> CLOSE RetrieveComponents
> DEALLOCATE RetrieveComponents
> RETURN
> END
>

Add Identity Column to Table Type UDF

Using SQL 2000 I have the following UDF that returns a table of hierarchical
info from an adjacency list. Is is possible to add an identity column to
@.retFindComponents or otherwise sort the table in the order of the hierarchy
ie root first, followed by descendents?
Thanks, Tad
CREATE FUNCTION dbo.Get_Component_Parts_From_Assembly_Part(@.Root_ID
int,@.IncludeRoot bit)
RETURNS @.retFindComponents TABLE(
Part_Assembly_ID int,
Part_Component_ID int,
Part_Component_Number nvarchar(50),
Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
IF (@.IncludeRoot=1)
BEGIN
INSERT INTO @.retFindComponents
SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
Part_id = @.root_id
END
DECLARE
@.Part_Component_ID int,
@.Part_Assembly_ID int,
@.Part_Component_Number nvarchar(50),
@.Part_Component_Type nchar(4)
DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Type
FROM Parts_Usage u
INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
WHERE Part_Assembly_ID=@.Root_ID
OPEN RetrieveComponents
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
@.Part_Component_Type
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
INSERT INTO @.retFindComponents
SELECT * FROM
dbo.Get_Component_Parts_From_Assembly_Part(@.Part_Component_ID,0)
INSERT INTO @.retFindComponent
VALUES(@.Part_Assembly_ID,@.Part_Component_ID,@.Part_Component_Number,@.Part_Component_Type)
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID,
@.Part_Component_Number,@.Part_Component_Type
END
CLOSE RetrieveComponents
DEALLOCATE RetrieveComponents
RETURN
ENDI think I found a solution but it took some reworking. I added an identity
column to the table @.retFindComponents and then changed the INSERT INTO
statements to use explicit column names to avoid conflicting with the
identity column. Because the function is applied recursively it appears that
the leaf nodes in the hierarchy are inserted first and the internal nodes
that branch off the root are added last. Therefore I moved the optional
INSERT INTO statement for @.IncludeRoot=1 to the end of the function. The
resulting table returned by the function is now in an order although I have
to sort by descending ID to get a top-to-bottom view of the hierarchy.
Tad
CREATE FUNCTION dbo.Get_Component_Parts_From_Assembly_Part(@.Root_ID
int,@.IncludeRoot bit)
RETURNS @.retFindComponents TABLE(
ID int identity(1,1),
Part_Assembly_ID int,
Part_Component_ID int,
Part_Component_Number nvarchar(50),
Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
DECLARE
@.Part_Component_ID int,
@.Part_Assembly_ID int,
@.Part_Component_Number nvarchar(50),
@.Part_Component_Type nchar(4)
DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Type
FROM Parts_Usage u
INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
WHERE Part_Assembly_ID=@.Root_ID
OPEN RetrieveComponents
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
@.Part_Component_Type
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
INSERT INTO @.retFindComponents
SELECT Part_Assembly_ID, Part_Component_ID, Part_Component_Number,
Part_Component_Type FROM
dbo.Get_Component_Parts_From_Assembly_Part(@.Part_Component_ID,0)
INSERT INTO @.retFindComponent
VALUES(@.Part_Assembly_ID,@.Part_Component_ID,@.Part_Component_Number,@.Part_Component_Type)
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID,
@.Part_Component_Number,@.Part_Component_Type
END
IF (@.IncludeRoot=1)
BEGIN
INSERT INTO @.retFindComponents (Part_Assembly_ID, Part_Component_ID,
Part_Component_Number, Part_Component_Type)
SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
Part_id = @.root_id
END
CLOSE RetrieveComponents
DEALLOCATE RetrieveComponents
RETURN
END
"Tadwick" wrote:
> Using SQL 2000 I have the following UDF that returns a table of hierarchical
> info from an adjacency list. Is is possible to add an identity column to
> @.retFindComponents or otherwise sort the table in the order of the hierarchy
> ie root first, followed by descendents?
> Thanks, Tad
> CREATE FUNCTION dbo.Get_Component_Parts_From_Assembly_Part(@.Root_ID
> int,@.IncludeRoot bit)
> RETURNS @.retFindComponents TABLE(
> Part_Assembly_ID int,
> Part_Component_ID int,
> Part_Component_Number nvarchar(50),
> Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
> )
> AS
> BEGIN
> IF (@.IncludeRoot=1)
> BEGIN
> INSERT INTO @.retFindComponents
> SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
> Part_id = @.root_id
> END
> DECLARE
> @.Part_Component_ID int,
> @.Part_Assembly_ID int,
> @.Part_Component_Number nvarchar(50),
> @.Part_Component_Type nchar(4)
> DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
> SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Type
> FROM Parts_Usage u
> INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
> WHERE Part_Assembly_ID=@.Root_ID
> OPEN RetrieveComponents
> FETCH NEXT FROM RetrieveComponents
> INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
> @.Part_Component_Type
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> INSERT INTO @.retFindComponents
> SELECT * FROM
> dbo.Get_Component_Parts_From_Assembly_Part(@.Part_Component_ID,0)
> INSERT INTO @.retFindComponents
> VALUES(@.Part_Assembly_ID,@.Part_Component_ID,@.Part_Component_Number,@.Part_Component_Type)
> FETCH NEXT FROM RetrieveComponents
> INTO @.Part_Assembly_ID, @.Part_Component_ID,
> @.Part_Component_Number,@.Part_Component_Type
> END
> CLOSE RetrieveComponents
> DEALLOCATE RetrieveComponents
> RETURN
> END
>

Add Identity Column to Table Type UDF

Using SQL 2000 I have the following UDF that returns a table of hierarchical
info from an adjacency list. Is is possible to add an identity column to
@.retFindComponents or otherwise sort the table in the order of the hierarchy
ie root first, followed by descendents?
Thanks, Tad
CREATE FUNCTION dbo. Get_Component_Parts_From_Assembly_Part(@.
Root_ID
int,@.IncludeRoot bit)
RETURNS @.retFindComponents TABLE(
Part_Assembly_ID int,
Part_Component_ID int,
Part_Component_Number nvarchar(50),
Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
IF (@.IncludeRoot=1)
BEGIN
INSERT INTO @.retFindComponents
SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
Part_id = @.root_id
END
DECLARE
@.Part_Component_ID int,
@.Part_Assembly_ID int,
@.Part_Component_Number nvarchar(50),
@.Part_Component_Type nchar(4)
DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Type
FROM Parts_Usage u
INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
WHERE Part_Assembly_ID=@.Root_ID
OPEN RetrieveComponents
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
@.Part_Component_Type
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
INSERT INTO @.retFindComponents
SELECT * FROM
dbo. Get_Component_Parts_From_Assembly_Part(@.
Part_Component_ID,0)
INSERT INTO @.retFindComponents
VALUES(@.Part_Assembly_ID,@.Part_Component
_ID,@.Part_Component_Number,@.Part_Com
ponent_Type)
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID,
@.Part_Component_Number,@.Part_Component_T
ype
END
CLOSE RetrieveComponents
DEALLOCATE RetrieveComponents
RETURN
ENDI think I found a solution but it took some reworking. I added an identity
column to the table @.retFindComponents and then changed the INSERT INTO
statements to use explicit column names to avoid conflicting with the
identity column. Because the function is applied recursively it appears tha
t
the leaf nodes in the hierarchy are inserted first and the internal nodes
that branch off the root are added last. Therefore I moved the optional
INSERT INTO statement for @.IncludeRoot=1 to the end of the function. The
resulting table returned by the function is now in an order although I have
to sort by descending ID to get a top-to-bottom view of the hierarchy.
Tad
CREATE FUNCTION dbo. Get_Component_Parts_From_Assembly_Part(@.
Root_ID
int,@.IncludeRoot bit)
RETURNS @.retFindComponents TABLE(
ID int identity(1,1),
Part_Assembly_ID int,
Part_Component_ID int,
Part_Component_Number nvarchar(50),
Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
)
AS
BEGIN
DECLARE
@.Part_Component_ID int,
@.Part_Assembly_ID int,
@.Part_Component_Number nvarchar(50),
@.Part_Component_Type nchar(4)
DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Type
FROM Parts_Usage u
INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
WHERE Part_Assembly_ID=@.Root_ID
OPEN RetrieveComponents
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
@.Part_Component_Type
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
INSERT INTO @.retFindComponents
SELECT Part_Assembly_ID, Part_Component_ID, Part_Component_Number,
Part_Component_Type FROM
dbo. Get_Component_Parts_From_Assembly_Part(@.
Part_Component_ID,0)
INSERT INTO @.retFindComponents
VALUES(@.Part_Assembly_ID,@.Part_Component
_ID,@.Part_Component_Number,@.Part_Com
ponent_Type)
FETCH NEXT FROM RetrieveComponents
INTO @.Part_Assembly_ID, @.Part_Component_ID,
@.Part_Component_Number,@.Part_Component_T
ype
END
IF (@.IncludeRoot=1)
BEGIN
INSERT INTO @.retFindComponents (Part_Assembly_ID, Part_Component_ID,
Part_Component_Number, Part_Component_Type)
SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
Part_id = @.root_id
END
CLOSE RetrieveComponents
DEALLOCATE RetrieveComponents
RETURN
END
"Tadwick" wrote:

> Using SQL 2000 I have the following UDF that returns a table of hierarchic
al
> info from an adjacency list. Is is possible to add an identity column to
> @.retFindComponents or otherwise sort the table in the order of the hierarc
hy
> ie root first, followed by descendents?
> Thanks, Tad
> CREATE FUNCTION dbo. Get_Component_Parts_From_Assembly_Part(@.
Root_ID
> int,@.IncludeRoot bit)
> RETURNS @.retFindComponents TABLE(
> Part_Assembly_ID int,
> Part_Component_ID int,
> Part_Component_Number nvarchar(50),
> Part_Component_Type nchar(4) COLLATE SQL_Latin1_General_CP1_CI_AS
> )
> AS
> BEGIN
> IF (@.IncludeRoot=1)
> BEGIN
> INSERT INTO @.retFindComponents
> SELECT NULL, @.root_id, m.Part_Number,m.Part_Type FROM Parts m WHERE
> Part_id = @.root_id
> END
> DECLARE
> @.Part_Component_ID int,
> @.Part_Assembly_ID int,
> @.Part_Component_Number nvarchar(50),
> @.Part_Component_Type nchar(4)
> DECLARE RetrieveComponents CURSOR STATIC LOCAL FOR
> SELECT u.Part_Assembly_ID, u.Part_Component_ID, m.Part_Number, m.Part_Typ
e
> FROM Parts_Usage u
> INNER JOIN Parts m on m.Part_ID = u.Part_Component_ID
> WHERE Part_Assembly_ID=@.Root_ID
> OPEN RetrieveComponents
> FETCH NEXT FROM RetrieveComponents
> INTO @.Part_Assembly_ID, @.Part_Component_ID, @.Part_Component_Number,
> @.Part_Component_Type
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> INSERT INTO @.retFindComponents
> SELECT * FROM
> dbo. Get_Component_Parts_From_Assembly_Part(@.
Part_Component_ID,0)
> INSERT INTO @.retFindComponents
> VALUES(@.Part_Assembly_ID,@.Part_Compone
nt_ID,@.Part_Component_Number,@.Part
_Component_Type)
> FETCH NEXT FROM RetrieveComponents
> INTO @.Part_Assembly_ID, @.Part_Component_ID,
> @.Part_Component_Number,@.Part_Component_T
ype
> END
> CLOSE RetrieveComponents
> DEALLOCATE RetrieveComponents
> RETURN
> END
>

Add consecutive Id in Insert mode

Hi,
I am using the following procedure to fill Product table from LanTable:

BEGIN
insert into Product (Product_Num,Sticker_type)
Select LanTable.ProductNum,LanTable.StickerType
From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
END

In Product table i have an additional ID column.

I need to fill this field with consecutive numbers according to the Insert above.
If current ID value is 10 and I have 20 new products to insert, the ID field will be filled with 11 to 31.
How can I insert into ID column consecutive numbers starting with 11 that dependes on the number of rows added to Product table?
Thanks
YossiCould you set your ID attribute to an IDENTITY and let the system sort it out?|||Originally posted by Paul Young
Could you set your ID attribute to an IDENTITY and let the system sort it out?
Thanks for your replay.
The Id column has a meaning.
Not every time I will use the Insert routine the Id should get the consecutive value. Thats why i need to know the current Id and from that value to work on. with Identity the values will raise up to the roof.
Yours
Yossi|||Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.|||Originally posted by Paul Young
Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.

I will work on it....

Thanks a bunch mate.|||Originally posted by Paul Young
Okay, just wanted to rule out the obveous...

How about something like:
declare @.Product_Num int, @.Sticker_Type as int

select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
while (@.Product_Num is not null) begin
insert into Product (ID_Column,Product_Num,Sticker_type)
select max(ID_Column) + 1, @.Product_Num int, @.Sticker_Type from Product
select @.Product_Num = min(LanTable.ProductNum) From LanTable left Join Product p On LanTable.ProductNum=p.Product_Num where p.Product_Num is null and LanTable.ProductNum > @.Product_Num
select @.Sticker_Type = Sticker_Type from LanTable where ProductNum = @.Product_Num
end

Of course this is UNTESTED and you will need to change datatypes and attribute names, but look it over and let me know your thoughts.
Hi Paul,
I have tried that and I have the following problems:
1.Product_Num is a barcode string, min function cannot help us here.
2.ID_Column can be with no value at first (when the table is empty there is no value) but it cannot be nulled because its the PK so I got an Error trying to draw the Max value. (i can deal with that).

The main problem is 1.
Can you help with that?
Thanks|||Sorry,

1. Yes, min will work with strings. You will need to adjust the local variable datatypes to match your table attributes. Of course if ProductNum is not unique this approach probably wont work well but then I suspect you will have other problems as well. if this is still giving you fits, post the ddl from the Product and LanTable tables.

2. The quick fix on the ID_Column is to test for a null i.e. isnull(max(ID_COLUMN) + 1,1)|||Again, I would encourage you to look at using an Identity Attribute as it will accomplish exactly what you are trying to create. Yes the value just keeps growing but so what. IMHO trying to reclaime gaps in IDs is a wast of time.

Assuming you want to persue the DIY approach, what will you insert into the Product.LabelId attribute untill the trigger can assign the correct ID? You can't leave it blank or insert a default value? PK suggests Not Null and unique.

Triggers should always be written to handle multiple rows, it only takes a little more effort.

If your primary key is NOT unique (bad idea) you can probably make the trigger approach work. The key is to process each record of the temporary inserted table one at a time. Basically take the code I provided earlier and modify it to start a transaction, update the FreeID table to the next value, select the next ID, end the transaction, process one record from the isnerted table and then repeat until all reacords are processed.

Digest all of this and let me know.|||Originally posted by Paul Young
Again, I would encourage you to look at using an Identity Attribute as it will accomplish exactly what you are trying to create. Yes the value just keeps growing but so what. IMHO trying to reclaime gaps in IDs is a wast of time.

Assuming you want to persue the DIY approach, what will you insert into the Product.LabelId attribute untill the trigger can assign the correct ID? You can't leave it blank or insert a default value? PK suggests Not Null and unique.

Triggers should always be written to handle multiple rows, it only takes a little more effort.

If your primary key is NOT unique (bad idea) you can probably make the trigger approach work. The key is to process each record of the temporary inserted table one at a time. Basically take the code I provided earlier and modify it to start a transaction, update the FreeID table to the next value, select the next ID, end the transaction, process one record from the isnerted table and then repeat until all reacords are processed.

Digest all of this and let me know.

I will do that.
cheers|||I am glad to see that you are following Paul's advice. While I was reading your post, the description was a primary key field that needed to be incremented - and I was curious why you said you did not want to use IDENTITY (I could only think of the gaps as Paul mentioned - as a down side). Anyway, you changed your mind - so good luck.

add column to exiting table

I have a table that has the following columns:
ID SSN LastName FirstName TypeCodeID
With keeping the integrity of the information can I create a new column that
is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to
drop table and rather use the Alter table command. If so, how?
This would be new table format.
ID SSN LastName FirstName Active TypeCodeIDYou cannot add a column in the middle of a table. When you do an ALTER
TABLE to add a column, it is added at the and of the table.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
I have a table that has the following columns:
ID SSN LastName FirstName TypeCodeID
With keeping the integrity of the information can I create a new column that
is between FIRSTNAME and TypeCodeID named Active? I am hoping not having to
drop table and rather use the Alter table command. If so, how?
This would be new table format.
ID SSN LastName FirstName Active TypeCodeID|||Big D wrote:

> I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
that
> is between FIRSTNAME and TypeCodeID named Active? I am hoping not having
to
> drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
Hi,
Yes, you use ALTER TABLE to add a column to an existing table, but columns
do not have any order, just as you have no control over the order of rows.
You can specify any order you desire for columns when you retrieve rows with
a SELECT statement.
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--|||Short answer, you shouldn't care. This:
> ID SSN LastName FirstName Active TypeCodeID
is equivalent to:
> ID SSN LastName FirstName TypeCodeID Active
It has the same data, and the same properties. When you query the data, you
order the columns how you want, as you should never do:
select *
from table
Other than when you are testing.
That having been said, I know what you mean and sometimes you want to
reorder the columns for "ease of use." For this, you have to drop and
recreate the table in the order you want the columns.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
>I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
> that is between FIRSTNAME and TypeCodeID named Active? I am hoping not
> having to drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
>
>|||An idea may be using Enterprise Manager for such sort of work. Thru EM you
can do such thing as I did many times previously.
"Big D" <BigDaddy@.newsgroup.nospam> wrote in message
news:eXsabilYFHA.1868@.TK2MSFTNGP14.phx.gbl...
>I have a table that has the following columns:
> ID SSN LastName FirstName TypeCodeID
> With keeping the integrity of the information can I create a new column
> that is between FIRSTNAME and TypeCodeID named Active? I am hoping not
> having to drop table and rather use the Alter table command. If so, how?
>
> This would be new table format.
> ID SSN LastName FirstName Active TypeCodeID
>
>|||However, Big didn't want to drop and re-create the table, which is exactly w
hat EM does...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Wayne Right" <serdar@.senkronyazilim.com> wrote in message
news:%239C55roYFHA.1152@.tk2msftngp13.phx.gbl...
> An idea may be using Enterprise Manager for such sort of work. Thru EM you
can do such thing as I
> did many times previously.
> "Big D" <BigDaddy@.newsgroup.nospam> wrote in message news:eXsabilYFHA.1868
@.TK2MSFTNGP14.phx.gbl...
>|||Thanks everyone who helped.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23vF52XpYFHA.3616@.TK2MSFTNGP15.phx.gbl...
> However, Big didn't want to drop and re-create the table, which is exactly
> what EM does...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Wayne Right" <serdar@.senkronyazilim.com> wrote in message
> news:%239C55roYFHA.1152@.tk2msftngp13.phx.gbl...
>

add article to publication in Merge Replication

I added an article to one of my publication with sp_addmergearticle as a
no-sync. A subscriber replicated last night with the following error:
The merge process could not retrieve column information for table
'dbo.ChargeGroup'.
(Source: Merge Replication Provider (Agent); Error number: -2147201016)
Could not find stored procedure 'sp_sel_00BE6107195E4608967622C23A664DE2'.
(Source: CBD076\MGW (Data source); Error number: 2812)
Do I need to reinitialize all my subscribers? I'm trying to add the
article without having to manually touch each one of the subscribers since
all of them are remote.
Thanks
Tina
Yes you will have to. You must use the @.force_invalidate_snapshot switch
with a setting of 1.ie
sp_addmergearticle 'Publication','tableName','tableName',
@.force_invalidate_snapshot=1
The bad news is that you will have to regenerate your snapshot. The good
news is that only this new article/table will travel to the subscriber(s).
Before you do this, can you check a couple of things.
Do a
select *from sysmergearticles where
select_proc='sp_sel_00BE6107195E4608967622C23A664D E2' on your publication
database and the problem subscriber.
Do you get a row on the publisher and none on the subscriber?
Then do this on your publisher?
select name from sysmergearticles where
select_proc='sp_sel_00BE6107195E4608967622C23A664D E2'
using that name do this on your subscriber
select select_proc from sysmergearticles where name=the name you got on your
publisher.
If these don't agree you have to drop your publications and subscriptions
and start again. Were you using dynamic snapshots? I ran into this problem
when I deployed dynamic snapshots, but after fixing it the one time,
everything worked well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:e8hf7NGgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> I added an article to one of my publication with sp_addmergearticle as a
> no-sync. A subscriber replicated last night with the following error:
> The merge process could not retrieve column information for table
> 'dbo.ChargeGroup'.
> (Source: Merge Replication Provider (Agent); Error number: -2147201016)
> ----
--
> --
> Could not find stored procedure 'sp_sel_00BE6107195E4608967622C23A664DE2'.
> (Source: CBD076\MGW (Data source); Error number: 2812)
> ----
--
> --
> Do I need to reinitialize all my subscribers? I'm trying to add the
> article without having to manually touch each one of the subscribers since
> all of them are remote.
> Thanks
> Tina
>
|||I already added the article with force_invalidate_snapshot = 1 and
regenerated the snapshot.
The sp_sel_ does exist in my sysmergearticles table on the publisher. The
subscriber is remote so I haven't had a chance to check for the stored
procedure on the subscriber.
Yes, I am using dynamic filters. I sure hope I don't have to drop my
publications to fix this.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u9WkigGgEHA.556@.tk2msftngp13.phx.gbl...
> Yes you will have to. You must use the @.force_invalidate_snapshot switch
> with a setting of 1.ie
> sp_addmergearticle 'Publication','tableName','tableName',
> @.force_invalidate_snapshot=1
> The bad news is that you will have to regenerate your snapshot. The good
> news is that only this new article/table will travel to the subscriber(s).
> Before you do this, can you check a couple of things.
> Do a
> select *from sysmergearticles where
> select_proc='sp_sel_00BE6107195E4608967622C23A664D E2' on your publication
> database and the problem subscriber.
> Do you get a row on the publisher and none on the subscriber?
> Then do this on your publisher?
> select name from sysmergearticles where
> select_proc='sp_sel_00BE6107195E4608967622C23A664D E2'
> using that name do this on your subscriber
> select select_proc from sysmergearticles where name=the name you got on
your[vbcol=seagreen]
> publisher.
> If these don't agree you have to drop your publications and subscriptions
> and start again. Were you using dynamic snapshots? I ran into this problem
> when I deployed dynamic snapshots, but after fixing it the one time,
> everything worked well.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:e8hf7NGgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> ----
> --
'sp_sel_00BE6107195E4608967622C23A664DE2'.[vbcol=seagreen]
> ----
> --
since
>
|||You mentioned that you added it as no-sync.
Did you mean the subscription was added as a no sync subscription ?
If so then, the table needs to exist at the subscriber when merge is run.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> I already added the article with force_invalidate_snapshot = 1 and
> regenerated the snapshot.
> The sp_sel_ does exist in my sysmergearticles table on the publisher.
The[vbcol=seagreen]
> subscriber is remote so I haven't had a chance to check for the stored
> procedure on the subscriber.
> Yes, I am using dynamic filters. I sure hope I don't have to drop my
> publications to fix this.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u9WkigGgEHA.556@.tk2msftngp13.phx.gbl...
subscriber(s).[vbcol=seagreen]
publication[vbcol=seagreen]
> your
subscriptions[vbcol=seagreen]
problem[vbcol=seagreen]
a[vbcol=seagreen]
error:[vbcol=seagreen]
number: -2147201016)
> ----
> 'sp_sel_00BE6107195E4608967622C23A664DE2'.
> ----
> since
>
|||Hi Mahesh,
The table already exist at the subscriber.
Thanks
Tina
"Mahesh [MSFT]" <maheshrd@.hotmail.com> wrote in message
news:eHsJpXNgEHA.140@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> You mentioned that you added it as no-sync.
> Did you mean the subscription was added as a no sync subscription ?
> If so then, the table needs to exist at the subscriber when merge is run.
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> The
switch[vbcol=seagreen]
good[vbcol=seagreen]
> subscriber(s).
> publication
on[vbcol=seagreen]
> subscriptions
> problem
as[vbcol=seagreen]
> a
> error:
> number: -2147201016)
> ----
> ----
the
>
|||I ran into something a little similar with dynamic snapshots.
I deployed one group of 20 subscribers one month, and then another month I
deployed a second 20. IIRC the first 20 encountered this error.
I had to start from scratch. The error I got was with missing sp_ins*,
sp_upd*, and sp_del* procs on the subscriber.
After the second deployement we never had the error again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> I already added the article with force_invalidate_snapshot = 1 and
> regenerated the snapshot.
> The sp_sel_ does exist in my sysmergearticles table on the publisher.
The[vbcol=seagreen]
> subscriber is remote so I haven't had a chance to check for the stored
> procedure on the subscriber.
> Yes, I am using dynamic filters. I sure hope I don't have to drop my
> publications to fix this.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:u9WkigGgEHA.556@.tk2msftngp13.phx.gbl...
subscriber(s).[vbcol=seagreen]
publication[vbcol=seagreen]
> your
subscriptions[vbcol=seagreen]
problem[vbcol=seagreen]
a[vbcol=seagreen]
error:[vbcol=seagreen]
number: -2147201016)
> ----
> 'sp_sel_00BE6107195E4608967622C23A664DE2'.
> ----
> since
>
|||Thanks for all your input. I have many shoppes scheduled to replicate
Monday night. I'll assess the issue Tuesday morning and see where I need
to go from there. I'll let you know how it turns out.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23QzAfNTgEHA.2588@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> I ran into something a little similar with dynamic snapshots.
> I deployed one group of 20 subscribers one month, and then another month I
> deployed a second 20. IIRC the first 20 encountered this error.
> I had to start from scratch. The error I got was with missing sp_ins*,
> sp_upd*, and sp_del* procs on the subscriber.
> After the second deployement we never had the error again.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:ug9Z8HLgEHA.904@.TK2MSFTNGP09.phx.gbl...
> The
switch[vbcol=seagreen]
good[vbcol=seagreen]
> subscriber(s).
> publication
on[vbcol=seagreen]
> subscriptions
> problem
as[vbcol=seagreen]
> a
> error:
> number: -2147201016)
> ----
> ----
the
>

Ad hoc updates to system catalogs are not enabled

Hi,
Some time ago, following a security recommendation, I deleted
sp_change_users_login. Now I want it back. I scripted it as create
from another server. When I try to run it in QA as sa I get "Ad hoc
updates to system catalogs are not enabled The system administrator
must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 1, Procedure sp_change_users_login,
Line 197
Ad hoc updates to system catalogs are not enabled. The system
administrator must reconfigure SQL Server to allow this.
Thanks,
PeterHi Peter,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Please try to perform the following SQL statements before you run the
statements for adding the stored procedure.
SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
After adding the stored procedure, please perform the following SQL
statements for safe reason.
SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE
allow updates Option
Use the allow updates option to specify whether direct updates can be made
to system tables. By default, allow updates is disabled (set to 0), so
users cannot update system tables through ad hoc updates. Users can update
system tables using system stored procedures only. When allow updates is
disabled, updates are not allowed, even if you have the appropriate
permissions (assigned using the GRANT statement).
When allow updates is enabled (set to 1), any user who has appropriate
permissions can update system tables directly with ad hoc updates and can
create stored procedures that update system tables.
For more information regarding SP_CONFIGURE, please refer to the following
article on SQL Server Books Online.
Topic: "SP_CONFIGURE"
Topic: "allow updates Option"
Thanks for choosing Microsoft.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Peter,
Thank you for choosing Microsoft! Michael is on holiday and I'm his backup.
My name is
Billy and it's my pleasure to further assist you with this issue.
I believe Machael has pointed out the root cause of your issue and his solut
ion is accurate
and workable on your side. For your benefits, here I'd like to follow up wit
h something
important you should pay more attention to, as the system catalogs are very
critical to the
operation of SQL Server.
Please keep in mind that updating fields in system tables can prevent an ins
tance of SQL
Server from running or can cause data loss. If you create stored procedures
while the allow
updates option is enabled, those stored procedures always have the ability t
o update
system tables even after you disable allow updates. On production systems, y
ou should not
enable allow updates except under the direction of Microsoft Product Support
Services.
It is stongly recommend that you enable allow updates only in tightly contro
lled situations.
Prevent other users from accessing SQL Server while you are directly updatin
g system
tables by restarting an instance of SQL Server from the command prompt with
sqlservr -m.
This command starts an instance of SQL Server in single-user mode and enable
s allow
updates.
After successfully updating the system catalogs, please remember changing th
e allow
updates back to 0 AT ONCE, and then restart the instance services.
For more information on how to operate it in minimal configuration mode, ple
ase see the
following topic in Books Onlinie:
"Starting SQL Server with Minimal Configuration"
Thanks for choosing Microsoft.
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Ad Hoc Distributed Queries Error

I am getting the following error when with SQL Express.

SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

While I was very please to see such a verbose error and directions on where to find the answer I have yet to figure out how to turn this option on...

I tried sp_configure'Ad Hoc Distributed Queries', 1 but got the following error

The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.

If I execute only sp_configure it does not list Ad Hoc Distributied Queries as an option. I checked the sql Books Online and it tells me to use the Surface configuration tool which SQL Express does not seem to have...

Could someone help me out with this?

Thanks - Mark

As 'Ad Hoc Distributed Queries' is an advanced option, you need to turn on advanced options. Try the statements below:

sp_configure 'show advanced options',1
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries',1
reconfigure with override
go

|||

FANTASTIC! Quick and easy!

That works - Thanks

ActualRebinds & ActualRewinds

Hi,
I have the following query:
use northwind
go
select c.companyname,o.orderdate, od.discount, p.productname from customers
c join orders o
on c.customerid=o.customerid
join [order details] od on o.orderid=od.orderid
join products p on od.productid=p.productid
where c.customerid like 'a%' and o.shipcountry='germany'
order by c.city
I get a sort operator in graphical execution plan that has:
ActualRebinds=1 and
ActualRewinds=0
I read about these two in BOL (Physical Operators) but I couldn't understand
that about my query. What does it show?
Thanks in advance,
Leila
On May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
> Hi,
> I have the following query:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't understand
> that about my query. What does it show?
> Thanks in advance,
> Leila
This link might be helpful.
http://msdn2.microsoft.com/en-us/library/ms191158.aspx
Regards,
Enrique Martinez
Sr. Software Consultant
|||Thanks Enrique,
This is exactly what I read in BOL. I cannot understand this:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178476038.970481.125060@.u30g2000hsc.googlegr oups.com...
> On May 6, 1:09 pm, "Leila" <Lei...@.hotpop.com> wrote:
>
> This link might be helpful.
> http://msdn2.microsoft.com/en-us/library/ms191158.aspx
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
|||Leila (Leilas@.hotpop.com) writes:
> use northwind
> go
> select c.companyname,o.orderdate, od.discount, p.productname from
> customers
> c join orders o
> on c.customerid=o.customerid
> join [order details] od on o.orderid=od.orderid
> join products p on od.productid=p.productid
> where c.customerid like 'a%' and o.shipcountry='germany'
> order by c.city
> I get a sort operator in graphical execution plan that has:
> ActualRebinds=1 and
> ActualRewinds=0
> I read about these two in BOL (Physical Operators) but I couldn't
> understand that about my query. What does it show?
Not much, it seems. Books Online says:
Unless an operator is on the inner side of a loop join, ActualRebinds
equals one and ActualRewinds equals zero.
In your case, you got these two for a Sort operator, so the result is to
be expected.
Interesting enough, I did not get any Sort operator when I ran your
query in my Northwind database on SQL 2005 SP2...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Hi Erland,

> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
I guess you have index on customers(city,companyname) that optimizer has
choosen that!

> In your case, you got these two for a Sort operator, so the result is to
> be expected.
That's ok! But I'd like to know the meaning of these two items. For example
I cannot understand this from BOL:
A rebind means that one or more of the correlated parameters of the join
changed and the inner side must be reevaluated. A rewind means that none of
the correlated parameters changed and the prior inner result set may be
reused
How does the "correlated parameters of the join" can change during the
execution?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9928EDA6A1317Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> Not much, it seems. Books Online says:
> Unless an operator is on the inner side of a loop join, ActualRebinds
> equals one and ActualRewinds equals zero.
> In your case, you got these two for a Sort operator, so the result is to
> be expected.
> Interesting enough, I did not get any Sort operator when I ran your
> query in my Northwind database on SQL 2005 SP2...
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Leila (Leilas@.hotpop.com) writes:
> That's ok! But I'd like to know the meaning of these two items. For
> example I cannot understand this from BOL: A rebind means that one or
> more of the correlated parameters of the join changed and the inner side
> must be reevaluated. A rewind means that none of the correlated
> parameters changed and the prior inner result set may be reused
> How does the "correlated parameters of the join" can change during the
> execution?
I will have to admit that I'm quite much in the dark myself. It would
help to have a query where Actual Rebinds/Rewinds are non-zero (save for
sorting operations then). I've been trying to find such a query, but
since I don't know what I'm looking for, I have not been successful.
(But I did not spend the entire week looking. The week was busy, and when
I first tried, SQL Server did not want to cooperate at all. A corrupt
database, cause SQL Server to get a stalled scheduler already on
startup, and did not have the time to investigate that for a few
days.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
Exactly my problem!
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns992FE759593D0Yazorman@.127.0.0.1...
> Leila (Leilas@.hotpop.com) writes:
> I will have to admit that I'm quite much in the dark myself. It would
> help to have a query where Actual Rebinds/Rewinds are non-zero (save for
> sorting operations then). I've been trying to find such a query, but
> since I don't know what I'm looking for, I have not been successful.
> (But I did not spend the entire week looking. The week was busy, and when
> I first tried, SQL Server did not want to cooperate at all. A corrupt
> database, cause SQL Server to get a stalled scheduler already on
> startup, and did not have the time to investigate that for a few
> days.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx