creating new tables vs passing parameters

Hi,
I am using an Access ADP front end with a SQL Server backend.
I have a report which is based on a stored procedure with multiple parameters.
I have a Search form with multiple drop downs and the parameters are passed to the Sp from this form.

I had many problems passing the parameters to the Report since some of them may not be supplied and a default of % should be used.

I finally decided to creat a new table using the SP and insert the data to that table.

My question is if I create a temp table named "A" each time the report is ran, and say two users run the report at the same time, what will happen?

Is there any way to creat a temp table that SQL Server would take care of its name?

ThanksThere is no problem if you create a temp table during the execution of a SP. SQLServer will take care of you temp table and identify it after the connection user, so that even if two different users executes the same store proc the temp tables that are created are different. A temporary table is scoped to the execution of the batch (here the store proc), that means the table will be removed after the store proc ends.

But I still don't understant why you are not satisfied with store procs and which is the big problem in passing parameters to one. what the benefit will be if you create a temp table?

ionut calin|||The problem is that I want the SP to be the recordsource for a report.
See, I have a search form with the below criteria:

Caller
Property Manager
Date
BuildingID
Comm. Status
Ops Mgr

Then, I have a stored procedure based on 3 joins. The user should be able to check the Parameters he wants to search for and the SP will return the results. For example if I want all the records for Date='2/2/2002' and BuildingID='55555', the SP will return all the recods for these values and % (any thing) for other parameters.

I am using an access front end and I use VB to pass the paramertes to the SP. Its fine so far. But when I set the recordsource of the report to a sql string executing the SP with the parameters the report gets the first parameter and not the second or third or... one.
I don't get why?
Thats why I decided to set the recordsource of the report to a table.
You can see the VB code and the SP below:

Stored Procedure:---------------
---------------
CREATE PROCEDURE dbo.SP_Report_ComIssue_Custom_Param(@.RCaller varchar(50) = '%',
@.RPropMgr varchar(50) = '%',
@.RDate varchar(50) = '%',
@.RComStat varchar(50) = '%',
@.ROpsMgr varchar(50) = '%',
@.RBID varchar(50) = '%')

AS SELECT
dbo.tblComIssue.ComID,
dbo.tblProperty.AVPID, d
bo.tblProperty.OpsMgrID,
dbo.tblProperty.ProjectMgrID,
dbo.tblProperty.SupervisorID,
dbo.tblComIssue.IssueTitle,
dbo.tblProperty.Division,
dbo.tblComIssue.ComDate,
dbo.tblComIssue.ComType,
dbo.tblComIssue.IssueTxt,
ISNULL(tblContact_2.FirstName, '') + ' ' + ISNULL(tblContact_2.LastName, '') AS Caller,
ISNULL(tblContact_1.FirstName, '')
+ ' ' + ISNULL(tblContact_1.LastName, '') AS [Property Mgr], dbo.tblComResponse.ResDate,
dbo.tblComResponse.ResponseTxt,
ISNULL(dbo.tblStaff.FirstName, '') + ' ' + ISNULL(dbo.tblStaff.LastName, '') AS [From],
dbo.tblComResponse.ResID,
dbo.tblProperty.BuildingID,
dbo.tblProperty.BldgName,
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, dbo.tblComIssue.ContactID,
dbo.tblProperty.ContactID AS PropMgr,
dbo.tblComIssue.IssueClosed
FROM dbo.tblComResponse LEFT OUTER JOIN
dbo.tblStaff ON
dbo.tblComResponse.StaffID = dbo.tblStaff.ID RIGHT OUTER JOIN
dbo.tblContact tblContact_2 RIGHT OUTER JOIN
dbo.tblComIssue ON tblContact_2.ContactID = dbo.tblComIssue.ContactID ON dbo.tblComResponse.ComID = dbo.tblComIssue.ComID LEFT OUTER JOIN
dbo.tblContact tblContact_1 RIGHT OUTER JOIN
dbo.tblProperty ON tblContact_1.ContactID = dbo.tblProperty.ContactID ON dbo.tblComIssue.PropID = dbo.tblProperty.PropID

WHERE (
dbo.tblComIssue.ContactID LIKE @.RCaller OR
dbo.tblComIssue.ContactID IS NULL) AND

(dbo.tblProperty.ContactID LIKE @.RPropMgr OR
dbo.tblProperty.ContactID IS NULL) AND

(dbo.tblComIssue.IssueClosed LIKE @.RComStat OR
dbo.tblComIssue.IssueClosed IS NULL) AND

(dbo.tblProperty.OpsMgrID LIKE @.ROpsMgr OR
dbo.tblProperty.OpsMgrID IS NULL) AND

(dbo.tblProperty.BuildingID LIKE @.RBID OR
dbo.tblProperty.BuildingID IS NULL) AND

(CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) LIKE CONVERT(Varchar(10), @.RDate, 101) OR
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) IS NULL)

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

VB Code:------------------
'I have check boxes for each parameter. If the check box is check and the users wants to serach by that check box I will add that parameter to the string:

Dim strSQL As String
Dim Param

Param = ""

If Me!ChCaller = True Then
Param = Param & "@.RCaller=" & "'" & Me!RCaller & "'"
End If

If Me!ChBID = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RBID=" & "'" & Me!RBID & "'"
End If

If Me!ChPropMgr = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RPropMgr=" & Me!RPropMgr
End If

If Me!ChDate = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RDate=" & "'" & Me!RDate & "'"
End If

If Me!ChComStat = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.RComStat=" & "'" & Me!RComStat "'"&
End If

If Me!ChOpsMgr = True Then
If Not Param = "" Then
Param = Param & ","
End If
Param = Param & "@.ROpsMgr=" & "'" & Me!ROpsMgr & "'"
End If

strSQL = "execute SP_Report_ComIssue_Custom_Param " & Param

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewDesign
Reports("Rpt_SP_Rport_ComIsssue_AdvSearch").RecordSource = strSQL

DoCmd.Close acReport, "Rpt_SP_Rport_ComIsssue_AdvSearch", acSaveYes

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewPreview|||Sorry for the delay, but not my fault. I've wrote a replay for you post it but it didn't apear (I don't know why).

First of all, passing parameters to stored procs:

-I would try to set null as the default value for params (i've had problem passing null value through an ODBC connection (here you have an OLEDB one, and it just might work, but why take chances)

So store proc would look like this:

CREATE PROCEDURE dbo.SP_Report_ComIssue_Custom_Param(@.RCaller varchar(50) = NULL,
@.RPropMgr varchar(50) = NULL,
@.RComStat varchar(50) = NULL,
@.ROpsMgr varchar(50) = NULL,
@.RBID varchar(50) = NULL)

AS SELECT

--@.RDate varchar(50) = '%', I can't see the point of this parm

dbo.tblComIssue.ComID,
dbo.tblProperty.AVPID, d
bo.tblProperty.OpsMgrID,
dbo.tblProperty.ProjectMgrID,
dbo.tblProperty.SupervisorID,
dbo.tblComIssue.IssueTitle,
dbo.tblProperty.Division,
dbo.tblComIssue.ComDate,
dbo.tblComIssue.ComType,
dbo.tblComIssue.IssueTxt,
ISNULL(tblContact_2.FirstName, '') + ' ' + ISNULL(tblContact_2.LastName, '') AS Caller,
ISNULL(tblContact_1.FirstName, '')
+ ' ' + ISNULL(tblContact_1.LastName, '') AS [Property Mgr], dbo.tblComResponse.ResDate,
dbo.tblComResponse.ResponseTxt,
ISNULL(dbo.tblStaff.FirstName, '') + ' ' + ISNULL(dbo.tblStaff.LastName, '') AS [From],
dbo.tblComResponse.ResID,
dbo.tblProperty.BuildingID,
dbo.tblProperty.BldgName,
CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) AS Date, dbo.tblComIssue.ContactID,
dbo.tblProperty.ContactID AS PropMgr,
dbo.tblComIssue.IssueClosed
FROM dbo.tblComResponse LEFT OUTER JOIN
dbo.tblStaff ON
dbo.tblComResponse.StaffID = dbo.tblStaff.ID RIGHT OUTER JOIN
dbo.tblContact tblContact_2 RIGHT OUTER JOIN
dbo.tblComIssue ON tblContact_2.ContactID = dbo.tblComIssue.ContactID ON dbo.tblComResponse.ComID = dbo.tblComIssue.ComID LEFT OUTER JOIN
dbo.tblContact tblContact_1 RIGHT OUTER JOIN
dbo.tblProperty ON tblContact_1.ContactID = dbo.tblProperty.ContactID ON dbo.tblComIssue.PropID = dbo.tblProperty.PropID

WHERE (
dbo.tblComIssue.ContactID IS NULL or @.RCaller is null or dbo.tblComIssue.ContactID LIKE '%'+@.RCaller
) AND

(
dbo.tblProperty.ContactID IS NULL or @.RPropMgr is null or dbo.tblProperty.ContactID LIKE '%'+@.RPropMgr
) AND

(
dbo.tblComIssue.IssueClosed IS NULL or @.RComStat is null or
dbo.tblComIssue.IssueClosed LIKE '%'+@.RComStat
) AND

(
dbo.tblProperty.OpsMgrID IS NULL or @.ROpsMgr is null or
dbo.tblProperty.OpsMgrID LIKE '%'+@.ROpsMgr
) AND

(
dbo.tblProperty.BuildingID IS NULL or @.RBID is null or
dbo.tblProperty.BuildingID LIKE '%'+@.RBID OR
) --AND

--this make no sense at all. What's the point of next possible condition
--"03/12/2003" like "%01/12/2003". You can not use like to compare a
--date field with what another date, or another date part. No sense at all
--(at least not for me) so I've remove it
--(CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) LIKE
--CONVERT(Varchar(10), @.RDate, 101) OR
--CONVERT(Varchar(10), dbo.tblComIssue.ComDate, 101) IS NULL)

