Can I generate a SQL CREATE script for a table that exists in the db automatical

I'm trying to figure out what the easiest/best way to transfer just one table from one sql server to another. Any help would be appreciated. Thanks!you can use the query analyzer, select database --> user tables --> select correct table --> right click --> script object to new window as -->> create. The query analyzer will generate a script that will generate your table.|||Thanks for the quick reply!
However, I can't seem to find the "user tables-->select correct table" option in the query analyzer?
Thanks again!|||At the left side is the object browser. If you cannot see, push the F8 button. You see at the top your server and below the databases in that server. From that point on it's like the Windows Explorer. When you see the user tables you can select one and right click on it for the scripting.|||Is this a SQL 2000 thing? I'm on SQL 7.0|||don't know, never used sql 7. Something else you can try: in the enterprise manager, select the table you want to script. Right click on it, select all tasks and then generate script. A popup will appear and if you want you can set some preferences, but just pushing OK is sufficient. Hopefully this is in sql 7|||You can access this through enterprise manager by right-clicking on the table and selecting All Tasks, Generate SQL Script.

Note that this does not copy the actual data. To do that you will need to use DTS.

blindman

Can i debug/watch on the triggers INSERTED and DELETED records/values?

When i debug a trigger is it possible to add a WATCH
on the INSERTED or DELETED?

I think not, at least I couldn't figure out a way to do so.
Does someone have a suggestion on how I can see the values?

I did try to do something like

INSERT INTO TABLE1(NAME)
SELECT NAME FROM INSERTED

but this didn't work. When the trigger completed and I
went to see the TABLE1, there were no records in it.

Are there any documents, web links that describe ways
of debugging the trigger's INSERTED and DELETED?

Thank youOn Wed, 25 Jan 2006 13:29:59 -0500, serge wrote:

>When i debug a trigger is it possible to add a WATCH
>on the INSERTED or DELETED?

Hi Serge,

No. During debugging, it is (unfortunately) not possible to see the
contents of ANY tables.

>I think not, at least I couldn't figure out a way to do so.
>Does someone have a suggestion on how I can see the values?

You could add a SELECT to the trigger code, then test your code from
Query Analyzer. The values in the inserted and deleted pseudo-table
would go to the Query Analyzer results pane.

Or you could use SELECT INTO or INSERT ... SELECT to store the values in
a persistant table.

>I did try to do something like
>INSERT INTO TABLE1(NAME)
>SELECT NAME FROM INSERTED
>but this didn't work. When the trigger completed and I
>went to see the TABLE1, there were no records in it.

Hey, that's just what I suggested! <g
This should work. Some potential reasons for why it didn't work for you
are:
- Maybe the code never even reached the insert into statement? This
might be the case if the table TABLE1 didn;t exist at all after trigger
execution.
- Did you check that the table TABLE1 did not exist before the trigger
was executed? If it did, the command above would result in an error (and
you should have gotten an error message).
- Did you run the trigger with a zero-row operation? (I.e. an UPDATE or
DELETE, or an INSERT .. SELECT that affected 0 rows)
- Don't use a temp table for this. It will be removed when the trigger
execution finishes, as it only exists in the scope of the trigger.
- In a DELETTE trigger, the inserted table is ALWAYS empty.

All the above are just guesses, of course. I'd have to see the actual
code to help you further.

>Are there any documents, web links that describe ways
>of debugging the trigger's INSERTED and DELETED?
>Thank you

--
Hugo Kornelis, SQL Server MVP|||serge (sergea@.nospam.ehmail.com) writes:
> When i debug a trigger is it possible to add a WATCH
> on the INSERTED or DELETED?
> I think not, at least I couldn't figure out a way to do so.
> Does someone have a suggestion on how I can see the values?
> I did try to do something like
> INSERT INTO TABLE1(NAME)
> SELECT NAME FROM INSERTED
> but this didn't work. When the trigger completed and I
> went to see the TABLE1, there were no records in it.

