Asp.net 2, Sql2005, Double Data Rows

Sep 28, 2006

I'm stumped.  When using the classic asp page, the table produced on the web has 4 rows.  But, when using the asp.net 2.0 side against the same stored proc, I get double the rows - 8. However, when I pull from the sql table directly there are only 4 rows.  Any ideas?  I've looked at this far too many hours...da = New Data.SqlClient.SqlDataAdapter("pTblTempDisplayInsert2 0,'" & coList & "','" & tblList & "','" & varTblNameT & "'", cn)
da.Fill(ds, "myTables")
For Each drow As System.Data.DataRow In ds.Tables("myTables").Rows
varTxt &= drow.Item(
"titleID") & "<br />"
Next  I get:
1852
1850
2055
2053
1852
1850
2055
2053



If I run the last line of the stored procedure with the variable table name filled in:
SELECT d.titleID FROM ##varTblName d LEFT JOIN xtblTitles t ON d.tblID=t.tblID LEFT JOIN tblStatTitles s ON d.titleID=s.titleID LEFT JOIN xtblURL u ON u.urlID=t.urlID ORDER BY d.tblID, d.[year], d.lineOrder, d.countyID I get:
1852
1850
2055
2053


If I do a simple select * from ##varTableName, I get four rows....
Thanks,
Janet

View 4 Replies


ADVERTISEMENT

SQL Server 2008 :: DOUBLE Precision For Calculations / Convert To Double?

May 19, 2011

I am performing a series of calculations where accuracy is very important, so have a quick question about single vs double precision variables in SQL 2008.

I'm assuming that there is an easy way to cast a variable that is currently stored as a FLOAT as a DOUBLE prior to these calculations for reduced rounding errors, but I can't seem to find it.

I've tried CAST and CONVERT, but get errors when I try to convert to DOUBLE.

For example...

SELECT CAST(1.0/7.0 AS FLOAT)
SELECT CONVERT(FLOAT, 1.0/7.0)

both give the same 6 decimal place approximation, and the 6 decimals make me think this is single precision.

But I get errors if I try to change the word FLOAT to DOUBLE in either one of those commands...

SELECT CAST(1.0/7.0 AS DOUBLE)

gives "Incorrect syntax near )"

SELECT CONVERT(DOUBLE, 1.0/7.0)

gives "Incorrect syntax near ,"

View 2 Replies View Related

DB Engine :: Double Row Added From A Source Table That Did Not Have Double Record

Aug 28, 2014

Every night we connect to a remote server using Linked Server and copy details from that database to a loading table, then load it into the 'real' table in our own environment. The remove database we load it from has indexes/primary keys that match the 'real', however the 'loading' table itself does not have any indexes or primary keys, both are SQL Server 2005 machines.

In the loading table we first of all truncate it then do a select insert statement from the remote server, then we then truncate the 'real' table and load iit from the 'loading' table.

The issue is when we attempted to load it into our 'real' table from our loading table there was a duplicate row, and our process failed with a Primary Key violation.

I checked the source with does have the same primary key's in, it did not contain a duplicate row and I checked the loading table and that did contain a duplicate row.

My question this is in what circumstances this could happen ?

View 5 Replies View Related

SQL Server 2014 :: How To Insert CSV Data Into DB Where Some Data Don't Have Double Quotes

Aug 11, 2015

Example of data in CSV are as follows:

"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS

[Code] ....

View 5 Replies View Related

Double Data Type??

Jan 16, 2003

What data type in SQL server can be used in place of a double data type?? I don't even know what a double data type is but got a request to create a column with a data type of double.

Thanks.

View 1 Replies View Related

Remove Double Quotes From Column Data?

Jun 17, 2014

I imported data from flat file to SQL Server database table. After execution one of column got data with double quotes. It look like:

22222.....02/14/2014....."Smith, John"
333........02/14/2014....."Brownies, Alian"

How to remove quotes?

View 3 Replies View Related

Trouble Importing Data With Double Quotes

Jul 23, 2007

