SQLServerCentral / SQL Server 2005 / SQL Server 2005 General Discussion

ERwin

marjanagha — Sat, 17 May 2008 01:29:21 GMT

Hi there, I've been searching quite a bit online to find a tutorial for ERwin that would take me through the steps of the data modeling. I have seen basic tutorials, but I'm wondering if there is a complete guide to ERwin? Or a tutorial that goes deeper in the modeling ER and E-ER Diagrams, and relations. I would greatly appreciate your help. Thank you.

Creating and Dropping a View

Mybowlcut — Fri, 16 May 2008 21:04:43 GMT

Hello again.I've got some code that is supposed to create a view:[code]-- Mitch Curtis-- A2view.sql-- Set the active database to KWEA.USE KWEA;-- Drop view.DROP VIEW Property_Owner_Table;CREATE VIEW Property_Owner_TableAS SELECT owner#, nameFROM Property_Owner;[/code]But it gives me this error:[code]Msg 111, Level 15, State 1, Line 7'CREATE VIEW' must be the first statement in a query batch.[/code]So I change the code to this (even though now I can't implicitly ensure I'm working on the right database):[code]-- Mitch Curtis-- A2view.sql-- Drop view.DROP VIEW Property_Owner_Table;CREATE VIEW Property_Owner_TableAS SELECT owner#, nameFROM Property_Owner;[/code]But it gives me the same error:[code]Msg 111, Level 15, State 1, Line 7'CREATE VIEW' must be the first statement in a query batch.[/code]So I change it again![code]-- Mitch Curtis-- A2view.sqlCREATE VIEW Property_Owner_TableAS SELECT owner#, nameFROM Property_Owner;[/code]It works now... the only problem is that now the code can't be executed multiple times which is a requirement of the problem.Note that another requirement of the problem is that the code be contained within a single query file.How can I do this? :crazy:Cheers.

Foreign Key References Invalid Table

Mybowlcut — Thu, 15 May 2008 21:42:16 GMT

