Need help creating a temporary table in MS SQL Server.

Hello,

I am working on a webapp using VB.net

Right now I am writing to a sql table during a process where the end userstarts entering the contents for a file that is going to be generated once hefinishes entering the data, but the problem is that if more than one user isdoing the same process the data would get mixed up. To avoid this Ithought in creating a temporary table (its name will consist of a stringand the current date time).

I would like to see any tutorialabout creating and working with temp tables. Or if you have anysuggestions, I will appreciate them. Thanks

There are two types of temp tables in SQL Server # local temp table scope very limted but also the most used because it uses less resources and SQL Server may clean up if you forget it, the other ## global temp table longer scope but you must drop it explicitly or it can harm your application. Try the link below for some tricks of how to use the temp tables, I am assuming you know they are created in the Temp Db. Hope this helps.

http://www.awprofessional.com/articles/article.asp?p=25288&seqNum=4&rl=1

Need Help creating a SSIS Package (Newbie)

Following is what I would like to do, so I can keep updating my central SQL Server database with latest updates from the field. I like to use SSIS 2005 to create a package that could do this. Any help to get me started would be appreciated. I need some help soon, pls give me something to get started. APpreciate it. Thanks.

Open connection and read client location table on the local SQL Server database called PODO
For each location id in the table do the following:
Store locationid/clientid in a variable called CLLOC_ID
Construct file name with mdb extension and store in a variable MDB_FILE
Establish connection to the data import folder
Search for that MDB_FILE in the folder on the file system
If there is a file where match = true then do this:
1) Open the access database
2) Read and import the data from the customer experience table
3) Write that data to the SQL Server tables where location = CLLOC_ID
4) Exit process
IF there is no match, exit process
Keep looping until all the client ids/loc ids are read from the SQL Server client location table.

MA

SSIS won't "search" for files as such. You have to tell it where the files are. If you have a collection of .mdb files in a folder then you could loop over them using the ForEach loop.

Import data from Access can be done in a data-flow

There are a number of ways of discovering if the incoming data has a matchinng PK in the destination (which I think is what you are trying to do). Have a look at the LOOKUP component.

-Jamie

|||

Jamie:

Thanks for the advice. I would like to loop thru a SQL Server table and build file names for the mdb files , and then use those file names to match with what i have in the folder. For each file i construct, i would like to establish a connection to that file, and import data. How would i implement this functionality?

MA

|||

Here's how: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

-Jamie

|||

Hi Jamie:

I went ahead and added the ForEach Loop container, and would like to see if that is the right starting point for what I am trying to achieve. Can you give me like a step by step, on how to do this, since I am having a real tough time figuring out how to do things like build a filename from the data retrieved from the SQL execute task. Also then how do i pass that file name further down the process, and how do i create something that would take that built filename and compare it with the file names found in the actual folder that i am connecting to on my local hard drive. I have setup two connections using the connection manager, one is for the SQL Server, while the other one is right now pointing to one dummy access file in the folder. I would need to somehow make the connection manager dynamically look for filenames, and once a match is found, import data. The import data part is pretty straight forward, but i havent yet gotten there. The bigger issue is that i need to build a file name list on the fly by reading thru a sql server table, and then comparing the filenames with what i have in the folder on the local drive. If a match exisits, then i would like to continue with a data import process. Since I am extremely new to SSIS, and I am not a DBA/Database developer, i am more of an application developer on the front end, can you please give me a quick step by step directions. This will help me get started, and if i can figure out things like how to pass output from one sql statement to another task, and how to do comparisons within SSIS, and then how to dynamically read thru files with those variables, this will help in the future, as I will be building more of these type or ETL processes with much more advanced setup. I appreciate your time, and so far I really appreciate your help. Thanks and I look forward to your reply, and I know I am asking too much quesitons, but this is a real project, and I am up against a deadline. Thank you.

MA

sql

Need help combining views; DDL included

