Correct Procedures for Testing Against NULLs from SQL Server

Hi all,
I have some C# code that is pulling data from a database where a majority of the values being retrieved areNULL, yet their initial column data types are bothstringandint, which means that I have to temporarily store theseNULL's inintandstringdatatypes in C#. Later on in my code I have to test against these values,and was wondering if I am doing it correctly with the following code.
The following statement the variableor_team_home_idis of astringdata type, but may have had aNULLvalue assigned to it from the database
if (!or_team_home_id.Equals(DBNull.Value)) {}
The following statement the variableor_manager_id is of aintdata type, but also may have aNULLvalue assigned to it from the database.
if (!Convert.IsDBNull(or_manager_id)){}
Are these the correct way to test against NULL values retrieved fromteh database and that are stored in their respective data types.
Tryst
For string types you could compare with System.DbNull.Value as in (VB.NET)
If strVal IS System.DbNull.Value then

End If
For numeric datatypes I'd recommend doing an ISNULL(column,0) from the SQL stmt so it will be easier/faster to check for 0 from the front end.sql

correct me??

I've created C#.net program (behind code style).

when I run it in Internet explorer, the following error occurs in IE window.

pls instruct me how to handle and correct this error.

And how to initialize the connectionstring... Great thank!

Server Error in '/' Application.

------------------------

The ConnectionString property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidOperationException: The ConnectionString property has not been initialized.]
System.Data.SqlClient.SqlConnection.Open() +809
CodeBox.BehindCode.getSubject() +80
CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +29
System.Web.UI.Page.ProcessRequestMain() +724

------------------------

Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0

Hi,
You haven't posted the code that you are trying to execute.
From the error, it seems that you havent set the connection string property for your connection object.
You can initialize the connection string as follows:-
SqlConnection objCon = new SqlConnection("put your connection string here");
Thanks.|||if you still have the problem show us the code !!|||

hi! thanks...
but I still have problem...
I want to show you my code and error message...
here is my code...
SqlDataReader getSubject(){
//create instance of connection and command object

//SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
constring ="DSN=fisca;User ID=sa;Password=admin;";
SqlConnection myconnection = new SqlConnection(constring);

SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);
//open the database connection and execute the command
myconnection.Open();
SqlDataReader result=mycommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}

here is error message. I sure that dsn is really existing and userid, password of sql server is true....
and I also sure that variable constring is declared.

Server Error in '/' Application.

---

Unknown connection option in connection string: dsn.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.ArgumentException: Unknown connection option in connection string: dsn.
Source Error:

Line 77: //SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);Line 78: constring ="DSN=fisca;User ID=sa;Password=admin;";Line 79: SqlConnection myconnection = new SqlConnection(constring);Line 80: Line 81: SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);

Source File:c:\inetpub\wwwroot\CourseRequisition.cs Line:79
Stack Trace:

[ArgumentException: Unknown connection option in connection string: dsn.] System.Data.SqlClient.ConStringUtil.ParseStringIntoHashtable(String conString, Hashtable values) +673 System.Data.SqlClient.ConStringUtil.ParseConnectionString(String connectionString) +55 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +375 System.Data.SqlClient.SqlConnection..ctor(String connectionString) +164 CodeBox.BehindCode.getSubject() in c:\inetpub\wwwroot\CourseRequisition.cs:79 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\CourseRequisition.cs:36 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724

---

Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0|||

here is my code...

SqlDataReader getSubject(){
//create instance of connection and command object
SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
SqlCommand mycommand=new SqlCommand("select * from Subject",myconnection);
//open the database connection and execute the command
myconnection.Open();
SqlDataReader result=mycommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
}
but now I can find it out and I've corrected it myself.
cause I initialize connectionstring in web.config file. It can put away error.
so now I want to know that is there many ways to connect to sql server?...
how many way?
I just know two way,
1. Sqlconnection constring=new Sqlconnection("connection string");
2. SqlConnection myconnection=new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);
so pls let me know. cause I willingly want to know C#.net thoroughly.
I also learning it from book and creating C#.net application..
Have a bright and wonderful day!

