Strange Difference Between Sql 2002 And 2005.

Dec 5, 2007

hi,
I just realized that this must be a difference between sql server 2000 and 2005.
For example, the query is like this:
declare @n int
set @n = -1
select @n = employeeid from employees where companyID=1 and idnumber='1-1'
--select @n
if(@n is null)
......
If there is no such employee, in 20002, @n will be null, but in 2005, @n is -1, not null. Am I right?
So, for this piece, I have to do twice on the same where, like
if(exists(select * from employees where companyID=1 and idnumber='1-1'))
select @n=employeeid from employees where companyID=1 and idnumber='1-1'
else
set @n = null
Any better way?

Thanks.

View 1 Replies


ADVERTISEMENT

Mysterious Mystery -- 2002-11-08/2002-11-09

Jul 20, 2005

I have function that returns a table of information about properties. Thedata comes from three different tables -- addresses (called PropertyID),property characteristics, and events concerning those properties (sales,appraisals, etc.), plus a table that maps one representation of propertytypes into another. The records are selected on the basis of location(longitude & latitude), property type, event type, and a range ofevent dates (upper and lower date specified). There are tens of millionsof records of all types, and almost any location, property type, event typeand date range will yield records.The heart of it is a cursor that selects records from joins on this basis:SELECT <a bunch of fields>FROM Property dJOIN PropTypeMap ptm ON ptm.PropertyTypeID = d.PropertyTypeIDJOIN PropertyID a ON a.PropID = d.PropIDJOIN Event e1 ON e1.PropID = d.PropIDLEFT OUTER JOIN Event e2 ON e2.PropID = d.PropIDWHEREd.LastSaleDate >= @LoDateAND a.GeoLongitude BETWEEN @LowerLon AND @UpperLonAND a.GeoLatitude BETWEEN @LowerLat AND @UpperLatAND ptm.PropCategory = @PropTypeAND a.GeoMatch <= @MinGeoQualityAND e1.EventTypeID = @SaleEventTypeAND e1.TransactionType = 'R'AND e1.EventDt BETWEEN @LoDate AND @HiDateAND e1.EventAmt > 0AND e2.EventTypeID = @AssessmentEventTypeAND e2.EventDt <= @HiDateAND e2.EventAmt > 0Each property has one PropertyID record, one Property record, and N Eventrecords (average perhaps five).What is the mystery? If @HiDate, which is the upper end of the time window,is 2002-11-08 or earlier, nothing is returned. If it's 2002-11-09 or later,oodles of records are found. I get the same query plan for either one, andbased on the content of the data, they should return almost exactly the sameset of records -- exactly the same set in almost all cases, in fact.Is 2002-11-08/09 some sort of magic dividing point? I have replicated thison the large database and on a smaller test version on another SQL Server.(SQL Server 2000) I dropped the indexes and tried it, andthe same thing happened. This is driving me crazy!

View 3 Replies View Related

Can I Install VS.net 2005 / VS.net 2002 / SQL 2005 / SQL 2000 In One PC Win2000 Sp4?

Dec 1, 2005

P4 2.8G Hz, 512 M RAM.thanks.

View 3 Replies View Related

MS Access 2002 / SQL Server 2005 Express

Aug 31, 2006

I have few queries:

(1) What is the maximum database size of Access XP and SQL Server 2005 Express?

(2) Is SQL Server 2005 Express edition FREE for development use?

View 1 Replies View Related

Migrate Access 2002 To SQL Express 2005

Nov 26, 2005

Hello

View 5 Replies View Related

Visual Studio.net Academic (2002) With Sql Server Express 2005?

Sep 17, 2006



Can Visual Studio.net Academic (2002) be used with Sql Server Express 2005?

View 4 Replies View Related

Errors Upsizing An Access 2002 Database To SQL Server 2005

Feb 25, 2007

I have an Access DB with about 50 tables. When I run the upsize wizard all bur four tables import beautifully, data and all. Four of the tables fail with the message 'Table was skipped, or export failed'