VB:

In VB when you want to call a sub, a functin or a method and you don't want to give explicit values for the parameters that have a default one, you simply "step over" the parameter, but still put commas, like:

DOCmd.SomeMethod 1,,3,4 (here param 2 takes whatever its default value is)

In SQLServer store proc you can't just simply ignore a param. If you want a parameter to take its default value you must write DEFAULT instead of explicit value.

exec SomeStoreProc 1,DEFAULT,3,4 (You don't have to specify param names like @.PARAM1=1, @.PARAM2=default and so on...)

So here is your VB code:

Dim strSQL As String
Dim Param

Param = ""

If Me!ChCaller = True Then
Param = "'" & Me!RCaller & "' , "
else
Param="DEFAULT , "
End If

If Me!ChBID = True Then
Param = Param & "'" & Me!RBID & "' , "
else
Param=Param & "DEFAULT , "
End If

If Me!ChPropMgr = True Then
Param = Param & "'" & Me!RPropMgr & "' , "
else
Param=Param & "DEFAULT , "
End If

'I removed the follwoing lines (for the known reason)
'If Me!ChDate = True Then
'If Not Param = "" Then
'Param = Param & ","
'End If
'Param = Param & "@.RDate=" & "'" & Me!RDate & "'"
'End If

If Me!ChComStat = True Then
Param = Param & "'" & Me!RComStat "' , "
else
Param=Param & "DEFAULT , "
End If

If Me!ChOpsMgr = True Then
Param = Param & "'" & Me!ROpsMgr & "'"
else
Param=Param & "DEFAULT"
End If

strSQL = "execute SP_Report_ComIssue_Custom_Param " & Param

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewDesign
Reports("Rpt_SP_Rport_ComIsssue_AdvSearch").RecordSource = strSQL

DoCmd.Close acReport, "Rpt_SP_Rport_ComIsssue_AdvSearch", acSaveYes

DoCmd.OpenReport "Rpt_SP_Rport_ComIsssue_AdvSearch", acViewPreview

I am not too familiar with .adp type of project, and I'm not sure if this can really work:

RecordSource = "execute SP_Report_ComIssue_Custom_Param " & Param

but if you said that it works its ok. The main problem was the way that you passed the parameters.

Another thing. If you use Access XP .adp's then your store procedures are seen by Access as querys, so you can set that query as record source for report in design view, and not need to change afterwards (only need to set parameters value for querys -> it's very simple using ADODB and ADOX objects hierachy-> see Catalog object in ADO help)

In Access 2000, .adp's works with SQLServer7 and sees store proces as store procs. I don't think that you can set the record source of a report to a store proc (only to a table or a query, maybe that was thw reason they change the way that store procs aree seen in Access 2002)

Anyway record source property of Access report sucks, you can not for instance set the recor source to a ADO recordset (or to a DAO recordset for that matter). I think this is relly stupid, because prevent programmers from benefit the real power of SQLServer store procs, and I mean it store procs are the best (and now user defined functions too).

Anyway, good luck!

ionut calin

creating new tables on diff file group using EM

Hi,
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgs
In Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs
|||tks Cristian , i got it
"Cristian Lefter" wrote:

> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Second
> button from left to right) and use "Table Filegroup" dropdown list to select
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
>
>

creating new tables on diff file group using EM

Hi,
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgsIn Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs|||tks Cristian , i got it
"Cristian Lefter" wrote:
> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Second
> button from left to right) and use "Table Filegroup" dropdown list to select
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> > Hi,
> >
> > How do i acutally create a new table that i can specify to a different
> > file group using Enterprise Manager ?
> >
> > this is how i done in TSQL :
> >
> > create table test
> > ( myname char(20)
> > )
> > on MYOWNFILEGROUP
> >
> > appreciate any advice
> >
> > tks & rdgs
>
>

creating new tables on diff file group using EM

Hi,
How do i acutally create a new table that i can specify to a different
file group using Enterprise Manager ?
this is how i done in TSQL :
create table test
( myname char(20)
)
on MYOWNFILEGROUP
appreciate any advice
tks & rdgsIn Enterprise Manager, Right Click on "Tables" node and select "New Table"
from the context menu. Click on "Table and Index Properties" button (Second
button from left to right) and use "Table Filegroup" dropdown list to select
"MYOWNFILEGROUP".
Cristian Lefter, SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
> Hi,
> How do i acutally create a new table that i can specify to a different
> file group using Enterprise Manager ?
> this is how i done in TSQL :
> create table test
> ( myname char(20)
> )
> on MYOWNFILEGROUP
> appreciate any advice
> tks & rdgs|||tks Cristian , i got it
"Cristian Lefter" wrote:

> In Enterprise Manager, Right Click on "Tables" node and select "New Table"
> from the context menu. Click on "Table and Index Properties" button (Secon
d
> button from left to right) and use "Table Filegroup" dropdown list to sele
ct
> "MYOWNFILEGROUP".
> Cristian Lefter, SQL Server MVP
> "maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
> news:D90E63AB-F3AC-426F-875D-E8CB71B6189C@.microsoft.com...
>
>

Creating new tables in aspnetdb.mdf