Hey.I'm trying to create some tables in my database but I'm getting some errors... The one which is causing the most trouble is [code]Msg 1767, Level 16, State 0, Line 38Foreign key 'ten_fk' references invalid table 'Tenant'.[/code]I'm not sure why it's complaining... can anyone help me out here? I've also posted this on SQL Team's forums but haven't got a reply yet.Cheers![code]-- Mitch Curtis-- A2create.sql-- Set the active database to KWEA.USE KWEA;-- Drop existing tables (if any).DROP TABLE Ownership;DROP TABLE Tenant;DROP TABLE Staff;DROP TABLE Property;DROP TABLE Property_Status_Report;DROP TABLE Property_Owner;DROP TABLE Placement_Record;DROP TABLE Candidate_Tenant;DROP TABLE Waiting_List;-- Create new tables.CREATE TABLE Waiting_List( waiting# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, candidate_name VARCHAR(20) NOT NULL, anticipated_start_date SMALLDATETIME NULL, anticipated_end_date SMALLDATETIME NULL, max_affordable_rent SMALLMONEY NOT NULL);CREATE TABLE Candidate_Tenant( candidate# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, waiting# INT NULL, name VARCHAR(20) NOT NULL, phone_number INT NOT NULL, required_property_type VARCHAR(10) NOT NULL, CONSTRAINT w_fk FOREIGN KEY(waiting#) REFERENCES Waiting_List(waiting#));CREATE TABLE Placement_Record( opening# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, tenant# INT NOT NULL, start_date SMALLDATETIME NOT NULL, end_date SMALLDATETIME NOT NULL, total_bonds SMALLMONEY NOT NULL, weekly_rent SMALLMONEY NOT NULL, CONSTRAINT ten_fk FOREIGN KEY(tenant#) REFERENCES Tenant(tenant#));CREATE TABLE Property_Owner( owner# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, name VARCHAR(20) NOT NULL, phone_number INT NOT NULL);CREATE TABLE Property_Status_Report( address VARCHAR(30) NOT NULL, report_date SMALLDATETIME NOT NULL, weekly_rent SMALLMONEY NOT NULL, month_rent_start_date SMALLDATETIME NOT NULL, month_rent_end_date SMALLDATETIME NOT NULL, maintenance_fee SMALLMONEY NOT NULL, month_inspection_history VARCHAR(30) NULL, CONSTRAINT ar_pk PRIMARY KEY(address, report_date), FOREIGN KEY(address) REFERENCES Property(address));CREATE TABLE Property( address VARCHAR(30) PRIMARY KEY NOT NULL, staff# INT IDENTITY(1,1) NOT NULL, type VARCHAR NOT NULL, occupant_limit INT NOT NULL, comments VARCHAR(30) NULL, FOREIGN KEY(staff#) REFERENCES Staff(staff#));CREATE TABLE Staff( staff# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, manager# INT NOT NULL, name VARCHAR(20) NOT NULL, FOREIGN KEY(manager#) REFERENCES Staff(staff#));CREATE TABLE Tenant( tenant# INT IDENTITY(1,1) PRIMARY KEY NOT NULL, staff# INT NOT NULL, property_address VARCHAR(30) NOT NULL, name VARCHAR(20) NOT NULL, phone_number INT NOT NULL, street VARCHAR(20) NOT NULL, city VARCHAR(20) NOT NULL, postcode INT NOT NULL, category VARCHAR(10) NOT NULL, comments VARCHAR(30) NULL, FOREIGN KEY(staff#) REFERENCES Staff(staff#), FOREIGN KEY(property_address) REFERENCES Property(address));CREATE TABLE Ownership( address VARCHAR(30) NOT NULL, owner# INT NOT NULL, CONSTRAINT ao_pk PRIMARY KEY(address, owner#), FOREIGN KEY(address) REFERENCES Property(address), FOREIGN KEY(owner#) REFERENCES Property_Owner(owner#));-- Display tables.SELECT * FROM Waiting_List;SELECT * FROM Candidate_Tenant;SELECT * FROM Placement_Record;SELECT * FROM Property_Owner;SELECT * FROM Property_Status_Report;SELECT * FROM Property;SELECT * FROM Staff;SELECT * FROM Tenant;SELECT * FROM Ownership;[/code]Errors:[code]Msg 3701, Level 11, State 5, Line 8Cannot drop the table 'Ownership', because it does not exist or you do not have permission.Msg 3701, Level 11, State 5, Line 9Cannot drop the table 'Tenant', because it does not exist or you do not have permission.Msg 3701, Level 11, State 5, Line 10Cannot drop the table 'Staff', because it does not exist or you do not have permission.Msg 3701, Level 11, State 5, Line 11Cannot drop the table 'Property', because it does not exist or you do not have permission.Msg 3701, Level 11, State 5, Line 12Cannot drop the table 'Property_Status_Report', because it does not exist or you do not have permission.Msg 3701, Level 11, State 5, Line 13Cannot drop the table 'Property_Owner', because it does not exist or you do not have permission.Msg 3701, Level 11, State 5, Line 14Cannot drop the table 'Placement_Record', because it does not exist or you do not have permission.Msg 1767, Level 16, State 0, Line 38Foreign key 'ten_fk' references invalid table 'Tenant'.Msg 1750, Level 16, State 0, Line 38Could not create constraint. See previous errors.[/code]

Drop a ##tmp table if it exists otherwise create it.

kp_it_simpl — Fri, 16 May 2008 11:03:33 GMT

Here is what I am doing and obviously it never does the Else if the table does not exist with rows in it!!If exists (select top 1 * from ##tmp_mj_jobSync) Drop table ##tmp_mj_jobSyncElse Select * into ##tmp_mj_jobSync from mj_jobSyncWhat can I replace "If exists (select top 1 * from ##tmp_mj_jobSync)" so that the Else statement will run????????????

Trouble with subquery

silentfreelancer — Fri, 16 May 2008 00:08:40 GMT

I'm not sure if I'm posting this in the correct place but I'm taking a course about SQL. My current assignment involves creating a query that pulls a list of employees and their supervisor from the Northwind database. The query has to include a subquery. I have been able to develop a query that pulls all of the employees except the CEO, which is Andrew Fuller. Anyone have any ideas as to what I'm missing?[code]SELECT Employees.LastName, Employees.FirstName, [Supervisors].FirstName AS ManagerFirstName, [Supervisors].LastName AS ManagerLastNameFROM Employees, Employees AS Supervisors WHERE Employees.ReportsTo IN(SELECT ReportsTo FROM Employees WHERE ReportsTo = [Supervisors].EmployeeId);[/code]

Profiler90

Simon — Mon, 04 Feb 2008 21:05:54 GMT

A very simple way of scheduling profiler to run and stop at certain date time would be via the command prompt. I'm basically looking for a technical solution (quick and dirty, that is), not reasoning why i'm doing it this way.My question is very simple, as M$ allows an input argument to be specified for trace stop time with "/M", but i just cant seem to get it working.The code i'm running in command prompt is as below.profiler90 /S (local) /O c:\trace.trc /E /M "02-06-08 03:30:00""02-06-08 03:30:00" = feb, 06 2008 3.30AMWhen checking the launched profiler properties, it doesnt check the trace stop date time and will continue to trace all activities on the server. I've been scratching my head here figuring what's wrong. I was kind of expecting the trace to run from the moment i ran the command and stops automatically at feb, 06 2008 3.30AM, but it doesnt.By the way, profiler90 doesnt allow scripting as its predecessor, profiler.Thanks in advance for helping out there. Best regards,Simon

Triggers

somaprabhamv — Fri, 16 May 2008 15:18:13 GMT

Hi All,Can we have a trigger for two tables..? If so How to query it...ThanksSoma

Anyone else's Studio slow to launch lately?

Oblio Leitch — Thu, 15 May 2008 12:31:58 GMT

Had a round of updates the other day and now Studio takes several minutes to launch. Anyone else notice this? Any suggestions/fixes? :unsure:

Job failed after upgrade to SQL 2005

iragam.nagarjuna — Fri, 16 May 2008 03:13:14 GMT

Hi,After upgraded to sql 2005, Job is failing with an error message - "Cannot drop the table '#temp_procob', because it does not exist or you do not have permission", though the code is checking for whether temp table is exists in database before executing drop statement. Please find the below copied code for your reference.If exists (select 1 from tempdb..sysobjects where name like '#temp_procob%') DROP TABLE #temp_procobThis is happening in production server where temp database is created on three different drives. All these drives are having full rights to drop or create or delete the temp tables.When we ran the same job (Stored procedure) in query analyzer, it is running successfully. Can anyone help how to solve this issue.

Weird SSMS Exception Error

Brandie Tarvin — Fri, 16 May 2008 07:22:01 GMT

I was running a query, getting results, then cancelled the query part way through. The cancel threw the following Exception, only I've never seen this error before and Google has nothing on it. Has anyone ever seen this message before?If so, do you know what it means?An error occurred while executing batch. Error message is: Exception of type 'Microsoft.SqlServer.Management.UI.Grid.StorageDataReader+StorageAbortedException' was thrown.

User does not see connection in Management Studio

Danielle Reiser — Fri, 16 May 2008 09:29:18 GMT

I have a user that is trying to use the Management Studio tool to connect to a database server. When he clicks on the connect and puts in the server, it shows on the left hand side of the screen but it does not show the green arrow indicating he is connected. He can run a query against tables, but when he expands stored procedures in the object explorer he doesn't see anything. Has anyone come across this problem? I ensured that he was not a member of any group that has the login disabled and ensured he had data reader permissions to the database.

Triggers are Not Generated from Installer

mojosound — Fri, 16 May 2008 13:15:53 GMT

I am working on an application that can connect to a SQL Server database (2000 or 2005).Each table has an insert trigger that fires that will copy the data from the database optimized for writing to the database optimized for reading after a fixed number of records are inserted. If the SQL Server is installed on the same PC that the application is being installed then all tables and triggers are created. If the SQL server is on a remote PC (login with sa login) the tables are created but the triggers are not.I have tried to google this but have had no luck. Any ideas?

Right forum for SCOM SQL questions?

Marios Philippopoulos — Fri, 16 May 2008 10:21:09 GMT

Is this the right forum for questions related to SCOM as it applies to SQL Server monitoring?I have been assigned a project for creating monitors and alerts on SQL blocking, deadlocks, large table scans, database file expansion etc.Anyone have any suggestions on resources, links etc.?Any experiences anyone would like to share on the subject?

How to delete all data from MOST tables in a database without constraint conlicts

suzanne.dorman — Fri, 16 May 2008 09:42:08 GMT

I am a Java developer, not a DBA. I am trying to delete all data from almost all of the tables in a particular database. We have used osql and an input script in the past. The script just said 'DELETE FROM ...' for each table and was ordered appropriately to avoid constraint conflicts. This has become difficult to maintain because we frequently add/delete tables. I would like something more dynamic. I want to find all of the tables in the database and delete the contents of each EXCEPT for a certain set. In addition, I need the delete to not error on the constraints.I found this link that has a script to do what I want but there is no way to exclude a set of tables:http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PurgeAllTablesNot being a procedure person, I don't know how to modify this to make the exclusions work.I was successful in writing a small script that found every table and created the 'DELETE FROM' script for me. But I ran into the constraint problem. I would have to put each 'DELETE FROM' statement in a particular order to avoid the constraints. Any ideas on how to make this work? Thanks in advance.

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection

ritesh.saluja — Fri, 16 May 2008 09:31:07 GMT

I am executing stored procedure using linked server and getting error"Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection"I've change my procedure and includedSET ANSI_WARNINGS ONSET ANSI_NULLS ONbut then i am getting error "Divide by zero error encountered."The procedure works fine without linked server. Could you please suggest how to avoid error "Divide by zero error encountered."

DDL triggers not catching "new" database restore

Brian Smith — Fri, 16 May 2008 09:33:33 GMT

We are just starting to implement DDL triggers for added visibility in our SQL 2005 environment. And while they will catch when a brand new database is created, when we restore an existing database as a newly named database (this happens quite often in our Dev environment) the triggers are not catching that activity. This seemed strange to us, since we assumed that SQL had to create the database first, which would then be caught by the DDL trigger. But from using SQL Profiler, it appears that SQL is somehow modifying the registry to say "the server will be expecting these [new] files to exist in this location" and then circumventing the CREATE step and going straight to the RESTORE step.Has anyone else encountered this? Is there an easy way around it? I know we can add the "backup and restore" trace audit, but then we have to dig through the legitimate backup and restore actions to get at the actions where developers are just creating new databases willynilly. I have also heard that DDL auditing has gotten much better in SQL 2008; unfortunately, we're not that "bleeding edge" yet.

Inplace Upgrade of 2005 from 2000

Graeme100 — Fri, 16 May 2008 03:36:12 GMT

Hi,Not sure if this is in the right place, but here goes.We have decided to move up to SQL 2005..which is fine...looking forward to it.However, after talks of a side by side upgrade, it has been decided, due to financial constraints...(yeah same old story) that we must do an inplace upgrade.I've read another post on this site that suggested the inplace is very 'brittle' and that there are issues with service pack upgrades etc.Can anyone tell me if this is still the case?Has anyone done an inplace upgrade?My plan was strip the servers back to just data, no indexes or keys, DTS, replication, FTIs or jobs. Upgrade essentially just the database engine with simple databases, and then build up the system from there.Anyone have any thoughts on this?Any comments would be gratefully received.Many ThanksGraeme

View column datatypes

Matthew Creasey — Thu, 15 May 2008 20:28:57 GMT

How does SQL 2005 decide what datatype to give a column?I want SQL to use unicode data types, but it uses varchar instead of nvarchar for the view.Can I force it to use nvarchar?

Problem with installing Report Manager

FreeHansje — Fri, 16 May 2008 06:31:56 GMT

I have an installed SQL Server 2005 with all features, includng Reporting Services. However, I cannot start Report Manager; I receive the message, that it's not available. So I decide to install again and see if I can install Report Manager alone. And it seems I can: I open reporting services, click Report Manager on option: WIll be installed on harddisk. I see that the complete Reporting services is checked for installation and wonder about that, because Reporting Services IS installed and correctly(I think) configured.When I click NEXT I receive the message, that Reporting Services is already installed and I cannot install it again.What can be wrong here, or what am I missing?

Error : Could not allocate ancillary table for view or function resolution

nadiabisher — Fri, 16 May 2008 05:47:44 GMT

Hello everyone! I need a little help with a query I am trying to get to work... I am writing a an application in visual studio 2005. I have some attributes to be retrieved , these attributes are selected from different Views ( 5 Views ) and each view represents a union of tables from different 23 databases : As as exampleCreate View View_1ASSelect * From DB1..Table1UNIONSelect * From DB2..Table1... Select * From DB23..Table1I have 5 views created in this way , so any join between these 5 views will generate the following error :Server: Msg 4414, Level 16, State 1, Line 1Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.Is there any solution , knowing that i can't change the structure of my databases or views ??I appreciate your help.Nadia

UPDATE..FROM doesn't seem to work in SQL 2005!!!!

Emma Watson — Fri, 16 May 2008 02:39:22 GMT

Does anyone know if the T-SQL UPDATE..FROM query type has changed between the 2000 and 2005 versions? I have always used the following type of syntax for updating a table from another one based on a common field: UPDATE Table1SET Field2 = b.Field2FROM Table1 a, Table2 bWHERE a.Field1 = b.Field1 This would normally set the value of Field2 in Table1, to be the same as that in Table2, where the values of Field1 match. However, after running exactly the same script in SQL Server 2005, instead of this happening, the value of Field2 was set to the same value for every single row! It's almost as if it has ignored the WHERE clause. Can anyone shed any light on why this statement is now processed differently? Is this a brand new version of T-SQL? And is UPDATE..FROM not safe to use anymore? Any help greatly appreciated...

Stored procedure

sarchen21 — Thu, 15 May 2008 15:31:57 GMT

How to use a created stored procedure to get the records of a tempporary table and those records containing the constant value(unique G) in a point table.

Error : Could not allocate ancillary table for view or function resolution

nadiabisher — Fri, 16 May 2008 05:48:02 GMT

Accessing a named instance with local or computer's name

meixner — Fri, 16 May 2008 00:11:48 GMT

Hi all,we are using SQL Server 2005 Express for a local desktop application running on about 100 different laptops (with Windows XP).We install a named instance of SQL Server 2005 Express with name ABN. We access the sql server by the service name (local)\ABN which works on most machines.On two or three machines however we get the message that there is no such service. When we access the service by changing (local) by the actual computer’s name of the laptop it suddenly works. For example, (local)\ABN doesn’t work, MEIXISCOMP\ABN works, with MEIXISCOMP being my computer’s name.I’m pretty confused here. Is there some “Windows switch” or some option in the installation process of SQL Server? I would be grateful if someone had some insights.Best regardsAndreas

Strange Integrity check problem

eason36 — Thu, 15 May 2008 21:54:53 GMT

Hi gurus:I met a very strange problem recently. I set up a database integrity check maintenance plan. But this job failed every time. I looked into the logs, the error message was that [color=red]Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped[/color]. I used the sp_helpdb to check the version of the databases included in my maintenance plan. The sp result shows that all the databases are above version 80....Even more strange, i can successfully run the dbcc check query on each database. Any comment and suggestion will be very appreciated.

Problems Starting & Stopping Services Remotely using xp_cmdshell

John Carlson — Tue, 13 May 2008 13:18:56 GMT

I am attempting to use xp_cmdshell to start and stop a remote application server in a job. When executing the following:[b]exec xp_cmdshell 'sc \\MYSERVER stop MYSERVICE'[/b]I get the following error[font="Courier New"][SC] OpenService FAILED 5:Access is denied.[/font]When executing just the command to query the service:[b]exec xp_cmdshell 'sc \\MYSERVER query MYSERVICE'[/b]the query works fine, and it gives me the current state of the service:[font="Courier New"]SERVICE_NAME: MYSERVICE TYPE : 10 WIN32_OWN_PROCESS STATE : 4 RUNNING (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN) WIN32_EXIT_CODE : 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x0[/font]Just to add more relevant information, the account that the SQLServer & Services runs on is a power user on the application server, so it does have authority to start and stop services. As a matter of fact, if I open up a Command Prompt on my SQL Server, I can manually issue the SC commands I show above, and can START and STOP the services just fine.[b]Does anyone have any idea why I would get the access denied error because of running from xp_cmdshell?[/b](Also, just to clarify, I am logged in to SSMS with Windows Authentication using the account that starts the SQL Server and Agent services.)

About trigger

Sharul Nizam — Thu, 15 May 2008 12:09:41 GMT

I've have following table,tblHIT - this table will reveive record every minutesDate | Time | Main_ID | Hit------------------------------------3/1/2006 | 100 | 200 | 8700.........4/14/2008 | 100 | 200 | 45004/14/2008 | 100 | 201 | 87004/14/2008 | 200 | 200 | 35004/14/2008 | 300 | 201 | 7700......I've also 1 job in SQL Server Agent will execute STORED PROCEDURE and will filter 7 days record before from current date and insert it into tblHIT_7days. tblHIT_7days shown as followtblHIT_7daysDate | Time | Main_ID | Hit------------------------------------3/7/2008 | 100 | 200 | 8700.........4/13/2008 | 100 | 200 | 45004/13/2008 | 100 | 201 | 87004/13/2008 | 200 | 200 | 35004/13/2008 | 300 | 201 | 7700I've plan to create TRIGGER. This TRIGGER will insert the appropriate record from tblHIT into tblHIT_7days and also delete a appropriate record in tblHIT_7days. This INSERT and DELETE transaction depend on current date.Is it possible, TRIGGER can do this?

defragment sql volumes

dber — Thu, 15 May 2008 14:58:48 GMT

just curious if anyone defragment san volumes. I have a system thats kicking up a lot of these warnings and wondering how one would tackle this.thanksEvent Type: InformationEvent Source: Application PopupEvent Category: NoneEvent ID: 26Date: DateTime: TimeUser: N/AComputer: Computer NameDescription:Application popup: Windows - Delayed Write Failed: Windows was unable to save all the data for the file \\Server.domain.name\BackupFolder\MyBackUp.bak. The data has been lost. This error may be caused by a failure of your computer hardware or network connection. Please try to save this file elsewhere.http://support.microsoft.com/kb/843515/Event Type: ErrorEvent Source: MSSQLSERVEREvent Category: (2)Event ID: 17055Date: DateTime: TimeUser: User NameComputer: Computer NameDescription:18204 : BackupDiskFile::CreateMedia: Backup device '\\Server\BackupFolder\MyBackUp.bak' failed to create. Operating system error = 64(The specified network name is no longer available.)

Deleting data from access database-we need a trigger that also deletes data from other database(sql).

sarchen21 — Thu, 15 May 2008 15:26:58 GMT

If the user deletes one or more records from a table the trigger on deletion shud add a record into the TempTable. The records shud contain all the fields that are part of the a index type id from the table from whihc the user deleted the data. If the table doesntexist, it shud be created by the trigger.

Migration Wizard Error

chileu17 — Tue, 13 May 2008 08:16:21 GMT

Hi guys, I have to do some migration from sql server 2k to sql server 2k5 but when I use the dts migration wizard I get an error which is the one above. I have crawling the web but with no luck :doze:Any ideas? hope you guys can help me. Thanks in advance.TITLE: DTS Migration Wizard Error------------------------------Exception of type 'Microsoft.SqlServer.Dts.MigrationWizard.HelperUtility.DTSMWException' was thrown.Click Abort to stop the migration of the current package.Click Retry to retry the operation.Click Skip to skip the migration of the current task and continue with the next task.------------------------------BUTTONS:&Abort&Retry&Skip------------------------------

copy database from sql 2000 to sql 2005 in Server Management

dougk — Thu, 15 May 2008 15:04:30 GMT

Using Server Management Tool/database/tasks/import data wizaard I am able to connect from a sql 2005 database to a sql2000 database on the private lan. Moves tables and data just fine. But it drops all the keys in the process. Not acceptable Server Management tool/database/tasks/copy database command is supposed to copy all the database info. Better solution. However, when I enter the exact same connection information that I used for the /import data wizard, the connection fails. Why does the same connection information work for the /import data wizard but fails for the /copy database wizard?

how should i handle start date and end date from my import.

Karen Roslund — Thu, 15 May 2008 12:09:11 GMT

Here is my scenario.I am getting 6 dbf files from the customer and i am importing them using sql bulk copy to a staging database. And in the one of the Dbf files called PlanDbf. i have 2 fields called Start date and enddate which i want to capture and insert into to different tables in the production database. ( but not in the table that it is from). and i am stuck as to what to do.. The import process works where the user selects a PeriodId and then we pass that periodId to all the needed sproces and import the data in to the database..But now i need to eliminate the PeriodId take the start and end date from the plandbf table.This is my table structurePlanDbf.Plan_NUM Name1 Name2 Beg_Date End Date etc 01212 Abc Plannme 20080101 2008313 01211 bca jajal 20080101 2008313 so on and so forth..Then in the PartDbfPlan_NUM PartID OP_BAL etc01212 XXXX 12.5601212 XXXX 45.4501211 XXXX 25.42so on and so forth depending on how many Plans are there in the Plandbf file.. in plan they can be N no.. of unique PartId for each planSO when i am transfereing the data from the Staging databse to the productionI want to add the BEG_Date and the End Date in the PartDbf table in the production database..SO suppose if my query is like thisSelect pd.PlanNum, pd.PartId pl.Beg_Date, pl.End_Date From Partdbf pd Inner join other tables to get the required actually. that is actally planid from a particular tableInner Join Plandbf pl on pd.PlanNum = pl.PlanNum.So i ran a test data which has around 203 records in the Plandbf and 12603 in partdbf and if i dont inner join pl in the above query the it takes 0.01 secs to exceute the whole query.. if i add the pl inner join it take around 5.17 secs.. so i think theere should be a better way that i get that beg_date and end date in the query,,any help will be appreicated..ThanksKaren

SQL SERVER 2005 Managent Studio

elliot.lewis — Thu, 15 May 2008 10:03:33 GMT

Anyone have link for a list of Patches for Microsoft SQL Server Management Studio i'm on 9.00.1399.00

SQL Server Reporting Services

Mike DiRenzo — Thu, 24 Apr 2008 16:31:31 GMT

I am trying to model a SSRS report after a Crystal report. The Crystal report uses a data linking expert wizard that allows for multiple data sources to be included into the report. From this wizard, one can also define relaitionships.The scenario is as follows: This Crystal report has data sources from two different databases from two different RDBMS' (two physical database servers). I want to mirror this functionality in a SSRS report. The only way to come close to doing so is to create a SSRS Report Model Project.From within a report model project, multiple data sources can be defined. From here, one creates a data source view that includes said data sources. One can go even further by defining realtionships - similar to Crystal. The last step is to produce a Report Model which throws an exception. It can't be done because I am going against multiple datasources.ARGH!!!! HOW CAN ONE CREATE A SOURCE OF DATA THAT SPANS MULTIPLE RDBMS'?What I have tryed and it works is the following:1) Create a linked server to another RDBMS and query directly against the tables contained within2) using a the linked server, create a staging table in the local DB and query against it. In effect, I would be querying against one DB.I have neeeeeever been so frustrated before. Can someone please shed light on this issue?-Mike DiRenzomike_direnzo@hotmail.com

INSERT QUESTION

davidsalazar01 — Wed, 14 May 2008 21:47:36 GMT

INSERT INTO [Vimas].[dbo].[TempBatch]Select TransID From Batchheader (nolock) where DateCreated < GetDate() -180

starting sql server

zahid aziz — Thu, 04 Jan 2007 13:03:00 GMT

i had SQL 2005 working fine so far but today i was trying to connect to it i found that MSSQLSERVER service is not running i tried toi run it through configuration manager and manually too but didnt work and gives me the message" the sql server(MSSQLSERVER) service started and stopped. some services automatically stop when they have no work to do "

this is wat i got and i have no idea how to fix it.

any help would be appriciated.

Distinct, Group By

AFCC Inc. Com — Wed, 14 May 2008 07:56:39 GMT

I have a products table that has many photos, the products table and the photos table are connected by a tween table.I am trying to select only one photo per product.The trouble that I am having is that if the product has three photos then that product is returned three different times.I have attached the schema, and the results that I keep getting.The schema[img]http://afccinc.com/Software/Help/images/distinctHelp.gif[/img]The Results[img]http://afccinc.com/Software/Help/images/distinctquery.gif[/img]Thanks for the help !Erik

Advice needed

Roy Ernest — Wed, 14 May 2008 11:54:45 GMT

Hi All,There is a plan that couple of our Web Servers will be moved to different Server farm. The DB server stays here. I want to know if there is anything special I have to keep in mind when setting up DSN/ODBC conncections over the WAN. Our Sites have both ASP and .NET architecture code in it. Thanks

transactions and triggers

evans.c — Wed, 14 May 2008 12:17:12 GMT

Hello,

License Tracking in SQL Server 2005

Mossy — Tue, 13 May 2008 22:35:35 GMT

Hi,Just wanted to know what everyone thinks of this: Apparently in SQL Server 2005 there is no license tracking, so If I run SELECT SERVERPROPERTY('LICENSETYPE') and then run SELECT SERVERPROPERTY('NUMLICENSES') returns nothing. You have to update the registry with the license information manually so as the above queries return the appropriate information (ie number of processors licensed, amount of client license connections allowed etc).Has anyone else run across this at all?? Also I believe this may be the case in SQL 2008 - does anyone know if this is true as well?? If you've seen it, how do you deal with it?As always many thanks in advanceScott