When I try to IMPORT the data I get 100 or so messages like this one:



Warning 0x80047076: Data Flow Task: The output column "AssessPlanPHPID" (23) on output "OLE DB Source Output" (11) and component "Source - tblAssessPlan" (1) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
(SQL Server Import and Export Wizard)


And the import fails. Note that the upsize creates the tables just fine, it just won't import the data.



Any ideas?

I apologize for the fonts jumping around.

View 2 Replies View Related

How Do I Set Access 2002 Upsizing Wizard To Create Clustered-index On SQL 2005

Jun 3, 2006

Here is an extract from the Acc2Sql2.doc >>


By default, the Upsizing Wizard
transfers all indexes as nonclustered indexes. You can modify the Upsizing
Wizard to transfer the primary key index to a clustered index. To make this
change, start Microsoft Access and open the upsizing wizard library database.
For Microsoft Access 95, the filename is Wzcs.mda. For Microsoft Access 97, the
filename is Wzcs97.mda. When the database is open, click the Modules tab and open the
UT_ModUserConstants module. Search down to the UT_CLUSTERED constant. Change
the default value from False to True.

<<< end quote
Question: I am using ACCESS 2002 upsize wizard, I searched my computer for *.mda and could not find any wzcs*.mda. How do I set it so that it creates clustered-index instead of non-clustered-index?

View 1 Replies View Related

Sql Server Reporting 2002 Side-by-side 2005

Jul 15, 2007

having installed 2005 first and installed 2000 with both types of reports going to one 2005 home. i would like to have separate homes

with reports going their respective homes. Do i have to first unstall

2005 and do a new installation with a different report and server than

2000. I would like to avoid this and just fine tune 2000 with the rsconfig.exe. Help me out please.





View 1 Replies View Related

Difference Between SQL Mobile 2005 And SQL Compact 2005

May 24, 2007

Hello,

Could someone give me the difference between "Sql Mobile 2005" and "Sql Compact 2005".
At first I thought they were the same, that this was just something of the change in naming the server has had the last months.
My first guess was that Mobile would be the new one, but I have 'accidently' downloaded the Compact and what seems the System.data.SqlServerCE.dll is newer.

So is there a difference?
If yes, what are they.
If not, is the "compact" then the latest version and the name to be used (since on the site only "mobile" is mentioned).

Best regards,
Ike Casteleyn

View 6 Replies View Related

Strange Problem Using CTE In Sql Server 2005

Nov 27, 2006

I am trying to use a condition in a query with CTE but I got an error.It is simple enough but I just cannot get it work. Any help will be greatly appreciated!
Msg 156, Level 15, State 1, Line 8Incorrect syntax near the keyword 'if'.  declare @i as int
set @i=1;
with CTE_A as
(
select 'B'
)

if @i=1 ---this part get problem
select * from CTE_A
else
select 'B'

 

View 2 Replies View Related

STRANGE PROBLEM IN SQL SERVER 2005

Jun 23, 2008

Dear Friends

I am trying to create a trriger in sql server 2005 but it gives me error. i am unable to understand the problem . here is detail about it

Database name :- Orders

table name :- Currency_Master

Columns are :- Currency_Symbol, Currency_Name, Value_in_Rs

The create trigger query is


CREATE TRIGGER FOR ORDERS ON CURRENCY_MASTER
AFTER INSERT

AS SELECT 'CURRENCY_SyMBOL'


Can any body tell that why it gives an error and how to rectify it.

Thanks in advance.

Shivpreet2k1

View 5 Replies View Related

Strange SQL Server 2005 SP Issue

Sep 15, 2006

We have a stored proc that accepts a date and uses that date in a filter. This procedure worked fine for a couple of weeks then started hanging. Basically, this date parameter is used in a where clause.

Our workaround: when we declare a new variable, set it equal to the parameter, then use the declared variable - that fixes it. Take away the declared variable and use the parameter instead and the proc chokes. But the paremeter and the declare variable are both datetime typed. Weird.