I have made a new table called 'customer' which i wish to tie into the userId of the db. I have used a db diagram to do this (there are keys on each side of the link and userId is the FK) . When i put the membership/profile view onto the form alongside the new customer table nothing displays in the customer table when i run the app., i dont even see the titles - any ideas (i'm new to this sql malarky btw - so its probably something unbelievably straightforward). Any help appreciated.

Thanks

Hi,

By default, the membership/profile view will not show your additional information.

Since you have added one table into the database, and need to show it on your view, you will need to use your own query to get the user info together with the data in customer table. This can be done either by creating a View or use JOIN statement directly.

Then, you can customize the web UI to show the additional info.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

Hi, and thanks for replying.

I sort of rather optimistically and naively thought that because my new table was using pre-defined field variables including the key field (i.e. UserId (key), UserName etc.) that the system would know that these were what I required to be displayed (through the diagram), with no further programming, except to set to set the new field variables, that I've added, to new values within the program, and that the new fields would simply display the default value until I did something with them. - in another world that might have happened. After all, what have I created the diagram for?

What I didnt realise was that the aspnet.mdf tables are created, configured, and deleted through the Stored Procedures and that I needed to do the same for my table, even though there is a diagram telling the system that this 'linking in' of the aspnet_userId and my table is what I want to happen .

As I'm using the UserId as my key field I want the table to reflect the same membership as the aspnet.mdf membership tally - so I've sneaked in one or two statements within the createuser and deleteuser Stored Procedures to put in or remove UserId values from my table as new members are created or deleted respectively. I hope that that will solve my problem - it seems to have so far. I just cant help thinking that somewhere along the way my table is not going to tally with aspnet users for some reason, due to something that I'm not aware of (yet), caused by some insertion or deletion of a UserId without the createuser or deleteuser Stored Procedures being involved.

I'm sort of at a loss as to why I've created the diagram. I could now delete it and nothing would change.

Thanks again.

Creating new tables for new users

hi all,

How can i create a new data table automatically for new users signing into the website......what i mean here is that i have a predefined database table and i want that users signing in can have that table for them unique , so that they can fill data for themselves and that data will be visible to all just like forums

Hello Pankaj,

You can instantiate a new DataTable in the session where the user can modify his data.

But another possibility is to add a column like userid or username to the predefined table and write business logic that allows the user to modify its own records (where his username matches the username in the records) and other users can only read the records.

In this case a user is owner of his records (has it unique to himself for updates).

Jeroen Molenaar.

Creating New tables by copying existing table

Hi,
Is there any easy way of copying the structure of a table within a single
database and creating a new table with a new name. I have an app that the
structure of several new tables is close enough that it would save time if I
could create one and copy and recreate a new one under a new name. Then I
would only have to make the changes that are different.
thanks,
hughVery Easy
Select * into NewTableName from OldTableName where 1= 0
Keep in mind that this is just the table structure no keys, constraints or
indexes are created
http://sqlservercode.blogspot.com/
"Hugh O" wrote:

> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
I
> could create one and copy and recreate a new one under a new name. Then I
> would only have to make the changes that are different.
> thanks,
> hugh
>
>|||Script the CREATE TABLE statement and then edit it. You can do that in Query
Analyzer.
David Portas
SQL Server MVP
--
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:%23kGLB6DzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
> I could create one and copy and recreate a new one under a new name. Then
> I would only have to make the changes that are different.
> thanks,
> hugh
>|||Hi
You should consider putting your table definitions under version control in
which case you could use the scripting options on Enterpeise Manager or
Query Analyser to create the file to be edited.
John
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:%23kGLB6DzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
> I could create one and copy and recreate a new one under a new name. Then
> I would only have to make the changes that are different.
> thanks,
> hugh
>|||>> I have an app that the structure of several new tables is close enough th
at it would save time if I could create one and copy and recreate a new one
under a new name. Then I would only have to make the changes that are diffe
rent. <<
Be careful about this. While not a certainity, this is a sign that you
might have split a table on an attributes. For example, someone
recently posted a set of tables where an event had been put into a
temporal table and a separate physical location table and then was
trying to tie them back together to answer queries about the location
and status at a site.|||Thanks SQL, David, John, & Celko
These newsgroups are great. So helpful and shows the diversity and
alternatives so clearly.
hugh
"Hugh O" <HughLD@.newsgroup.nospam> wrote in message
news:%23kGLB6DzFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is there any easy way of copying the structure of a table within a single
> database and creating a new table with a new name. I have an app that the
> structure of several new tables is close enough that it would save time if
> I could create one and copy and recreate a new one under a new name. Then
> I would only have to make the changes that are different.
> thanks,
> hugh
>

Creating new table from passed variabe in MSSQL Stored procedure

Hi there i really cant understand why this should be a problem... it seems that i cant create a table from a stored procedure when passing the tablenam from application like this...

CREATE PROCEDURE dbo.CreateTable
@.TableName NVARCHAR(50)
AS
BEGIN

create table @.TableName ([id] int identity(1,1),[diller] int)

end
GO

THIS CANT BE TRUE !!!!!!!!You need to do the following to get it working

CREATE PROCEDURE dbo.CreateTable
@.TableName NVARCHAR(50)
AS

BEGIN
Declare @.SQLStatement nvarchar(4000)

SET @.SQLStatement = 'create table ' + @.TableName + '([id] int identity(1,1),[diller] int)'

Exec (@.SQLStatement )

end
GO

That should do it.

Let me know if you have any questions.|||If it aint to much i would very much like to check for the tables existens before i create it... so basicly i would need to check if the variable exists as a table before i insert it something like

if not exists(Select * From @.TableName)
begin

Stuff...

End

And by the way thk you for the quick reply...|||You can have it as
if not exists(Select * From INFORMATION_SCHEMA.TABLES Where TABLE_NAME = @.TableName)
begin

Stuff...

End

Recently, I wrote article on how to check the objects in database. You can check it at www.aspalliance.com/349 to understand the information_schema views.

Let me know if the above does not work for you.

AP|||Thk you very much been pulling my hair over this... and good article very helpfull... i just startet programming in SQL well my vision is to create websites structured from SQL so that changing stuff around the page is going to be an ease for the user..

thx again...

Jakob

Creating new table from existing table

Hi,
I m new to this forum with a Query
Create table <table name1 > as select * from <table name2>
this works for oracle. do anybody knows its alternative in sqlserver 2000
thanx. :)you can use SELECT INTO

select * into <table name2> from <table name1 >|||Hi

A minor adjustment:
SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

SELECT * INTO <table name2>
FROM <table name1 >
This does the same thing but creates the table and then populates it. Reason? Whilst the table is being created, tempdb gets locked up. If you do it in one stage then the creation lasts as long as the data insertion. Lots of data = long tempdb lock up.

HTH|||Hi

A minor adjustment:
SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

SELECT * INTO <table name2>
FROM <table name1 >
This does the same thing but creates the table and then populates it. Reason? Whilst the table is being created, tempdb gets locked up. If you do it in one stage then the creation lasts as long as the data insertion. Lots of data = long tempdb lock up.

HTH

Again correction,above code will throw error.And tempdb will get locked if u r creating temp table otherwise it will lock some of the system tables in database where u create table.

SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

insert into <table name2>
select *
from <table name1 >|||Again correction,above code will throw error.And tempdb will get locked if u r creating temp table otherwise it will lock some of the system tables in database where u create table.

SELECT TOP 0 * INTO <table name2>
FROM <table name1 >

insert into <table name2>
select *
from <table name1 >
Duh - yeah - thanks Mallier - copy and paste error :o

Creating New Table & Linking to Datas

Hi Everybody,
I have a table as "AdjSummary" with 5 columns. I have to create a
table with 2 columns from the above table. The structure of the table
"AdjSummary" is as below
INVNO ADJUST MODE PAYMENT MODE
10 100 OTH 0 NULL
11 150 OTH 0 NULL
11 100 DET 0 NULL
12 0 NULL 50 CSH
12 50 DET 50 VIS
12 12 DET 0 NULL
My new table should be with 2 columns (INVNO & ADJUST) as below.
INVNO ADJUST
10 100
11 250
12 62
Moreover, I want ADJUST column should be not equal to zero.
Pls help me in creating the table & to link the data to new table.
Thanks for your help in advance.
Regards,
Selvarathinam.Selvarathinam wrote:
> Hi Everybody,
> I have a table as "AdjSummary" with 5 columns. I have to create a
> table with 2 columns from the above table. The structure of the table
> "AdjSummary" is as below
> INVNO ADJUST MODE PAYMENT MODE
> 10 100 OTH 0 NULL
> 11 150 OTH 0 NULL
> 11 100 DET 0 NULL
> 12 0 NULL 50 CSH
> 12 50 DET 50 VIS
> 12 12 DET 0 NULL
> My new table should be with 2 columns (INVNO & ADJUST) as below.
> INVNO ADJUST
> 10 100
> 11 250
> 12 62
> Moreover, I want ADJUST column should be not equal to zero.
> Pls help me in creating the table & to link the data to new table.
> Thanks for your help in advance.
> Regards,
> Selvarathinam.
SELECT INVNO, ADJUST
--INTO MyTable
FROM AdjSummary
WHERE ADJUST <> 0
This will return the records to you in Query Analyzer. If you want to
keep a permament copy of the records, uncomming the INTO line above,
and change "MyTable" to a legitimate table name.|||Tracy McKibben wrote:
> Selvarathinam wrote:
> SELECT INVNO, ADJUST
> --INTO MyTable
> FROM AdjSummary
> WHERE ADJUST <> 0
> This will return the records to you in Query Analyzer. If you want to
> keep a permament copy of the records, uncomming the INTO line above,
> and change "MyTable" to a legitimate table name.
Sorry, make that:
SELECT INVNO, SUM(ADJUST)
--INTO MyTable
FROM AdjSummary
GROUP BY INVNO
HAVING SUM(ADJUST) <> 0

creating new sql database

Hi,
I am creating a new database with medium complexity, will have around 15
tables with most of them related by key relationships. I am planing on using
mssql2000, enterprise manager but I also have vs2005 installed. Just
wondering if there is any advantage in creating a database project in vs2005
and is that what it is for(to create new databases?) We do not have sql 2005
yet other than the developers version that comes with vs2005 (does not allow
networking).
Thanks,
Paul G
Software engineer.
Once you've collected the business requirements and designed the database ,
you can use Diagrams.

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:06DDA0DB-B543-4C13-9A5C-ECBCBF7CA81B@.microsoft.com...
> Hi,
> I am creating a new database with medium complexity, will have around 15
> tables with most of them related by key relationships. I am planing on
> using
> mssql2000, enterprise manager but I also have vs2005 installed. Just
> wondering if there is any advantage in creating a database project in
> vs2005
> and is that what it is for(to create new databases?) We do not have sql
> 2005
> yet other than the developers version that comes with vs2005 (does not
> allow
> networking).
> Thanks,
> --
> Paul G
> Software engineer.