I've solved this issue by creating 3 views but I'd rather do it in 1 SELECT
if possible.
Given my data I want to select duplicate securities based on the cusip field
in the Securities table where the cusip does not exist in the Positions
table. To rephrase I want duplicate securities that are not held.
Given my sample data I want to return one record with the cusip value 'E'
Thanks to anyone who could help.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Positions]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Securities]
GO
CREATE TABLE [dbo].[Positions] (
[AccountID] [int] NOT NULL ,
[SecurityID] [int] NOT NULL ,
[Quantity] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Securities] (
[SecurityID] [int] NOT NULL ,
[CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
(
[AccountID],
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
(
[SecurityID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Positions] ADD
CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
(
[SecurityID]
) REFERENCES [dbo].[Securities] (
[SecurityID]
)
GO
INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)Try This:
Select CusIP, Count(*)
From Securities
Where SecurityID Not In
(Select SecurityID
From Positions)
Group By CusIP
Having Count(*) > 1
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||What sulld happen in case that you add this to your sample data:
INSERT INTO Securities (SecurityID,CUSIP) VALUES (8,'E')
INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,8,10)
Would "E" still satisfy your request?
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||Anyway,
If your asnwer is Yes:
Solution 1:
select
sec.CUSIP
from Positions pos right outer join Securities sec
on pos.SecurityID=sec.SecurityID
where pos.AccountID is null
group by sec.CUSIP
having count(sec.CUSIP)>1
Solution 2:
select
sec.CUSIP
from Securities sec
where sec.SecurityID not in
(
select pos.SecurityID from Positions pos
)
group by sec.CUSIP
having count(sec.CUSIP)>1
If your answer is NO:
select CUSIP
from Securities
where CUSIP not in
(
select sec.CUSIP
from Positions pos join Securities sec
on pos.SecurityID=sec.SecurityID
)
group by CUSIP
having count(CUSIP)>1
"Terri" wrote:

> I've solved this issue by creating 3 views but I'd rather do it in 1 SELEC
T
> if possible.
> Given my data I want to select duplicate securities based on the cusip fie
ld
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>
>|||Terri
See Itzik Ben-Gan's script about duplicates
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Terri" <terri@.cybernets.com> wrote in message
news:d3f1h9$ocg$1@.reader2.nmix.net...
> I've solved this issue by creating 3 views but I'd rather do it in 1
SELECT
> if possible.
> Given my data I want to select duplicate securities based on the cusip
field
> in the Securities table where the cusip does not exist in the Positions
> table. To rephrase I want duplicate securities that are not held.
> Given my sample data I want to return one record with the cusip value 'E'
> Thanks to anyone who could help.
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_Positions_Securities]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[Positions] DROP CONSTRAINT FK_Positions_Securities
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Positions]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Positions]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Securities]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
> drop table [dbo].[Securities]
> GO
> CREATE TABLE [dbo].[Positions] (
> [AccountID] [int] NOT NULL ,
> [SecurityID] [int] NOT NULL ,
> [Quantity] [int] NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Securities] (
> [SecurityID] [int] NOT NULL ,
> [CUSIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] WITH NOCHECK ADD
> CONSTRAINT [PK_Positions] PRIMARY KEY CLUSTERED
> (
> [AccountID],
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Securities] WITH NOCHECK ADD
> CONSTRAINT [PK_Securities] PRIMARY KEY CLUSTERED
> (
> [SecurityID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Positions] ADD
> CONSTRAINT [FK_Positions_Securities] FOREIGN KEY
> (
> [SecurityID]
> ) REFERENCES [dbo].[Securities] (
> [SecurityID]
> )
> GO
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (1,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (2,'A')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (3,'B')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (4,'C')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (5,'D')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (6,'E')
> INSERT INTO Securities (SecurityID,CUSIP) VALUES (7,'E')
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,1,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,2,15)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,1,20)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (5,4,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,3,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (3,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,2,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (1,5,10)
> INSERT INTO Positions (AccountID,SecurityID,Quantity ) VALUES (4,5,10)
>

Need Help about Initial Snapshot

i have created a publication whereas i have provided a network path to its snapshop folder e.g ( \\server\folder ) at time of creating. When i try to make a Pull Subscription and follow all steps of wizards, it gives me following error "The initial snapshot for publication '' is not yet available". can you guide me what are causes of this problem and how may i solve it?

Thanks for help.
Regards,

After you create the publication, you need to start the snapshot agent. The snapshot agent is what creates the files available for subscriptions to use.|||

Dear Greg,

I did the same but when i try to pull the subscription it gives me the error that it can not take the initial snapshot. I also prefer to get snapshots from FTP but it never works. Please help me if you can.

Lookng forward to your reply.

Regards,

|||

Is this merge replication? If so, is it dynamically filtered, which may require a dynamic snapshot to be run?

If you want to use FTP, follow the steps for Configuring a Publication to Allow Subscribers to Retrieve Snapshot using FTP.

|||

Dear Greg,

I am facing the same problem, I am using Merge Replication. After creation of publication when i m trying to connect the server, it gives error of the initial snapshot is not yet available.

|||Just ran into this myself. Once you have everything good, make sure you run the distribution agent. This might fix it. If not, bring up the replication monitor, expand the publications on the left, right-click on the "trouble" server on the right, and check "mark for reinitialization". Then it'll grab the snapshot. You might also need to start the snapshot agent - expand "replication->local publications", right-click on the publication, and select "monitor snapshot agent". Once it comes up, click start.sql

Need Help about Initial Snapshot

i have created a publication whereas i have provided a network path to its snapshop folder e.g ( \\server\folder ) at time of creating. When i try to make a Pull Subscription and follow all steps of wizards, it gives me following error "The initial snapshot for publication '' is not yet available". can you guide me what are causes of this problem and how may i solve it?

Thanks for help.
Regards,

After you create the publication, you need to start the snapshot agent. The snapshot agent is what creates the files available for subscriptions to use.|||

Dear Greg,

I did the same but when i try to pull the subscription it gives me the error that it can not take the initial snapshot. I also prefer to get snapshots from FTP but it never works. Please help me if you can.

Lookng forward to your reply.

Regards,

|||

Is this merge replication? If so, is it dynamically filtered, which may require a dynamic snapshot to be run?

If you want to use FTP, follow the steps for Configuring a Publication to Allow Subscribers to Retrieve Snapshot using FTP.

|||

Dear Greg,

I am facing the same problem, I am using Merge Replication. After creation of publication when i m trying to connect the server, it gives error of the initial snapshot is not yet available.

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topic Creating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need Assistance: Trying to run jobs and getting nowhere/SQLServerAgent

I've gone through hundreds of posts here and on the Net. I'm creating an ssis package to access my db on my desktop. I then save a copy as "Test" on the msdb database on our sql 2005 box.

Try to create a job and I'm getting nowhere. My SQL Server Agent account is a member of local Administrator group and a member of the domain.

Now what I noticed is if I run the dtsexec from the command prompt and use the command line string then it works. Just seems like I'm not able to run anything from the SQL Server>>Jobs interface.

Please Help :)

What errors do you get? Have you seen http://support.microsoft.com/kb/918760?|||

I have it working with the set password but I'd like not to do this in case other developers create packages and then forget. I'd like to use the below method but how do you do this --> "SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.
"

Method 1: Use a SQL Server Agent proxy account

Create a SQL Server Agent proxy account. This proxy account must use a credential that lets SQL Server Agent run the job as the account that created the package or as an account that has the required permissions.

This method works to decrypt secrets and satisfies the key requirements by user. However, this method may have limited success because the SSIS package user keys involve the current user and the current computer. Therefore, if you move the package to another computer, this method may still fail, even if the job step uses the correct proxy account.

|||Books online have topicCreating SQL Server Agent Proxies under Administering the Database Engine > Automating Administrative Tasks (SQL Server Agent) > Implementing SQL Server Agent Security >

Need assistance on conditional update Trigger

I need some help here in creating a conditional update trigger. The purpose of this trigger would check to see if a contact already exist in the database on an insert and update only the fields that are null.

So How would I compare each field from the CONTACTS Table against my INSERTED Table?

Inserted.FirstName (COMPARE) Contacts.Firstname

Inserted.LastName (COMPARE) Contacts.LastName

Inserted.Email (COMPARE) Contacts.Email

I will be using the email address as the check for the duplicate record and if a duplicate is found... Instead of not allowing the insert I want to compare the existing record and update any fields that are NULL in Contacts with Inserted.

I have no idea on how to compare all of the fields.

Any help appreciated.

sadler_david@.yahoo.com

You could do the following in an INSTEAD OF trigger:

create trigger merge_contact

instead of insert

on dbo.Contacts

as

begin

update dbo.Contacts

set FirstName = coalesce(FirstName, i.FirstName),

LastName = coalesce(LastName, i.LastName)

from inserted as i

where i.Email = dbo.Contacts.Email

insert into dbo.Contacts (FirstName, LastName, Email)

select i.FirstName, i.LastName, i.Email

from inserted as i

where not exists(select * from dbo.Contacts as c

where c.Email = i.Email)

end

Ideally, you need to perform the operations in serializable transaction isolation level to avoid duplicate inserts.

Need Assistance Creating a stored procedure

Hi,

I'm trying to work around a bug that our helpdesk software has. When a new
issue is created, it cannot automatically default 2 fields to the value of
No like we need it to.

I have a field called "Audited" and one called "Billed to Client". When a
new issue is openned, it just leaves the value as Null in the database
instead of a value of No.

I would like to create a stored procedure and schedule it to run every 10
minutes to change any value of Null in those columns to No.

Database: bridgetrak
Table: Issues
Column: Audited
Column: Billed

If someone could help me out that would be great! I just don't have very
much experience with SQL statements.

Please email me at shawnf@.sccnet.com

Thanks,
ShawnHi Shawn,

A much better way would be to set up default values of "No" for those
columns and disallow nulls. You can do this through Enterprise manager or
through an Alter Table Query. A stored procedure for this situation is
unnecessary and highly unadvisable.

Regards,

Tyler
"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> Hi,
> I'm trying to work around a bug that our helpdesk software has. When a
new
> issue is created, it cannot automatically default 2 fields to the value of
> No like we need it to.
> I have a field called "Audited" and one called "Billed to Client". When a
> new issue is openned, it just leaves the value as Null in the database
> instead of a value of No.
> I would like to create a stored procedure and schedule it to run every 10
> minutes to change any value of Null in those columns to No.
> Database: bridgetrak
> Table: Issues
> Column: Audited
> Column: Billed
> If someone could help me out that would be great! I just don't have very
> much experience with SQL statements.
> Please email me at shawnf@.sccnet.com
> Thanks,
> Shawn|||Thank you for your help Tyler,

I tried your suggestion, however after doing that, the helpdesk software
would not save a new issue so I had to change it back to allow nulls and
undo the default value of No. That would have worked great if the program
didn't suck.

Any other suggestions? The only work around I can think of is the stored
procedure. It's much better then currently connecting with Access and doing
a Search and replace.

Thanks,
Shawn

"Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
news:cd6h0a$7i4$1@.news.datasync.com...
> Hi Shawn,
> A much better way would be to set up default values of "No" for those
> columns and disallow nulls. You can do this through Enterprise manager or
> through an Alter Table Query. A stored procedure for this situation is
> unnecessary and highly unadvisable.
>
> Regards,
> Tyler
> "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > Hi,
> > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > issue is created, it cannot automatically default 2 fields to the value
of
> > No like we need it to.
> > I have a field called "Audited" and one called "Billed to Client". When
a
> > new issue is openned, it just leaves the value as Null in the database
> > instead of a value of No.
> > I would like to create a stored procedure and schedule it to run every
10
> > minutes to change any value of Null in those columns to No.
> > Database: bridgetrak
> > Table: Issues
> > Column: Audited
> > Column: Billed
> > If someone could help me out that would be great! I just don't have
very
> > much experience with SQL statements.
> > Please email me at shawnf@.sccnet.com
> > Thanks,
> > Shawn|||It sounds as though the helpdesk software is specifying insert values of
Null for those columns. If you can edit the helpdesk software, I would go
that route. If not, try using a trigger.

CREATE TRIGGER Issues_INSUPD
ON Issues
FOR INSERT, UPDATE
AS

UPDATE Issues
SET Audited = 'No'
WHERE
Audited IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

UPDATE Issues
SET Billed = 'No'
WHERE
Billed IS NULL AND
<keyfieldgoeshere> IN (SELECT <keyfieldgoeshere> FROM INSERTED)

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
news:40f8031e$0$63722$8f4e7992@.newsreader.goldenga te.net...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and
doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for
those
> > columns and disallow nulls. You can do this through Enterprise manager
or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When
a
> > new
> > > issue is created, it cannot automatically default 2 fields to the
value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client".
When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >|||Shawn,

Try this:

create table Issues(Audited char(3), Billed char(3))
go

CREATE TRIGGER TRIGG1 ON Issues
INSTEAD OF INSERT
AS BEGIN
INSERT Issues
SELECT IsNull(Audited, 'No'), IsNull(Billed, 'No')
FROM inserted
END
go

insert Issues values(null, null)
select * from Issues

Shervin

"Shawn Fletcher" <shawnf@.sccnet.com> wrote in message news:<40f8031e$0$63722$8f4e7992@.newsreader.goldengate.ne t>...
> Thank you for your help Tyler,
> I tried your suggestion, however after doing that, the helpdesk software
> would not save a new issue so I had to change it back to allow nulls and
> undo the default value of No. That would have worked great if the program
> didn't suck.
> Any other suggestions? The only work around I can think of is the stored
> procedure. It's much better then currently connecting with Access and doing
> a Search and replace.
> Thanks,
> Shawn
>
> "Tyler Hudson" <TylerH@.Spam.MeNOTallpax.com> wrote in message
> news:cd6h0a$7i4$1@.news.datasync.com...
> > Hi Shawn,
> > A much better way would be to set up default values of "No" for those
> > columns and disallow nulls. You can do this through Enterprise manager or
> > through an Alter Table Query. A stored procedure for this situation is
> > unnecessary and highly unadvisable.
> > Regards,
> > Tyler
> > "Shawn Fletcher" <shawnf@.sccnet.com> wrote in message
> > news:40f6a897$0$49110$8f4e7992@.newsreader.goldenga te.net...
> > > Hi,
> > > > I'm trying to work around a bug that our helpdesk software has. When a
> new
> > > issue is created, it cannot automatically default 2 fields to the value
> of
> > > No like we need it to.
> > > > I have a field called "Audited" and one called "Billed to Client". When
> a
> > > new issue is openned, it just leaves the value as Null in the database
> > > instead of a value of No.
> > > > I would like to create a stored procedure and schedule it to run every
> 10
> > > minutes to change any value of Null in those columns to No.
> > > > Database: bridgetrak
> > > Table: Issues
> > > Column: Audited
> > > Column: Billed
> > > > If someone could help me out that would be great! I just don't have
> very
> > > much experience with SQL statements.
> > > > Please email me at shawnf@.sccnet.com
> > > > Thanks,
> > > Shawn
> >

Need Advice. Blog

Hello,

I am creating a simple blog system using SQL 2005.

I have a Blog table:
[BlogId] > PostId (PK), BlogTitle, ...

And a Posts table
[Posts] > PostId (PK), BlogId (FK), PostContent, PostLabels, ...

PostLabels would have the following format:
Label1,Label2,Label3, etc ...

I will need to perform 3 actions:
1. Get all posts in blog
2. Get all labels in a post
3. Get all unique existing labels in all posts in a blog and make a list.

I am not sure if my approach of using a simple labels column in my Posts table is a good idea.

So my other idea would be to add two more tables:
[BlogLabels] > BlogLabelId (PK), BlogId (FK), LabelName ...

[LabelsInPosts] > BlogLabelId (PK), PostId (PK)

So my idea is:

1. When creating a post one of the parameters would be a comma
separating string with all labels for the post.
Inside SQL Procedure I will need to loop through each label and
check if it exists in BlogLabels. If not then I added it.
For each label I add a records in LabelsInPosts.

How to create this loop? Am I thinking this right?

2. To get a list of all labels in a blog I would need to go to
BlogLabels and get all labels which are related with posts in
LabelsInPosts. Those posts must be only the ones that are related
with my given BlogId.

Grrr, this is getting really confusing for me.
Is this possible to to? How?

Please, give me some advice about all this.

Thanks,
MiguelThe second approach is definitely more scalable and is the standard way to go. You will be able to obtain all the labels with a single select.

Need advice on db maintenance plan

Hi:
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoon
What version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegr oups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>
|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon
|||Prior to 2005, a database backup will block a log backup. So you can have your log backups scheduled
if you wish, the log backup job will just sit a wait until the database has been performed. Also,
Agent will not start a job if it is already running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegr oups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>
|||Tibor Karaszi
Thanks.

>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon
|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.

> will the log backup block the full database
> backup ?
Yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegr oups.com...
> Tibor Karaszi
> Thanks.
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>
|||Thanks
JCVoon

Need advice on db maintenance plan

Hi:
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoonWhat version of SQL Server?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegroups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon|||Prior to 2005, a database backup will block a log backup. So you can have yo
ur log backups scheduled
if you wish, the log backup job will just sit a wait until the database has
been performed. Also,
Agent will not start a job if it is already running.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>|||Tibor Karaszi
Thanks.

>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.

> will the log backup block the full database
> backup ?
Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi
> Thanks.
>
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>|||Thanks
JCVoon

Need advice on db maintenance plan

Hi:
I need advice on creating the maintenance plan.
12:00 AM ~ 1:59 AM DTS / Archive Job
02:00 AM ~ 2:59 AM Full DB Backup
03:00 AM ~ 3:59 AM DB Integrity Check
04:00 AM ~ 5:59 AM Defrag Index
04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
Can the trx log backup schedule to start from today 4:00 AM until next
day 1:59 AM ? or i need to create two scheduler job to backup the trx
log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
11:59:59PM ?
I schedule the log backup to start from 4:00 AM is because i need the
trx log backup to truncate the log file during defrag index (04:00 AM ~
5:59 AM).
I need to backup the trx log during the DTS/Archive Job (12:00 AM to
1:59 AM) before start the full db backup.
If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
what will happen at 2:00AM when the full db backup start ? will it run
the trx log backup first or the full db backup first ? will the
scheduler queue the request or it just simply ignore one of them ?
If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
and integrity check), what will happen if the db crash after the the
full db backup completed (after 2:59 AM), i only able to recover the db
up to 2:00AM ?
Please help.
Thanks
JCVoonWhat version of SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143183567.147117.216070@.e56g2000cwe.googlegroups.com...
> Hi:
> I need advice on creating the maintenance plan.
> 12:00 AM ~ 1:59 AM DTS / Archive Job
> 02:00 AM ~ 2:59 AM Full DB Backup
> 03:00 AM ~ 3:59 AM DB Integrity Check
> 04:00 AM ~ 5:59 AM Defrag Index
> 04:00 AM ~ 1:59 AM Transaction Log Backup (every hour)
> Can the trx log backup schedule to start from today 4:00 AM until next
> day 1:59 AM ? or i need to create two scheduler job to backup the trx
> log one from 4:00AM to 11:59:59 PM and another one from 12:00 AM to
> 1:59:59 AM ? or i need to schedule the trx log backup from 12:00AM to
> 11:59:59PM ?
> I schedule the log backup to start from 4:00 AM is because i need the
> trx log backup to truncate the log file during defrag index (04:00 AM ~
> 5:59 AM).
> I need to backup the trx log during the DTS/Archive Job (12:00 AM to
> 1:59 AM) before start the full db backup.
> If i schedule the trx log backup from 12:00AM to 11:59:59PM (24Hours),
> what will happen at 2:00AM when the full db backup start ? will it run
> the trx log backup first or the full db backup first ? will the
> scheduler queue the request or it just simply ignore one of them ?
> If the trx log backup does not cover 2:00 AM to 3:59 PM (full db backup
> and integrity check), what will happen if the db crash after the the
> full db backup completed (after 2:59 AM), i only able to recover the db
> up to 2:00AM ?
>
> Please help.
> Thanks
> JCVoon
>|||Tibor Karaszi:
Oops...sorry, forgot to mention. It is SQL Server 2000 ent
Regards
JCVoon|||Prior to 2005, a database backup will block a log backup. So you can have your log backups scheduled
if you wish, the log backup job will just sit a wait until the database has been performed. Also,
Agent will not start a job if it is already running.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143188648.197598.226630@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi:
> Oops...sorry, forgot to mention. It is SQL Server 2000 ent
> Regards
> JCVoon
>|||Tibor Karaszi
Thanks.
>Prior to 2005, a database backup will block a log backup.
I'm using SQL 2000, block the log backup means the log backup will be
abort and generate error ? will the log backup block the full database
backup ?
Regards
JCVoon|||> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ?
No, it will wait until the database backup has finished.
> will the log backup block the full database
> backup ?
Yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1143253350.681426.250150@.t31g2000cwb.googlegroups.com...
> Tibor Karaszi
> Thanks.
>>Prior to 2005, a database backup will block a log backup.
> I'm using SQL 2000, block the log backup means the log backup will be
> abort and generate error ? will the log backup block the full database
> backup ?
> Regards
> JCVoon
>|||Thanks
JCVoon

Need a Suggestion for creating Tables ?

One of my Customer deals in different Items, and every Item has different
specifications and types.
For example:
Item A has different categories, types, colours
Item B has different colours, sizes, thickness, weight
Item C has different colours, qualities, yarn counts, widths, types, weave,
design type
Now how can I create my Product Table(s) which should accomodate all above.
How many tables I have to create, is it possible that I create one or two
tables and use self aliasing, if possible, how ?
One easy way is to create different tables for all the required
specifications, but what, if customer deals with 100s of specifications all
over, and another customer whom I sell this product, deals with another 100
specifications which are totally different than the last customer ?
Please give me your best solutions so that I can easily use these tables in
my Inventory Application, without changing the design again and again.
I hope you understand what I am trying to say ?
I am developing my Application in VB.Net 2005.
Best Regards,
LuqmanSee if this helps. Read about "Entity Supertypes and Subtypes".
http://72.14.203.104/search?q=cache...s&ct=clnk&cd=17
AMB
"Luqman" wrote:

> One of my Customer deals in different Items, and every Item has different
> specifications and types.
> For example:
> Item A has different categories, types, colours
> Item B has different colours, sizes, thickness, weight
> Item C has different colours, qualities, yarn counts, widths, types, weave
,
> design type
> Now how can I create my Product Table(s) which should accomodate all above
.
> How many tables I have to create, is it possible that I create one or two
> tables and use self aliasing, if possible, how ?
> One easy way is to create different tables for all the required
> specifications, but what, if customer deals with 100s of specifications al
l
> over, and another customer whom I sell this product, deals with another 10
0
> specifications which are totally different than the last customer ?
> Please give me your best solutions so that I can easily use these tables i
n
> my Inventory Application, without changing the design again and again.
> I hope you understand what I am trying to say ?
> I am developing my Application in VB.Net 2005.
>
> Best Regards,
> Luqman
>
>|||Any practical example of table(s) and queries will be helpful.
Best Regards,
Luqman
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:7DF777AC-4DE8-4BE0-BB98-946BB6FDC3B5@.microsoft.com...
> See if this helps. Read about "Entity Supertypes and Subtypes".
> http://72.14.203.104/search?q=cache...s&ct=clnk&cd=17
>
> AMB
> "Luqman" wrote:
>

Need a little help

Is there a way to include either a checkbox or a yes/no field in SQL? Here is why I ask. I have a small db that I am creating that will house data about cars. I want to display two of these cars based off of a user selected field in SQL, or on a webform. There will be about a hundred or so cars that will be available, but I only want the ones that the user selects to show on my page. What is the best way to accomplish this?

DUUUUHHHHH.... how about adding a bit field to the db and a checkbox field to the Grid View!!

|||YesNo fields in Sql Server are bit datatypes - 1 or 0. The value can be read as 1 or 0, true or false.