We are worried (partly because it worked without hanging for a while) that we are experiencing a larger issue. We are reporting off a database we denormalize and populate nightly with transactional data. A poor man's warehouse if you will, staging the data in a fast reporting format with the prep done each night. This database is wiped and re-populated each night. We have been testing this process with the same source data, so we are not growing our data at all (net result at end of each night is same data as previous day).

I think the source code here is overkill, but I enclosed the proc anyway. The way it is written below, it hangs. Replace these two uses of @AsOf in the proc with @test and the procedure almost instantly returns data. There is a third use of @AsOf that doesn't affect the issue one way or the other. This line is where the replacement fixes the problem:

--Breaks
Where (OpenedDt < DateAdd(day, 1, @AsOf) or ClosedDt < DateAdd(day, 1, @AsOf)) and (PlanType = @PlanType or @PlanType is null)

--Fixed
Where (OpenedDt < DateAdd(day, 1, @test) or ClosedDt < DateAdd(day, 1, @test)) and (PlanType = @PlanType or @PlanType is null)

If anyone has experienced this or knows some other symptoms to check for, we are all ears. This is scaring us because otherwise we have a working system on our hands and are near a delivery point. Help would be greatly appreciated.

Here is the complete proc...

ALTER PROCEDURE [dbo].[ClaimCountsByLineOfBusiness]
-- Add the parameters for the stored procedure here
@AsOf datetime,
@PlanType varchar(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @DataFreshness datetime
Select @DataFreshness = DataFreshness From metaReportList Where StoredProcedure = 'ClaimCountsByLineOfBusiness'

If @AsOf is null
SET @AsOf = Convert(datetime, Convert(varchar(10), GetDate(), 101))

DECLARE @test datetime
SET @test = @AsOf

-- Insert statements for procedure here
Select PolicyGroup, Lines,
SUM(CASE WHEN HasPayment = 1 and OpenClosed <= 0 THEN 1 ELSE 0 END) as ClosedClaimswithPayment,
SUM(CASE WHEN HasPayment = 0 and OpenClosed <= 0 THEN 1 ELSE 0 END) as ClosedClaimswithoutPayment,
SUM(CASE WHEN OpenClosed > 0 THEN 1 ELSE 0 END) as ClaimsOpen,
Count(ClaimSummary.ClaimNumber) as ClaimsReported,
AccidentYear, ReportingYear,
--Return the value of the parameters in a friendly way (if null)
Convert(varchar(10), @Asof, 101) as AsOfParameter,
IsNull(@PlanType, 'any value') as PlanTypeParameter,
@DataFreshness as DataFreshness

FROM
(Select ClaimNumber, Max(OpenedDt) as OpenedDt, HasPayment, PolicyGroup, Lines, AccidentYear, ReportingYear
From dbo.factClaimSummary
Where (OpenedDt < DateAdd(day, 1, @AsOf) or ClosedDt < DateAdd(day, 1, @AsOf)) and (PlanType = @PlanType or @PlanType is null)
Group by ClaimNumber, HasPayment, PolicyGroup, Lines, AccidentYear, ReportingYear) as ClaimSummary

INNER JOIN

(Select ClaimNumber, SUM(CASE WHEN OpenedDt is not null THEN 1 ELSE -1 END) as OpenClosed
From dbo.factClaimSummary
Group by claimnumber) Status ON ClaimSummary.ClaimNumber = Status.ClaimNumber

Group by PolicyGroup, Lines, AccidentYear, ReportingYear
Order by PolicyGroup, Lines
END

View 2 Replies View Related

Strange SqlBulkCopy Exception With SQL Server 2005

Dec 10, 2006

Hello,

Wondering if anyone can help with a strange exception thrown while using the SqlBulkCopy class. I am using the class to transfer records from a DataTable in memory (approx 11,000 rows) into a SQL Server 2005 table.

Initially, the WriteToServer method was timing out a la KB913177 (http://support.microsoft.com/default.aspx/kb/913177), however I downloaded the hotfix, which eliminated this issue.

Now, I get a new exception thrown, as follows:

System.Data.SqlClient.SqlException: OLE DB provider 'STREAM' for linked server '(null)' returned invalid data for column '[!BulkInsert].Power_Avg'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table)

I can't see anything wrong with the data I have. The column "Power_Avg" is of type "float". When forming the DataTable, I cast my data to float expcilitly in C#.

Other things to know:

I am using SQL Server Express (2005)
This same code works fine with SQL Server 2000 (MSDE)
My code makes all modifications inside a single transaction of type "Snapshot" (I have activated SNAPSHOT READ COMMITTED in the database)
I have not activated MARS in the connection

Any ideas / suggestions?

Thanks,

Nick

View 7 Replies View Related

Difference Between DataTypes In SQL 2005?

Apr 11, 2006

What is the difference between binary and image dataType.

When and how should I use them?

View 1 Replies View Related

How To Tell Difference Between Sql 2005 Express

Oct 15, 2007



HI guys

I installed the SQL server 2005 express edition, but it shows in my add/remove programs as "Microsoft SQL server 2005".

Howcan I tell which version I have installed ?

View 4 Replies View Related

Strange Login Issue With SQL Server 2005 Mirroring

Mar 27, 2008

Hi,
we have setup synchronous mirroring with witness server.We ran into problem when we stoped the SQLService of Primary Server. Failover Occured and Witness Server made Mirror Server Primary.

The User assosciated with the Database become Orphaned and no one is able to connect to the Database .
We recove login failed error for all login for mirrored database.
We have to manually run the below comand and reset the password for each login.

1.To Detect Orphaned Users
sp_change_users_login @Action='Report';
2.To Resolve an Orphaned User
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO

Please let me know why sql server mirroring show such strange behavior. If we manually failove every thing work fine.

Regards
Sufian

View 3 Replies View Related

19/04/2002 + &#39; &#39; + 23:00:00 = 17/04/2002 23:00:00 ??????

Apr 24, 2002

INSERT INTO StockTakeRecords
SELECT Serial_Number, [Date] + ' ' + [Time], Location
FROM ImportedStockTakeRecords

The date in the 'ImportedStockTakeRecords' is 19/04/2002.
The Time in the 'ImportedStockTakeRecords' is 23:00:00.

Still, the result in the StockTakeRecords is 17/04/2002 23:00:00.

Does anyone know why, and how I can solve this?

Regards
Ruben

View 1 Replies View Related

Difference Between Sql Server 2005 And 2008

Feb 25, 2008



Hi,

do any body know the article that can give me berif idea about the difference between sql server 2005 and 2008, acutally i want to move over to 2008. but if the difference is not that much then i m might think about that.


Thanks and looking forward.

View 1 Replies View Related

Can Anyone Give Me A Layman's Explanation Of The Difference Between CURRENT_TIMESTAMP And GETDATE() (if There Is A Difference)?

Oct 24, 2007

Question is in the subject.

Thanks in advance
-Jamie

View 7 Replies View Related

2000 To 2005 Query Processng Difference?

Mar 8, 2007

I found an unusual problem between 2000 and 2005 I haven't been ableto decipher from any documentation.The query structure is as follows:select *fromtableA ajointableB b ON a.somekey = b.somekeywherea.type = 'A'and datediff(yyyy, b.someDateField, getdate()) betweena.lowboundary and a.highboundarySome basic facts about the elements and data. The low and high-boundary fields are varchar datatypes. In 2005 (regardless ofcompatibility type I run the database under), the query evaluates theBETWEEN and errors out due to the fact that it is evaluating theDATEDIFF as an integer and finds a non-integer entry in eitherlowboundary or highboundary. I understand and expect this behavior.Obviously, changing the result of the DATEDIFF function to varcharallows the operation to go forth.The odd thing is that there is no "a.type = 'A' " entry, thus thequery wouldn't return anything. In 2000, it seems as though theengine is evaluating the type = 'A' and short-circuiting and in 2005,it is trying to evaluate the entire query OR is there an implicitconversion occuring in 2000 and not in 2005?As I mentioned, the compatibility mode doesn't change how this reacts,but running this on a native 2000 server allows this to happen. Thisparticular code isn't the problem, it's what we might have to contendwith when we migrate this through. Sure, we're going to performregression testing, but I'm concerned about what we would miss.Thanks for any replies.

View 2 Replies View Related

What Is The Difference Between SQL Server 2005 Installed With .NET And A Standalone One

Aug 19, 2006

Please could anyone tell me what are the real differences between SQL Server 2005 bundled along with Visual Studio.NET and a standalone one.

Thanks in advance.

View 3 Replies View Related

Difference Between Destination Adapter And Bcp In Sql Server 2005

Aug 24, 2007



Hi,

Will someone please tell me what is the difference between Destination adapter and bcp in Sql server 2005 ?

Thanks and Regards
Altaf Nizamuddin

View 1 Replies View Related

Strange Problem Sql 2005 Returning Different Size Result Sets From Different Machine..

Nov 5, 2007

Hi,

I have a very strange problem using SQL Server 2005

I have several machines running an application, the problem is that on all machines except one of them the size of the result set that gets returned when I execute the following query is dfferent:

Select * from custoemr where EmployeeID = 3

on three out of the four machine the size of the result set is 1000, where on the other machine the size of the result set is 250, No errors are generated..

Can someome please teel me how to preceed in resolving this issue..

thanks,

View 2 Replies View Related

Moving Access 2002 Db To MS SQL

Nov 7, 2007

Hey guys,

I was just trying to get some information on how to move an Access 2002 db to MS SQL. Also, what all do I need to be able to access the db from the web. For instance, query the db for info from a website. I don't have that much experience in SQL but I quiet a few programming languages so i'm sure i'll learn it quick, I usually do. Right now I have a server set up with MS SQL server 2005 with all the other things required for testing over in my own little world. DNS, Active Dir., etc....

I'm trying to do this because a friend of a friend has a business(small businessish) who wants to be able to view reports from queries over the internet. And i'm sure I can do it but i told them to let me give it a test run to make sure I can before we commit.

Thanks in advance.

View 4 Replies View Related

SQL Express And VisualStudio 2002

Nov 10, 2006

Hi, All!

Can I use Visual Studio 2002 for creating deployment for SQL Express
2005 (like MSDE Deployment toolkit) or it is possible only with VS
2005? I need to create installer, that installs SQL Express 2005 and
creates DB (executes sql file). How can I do it better?





Thank you!

View 3 Replies View Related

Find Difference In Month And Year In Sql Server 2005

Jun 17, 2008

--find day,month,year
--for day select datediff(d,'01 may 2008',getdate())
 -- --for month select datediff(m,'01 jun 2006',getdate())
-- --for year select datediff(year,'01 jun 2006',getdate())
above working fine but suppose difference is 1 year 4 month and 2 month 15 days then It's giving 1 year and 2 month respectively.
but I want completely so I can use this in case of expired user in my project.
User can be expired in 1 month,3 month and 1 year. So I'm not able to recognize.
 thanks

View 3 Replies View Related

Problem: Performance Difference Between MSDE And SQL Express 2005

Feb 4, 2007

Hello, all, I started out thinking my problems were elsewhere but as Ihave worked through this I have isolated my problem, currently, as adifference between MSDE and SQL Express 2005 (I'll just call itExpress for simplicity).I have, to try to simplify things, put the exact same DB on twosystems, one running MSDE and one running Express. Both have 2 Ghzprocessors (one Intel, one AMD), both have a decent amount of RAM(Intel system has 1 GB, AMD system has 512 MB), and plenty of GB offree disk space. MSDE is running on the Intel system, Express isrunning on the AMD system. To keep things fair I use the exact sameDB's and query on both systems. The DB's were created on MSDE so Isp_detach_db'd them from MSDE and then sp_attach_db'd them to Express(this is how MS says to do a "side-by-side" upgrade, so it'sacceptable to do so). After fighting problems in performancedifferences in different situations I have narrowed the problem downto this:Executing a simple select statement with join clause on the databasesyields a difference in execution time that is quite great. Using theExpress Management program I can run the query against either system(MSDE or Express, the two systems are connected via crossover cable toeliminate any network problems/issues). When running the queryagainst the MSDE system (which is over the network) I consistently get<20 ms response times on the query. When running the query againstthe Express installation (which is in shared memory) I consistentlyget 700 ms or longer response times. Both times are for the TotalExecution Time.The query is simply this: select db1.* from db1.owner.tablename as db1inner join db2.owner.tablename as db2 on db1.pkey = db2.someid wheredb1.criteria = 3So, gimme all the columns from one table in one DB (local to theinstallation), matching the records in another DB (also local to theinstallation), where one field in the first db matches a field in thesecond db and where, in the first db, one column value = 3.The first table has a total record count of 630 records of which only12 match the where clause. The second table has a total record countof about 2,700 of which only 12 match up on the 12 out of 630.Even though the data is the same and I've done the detach and attach,and even done the sp_updatestats, the difference in execution time isremarkable, in a bad way.Checking the Execution Plan reveals that both queries have the samesteps, but, on the MSDE system the largest consumer in the process isthe Clustered Index Scan of the 630 record table (DB1 in my queryexample), using 85%. The next big consumer is a Clustered Index Seekagainst the other table (2,700 rows), using 15%.The Execution Plan against the Express system reveals basically theexact opposite: 27% going to the Clustered Index Scan of the 630record DB1, and 72% going to the Clustered Index Seek of the 2,700record DB2.I'm sorry to be stupid but I have this information but I don't knowwhat to do with it. The best that I can tell from this is that thisis the source of my problems. My problems are that on my currentsystems that my clients use the data is returned to them faster thanthey can click the mouse and that the new system (that is, when theychose (or are forced by attrition) to move to Vista and thus Express2005) the screen pop is like 1.5 seconds. This creates poor userexperience. Worse, one process I allow the users to do goes fromtaking 14-30 seconds to over 4 minutes (all on the same machine withthe same OS and version of my program, so it's not a machine or OS ormy app problem).Anyway, I hope someone can shed some light on this now that I've paredit down some.Thanks in advance.--HC