In additions to Hugo's suggestions, keep in mind that if the trigger
fails, then the statement will be rolled back, and that includs the
data insertedvinto Table1

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 25 Jan 2006 22:27:44 +0000 (UTC), Erland Sommarskog wrote:

>serge (sergea@.nospam.ehmail.com) writes:
>> When i debug a trigger is it possible to add a WATCH
>> on the INSERTED or DELETED?
>>
>> I think not, at least I couldn't figure out a way to do so.
>> Does someone have a suggestion on how I can see the values?
>>
>> I did try to do something like
>>
>> INSERT INTO TABLE1(NAME)
>> SELECT NAME FROM INSERTED
>>
>> but this didn't work. When the trigger completed and I
>> went to see the TABLE1, there were no records in it.
>In additions to Hugo's suggestions, keep in mind that if the trigger
>fails, then the statement will be rolled back, and that includs the
>data insertedvinto Table1

Ah, of course. How could I forget it?

Time for bed, I guess :-)

Thanks, Erland!

--
Hugo Kornelis, SQL Server MVP|||Thanks Hugo, Erland.

Hugo,

>This should work. Some potential reasons for why it didn't work for you
>are:
>- Did you run the trigger with a zero-row operation? (I.e. an UPDATE or
>DELETE, or an INSERT .. SELECT that affected 0 rows)

I believe I was running an UPDATE statement with a zero-row operation.
But let's ignore that statement as now I've tested it again using a new
UPDATE statement that updates rows for sure. After testing the last 30
minutes I now understand things better.

I am able to INSERT the records from the trigger's INSERTED table
to the permanent table I created before running the update statement.

I also realized that when running in DEBUG mode I should make sure
to uncheck the DEBUG PROCEDURE's AUTO ROLL BACK check
box. This problem until I realized it kept me puzzled for 10 minutes.

>You could add a SELECT to the trigger code, then test your code from
>Query Analyzer. The values in the inserted and deleted pseudo-table
>would go to the Query Analyzer results pane.

Too bad ADD WATCH isn't available.
Anyone knows if SQL 2005 allows to add watches and monitor the
contents of the inserted and deleted when debugging triggers?

Thank you|||serge (sergea@.nospam.ehmail.com) writes:
> Anyone knows if SQL 2005 allows to add watches and monitor the
> contents of the inserted and deleted when debugging triggers?

I haven't tried debugging in SQL 2005, as it you only can debug from
Visual Studio. But I would not really expect so.

Personally, I have more or less stopped using the debugger. It usually
works when you want to debug your local server, but when connecting to
another, there is so much red tape. Debug PRINTs and SELECTs are easier
to handle.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Personally, I have more or less stopped using the debugger. It usually
> works when you want to debug your local server, but when connecting to
> another, there is so much red tape.

Sometimes I do debug remotely (maybe often). Are you saying it is not always
a
safe approach to debug remotely? "There is so much red tape": these
are known issues that happen frequently or in the very rare cases?
Would you know if there are MS KB on these problems or personal
experiences led you to stop debugging remotely?

Can you please list some situations where debugging remotely is not safe
or the problems you ran into are complicated to explain?

If this is the case then I should avoid debugging remotely and end up
using Terminal Services to connect to the SQL Server and debug locally?

> Debug PRINTs and SELECTs are easier to handle.

I just tried "SELECT * FROM INSERTED" and I saw the result
in the results pane so I am not sure why I had not tried this before.
Well, at least now I know and I won't need to create a table if I am
only interested in seeing the result during the debugging only.

Thanks Erland.|||On Thu, 26 Jan 2006 01:19:30 -0500, serge wrote:

(snip)
>>You could add a SELECT to the trigger code, then test your code from
>>Query Analyzer. The values in the inserted and deleted pseudo-table
>>would go to the Query Analyzer results pane.
>Too bad ADD WATCH isn't available.
>Anyone knows if SQL 2005 allows to add watches and monitor the
>contents of the inserted and deleted when debugging triggers?