creating new sql database

Hi,
I am creating a new database with medium complexity, will have around 15
tables with most of them related by key relationships. I am planing on using
mssql2000, enterprise manager but I also have vs2005 installed. Just
wondering if there is any advantage in creating a database project in vs2005
and is that what it is for(to create new databases?) We do not have sql 2005
yet other than the developers version that comes with vs2005 (does not allow
networking).
Thanks,
--
Paul G
Software engineer.Once you've collected the business requirements and designed the database ,
you can use Diagrams.
Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:06DDA0DB-B543-4C13-9A5C-ECBCBF7CA81B@.microsoft.com...
> Hi,
> I am creating a new database with medium complexity, will have around 15
> tables with most of them related by key relationships. I am planing on
> using
> mssql2000, enterprise manager but I also have vs2005 installed. Just
> wondering if there is any advantage in creating a database project in
> vs2005
> and is that what it is for(to create new databases?) We do not have sql
> 2005
> yet other than the developers version that comes with vs2005 (does not
> allow
> networking).
> Thanks,
> --
> Paul G
> Software engineer.

Creating New Packages - Saving in Project Folder Unnecessarily

In BI Dev Studio, I have a Project/Solution created successfully, which has got say 5 packages working fine. I created a new package by Right Clicking on a solution explorer and selecting "New SSIS Package". System has given itself a default name and I decided to leave the BI Dev Studio WITHOUT SAVING project / solution.

Even though I haven't saved the project / solution still it left a package in project folder with a name "Package1.dtsx". I think this is a bug? Any ideas please?

Thanks

SuthaThis is true. I am sure this is a bug.|||If it is a bug, it is one common to all VS products, and has been since VS .Net came out. The IDE will always create files on disk first. You must supply the filename upfront for a solution, project or file, although in the later case in can be inferred as it is here. I'd rather it save it for me, than me forget and loose work. VS will also save the package every time you execute it as well.|||Daran, thanks.

I understand your argument. I suppose if it is unnecessary we can delete manaually, rather than losing hours of work incase of any disaster.

Creating new Notificatio services instance

When I try to create new Notification services instance, I am getting the following error message.

"Notification services failed to open a connection to sql server".

I have changed default connections to remote.But still I am getting same error.Please help me I am new to Notification services.

Can you elaborate a bit? Anything in the windows event log? Are you an administrator on the SQL Server? Does your SQL Server instance allow remote connections?

Joe

Creating new Notificatio services instance

When I try to create new Notification services instance, I am getting the following error message.

"Notification services failed to open a connection to sql server".

I have changed default connections to remote.But still I am getting same error.Please help me I am new to Notification services.

Can you elaborate a bit? Anything in the windows event log? Are you an administrator on the SQL Server? Does your SQL Server instance allow remote connections?

Joe

Creating new login on SQL server

hello,
I am facing problem to create a new login account on SQL server
which is on different domain.
Can any one help me out?
Thanks.It is OK if the domains have a trust relationship - if not you can't do it.
"Sushi" <sushi_20003@.yahoo.co.uk> wrote in message
news:8f9a3b2.0403142131.7a420e66@.posting.google.com...
> hello,
> I am facing problem to create a new login account on SQL server
> which is on different domain.
> Can any one help me out?
> Thanks.|||Hi,
Are you able to connect to SQL server using SQL server authentication, If
yes, then you will not have any problems in creating the login.
1. Connect to SQL Server using SA user
2. Use SP_ADDLOGIN (refer books online) to add the login
3. Use SP_ADDUSERS (refer books online) to add the user to database
4. Use SP_ADDROLEMEMBER to add the database role to that user.
Note:
If you are not able to connect to the SQL server,Check with system
administrator on Trust Relationship. If it is not created ask him to
create the Trust relationship and then perform the above steps to create
Login.
Thanks
Hari
MCDBA
"Sushi" <sushi_20003@.yahoo.co.uk> wrote in message
news:8f9a3b2.0403142131.7a420e66@.posting.google.com...
> hello,
> I am facing problem to create a new login account on SQL server
> which is on different domain.
> Can any one help me out?
> Thanks.

Creating new instances of SQL Server 2005 Express

Greetings,
I am trying to create new instances of sqlserver express (while the
default SQLEXPRESS is still there). How can i do that?
I'm trying to set up a development and production instance in one
machine. is this a right way to do it?
please advice.
am i doing the right thing? do people really do this? as in separating
development and production with the sql server instance?
On May 23, 3:49 pm, "vt" <vinu.t.1...@.gmail.com> wrote:[vbcol=seagreen]
> cerebellum,
> Run the SQLEXPRESS installation again, during the installation time setup
> will ask for an instance name., give the new instance name there
> regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/
> "cerebellum" <wilsone...@.gmail.com> wrote in message
> news:1179906209.618347.241560@.p77g2000hsh.googlegr oups.com...
>
|||Hi
Actually , it will be better if you would have a separated server for
developing. Make sure that you have enough memory on the server as each
instance will consume memory and it may hurt performance
"cerebellum" <wilsoneden@.gmail.com> wrote in message
news:1179988261.600407.241950@.a26g2000pre.googlegr oups.com...
> am i doing the right thing? do people really do this? as in separating
> development and production with the sql server instance?
> On May 23, 3:49 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
>

Creating new instances of SQL Server 2005 Express

Greetings,
I am trying to create new instances of sqlserver express (while the
default SQLEXPRESS is still there). How can i do that?
I'm trying to set up a development and production instance in one
machine. is this a right way to do it?
please advice.cerebellum,
Run the SQLEXPRESS installation again, during the installation time setup
will ask for an instance name., give the new instance name there
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"cerebellum" <wilsoneden@.gmail.com> wrote in message
news:1179906209.618347.241560@.p77g2000hsh.googlegroups.com...
> Greetings,
> I am trying to create new instances of sqlserver express (while the
> default SQLEXPRESS is still there). How can i do that?
> I'm trying to set up a development and production instance in one
> machine. is this a right way to do it?
> please advice.
>|||am i doing the right thing? do people really do this? as in separating
development and production with the sql server instance?
On May 23, 3:49 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
> cerebellum,
> Run the SQLEXPRESS installation again, during the installation time setup
> will ask for an instance name., give the new instance name there
> regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/
> "cerebellum" <wilsone...@.gmail.com> wrote in message
> news:1179906209.618347.241560@.p77g2000hsh.googlegroups.com...
> > Greetings,
> > I am trying to create new instances of sqlserver express (while the
> > default SQLEXPRESS is still there). How can i do that?
> > I'm trying to set up a development and production instance in one
> > machine. is this a right way to do it?
> > please advice.|||Hi
Actually , it will be better if you would have a separated server for
developing. Make sure that you have enough memory on the server as each
instance will consume memory and it may hurt performance
"cerebellum" <wilsoneden@.gmail.com> wrote in message
news:1179988261.600407.241950@.a26g2000pre.googlegroups.com...
> am i doing the right thing? do people really do this? as in separating
> development and production with the sql server instance?
> On May 23, 3:49 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
>> cerebellum,
>> Run the SQLEXPRESS installation again, during the installation time setup
>> will ask for an instance name., give the new instance name there
>> regards
>> VT
>> Knowledge is power, share it...http://oneplace4sql.blogspot.com/
>> "cerebellum" <wilsone...@.gmail.com> wrote in message
>> news:1179906209.618347.241560@.p77g2000hsh.googlegroups.com...
>> > Greetings,
>> > I am trying to create new instances of sqlserver express (while the
>> > default SQLEXPRESS is still there). How can i do that?
>> > I'm trying to set up a development and production instance in one
>> > machine. is this a right way to do it?
>> > please advice.
>

Creating new instances of SQL Server 2005 Express