View 9 Replies View Related

Query Performance Difference Between Sql Server 2005 And 2000

Aug 1, 2007

Hi,

I'm having an issue with a query I'm running on Sql Server 2005. It's a semi-complex query involving an in-line table function and several left outer joins which are joined on to the results of the function call. Two of the left outer joins are then qualified in a where clause of the form where table.Col is not null; the idea is that the final result set contains data that has no match in those two tables.

The problem revolves around a where clause in the function and the last left outer join (ie, one of the ones qualified with where not null). When I alter the where clause of the function to further restrict the result set the function returns, the query times shoots up from 1 second to roughly 2-3 minutes. Note that the time the function takes to complete is not affected. The difference in time is purely down to what the query does with the results the function provides. Also note that the change to the where clause provides a subset of the original data; it does not add any more data (it actually restricts the original resultset by roughly 1000 rows).

I can bring the query speed back down again by removing the last left outer join - this join takes one of the columns from the function, and joins it to a small table - 924 rows. So it appears that this particular join is the cause of the issue, but only when using the resultset generated from the modified function query.

Now, as the thread title alludes, Sql Server 2000 and 2005 handle this differently, or appear to. When I execute this same query on a Sql 2000 machine, there's no apparent time differences, and the data that is returned is as expected. Does anyone have any suggestions as to what might be causing this and how I can fix it? I could simply return the larger resultset and use managed code to filter out the rows I don't want; however, I would like to get to the bottom of this, especially if it's going to effect future queries.

