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 ASAP! FoxPro to MSSQL 2000 Conversion question..

I am in the process of converting an old FoxPro 2.6 based DB to MSSQL 2000..

It appears that FoxPro 2.6 stores it's null value dates as 12:00:00 AM which is causing errors when importing the data into MSSQL 2000.

The format of all of the other dates is mm/dd/yyyy

Has anyone else encountered this? is there a filter, or a a script or switch somewhere to replace the NULL values with something that MSSQL is happier with?

..:: EDIT ::..

Looking @. the properties of the affected columns, it looks like they are set as smalldatetime, but if i switch them to datetime with a length of 8 it imports just fine.

So.. now my task is to update all of the columns from smalldatetime to datetime.

sugggestions?

Thanks in advance!

Joe..::bump::..

anyone?|||Use DTS.sql

Need help - Error: 823, Severity: 24, State: 11

I got the following errors......

Error: 823, Severity: 24, State: 11

I/O error 33(The process cannot access the file because another process has locked a portion of the file.) detected during write at offset 0x000002983a0000 in file 'e:\MSSQL\data\tempdb.mdf'..

Error: 15457, Severity: 0, State: 1

Seems like a bad disk. It is a Raid 0 drive.

I have asked a drive check to be performed for this drive. I am not sure if it can be done while the SQL Server is running.

I am thinking of shrinking the tempDB and let it grow again so that it may not expand on the bad sectors.

I could also move the tempDB to another drive temporarily, but would require me to reboot our production server.

Also, my concern is that if the drive is bad, it will affect the other system databases as they are sitting on the same drive. The sytem databases have passed integrity checks over the weekend and seem to be okay.

Anyone has any ideas on what steps I should take or the best way to resolve this problem.Not too sure, but you could check to see that virus scan and any backup software remembers to ignore *.MDF, *.LDF, and *.NDF files. Also you may want to check on tempdb to make sure that auto-close is off (though, I am not sure you can set it to auto-close). Good luck, and let us know if you find the problem.|||Sorry to get back late here, we had some other problems I was working on. Well, our hardware people said that there was nothing wrong with the drive in question. So, it had to be the tempdb problem.

I ran the DBCC CheckDB with All_ErrorMsgs and it returned me no errors. There were no non-system objects in the tempdb and the space used in the 35GB tempdb database was only 4MB. Looking at the time it required to run and the results, it seems to me that the the DBCC command checks only on the space that has been allocated to the tables, etc. So, I ran the DBCC CheckDB with Physical_Only with the hopes that it will scan all the pages even if they are empty, but it came with the same results. No errors.