I have to import a flat file with commas and double quotes as the text qualifier in a SSIS package. However, when I try to import the data into a table, the data moves the information to right, therefore, the last field will capture mulitple fields worth of data. When I create the Connection Manager for the flat file, I have the format as [Delimited] and Text qualifier as ["]. I do not check the unicode button, but under Advanced, make each field a Unicode string [DT-WSTR]. I have included a sample of my data below.



"Internal Sales Document ",9/23/2005 0:00,0.58,"STORES ISSUES","TAPE, PACKING, 2" X 55 YD, CLE ","EP0079771","US363800","2065431980"
"Internal Sales Document ",10/7/2005 0:00,3.76,"STORES ISSUES","Post-It Note Pads, 3"x3", Cana ","EP0079770","US363799","2065431980"


As I highlighted above, I will have a description field that will have double quotes and commas within the text before the end of field.



Please let me know if this is user error. Thanks,

View 8 Replies View Related

SQL Server 2012 :: Query Get Data In Double Codes In String

Feb 9, 2014

I have data like this

"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}

I want to get only data in double codes in using sql query.

View 9 Replies View Related

Insert Or Update Into A Image Column Causes Data To Double In Size

Aug 1, 2005

We are experiencing problems inserting or updating image fields fromone table to another in SQL Server.When we do this what ever size of file we insert is doubled in sizewhen it is inserted into the destination table.This happens in insert and update queries, and if we use DTS.Any help would be greatly appreciated

View 3 Replies View Related

Converting A Data Type Double To A Type Float Using A Data Adapter &&amp; SSCE 3.5

Feb 13, 2008

Hi,

I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:


this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);

this.mycourtsDataSet1.AcceptChanges();

this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);


This did not happen with VS2005/CE3.01.

I have tried changing all references to decimal (or money in CE3.5) without luck.

I'm beginning to think that string may be the way to go!!!!!!!

Can someone shed some light on my problem.

Thanks,

Later:

It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.

View 4 Replies View Related

Storing A Money Data Item (in SQL Server) In A Double SSIS Variable

Nov 5, 2007

Hi

I have a table in SQL Server with following spec
Table1(Grossamount(money))

I have a SSIS variable called grosstot of type double and use following sql in Execute SQL task in SSIS

Select Sum(Grossamount) from Table1

I then assign the result of above sql stmt to the SSIS variable grosstot within the same Execute SQL task.

it gives me the error :
[Execute SQL Task] Error: An error occurred while assigning a value to variable "grosstot ": "The type of the value being assigned to variable "User::grosstot " differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ".



I tried the following sql to no avail

Select CONVERT(numeric (12,2), Sum(Grossamount) from Table1


Your help very much appreciated

View 7 Replies View Related

Transform To Remove Rows From Data Set A That Match Rows In Data Set B On A Given Key?

Jun 28, 2006

Hi,

I have a common requirement in numerous SSIS processes to take my main input data set and to remove all rows from it that match a second input data set on a given key and output this as the main output. I also want to output (as a second output) all the rows from the main input data set that did match on the given key. However, I don't want to merge in data from the second input, nor am I interested in rows from the second input data set that have no match in the main input.

E.g. If I have the following data:

Main input:
Key Name
--- ----
1 Steve
2 Jamie
3 Donald

Second Input
Key DontCareAboutThisField1
--- -----------------------
1 ...
3 ...
4 ...

Then I would like the following output:

Main Output
Key Name
--- ----
2 Jamie

Second Output
Key Name
--- ----
1 Steve
3 Donald

Can I do this with a standard transform, or will I have to write my own? Any help on this would be greatly appreciated!

Thanks in advance,

Lawrie

View 1 Replies View Related

Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)

Dec 25, 2005

Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1   answer1user1   answer2user1   answer3user2   answer1user2   answer2user2   answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1   answer1   answer2   answer3user2   answer1   answer2   answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.

View 1 Replies View Related

Using SSIS 2005 To Strip Out Bad Rows In Excel And Then Insert Detailed Rows Into OLE DB Data Source

Apr 6, 2006

Environment:
 
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
 