Cheers,

Chris

View 4 Replies View Related

Strange Exceptions With Views Usings Casts On 2005 (worked Fine On Sql2k)

Apr 3, 2007

I have views that cast the data into data types.



On 2005 when retrieving the views using a where clause i get errors. I have put together an example below that illustrates the error.



CREATE TABLE TestData ( Id VARCHAR(10), [Description] VARCHAR(20), [Type] INT)

CREATE TABLE TestTypeLookup( [Type] INT, [Description] VARCHAR(20))

GO

INSERT INTO TestTypeLookup VALUES (1, 'Type 1')

INSERT INTO TestTypeLookup VALUES (2, 'Type 2')

INSERT INTO TestData VALUES ('AA', 'Description for AA', 1)

INSERT INTO TestData VALUES ('10', 'Description for 10', 2)

INSERT INTO TestData VALUES ('20', 'Description for 20', 2)

GO

CREATE VIEW TestView AS

SELECT TOP 100 PERCENT CAST(Id AS INT) Id, [Description]

FROM TestData

WHERE [Type] = (SELECT TOP 1 [Type] FROM TestTypeLookup WHERE [Description] = 'Type 2')

ORDER BY CAST(Id AS INT)

GO

SELECT * FROM TestView WHERE Id = 10

GO

DROP VIEW TestView

