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' .
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?
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.
I'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.
Hello - does anyone have experience w/SQL Server 2005 in a virtual environment? I'm considering this for a production environment but not sure if performance will suffer. Our databases will have a lot of writing but not too much reading. A SSRS solution is currently the only app. connecting to the SQL db. Max users to server at any given time will be very low (~10 users max). But the databases are pulling in data from other, outside multiple data sources on a daily basis.
We are about to migrate just over 30 databases from a SQL server 7 installation to a SQL 2000 on a different server. Each database has it's own logins.
What is the best, and most fool-proof path to take? I know from previous experience that restoring a single database works fine, but that the logins are orphaned. Are there complications with restoring the master database from the SQL 7 machine on the SQL 2000 machine? Any advice would be helpful as this is bound to be a painful process. ;-)
Hello,I'm looking into offering a custom data driven web app that I wrote for an organization that I'm apart of to other similar organizations. I would be hosting the data and web application code on my dedicated server. This application is using the membership api supplied in .NET 2.0 and also has my own custom data tables within it.My question is what would be the best way to add clients to this? Should I simply create a new database for each new client like so: ACME_Database, ABC_Database, AAA_Database etc. Or should I add some sort of client "Tag" (tag meaning column within each datatable) to these databases and then update my SQL queries to process them accordingly. I imagine I could do both but I guess I need some advice from people that already had experiance with providing this kind of service. Thanks!Jason
It's arriving from ebuyer tomorrow for 32 quid. I already have aninstance of MSDE running on the laptop and ideally i would like SQLserver to be installed and use the current instance and not installany other additional services. [hope i got my terminology correcthere.] Basically all i would like is to use the Enterprise Managerwith my existing MSDE database. I was wondering if there is anything ishould be doing before I install. Should i shut down the service orleave it running for instance.Thanks for any tips,AndyB
Hey all - been knocking my head on this on for awhile - perhaps someone could shed a little light. I have 2 tables that I want to link: Table 1: UserID - Primary key First Name Last name AuditionID - FK
Table 2: AuditionID - PK Description Sing instrument
So - I have a web form that collects the information for both tables and then insets the collected data. But, the AuditionID value in Table1 does not update. Now, I assume I am incorrect in thinking the AuditionID in Table1 will be automagically updated when Table2 creates it's key...? If that is indeed the case, how do you update 2 tables and maintain a foreign key relationship?
I hope I am being clear enough - any help would be greatly appreciated.
I'm running a (phpBB) forum which uses a MYSQL data base - I can access the database via a webpage through my ISP and it lets me run short scripts (through a web interface). I need to do the following (shown in psuedo code below). I would be eternally grateful if someone could translate it into pure SQL commands for me to paste into my SQL quiery input field on the web page I'm using to access my database. (I'm a complete SQL novice)
===================== /* I need to be sure these commands will do nothing if 'jeff' does not exist in the user table */
/* extract the id value for the user called 'jeff' from the users table */ user = GET id FROM users WHERE username = "jeff"
/* remove all entries from the forums_watch and topics_watch tables that contain Jeff's userID */ REMOVE ALL ENTRIES WHERE userID = user
notify = 0;
/* insert many entries into the topics_watch table, one for each topic that exists in the topic table */ arrayOfTopicIDs = GET ALL id FROM topics FOR EACH current_Topic IN arrayOfTopicIDs { INSERT INTO topics_watch (topic_id, user_id, notify_status) values (current_Topic, user, notify) }
/* insert many entries for jeff into the forums_watch table, one entry for each forum that exists in the forum table */ arrayOfForumIDs = GET ALL id FROM forums FOR each current_Forum in arrayOfTopicIDs { INSERT INTO forums_watch (topic_id, user_id, notify_status) values (current_Forum, user, notify) }
=====================
I'm going to implement this with php and MYSQL as a mod for my forum application (phpBB) - but I could really do with a way of doing this immediately with pure SQL commands as a quick and dirty one shot paste into the input field of the MYSQL database web interface until I get the php mod going.
Can this be done using only SQL commands without tedious repetition?
How do you work with an xml file that resides in a database column using IS XML related features? From what i understand so far, the path cannot point to a column within a database but only to a source outside of the database. Am I correct?
I'm sure this problem borders on retarded for most readers of this form, but here it is:
I have table with contest and another table with user ratings on those contests.
Contests: ContestID, CreatorUserID, Title Ratings: ContestID, UserID, Rating
If I
SELECT Contests.*, Ratings.* FROM Contests INNER JOIN Ratings ON Contests.ContestID = Ratings.ContestID WHERE (Ratings.UserID = '1')
This only returns the contests that User 1 has rated. But I need all contests. If the user hasn't rated one, I want the result set to show Rating = NULL. The problem is that if a user hasn't rated a contest, that ContestID does not exist in the Ratings table.
seems like it should be a simple ALTER TABLE query but im getting a 'Incorrect syntax near the keyword 'column'.' error. Trying to add a boolean column, heres the script, any help would apprectiated.
ALTER TABLE SHIPPING_ADDRESS ADD COLUMN isUpdated bit,
What is the common mechanism (if any) to make the server notify aclient when some condition is met (say, upon completion of asql-transaction)? The only way to implement this feature I am aware ofnow is to set up a timer and query the server every once in a whilebut surely this technique isn't much good.
I'm creating a website for a student organization at my university. Basically, I have an Excel file with a list of contacts that the members of our organization need to be able to search on our website.
The only field that I need our members to be able to search is "contact location." So for example, a member might want to know if our organization has any contacts in Spain, so the member would type "spain" into the "contact location search" field" and would get results.
I really don't known where to turn... I need the easiest thing that would be manageable for a novice. It doesn't need to be an intense search function; just something to make the website functional (so it obviously needs to be uploadable to a server; the server has PHP capability...no idea if that has any relevance). I've been searching around all day and have come across potentials like PHP, SQL, XML, etc. but have no idea which way to turn... I have Office and Adobe Studio programs at my disposal.
Thank you all-powerful and all-knowing web gods! I owe you my deepest gratitude!
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
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)
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 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?
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.
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.
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...?
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.
Hi, i was after some advice on moving a SQL Server 2000 database from one server to another.
Usually i would do this by backing up the database on the original server the copying it accross the LAN to the new server and restoring it there. This database is 10Gb in size and copying it accross the LAN will take some time and i would like to minimise downtime if possible. The database is at a customer site where i am not responsible for the network or Hardware.
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.
I 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.
Hello,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!!!!
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.
I'm having a problem finding the nearest age. If the last bday is closer then the current age, if the next bday is closer then current age + 1 year. I'm familar with dateadd, datediff and datepart. Here's some psudo code...
declare @bday datetime ,@age int set @bday = '01/26/1956' set @thisdate = getdate() set @age = case when next @bday is closer than the past birthdate then age(@bday) + 1yr when next @bday is farther than past @bday then age(@bday) end
I know there is not an age() function, but that's part of what I'm looking for. I'm also stumped on getting the nearest age. What I had only worked for part of the year, prior to around June 15th, (183 days into the year)
Hello Which is better and faster?? and WHY????Writing Select Statement with joins in Stored procedure, or creating view and calling it from stored procedure (select * from view)..
I am not entirely ignorant to web technologies, and best practices but i am having a bit of a planning dylema.
My company has a well established SQL 2000 database with windows application which has been created by myself, what i am planning on doing is creating a web site, using asp.net and publishing some of the information, so that our clients may use it, and stop pestering us on the phone. what i would like to know is what would be the best way forward, obviously i don't want to show them all our information, and don't want to put 5Gb worth of data onto a ISP website. What would you suggest i do?
DECLARE @returnDay int SELECT @returnDay = DatePart(day,GetDate()) If @returnDay = 8 BEGIN select * from Hospitals left join Units ON Units.HospitalID = Hospitals.HospitalID where Units.HospitalID is null RETURN END
this is just a part of the procedure I am trying to create, I am getting hospitals that haven't submitted any data and wish to send them an email.
on the other hand I have two tables that have all the data for emailing to hospitals but are not linked to tables giving the list of hospitals
I have been advised to create a cursor(easier said then done) that will go through my list of records that need to receive an email
nothing going very well with that at the moment.
so I was hoping to see if somebody has any other suggestions for me.....