Hi Serge,

I don't know. I haven't seen the debugging capabilities in SQL 2005 yet.

In SQL 2000, the debugger was integral part of the product. But that has
been removed from SQL 2005 - you can now only debug triggers and stored
procedures if you also have Visual Studio installed.

http://lab.msdn.microsoft.com/Produ...06-f50123f6d235

--
Hugo Kornelis, SQL Server MVP|||serge (sergea@.nospam.ehmail.com) writes:
> Sometimes I do debug remotely (maybe often). Are you saying it is not
> always a safe approach to debug remotely? "There is so much red tape":
> these are known issues that happen frequently or in the very rare cases?
> Would you know if there are MS KB on these problems or personal
> experiences led you to stop debugging remotely?

Unsafe? Yes, a little, although that was not really what I meant with
red tape. What I mean is simply that there are so many things have to
be aligned for it to work, that I don't find it worth the hassle.

Some time back, we found that debugging did not work when you had Windows
XP SP2 installed. I did some investigation, and found that hotfix
8.00.944 addressed this problem. (This hotfix is included in SP4.) I
installed hotfix on client and server. I also had to open port 135 in
Windows firewall. Now, port 135 is not any port: this is RPC, and a
prime attack surfaces for viruses. So opening port 135 is a little unsafe,
so there is all reason to only open it for the SQL Servers you want to
debug. (If is possible to open a port only for a certain IP address in
Windows firewall.) Eventually I got it working.

Then some months later, I felt like debugging again, but now I was out
of luck again. I did some inquires, and apparently our Windows admin had
decided to cut the number of permissions for the SQL Server service
account. I don't know exactly what permissions that are required, but
as it writes back to the client, it needs more than plain-user rights.

At this point, I just gave it up. These are not the only thing that
can stop debugging from working. And after all, what you can dig out
from the debugger can easily be achieved in other ways. Of course,
code that uses iterative approaches can be painful to debug that
way. But good SQL should not have much such code anyway. :-)

And, oh, there is one more possible issue with the debugger. Single-
stepping through a transaction is not that friendly if other users
needs to access the data.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Unsafe? Yes, a little, although that was not really what I meant with
> red tape. What I mean is simply that there are so many things have to
> be aligned for it to work, that I don't find it worth the hassle.
> Some time back, we found that debugging did not work when you had Windows
> XP SP2 installed. I did some investigation, and found that hotfix
> 8.00.944 addressed this problem. (This hotfix is included in SP4.) I
> installed hotfix on client and server. I also had to open port 135 in
> Windows firewall. Now, port 135 is not any port: this is RPC, and a
> prime attack surfaces for viruses. So opening port 135 is a little unsafe,
> so there is all reason to only open it for the SQL Servers you want to
> debug. (If is possible to open a port only for a certain IP address in
> Windows firewall.) Eventually I got it working.
> Then some months later, I felt like debugging again, but now I was out
> of luck again. I did some inquires, and apparently our Windows admin had
> decided to cut the number of permissions for the SQL Server service
> account. I don't know exactly what permissions that are required, but
> as it writes back to the client, it needs more than plain-user rights.
> At this point, I just gave it up. These are not the only thing that
> can stop debugging from working. And after all, what you can dig out
> from the debugger can easily be achieved in other ways. Of course,
> code that uses iterative approaches can be painful to debug that
> way. But good SQL should not have much such code anyway. :-)
> And, oh, there is one more possible issue with the debugger. Single-
> stepping through a transaction is not that friendly if other users
> needs to access the data.

Thanks for the detailed explanation. Some interesting information
that I'll keep in mind.|||> In SQL 2000, the debugger was integral part of the product. But that has
> been removed from SQL 2005 - you can now only debug triggers and stored
> procedures if you also have Visual Studio installed.
> http://lab.msdn.microsoft.com/Produ...06-f50123f6d235

Then I presume SQL 2005 Studio Management that comes with SQL 2005
is not a flavor of Visual Studio that can debug triggers.