Problem & Info:
 
I am bringing in an Excel file.  I need to first strip out any non-detail rows such as the breaks you see with totals and what not.  I should in the end have only detail rows left before I start moving them into my SQL Table.  I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls

Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table.  I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
 
Desired Help:

 
How to perform
 
1)       stripping out all undesired rows
2)       importing each column into sql table

View 1 Replies View Related

Integration Services :: Data Flow Task Failed After Loading 29000 Rows Out Of 234567 Rows

Oct 13, 2015

I am facing an issue that Data flow task failing after loading 29000 rows out of 2lakhs rows.

I am loading data from .csv file to OLE DB Destination.

This data flow task is placed inside For each loop container.

is this issue because of any performance issue in SSIS packages such as buffer size.

find the error below:

DFT Load Data from FlatFile:Error: The conditional operation failed.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. 

The "DER Add Calc Columns" failed because error code 0xC0049063 occurred, and the error row disposition on "DER Add Calc Columns.Outputs[Derived Column Output].Columns[M_VALUE_NUM]" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "DER Add Calc Columns" (48) failed with error code 0xC0209029 while processing input "Derived Column Input" (49). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

[code]....

View 8 Replies View Related

Sql2005 Database Restore From Another Sql2005 Backup File Error.

Dec 15, 2005

hi

i try to restore a bak file from another sql2005 server to my sql2005 server, but it show the error message as below :

 

 

TITLE: Microsoft SQL Server Management Studio Express
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupackup.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


 

 

 

pls some one can help me ???

 

thanks

chaus

View 62 Replies View Related

SQL2005 && Importing XML Data

Sep 11, 2006

I understand that SQL 2005 now supports XML documents. I have a rather large file that is in XML format that I need to get into a table in SQL2005. I have tried the import wizard to no avail. I even tried to use DTS packages in SQL 2000 and still no luck.

Does anyone know of an article or information I can obtain on how to import data from and XML file to a SQL 2005 Table?

Here is a basic idea of the data as it is shown on the first row of the XML file.


Code:


<?xml version="1.0" encoding="UTF-8"?><SheetName Version="1.0" Date="2006-09-10">
<SheetNameLine>
<action>A</action>
<Id>1</Id>
<Code>ACCOMPS</Code>
<AddData></AddData>
<DataIssue></DataIssue>
<Type>DATA HERE</Type>
<Serial>123546789</Serial>
<Date>DATA HERE</Date>
<Updated>DATA HERE</Updated>
<Info>DATA HERE</Info>
<AColumn></AColumn>
<BColumn></BColumn>
<CColumn></CColumn>
<DColumn></DColumn>
</SheetNameLine>



This particular file is way too large to import into Excel and then into SQL, so I need to find an alternative way.

Any ideas? Help?

Thanks in advance.............

View 1 Replies View Related

Export All Data From SQL2005

May 1, 2007

Hi,

How to export all data from SQL2005 server.



Is there any method to do so?

with regards
Shaji

View 2 Replies View Related

Can You Open A Database Created In SQL2005 In SQL2005 Express?

Oct 12, 2007



Can you open/use a database created in SQL2005 in SQL2005 Express?

Thanks for the help!

Max

View 4 Replies View Related

Data Transfer From SQL200o To SQL2005

Dec 7, 2006

I need to transfer data from SQL Server 2000 to SQL Server 2005 nightly. Replication DB1.T1 table in SQL 2000 to DB2.T1 table (the same table structure) in SQL 2005. What are my options? I need to extract a daily data and do this daily. Some example would be greatly appreciated.
Thanks,
 
 

View 1 Replies View Related

Import As400-DB2 Data Into SQL2005

May 19, 2008

How do i import As400 data in to SQL2005(standard version) using SSIS?? Please help!!!

View 1 Replies View Related

DTS / Export Data In Sql2005 Mgm Studio

Jun 27, 2006

Dear all,

I have a SQL 2000 and SQL 2005 servers separately, but by whatever reason, I can't use SQL 2000 Enterprise Mgr anymore now so I have to use SQL 2005 Mgm studio to open up my SQL 2000 databases now.

However, I'd like to export data from Sql 2000 to another Sql 2000 server, but via a Sql 2005 mgm studio interface right now. How can I do so? I can't find "Export Data" from the "Task" context menu in Sql 2005 mgm studio.

Thanks,

View 1 Replies View Related

Import Data From ODBC To SQL2005

Mar 16, 2007

Hi, there;
I want to importing data from ODBC, I created DataReader Source which use a .NET Provide Odbc Data Provider and connected successfully. My destination is a OLE DB Destination that points to SQL2005. I set the SQL command as "SELECT * from ....".
I also have problem to create new table in SQL2005 using SSIS Import and Export Wizard, it doesn't know the source table table schema (two date type column). So I create the new table manully and run the package, I got error:


SSIS package "Package1.dtsx" starting.
Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Data Flow Task, DTS.Pipeline: Execute phase is beginning.
Error: 0xC02090F5 at Data Flow Task, Source - Query [1]: The component "Source - Query" (1) was unable to process the data.
Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "Source - Query" (1) returned error code 0xC02090F5. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Information: 0x40043008 at Data Flow Task, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Data Flow Task, Destination - PITest CMF [169]: The final commit for the data insertion has started.
Information: 0x402090E0 at Data Flow Task, Destination - PITest CMF [169]: The final commit for the data insertion has ended.
Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "Destination - PITest CMF" (169)" wrote 0 rows.
Task failed: Data Flow Task
SSIS package "Package1.dtsx" finished: Success.

Can any one know what's wrong here?

And it is quite pain that you have to specify the table name  every time you want to create a new table in SQL2005. It is so easy in SQL2000!!!

Thanks.

View 1 Replies View Related

Import Data From Sql2005 Into Sql Mobile

Sep 3, 2007

Hi,
i need to import data from sql 2005 into sql mobile, is there anyway to do it?
Need help urgently....

thanks

Joel

View 1 Replies View Related

Error Catching On Data Duplication In A Sql2005 Db

Apr 15, 2007

Hello, everyone.  I am having problems catching a data duplication issue.  I hope I can get an answer in this forum.  If not, I would appreciate it if someone can direct me to the right forum.
I am working on a vs2005 app which is connected to a sql2005 db.  Precisely, I am working on a registration form.  Users go to the registration page, enter the data, ie. name, address, email, etc. and submit to register to the site.
The INSERT query works like a charm.  The only problem is that I am trying to reject registrations for which an email address was used.  I put a constraint on the email field in the table and now if I try to register using an e-mail address that already exists in the database I get a violation error (only visible on the local machine) on the sql's email field, which is expected.
How can I catch that there is already an email address in the database and stop the execution of the code and possibly show a message to the user to use a different address?
 Thank you for all your help.
 
Antonio

View 4 Replies View Related

Problem In Importing Data From Excel To Sql2005

Sep 20, 2006

Hi
I have to import data from a number of excel files to corresponding tables in SQL 2005. The excel files are created using excel 4.0. I have created an excel connection manager and provided it with the path of the excel sheet.Next i have added an excel source from the toolbox to the dataflow. I have set the connection manger, data access mode, and the name of the excel sheet (the wizard detects the sheet correctly) in the dialog window i get when i double click the excel source. Every thing goes fine till here. Now when i select the 'columns' in this dialog window or the preview button, i get this error

TITLE: Microsoft Visual Studio------------------------------Error at Data Flow Task [Excel Source [1]]: An OLE DB error has occurred. Error code: 0x80004005.Error at Data Flow Task [Excel Source [1]]: Opening a rowset for "test4$" failed. Check that the object exists in the database.------------------------------ADDITIONAL INFORMATION:Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)------------------------------

Any ideas about why is this happening???
Umer

View 3 Replies View Related

Copying / Moving Data From SQL2005 To SQL2000

Oct 22, 2007

Hi
I have a (possibly) common position where half of our IT department is SQL 2005 and the rest is SQL2000. For myself, having to work in a SQL2000 environment and needing data from a SQL2005 Cluster I came up with this solution.
Also, to alert me when the process starts and completes since it is part of a scheduled process, I have it do a RAISERROR with logging to record entry and exit times. In addition, this runs out a series of PRINT statements that lets the operator know what table is currently being worked on.

Of course, any suggestions for speeding this up would be helpful!

What I have found that works for getting data (albeit slow) from SQL 2005 down to SQL 2000 is to script a fairly simple copy process -

-- it is actually pretty easy to follow if you just read the code......

-- first, we find all the views which are present, so they can be ignored when copying the raw data

CREATE TABLE #VIEWS (TABLE_NAME NVARCHAR (255) )

INSERT #VIEWS
SELECT TABLE_NAME FROM
OPENDATASOURCE(
'SQLOLEDB',
Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' )
.SQL_SIS_SYSTEM.INFORMATION_SCHEMA.VIEWS

-- now, we get the actual tables with data
CREATE TABLE #TEMP (TBL_SCHEMA VARCHAR (12), TBL_NAME VARCHAR (255), RECCNT INT )

INSERT #TEMP
SELECT TABLE_SCHEMA , TABLE_NAME FROM
OPENDATASOURCE( 'SQLOLEDB', 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' )
.SQL_SIS_SYSTEM.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM #VIEWS)



-- then, we start copying tables over - now we tag the ones which are populated with at least 1 row.
-- the first cursor loop gets all table names, the second will find row counts of source tables.
-- this segment uses (ugh) cursor to loop through and gather all of the data. This cursor is at the table name level - not
-- processing anything, and is used only to find tables which have a rowcount > 0
-- believe it or not, the cursors run pretty darn quickly since they arnet doing any calculations - just finding
-- tables with row counts > 0



SET @QUOT = CHAR(39)
SET @LBRAKT = '['
SET @RBRAKT = ']'
SET @IUT = 'IsUserTable'
SET @ODBC_CMD ='(' + @QUOT + 'SQLOLEDB' + @QUOT + ',' + @QUOT + 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT +
').SQL_SIS_SYSTEM.dbo.'
PRINT 'BEGIN TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) )
DECLARE GETEM CURSOR FOR
SELECT TBL_SCHEMA, TBL_NAME FROM #TEMP
OPEN GETEM
FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = 'UPDATE #TEMP SET RECCNT = ' +
'(SELECT COUNT(*) FROM OPENDATASOURCE(' +
@QUOT + 'SQLOLEDB'+ @QUOT + ',' + @QUOT +''Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT +
').SQL_SIS_SYSTEM.' + @TBL_SCHEMA + '.' + @TBL_NAME +')' +
' WHERE TBL_NAME = ' + @QUOT + @TBL_NAME + @QUOT
EXEC (@SQL)
FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME
END
CLOSE GETEM
DEALLOCATE GETEM

PRINT 'FINIS TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) )



DECLARE @PLIN VARCHAR (80),@LFT_PLIN VARCHAR (20),
@RT_PLIN VARCHAR (20), @TLIN INT
PRINT 'BEGIN TABLE CHECK AND LOAD CURSOR.'
DECLARE FETCHIT CURSOR FOR
SELECT TBL_NAME, RECCNT FROM #TEMP WHERE RECCNT > 0



OPEN FETCHIT
FETCH NEXT FROM FETCHIT INTO @TBL_NAME, @RECCNT
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'CHECKING ' + @LBRAKT+ @TBL_NAME + @RBRAKT

IF NOT EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id
(@LBRAKT+ @TBL_NAME + @RBRAKT )
AND OBJECTPROPERTY(id,''+@IUT) = 1)
BEGIN
SET @LFT_PLIN = 'OBJECT' + SPACE(5)
SET @RT_PLIN = SPACE(5) + 'NOT FOUND. BUILD ' +
CAST(@RECCNT AS VARCHAR (6) ) + ' rowS.'
SET @PLIN = @LFT_PLIN + @LBRAKT+ @TBL_NAME + @RBRAKT
SET @TLIN = LEN(@PLIN)
SET @PLIN = @PLIN + SPACE(50 - @TLIN) + @RT_PLIN
PRINT 'ENTRY. ' + CAST(GETDATE() AS VARCHAR (50) )
PRINT @PLIN
PRINT 'COPYING DATABASE TABLE.'
SET @SQL = ' SELECT * INTO ' + @TBL_NAME +
' FROM OPENDATASOURCE' + @ODBC_CMD + @TBL_NAME
EXEC (@SQL)
PRINT 'COMPLETED. ' + CAST(GETDATE() AS VARCHAR (50) )
END

IF EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = object_id
(@LBRAKT+ @TBL_NAME + @RBRAKT )
AND OBJECTPROPERTY(id,''+@IUT) = 1)

-- #TEMP (RECCNT) CONTAINS ROW COUNT OF SOURCE SYSTEM.
-- COMPARE TO ROW COUNT OF LOCAL TABLE
BEGIN
SELECT @DEST_RECCNT = RECCNT FROM #TEMP
WHERE TBL_NAME = @TBL_NAME
IF @DEST_RECCNT = @RECCNT
BEGIN
PRINT 'TABLE row COUNT MATCHES. BYPASSING REBUILD.'
END
IF @DEST_RECCNT <> @RECCNT
BEGIN
SET @SQL = 'DROP TABLE ' + @TBL_NAME
EXEC (@SQL)
SET @LFT_PLIN = 'OBJECT' + SPACE(5)
SET @RT_PLIN = SPACE(5) + 'REBUILD.' +
CAST(@RECCNT AS VARCHAR (6) ) + ' rowS.'
SET @PLIN = @LFT_PLIN + @LBRAKT+ @TBL_NAME + @RBRAKT
SET @TLIN = LEN(@PLIN)
SET @PLIN = @PLIN + SPACE(50 - @TLIN) + @RT_PLIN
PRINT 'ENTRY. ' + CAST(GETDATE() AS VARCHAR (50) )
PRINT @PLIN
PRINT 'COPYING DATABASE TABLE.'
SET @SQL = ' SELECT * INTO ' + @TBL_NAME +
' FROM OPENDATASOURCE' + @ODBC_CMD + @TBL_NAME
EXEC (@SQL)

PRINT 'COMPLETED. ' + CAST(GETDATE() AS VARCHAR (50) )
END
END
FETCH NEXT FROM FETCHIT INTO @TBL_NAME, @RECCNT
END

CLOSE FETCHIT
DEALLOCATE FETCHIT
PRINT 'FINIS TABLE CHECK AND LOAD CURSOR.'


XIT:


RAISERROR (50002,010,1) WITH LOG -- FLAG COMPLETION TIME IN SQL LOG

SET QUOTED_IDENTIFIER OFF


View 1 Replies View Related

Problem Inserting Data From IDictionary Into Sql2005

Jan 4, 2007

Hi,

I'm not sure if this should be in the Sql or VB forums.

Using VS2005 I have a graphics program which holds data in a Dictionary Object.

The data consists of a Key and a Structure called Arrows. This structure holds a startpoint and an endpoint, both of which consist of x and y co-ordinates. I need to insert these four co-ordinates into X,Y,X1 and Y1 columns of a table,

The following Insert statement will insert the correct IdKey and Textbox data, but will not compile the co-ordinates.

I have initialised the Points of the structure, but get the error message that Type is not defined.

With dbCommand

Dim _mstartPoint As Point

Dim _mendPoint As Point

Dim strSql As String

For Each kvp As KeyValuePair(Of String, Arrow) In Arrows

strSql = "INSERT INTO Drawing_Arrows (ArrowId,FlashingCode,X1,Y1,X2,Y2) Values ('" & kvp.Key & "', '" & txt_Title.Text & "','" & CType(Arrows(kvp.Key), _mstartPoint).x.ToString & "', '" & CType(Arrows(kvp.Key), _mstartPoint).y.ToString & "''" & CType(Arrows(kvp.Key), _mendPoint).x.ToString & "', '" & CType(Arrows(kvp.Key), _mendPoint).y.ToString & "')"