DROP TABLE TestData

DROP TABLE TestTypeLookup

GO

View 11 Replies View Related

HELP!!! How To Upsize From Access 2002 To SQL Express

Nov 29, 2005

Problem:Upsize a backend MSAccess 2002 Database to SQL Express 2005Explored:Tried using the upsizing wizard from Office XP(2002), Two tables always get skipped.***! The two tables skipped data only, the tablename and data structure were created.Tried to install UPSize Pro, installation failed.I decide to try it in VWD 2005, here is my code so far but it keeps erroring out.
Dim cn As System.Data.OleDb.OleDbConnectionDim cmd As System.Data.OleDb.OleDbDataAdapterDim ds As New System.Data.DataSet()cn = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:Documents and SettingsBillMy DocumentsVisual Studio 2005WebSitesWebSite1App_DataData for Database.mdb';Persist Security Info=True")cmd = New System.Data.OleDb.OleDbDataAdapter("select * from Service_Orders", cn)cn.Open()cmd.Fill(ds)cn.Close()


Dim connDest As New Data.SqlClient.SqlConnection("Data Source=WLOCKLAPTOPSQLEXPRESS;Initial Catalog='Data for DatabaseSQLND1';Integrated Security=True")connDest.Open()Dim oBCP As New Data.SqlClient.SqlBulkCopy(connDest)oBCP.DestinationTableName = "Service_Orders"oBCP.WriteToServer(ds)oBCP.Close()connDest.Close()
It erors on Line oBCP.WriteToServer(ds)with......System.InvalidCastException was unhandled by user code  Message="Unable to cast object of type 'System.Data.DataSet' to type 'System.Data.IDataReader'."  Source="App_Web_hb6xyamq"  StackTrace:       at ASP.xfer_data_aspx.Button1_Click(Object sender, EventArgs e) in C:Documents and SettingsBillMy DocumentsVisual Studio 2005WebSitesWebSite1xfer data.aspx:line 30       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)I'm open for ANY suggestions, I do not have access to DTS, its not in the Express addition. Thanks in advance.Bill

View 2 Replies View Related

Front Page 2002 And SQL Query

Jul 23, 2005

I am using a very basic query in Front Page 2002 against a SQL table inSQL 2000SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)and f_docnumber is a search form field.When I run I getDescription: The precision is invalid.Number: -2147467259 (0x80004005)The field is defined in the table as numberic 9(10,0)Any ideas?

View 1 Replies View Related

Transfer Data To Excel 2002

Aug 21, 2007



Newbie, using SSIS how to transfer data from SQL table to Excel (excel file does not exist)?


Drop OLEDB connection manager (pointing to my sql server)


Drop Excel destination (but it requires excel files exist)


in my case I do not want to create excel file manually


Is there a way in SSIS you can transfer data from sql table into a new excel file?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved