Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciate
Pooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best Strategy to backup the system db

Hi,
I am trying to approach method to copy my system database using enterprise
tool. I have researched on Database Mainteance tool and Backup database. I
would like to use one of the method to copy my database without any
transcational log since we don't have any transcational going on and not
growing my database too much.
how would i accomplish this?
any comments would be appreciatePooja,
You might want to clarify whether or not this is a system database i.e.,
Master, MSDB, etc... or a user-defined database. When you say "my" database
I'm going to assume it is a user-defined database. If you do not require
the transaction logs to be backed up as part of your database recovery
strategy, you can set the recovery mode for the database to SIMPLE (the
t-log will be automatically truncated). Then schedule your full database
backup as a job or use a Maintenance Plan.
HTH
Jerry
"Pooja" <Pooja@.discussions.microsoft.com> wrote in message
news:8F05EF72-186D-4B93-BA20-E81364824F29@.microsoft.com...
> Hi,
> I am trying to approach method to copy my system database using enterprise
> tool. I have researched on Database Mainteance tool and Backup database. I
> would like to use one of the method to copy my database without any
> transcational log since we don't have any transcational going on and not
> growing my database too much.
> how would i accomplish this?
> any comments would be appreciate

Best approach?

I have a problem. My website hosting company can only host sql server datab
ase that are less than 100 mb. Usually that is not a problem for most of my
applications, however I have one client who has a database with over 270000
records which makes the database over 100MB. What can I do? I thought of
purchasing another database from the host so I'll have 200MB then I can prog
ramatically split the tables up based on ID, for instance if recordID < 1500
00 then move this data to sql server 1. If recordID > 150000 then move data
to sql server 2. Then when I want to query the system for a student, I wou
ld first check the recordID for greater than or less than 150000 then query
the proper table. The data in the system is only used for retrieval (read o
nly), there are no updates to the system.
Do you think this approach is incorrect or do you have any other suggestions
? Thanks!I would find another host for this particular client rather than do a dog an
d
pony dance. I actually like to spend time with my family, so your mileage ma
y
vary. :-)
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Shawn Ferguson" wrote:

> I have a problem. My website hosting company can only host sql server database th
at are less than 100 mb. Usually that is not a problem for most of my applications,
however I have one client who has a database with over 270000 records which makes t
he
database over 100MB. What can I do? I thought of purchasing another database from the hos
t so I'll have 200MB then I can programatically split the tables up based on ID, for instan
ce if recordID < 150000 then move this data to sql server 1. If recordID
> 150000 then move data to sql server 2. Then when I want to query the system for a
student, I would first check the recordID for greater than or less than 150000 then
query the proper table. The data in the system is only used for retrieval (read on
ly
), there are no updates to the system.
> Do you think this approach is incorrect or do you have any other suggestio
ns? Thanks!
>|||Any suggestions for economical SQL Server hosting?

I would find another host for this particular client rather than do a dog an
d
pony dance. I actually like to spend time with my family, so your mileage ma
y
vary. :-)
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Shawn Ferguson" wrote:
> I have a problem. My website hosting company can only host sql server dat
abase that are less than 100 mb. Usually that is not a problem for most of
my applications, however I have one client who has a database with over 2700
00 records which makes the database over 100MB. What can I do? I thought o
f purchasing another database from the host so I'll have 200MB then I can pr
ogramatically split the tables up based on ID, for instance if recordID < 15
0000 then move this data to sql server 1. If recordID > 150000 then move da
ta to sql server 2. Then when I want to query the system for a student, I w
ould first check the recordID for greater than or less than 150000 then quer
y the proper table. The data in the system is only used for retrieval (read
only), there are no updates to the system.
>
> Do you think this approach is incorrect or do you have any other suggestio
ns? Thanks!
>|||You could have a second db and then use a Partitioned View. That way you do
n't have to put any logic into the app at all.
--
Andrew J. Kelly SQL MVP
"Shawn Ferguson" <SFergus2@.cscc.edu> wrote in message news:uBn5eDuFGHA.216@.T
K2MSFTNGP15.phx.gbl...
I have a problem. My website hosting company can only host sql server datab
ase that are less than 100 mb. Usually that is not a problem for most of my
applications, however I have one client who has a database with over 270000
records which makes the database over 100MB. What can I do? I thought of
purchasing another database from the host so I'll have 200MB then I can prog
ramatically split the tables up based on ID, for instance if recordID < 1500
00 then move this data to sql server 1. If recordID > 150000 then move data
to sql server 2. Then when I want to query the system for a student, I wou
ld first check the recordID for greater than or less than 150000 then query
the proper table. The data in the system is only used for retrieval (read o
nly), there are no updates to the system.
Do you think this approach is incorrect or do you have any other suggestions
? Thanks!|||I use Webhost4life... really cheap...
www.webhost4life.com ( $10 Month 300 MB)
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Andrew J. Kelly" wrote:

> You could have a second db and then use a Partitioned View. That way you
don't have to put any logic into the app at all.
> --
> Andrew J. Kelly SQL MVP
>
> "Shawn Ferguson" <SFergus2@.cscc.edu> wrote in message news:uBn5eDuFGHA.2
16@.TK2MSFTNGP15.phx.gbl...
> I have a problem. My website hosting company can only host sql server database
that are less than 100 mb. Usually that is not a problem for most of my application
s, however I have one client who has a database with over 270000 records which makes
th
e database over 100MB. What can I do? I thought of purchasing another data
base from the host so I'll have 200MB then I can programatically split the t
ables up based on ID, for instance if recordID < 150000 then move this data
to sql server 1. If record
ID > 150000 then move data to sql server 2. Then when I want to query the s
ystem for a student, I would first check the recordID for greater than or le
ss than 150000 then query the proper table. The data in the system is only
used for retrieval (read on
ly), there are no updates to the system.
> Do you think this approach is incorrect or do you have any other suggest
ions? Thanks!
>
>|||If your constraint is definitely the 100MB limit, your approach is fine. The
method you used is called horizontal partitioning. As Andrew Kelly mentioned
in the other reply, you should use a view to do it. Goto SQL Server Books
Online and search for "partitioning data".
Dennis
"Shawn Ferguson" wrote:

> I have a problem. My website hosting company can only host sql server database th
at are less than 100 mb. Usually that is not a problem for most of my applications,
however I have one client who has a database with over 270000 records which makes t
he
database over 100MB. What can I do? I thought of purchasing another database from the hos
t so I'll have 200MB then I can programatically split the tables up based on ID, for instan
ce if recordID < 150000 then move this data to sql server 1. If recordID
> 150000 then move data to sql server 2. Then when I want to query the system for a
student, I would first check the recordID for greater than or less than 150000 then
query the proper table. The data in the system is only used for retrieval (read on
ly
), there are no updates to the system.
> Do you think this approach is incorrect or do you have any other suggestio
ns? Thanks!
>

Best approach with DTS

Let me see if I can explain this.

I have the need to pull data from multiple tables from a DB2 system via ODBC and update or insert as needed into tables in a SQL200 DB.

Step 1.
The data from the initial parent table will need to be limited to being a set number of days old, which I have in place and working.

Step 2
The next tables data needs to be limited from the data retrieved in step 1 (Id like to use the paprent table retrieved in step 1, that is in SQL now, rather than doing it on the DB2 side.

Step 3
The returned rows here, need to be limited to key values returned from step 2

Additional steps apply, but nearly all will be limited to the results of parent tables from the prior step.

What is the best approach to this? I really want to pull table A to SQL, and limit the next child set from Table A, that was pulled to SQL in the prior step.

I also need to do updates rather than dropping and creating the needed tables each time. Insert if no key exists, etc .etc.

What is the best approach?I think DTS is better in this regard.
You need to workout to re-arrange data based upon the requirement.
Once data is imported you can contro updations from SQL side using normal TSQL.|||I think I'm going to continue to limit the selection on the db2 side based on sub queries. Initially set it up to drop and create the tables each time, and after that's all done, modify to import into temp tables from dts and then use sql to update the existing tables from the temp tables, I think this is the approach I'm going to take.

I'm open to ideas for alternatives

Best approach to updating remote server?

I have a local production server, and a web server at a remote ISP connected
via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
of data on the production server and update it to the web server daily. The
web server only gets a subset of the information on the production server.
I know I can't be the first to need this so what I am searching for is the
best way to accomplish this. Do I refresh the web server daily (about 500MB
of data)? Should I send each table with an indicator as to
Add/Change/Delete, and write a procedure to import? or is there just another
solution I am overlooking.
Any suggestions would be appreciated.
TIA, MarkHi Mark.
A common approach to achieving this is to create a DTS package that
encapsulates the process, applies business rules to the data etc & then
schedule it to run from the local production server. I've done this with
various ISP hosted SQL databases with fairly good success.
Is your ISP hosted SQL Server dedicated or shared? If dedicated, many
options may open up to you that a shared server might have available.
HTH
Regards,
Greg Linwood
SQL Server MVP
"Mark Butler" <mredhat_nospam@.yahoo.com> wrote in message
news:enU%238d8DEHA.2932@.tk2msftngp13.phx.gbl...
> I have a local production server, and a web server at a remote ISP
connected
> via a VPN. Both servers are running MS SQL 2000. I wish to do an extract
> of data on the production server and update it to the web server daily.
The
> web server only gets a subset of the information on the production server.
> I know I can't be the first to need this so what I am searching for is the
> best way to accomplish this. Do I refresh the web server daily (about
500MB
> of data)? Should I send each table with an indicator as to
> Add/Change/Delete, and write a procedure to import? or is there just
another
> solution I am overlooking.
> Any suggestions would be appreciated.
> TIA, Mark
>

Best approach to sending field names dynamically

Hi,

I have a C# web app that searches my database table using the
following search parameters

Search string, criteria (< =) and the field you want to perform your
search on. My understanding is that stored procedure is the way to go.
What's the best way of doing this using stored procedures. Can I
define a placeholder for the field name?

Ex.
SELECT field1, field2... FROM Table WHERE field1='value1' where field1
and value1 are both sent from code.

If it's not possible then what is the best way to approach this
problem? I see so many searches like that on the internet. I can only
do them with inline SQL and not stored procedure.

Thank you
Maz.(maflatoun@.gmail.com) writes:

Quote:

Originally Posted by

I have a C# web app that searches my database table using the
following search parameters
>
Search string, criteria (< =) and the field you want to perform your
search on. My understanding is that stored procedure is the way to go.
What's the best way of doing this using stored procedures. Can I
define a placeholder for the field name?
>
Ex.
SELECT field1, field2... FROM Table WHERE field1='value1' where field1
and value1 are both sent from code.
>
If it's not possible then what is the best way to approach this
problem? I see so many searches like that on the internet. I can only
do them with inline SQL and not stored procedure.

Yes, these sort of searches are not very easy to do with static SQL.
I have an article on my web site that discusses this topic in detail:
http://www.sommarskog.se/dyn-search.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Best approach to encrypt data?

Hi,

I want to encrypt certain data like password, ssn, credit card info etc before saving in database. Also, this encrypted data can be queried using standard SQL statements like:

select * from users where userid=454 and password = 'encrypted data'

The mechanism to encrypt data could be in a .net application. The code that does encryption/decryption should also be protected so that it doesnt work if it falls in wrong hands.

Can anyone suggest what would be the best way to accomplish above?

thanks,
dapi

You can start with the following links

http://www.microsoft.com/technet/prodtechnol/sql/2005/sqlencryption.mspx

http://articles.techrepublic.com.com/5100-22-5083541.html

Hope this helps

|||

I would suggest reading "Writing Secure Code", Chapter 6. It has a number of examples of how to encrypt data on a Windows platform, and where people make common mistakes. The book is fairly cheap ($0.10 per page) and probably available at the library. I understand C# has a rather extensive crypto library, but knowing how and why is probably better than just "plugging and chugging".

Hope that helps,

John

Best approach to creating an annotated schema?

I have painfully found out that the schema created from a
dataset.writexmlschema does NOT create an XML schema that can be used with
the XMLBULKLOADER. Does anybody have any insights/code snippets/ideas on ho
w
to create an annotated schema with the 'sql:relation' and 'sql:field'
annotations that are necessary for the xmlbulkloader'You can check out the Books online:
http://msdn.microsoft.com/library/d...ations_0gqb.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:51ABC14A-47D6-4F50-8390-F8ADF79B55D9@.microsoft.com...
> I have painfully found out that the schema created from a
> dataset.writexmlschema does NOT create an XML schema that can be used with
> the XMLBULKLOADER. Does anybody have any insights/code snippets/ideas on
how
> to create an annotated schema with the 'sql:relation' and 'sql:field'
> annotations that are necessary for the xmlbulkloader'

Best approach to creating an annotated schema?

I have painfully found out that the schema created from a
dataset.writexmlschema does NOT create an XML schema that can be used with
the XMLBULKLOADER. Does anybody have any insights/code snippets/ideas on how
to create an annotated schema with the 'sql:relation' and 'sql:field'
annotations that are necessary for the xmlbulkloader?
You can check out the Books online:
http://msdn.microsoft.com/library/de...tions_0gqb.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"MSSQLServerDeveloper" <MSSQLServerDeveloper@.discussions.microsoft.com>
wrote in message news:51ABC14A-47D6-4F50-8390-F8ADF79B55D9@.microsoft.com...
> I have painfully found out that the schema created from a
> dataset.writexmlschema does NOT create an XML schema that can be used with
> the XMLBULKLOADER. Does anybody have any insights/code snippets/ideas on
how
> to create an annotated schema with the 'sql:relation' and 'sql:field'
> annotations that are necessary for the xmlbulkloader?

Best approach SQL Reporting Authentication - WorkGroup

Hi there
Situration
1.Development Server is on WorkGroup, not main Domain. (win 2003
server(std))
2.Accessing dev server via \\Main Domain\Administrator
3.SQL 2K (Std)
SQL Reporting
1.Using SQL authentication for shared resource, rather win
authentication.
2.Shared folder, access set to everyone.
Current Situration
1.Have created 5 reports, each has a url within Domino Notes
application. Click on anyone of them (assuming
\\Main Domain\Administrator\) --> reports work ok.
2.Log-on as non-administrator --> click on url, enter site ok, but
popup appears asking for authentication of user
e.g. name, pwd & domain.
Constraints
1.Development Box cannot be joined to main domain i.e. Non-Wins
authentication
2.Host name e.g. http:\\wtdev01\reportserver --> dealt with WINS
server, not host file !
Requirement
Any user clicks on URL for the report, will open the report and be run
without authentication issues.
Tried
1.Custom Authentication (e.g. Form Authentication) - issue cannot use
Standard version of SQL Server ! Needs to be Enterprise. Dead end.
Any ideas.This is where Anonymous access may do the trick for you if you don't need RS
to differentiate the users. If you do, then one of your constraints needs to
go away.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"ImraneA" <i.akram@.weir.co.uk> wrote in message
news:24b9d612.0411010439.7f2e15d4@.posting.google.com...
> Hi there
> Situration
> 1.Development Server is on WorkGroup, not main Domain. (win 2003
> server(std))
> 2.Accessing dev server via \\Main Domain\Administrator
> 3.SQL 2K (Std)
> SQL Reporting
> 1.Using SQL authentication for shared resource, rather win
> authentication.
> 2.Shared folder, access set to everyone.
> Current Situration
> 1.Have created 5 reports, each has a url within Domino Notes
> application. Click on anyone of them (assuming
> \\Main Domain\Administrator\) --> reports work ok.
> 2.Log-on as non-administrator --> click on url, enter site ok, but
> popup appears asking for authentication of user
> e.g. name, pwd & domain.
> Constraints
> 1.Development Box cannot be joined to main domain i.e. Non-Wins
> authentication
> 2.Host name e.g. http:\\wtdev01\reportserver --> dealt with WINS
> server, not host file !
> Requirement
> Any user clicks on URL for the report, will open the report and be run
> without authentication issues.
> Tried
> 1.Custom Authentication (e.g. Form Authentication) - issue cannot use
> Standard version of SQL Server ! Needs to be Enterprise. Dead end.
> Any ideas.|||Give that a try. Thanks.

Best approach line

Hello
I've painted a line chart that represents dispersed points and I need a line that represents on the same chart the best approach line to theese points.
Please tell me how can I do this using Crystal Reports if it is possible.
Thanks to all.Please, I need help about this!|||Crystal supports conditional supporting. If you go to Crystal Report's developers help and search for "conditional" you may get some clue.

Thanks

Best approach for pushing records to MS Access

All,

I am new to DTS/SSIS and have a couple of questions about using it to solve a problem. We have an application running on SQL Server 2005 where status records are written to a status table. I need to be able to send those records over to a status table in a legacy application running on Access.

Originally, I thought about writing a custom c# stored proc and accessing Access from it and then someone pointed me to DTS/SSIS.

Is there a way to exectute the package based on a trigger event that a row was inserted or updated? If not and I take a scheduled approach (every 3 minutes, etc.) do I have to maintain a column for the records that get processed so they are not picked up again.

In general is using SSIS the approach to take? The overall business requirements are straight forward, but I am not sure if SSIS is overkill for this or not.

Thanks,

Steve

If I use an Execute SQL Task on the Control Flow, how do I use that resulting dataset as a Data Source on the Data Flow? I added a variable named 0 and type object, but I cannot figure out how to reference it on the Data Flow designer tab.

Best approach for creating SQL Server 2005 reports to run against SQL 2000 database engine

We have a client who is running SQL Server 2000. They have Business Objects reports. We want to create those same reports using SQL Reporting Services and get rid of Business Objects. The trick is, we'd prefer to create them in SQL Server 2005 with VS 2005, so that we have the reports created with the newest version rather than having to use the older, less functional version to create them. What are our options here? Can we create the reports in 2005 and somehow run the reports against a 2000 database without making our client have to install a SQL Server 2005 instance?
Or will we need to create the reports in SQL Server 2000? Are there other combinations that would work where we can still create them in 2005 but run against 2000?
I'm sure they will upgrade eventually, but I'd rather not rush them at this time.
Thanks much!I think I didn′t get you right, so here are two explanations to your problem:

You can run reports against every supported datasource, this does not have to be SQL Server, it can either be Oracle, a text file, a Webservice or any other source. You can use the out-of-the-box functionality and sources or you can create your own custom provider which leaves with unlimited variety for sources. The Report Server has to be installed on a SQL Server 2005 Server. Therefore you need a SQL Server 2005 licence. If you have one of these you can also install a SQL Server 2005 database. If the clients don′t have a SQL Server 2005 licences you will be stuck to SQL Server Express Advanced Edition, which is capable to distribute it as you want but will only have access to local SQL Server database sources.

So I hope I hit on of your problems withthat answer :-)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Jens,

Thanks - I think that does answer my question. My customer only has SQL Server 2000 right now, so it seems I will need to build the reports in SQL Server 2000 using Visual Studio 2003 as the IDE. In our development shop we have moved on to VS 2005, so I am assuming that for new clients we can upgrade our reports to 2005 without too much effort. If you know of any issues about upgrading reports from SQL Server 2000 reporting services to SQL Server 2005, please let me know.

Thanks much!

best approach for "trigger on commit"?

(I've searched the forum but haven't found anything that seems to address my specific question)

We have multiple tables that get updated as part of a transaction.

After all the data is added succesfully there is processing that needs

to be done, so we'd like to use a trigger to kick it off automatically. The schema can be thought of as including a "master report" table that gets updated once whenever a new report is submitted and multiple related tables each corresponding to different report sections. The PK-FK relations imply that the master report will be updated prior to the subsidiary sections, but the exact order of updates to the subtables is not defined.

I thought of attaching a trigger to the master report table but I can't find an approach or mechanism that would allow us to defer the triggered code until after all the tables have been updated.

It almost seems like I want a transaction trigger. Something that would be executed before or after a COMMIT TRAN. But the

post-processing code does not have to be deferred until after the

transaction is committed, it just mustn't be started until all the

tables participating in the transaction have been updated, otherwise

all the data needed won't be present.

There are several work-arounds that come to mind, but none are as

elegant as having a TRIGGER ON COMMIT capability. I'd be interested in

how other people have solved this problem.

Here's some of the work-arounds we came up with

1. Identify the last table to be updated, and place a trigger on that. Ugly for obvious reasons and very prone to breaking.

2. Create an "update history" table with a trigger on it. After the

actual transaction commits, a row should be added to this table by the

db user. Less ugly, but still not pretty.

3. Variation on #2 -- after an update transaction, have the database user call a stored procedure. Just as ugly as #2

4. Create a view that is used to write through to the underlaying tables, and have the trigger on the view. Unfortunately the large amount of data (approx 20 tables participate in one update transaction) makes this very unattractive. (Imagine an INSERT statement with 300 values!?)

Thanks in advance for suggestions and help

TerryPesonally, I would recommend any solution that does not use Triggers. Triggers are evil. They are a maintenance nightmare. After you finish your "transaction", you can call a stored proc that takes care of the rest of the updates which is a more controlled way of doing than doing it in a trigger. Do a simple google search on issues with triggers and you will be convinced enough to not use them.|||

We, I cannot agree with your feelings on triggers (Cursors are evil, not triggers. Triggers are just a bit difficult :) I do agree that this is not the place for a big trigger (considering how SQL Server triggers work.)

An alternative that I might suggest if this can be done asynchronously is that you have a job that queries your tables to see when the transaction is finished (either you check for the existance of data in all tables, or you write some sort of control row (possibly using triggers here) when you do each insert into the other tables. Then the process can check to see when all of the data for a "transaction" is completed and do your processing.

|||I can second Louis, triggers always had and will have a bad reputation because of the wrong implementation of users. Sure, in some cases they behave slow, they act syncronously (which can real nightmare if not considered) and are fired per statement not per row, so a bit implementation afford is needed here, but triggers can help your through the day for enforcing business rules which can′t be accomplished through any middle tier (if the database is directly access by several applications). So I would say that they are as evli as you force them to be :-)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||After the database is developed it may have data added by an unknown number of subcontractors. So it is important that the "api" be as simple as possible and that the database be as intelligent as possible. Building business logic into the database in the form of SP and triggers has clear advantages for us over an approach that depends on the goodwill of users to follow our suggested/required calling structure.

In other words, having a document that says "after inserting all of a report call SP after_insert_processing()" opens up the door for someone to forget to call the SP. Having a mechanism in place that detects the operation and automatically calls the SP is much more attractive to us.

Hmmm.....|||BTW thanks for all of the suggestions. We may go with a job that checks the time of the last update and determines from that if the post-insert processing should be done.|||

You can do something like below:

1. Create a tracking table

2. Insert a row from the tracking table for each operation on the other table(s) (Need not be at insert level. You can determine the granularity)

3. Have the insert trigger on the tracking table determine the matching conditions to fire the SP. This can be done by checking for count of specific number of operations per report for example. The post_insert_processing SP can be fired using an on-demand SQLAgent job so that it is asynchronous.

You could do away with the tracking table approach and simply do the check in every SP that inserts into the other table(s) also. Either way you can centralize the logic to check in a SP (by using simple queries against the required table(s)).

|||

My first question would be..."How do you currently ensure all information is updated correctly?"
My second question would be..."Is this a manual process that you want to include in your automation?"

It sounds as if there is some form of linear progression with a definitive end but you want to avoid this assumption for fear of change.

So...to satify your anxieties, create an independant action (the approach is insignificant).

Forget about the clever trickery or sly workaround, code a 101 solution to a 101 problem.

Adamus

Best Approach at Refreshing large table

I have a large table that needs to be refreshed on a regular basis. This
data is read-only, it will not be modified. I import the refreshed data in
its entirety (all data exists in a fresh table).
I can think of 3 ways to do the refresh but I'm not sure how to evaluate
which option is best:
1. Import the refreshed table
Drop the original table
Rename the refreshed table to original table
2. Import the refreshed table
Truncate the old table
Insert all the records from the refresh table into the original table
3. Import the refreshed table
Delete all records in the original table that do not exist in the
refresh table
Insert all records in the refresh table that do not exist in the
original table
Update all records in the original that are not the same as those in the
refresh table
Method 1 might lead to "invalid object" errors if users attempt to access
the table during the refresh so I do not think this is a good choice.
I think method 2 and 3 need to be evaluated based on the locks they use and
the time they take to execute.
What would happen if a user attempts to access the table during the TRUNCATE
and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
After the INSERT completes?
What about option 3? What kind of access will a user have during these 3
data modification actions INSERT, DELETE and UPDATE?
As I mentioned, the data is read-only and I need to try to maintain maximum
accessibility for the users.
Any comments are appreciated.
DaveHi,
If you need to provide maximum accssibility to user then go for step3.
Ensure that when ever you do a DML, do it row level.
So only that record will be locked and users will be able to view data with
out any problems.
Thanks
Hari
MCDBA
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data

---

in

quote:

---

> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in

---

the

quote:

---

> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use

---

and

quote:

---

> the time they take to execute.
> What would happen if a user attempts to access the table during the

---

TRUNCATE

quote:

---

> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain

---

maximum

quote:

---

> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>

---

|||Dave,
If availability is the concern, and the data is truly read-only, then here's
what I would do...
Let's make believe you're refreshing "ORDERS"
A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
B1.) Set transaction isolation level serializable.
B2.) Begin transaction.
B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
B5.) Commit transaction
C1.) Drop "old" table.
This way, the data is only 'unavailable' for milliseconds, and the side
benefit is that, because of the locks acquired, your users will not receive
the 'invalid object name' errors.
I do this in production environments all the time.
Of course, there's a little more to it if you've got DRI and what-not, but
I'm sure you get the idea.
James Hokes
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data

---

in

quote:

---

> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in

---

the

quote:

---

> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use

---

and

quote:

---

> the time they take to execute.
> What would happen if a user attempts to access the table during the

---

TRUNCATE

quote:

---

> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain

---

maximum

quote:

---

> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>

---

|||Hari,
His data is static.
Step 3 would blow chunks in any sort of VLDB situation.
Not to mention that your suggestion of row-level DML goes against the grain
of set-based RDBMS theory.
This is nothing more than a simple table-swap op.
James Hokes|||Excellent!
Thanks very much.
"James Hokes" <noemail@.noway.com> wrote in message
news:e4$vP846DHA.452@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Dave,
> If availability is the concern, and the data is truly read-only, then

---

here's

quote:

---

> what I would do...
> Let's make believe you're refreshing "ORDERS"
> A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
> B1.) Set transaction isolation level serializable.
> B2.) Begin transaction.
> B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
> B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
> B5.) Commit transaction
> C1.) Drop "old" table.
> This way, the data is only 'unavailable' for milliseconds, and the side
> benefit is that, because of the locks acquired, your users will not

---

receive

quote:

---

> the 'invalid object name' errors.
> I do this in production environments all the time.
> Of course, there's a little more to it if you've got DRI and what-not, but
> I'm sure you get the idea.
> James Hokes
> "DaveF" <davef@.comcast.net> wrote in message
> news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
This[QUOTE]
data[QUOTE]
> in
table[QUOTE]
> the
access[QUOTE]
> and
> TRUNCATE
TRUNCATE?[QUOTE]
3[QUOTE]
> maximum
>

---

Best Approach at Refreshing large table

I have a large table that needs to be refreshed on a regular basis. This
data is read-only, it will not be modified. I import the refreshed data in
its entirety (all data exists in a fresh table).
I can think of 3 ways to do the refresh but I'm not sure how to evaluate
which option is best:
1. Import the refreshed table
Drop the original table
Rename the refreshed table to original table
2. Import the refreshed table
Truncate the old table
Insert all the records from the refresh table into the original table
3. Import the refreshed table
Delete all records in the original table that do not exist in the
refresh table
Insert all records in the refresh table that do not exist in the
original table
Update all records in the original that are not the same as those in the
refresh table
Method 1 might lead to "invalid object" errors if users attempt to access
the table during the refresh so I do not think this is a good choice.
I think method 2 and 3 need to be evaluated based on the locks they use and
the time they take to execute.
What would happen if a user attempts to access the table during the TRUNCATE
and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
After the INSERT completes?
What about option 3? What kind of access will a user have during these 3
data modification actions INSERT, DELETE and UPDATE?
As I mentioned, the data is read-only and I need to try to maintain maximum
accessibility for the users.
Any comments are appreciated.
DaveHi,
If you need to provide maximum accssibility to user then go for step3.
Ensure that when ever you do a DML, do it row level.
So only that record will be locked and users will be able to view data with
out any problems.
Thanks
Hari
MCDBA
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data
in
> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in
the
> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use
and
> the time they take to execute.
> What would happen if a user attempts to access the table during the
TRUNCATE
> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain
maximum
> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>|||Dave,
If availability is the concern, and the data is truly read-only, then here's
what I would do...
Let's make believe you're refreshing "ORDERS"
A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
B1.) Set transaction isolation level serializable.
B2.) Begin transaction.
B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
B5.) Commit transaction
C1.) Drop "old" table.
This way, the data is only 'unavailable' for milliseconds, and the side
benefit is that, because of the locks acquired, your users will not receive
the 'invalid object name' errors.
I do this in production environments all the time.
Of course, there's a little more to it if you've got DRI and what-not, but
I'm sure you get the idea.
James Hokes
"DaveF" <davef@.comcast.net> wrote in message
news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
> I have a large table that needs to be refreshed on a regular basis. This
> data is read-only, it will not be modified. I import the refreshed data
in
> its entirety (all data exists in a fresh table).
> I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> which option is best:
> 1. Import the refreshed table
> Drop the original table
> Rename the refreshed table to original table
> 2. Import the refreshed table
> Truncate the old table
> Insert all the records from the refresh table into the original table
> 3. Import the refreshed table
> Delete all records in the original table that do not exist in the
> refresh table
> Insert all records in the refresh table that do not exist in the
> original table
> Update all records in the original that are not the same as those in
the
> refresh table
>
> Method 1 might lead to "invalid object" errors if users attempt to access
> the table during the refresh so I do not think this is a good choice.
> I think method 2 and 3 need to be evaluated based on the locks they use
and
> the time they take to execute.
> What would happen if a user attempts to access the table during the
TRUNCATE
> and INSERT? Will it be locked from any SELECTs until after the TRUNCATE?
> After the INSERT completes?
> What about option 3? What kind of access will a user have during these 3
> data modification actions INSERT, DELETE and UPDATE?
> As I mentioned, the data is read-only and I need to try to maintain
maximum
> accessibility for the users.
> Any comments are appreciated.
> Dave
>
>
>|||Hari,
His data is static.
Step 3 would blow chunks in any sort of VLDB situation.
Not to mention that your suggestion of row-level DML goes against the grain
of set-based RDBMS theory.
This is nothing more than a simple table-swap op.
James Hokes|||Excellent!
Thanks very much.
"James Hokes" <noemail@.noway.com> wrote in message
news:e4$vP846DHA.452@.TK2MSFTNGP11.phx.gbl...
> Dave,
> If availability is the concern, and the data is truly read-only, then
here's
> what I would do...
> Let's make believe you're refreshing "ORDERS"
> A1.) Import fresh data into staging table, say it's called "ORDERS_STAGE".
> B1.) Set transaction isolation level serializable.
> B2.) Begin transaction.
> B3.) Rename existing table, say it's called "ORDERS", to "ORDERS_OLD"
> B4.) Rename staging table, say it's called "ORDERS_STAGE" to "ORDERS"
> B5.) Commit transaction
> C1.) Drop "old" table.
> This way, the data is only 'unavailable' for milliseconds, and the side
> benefit is that, because of the locks acquired, your users will not
receive
> the 'invalid object name' errors.
> I do this in production environments all the time.
> Of course, there's a little more to it if you've got DRI and what-not, but
> I'm sure you get the idea.
> James Hokes
> "DaveF" <davef@.comcast.net> wrote in message
> news:OEhj0n36DHA.2480@.TK2MSFTNGP10.phx.gbl...
> > I have a large table that needs to be refreshed on a regular basis.
This
> > data is read-only, it will not be modified. I import the refreshed
data
> in
> > its entirety (all data exists in a fresh table).
> >
> > I can think of 3 ways to do the refresh but I'm not sure how to evaluate
> > which option is best:
> >
> > 1. Import the refreshed table
> > Drop the original table
> > Rename the refreshed table to original table
> >
> > 2. Import the refreshed table
> > Truncate the old table
> > Insert all the records from the refresh table into the original
table
> >
> > 3. Import the refreshed table
> > Delete all records in the original table that do not exist in the
> > refresh table
> > Insert all records in the refresh table that do not exist in the
> > original table
> > Update all records in the original that are not the same as those in
> the
> > refresh table
> >
> >
> > Method 1 might lead to "invalid object" errors if users attempt to
access
> > the table during the refresh so I do not think this is a good choice.
> >
> > I think method 2 and 3 need to be evaluated based on the locks they use
> and
> > the time they take to execute.
> >
> > What would happen if a user attempts to access the table during the
> TRUNCATE
> > and INSERT? Will it be locked from any SELECTs until after the
TRUNCATE?
> > After the INSERT completes?
> >
> > What about option 3? What kind of access will a user have during these
3
> > data modification actions INSERT, DELETE and UPDATE?
> >
> > As I mentioned, the data is read-only and I need to try to maintain
> maximum
> > accessibility for the users.
> >
> > Any comments are appreciated.
> > Dave
> >
> >
> >
> >
> >
>

best approach "wher in" or JOIN

Hi,

I have 2 sets
Set1 has few UNIQUE items say workitem Ids 2,3,4

Set2 has multiple items say workitems Ids is 1,2,2,4,3,5,4,3,6,7,8

I need to look for Set1 in Set2 so i get 2,3,4 etc...

which is the best way

Set2 Left join Set1

OR

Select item from Set2 where item in (Set1)INNER join, not LEFT OUTER|||I posted your answer over at SQLTeam|||see if this helps

books online {Using EXISTS and NOT EXISTS to Find Intersection and Difference} QUOTED IN ENTIRETY
Subqueries introduced with EXISTS and NOT EXISTS can be used for two set-theory operations: intersection and difference. The intersection of two sets contains all elements that belong to both of the original sets. The difference contains elements that belong only to the first of the two sets.

The intersection of authors and publishers over the city column is the set of cities in which both an author and a publisher are located.

USE pubs
SELECT DISTINCT city
FROM authors
WHERE EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

Here is the result set:

city
---
Berkeley

(1 row(s) affected)

Of course, this query could be written as a simple join.

USE pubs
SELECT DISTINCT authors.city
FROM authors INNER JOIN publishers
ON authors.city = publishers.city

The difference between authors and publishers over the city column is the set of cities where an author lives but no publisher is located, that is, all the cities except Berkeley.

USE pubs
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)

This query could also be written as:

USE pubs
SELECT DISTINCT city
FROM authors
WHERE city NOT IN
(SELECT city
FROM publishers)

Best approach

I have what I feel like is a simple package I am working to create. I am teaching myself SSIS as I go along.

Source server SQL 2000 database allows NULL values in columns.

Destination Server also SQL 2000 but the database required a value in each column.

So I do a basic source select what I want. I next need to read the values and determine if null then insert a space, do some column matching and insert them into the destination sever.

I believe I should use a Derived Column and an expression ISNULL to accomplish what I want.

Maybe there is a better way. Suggestion and comment appreciated.

Ryan

Try using the T-SQL ISNULL() function in your initial query, replacing any null values with an empty string or whatever...

ISNULL(myField,'')

best approach

I've been banging my head for a while now, and it is sore! :-P

I'm a best practice/Microsoft approach type of person and want to make sure I do things correctly.

I have a database, kind of like a forum.

Obviously executing multiple queries in one "batch" (stored proc) would have an impact on the performance.

Now, I would like to give a more detailed/specific error back to the caller (either by aid of error code or whatever) with such situations like...

"EditReply"

Edit reply takes the threadID, replyID and userID.

Before actually commiting the changes, it needs to check:

1) does the user exist in the database? (during the editing of the reply, perhaps the user may have been deleted before running the stored proc, who knows)

2) does the thread exist?

3) does the reply exist?

if the conditions are met, only then will it go ahead and update the database. Now that is 3 queries, and 4 statements overall to make a change to a field/table.

Obviously if one of the commands returns false, in other words if say "does the thread exist" returns 0 (thread doesnt exist) it will return back to the caller an errorcode, which they will handle in their application. Thats all fine but the question is

Am I doing this correctly? (no) - how can I improve this? What do I need to think about?

Of course I would like to give a more detailed error back to the caller (aid of errorcode designed in the application overall) instead of just "no, databases not updated".

In this situation, am I wrongly assuming that the database designers use this type of approach?

Please help, I value your feedback and suggestions. I want to improve and think of the right lines of doing these things.

A lot of the business logic of a database should be encorporated into the table design.

If you insert into a table which is logically linked to another table then there should be foreign keys and check constraints in place to enforce the business logic at table level. This would cover you for user and threads, as the replies table must have a foreign key constraint to the user and threads tables.

if the reply does not exist then nothing is going to get updated anyway, you can use the @.@.rowcount system variable to check how many rows were effected by the last statement.

So with the table constraints enforcing the business logic you should be able to go ahead and make the update regardless of whether it is valid or not. Your application will need to handle SQL errors and report them back to the user and it will need to get that @.@.rowcount system variable to see if the table was actually updated

|||

Many thanks for this

Yes, the tables are all constrainted and relationships are in place and so on.

However I understand I can use the @.@.rowcount to check how many rows where effected by the statement, but if for instance the row was not updated, I would like to know why it was not updated, this could be for example, the user did not exist, in which case I would return an errorcode back to the caller, who handles this particular error code.

I am wondering if this is best design, as the user would like to know what went wrong (such as they got deleted whilst making a change to something, or the thread got deleted whilst they where making a change etc...) therefore in the current stage, there are multiple IF EXISTS statement, and IF EXISTS returns false, they then would get returned back a specific error code from within the application back to the caller.

is this a bad design? how better can they/I make it?

|||

I would also consider looking at another approach wherein the database stored procedures do the data consistency check and return pre-defined error code which the application maintains (via an enum). The application can then check the status code returned by the stored proc and proceed based on that code. Something like this...

enum ReplyOperationStatus {
Success = 0, UserNotFound = 1, ReplyNotFound = 2, ThreadNotFound = 3, UnknownError = 4
};

stored proc : UpdateReply

if not exists (select null from threads where threadId = @.threadId) return 3

if not exists (select null from users where userId = @.userId) return 1

if not exists (select null from thread_replies where threadId = @.threadId AND replyId = @.replyId) return 2

... perform the update ...

return 0

|||

I agree, but flip it around. I will just be generic and use 2000 style errors. Use try...catch if you are using 2005 to catch errors and then you can have a bit more control.

stored proc : UpdateReply

declare @.error int, @.rowcount int

... perform the update ...

set @.error = @.@.error, @.rowcount = @.@.rowcount

if it is an error due to some key violation, it will be raised, so handle it. A good example is having a unique index on a post guid that you generate on the client. This guid would keep them from duplicating a post.

if @.@.rowcount = 0
begin --now see why not
if not exists (select null from threads where threadId = @.threadId) return 3
if not exists (select null from users where userId = @.userId) return 1
if not exists (select null from thread_replies where threadId = @.threadId AND replyId = @.replyId)
return 2
end

return 0

This way you only waste effort to check for things on a failure, which should be infrequent.

|||Wow, thats wonderful. Thanks for the tip Louis.|||Thanks very much, that is pretty much exactly what I already have!! awesome, thanks a bunch :-D|||

Nice expansion on my post with some code there Loius...

I was just wondering though... is it worthwhile having to do those 3 selects in the event of a failure? If the table constraints are set up properly then the SQL Server error message could be just reformatted by the calling application and then returned to the user...

|||Sam - sorry i dont quite follow - do you mean that if there was a SQL general error? if so, then an exception is thrown (assuming we are working with .NET) back to the caller which they handle automatically.|||

Yes a SQL Server generated error

The error text you get when using Query Analyser or Enterprise manager eg:

Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_EmployeeSales_Employees'. The conflict occurred in database 'MyDatabase', table 'Employees', column 'EmployeeID'.
The statement has been terminated.

If that is accessible to the callnig application then it could be quickly parsed and returned in a more legible way to the user leaving the data alone for everyone else

|||

I am unsure if you are aware of this, or maybe i've mis interpreted, but that sort of error generated by SQL is thrown back into .NET and will be thrown to the application user so the developer/application has to handle it.

most of the time the error code is also given in the SQLException in .NET so the developer can handle such errors the way they want it.

|||

My thought was that he was doing something like this:

insert into table (columns)
select userId, otherColumns
from user
where user.name = 'fred@.fred.com'

Now, if that user does exist, done. If not, there will be no row inserted. Then you can decide why in the other queries.