.CommandText = strSql

.CommandType = Data.CommandType.Text

.Connection = dbConnection

.ExecuteNonQuery()

Next

End With

My problem is how to extract the _mstartPoint x and _mstartPoint y as seperate items for insertion into X and Y in the table.



Any suggestions welcome.



Tailor





View 1 Replies View Related

How To Import MySQL Data Into SQL2005 Using Wizard?????

Mar 22, 2007

Hello everybody,

I'm a new SQL2005 user.
I'm
trying to import data from MySQL version 5x into my SQL2005 by wizard.
I created a DNS file and tested successfully using MySQL Connector/ODBC
v5.
Everthing seems fine but at the last step selecting data
source. The SQL2005 wizard forced me to choose using SQL command option
instead of selecting tables/views from a list. Anyone can tell me why?
My collegues faced the same case as they help me to find the reason.
I'm sure that there are a few source objetcts in MySQL source.

Any help will be appreciated!!!

khanhmy

View 1 Replies View Related

Update Data On SQL2005 Linked Servers To DB2

Oct 25, 2007

Hi all.

I need your help.

I create a linked server on SQL 2005 server using IBMDA400 as provider.

I create a VBscript to update some data on DB2, and issue begin trans and commit trans. Kindly refer to below code.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Set oKCDat = CreateObject("KCDAT.kcdatapi")
Set objConnection = oKCDat.OpenConnection_SQL(strDataSource,strDB, strUserID, strPassword)

Set rs = CreateObject("ADODB.Recordset")

strName = "TEST1"

strTemp1 = "2"
strTemp2 = "3"
intTemp1 = 199

strSQLStatement = "SELECT * FROM QS36F.TEST WHERE PRACNM = ''" & strName & "''"
strUpdSQL = "TEST1 = '" & strTemp1 & "', TEST2 = '" & strTemp2 & "', TEST3 = " & intTemp1

strSQL = "UPDATE OPENQUERY(TESTDB2," & "'" & strSQLStatement & "')" & " SET " & strUpdSQL & ";"


objConnection.BeginTrans

objConnection.Execute(strSQL)

objConnection.CommitTrans


objConnection.Close


Set rs = Nothing
Set oKCDat = Nothing
Set objConnection = Nothing

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------


When i run above script, it prompt me an error message;
"Microsoft OLE DB Provider for SQL Server
The requested operation could not be performed because OLE DB provider "IBMDA400" for linked server "TESTDB2" does not support the required transaction interface."

If i run it without "begintrans" and "committrans", it update the data successfully.

Does anyone know about it?

Highly appreciated for above matters.


Regards

View 3 Replies View Related

Upgraded SQL2000 To SQL2005, SQL2005 VERY Slow

Dec 27, 2005

I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?

View 3 Replies View Related

Sql2005 Standard To Sql2005 Enterprise Edition

Jan 4, 2007

Hi

We have Sql2005 x64 bit standard edition server installed in windows 2003 64 bit editio server,

currently due to buisness requirements we need to have sql2005 x64 bit enterprise edition, please let me know how do i do the upgrade or change.

is it possible to retain all our custom settings in the standard edition after changing to enterprise edition.

This has to be done for our production and very critical, please help



Thanks

Samuel I

View 4 Replies View Related

SQL2005 Equivalent Of The Data Import Task In SQL2000

Jun 18, 2006

I had to upgrade to SQL2005 when my customer did so. Everything went OK until I had to load additional data to the datamart. The SQL2000 task to load data from flat text files was straight forward and literally took me seconds tyo set up and execute and has never given me problems. I have now spent 3 days straight trying to do the same in SQL2005 going in ever wider circles. I have got SSIS up and going, I saved a dtsx package that won't run without a multitude of errors ranging from truncation errors (which I tried to avoid by meticulously ensuring all field definitions on the import and export side are defined and correct) to errors relating to not being able to connect to the database I am working on or of not having the right security access. I am at my wits end. I need help!

View 8 Replies View Related







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