Greetings,
I am trying to create new instances of sqlserver express (while the
default SQLEXPRESS is still there). How can i do that?
I'm trying to set up a development and production instance in one
machine. is this a right way to do it?
please advice.cerebellum,
Run the SQLEXPRESS installation again, during the installation time setup
will ask for an instance name., give the new instance name there
regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"cerebellum" <wilsoneden@.gmail.com> wrote in message
news:1179906209.618347.241560@.p77g2000hsh.googlegroups.com...
> Greetings,
> I am trying to create new instances of sqlserver express (while the
> default SQLEXPRESS is still there). How can i do that?
> I'm trying to set up a development and production instance in one
> machine. is this a right way to do it?
> please advice.
>|||am i doing the right thing? do people really do this? as in separating
development and production with the sql server instance?
On May 23, 3:49 pm, "vt" <vinu.t.1...@.gmail.com> wrote:[vbcol=seagreen]
> cerebellum,
> Run the SQLEXPRESS installation again, during the installation time setup
> will ask for an instance name., give the new instance name there
> regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/
> "cerebellum" <wilsone...@.gmail.com> wrote in message
> news:1179906209.618347.241560@.p77g2000hsh.googlegroups.com...
>
>
>|||Hi
Actually , it will be better if you would have a separated server for
developing. Make sure that you have enough memory on the server as each
instance will consume memory and it may hurt performance
"cerebellum" <wilsoneden@.gmail.com> wrote in message
news:1179988261.600407.241950@.a26g2000pre.googlegroups.com...
> am i doing the right thing? do people really do this? as in separating
> development and production with the sql server instance?
> On May 23, 3:49 pm, "vt" <vinu.t.1...@.gmail.com> wrote:
>

Creating New Indexes

I have a table that I need to update and the update is killing everything
else because the column I need to update appears to not be indexed. Now, I
thought I could go into the Design Table screen and add an index, but that
doesn't seem to have helped me. So, how then do I check what indexes and/or
keys exist on a given table? Thanks.
WillieWillie,
Try:
EXEC SP_HELPINDEX YOURTABLENAME
or use Manage Indexes in Query Analyzer.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hi,
The best way Jelly told u
if u need more then u can search like this
declare @.id varchar(20)
select @.id=id from sysobjects where name='custtable'
select * from sysindexes where id=@.id
from
Doller|||You don't need to index the column that's being updated to improve
performance just the index the columns in the WHERE clause.
Exmaple
1 million rows or
col1 int
col2 int
UPDATE table SET col1 = 5 WHERE col2 = 9
A full table scan must be performed to locate the data, if you add an index
on col1 a table scan will still be performed because what you're searching
on is col2
you should place an index on col2. This will enable SQL server to locate the
row quickly.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hmm... OK, so I go in and the Index does exist. Now, the column I am
updating is the same column that I am querying on. Basically, I am trying to
do a bulk update on a column and that column is the only thing that
designates it. Here is the table create script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CustomerProduct]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[CustomerProduct]
GO
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
And the Index info:
customerproduct_Site_ProductNumber nonclustered located on PRIMARY iSiteId,
chProductNumber
CustomerProduct0 nonclustered located on PRIMARY iOwnerId, iProductId
IX_CustomerProduct_Source nonclustered located on PRIMARY iSourceId
NNXCustomerProduct_iContactId nonclustered located on PRIMARY iContactId
NNXcustomerproduct_ownerid nonclustered located on PRIMARY iOwnerId
NNXcustomerproduct_prodnumber nonclustered located on PRIMARY
chProductNumber
NNXcustomerproduct_serialnum nonclustered located on PRIMARY vchSerialNumber
NNXCustomerProductInsertDate nonclustered located on PRIMARY dtInsertDate
NNXCustomerProductRecStat nonclustered located on PRIMARY tiRecordStatus
PKNUCCustomerProduct clustered, unique, primary key located on PRIMARY
iProductId, iSiteId
and finally the field I am searching/updating on is chProductNumber. Thanks
again for your help.
Willie
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:LLn1f.7212$nF6.3110@.fe04.news.easynews.com...
> You don't need to index the column that's being updated to improve
> performance just the index the columns in the WHERE clause.
> Exmaple
> 1 million rows or
> col1 int
> col2 int
> UPDATE table SET col1 = 5 WHERE col2 = 9
> A full table scan must be performed to locate the data, if you add an
> index on col1 a table scan will still be performed because what you're
> searching on is col2
> you should place an index on col2. This will enable SQL server to locate
> the row quickly.
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>

Creating new indexes

Hello all,
Just a quickie. If i add new indexes to tables, do I have to reanalyse /
reindex / do anything or is this done automatically by SQL Server? We're
running SQL Server 2000.
Thanks,
JonYes correct, Whenever you Create/Rebuild/Drop n Create a index its
pages are Organized, and there is nothing much you can do on it.
But yes if your senario is highly intensive transactional (Lots of
Inserts n Deletes), Then u maye want to check back on the Index
Fragmentations.
On Jan 22, 1:36 pm, Jon <J...@.discussions.microsoft.com> wrote:
> Hello all,
> Just a quickie. If i add new indexes to tables, do I have to reanalyse /
> reindex / do anything or is this done automatically by SQL Server? We're
> running SQL Server 2000.
> Thanks,
> Jon|||Hello,
If you add a clustered index automatically all the non clustered index
associated with that table will be recreated and its statistics will be
updated. If it is non clustered index
you may need to reindex all the other indexes individually or use DBCC
DBREINDEX('Tablename')
Thanks
Hari
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:F2A8A2B8-C395-49A6-8751-0D9592FF3D38@.microsoft.com...
> Hello all,
> Just a quickie. If i add new indexes to tables, do I have to reanalyse /
> reindex / do anything or is this done automatically by SQL Server? We're
> running SQL Server 2000.
> Thanks,
> Jon

Creating new indexes

Hello all,
Just a quickie. If i add new indexes to tables, do I have to reanalyse /
reindex / do anything or is this done automatically by SQL Server? We're
running SQL Server 2000.
Thanks,
Jon
Yes correct, Whenever you Create/Rebuild/Drop n Create a index its
pages are Organized, and there is nothing much you can do on it.
But yes if your senario is highly intensive transactional (Lots of
Inserts n Deletes), Then u maye want to check back on the Index
Fragmentations.
On Jan 22, 1:36 pm, Jon <J...@.discussions.microsoft.com> wrote:
> Hello all,
> Just a quickie. If i add new indexes to tables, do I have to reanalyse /
> reindex / do anything or is this done automatically by SQL Server? We're
> running SQL Server 2000.
> Thanks,
> Jon
|||Hello,
If you add a clustered index automatically all the non clustered index
associated with that table will be recreated and its statistics will be
updated. If it is non clustered index
you may need to reindex all the other indexes individually or use DBCC
DBREINDEX('Tablename')
Thanks
Hari
"Jon" <Jon@.discussions.microsoft.com> wrote in message
news:F2A8A2B8-C395-49A6-8751-0D9592FF3D38@.microsoft.com...
> Hello all,
> Just a quickie. If i add new indexes to tables, do I have to reanalyse /
> reindex / do anything or is this done automatically by SQL Server? We're
> running SQL Server 2000.
> Thanks,
> Jon

Creating New Indexes

I have a table that I need to update and the update is killing everything
else because the column I need to update appears to not be indexed. Now, I
thought I could go into the Design Table screen and add an index, but that
doesn't seem to have helped me. So, how then do I check what indexes and/or
keys exist on a given table? Thanks.
Willie
Willie,
Try:
EXEC SP_HELPINDEX YOURTABLENAME
or use Manage Indexes in Query Analyzer.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>
|||Hi,
The best way Jelly told u
if u need more then u can search like this
declare @.id varchar(20)
select @.id=id from sysobjects where name='custtable'
select * from sysindexes where id=@.id
from
Doller
|||You don't need to index the column that's being updated to improve
performance just the index the columns in the WHERE clause.
Exmaple
1 million rows or
col1 int
col2 int
UPDATE table SET col1 = 5 WHERE col2 = 9
A full table scan must be performed to locate the data, if you add an index
on col1 a table scan will still be performed because what you're searching
on is col2
you should place an index on col2. This will enable SQL server to locate the
row quickly.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>
|||Hmm... OK, so I go in and the Index does exist. Now, the column I am
updating is the same column that I am querying on. Basically, I am trying to
do a bulk update on a column and that column is the only thing that
designates it. Here is the table create script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CustomerProduct]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[CustomerProduct]
GO
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
And the Index info:
customerproduct_Site_ProductNumber nonclustered located on PRIMARY iSiteId,
chProductNumber
CustomerProduct0 nonclustered located on PRIMARY iOwnerId, iProductId
IX_CustomerProduct_Source nonclustered located on PRIMARY iSourceId
NNXCustomerProduct_iContactId nonclustered located on PRIMARY iContactId
NNXcustomerproduct_ownerid nonclustered located on PRIMARY iOwnerId
NNXcustomerproduct_prodnumber nonclustered located on PRIMARY
chProductNumber
NNXcustomerproduct_serialnum nonclustered located on PRIMARY vchSerialNumber
NNXCustomerProductInsertDate nonclustered located on PRIMARY dtInsertDate
NNXCustomerProductRecStat nonclustered located on PRIMARY tiRecordStatus
PKNUCCustomerProduct clustered, unique, primary key located on PRIMARY
iProductId, iSiteId
and finally the field I am searching/updating on is chProductNumber. Thanks
again for your help.
Willie
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:LLn1f.7212$nF6.3110@.fe04.news.easynews.com...
> You don't need to index the column that's being updated to improve
> performance just the index the columns in the WHERE clause.
> Exmaple
> 1 million rows or
> col1 int
> col2 int
> UPDATE table SET col1 = 5 WHERE col2 = 9
> A full table scan must be performed to locate the data, if you add an
> index on col1 a table scan will still be performed because what you're
> searching on is col2
> you should place an index on col2. This will enable SQL server to locate
> the row quickly.
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>

