Hello all,
I have 2 primary key fields the ssn and refnum... if the data in the file is duplicated it will not import to my table rights even though i am using DTS to do my import, correct? or do I need to add an extra validator in there?
I have a task to perform a new SQL Server 2005 installation at a client.
They have a system with 10 external SCSI drives, each of 72GB. They only have one database of 80GB in use at this system.
1) Userdata: I think that I will put 5 disks at SCSI channel 1, with RAID 3 or 6, with formatted space of 140GB.
2) Log: I think that I will put 3 disks at SCSI channel 2, with RAID 5, with formatted space of 70GB.
3) TempDB: I think that I will put 2 disks at SCSI channel 3, with RAID 0, with formatted space of 140GB.
System already has two built-in drives (RAID 1) for operating system, where I think I will put system databases.
I am open for suggestions and improvements!
Peter Larsson
Helsingborg, Sweden
I am working with a client who has all databases, logs, tempdb and user/system databases on a single raid set.
In order to make some speed improvement, we have decided to move log files to a second raid controller card.
To accomplish this, we are thinking about taking these steps:
1) Detach database XYZ.
2) Stop database service.
3) Create a new partition on new controller card.
4) Copy old log files from I: to new K: drive.
5) Remove drive letter I: from system.
6) Change drive letter K: to I:
7) Attach database
My question is, does SQL Server recognize the log files automatically, since they are placed at same logical position (i: drive)?
Peter Larsson
Helsingborg, Sweden
Just a thought.
If an issue is resolved please note that the problem has been resolved. Because there are "many ways to skin a cat" it would be helpful to anyone else with a similar problem (or someone trying to learn) what the solution was.
We have a project at work that requires us to run an msde database from cd. My sup. and I disagree on if this can be done or not. We currently have an application written in asp that uses an access db. We copy the *.mdb file and all the asp files to cd and use the app. to query the db. Now, we have a client who wants the same capability only using msde. They will not allow us to install the full version of sql on there machines. No one in the dept wants to re-write this app. so we're trying to figure out if we can make it work with msde. So, My first question is, does anyone think that it is possible to just copy the *.mdf file to cd (mind you WITHOUT the *.ldf file... can't write to a cd)andMy second question is, can a sql db run without both files in any situation(mdf,ldf)? (I believe this is impossible, but would like to here it from someone with more experience)Any comments are greatly appreciatedthanks
View 1 Replies View RelatedHi.
I am about to write my registration confirmation tables.
Right now I have my registration form and table "Users"
that receives input from user registration.
I want to do email confirmation of a registered account.
I was thinking of doing this by creating a table "Verify"
that is set to 0 if not verified and 1 if the user verifies
his email address.
Then I started thinking about the original registration
information. If a user "doesn't" verify, then there is all
this dormant data in the "Users" database. So, maybe
it is better to write all the registration data to the "Verify"
table and then have SQL move that data to the "Users"
table upon verification? This sounds a bit sloppy to me...
is this how the email verification of data is done or, if
not, can somebody suggest a best practice?
I thank you.
I need confirmation from you SQL Server experts out there. Please let me know if the following works. Thanks!
This stored procedure gets a value and increments by 1, but while it does this, I want to lock the table so no other processes can read the same value between the UPDATE and SELECT (of course, this may only happen in a fraction of a second, but I anticipate that we will have thousands of concurrent users). I need to manually increment this column because an identity column is not appropriate in this case.
BEGIN TRANSACTION
UPDATE forum WITH (TABLOCKX)
SET forum_last_used_msg_id = forum_last_used_msg_id + 1
WHERE forum_id = @forum_id
SELECT @new_id = forum_last_used_msg_id
FROM forum
WHERE forum_id = @forum_id
COMMIT TRANSACTION
Hi to alll of you, i'm working in a project to save cars information, when the user who adds on the new record enter all the data, this will need to be printed with a particulary number, which needs to be unique, (lets take a passport number as an example) this generated number will takes some info from the filling fields, for example:
Cars Brand: BMW (catalog number: 120)
Cars Model: 325 (catalog number: 30)
Year: 2008 (catalog number: 18)
So the unique certification number will takes from the catalog numbers * 20 / 5 for example for cars Brand
for Cars Modelo: catalog number *3 + 15
CERTIFICATE NUMBER: 480-105
I'm not sure if this is possible.
I will realy thanks for your help in this issue.
Best Regards
Gian
How can we say whether the SP is successfully compiled or not if we are compiling it on the server as a part of the TSQL script since it does not throw any message like ORACLE does.
In oracle, system will let you know whether the the procedure is successfully complied or not?
Thanks/
Hi
How can I check "Native client" is present on my client? I'm trying to eliminate all the reasons my client won't run a VC++ app which access a sql server.
thanks
Z
Hi.
I have set up my confirmation system to work as thus:
1) user registers
2) registration goes into a temp table with a code
3) user gets a MD5 code in his inbox
4) user clicks back
5) click-back page moves registration data from TEMP table to USER table and sends the user to the signin page.
6) ...
Now, my question is - how do I handle this final step in account
confirmation? Should the first signin act as a final confirmation of
the users account? This makes sense. Should the data in the USER table self-delete after a day if there has been no first sign-in? Should I have a column in the USER table to show if the first login has happened? How should I do this?
I am sure I could mess around with this but it would be great to
get feedback from somebody that has done this multiple times and has a sense of what the best practice is (based on large volume examples).
Your feedback is much apprecaited.
Thanks.
Hello,
I am looking for some confirmation on a behavior of the SSIS Script Task. I have a custom script task that takes an input file, and archives it after it has been processed into the database.
When I run this package in the Visual Studio GUI, if the destination drive is full, it throws an exception telling me that there is not enough disk space. So, my questions are:
1) If this happens when the package is running through the command line, would this exception still be thrown? (I am thinking it will be)
2) Also, Do I need to explicitly fail the script task in the event handler, in order to ensure this .Net exception being thrown will cause the component to fail. (I am fairly certain I do, since this is what I had to do inside of the Visual Studio GUI, but does anyone know if this same behavior would occur when running from the command line?)
Thanks,
Chris
Hello
I have this data in a Access DB of ~4500 posts.
Here is a sample of my problem.
The Name has no ID, it is a simple text field with ~1800 diferent names in it:
Year|Name|
-------------------
2005|NN|
2005|NN|
2005|YY|
2005|XX|
2006|XX|
2006|XX|
2006|XX|
2006|NN|
2006|NN|
2008|NN|
2008|NN|
2008|NN|
I have tried to make a SQL query to show this:
Count of each Name Grouped by year
Year|Name|Cnt
-------------------
2005|NN|2
2005|YY|1
2005|XX|1
2006|XX|3
2006|NN|2
2008|NN|3
and this:
All Name as total count in the DB
|Name|TotalCnt
------------
|NN|7
|XX|4
|YY|1
without any success.
All ideas are welcome at this point since i am stucked.
1)Which statement shows the maximum salary paid in each job category of each department?_______
A. select dept_id, job_cat,max(salary) from employees where salary > max(salary);
B. select dept_id, job_cat,max(salary) from employees group by dept_id,job_cat;
C. select dept_id, job_cat,max(salary) from employees;
D. select dept_id, job_cat,max(salary) from employees group by dept_id;
E. select dept_id, job_cat,max(salary) from employees group by dept_id,job_cat,salary;
2)description of the students table:
sid_id number
start_date date
end_date date
which two function are valid on the start_date column?_________¡£
A. sum(start_date)
B. avg(start_date)
C. count(start_date)
D. avg(start_date,end_date)
E. min(start_date)
F. maximum(start_date)
I would like to pull some data from a SQLServer database, and save it into an Access MDB file (which can be empty to start). I would then zip up the MDB and download it to the user.
I am seeking advice on the most "elegant" or "efficient" way to do this. Here are some ideas I have been considering:
1) Should I start with an empty template MDB and file-copy it before I populate it? Or is there a neat way in ASP.NET to allocate a brand new MDB outright?
2) I could read the SQLServer data into a Dataset object. I could then open a connection to the MDB, create a table object, defining all the columns, etc., and then I could write the data to the new table object. BUT ... I have a hunch there is a nifty ADO.NET way to save the data already in the Dataset object right into the MDB (creating the table and columns as a matter of course) ... all with an instruction or two (or three). Any ideas?
Thanks in advance!
A database with 1 mdf and 2 ldf has been detached from SQL Server 7.0 . Then removed the log files ( they are gone , unable to recover ) and there's no backup at all . Now I want to attach the database with the same mdf , but got error msg - 'Device activation error'. It seems like it's looking for one of the log files.
Is there any way to recover the db ?
I guess NOT , isn't it ?
I don't understand why it doesn't work with sp_attach_single_file_db and sp_attach_db . I actually tested it with a dummy database with 1 log file , and it worked - a new log file was recreated. Thus I performed in production server. Don't understand why it doesn't work.
thanks in advance.
Hello, everyone:
There is a big table with several million records. I am developing a query that retrieve the first rowset that meets WHERE condition. Any suggestions for the fast query? Thanks a lot.
ZYT
I have discovered what looks like a bug in the optimiser. I've posted it at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=288243 but I wonder if any of you with SQL 2005 RTM, 2005 SP1 or 2008 CTP could confirm when this was introduced and whether it is still an issue?
Code Snippet
-- Bug report
-- 2007/07/19
-- Alasdair Cunningham-Smith
-- alasdair at acs-solutions dot co dot uk
set nocount on
go
-- example date in in British date format
set dateformat dmy
go
use tempdb
go
create table foo( bar varchar( 30 ) not null )
go
insert into foo( bar ) values ( 'fishy' )
insert into foo( bar ) values ( '19/07/2007' )
go
-- this works fine in all versions - only valid dates are passed to the convert function
select
convert( smalldatetime, bar, 103 ) as bardate
from
foo
where
bar like '__/__/____'
go
-- this works on SQL 2000, but fails on SQL 2005 SP2 (I've not tried other SPs of SQL 2005):
-- Msg 295, Level 16, State 3, Line 2
-- Conversion failed when converting character string to smalldatetime data type.
--
-- I believe the query is rewritten as if the derived table query contained
-- "and convert( smalldatetime, bar, 103 ) < getdate()"
-- which would expose the convert to the invalid data
select
*
from
(
select
convert( smalldatetime, bar, 103 ) as bardate
from
foo
where
bar like '__/__/____'
) as derived
where
bardate < getdate()
go
-- Workaround:
-- Use a case statement to protect the convert operator from the invalid data
select
*
from
(
select
case when bar like '__/__/____' then
convert( smalldatetime, bar, 103 )
else
null
end as bardate
from
foo
where
bar like '__/__/____'
) as derived
where
bardate < getdate()
go
drop table foo
go
The workaround I discovered is simple but ugly. I invite your comments...
alasdair.
I have the following UNION ALL query with SELECT INTO @tblData temp table. I would like to confirm if my query is correct.
In my first SELECT statement, I have INSERT INTO @tblData.
Do I need another INSERT INTO @tblData again in my second SELECT statement after UNION ALL?
DECLARE @BeginDate as Datetime
DECLARE @EndDate as Datetime
SET @BeginDate = '7/1/2015'
SET @EndDate = '7/13/2015'
DECLARE @tblData table
[Code] ....
Hello,
I am asking a question i have seen many threads on, but I am looking for an idiot's guide on how to convert my SQL 2005 database to SQL 2000 so i can get it to run on my web hosting server. I'm very new to asp.net , but have ahd years of experience in normal HTML and a year or two in the old ASP.
I was advised to learn ASP.Net 2.0 and have found it nothing but brilliant. The intergration with SQL 2005 made it a lot quicker to link up a database than using Access. Unfortunatly my hosting company is a little behind and still using SQL 2000. There isn't mch databse intergration (a few aplication forms) so I dont mind re-writing the whole database but I dont know how to set Visual web developer up with a SQL 2000 Database. I have also read from various other forums that you can convert a databse to 2000 by doing something with the scripts, but the explaination is too complicated for me to follow.
Is there anyone out there who wouldn't mind going over some old ground and explain this all in simple terms? Im using 'SQL Server Managment Studio Express' (although i dont know how to use it) and 'Microsoft visual web developer 2005 express edition' .
Thanks for reading this
Simon
Hello all, I'm new to SSIS and this forum, and this is my first post.
We're migrating a 2000 DTS ETL process to 2005 SSIS. We really like the enhanced functionality of SSIS thus far.
One problem we have with our 2000 process is that runs at 1:00am each morning. The scheduling is done via a distributed scheduling tool called Maestro. Our process pulls data from a mainframe-based DB2 OLTP and reformats it into SQL Server reporting tables. We have nightly mainframe batch processing that updates the DB2 tables, and we need those updates on a nightly basis.
The mainframe batch process starts at 8:00pm each evening. It finishes normally by 1:00am 90% of the time, but it is 20+ years old, and has its share of problems, especially during month-end. The problems can't be resolved until the next business day in some cases.
We'd like to elegantly connect the two processes somehow so the SSIS ETL process kicks off when the mainframe batch process finishes. I intentionally didn't use the word 'trigger' up until this point.
It would not be a problem to modify the mainframe batch process to insert or update a DB2 table that SSIS has access to, but I don't think we can get the mainframe batch process to update SQL Server 2005 tables...?
Any advice would be greatly appreciated--TIA!
John
I have two sites. Site A and Site B
Each site has two databases
Site A
Db1
Db2
Site B
Db1
Db2
Site A Db1 has to perform transaction replication to Site A- Db2 and Site B- Db1 and Db2.
I started Site A as pubisher and distributor and Site A and Site B both as subscriber.
Site B is in a different geographical area (state).
----------
Please suggest the best scenario to save bandwidth and server load for Publisher, and Distributor.
-------
Earlier I thought that I will implement local replication between Site B - in between Db1 and Db2. The Sql Server does not let me set Db1 as publisher, and distributor for its local database Db2.
-------
P.S. My all databases need same transactions though they are connected to different hardware at different places. So please don't question that why I need four similar databases.
Hello everyone, I am upgrading from SQL Server 2000 to SQL Server 2005. Any caveats? Can I just detach the db's and attach them into 2005. Or is there any conversion I should run or import first?
Thanks,
In desperate need of implementing a solution where the customer has purchased a CMS to replace their corporate site and wants to use MSSQL as the DB server type. I have 3 servers allocated to me to complete this and I could use some advice on the best setup.
THey're running windows 2003 standard server along with sql 2000 standard server. The intended plan is for the 2 servers to become web servers with the last server becoming the sql server. The CMS will reside soley on the sql server and the content for the web site on the seb server(s).
What I need to know is if it's possible to set up an active/passive node to accommadate this using the items mentioned above? From what I've been reading sql 2000 enterprise does clustering but I'm hoping this version of sql can be used for something.
Any responses are appreciated.
Hi,I am seeking the help of volunteers to test some software that I'vedeveloped which facilitates distributed two-phase commit transactions,encompassing any resource manager (e.g. SQL/Server or Oracle) controlled byMicrosoft's Distributed Transaction Coordinator in a Windows2000environment, with any resource manager under the control of DECdtm (e.g. Rdb(or Oracle via the XA Veneer)) in a VMS environment.[Yes, at some stage, I hope to sell this software and make money out of it,so unless you have a large philanthropic streak or are simply a techie wholikes to stay on top of Windows<->VMS connectivity issues, then you may wishto look away now. But if you do choose to participate, then rest assuredthat I have no interest in your personal or company details. (Just yourwork-rate :-)]What differentiates my Transaction Manager software from existingTransaction Monitor packages that are already in the marketplace (and whyyou should be interested) is that it is based on the Transaction InternetProtocol TIP standard. (RFC 2372) For those of you who don't know, thebeauty of TIP's "Two-Pipe" strategy is it's application-pipe (or middleware)neutrality. Whereas most XA implementations mandate homogenous TransactionMonitor deployments (such as Tuxedo everywhere, Encina everywhere, MQSerieseverywhere, ACMSxp everywhere and so on . . .), hotTIP from TIER3 Softwaregives you complete freedom to choose the middleware product(s) that bestsuite your particular application and heterogeneous network needs.Would you like to talk to VMS with TIER3 Sockets, COM or DCE/RPC? BEAMessageQ, IBM MQSeries or HTML? The choice is yours and yours alone. Butonce you realize that you need to encase your critical transactions withinthe ACID properties of a true Heterogeneous Two-Phase Commit then you willcome to the conclusion that you need a Transaction Manager that looks a lotlike this.Another drawback of traditional "One-Pipe" strategies is that they precludethe run-time determination of transaction participants. (Functionalitywhich may be advantageous in a wide-area or Internet based application.)Anyway, this is what I have: -On the Windows side, you need absolutely *NO* additional software! I'llreply to this note with a brief description of the COM+ and DTC functionsthat you would need to invoke in order to successfully push a MTS/DTCtransaction to VMS. NB: These are standard Windows APIs that are fullydocumented on MSDN.On the VMS side, I have a VMSINSTAL saveset that (all zipped up) is some150KB that I'm happy to e-mail to you along similar lines to the VMShobbyists (non-commercial use) license. I'll reply to this note with anInternet Daemon (INETd) example of code that uses my software to cedetransactional control, over an SQL insert into a Rdb database, to MTS/DTC.It's under 500 lines long and contains all of the DCL, 3GL, SQL required toproduce a working example of a TIP-2PC capable TCP/IP auxiliary server. Thisexample will insert a row into the MF_PERSONNEL.Employees table on the VMSside in co-operation with Windows2000 MTS/DTC client that is inserting a rowinto the NORTHWIND.Employee table. Commit them all or roll them all back.So, in summary, If you'd like to volunteer to put hotTIP through it's pacesthen simply reply to this mail.Regards Richard MaherPS. The following are a few functionality restrictions with the currentversion of my software that may effect your decision to participate: -1) Transaction has to be started/mastered/coordinated by W2K MTS/DTC2) Transactions cannot be PULLed from VMS and must be PUSHed from W2K3) No cluster-wide recovery.(If a txn falls over after being prepared then you have to wait for thatspecific node to become contactable again even though that lovely RDMrecovery job is sitting on another node protecting the database until myhotTIP TM tells it to commit or abort.)4) There is currently no Alpha or Itanium version available. The Alpha portis currently in progress but, for the time being, you'll either need a VAXor a VAX emulator on your PC.
View 7 Replies View RelatedI read somewhere that market basket analysis finds rules with substitutes as likely as rules with complements due to a consumer behavior called "horizontal variety seeking". This is when customers buy more than one product in the same category even though they are subsitutes. For example, when people go to the grocery store and buy soda, they buy coke and sprite at the same time even though they are substitutes of each other. I was wondering if anyone has experience with this anomaly and how they solved it. I found a time series model called the vector autoregressive model which is used to find the elasticity of prices over a time period. Does anyone have experience working with the VAR model? I am having trouble figuring out what some of the variables in the model are.
Below is the paper
http://www.feb.ugent.be/fac/research/WP/Papers/wp_04_262.pdf#search='VAR%20model%20market%20basket%20analysis'
Hi. We've decided to convert our Crystal Reports to SSRS 2005. We know (thanks to this forum) there are companies that will convert the reports at a cost; however, we'd like to undertake this ourselves. Are there resources you can point us to that might be specific for Crystal Reports users coming over to SSRS, especially for newbies? Thank you.
View 3 Replies View RelatedI'm a complete newbie. Need to insert a Company logo into a databasecolumn to use later on in a check printing application. Read how toinsert the pointer instead of the object into the column. Below iswhat I did:SET QUOTED_IDENTIFIER OFFGOINSERT INTO BankInfo(CoLogo) VALUES(0xFFFFFFFF)***Then I did this****DECLARE @Pointer_Value varbinary(16)Select @Pointer_Value = TEXTPTR(CoLogo)FROM BankInfoWHERE CMCo = '91'WRITETEXT BankInfo.CoLogo @Pointer_Value"\192.31.82.77DataCheckImagesWyattLogo.jpg"****This was straight out of a book and it seemed to work it gave me amessage that it was successful and when I view the data in the columnI can see the pointer0x453A5C436865636B496D616765735C57796174744C6F676F 2E6A7067*****But when I try to use the column in either Crytal Report or an AccessReport the Bank Logo does not show up. I also placed the logo on my Cdrive and tried pointing to it there with "C:WyattLogo.jpg" with nosuccess.It can't be this difficult to get a Company logo into a column. Idesperately need assistance. Remember I am the ultimate newbie. Ilooked at my first sql database last week. Thanks in advance for anyhelp, it is appreciated.
View 1 Replies View RelatedHello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!
View 7 Replies View Related
I'm trying to ascertain how I can find out more about a particular job.
The information I have from a script I have to identify deadlock root causes gave me back this information:
spid 86 is blocking spid 51... spid 86 info: SQLAgent - TSQL JobStep (Job 0xBAD836E3D331B44BA4CCAC400D244B17 : Step 1)
Well, that's good to know, but I would like to be able to identify the particular job that 'owns' TSQL JobStep (Job 0xBAD836E3D331B44BA4CCAC400D244B17 : Step 1).
I've read the BOL on the sysjob type tables; and, while they tell me about the columns in the tables and what they are, they tell me absolutely nothing about how one goes about figureing out what I want to know.
I suspect one problem I have is that '0xBAD836E3D331B44BA4CCAC400D244B17' needs converting to something else and I have no idea how to go about doing this. I was never that good at converting hex (I assume that is what this is) when I was doing it rather often, which is years ago, so I really have no idea how to start.
Can anyone throw me a lifeline here?
Tia
randyvol
I've run into a problem with SSIS packages wherein tasks that write or copy files, or create or delete directories, quit execution without any hint of an error nor a failure message, when called from an ASP.NET 2.0 application running on any other machine than the one where the package was created from. By all indications it appeared to be an identity/permissions problem.
Our application involves a separate web server and database server. Both have SQL Server 2005 installed, but the application server originally only had Integration services. The packages are file system-deployed on the application server, and are called using Microsoft.SqlServer.Dts.Runtime methods. For all packages that involve file system tasks, the above problem occurs.
When the above packages are run using the command prompt (either DTEXEC or DTEXECUI) the packages execute just fine. This is expected since we are using an administrative account. However when a ShellExecute of the same command is called from ASP.NET, the same problem occurs.
I've tried giving administrative permissions to the ASPNET worker process user to no avail.
I have likewise attempted to use the SQL Server Agent job approach but that approach might not be acceptable for our clients since it means installing SQL Server 2005 Database services on the application server.
I have read the relevant threads in this forum, namely http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1044739&SiteID=1 and http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=927084&SiteID=1 but failed to find any solution appropriate for our set up.
Anybody got any idea on how to go about this?
We manage some SSIS servers, which has only SSIS and SSIS tools installed on them and not the sql server DB.
SSIS packages and configuration files are deployed on a NAS. We run the SSIS packages through DTEXEC by logging in to the server.
We want to allow developers to run their packages on their own on the server, but at the same time we dont want to give them physical access on the server i.e we do not want to add them into RDP users list on server properties. We want them to allow running their packages remotely on the server.
One way We could think of is by using powershell remoting and we are working on that. But is there any other way or any tool already present for the same.
Hi all
Our data management system currently runs DTS packages using DTSPKG.dll.
I am currently looking at the possibliity of replacing the DTS packages and SQL 2000 with DTSX packages using SSIS in SQL 2005.
Do I need a new dll? or will the current dtspkg.dll handle the new DTSX packages?
Many thanks in advance!