So, I restarted the server in minimum configuration mode (at the dos prompt with "SQLServr.Exe -c -f" and then using Query Analyzer, I altered the database files (mdf and log) size of the tempdb using the 'alter database' command. And restarted the SQLServer.

If this had not worked, I was going to try to use "DBCC ShrinkFile" and shrink the tempdb database files.

Thanks for your help.

Need for SQL

I'm in the process of ordering a new 2003 server from
Dell. It will be used in a Doctor's office with <5
clients. In your opinion is SQL software an essential
ingredient for overall performance, or does the 2003
server already incorporate many of the features of SQL ?
In simple terms what does SQL provide that 2003 srever
doesn't? As you can see I'm confused, I want to save cost
and not be redundant but in know way do I want to limit
the capabilities of the office.Hi
You may want to look at Small Business Server 2003. This includes amongst
others!
Windows 2003
Exchange 2003
The premium edition includes SQLServer 2000.
Although it may be alot of software to run on a single system the pricing of
this product may make it a viable option if you are looking to deploy all
three applications.
http://www.microsoft.com/windowsserver2003/sbs/evaluation/overview/default.mspx#XSLTsection122121120120
John
"Marty" <anonymous@.discussions.microsoft.com> wrote in message
news:02e101c3981e$3e81f150$a601280a@.phx.gbl...
> I'm in the process of ordering a new 2003 server from
> Dell. It will be used in a Doctor's office with <5
> clients. In your opinion is SQL software an essential
> ingredient for overall performance, or does the 2003
> server already incorporate many of the features of SQL ?
> In simple terms what does SQL provide that 2003 srever
> doesn't? As you can see I'm confused, I want to save cost
> and not be redundant but in know way do I want to limit
> the capabilities of the office.

Need for SQL

I'm in the process of ordering a new 2003 server from
Dell. It will be used in a Doctor's office with <5
clients. In your opinion is SQL software an essential
ingredient for overall performance, or does the 2003
server already incorporate many of the features of SQL ?
In simple terms what does SQL provide that 2003 srever
doesn't? As you can see I'm confused, I want to save cost
and not be redundant but in know way do I want to limit
the capabilities of the office.There is no relational database functionality built into Windows 2000
Server. SQL Server is needed only if you have (or are planning to
develop/purchase) database applications which require SQL Server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Marty" <anonymous@.discussions.microsoft.com> wrote in message
news:04d601c3981e$3ec19260$a301280a@.phx.gbl...
> I'm in the process of ordering a new 2003 server from
> Dell. It will be used in a Doctor's office with <5
> clients. In your opinion is SQL software an essential
> ingredient for overall performance, or does the 2003
> server already incorporate many of the features of SQL ?
> In simple terms what does SQL provide that 2003 srever
> doesn't? As you can see I'm confused, I want to save cost
> and not be redundant but in know way do I want to limit
> the capabilities of the office.

Need call a DB function in the middle of the dataflow process

All,

I have to use a field that is calculated in a data flow process and call a database function (return a value) to do anther calculation; then return a value back to the data flow.I tried OLD DB Command but I cannot configure to return a value back to the same data flow.

If there any transformations that can call a DB function and get a value from the function in the middle of the data flow process?Need more detailed instruction.

The data flow is Like:

SourceDB à New_filed 1 = field1 + filed2 à New_filed 2= DB_function (New_filed 1) à Destination DB

Thanks in Advance

Jessie

HI, to return a value from a DBfunction, you need to add a derived column into the pipeline and map the OLEDB command return value to this newly added derived column. In the OLEDB command text you insert the following command:

EXEC ? = DB_Function(?)

Then on the mapping tab, you map the return value (first ?) to the derived column and the parameter (second ?) to your New_Field 1 parameter. This way, the new derived column gets the parameter from the DB_function.

HTH,
Ccote

|||

Hi, Ccote,

Thanks so much for the reply. I have followed your instruction but still getting errors. Here is the detail.

before the OLE command, I added a derived column, make a new column(NEW_Col) there with the same data type as the DB_function return value (int), set the default value to 0.

In the OLE command,I put exec ?=[dbo].[F_DBfunction] (?,?,?) in the SQLcommand field, mapping the 3 input column with the parameters, map the first ? with the NEW_Col from the derived column.

The OLE command does not let me to add any new column to the OLE command output.

I got error when I click on REFRESH, “Invalid parameter number ‘

Where I’m missing here?

Thanks

Jessie

|||

I got it, after change exec ?= [dbo].[F_DBfunction] (?,?,?) to exec ?= [dbo].[F_DBfunction] ?,?,?

Thanks

Need advice on massive database

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.clustering:14782
Hi,
We're in the process of architecting a very large SQL2K database. We
believe this database will grow 2TB per month. 99% of the inserts will be
done via bulk inserts. Approximately 3-4 per day an application that we
wrote will query and pull approximately 6 million records at a time.
* Will this work with SQL Enterprise or should I use DataCenter?
* Will this operate on Win2k Enterprise or should I use Datacenter?
* What I/O recommendations do you suggest (RAID or fiberchanner / RAID
array, SAN, or NetSAN, memory on controller, etc) to keep up with a bulk
insert of approximately 2,000 records per second?
* Will failover clustering work with a database this size (stupid
question but I have to ask)?
Thanks!
Jack
Comments Inline
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jack Victor" <Nomail@.JustPostReply.tv> wrote in message
news:pan.2004.05.21.14.06.17.720000@.JustPostReply. tv...
> Hi,
> We're in the process of architecting a very large SQL2K database. We
> believe this database will grow 2TB per month. 99% of the inserts will be
> done via bulk inserts. Approximately 3-4 per day an application that we
> wrote will query and pull approximately 6 million records at a time.
> * Will this work with SQL Enterprise or should I use DataCenter?
DataCenter is an OS-level difference. SQL stops at Enterprise Edition, but
it will run on DataCenter OS. You may need DataCenter, but only for the #of
CPUs it will support.
> * Will this operate on Win2k Enterprise or should I use Datacenter?
>
Again, you may need the memory and/or CPU scalability of Datacenter. A lot
depends on the number of concurrent users and the total load.
> * What I/O recommendations do you suggest (RAID or fiberchanner / RAID
> array, SAN, or NetSAN, memory on controller, etc) to keep up with a bulk
> insert of approximately 2,000 records per second?
SAN. Big SAN with multiple HBAs and paths.
> * Will failover clustering work with a database this size (stupid
> question but I have to ask)?
Sure. It may take some time to go through the recovery process during a
failover, but clustering does not affect scalability.
> Thanks!
> Jack
>

Need advice for process of swapping DBs

I need to build a *.sql script that will remove a database (let's call
it "DB1") and replace it with a brand new empty database (let's call it
"DB2").

Caveat: I don't want to be left with database "DB1" having it's files
confusingly named "DB2.mdf" and "DB2_log.ldf". These two files should
also be renamed to "DB1.mdf" and "DB1_log.ldf" so that outside
customers are not left confused. In addition, I need to be able to
restore the original DB1 if anything goes wrong during, or even after,
the entire process.

Let's assume every customer's *.mdf's and *.ldf's will always reside in
C:\Program Files\Microsoft SQL Server\MSSQL\data folder.

I've researched sp_attach_db, but this looks more appropriate for
moving databases. This isn't what I want to do.

Thank you in advance.Simple.. In EM you can right right the database and "All tasks -
Generate SQL Script".. Go through and choose want you want to script
i.e. tables, views, stored procs, constraints, create database etc
etc.. Save the script as a .sql on your computer.. Open the file in a
text editor or SQL QA - modify the create database command and add a
"drop database" command at the top.