Thanks Hugo.|||On Thu, 26 Jan 2006 22:57:53 -0500, serge wrote:

>> In SQL 2000, the debugger was integral part of the product. But that has
>> been removed from SQL 2005 - you can now only debug triggers and stored
>> procedures if you also have Visual Studio installed.
>>
>> http://lab.msdn.microsoft.com/Produ...06-f50123f6d235
>Then I presume SQL 2005 Studio Management that comes with SQL 2005
>is not a flavor of Visual Studio that can debug triggers.

Hi Serge,

That's correct. Management Studio is replacement for Enterprise Manager
plus Query Analyzer, but doesn't have the debugger.

--
Hugo Kornelis, SQL Server MVP

Can anyone tell me why this code wont update my SQL Server database?

I am trying to perform a simple update to a SQL Server database table and I can't figure out why this simple UPDATE command doesn't work. I am performing the same thing in another page for owner information and it works just fine. It seems that some of my code won't work even though the syntax is correct and there is no reason for it not to work.

I have hardcoded text for some of the fields and the UPDATE code below works but when I try to use the information that may be in the Textbox, the code won't do the UPDATE. Please help me figure out what is going on.

Thanks

Sub btnUpdate_Click_1(sender As Object, e As EventArgs)
 Dim UserCode as String
 Dim PropertyCode as String
 Dim UnitCode as String
 Dim address1 as String
 Dim address2 as String
 Dim address3 as String
 Dim city as String
 Dim zip_Code as String
 Dim description as String
 Dim price_Range as String
 Dim state as String
 Dim type_Property as String
 Dim property_Status as String
 if chkChangeState.Checked = True then
 state = ddl_State.SelectedItem.Value
 else
 state = lblState.Text
 end if
 if chkPropertyType.Checked = True then
 type_Property = ddlPropertyType.SelectedItem.Value
 else
 type_Property = lblPropertyType.Text
 end if
 if chkPropertyStatus.Checked = True then
 property_Status = ddlPropertyStatus.SelectedItem.Value
 else
 property_Status = lblPropertyStatus.Text
 end if
 txtPropertyCode.ReadOnly = False
 txtUnitCode.ReadOnly = False
 'address1 = txtAddress1.Text
 'address2 = txtAddress2.Text
 'address3 = txtAddress3.Text
 'city = txtCity.Text
 'zip_Code = txtZipCode.Text
 'description = txtDescription.Text
 'price_Range = txtPriceRange.Text
 UserCode = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
 PropertyCode = txtPropertyCode.Text
 UnitCode = txtUnitCode.Text
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
 Dim queryString As String = "UPDATE [Property_db] SET [Address1]=@.Address1, [Address2]=@.Address2, [Address3]=@.Address3, [City]=@.City, [State]=@.State, [Zip_Code]=@.Zip_Code, [Type_Property]=@.Type_Property, [Description]=@.Description, [Property_Status]=@.Property_Status, [Price_Range]=@.Price_Range WHERE ([Property_db].[Code] = @.Code) AND ([Property_db].[Prop_Code] = @.Prop_Code) AND ([Property_db].[Unit_Code] = @.Unit_Code)"
 Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
 dbCommand.CommandText = queryString
 dbCommand.Connection = dbConnection
 Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_code.ParameterName = "@.Code"
 dbParam_code.Value = UserCode
 dbParam_code.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_code)
 Dim dbParam_prop_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_prop_Code.ParameterName = "@.Prop_Code"
 dbParam_prop_Code.Value = PropertyCode
 dbParam_prop_Code.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_prop_Code)
 Dim dbParam_unit_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_unit_Code.ParameterName = "@.Unit_Code"
 dbParam_unit_Code.Value = UnitCode
 dbParam_unit_Code.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_unit_Code)
 Dim dbParam_address1 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_address1.ParameterName = "@.Address1"
 dbParam_address1.Value = txtAddress1.Text
 dbParam_address1.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_address1)
 Dim dbParam_address2 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_address2.ParameterName = "@.Address2"
 dbParam_address2.Value = txtAddress2.Text
 dbParam_address2.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_address2)
 Dim dbParam_address3 As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_address3.ParameterName = "@.Address3"
 dbParam_address3.Value = txtAddress3.Text
 dbParam_address3.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_address3)
 Dim dbParam_city As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_city.ParameterName = "@.City"
 dbParam_city.Value = txtCity.Text
 dbParam_city.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_city)
 Dim dbParam_state As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_state.ParameterName = "@.State"
 dbParam_state.Value = state
 dbParam_state.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_state)
 Dim dbParam_zip_Code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_zip_Code.ParameterName = "@.Zip_Code"
 dbParam_zip_Code.Value = txtZipCode.Text
 dbParam_zip_Code.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_zip_Code)
 Dim dbParam_type_Property As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_type_Property.ParameterName = "@.Type_Property"
 dbParam_type_Property.Value = type_Property
 dbParam_type_Property.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_type_Property)
 Dim dbParam_description As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_description.ParameterName = "@.Description"
 dbParam_description.Value = txtDescription.Text
 dbParam_description.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_description)
 Dim dbParam_property_Status As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_property_Status.ParameterName = "@.Property_Status"
 dbParam_property_Status.Value = property_Status
 dbParam_property_Status.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_property_Status)
 Dim dbParam_price_Range As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
 dbParam_price_Range.ParameterName = "@.Price_Range"
 dbParam_price_Range.Value = txtPriceRange.Text
 dbParam_price_Range.DbType = System.Data.DbType.String
 dbCommand.Parameters.Add(dbParam_price_Range)
 Dim rowsAffected As Integer = 0
 dbConnection.Open
 Try
 rowsAffected = dbCommand.ExecuteNonQuery
 Finally
 dbConnection.Close
 End Try
'if Not rowsAffected then
 Response.Redirect("./editproperty.aspx")
'end if
End Sub

Be sure to indicate parameter lengths for character-type data.

Terri

CAN ANYONE PLS HELP ME ON THIS--REGARDING SQL SERVER installation....VERY URGENT

I have downloaded the sql server 2005 express edition from the MSDN...but i cant figure out how to create the database there....no database engine is shown where I can proceed.Only Configuration tool is shown...how do i go about it...pls tell me asap.thanks

If you've installed the database engine (check config tool or services) your next step may be to download the SQL Server Management Studio Express which is the GUI for managing express databases. This doesn't come out the box (unless you chose the edition with advanced services).

HTH!

|||

Could you tell me that ...in order to work on VB.NET,ASP.NET,SQL SERVER 2000......to create web application.....which software to download from the internet free of cost so that I can work on it for practice.....

|||you can download Mnagment Studio from this link
http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&DisplayLang=en|||

Check out the Visual Studio express editions.

http://msdn2.microsoft.com/en-gb/express/default.aspx

Calling Stored Procedures using plain ADO in C#

Hello All,

We are trying to figure out how to make a stored procedure call and pass some inputs using C# and plain ADO. We are able to call an empty stored procedure but cannot pass parameters into a stored procedure.

Does anyone have sample code in C# whereby we can open a connection and pass inputs into a stored procedure? The following is a sample code we are using:

ADODB.Parameter param = new ADODB.Parameter();

param=cmd.CreateParameter("@.StoreID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput,sizeof(int) , 9);

cmd.Parameters.Append(param);

ADODB.Recordset rsInv = cmd.Execute(out objAffected, ref objAffected,-1 );

In the above example, we create a parameter of type integer and try to add it to the ADO Command object. It is supposed to return a recordset. It returns a recordset with record count -1.

Sincerely,

Dwight Kulkarni

Did you check the EOF property for the Recordset? The RecordCount property is not always an accurate indicator of how many rows have been returned, and in some instances will return a value of -1 (such as for a forward-only cursor).|||

It was a simple thing of course, the cursor location was EOF.

Thanks.