Creating New Indexes

I have a table that I need to update and the update is killing everything
else because the column I need to update appears to not be indexed. Now, I
thought I could go into the Design Table screen and add an index, but that
doesn't seem to have helped me. So, how then do I check what indexes and/or
keys exist on a given table? Thanks.
WillieWillie,
Try:
EXEC SP_HELPINDEX YOURTABLENAME
or use Manage Indexes in Query Analyzer.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hi,
The best way Jelly told u
if u need more then u can search like this
declare @.id varchar(20)
select @.id=id from sysobjects where name='custtable'
select * from sysindexes where id=@.id
from
Doller|||You don't need to index the column that's being updated to improve
performance just the index the columns in the WHERE clause.
Exmaple
1 million rows or
col1 int
col2 int
UPDATE table SET col1 = 5 WHERE col2 = 9
A full table scan must be performed to locate the data, if you add an index
on col1 a table scan will still be performed because what you're searching
on is col2
you should place an index on col2. This will enable SQL server to locate the
row quickly.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hmm... OK, so I go in and the Index does exist. Now, the column I am
updating is the same column that I am querying on. Basically, I am trying to
do a bulk update on a column and that column is the only thing that
designates it. Here is the table create script:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[CustomerProduct]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[CustomerProduct]
GO
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
And the Index info:
customerproduct_Site_ProductNumber nonclustered located on PRIMARY iSiteId,
chProductNumber
CustomerProduct0 nonclustered located on PRIMARY iOwnerId, iProductId
IX_CustomerProduct_Source nonclustered located on PRIMARY iSourceId
NNXCustomerProduct_iContactId nonclustered located on PRIMARY iContactId
NNXcustomerproduct_ownerid nonclustered located on PRIMARY iOwnerId
NNXcustomerproduct_prodnumber nonclustered located on PRIMARY
chProductNumber
NNXcustomerproduct_serialnum nonclustered located on PRIMARY vchSerialNumber
NNXCustomerProductInsertDate nonclustered located on PRIMARY dtInsertDate
NNXCustomerProductRecStat nonclustered located on PRIMARY tiRecordStatus
PKNUCCustomerProduct clustered, unique, primary key located on PRIMARY
iProductId, iSiteId
and finally the field I am searching/updating on is chProductNumber. Thanks
again for your help.
Willie
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:LLn1f.7212$nF6.3110@.fe04.news.easynews.com...
> You don't need to index the column that's being updated to improve
> performance just the index the columns in the WHERE clause.
> Exmaple
> 1 million rows or
> col1 int
> col2 int
> UPDATE table SET col1 = 5 WHERE col2 = 9
> A full table scan must be performed to locate the data, if you add an
> index on col1 a table scan will still be performed because what you're
> searching on is col2
> you should place an index on col2. This will enable SQL server to locate
> the row quickly.
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>>I have a table that I need to update and the update is killing everything
>>else because the column I need to update appears to not be indexed. Now, I
>>thought I could go into the Design Table screen and add an index, but that
>>doesn't seem to have helped me. So, how then do I check what indexes
>>and/or keys exist on a given table? Thanks.
>> Willie
>

Creating new File groups on an existing db

Out production database is around 10G and it is all in one file group
(Primary).
I would like to create new file groups and split the database into multiple
file groups for better manageablity.
I have identified the tables that can be moved into new file groups.
What is the best approach to spilt an existing production database into
multiple file groups?
Thanks,
S.KumarSuresh
If your table whose are going to be place on separate filegroup have
clustered indexes then you can easily re-create the clustered index with
specify file group .For details please refer to BOL.
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> Out production database is around 10G and it is all in one file group
> (Primary).
> I would like to create new file groups and split the database into
multiple
> file groups for better manageablity.
> I have identified the tables that can be moved into new file groups.
> What is the best approach to spilt an existing production database into
> multiple file groups?
> Thanks,
> S.Kumar
>|||You should start changing the filegroups for the specified tables, if there
aren´t many tables you could work with EM, edit table, property of the
tables/ indexes.
Jens Süßmeyer.|||Multiple files in a single filegroup allows SQL to do parallel IO on a
single query...
Using multiple filegroups allow you to.
1. Backup/Restore subsets of tables with different recovery needs.
2. Balance IO if you beleive you can do better than striping
3. Limit the space allocation which is used by a subset of tables...
The 1st is the best reason for filegroups...It does not improve
manageability, but causes you to have to watch for disk space utilization on
EACH filegroup...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corp (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:OxBFFmWbDHA.3360@.tk2msftngp13.phx.gbl...
> My aim is to split the database into multiple files.
> I could have multiple files referring to the same File Group (Primary) or
> multiple files going to multiple File groups.
> Which way is better and what are the pros and cons?
> Thanks,
> S.Kumar
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eki5qUWbDHA.1580@.tk2msftngp13.phx.gbl...
> > Suresh
> > If your table whose are going to be place on separate filegroup have
> > clustered indexes then you can easily re-create the clustered index with
> > specify file group .For details please refer to BOL.
> >
> >
> >
> > "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> > news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> > > Out production database is around 10G and it is all in one file group
> > > (Primary).
> > > I would like to create new file groups and split the database into
> > multiple
> > > file groups for better manageablity.
> > >
> > > I have identified the tables that can be moved into new file groups.
> > > What is the best approach to spilt an existing production database
into
> > > multiple file groups?
> > >
> > > Thanks,
> > > S.Kumar
> > >
> > >
> >
> >
>|||Wayne,
Did you use to work for Unisys ?
I use to know one Wayne Snyder when I worked for them in Charlotte 10 yrs
ago.
If you are the one, please send me a note to my personal email address.
Thanks,
Suresh Kumar
"Wayne Snyder" <wsnyder@.ikon.com> wrote in message
news:OyKlrSXbDHA.2672@.tk2msftngp13.phx.gbl...
> Multiple files in a single filegroup allows SQL to do parallel IO on a
> single query...
> Using multiple filegroups allow you to.
> 1. Backup/Restore subsets of tables with different recovery needs.
> 2. Balance IO if you beleive you can do better than striping
> 3. Limit the space allocation which is used by a subset of tables...
> The 1st is the best reason for filegroups...It does not improve
> manageability, but causes you to have to watch for disk space utilization
on
> EACH filegroup...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Computer Education Services Corp (CESC), Charlotte, NC
> (Please respond only to the newsgroups.)
> PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
> "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> news:OxBFFmWbDHA.3360@.tk2msftngp13.phx.gbl...
> > My aim is to split the database into multiple files.
> > I could have multiple files referring to the same File Group (Primary)
or
> > multiple files going to multiple File groups.
> >
> > Which way is better and what are the pros and cons?
> > Thanks,
> > S.Kumar
> >
> >
> > "Uri Dimant" <urid@.iscar.co.il> wrote in message
> > news:eki5qUWbDHA.1580@.tk2msftngp13.phx.gbl...
> > > Suresh
> > > If your table whose are going to be place on separate filegroup have
> > > clustered indexes then you can easily re-create the clustered index
with
> > > specify file group .For details please refer to BOL.
> > >
> > >
> > >
> > > "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> > > news:#A3i3KWbDHA.2548@.TK2MSFTNGP09.phx.gbl...
> > > > Out production database is around 10G and it is all in one file
group
> > > > (Primary).
> > > > I would like to create new file groups and split the database into
> > > multiple
> > > > file groups for better manageablity.
> > > >
> > > > I have identified the tables that can be moved into new file groups.
> > > > What is the best approach to spilt an existing production database
> into
> > > > multiple file groups?
> > > >
> > > > Thanks,
> > > > S.Kumar
> > > >
> > > >
> > >
> > >
> >
> >
>

Creating new field while app running

Hi
Is it possible to add a field to an sql server 20005 table programmatically
by a vb.net app while the vb.net app has the table being viewed on a vb.net
form?
Thanks
Regards
you can, if you use dataset for example, then your program retrieve the data
then close the connection. so there is no lock when you'll add a field.
also, adding a field is a transaction like any other transaction in the
database, so when the table become available, the field will be added
"John" <John@.nospam.infovis.co.uk> wrote in message
news:ulQbPTHoFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Hi
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table being
> viewed on a vb.net form?
> Thanks
> Regards
>
|||John,
You can, however it is probably not a good idea.
You can do that with SQL "Alter" commands and use the Adonet command
(SQLClient.SQLCommand) ExecuteNonQuery
You have crossposted to more newsgroups, did you know that the real
newsgroup for your question is
microsoft.public.dotnet.framework.adonet
I hope this helps,
Cor
|||or maybe
microsoft.public.sqlserver.programming ? ( as there are more ways to
perform this as with ADO.Net )
ofcourse it is always the est in a managed situation to let the DBA perform
these tasks , as changing columns might also add the need of performance
analyzing of the database and take apropriate actions that might affect the
database in a possitive way ( adding deleting indexes , constrains etc etc )
if your program is also responsible for the managing task ( like a user
level deployed MSDE database ) it is a good idea in my opinion to do the
following
perform the wanted tasks on a sql server ,with enterprise manager , optimize
the database, now export all your changes as SQL files
you can now import these files in your deployed sql instances either with a
shell call to the command line tools or just read them in and execute the
statements on the connection object ( ofcourse you need to log in with SA ,
administrator rights to do that , or with a pre defined user that was
granted the apropriate rights on the database to perform these actions )
regards
Michel Posseth
"Cor Ligthert [MVP]" <notmyfirstname@.planet.nl> wrote in message
news:enuiN7JoFHA.2904@.TK2MSFTNGP14.phx.gbl...
> John,
> You can, however it is probably not a good idea.
> You can do that with SQL "Alter" commands and use the Adonet command
> (SQLClient.SQLCommand) ExecuteNonQuery
> You have crossposted to more newsgroups, did you know that the real
> newsgroup for your question is
> microsoft.public.dotnet.framework.adonet
> I hope this helps,
> Cor
>
|||John (John@.nospam.infovis.co.uk) writes:
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table
> being viewed on a vb.net form?
Well, depends on what you mean with possible. But since ADO .Net is
disconnected, there are no locks on the table preventing columns to
be added by you or anyone else.
What you will happen to your VB app next time it access the table
I don't know. Since, a table is intended to be a static entity which
changes only on application upgrades, it's not that your dataset is
going to change automatically. There may be methods to add columns,
though.
Anyway, unless this is a temp table you are working with, you are
probably off-track here. Describe you real business problem, and you
may get better approaches to solve that problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Creating new field while app running

Hi
Is it possible to add a field to an sql server 20005 table programmatically
by a vb.net app while the vb.net app has the table being viewed on a vb.net
form?
Thanks
Regardsyou can, if you use dataset for example, then your program retrieve the data
then close the connection. so there is no lock when you'll add a field.
also, adding a field is a transaction like any other transaction in the
database, so when the table become available, the field will be added
"John" <John@.nospam.infovis.co.uk> wrote in message
news:ulQbPTHoFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Hi
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table being
> viewed on a vb.net form?
> Thanks
> Regards
>|||John,
You can, however it is probably not a good idea.
You can do that with SQL "Alter" commands and use the Adonet command
(SQLClient.SQLCommand) ExecuteNonQuery
You have crossposted to more newsgroups, did you know that the real
newsgroup for your question is
microsoft.public.dotnet.framework.adonet
I hope this helps,
Cor|||or maybe
microsoft.public.sqlserver.programming ' ( as there are more ways to
perform this as with ADO.Net )
ofcourse it is always the est in a managed situation to let the DBA perform
these tasks , as changing columns might also add the need of performance
analyzing of the database and take apropriate actions that might affect the
database in a possitive way ( adding deleting indexes , constrains etc etc )
if your program is also responsible for the managing task ( like a user
level deployed MSDE database ) it is a good idea in my opinion to do the
following
perform the wanted tasks on a sql server ,with enterprise manager , optimize
the database, now export all your changes as SQL files
you can now import these files in your deployed sql instances either with a
shell call to the command line tools or just read them in and execute the
statements on the connection object ( ofcourse you need to log in with SA ,
administrator rights to do that , or with a pre defined user that was
granted the apropriate rights on the database to perform these actions )
regards
Michel Posseth
"Cor Ligthert [MVP]" <notmyfirstname@.planet.nl> wrote in message
news:enuiN7JoFHA.2904@.TK2MSFTNGP14.phx.gbl...
> John,
> You can, however it is probably not a good idea.
> You can do that with SQL "Alter" commands and use the Adonet command
> (SQLClient.SQLCommand) ExecuteNonQuery
> You have crossposted to more newsgroups, did you know that the real
> newsgroup for your question is
> microsoft.public.dotnet.framework.adonet
> I hope this helps,
> Cor
>|||John (John@.nospam.infovis.co.uk) writes:
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table
> being viewed on a vb.net form?
Well, depends on what you mean with possible. But since ADO .Net is
disconnected, there are no locks on the table preventing columns to
be added by you or anyone else.
What you will happen to your VB app next time it access the table
I don't know. Since, a table is intended to be a static entity which
changes only on application upgrades, it's not that your dataset is
going to change automatically. There may be methods to add columns,
though.
Anyway, unless this is a temp table you are working with, you are
probably off-track here. Describe you real business problem, and you
may get better approaches to solve that problem.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Creating new field while app running

Hi
Is it possible to add a field to an sql server 20005 table programmatically
by a vb.net app while the vb.net app has the table being viewed on a vb.net
form?
Thanks
Regardsyou can, if you use dataset for example, then your program retrieve the data
then close the connection. so there is no lock when you'll add a field.
also, adding a field is a transaction like any other transaction in the
database, so when the table become available, the field will be added
"John" <John@.nospam.infovis.co.uk> wrote in message
news:ulQbPTHoFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Hi
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table being
> viewed on a vb.net form?
> Thanks
> Regards
>|||John,
You can, however it is probably not a good idea.
You can do that with SQL "Alter" commands and use the Adonet command
(SQLClient.SQLCommand) ExecuteNonQuery
You have crossposted to more newsgroups, did you know that the real
newsgroup for your question is
microsoft.public.dotnet.framework.adonet
I hope this helps,
Cor|||or maybe
microsoft.public.sqlserver.programming ' ( as there are more ways to
perform this as with ADO.Net )
ofcourse it is always the est in a managed situation to let the DBA perform
these tasks , as changing columns might also add the need of performance
analyzing of the database and take apropriate actions that might affect the
database in a possitive way ( adding deleting indexes , constrains etc etc )
if your program is also responsible for the managing task ( like a user
level deployed MSDE database ) it is a good idea in my opinion to do the
following
perform the wanted tasks on a sql server ,with enterprise manager , optimize
the database, now export all your changes as SQL files
you can now import these files in your deployed sql instances either with a
shell call to the command line tools or just read them in and execute the
statements on the connection object ( ofcourse you need to log in with SA ,
administrator rights to do that , or with a pre defined user that was
granted the apropriate rights on the database to perform these actions )
regards
Michel Posseth
"Cor Ligthert [MVP]" <notmyfirstname@.planet.nl> wrote in message
news:enuiN7JoFHA.2904@.TK2MSFTNGP14.phx.gbl...
> John,
> You can, however it is probably not a good idea.
> You can do that with SQL "Alter" commands and use the Adonet command
> (SQLClient.SQLCommand) ExecuteNonQuery
> You have crossposted to more newsgroups, did you know that the real
> newsgroup for your question is
> microsoft.public.dotnet.framework.adonet
> I hope this helps,
> Cor
>|||John (John@.nospam.infovis.co.uk) writes:
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table
> being viewed on a vb.net form?
Well, depends on what you mean with possible. But since ADO .Net is
disconnected, there are no locks on the table preventing columns to
be added by you or anyone else.
What you will happen to your VB app next time it access the table
I don't know. Since, a table is intended to be a static entity which
changes only on application upgrades, it's not that your dataset is
going to change automatically. There may be methods to add columns,
though.
Anyway, unless this is a temp table you are working with, you are
probably off-track here. Describe you real business problem, and you
may get better approaches to solve that problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating New Empty Database

