Get @@IDENTITY of new inserted record,

I am using VS.net (2003) SQLcommand TEXT. with input params.
SQL server 2000

Can anyone tell me how to get the Identity value (field value, Idenity col) when you do an insert? I want to get the Identity value, then redirect the user to another page and use this identity value so they can update more (other) fields that are on that page.

My code so far that works... but Where do I put @.@.IDENTITY ?
How do I call or assign the @.@.IDENTITY value to a value in my aspx.vb code page?

Question: how do I get the Identity value from the ID column.
Question: How do I assign that value to some variable in code, say, assign it to (Session("App_ID")) = IdentityValueOrSomething?Help...
-------
INSERT INTO App
(AppName, Acronym, Description,bla bla bla bla......)
VALUES (@.AppName, @.Acronym, @.Description, bla bla bla bla......)

----------

Private Sub btnAddApp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddApp.Click
With cmdAddApp
'.Parameters("@.app_id").Value = Session("App_ID")
.Parameters("@.AppName").Value = txtAppName.Text
.Parameters( bla bla bla..........
.Parameters( bla bla bla..........
.Parameters( bla bla bla..........

End With
Try
cnAppKBdata.Open()
cmdAddApp.ExecuteNonQuery()
cnAppKBdata.Close()
''Session("App_ID") = whatever the @.@.IDENTITY is...'''??
Response.Redirect("AppUpdate.asp")
Catch ex As Exception

End Try
End Sub

Anyone have the lines of code that does this?

Any advise or examples :) thanks you.Well you have a few options here. to name a couple, place it in an output parameter or a return value. I think the better choice is an output param as return is typically reserved for returning custom errors and messages.

.NET Data Access Architecture Guide

Also have a look at scope_identity vs @.@.identity

Generic Stored Procedure

I have 24 lookup tables that contain the same columns (e.g. Rec_Id (identity field), Code, Desc) and I need to build an interface for each of these tables with Create, Update, and Delete capabilities.

I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.

For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @.tablename, @.code, @.desc, and then create a new record in the table specified by @.tablename.

I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.

Any help would be greatly appreciated!::I would like to create three stored procedures (create, update, delete) that would perform
::the task for any of the lookup tables.

Forget it. Not a feasible way.|||Thanks for your input!

I'm currently writing separate stored procs for each of my lookup tables and will continue to do so until somebody shows me a better way.|||Skip the SP's, go with dynamic SQL and use an intelligent DAL to never maintain the SQL anymore.|||I've written generic stored procedures in the past. The trick is to use the EVAL function of SQL. As an example, here is a stored procedure that would take a table name as parameter and return all records from this table.

The stored procedure is defined as follow:
CREATE PROCEDURE SP_Query
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

GO

You must call it as follow:
SP_Query 'Customers'

As your tables have the same columns it should be easy to built your 3 generic stored procedures in the same way as my example.

Good luck,
Olivier Giulieri
www.kakoo.net|||You can use a mixture of dynamic as static.
Have a utility the writes stored proces and either map them or construct their name.

So...
Template:
Update<table>
Gives:
UpdateMyTable1
UpdateMyTable2
...etc

Then the dynamic part is constructing the name of the stored proc...
CommandText = "Update" + strTableName

PS Don't prefix stored procs with "SP_" it's bad news.|||Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').|||I was just making a quick example of stored procedure to illustrate the use of "EVAL". For sure, I picked a very bad name. Here is my example again:

CREATE PROCEDURE GenericQuery
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

GO

PS: Am I accused of not reading documentations because you said "Forget it. Not a feasible way." and my solution may work? Please let's just try to be constructive here.|||*If* you do need to use an "EVAL" function then consider using sp_executeSQL instead. It's geared up for param' queries.|||<Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').
Easy now big fella! ;)

Check every website that has examples, I will bet 99% use sp_ . For the record I do not, but if you take samples from the net, or even Microsoft you will see they almost always use sp_ so you cannot get too uptight at people for following the convention, even if it does reduce processing efficiency.|||There is no 'good' way to do this.

If you use a new proc for each it's a lot of writing and a lot of changing if it changes. If you use dynamic stored procs you lose the security of refusing anyone direct access to your tables (dynamic stored procs require the use to have table access) and they run much more slowly.

My work around when this happens,

Write one proc per table.
Write one proc which the application calls, it then calls the correct proc. That way your application programmers only need to remember one stored procedure to call and you do the rest.

Avoid using IF statements in an SQL proc.

If you have something like this:

IF X
 Begin
 {code code code}
 End
Else
 Begin
 {code code code}
 End

When you execute only half of the proc will be in the plan and run efficiently. The other half will be adhoc.

Consider this:

If X
 Begin
 exec prCode1
 End
Else
 Begin
 exec prCode2
 End

Since each If calls a procedure that has an execution plan it runs more efficiently (AKA faster).

</code>

Generating XML Schema from db tables - including field lengths

I'm working on a C# project that essentially features a dump of the
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegroups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegroups.com...
>
>
>
>|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>

Generating XML Schema from db tables - including field lengths

I'm working on a C# project that essentially features a dump of the
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?
Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>
|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
>
>
|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>

Generating Percent problem with divide by zero

Im trying to generate a percentage value on a agregated field
but in some groups there isn't any information so it tries to divide by zero
and i get an error
So i thought id use a IIf to se if either the Value or the Total was zero
and write out 0 % but the IIf seems to be running the calculation anyway..
heres the code that i'm using as an expression for the percentage field
=IIf(Sum(Fields!MyValue.Value) = 0 Or Sum(Fields!MyTotal.Value) = 0,
0,
SUM(Fields!MyValue.Value) / SUM(Fields!MyTotal.Value)
)
Is this a bug, or is their another way to se that reporting services doesn't
divide by zero?
// LeonHi Leon,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to know how to aviod
"divide by zero" error by using IIF. If I have misunderstood your concern,
please feel free to point it out.
Based on my knowledge, IIF is a function call and will therefore evaluate
all arguments so it will result in a Division by Zero exception. You may
modify your expression below
=IIf(Fields!MyValue.Value = 0, 0, Fields!MyValue) /
IIf(Fields!MyTotal.Value = 0, 1, Fields!MyTotal.Value)
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong'
--
Ranbdy KrofickI don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:

>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter
"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong'

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong'
--
Ranbdy KrofickI don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:
>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong'

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong?
Ranbdy Krofick
I don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:

>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong?

Generate Sequential Numbers - T-SQL

Hi
i have a table with a field called response id. i would like to update this
field with sequential numbers starting from 1000.
can someone please advise me how i go about this.
any help would be appreciated.
thanks heaps in advance
Kind Regards
RidhimaTested on NORTHWIND:
alter table ORDERS
add RESPONSEID int
GO
update ORDERS
set RESPONSEID = RowNumber + 999
from ORDERS inner join
(select ROW_NUMBER() OVER (order by OrderID) as RowNumber, ORDERID from
ORDERS) as TMP on
TMP.ORDERID = ORDERS.ORDERID
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Ridhima" <Ridhima@.discussions.microsoft.com> wrote in message
news:65055160-3C5A-4278-B453-C4DA6344731D@.microsoft.com...
> Hi
> i have a table with a field called response id. i would like to update
> this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima|||On Nov 15, 9:14 pm, Ridhima <Ridh...@.discussions.microsoft.com> wrote:
> Hi
> i have a table with a field called response id. i would like to update this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima
Another way to control this moving forward is to select Design for the
table in SSMS and select the ResponseID field. In the Column
Properties section, select Identity Specification >> (Is Identity) and
set it to Yes. Set Identity Increment to 1 and then set Identity Seed
to 1000 (which is the starting integer value of the ResponseID field).
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||That was my first thought too. But when I tested it, it created "random"
values in the ResponseID field.
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:29bb8aeb-5839-42b5-8e27-57978af3f160@.v4g2000hsf.googlegroups.com...
> On Nov 15, 9:14 pm, Ridhima <Ridh...@.discussions.microsoft.com> wrote:
>> Hi
>> i have a table with a field called response id. i would like to update
>> this
>> field with sequential numbers starting from 1000.
>> can someone please advise me how i go about this.
>> any help would be appreciated.
>> thanks heaps in advance
>> Kind Regards
>> Ridhima
>
> Another way to control this moving forward is to select Design for the
> table in SSMS and select the ResponseID field. In the Column
> Properties section, select Identity Specification >> (Is Identity) and
> set it to Yes. Set Identity Increment to 1 and then set Identity Seed
> to 1000 (which is the starting integer value of the ResponseID field).
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant|||declare @.secuence int
set @.secuence = 999
update table
set @.secuence = response_id = @.secuence + 1
PS.
It does work ! Have faith !
"Ridhima" <Ridhima@.discussions.microsoft.com> escribió en el mensaje
news:65055160-3C5A-4278-B453-C4DA6344731D@.microsoft.com...
> Hi
> i have a table with a field called response id. i would like to update
> this
> field with sequential numbers starting from 1000.
> can someone please advise me how i go about this.
> any help would be appreciated.
> thanks heaps in advance
> Kind Regards
> Ridhima

generate Identity field on existing table

Hi, I have the lovely task of overhauling some of our SQL-based systems. I've found many tables that don't have unique identifying numbers that really should have them. I've searched around and people keep mentioning the Identity field as being similar to Autonumber in Access. The only examples I could find involved setting up a new table... but I need to add (and populate) an identity column to an existing database table. Does anyone know the command for this?

Example... my table is called PACountyTown. It currently has 3 columns: County, Town, and Area. I wish to call the identity-ish field RecordID.

Thanks in advance!do you mean programmatically ? you can always add it in the design view...

hth|||If you add a new column and set its type to INT and set Identity to true, it will auto populate based on the Identity Seed and the Identity Increment.|||Thanks for the replies. I figured it out right after my first posting. DOH! :)

Generate documentation of tables and columns?

Is this possible? the tables has a field called description and so does the columns.

I need to generate a data dictionary.

yes, you can either pull back the description via fn_listextendedproperty (or via SMO). Alternatively if you're just looking to document the schema, then perhaps you can use the free codesmith version and this template

Cathal|||Yes, if you enter a value in the the Description column (using Enterprise Manager), then the value becomes a property of the table or column. These descriptions are called extended properties and almost all objects in SQL Server (including the database itself) can have one or more descriptions.

In SQL Server Books 2000 Books Online, see the topic "Using Extended Properties on Database Objects" for more information. There are also 3rd party tools that can be used to document database objects as well.

Regards,|||You can also try using SqlSpec - it will generate data dictionaries against any sql 2000 or 2005 database. www.elsasoft.org for more info.|||

Here i can execute document on my local sever but when i tried to execute network servers from my work station It is throwing below error

8/28/2006 2:30:17 PM

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy

everthing you see here and send it to sqlspec@.elsasoft.org. If

you can, also attach a SQL script to generate the database that

reproduces this bug, it will help us reproduce the issue. Thanks!

SqlSpec v2.0.19

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at n.b(String A_0)

at n.f()

at p.d()

when i connected network server and running this command local to that server that time it is asking me to install htmlhelpworking file and I installed still it is asking same .how can i do this what things i need to keep in mind to successful execution.

|||

I think your connection string is wrong:

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

should be:

Executing query to: server = AHADMD01; database = AHAWEEKLY ; Trusted_Connection = yes ;

that is, get rid of the parens.

|||

It is working great and thankyou ,

again i have annother quastion how can i change its path because it is default creating document in documents and settings how could i change it .

|||I think if you run it from the cmd line, you can specify where the output goes. try SqlSpec /? to see all the switches. I think the /o switch allows you to specify the output directory.|||nice it is working fine|||Hi,

I just tried using ElsaSoft SqlSpec. It worked on one local database but won't work for another one.
I hope you can help me out. Here's the error I got:

9/7/2006 2:50:48 PM
Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy
everthing you see here and send it to sqlspec@.elsasoft.org. If
you can, also attach a SQL script to generate the database that
reproduces this bug, it will help us reproduce the issue. Thanks!
SqlSpec v2.0.20
9/7/2006 2:50:48 PM

Cannot resolve collation conflict for concatenation operation.

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteXmlReader()
at n.b(String A_0)
at n.f()
at o.d()|||I think this is fixed in the latest version on the elsasoft.org site, although the build number is the same at 2.0.20. Can you download the version on the site and see if this still repros?|||

I think your connection string is wrong:

Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

should be:

Executing query to: server = networkservername ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

that is, get rid of the parens.

when you are calling net work srever from local workstation should use string without paren

i hope it woks

|||Hi,

I was actually trying this on a database located on my PC that's why I kept the (local). I also tried connecting to a database on a server and changed the (local) to the server name but got the same error as well. Our IT Ops guy fixed this just now by setting an login/password to the database and executing the line from the command prompt, adding the parameters for login and password. Not sure if this will work for others.

Thanks!|||May i know the string which you are calling from command line|||You might want to consider SchemaToDoc (http://www.schematodoc.com). It creates Word or HTML output that documents primary keys, fields (datatype, size, nullable, defaults), indexes, check constraints, foreign keys, triggers, views, and stored procedures. It also has an interface that lets you annotate your tables and fields; you can include these extended property descriptions in your output documents.

Generate documentation of tables and columns?

Is this possible? the tables has a field called description and so does the columns.

I need to generate a data dictionary.yes, you can either pull back the description via fn_listextendedproperty (or via SMO). Alternatively if you're just looking to document the schema, then perhaps you can use the free codesmith version and this template

Cathal|||Yes, if you enter a value in the the Description column (using Enterprise Manager), then the value becomes a property of the table or column. These descriptions are called extended properties and almost all objects in SQL Server (including the database itself) can have one or more descriptions.

In SQL Server Books 2000 Books Online, see the topic "Using Extended Properties on Database Objects" for more information. There are also 3rd party tools that can be used to document database objects as well.

Regards,|||You can also try using SqlSpec - it will generate data dictionaries against any sql 2000 or 2005 database. www.elsasoft.org for more info.|||

Here i can execute document on my local sever but when i tried to execute network servers from my work station It is throwing below error

8/28/2006 2:30:17 PM

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy

everthing you see here and send it to sqlspec@.elsasoft.org. If

you can, also attach a SQL script to generate the database that

reproduces this bug, it will help us reproduce the issue. Thanks!

SqlSpec v2.0.19

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at n.b(String A_0)

at n.f()

at p.d()

when i connected network server and running this command local to that server that time it is asking me to install htmlhelpworking file and I installed still it is asking same .how can i do this what things i need to keep in mind to successful execution.

|||

I think your connection string is wrong:

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

should be:

Executing query to: server = AHADMD01; database = AHAWEEKLY ; Trusted_Connection = yes ;

that is, get rid of the parens.

|||

It is working great and thankyou ,

again i have annother quastion how can i change its path because it is default creating document in documents and settings how could i change it .

|||I think if you run it from the cmd line, you can specify where the output goes. try SqlSpec /? to see all the switches. I think the /o switch allows you to specify the output directory.|||nice it is working fine|||Hi,

I just tried using ElsaSoft SqlSpec. It worked on one local database but won't work for another one.
I hope you can help me out. Here's the error I got:

9/7/2006 2:50:48 PM
Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy
everthing you see here and send it to sqlspec@.elsasoft.org. If
you can, also attach a SQL script to generate the database that
reproduces this bug, it will help us reproduce the issue. Thanks!
SqlSpec v2.0.20
9/7/2006 2:50:48 PM

Cannot resolve collation conflict for concatenation operation.

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteXmlReader()
at n.b(String A_0)
at n.f()
at o.d()

|||I think this is fixed in the latest version on the elsasoft.org site, although the build number is the same at 2.0.20. Can you download the version on the site and see if this still repros?

|||

I think your connection string is wrong:

Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

should be:

Executing query to: server = networkservername ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

that is, get rid of the parens.

when you are calling net work srever from local workstation should use string without paren

i hope it woks

|||Hi,

I was actually trying this on a database located on my PC that's why I kept the (local). I also tried connecting to a database on a server and changed the (local) to the server name but got the same error as well. Our IT Ops guy fixed this just now by setting an login/password to the database and executing the line from the command prompt, adding the parameters for login and password. Not sure if this will work for others.

Thanks!
|||May i know the string which you are calling from command line|||You might want to consider SchemaToDoc (http://www.schematodoc.com). It creates Word or HTML output that documents primary keys, fields (datatype, size, nullable, defaults), indexes, check constraints, foreign keys, triggers, views, and stored procedures. It also has an interface that lets you annotate your tables and fields; you can include these extended property descriptions in your output documents.

Generate documentation of tables and columns?

Is this possible? the tables has a field called description and so does the columns.

I need to generate a data dictionary.

yes, you can either pull back the description via fn_listextendedproperty (or via SMO). Alternatively if you're just looking to document the schema, then perhaps you can use the free codesmith version and this template

Cathal|||Yes, if you enter a value in the the Description column (using Enterprise Manager), then the value becomes a property of the table or column. These descriptions are called extended properties and almost all objects in SQL Server (including the database itself) can have one or more descriptions.

In SQL Server Books 2000 Books Online, see the topic "Using Extended Properties on Database Objects" for more information. There are also 3rd party tools that can be used to document database objects as well.

Regards,|||You can also try using SqlSpec - it will generate data dictionaries against any sql 2000 or 2005 database. www.elsasoft.org for more info.|||

Here i can execute document on my local sever but when i tried to execute network servers from my work station It is throwing below error

8/28/2006 2:30:17 PM

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy

everthing you see here and send it to sqlspec@.elsasoft.org. If

you can, also attach a SQL script to generate the database that

reproduces this bug, it will help us reproduce the issue. Thanks!

SqlSpec v2.0.19

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at n.b(String A_0)

at n.f()

at p.d()

when i connected network server and running this command local to that server that time it is asking me to install htmlhelpworking file and I installed still it is asking same .how can i do this what things i need to keep in mind to successful execution.

|||

I think your connection string is wrong:

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

should be:

Executing query to: server = AHADMD01; database = AHAWEEKLY ; Trusted_Connection = yes ;

that is, get rid of the parens.

|||

It is working great and thankyou ,

again i have annother quastion how can i change its path because it is default creating document in documents and settings how could i change it .

|||I think if you run it from the cmd line, you can specify where the output goes. try SqlSpec /? to see all the switches. I think the /o switch allows you to specify the output directory.|||nice it is working fine|||Hi,

I just tried using ElsaSoft SqlSpec. It worked on one local database but won't work for another one.
I hope you can help me out. Here's the error I got:

9/7/2006 2:50:48 PM
Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy
everthing you see here and send it to sqlspec@.elsasoft.org. If
you can, also attach a SQL script to generate the database that
reproduces this bug, it will help us reproduce the issue. Thanks!
SqlSpec v2.0.20
9/7/2006 2:50:48 PM

Cannot resolve collation conflict for concatenation operation.

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteXmlReader()
at n.b(String A_0)
at n.f()
at o.d()|||I think this is fixed in the latest version on the elsasoft.org site, although the build number is the same at 2.0.20. Can you download the version on the site and see if this still repros?|||

I think your connection string is wrong:

Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

should be:

Executing query to: server = networkservername ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

that is, get rid of the parens.

when you are calling net work srever from local workstation should use string without paren

i hope it woks

|||Hi,

I was actually trying this on a database located on my PC that's why I kept the (local). I also tried connecting to a database on a server and changed the (local) to the server name but got the same error as well. Our IT Ops guy fixed this just now by setting an login/password to the database and executing the line from the command prompt, adding the parameters for login and password. Not sure if this will work for others.

Thanks!|||May i know the string which you are calling from command line|||You might want to consider SchemaToDoc (http://www.schematodoc.com). It creates Word or HTML output that documents primary keys, fields (datatype, size, nullable, defaults), indexes, check constraints, foreign keys, triggers, views, and stored procedures. It also has an interface that lets you annotate your tables and fields; you can include these extended property descriptions in your output documents.

Generate documentation of tables and columns?

Is this possible? the tables has a field called description and so does the columns.

I need to generate a data dictionary.

yes, you can either pull back the description via fn_listextendedproperty (or via SMO). Alternatively if you're just looking to document the schema, then perhaps you can use the free codesmith version and this template

Cathal|||Yes, if you enter a value in the the Description column (using Enterprise Manager), then the value becomes a property of the table or column. These descriptions are called extended properties and almost all objects in SQL Server (including the database itself) can have one or more descriptions.

In SQL Server Books 2000 Books Online, see the topic "Using Extended Properties on Database Objects" for more information. There are also 3rd party tools that can be used to document database objects as well.

Regards,|||You can also try using SqlSpec - it will generate data dictionaries against any sql 2000 or 2005 database. www.elsasoft.org for more info.|||

Here i can execute document on my local sever but when i tried to execute network servers from my work station It is throwing below error

8/28/2006 2:30:17 PM

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy

everthing you see here and send it to sqlspec@.elsasoft.org. If

you can, also attach a SQL script to generate the database that

reproduces this bug, it will help us reproduce the issue. Thanks!

SqlSpec v2.0.19

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at n.b(String A_0)

at n.f()

at p.d()

when i connected network server and running this command local to that server that time it is asking me to install htmlhelpworking file and I installed still it is asking same .how can i do this what things i need to keep in mind to successful execution.

|||

I think your connection string is wrong:

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

should be:

Executing query to: server = AHADMD01; database = AHAWEEKLY ; Trusted_Connection = yes ;

that is, get rid of the parens.

|||

It is working great and thankyou ,

again i have annother quastion how can i change its path because it is default creating document in documents and settings how could i change it .

|||I think if you run it from the cmd line, you can specify where the output goes. try SqlSpec /? to see all the switches. I think the /o switch allows you to specify the output directory.|||nice it is working fine|||Hi,

I just tried using ElsaSoft SqlSpec. It worked on one local database but won't work for another one.
I hope you can help me out. Here's the error I got:

9/7/2006 2:50:48 PM
Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy
everthing you see here and send it to sqlspec@.elsasoft.org. If
you can, also attach a SQL script to generate the database that
reproduces this bug, it will help us reproduce the issue. Thanks!
SqlSpec v2.0.20
9/7/2006 2:50:48 PM

Cannot resolve collation conflict for concatenation operation.

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteXmlReader()
at n.b(String A_0)
at n.f()
at o.d()|||I think this is fixed in the latest version on the elsasoft.org site, although the build number is the same at 2.0.20. Can you download the version on the site and see if this still repros?|||

I think your connection string is wrong:

Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

should be:

Executing query to: server = networkservername ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

that is, get rid of the parens.

when you are calling net work srever from local workstation should use string without paren

i hope it woks

|||Hi,

I was actually trying this on a database located on my PC that's why I kept the (local). I also tried connecting to a database on a server and changed the (local) to the server name but got the same error as well. Our IT Ops guy fixed this just now by setting an login/password to the database and executing the line from the command prompt, adding the parameters for login and password. Not sure if this will work for others.

Thanks!|||May i know the string which you are calling from command line|||You might want to consider SchemaToDoc (http://www.schematodoc.com). It creates Word or HTML output that documents primary keys, fields (datatype, size, nullable, defaults), indexes, check constraints, foreign keys, triggers, views, and stored procedures. It also has an interface that lets you annotate your tables and fields; you can include these extended property descriptions in your output documents.

Generate documentation of tables and columns?

Is this possible? the tables has a field called description and so does the columns.

I need to generate a data dictionary.yes, you can either pull back the description via fn_listextendedproperty (or via SMO). Alternatively if you're just looking to document the schema, then perhaps you can use the free codesmith version and this template

Cathal|||Yes, if you enter a value in the the Description column (using Enterprise Manager), then the value becomes a property of the table or column. These descriptions are called extended properties and almost all objects in SQL Server (including the database itself) can have one or more descriptions.

In SQL Server Books 2000 Books Online, see the topic "Using Extended Properties on Database Objects" for more information. There are also 3rd party tools that can be used to document database objects as well.

Regards,|||You can also try using SqlSpec - it will generate data dictionaries against any sql 2000 or 2005 database. www.elsasoft.org for more info.|||

Here i can execute document on my local sever but when i tried to execute network servers from my work station It is throwing below error

8/28/2006 2:30:17 PM

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy

everthing you see here and send it to sqlspec@.elsasoft.org. If

you can, also attach a SQL script to generate the database that

reproduces this bug, it will help us reproduce the issue. Thanks!

SqlSpec v2.0.19

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at n.b(String A_0)

at n.f()

at p.d()

when i connected network server and running this command local to that server that time it is asking me to install htmlhelpworking file and I installed still it is asking same .how can i do this what things i need to keep in mind to successful execution.

|||

I think your connection string is wrong:

Executing query to: server = (AHADMD01) ; database = AHAWEEKLY ; Trusted_Connection = yes ;

should be:

Executing query to: server = AHADMD01; database = AHAWEEKLY ; Trusted_Connection = yes ;

that is, get rid of the parens.

|||

It is working great and thankyou ,

again i have annother quastion how can i change its path because it is default creating document in documents and settings how could i change it .

|||I think if you run it from the cmd line, you can specify where the output goes. try SqlSpec /? to see all the switches. I think the /o switch allows you to specify the output directory.|||nice it is working fine|||Hi,

I just tried using ElsaSoft SqlSpec. It worked on one local database but won't work for another one.
I hope you can help me out. Here's the error I got:

9/7/2006 2:50:48 PM
Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

Error occured. If you think this error is caused by a bug, copy
everthing you see here and send it to sqlspec@.elsasoft.org. If
you can, also attach a SQL script to generate the database that
reproduces this bug, it will help us reproduce the issue. Thanks!
SqlSpec v2.0.20
9/7/2006 2:50:48 PM

Cannot resolve collation conflict for concatenation operation.

System.Data.SqlClient.SqlException

.Net SqlClient Data Provider

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteXmlReader()
at n.b(String A_0)
at n.f()
at o.d()

|||I think this is fixed in the latest version on the elsasoft.org site, although the build number is the same at 2.0.20. Can you download the version on the site and see if this still repros?

|||

I think your connection string is wrong:

Executing query to: server = (local) ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

should be:

Executing query to: server = networkservername ; database = ImbFiordlandProdApp ; Trusted_Connection = yes ;

that is, get rid of the parens.

when you are calling net work srever from local workstation should use string without paren

i hope it woks

|||Hi,

I was actually trying this on a database located on my PC that's why I kept the (local). I also tried connecting to a database on a server and changed the (local) to the server name but got the same error as well. Our IT Ops guy fixed this just now by setting an login/password to the database and executing the line from the command prompt, adding the parameters for login and password. Not sure if this will work for others.

Thanks!
|||May i know the string which you are calling from command line|||You might want to consider SchemaToDoc (http://www.schematodoc.com). It creates Word or HTML output that documents primary keys, fields (datatype, size, nullable, defaults), indexes, check constraints, foreign keys, triggers, views, and stored procedures. It also has an interface that lets you annotate your tables and fields; you can include these extended property descriptions in your output documents.

generate a text file from SQL Server

Hi

I need to generate a text file from SQL Server. The task automatically runs daily.

The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char and uses "\". The number of char includes the data parts and excludes a few word of "the number of char".

For example,
The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.

1/060222/008888/234/1/7441/2/BB/10000//////290025/////1/060222/008888/234/1/7441/3/XX/100-//////290025/////1/060222/008881/234/1/7442/2/BB/10000//////290025/////161\

I am no idea about this problem. Please give me some suggestions. Thanks a lot

regards
Alex

For your information

I use SQL Server 2000 in Windows 2000 Server environments

Hi Alex,

You need to generate a Text File from SQL Server with field delimeter as "/" and the line delimeter as "/////"?

Also please let us know that whether you want to generate a Text file with static filename or it may vary?

Anyway I will also try to generate the file based on my understandings from your post.

Regards,

Prakash Srinivasan

|||

Hi Prakash Srinivasan

The field delimeter is "/" and there is no line delimeter.

The task generates many files and place at different places where are the fixed folders. The filenames are static and need to override the old one.

Thank you for your help.

Alex

|||

Hi Alex,

If you don't have any line delimeter (Row delimeter), then how would you differentiate the records. So there should be a row delimeter in the file to separate the records.

I assume there is and proceed further.

Regards,

Prakash Srinivasan

|||

Hi Alex,

First create a Package and put a Data Flow. In the Data Flow, put one OleDb Source and Flat File Destination. Also create Connection for both OleDb Source and Flat File Destination.

And in the Flat File Connection Manager, specify the filename in which you want to dump the data, and leave the rest of the setting in General tab as it is.

In Advanced tab, create columns based on your requirement (Suppose if you need only 3 columns, create 3 columns and set the datatype based on the datatype specified in your SQL Server database).

In Columns tab, give the Row Delimeter as "////" and Column delimeter as "/".

Now connect your OleDb Source to the OleDb Connection manager and mention the tables which you want to access.

Now map the columns appropriately in the Flat File Destination and execute the package.

The results are coming exactly in my machine. And for writing the number of characters at the end of the file, put one Script Task and write a code to open the text file and read till the end of the file and write at the end of the file and add "\".

I hope there is a way to get this done in SSIS. If I get that, will let you know.

Regards,

Prakash Srinivasan.

|||

One record followed by one record. The number of field are fixed in each row, so there are not the row delimeter.

for example,
record1field1/record1field2/record2field1/record2field2/55\

--Alex

|||Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!|||

Miles Calunod wrote:

Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!

Miles,

Put an expression on the connection string property of the connection manager that you are using to point at the destination file. This expression will get evaluated at runtime and therefore can set the connection string (i.e. the file location) to be whatever you like.

-Jamie

generate a text file from SQL Server

Hi

I need to generate a text file from SQL Server. The task automatically runs daily.

The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char and uses "\". The number of char includes the data parts and excludes a few word of "the number of char".

For example,
The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.

1/060222/008888/234/1/7441/2/BB/10000//////290025/////1/060222/008888/234/1/7441/3/XX/100-//////290025/////1/060222/008881/234/1/7442/2/BB/10000//////290025/////161\

I am no idea about this problem. Please give me some suggestions. Thanks a lot

regards
Alex

For your information

I use SQL Server 2000 in Windows 2000 Server environments

Hi Alex,

You need to generate a Text File from SQL Server with field delimeter as "/" and the line delimeter as "/////"?

Also please let us know that whether you want to generate a Text file with static filename or it may vary?

Anyway I will also try to generate the file based on my understandings from your post.

Regards,

Prakash Srinivasan

|||

Hi Prakash Srinivasan

The field delimeter is "/" and there is no line delimeter.

The task generates many files and place at different places where are the fixed folders. The filenames are static and need to override the old one.

Thank you for your help.

Alex

|||

Hi Alex,

If you don't have any line delimeter (Row delimeter), then how would you differentiate the records. So there should be a row delimeter in the file to separate the records.

I assume there is and proceed further.

Regards,

Prakash Srinivasan

|||

Hi Alex,

First create a Package and put a Data Flow. In the Data Flow, put one OleDb Source and Flat File Destination. Also create Connection for both OleDb Source and Flat File Destination.

And in the Flat File Connection Manager, specify the filename in which you want to dump the data, and leave the rest of the setting in General tab as it is.

In Advanced tab, create columns based on your requirement (Suppose if you need only 3 columns, create 3 columns and set the datatype based on the datatype specified in your SQL Server database).

In Columns tab, give the Row Delimeter as "////" and Column delimeter as "/".

Now connect your OleDb Source to the OleDb Connection manager and mention the tables which you want to access.

Now map the columns appropriately in the Flat File Destination and execute the package.

The results are coming exactly in my machine. And for writing the number of characters at the end of the file, put one Script Task and write a code to open the text file and read till the end of the file and write at the end of the file and add "\".

I hope there is a way to get this done in SSIS. If I get that, will let you know.

Regards,

Prakash Srinivasan.

|||

One record followed by one record. The number of field are fixed in each row, so there are not the row delimeter.

for example,
record1field1/record1field2/record2field1/record2field2/55\

--Alex

|||Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!|||

Miles Calunod wrote:

Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!

Miles,

Put an expression on the connection string property of the connection manager that you are using to point at the destination file. This expression will get evaluated at runtime and therefore can set the connection string (i.e. the file location) to be whatever you like.

-Jamie