|||the connection string that you provided is not correct !!!
look atwww.connstingSttings.com and see the proper contents of the connection string try to look the connetion string and if you still have problem get back to me

copying tables from one database to another database

i am using visual web developer 2005 and SQL Express 2005 with VB as the code behind

i have two databases .

i want to copy all the tables with all the contents from one database to another database programatically

how to achieve this ?

please help me

easiest way is to use the detach - attach method or backup and restoresql

Copying Tables

Hi,

I'd like a really simple way of making a replica of a table. The thing is i'd like the table name to be a variable. The following code doesn't work, any ideas??

Thanks in advance,

Alph

CREATE Procedure Test

@.vMonth as varchar(3)

As

SELECT tbl_Targets.* INTO @.vmonth
FROM tbl_Targets;
GOLook up sp_executesql and EXECUTE in the SQL Books Online.

copying stored procedures

Hi,
How can I copy many sp's without recreate them by code
between two databases ?
Thanks,
OriUse the DTS copy objects function. You can set this up easily via the
wizard. Watch out for the option to copy dependent objects as well, as you
may wind up moving a table along with a SP.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
> How can I copy many sp's without recreate them by code
> between two databases ?
> Thanks,
> Ori|||Take a look at Data Transformation Services. Right-click Tables, select All
Tasks > Export data, and when you get to the screen that offers three
choices, select "Copy objects and data..."
The wizard is fairly self-explanatory.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
> How can I copy many sp's without recreate them by code
> between two databases ?
> Thanks,
> Ori|||Transfer via DTS?
Script and execute via Query Analyzer or Enterprise Manager?
-- Keith
"ori" <anonymous@.discussions.microsoft.com> wrote in message =news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
> Hi,
> > How can I copy many sp's without recreate them by code > between two databases ?
> > Thanks,
> > Ori|||Normal procedure is to generate a script of the required stored procedures,
and run that script on your target database.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"ori" <anonymous@.discussions.microsoft.com> wrote in message
news:137b501c44400$ef2e8a70$a401280a@.phx.gbl...
Hi,
How can I copy many sp's without recreate them by code
between two databases ?
Thanks,
Ori

Copying procedures in SQL from one server to another using VBA in excel

Is there any sample code or help on trying to copy/replace views or prodecure in SQL or Oracle? Im using VBA in Excel...

You can use SMO to do that and in SQL Server SMO/DMO section you can find , here , an example to copy a table between different instances; i think you can adapt it for sp.

|||SMO/DMO will certainly work as ggciubuc has mentioned if you'd like to continue using VBA, however there are a number of other tools out there that will make it easier.

Management Studio has the generate scripts menu item that will script out the database object so you can run the script against another server.

Red-Gate and other tools manufacturers have compare utilities that examine the database objects from one server and compare them to the objects of another server, scripting out the differences and even applying them if you wish.

Data Transformation Services (DTS) in SQL Server 2000 has a task that allows you to migrate database objects from one server to another.

HTH...

Joe

CopyFromRecordSet In Excel 2003

I want to use the CopyFromRecordSet however I am using a stored procedure that returns a recordset. Here is the code, but the recordset never opens. I have commented out the Execute

Here is a code snip, any Ideas? TIA Mike

Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim Rs As New ADODB.Recordset

Con1.Open

Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "MyStroredProc"
Cmd1.Execute
Rs.Open Cmd1

Worksheets("T1").Range("B1").CopyFromRecordset Rs

I believe it should be like

Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdStoredProc ' Please verify spelling for adCmdStoredProc, since I do not have documentation with me
Cmd1.CommandText = "MyStroredProc"
Set Rs = Cmd1.Execute

|||

VMazur,

Thank you for the code snip however when I query the recordset state, it is still closed or 0 and I generate an error. I did use 4 as the commandType

Any other thoughts would be appreciated.

'Open the connection
Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = 4 '' Stored Proc
Cmd1.CommandText = "REPORT_procASDCStatus"
Set Rs = Cmd1.Execute

'Rs.Open Cmd1 '' Tried this also
Worksheets("T1").Range("A1").CopyFromRecordset Rs <<Fails with message that rs is not open

MikeD

|||What happens when you call Execute method? Does it generate any error or not?|||

The issue was Set NO COUNT ON in the SP. This was missing in the SP.

As soon as I added this, the recordset opened and I could do copy from.

Thank you Mazur

|||

Hi Mazur,

There was no error message, only issue was there was no data transfer. As soon as we set NOCOuNT ON, data started transfering.

This question is resolved.

Thank you

Mike

CopyFromRecordSet In Excel 2003

I want to use the CopyFromRecordSet however I am using a stored procedure that returns a recordset. Here is the code, but the recordset never opens. I have commented out the Execute

Here is a code snip, any Ideas? TIA Mike

Dim Con1 As New ADODB.Connection
Dim Cmd1 As New ADODB.Command
Dim Rs As New ADODB.Recordset

Con1.Open

Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdText
Cmd1.CommandText = "MyStroredProc"
Cmd1.Execute
Rs.Open Cmd1

Worksheets("T1").Range("B1").CopyFromRecordset Rs

I believe it should be like

Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = adCmdStoredProc ' Please verify spelling for adCmdStoredProc, since I do not have documentation with me
Cmd1.CommandText = "MyStroredProc"
Set Rs = Cmd1.Execute

|||

VMazur,

Thank you for the code snip however when I query the recordset state, it is still closed or 0 and I generate an error. I did use 4 as the commandType

Any other thoughts would be appreciated.

'Open the connection
Con1.Open

Set Cmd1.ActiveConnection = Con1
Cmd1.CommandType = 4 '' Stored Proc
Cmd1.CommandText = "REPORT_procASDCStatus"
Set Rs = Cmd1.Execute

'Rs.Open Cmd1 '' Tried this also
Worksheets("T1").Range("A1").CopyFromRecordset Rs <<Fails with message that rs is not open

MikeD

|||What happens when you call Execute method? Does it generate any error or not?|||

The issue was Set NO COUNT ON in the SP. This was missing in the SP.

As soon as I added this, the recordset opened and I could do copy from.

Thank you Mazur

|||

Hi Mazur,

There was no error message, only issue was there was no data transfer. As soon as we set NOCOuNT ON, data started transfering.

This question is resolved.

Thank you

Mike

CopyFile in DTS ActiveX Script Task

I am trying to copy a file and giving it a name with a date behind it. Belo
w
is my code I'm using in my DTS ActiveX Script Task. I'm getting a "File Not
Found" message on Line 25 (I marked it below). Can someone see the problem
with my code'
Dim NYear
Dim NMonth
Dim NDate
NYear = Year(Date)
NMonth = Month(Date)
NDate = CStr(NYear) + CStr(NMonth)
Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Set oFSO = CreateObject("Scripting.FileSystemObject")
sSourceFile =
" \\wstlfp02\ebs$\SQLDataAccess\WPE\PandGI
nvoicesToMCFA\PGInvoiceDetailYYYYMM
"
sDestinationFile =
" \\wstlfp02\ebs$\SQLDataAccess\WPE\PandGI
nvoicesToMCFA\PGInvoiceDetail" +
"_RunDate_" + NDate
oFSO.CopyFile sSourceFile, sDestinationFile '***(This is Line 25)***
' Clean Up
Set oFSO = Nothing
Main = DTSTaskExecResult_SuccessHi
I assume you have tried outputting the file names in a MsgBox and validated
that they are correct? http://www.sqldts.com/default.aspx?292 has an example
of using the filesystem object to copy a file, and
http://www.sqldts.com/default.aspx?200 has an example of using the date as
part of a filename.
Make sure that the account that you are running this has permissions to the
share, it may be worth getting it working with a local drive first.
John
"atchleykl" wrote:

> I am trying to copy a file and giving it a name with a date behind it. Be
low
> is my code I'm using in my DTS ActiveX Script Task. I'm getting a "File N
ot
> Found" message on Line 25 (I marked it below). Can someone see the proble
m
> with my code'
> Dim NYear
> Dim NMonth
> Dim NDate
> NYear = Year(Date)
> NMonth = Month(Date)
> NDate = CStr(NYear) + CStr(NMonth)
> Dim oFSO
> Dim sSourceFile
> Dim sDestinationFile
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> sSourceFile =
> " \\wstlfp02\ebs$\SQLDataAccess\WPE\PandGI
nvoicesToMCFA\PGInvoiceDetailYYYY
MM"
> sDestinationFile =
> " \\wstlfp02\ebs$\SQLDataAccess\WPE\PandGI
nvoicesToMCFA\PGInvoiceDetail" +
> "_RunDate_" + NDate
> oFSO.CopyFile sSourceFile, sDestinationFile '***(This is Line 25)***
> ' Clean Up
> Set oFSO = Nothing
> Main = DTSTaskExecResult_Success
>

Copy'n data from one table to the other

I want to copy an entire tables data into a new table but this code does not work need some help

SELECT condcode,brief_desc,condtext
INTO CD_ConditionCode, CD_BriefDesc, CD_Description
FROM [frds2\gtprod].gtgrandstestuja.dbo.[condcode]

The INTO clause is where you define the target table.

You would do something like:

Code Snippet

SELECT condcode,brief_desc,condtext
INTO dbo.MyNewTable
FROM [frds2\gtprod].gtgrandstestuja.dbo.[condcode]

The table will be populated with the columns listed in the SELECT.

Copy'n an data from one table to the other

I want to copy an entire tables data into a new table but this code does not work need some help

SELECT condcode,brief_desc,condtext
INTO CD_ConditionCode, CD_BriefDesc, CD_Description
FROM [frds2\gtprod].gtgrandstestuja.dbo.[condcode]

The INTO clause is where you define the target table.

You would do something like:

Code Snippet

SELECT condcode,brief_desc,condtext
INTO dbo.MyNewTable
FROM [frds2\gtprod].gtgrandstestuja.dbo.[condcode]

The table will be populated with the columns listed in the SELECT.

Copy table from one SQL Server to Another

Using ASP.NET code (VB) I need to copy tables from one SQ: Server to another
SQL Server. This is a poor mans safety backup of some critical data. The
source SQL Server is hosted by an ISP where there is no way to access the DB
via EM or from anywhere other than web pages on that ISP web account so DTS
and such are not options.
WayneIf you're going "poor man's" route ...
Setup the source server as a linked server via your local SQL Server EM
using the credentials you use to login. Then while in your local DB, run
something like
select *
into [My Backup]
from [sqlServer.ispName.com].databasename.dbo.table
-- Alex Papadimoulis
"Wayne Wengert" wrote:

> Using ASP.NET code (VB) I need to copy tables from one SQ: Server to anoth
er
> SQL Server. This is a poor mans safety backup of some critical data. The
> source SQL Server is hosted by an ISP where there is no way to access the
DB
> via EM or from anywhere other than web pages on that ISP web account so DT
S
> and such are not options.
> Wayne
>
>|||The source server can only be "seen" from web pages on the ISP account!
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:7C69D8B7-1870-484D-8658-A21E230F2C0C@.microsoft.com...
> If you're going "poor man's" route ...
> Setup the source server as a linked server via your local SQL Server EM
> using the credentials you use to login. Then while in your local DB, run
> something like
> select *
> into [My Backup]
> from [sqlServer.ispName.com].databasename.dbo.table
> -- Alex Papadimoulis
> "Wayne Wengert" wrote:
>
another
the DB
DTS|||Wayne Wengert wrote:
> Using ASP.NET code (VB) I need to copy tables from one SQ: Server to
> another SQL Server. This is a poor mans safety backup of some
> critical data. The source SQL Server is hosted by an ISP where there
> is no way to access the DB via EM or from anywhere other than web
> pages on that ISP web account so DTS and such are not options.
> Wayne
I'm not sure how you can do that. You would probably need access to the
"other" SQL Server from the ISP's SQL Server by adding a linked server,
but I doubt you have those rights based on what you wrote. You could
create an ASP.Net page to display the contents of the table in a
datagrid (control name?), but from there all you have is the data in an
HTML page. I suppose you could package the table as XML and return the
raw XML in the HTML results.
Are you saying that the ISP won't provide you a backup of the database?
It's strange that you have ADO.Net access to the SQL Server from
ASP.Net, but can't access the server using something like the web data
administrator or even Query Analyzer. I guess the SQL Server has no
direct internet access and is behind the web server (which is a secure
way of setting things up). It's just more difficult from a maintenance
standpoint.
Something tells me there is more harmonious solution here... that I'm
probably just not thinking of. I would request from the ISP they give
you a backup of the database.
David Gugick
Imceda Software
www.imceda.com|||Since this is "critical data" ... get a new ISP ;-)
What about creating a page that outputs CSV or XML and then writing a DTS
package (on local) to grab the data from the URL?
-- Alex Papadimoulis
"Wayne Wengert" wrote:

> The source server can only be "seen" from web pages on the ISP account!
> Wayne
> "Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
> news:7C69D8B7-1870-484D-8658-A21E230F2C0C@.microsoft.com...
> another
> the DB
> DTS
>
>|||Alex;
Thanks for the response. I don't get to choose the ISP! I know that if I
have the table structure on the backup server I can write code to read each
row form the source server and "Insert Into" the backup copy. My problem is
that if they add or remove a field from a table on the source server I need
to reflect that change?
I guess that what I am looking for is a way to create the "CREATE TABLE..."
syntax in code?
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:9AECDF77-5D17-44AD-864E-A883FE054F4A@.microsoft.com...
> Since this is "critical data" ... get a new ISP ;-)
> What about creating a page that outputs CSV or XML and then writing a DTS
> package (on local) to grab the data from the URL?
>
> -- Alex Papadimoulis
> "Wayne Wengert" wrote:
>
EM
run
The
access
so|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:esSLUXFJFHA.2884@.tk2msftngp13.phx.gbl...
> Wayne Wengert wrote:
> I'm not sure how you can do that. You would probably need access to the
> "other" SQL Server from the ISP's SQL Server by adding a linked server,
> but I doubt you have those rights based on what you wrote. You could
> create an ASP.Net page to display the contents of the table in a
> datagrid (control name?), but from there all you have is the data in an
> HTML page. I suppose you could package the table as XML and return the
> raw XML in the HTML results.
> Are you saying that the ISP won't provide you a backup of the database?
The ISP does nightly backups but their restore services don't give me a warm
fuzzy (-;
I also want to get copies of the tables into an environment where I can use
other tools to see what is going on. The only export they offer is a CSV of
the data rows (no way to export the table structure I see.)

> It's strange that you have ADO.Net access to the SQL Server from
> ASP.Net, but can't access the server using something like the web data
> administrator or even Query Analyzer. I guess the SQL Server has no
> direct internet access and is behind the web server (which is a secure
> way of setting things up). It's just more difficult from a maintenance
> standpoint.
Amen to that!!!!!
> Something tells me there is more harmonious solution here... that I'm
> probably just not thinking of. I would request from the ISP they give
> you a backup of the database.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Oy.
Well, looks like you're just going to have to write it yourself. DDL isn't
very hard (I create my tables like that, instead of using EM). And then
you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
Some quick queries ...
select table_name from information_schema.tables
select column_name, data_type, data_size from information_schema.columns
where table_name = ?
Look thru the results, build your CREATE TABLE statement.
Good luck!
-- Alex
"Wayne Wengert" wrote:

> Alex;
> Thanks for the response. I don't get to choose the ISP! I know that if I
> have the table structure on the backup server I can write code to read eac
h
> row form the source server and "Insert Into" the backup copy. My problem i
s
> that if they add or remove a field from a table on the source server I nee
d
> to reflect that change?
> I guess that what I am looking for is a way to create the "CREATE TABLE...
"
> syntax in code?
> Wayne
> "Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
> news:9AECDF77-5D17-44AD-864E-A883FE054F4A@.microsoft.com...
> EM
> run
> The
> access
> so
>
>|||Thanks for the suggestion and the sample queries. I'll give it a try.
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:4714AABE-11B2-4ABF-A289-BAA9C6092524@.microsoft.com...
> Oy.
> Well, looks like you're just going to have to write it yourself. DDL isn't
> very hard (I create my tables like that, instead of using EM). And then
> you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
> Some quick queries ...
> select table_name from information_schema.tables
> select column_name, data_type, data_size from information_schema.columns
> where table_name = ?
> Look thru the results, build your CREATE TABLE statement.
> Good luck!
> -- Alex
>
> "Wayne Wengert" wrote:
>
each
is
need
TABLE..."
DTS
account!
message
Server
DB,
Server to
data.
account|||Bummer... they don't allow access to "master" SP's. On to Plan C
Wayne
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:4714AABE-11B2-4ABF-A289-BAA9C6092524@.microsoft.com...
> Oy.
> Well, looks like you're just going to have to write it yourself. DDL isn't
> very hard (I create my tables like that, instead of using EM). And then
> you've got the INFORMATION_SCHEMA and sys* tables. Just ... go at it ;-)
> Some quick queries ...
> select table_name from information_schema.tables
> select column_name, data_type, data_size from information_schema.columns
> where table_name = ?
> Look thru the results, build your CREATE TABLE statement.
> Good luck!
> -- Alex
>
> "Wayne Wengert" wrote:
>
each
is
need
TABLE..."
DTS
account!
message
Server
DB,
Server to
data.
account

Copy SQL server 2000 database structure

Hello,

Is there some solution to copy database using VB.NET 2003 code with all constraints ,specifications and relationships ?

Thnx in adv

you may script the database store it in a file. then use the "shell" command to run sqlcmd using the script you generated

or you may wan't to progaram using the sql SMO.

Copy SPROCs to different database

Is there a manual or code way to copy stored procedures from 1one sql db to
another without opening each one, copying, creating a new SPROC in target db
and pasting?
Can DTS accomplish this?You can use DTS. If you have enterprise manager, you can also just
right-click on a database and choose to import or export data. When you get
into the wizard, choose to "Copy objects and data betwen SQL Server
databases". You can then save this entire thing as a DTS package, which you
can manipulate, schedule, etc.
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"scott" wrote:

> Is there a manual or code way to copy stored procedures from 1one sql db t
o
> another without opening each one, copying, creating a new SPROC in target
db
> and pasting?
> Can DTS accomplish this?
>
>|||You can use Enterprise Manager and right click on the db and choose "All
Tasks - Generate SQL Script". Then choose all the sp's and you will get one
script for all the sps. Just run that in the other db.
Andrew J. Kelly SQL MVP
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>|||You can use DTS to copy database objects from one database to another.
Are you using Sql Server 2000 or Sql Server 2005? I think, if I rememer
correctly, there is a copy database or copy database objects task in 2000.
2005 makes it a whole lot easier.
-Tim
"scott" <sbailey@.mileslumber.com> wrote in message
news:e45c5eteGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Is there a manual or code way to copy stored procedures from 1one sql db
> to another without opening each one, copying, creating a new SPROC in
> target db and pasting?
> Can DTS accomplish this?
>

copy row within same table

Gud day guys...i have this problem of copying records within a table...pls help me

I have tried the code below but returned an error.......
"violation of PRIMARY or UNIQUE KEY constraint "INTEG_29" on table "SERVICE" "

Insert into Service (GroupNo,ServiceCode,ServiceName,SystemNo,Upload,timestampstr)
SELECT 6,ServiceCode,ServiceName,SystemNo,Upload,TIMESTAMPSTR
FROM Service
WHERE GroupNo=1

is this possible? pls help me with the sql statement to make this work......

note: timestampstr is the primary key with a not null value ...

Short answer: if timestampstr is your primary key, you need to have different values in there. I don't know what the definition of your timestampstr or what the semantics of its values are, so I can't tell you how to generate a unique value for it for the new rows.

HTH,

Mostafa Elhemali - SQL Engine

|||Its a datatype in Firebird which holds the timestamp value wherein date and time is stored = it is equivalent to datetime datatype in other languages...

my purpose in doing this is that i wanted to copy all records in a table(service) with groupno = 1 and insert in the same table changing only the groupno field in to 6 and all the other records are the same.....any alternative solution for this if primary doesnt allow the same value for a field? thanks.....|||you can not insert the value in the TimeStamp datatype explicitly, the Server will automatically insert the value into it when you do insert operation.