Hello, I have a production database that I need to recreate somewhere else
but with no data in the tables. Is the best way to do this, simply using the
'Generate SQL Script' option though Enterprise Manager against the existing
database. Are there any objects that will not get created?
One thing I have come across with scripting so far is that I have views
based on views. So I'm having to manually change the order of views in my
script to have the ones not based on views at the top. Is there a easier way
to do this?
Thanks
Don
Hope the server is 2000 itself, the best way is by generate script only. You
will get all the objects asscoiated with one DB when using generate script.
Again the question regarding views, after generating the script reorder the
views according to your need. While sciprting it will get generated in the
order in which each object was created, so there wont be much work for you.
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Don" wrote:

> Hello, I have a production database that I need to recreate somewhere else
> but with no data in the tables. Is the best way to do this, simply using the
> 'Generate SQL Script' option though Enterprise Manager against the existing
> database. Are there any objects that will not get created?
> One thing I have come across with scripting so far is that I have views
> based on views. So I'm having to manually change the order of views in my
> script to have the ones not based on views at the top. Is there a easier way
> to do this?
> Thanks
> Don

Creating New Empty Database

Hello, I have a production database that I need to recreate somewhere else
but with no data in the tables. Is the best way to do this, simply using the
'Generate SQL Script' option though Enterprise Manager against the existing
database. Are there any objects that will not get created?
One thing I have come across with scripting so far is that I have views
based on views. So I'm having to manually change the order of views in my
script to have the ones not based on views at the top. Is there a easier way
to do this?
Thanks
DonHope the server is 2000 itself, the best way is by generate script only. You
will get all the objects asscoiated with one DB when using generate script.
Again the question regarding views, after generating the script reorder the
views according to your need. While sciprting it will get generated in the
order in which each object was created, so there wont be much work for you.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Don" wrote:
> Hello, I have a production database that I need to recreate somewhere else
> but with no data in the tables. Is the best way to do this, simply using the
> 'Generate SQL Script' option though Enterprise Manager against the existing
> database. Are there any objects that will not get created?
> One thing I have come across with scripting so far is that I have views
> based on views. So I'm having to manually change the order of views in my
> script to have the ones not based on views at the top. Is there a easier way
> to do this?
> Thanks
> Don

Creating New Empty Database

Hello, I have a production database that I need to recreate somewhere else
but with no data in the tables. Is the best way to do this, simply using th
e
'Generate SQL Script' option though Enterprise Manager against the existing
database. Are there any objects that will not get created?
One thing I have come across with scripting so far is that I have views
based on views. So I'm having to manually change the order of views in my
script to have the ones not based on views at the top. Is there a easier wa
y
to do this?
Thanks
DonHope the server is 2000 itself, the best way is by generate script only. You
will get all the objects asscoiated with one DB when using generate script.
Again the question regarding views, after generating the script reorder the
views according to your need. While sciprting it will get generated in the
order in which each object was created, so there wont be much work for you.
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Don" wrote:

> Hello, I have a production database that I need to recreate somewhere else
> but with no data in the tables. Is the best way to do this, simply using
the
> 'Generate SQL Script' option though Enterprise Manager against the existin
g
> database. Are there any objects that will not get created?
> One thing I have come across with scripting so far is that I have views
> based on views. So I'm having to manually change the order of views in my
> script to have the ones not based on views at the top. Is there a easier
way
> to do this?
> Thanks
> Don

Creating New DB using SMO

Hi,
I am trying to create a new database using SMO, I am getting Set_Parent
Failure Error when try to create a new database.
This is the code I am using to create the database.

Database database= new Database(DbServer, "MyNewDB");
database.Create();
I know that if you just use the first line it will try to attach the database, and u will get set_parent failure error, but I am using .Create() also.... still I am getting the error.... can anyone help me in this ?
Thanks
Rujith

If above is the code you are using, I would think you would get a compile error. The Database Constructor overload that takes 2 parameters is the following:

Database(Server server, string name);

where you must give it a server object where the new database will reside.

Here is some sample code that works:

Server server = new Server(".");
Database db = new Database(server,"MyDatabaseName");
db.Create();

I have a few SMO tutorials that I have been slowly writing that may be of help:

SQL Server Management Objects (SMO) Tutorials

Regards,

Dave

|||

Dave,

Sorry for the wrong code, I removed the first line(creating server), while putting in the forum. Thats the reason for the syntax error. In my actual code, I am creating server first, then calling the database constructor.

Did you ever get the Set_parent failure error while working with SMO?

-Rujith

|||

You can create that error a couple of ways:

Database db = new Database(null,"MyDatabaseName");
db.Create();

or

Database db = new Database();
db.Parent = null;

Either way, if you pass in null for the server (parent) you will get the error:

SetParent failed for Database 'MyDatabaseName'.

Change your code to see if server is null before passing it into the constructor.

Regards,

Dave

Creating new connection manager types

Hi,
Wanted to know how one can write new connection manager types, and, additionally, if there exist any code samples that show how this can be done. I searched through BOL but could not locate any.
I basically work with C++ and was curiuos if code samples exist in it? I have worked with a couple of ETL tools (or 'platforms' should I say) before, but never with SSIS.
Would really appreciate it if someone could help me out here.We're working on providing some samples for custom connection managers. Writing them in native code isn't supported this release, but in managed it is. The pattern is the same as for custom tasks. Derive from the base connection manager type in the Microsoft.SQLServer.ManagedRuntime assembly and override AcquireConnection.
This will get you the super simple connection manager.|||is there any update on this one? a sample would be rather nice?

Creating new connection manager types

Hi,
Wanted to know how one can write new connection manager types, and, additionally, if there exist any code samples that show how this can be done. I searched through BOL but could not locate any.
I basically work with C++ and was curiuos if code samples exist in it? I have worked with a couple of ETL tools (or 'platforms' should I say) before, but never with SSIS.
Would really appreciate it if someone could help me out here.We're working on providing some samples for custom connection managers. Writing them in native code isn't supported this release, but in managed it is. The pattern is the same as for custom tasks. Derive from the base connection manager type in the Microsoft.SQLServer.ManagedRuntime assembly and override AcquireConnection.
This will get you the super simple connection manager.|||is there any update on this one? a sample would be rather nice?

Creating new backup device

I need to create a new backup device. I have installed a
USB drive, and would like to backup a database to it.
However, SQL Server can only see one of the drives
available on the machine when I try to create the new
device. How can I make a new backup device to a different
drive letter?
Can you try creating a dump device using sp_addumpdevice from Query Analyzer
and see if that solves the problem? See SQL Server Books Online for more
information.--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"onemoreshot" <anonymous@.discussions.microsoft.com> wrote in message
news:7fa201c484ae$4c09e7b0$a401280a@.phx.gbl...
I need to create a new backup device. I have installed a
USB drive, and would like to backup a database to it.
However, SQL Server can only see one of the drives
available on the machine when I try to create the new
device. How can I make a new backup device to a different
drive letter?
|||In other words, it can't be done through Enterprise
Manager? Thanks .

>--Original Message--
>Can you try creating a dump device using sp_addumpdevice
from Query Analyzer
>and see if that solves the problem? See SQL Server Books
Online for more
>information.--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"onemoreshot" <anonymous@.discussions.microsoft.com> wrote
in message
>news:7fa201c484ae$4c09e7b0$a401280a@.phx.gbl...
>I need to create a new backup device. I have installed a
>USB drive, and would like to backup a database to it.
>However, SQL Server can only see one of the drives
>available on the machine when I try to create the new
>device. How can I make a new backup device to a different
>drive letter?
>
>.
>
|||If it is a proper drive, then you should be able to do it in EM also. I am
just trying to narrow down the problem, by trying this in Query Analyzer.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
<anonymous@.discussions.microsoft.com> wrote in message
news:8be201c48592$81ec8f60$a401280a@.phx.gbl...
In other words, it can't be done through Enterprise
Manager? Thanks .

>--Original Message--
>Can you try creating a dump device using sp_addumpdevice
from Query Analyzer
>and see if that solves the problem? See SQL Server Books
Online for more
>information.--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"onemoreshot" <anonymous@.discussions.microsoft.com> wrote
in message
>news:7fa201c484ae$4c09e7b0$a401280a@.phx.gbl...
>I need to create a new backup device. I have installed a
>USB drive, and would like to backup a database to it.
>However, SQL Server can only see one of the drives
>available on the machine when I try to create the new
>device. How can I make a new backup device to a different
>drive letter?
>
>.
>