Sql Server 2005 Inserting Prbblem..wrong SQL? Wrong Parameter?

Feb 19, 2006

Im trying to insert a record in my sql server 2005 express database.
The following function tries that and without an error returns true.

However, no data is inserted into the database...
Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about that

Also, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....

Here's my code:


        Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _
        ByVal Email As String, ByVal Gender As Integer, _
        ByVal FirstName As String, ByVal LastName As String, _
        ByVal CellPhone As String, ByVal Street As String, _
        ByVal StreetNumber As String, ByVal StreetAddon As String, _
        ByVal Zipcode As String, ByVal City As String, _
        ByVal Organization As String _
        ) As Boolean
            'returns true with success, false with failure
            Dim MyConnection As SqlConnection = GetConnection()
            Dim bResult As Boolean
            Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection)
            MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName))
            MyCommand.Parameters.Add(New SqlParameter("@Password", Password))
            MyCommand.Parameters.Add(New SqlParameter("@Email", Email))
            MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender))
            MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName))
            MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName))
            MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone))
            MyCommand.Parameters.Add(New SqlParameter("@Street", Street))
            MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber))
            MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon))
            MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode))
            MyCommand.Parameters.Add(New SqlParameter("@City", City))
            MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization))
            Try
                MyConnection.Open()
                MyCommand.ExecuteNonQuery()
                bResult = True
            Catch ex As Exception
                bResult = False
            Finally
                MyConnection.Close()
            End Try
            Return bResult
        End Function


Thanks!

View 1 Replies


ADVERTISEMENT

Problems Inserting Data- Am I Doing Something Wrong?

Mar 23, 2007

I am having problem inserting data obtained from a adhoc sql query. Am I doing something wrong here (the select statement in below mentioned query works fine)
Insert into customer(CustomerID, LastName, FirstName, BillingAddress, City, State, Country, Zipcode, PhoneNumber, EmailAddress)
select CustomerID,(left(ContactName, charindex(' ', ContactName)-1)) as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1)) as FirstName,
Address, City,Region,PostalCode,Country,Phone from Northwind.dbo.Customers
 
I am just doing it this way since Customer's name (both last name & first name ) are stored in a single column in Northwind database. I want to break it into two columns...The reason I am trying to get this to work is...it would be less of a work for me in inserting test data for my applicationAny help would be of great

View 5 Replies View Related

Inserting Nulls? What's Wrong With This Expression?

Jan 9, 2008

I have a derived column transformation that inspects two columns for spaces and creates a third column, initialised with either '1' or 'X'.


However the transformation is failing with the error"

Cannot insert the value NULL into column 'TNV-INSERT1'


"

Here's the expression I'm using:
SUBSTRING(rt_tran_inv_text,1,255) != " " ? " X" : SUBSTRING(rt_tnv_narr_cr,1,30) != " " ? " X" : "1"

Neither of the source columns contain nulls. Any idea why it's trying to insert nulls into the new column?

View 7 Replies View Related

SQL Server 2012 :: SSRS - Display A Message When User Enter Wrong Parameter?

Mar 12, 2015

Issue #1 I have a report that takes 8 character parameter, A error message needs to popped out if user enters parameter less than 8 character.

Issue #2 I have a report with Tablix. A message needs to display if Tablix returns no Rows.

View 1 Replies View Related

SQL Server 2005 (Dev Ed) Install Gone WRONG!

Jan 7, 2007

Hi,

I cant get SQL Server Management Studio installed. The install completes but Management Studio is unavailable in my program files list. I've uninstalled and reinstalled and even been through "How to uninstall an instance of SQL Server 2005 manually" http://support.microsoft.com/kb/909967 . But no matter how many times I run ARPWrapper.exe 5 items are still present:

<img scr="http://EventAnalyser.Appointmentsbook.com/SQLWontUninstall.jpg">

I'm tempted to use MSIZAP but it doesn't apply to sqlserver. How can I get rid of SQL Server and start again without rebuilding my pc?

Cheers,

Jeremy

View 4 Replies View Related

Parameter Entered Has The Wrong Format.. You Must Be ....me!

May 17, 2006

Hi
I have the problem that the below defined paramter gets entered in the database as a interger. the Field in the DB is a nvarchar(5) and the controll that suplies the value is a TextBox
this is the parameter definition:<asp:ControlParameter ControlID="tbComment" Name="Comment" PropertyName="Text" Type="String" />
Why do I get this error, why does ASP to whant to make an integerfrom this text field? When putting a interger value in the textbox all works well and the data gets posted to the database.
I use a SqlDataSource with automatic generated script.
 
look forwart to a solution
walter

View 1 Replies View Related

SQL Server 2005 Choosing A Wrong Execution Plan

Jan 15, 2007

Hi
I am having a query
SELECT Dur1.rootId
FROM DurableEventTab Dur1
WHERE (Dur1.dev_ReferenceClusterRoot = 'iyrwd.52' )
AND Dur1.dev_Action = 'Order:Ordered')
AND (Dur1.dev_Active = 1) AND (Dur1.dev_PurgeState = 0)
AND (Dur1.dev_PartitionNumber = 0)

This table has a primary key : aribapk11
and the indexes on the dev_ReferenceClusterRoot,
dev_Action,dev_purgestate .

Now when I fire this query
the query execution plan is actaull doing a Clustered Index scan on the PK :aribaPK11 . What I was expecting was an index seek on the key defined on dev_referenceClusterRoot. Please not the index seek is the behaviour in sql server 2000.

Any idea what is going wrong ?



Clustered Index Scan(OBJECT:([typhoon1902].[dbo].[DurableEventTab].[AribaPK7] AS [Dur1]), WHERE:([typhoon1902].[dbo].[DurableEventTab].[dev_Active] as [Dur1].[dev_Active]=(1.) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PurgeState] as [Dur1].[dev_PurgeState]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PartitionNumber] as [Dur1].[dev_PartitionNumber]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_ReferenceClusterRoot] as [Dur1].[dev_ReferenceClusterRoot]='iyrwd.52' AND [typhoon1902].[dbo].[DurableEventTab].[dev_Action] as [Dur1].[dev_Action]=N'Order:Ordered')) 0 0 Clustered Index Scan Clustered Index Scan OBJECT:([typhoon1902].[dbo].[DurableEventTab].[AribaPK7] AS [Dur1]), WHERE:([typhoon1902].[dbo].[DurableEventTab].[dev_Active] as [Dur1].[dev_Active]=(1.) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PurgeState] as [Dur1].[dev_PurgeState]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_PartitionNumber] as [Dur1].[dev_PartitionNumber]=(0) AND [typhoon1902].[dbo].[DurableEventTab].[dev_ReferenceClusterRoot] as [Dur1].[dev_ReferenceClusterRoot]='iyrwd.52' AND [typhoon1902].[dbo].[DurableEventTab].[dev_Action] as [Dur1].[dev_Action]=N'Order:Ordered') [Dur1].[rootId] 1 0.00386574 0.0002263 71 0.00409204 [Dur1].[rootId] PLAN_ROW 0 1

View 3 Replies View Related

What Is Wrong With My Codes To Connect VB.net To Sql Server 2005 Express Database?

Mar 16, 2007

Hi am very new with VB.net (Visual Studio 2005) and the first time try to use the sql server 2005 express. I did create a trial.mdf (local server) and complete procedure to attach the trial1.mdf in Microsoft SQL Management Studio Express.

I try to use VB to connect /open/close it with the following codes: (but failed)

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

Imports System.Data.SqlClient

Public Class Form1
'Link database
Dim DB_Location = "c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataTrial1.mdf"
Dim MyConnection = "Server=localhost; DataBase= " & DB_Location & "Integrated Security=SSPI"
Dim sqlConnection As New SqlConnection(MyConnection)

'Use database
Dim strSQL As String = "SELECT FROM Trial1 WHERE col2 = 'trial' "
Dim cmd As New SqlCommand(strSQL, MyConnection)

sqlConnection.Open()  ' ----> error
cmd.ExecuteNonQuery() ' ----> error
sqlConnection.Close() ' ----> error

End Class


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

At the last 3 line of codes I got the errors ... can someone explain why & help me to correct it?

Thanks for any help

View 10 Replies View Related

Visual Studio 2005 Shows Wrong SQL Server Versions

Apr 2, 2007

Visual Studio 2005 shows SQL Server 2005 installed product versions differently than the actual. As seen below VS2005 displays 9.00.3042.00 as the version of SQL Server 2005 however it is 9.00.3050.00 in SQL Server Management Studio. [Look at Installed Products section after selecting About MS Visual Studio from Help menu ]



SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.3042.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.3042.00



I have VS2005 SP1 and SQL2005 SP2 installed. Why VS2005 reports versions wrong?

View 1 Replies View Related

DateTime Parameter Is In Wrong Format When Report Is Executed For The First Time

Dec 7, 2007

In my report I have two date parameters, both are of type DateTime.
The problem is that when the report is called for the first time the report parameter value is not shown in the expected format (de-DE, 01.01.2007) but in en-US 01/01/2007
Only when I change a Date using the calendar popup and click on the "show report" button, the right format will be taken over.

Language properties of the report are"=User!Language"
IE language is de-DE.
Report Server is a MS Windows Server 2003 R2 Standart Edition SP2
with MS SQL SERVER 2005 SP2


P.S.
on a server with MS SQL SERVER 2005 SP1, this report works fine.
may be this bug is a new feature of SP2?

View 13 Replies View Related

SUM In 2005 Wrong

Apr 20, 2006

I have same database on 2000 and on 2005

I have 10 rows each with value of 100 for BudgetAmount and BudgetType = 'T'

When running

SELECT M.Market_code, M.Description,

Revenue = SUM( CASE WHEN BudgetType = 'B' THEN B.BudgetAmount ELSE 0 END ),

CM = SUM( CASE WHEN BudgetType = 'C' THEN B.BudgetAmount ELSE 0 END ),

Tooling = SUM( CASE WHEN BudgetType = 'T' THEN B.BudgetAmount ELSE 0 END )

from Budget B, Market M

Where B.BudgetYear=2006

and B.Customer_Market_code = M.Market_code

GROUP BY M.Market_code, M.Description

ORDER BY M.Description

SQL2000 shows Tooling as 1200 (correct) but SQL2005 shows 2400

Any Idea what the difference is?



View 1 Replies View Related

Uninstall Of 2005 Gone Horribly Wrong

Mar 23, 2007

I am an old hand at RDBMS but have been using SQL Server for only 1 year.  I have a local install of 2005 Developer Edition, and also access a number of 2000 and 2005 instances on remote and network servers.  OK, so on 3/16 (after installing the 3/15 Windows Security Updates), I began getting this error when invoking any 2005 DB under Object Explorer in Management Studio "SQLWB - SQL Server Management Studio has encountered a problem and needs to close.  We are sorry for the inconvenience."  Very helpful.  So I ran a debug and got some unhandled exception errors:

'SqlWb.exe': Loaded 'C:WINDOWSsystem32wbemwbemsvc.dll', No symbols loaded.
'SqlWb.exe': Loaded 'C:WINDOWSsystem32wbemfastprox.dll', No symbols loaded.
'SqlWb.exe': Loaded 'C:WINDOWSMicrosoft.NETFrameworkv2.0.50727diasymreader.dll', No symbols loaded.
'SqlWb.exe': Loaded 'C:WINDOWSsystem32apphelp.dll', No symbols loaded.
The thread 'Win32 Thread' (0x21c) has exited with code 0 (0x0).
The thread 'Win32 Thread' (0xa98) has exited with code 0 (0x0).
Unhandled exception at 0x79ea69f3 in SqlWb.exe: 0xC0000006: In page error.
First-chance exception at 0x79ea69f3 in SqlWb.exe: 0xC0000005: Access violation reading location 0x088b200c.
Unhandled exception at 0x79ea69f3 in SqlWb.exe: 0xC0000005: Access violation reading location 0x088b200c.
First-chance exception at 0x79ea69f3 in SqlWb.exe: 0xC0000005: Access violation reading location 0x088b200c.

After some poking around, my DBA told me that we had upgraded the servers for our team to SP1, and I should install that, because the file being read did not 'match up' with the commands.  But when I did so I got the warning:

- Edition Change Check (Warning)



Messages

Edition Change Check


To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.

I researched this information, but it didn't really apply to my situation.  So I selected 'CONTINUE' and everything went downhill from there.  The install downloaded the SP1 setup support files (which by the way "cannot be removed") and then aborted.  I poked around some more and decided it would be easiest to Uninstall and Reinstall the Dev Edition from the DVD and then apply the SP.  The uninstall went fine until it got to the tools (which apparently were my real problem to start with).  When I attempt to uninstall the tools, I get the following message:

"Error reading from file C:WindowsassemblyGAC_MSILMicrosoft.SqlServer.SqlEnum9.0.242.0__89845dcd8080cc91Microsoft.SqlServer.SqlEnum.dll

Verify that this file exists and that you can access it"

This message is presented with Retry and Cancel options.  The file does NOT exist, so Retry is not useful and cancel rolls back the entire uninstall. 

Error signature:

EventType : sql90setup     P1 : installfinalize     P2 : 0x643     P3 : unknown
P4 : 0x519     P5 : unknown     P6 : unknown    
P7 : sqlrun_tools.msi@9.00.1399.06    

In this part SP1/half uninstalled 2005 environment I am stuck like chuck.  I have searched for this dll online and on other folders of my hard drive to no avail.  I have also dug through these forums until my eyes are crossed.

Can anyone help?!?

View 3 Replies View Related

What's Wrong With Setup.exe For MS SQL 2005 (trial Version)

Aug 10, 2006

Hi,

I just downloaded the trial version of MS SQL 2005 from Micriosoft website.

However, when I run the setup.exe. it is to install Microsoft Office 2003.

Can anyone help me?

Thanks.

View 3 Replies View Related

Reporting Services 2005 - Showing Wrong Version

Jun 15, 2006

Hi

I installed RS 2005, SP1 and then the rollup fix 2153 however; if I run http://server/reportserver or view the reportserver log file, it shows version 2047 which is the SP1 version. I have added the tail end of the rollup hotfix log file which shows I have installed it.

Is this a known issue?

06/14/2006 16:03:57.153 Failed to read version information for the following file: C:Program FilesMicrosoft SQL ServerMSSQL.1Reporting ServicesReportServerCatalog.sql
06/14/2006 16:03:57.169 Failed to read version information for the following file: C:Program FilesMicrosoft SQL ServerMSSQL.1Reporting ServicesReportServerStylesHtmlViewer.css
06/14/2006 16:03:58.216 Attempting to install file: sqlrun_rs.msp
06/14/2006 16:03:58.263 Attempting to install file: \NLDN9577DWWd$89a527f93aa2bb044103hotfixrsFilessqlrun_rs.msp
06/14/2006 16:03:58.278 Creating MSP install log file at: C:WINDOWSHotfixRS9LogsRS9_Hotfix_KB918222_sqlrun_rs.msp.log
06/14/2006 16:03:58.294 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller
06/14/2006 16:03:58.294 Failed to read registry key: Debug
06/14/2006 16:04:44.826 MSP returned 0: The action completed successfully.
06/14/2006 16:04:45.092 Successfully opened registry key: SoftwarePoliciesMicrosoftWindowsInstaller
06/14/2006 16:04:45.107 Failed to read registry key: Debug
06/14/2006 16:04:45.123 Successfully installed file: \NLDN9577DWWd$89a527f93aa2bb044103hotfixrsFilessqlrun_rs.msp
06/14/2006 16:04:45.139 Restarting RS Service ReportServer since it was previously running
06/14/2006 16:04:45.154 Attempting to start service: ReportServer
06/14/2006 16:04:51.936 Successfully started service: ReportServer
06/14/2006 16:04:51.983 Successfully installed target: NLDN9577DWW
06/14/2006 16:04:51.998 Successfully installed instance: MSSQLSERVER
06/14/2006 16:04:52.014 Successfully opened registry key: SoftwareMicrosoftWindowsCurrentVersionUninstall
06/14/2006 16:04:52.608
06/14/2006 16:04:52.623 Product Status Summary:
06/14/2006 16:04:52.639 Product: SQL Server Reporting Services 2005
06/14/2006 16:04:52.701 MSSQLSERVER - Success
06/14/2006 16:04:52.717
06/14/2006 16:06:05.500 Hotfix package closed



Cheers

Steve

View 1 Replies View Related

What Can Go Wrong With SQL Server?

May 5, 2000

What Can Go Wrong With SQL Server?

I would like to gather a list of limitations that cause data corruption in SQL Server (documented and undocumented); the purpose is to educate myself and others about the behavior of this product, so measures can be taken to minimize data corruption problems in the future.

The sharing of your experiences is most welcome!
Thank you!

Llyal

View 1 Replies View Related

Help!!!emergency,SQL SERVER Goes Wrong

Aug 10, 2004

Our SQL SERVER seems to be sick,all queries run very very slow since three days ago. SQL query analyzer can't select a efficient index to produce execute plan and the CPU is idle and lazy even if many proccess threads waiting for handle,it is very strange!! Otherwise,there are too many deadlock existing when the 2 third users use system at same time.
I have use DBCC REINDEX to re-build all indeies.

Total data used space :11.22G:
reserved index_size data unused
--------------------------------------------------
11220056 2053432 8945432 221192

data rows in some table up to 16009830,details of some table as follow:
Table_name Rows reserved data index unused
------------------------------------------------------------------------
Worksheet_Fabric 4629414 1073568 KB 782504 KB 290360 KB 704 KB
Fab_Input_Detail 16009830 804512 KB 616896 KB 187248 KB 368 KB
Product_In 4385485 774048 KB 525896 KB 247880 KB 272 KB
Dw_note 9109810 741336 KB 736600 KB 72 KB 4664 KB
Instructions_Detail 1906960 663432 KB 592800 KB 69288 KB 1344 KB
Instructions_M_Detail 2328862 94128 KB 504824 KB 88656 KB 648 KB
Product_Out 4323939 575256 KB 364880 KB 210208 KB 168 KB
Worksheet_QC_Detail 3404309 561800 KB 507552 KB 54040 KB 208 KB

SQL SERVER VERSION:
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

result of sp_configure:
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ---------
affinity mask 0 2147483647 0 0
allow updates 0 1 0 0
cost threshold for parallelism 0 32767 5 5
cursor threshold -1 2147483647 -1 -1
default language 0 9999 0 0
default sortorder id 0 255 197 197
extended memory size (MB) 0 2147483647 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 1600000 0 0
language in cache 3 100 3 3
language neutral full-text 0 1 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max async IO 1 255 32 32
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2147483647 2147483647
max text repl size (B) 0 2147483647 65536 65536
max worker threads 10 1024 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65535 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 0 0
resource timeout (s) 5 2147483647 10 10
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
spin counter 1 2147483647 10000 10000
time slice (ms) 50 1000 100 100
two digit year cutoff 1753 9999 2049 2049
Unicode comparison style 0 2147483647 196609 196609
Unicode locale id 0 2147483647 1028 1028
user connections 0 32767 0 0
user options 0 4095 0 0

View 10 Replies View Related

Installed Wrong Version Of SQL Server

Nov 18, 2002

We installed an Enterprise version onto a server that only needs Standard. Unfortunately an outside supplier installed software and databases on it before we realised our mistake. Is it safe to just install Standard 'over the top'and what would that do to the application's databases ?

View 1 Replies View Related

Windows Groups For SQL Server - What Am I Doing Wrong?

Jul 23, 2005

Hi there,I have an Access front end application that uses a SQL Server 2000Desktop Edition backend. I have created a package with the Access2003 runtime that I'd now like to deploy. I have created a globalgroup in the domain and placed the users that I'd like to give accessto the database (via my application front end) in that group. I'vethen created a local group on the SQL Server machine and added myglobal SQL Server group to it. I've also added that local group asdatabase user from SQL Server. From my reading of the variousmaterial on the Internet this should work but it doesn't. I can'taccess the database via the application from any machine other than mydevelopment machine. My VBA code specifies integrated security forthe connection string.Help please! I'm so close to deploying this and now I'm bogged downin security. Any ideas anyone has would be appreciated. Accountadministration and Windows security is not my strong suit.Barb

View 2 Replies View Related

System Time On Sql Server Is Wrong?

Jul 23, 2005

Hello,My sql server 2000 database is on a shared server that I haveabsolutely no control over.When i run query select getdate() it is reporting the wrong time!I am pretty sure that the sql admin changed the date on the server lastnight and now this is happening.Is this possible. Shouldn't getdate() ALWAYS report back the date timein my LOCAL time zone?

View 1 Replies View Related

Wrong When I Specify The Report Server Database

Dec 10, 2007

My db version is sqlserver2005 sp2
Now, I need to install report services
However failed when i specify the report server database after report services was installed
It prompt the database version(C.0.8.40) does not match the report services installation.You must update your reporting services installation.
but how to update reporting services installation?
Thanks a lot

View 2 Replies View Related

Wrong Query Results In SQL Server 2000 From VB.NET

Jul 20, 2005

I have a relatively simple SQL Server 2000 database which I amquerying from a VB.NET application. The query is very simple, such asSELECT * FROM tblSystems. Let's say there are 10 records in the tabletblSystems. I expect to get 10 records from this query, and sometimesdo. But for some reason, I start getting 0 records from my app. If Irun the same query in SQL Server's Query Analyzer, it works fine. IfI reboot my machine, then it seems to work fine for a while, thenstarts giving me bad results again. I am running my app on the samemachine as SQL Server, although I can connect to my database remotelythrough my app, and I get the same results either way. Anyone seenanything like this? Was this fixed in a service pack (I'm using SP1,and don't want to install a newer one unless it's a known fix)?Thanks,Andrew

View 1 Replies View Related

SQL Server 2012 :: OUTPUT Clause Returning Wrong Row?

Nov 6, 2014

I'm looking at various methods for deleting duplicate rows. Among the alternatives, one works just fine but gives me results that make me go?.

Consider this script:

declare @t table (a int, b int, c int, d int, e int)
insert into @t (a, b, c, d, e) values
(1, 2, 3, 4, 5),
(3, 4, 2, 3, 4),
(1, 2, 3, 4, 5)

select a,b,c,d,e, rn = row_number() over (
partition by a,b,c,d,e

[Code] ....

The code works -- that is, the duplicate row is deleted. However the output clause returns:

abcdern
123451

So....why? Why does the output clause show that the row with rn=1 was deleted, when the where clause stipulates rn > 1?

View 9 Replies View Related

Enterprise Manager Reporting Wrong Server Version

Dec 3, 2006

I am running MS SQL 2000.I recently ran a procedure in Query Analyzer from the Master db toclear out all replication information so I could start/recreate itagain.After I ran this procedure Enterprise Manager no longer showed theregistered server in the tree. When I tried to re-register it gave methe following message:"A connection could not be established to ([Database Name])""Reason: [SQL-DMO]Sql Server ([Database Name]) must be upgraded toversion 7.0 or later to be administered by this version of SQL-DMO""Please verify that sql is running and check your SQL serverregistration properties (by right click on the ([Database Name]) node)and try again."I ran the following procedure:<code>exec sp_configure N'allow updates', 1goreconfigure with overridegoDECLARE @name varchar(129)DECLARE @username varchar(129)DECLARE @insname varchar(129)DECLARE @delname varchar(129)DECLARE @updname varchar(129)set @insname=''set @updname=''set @delname=''DECLARE list_triggers CURSOR FORselect distinct replace(artid,'-',''), sysusers.name fromsysmergearticles,sysobjects, sysusers wheresysmergearticles.objid=sysobjects.idand sysusers.uid=sysobjects.uidOPEN list_triggersFETCH NEXT FROM list_triggers INTO @name, @usernameWHILE @@FETCH_STATUS = 0BEGINPRINT 'dropping trigger ins_' +@nameselect @insname='drop trigger ' +@username+'.ins_'+@nameexec (@insname)PRINT 'dropping trigger upd_' +@nameselect @updname='drop trigger ' +@username+'.upd_'+@nameexec (@delname)PRINT 'dropping trigger del_' +@nameselect @delname='drop trigger ' +@username+'.del_'+@nameexec (@updname)FETCH NEXT FROM list_triggers INTO @name, @usernameENDCLOSE list_triggersDEALLOCATE list_triggersgoif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY(id,N'IsUserTable')= 1) begin DECLARE @name varchar(129)DECLARE list_pubs CURSOR FORSELECT name FROM syspublicationsOPEN list_pubsFETCH NEXT FROM list_pubs INTO @nameWHILE @@FETCH_STATUS = 0BEGINPRINT 'dropping publication ' +@nameEXEC sp_dropsubscription @publication=@name, @article='all',@subscriber='all'EXEC sp_droppublication @nameFETCH NEXT FROM list_pubs INTO @nameENDCLOSE list_pubsDEALLOCATE list_pubsendGODECLARE @name varchar(129)DECLARE list_replicated_tables CURSOR FORSELECT name FROM sysobjects WHERE replinfo <>0UNIONSELECT name FROM sysmergearticlesOPEN list_replicated_tablesFETCH NEXT FROM list_replicated_tables INTO @nameWHILE @@FETCH_STATUS = 0BEGINPRINT 'unmarking replicated table ' +@name--select @name='drop Table ' + @nameEXEC sp_msunmarkreplinfo @nameFETCH NEXT FROM list_replicated_tables INTO @nameENDCLOSE list_replicated_tablesDEALLOCATE list_replicated_tablesGOUPDATE syscolumns set colstat = colstat & ~4096 WHERE colstat &4096<>0GOUPDATE sysobjects set replinfo=0GODECLARE @name nvarchar(129)DECLARE list_views CURSOR FORSELECT name FROM sysobjects WHERE type='V' and (name like 'syncobj_%'ornamelike 'ctsv_%' or name like 'tsvw_%' or name like 'ms_bi%')OPEN list_viewsFETCH NEXT FROM list_views INTO @nameWHILE @@FETCH_STATUS = 0BEGINPRINT 'dropping View ' +@nameselect @name='drop View ' + @nameEXEC sp_executesql @nameFETCH NEXT FROM list_views INTO @nameENDCLOSE list_viewsDEALLOCATE list_viewsGODECLARE @name nvarchar(129)DECLARE list_procs CURSOR FORSELECT name FROM sysobjects WHERE type='p' and (name like 'sp_ins_%'ornamelike 'sp_MSdel_%' or name like 'sp_MSins_%'or name like 'sp_MSupd_%' ornamelike 'sp_sel_%' or name like 'sp_upd_%')OPEN list_procsFETCH NEXT FROM list_procs INTO @nameWHILE @@FETCH_STATUS = 0BEGINPRINT 'dropping procs ' +@nameselect @name='drop procedure ' + @nameEXEC sp_executesql @nameFETCH NEXT FROM list_procs INTO @nameENDCLOSE list_procsDEALLOCATE list_procsGODECLARE @name nvarchar(129)DECLARE list_conflict_tables CURSOR FORSELECT name From sysobjects WHERE type='u' and name like '_onflict%'OPEN list_conflict_tablesFETCH NEXT FROM list_conflict_tables INTO @nameWHILE @@FETCH_STATUS = 0BEGINPRINT 'dropping conflict_tables ' +@nameselect @name='drop Table ' + @nameEXEC sp_executesql @nameFETCH NEXT FROM list_conflict_tables INTO @nameENDCLOSE list_conflict_tablesDEALLOCATE list_conflict_tablesGOUPDATE syscolumns set colstat=2 WHERE name='rowguid'GODeclare @name nvarchar(200), @constraint nvarchar(200)DECLARE list_rowguid_constraints CURSOR FORselect sysusers.name+'.'+object_name(sysobjects.parent_ob j),sysobjects.namefrom sysobjects, syscolumns,sysusers where sysobjects.type ='d' andsyscolumns.id=sysobjects.parent_objand sysusers.uid=sysobjects.uidand syscolumns.name='rowguid'OPEN list_rowguid_constraintsFETCH NEXT FROM list_rowguid_constraints INTO @name, @constraint WHILE@@FETCH_STATUS = 0 BEGINPRINT 'dropping rowguid constraints ' +@nameselect @name='ALTER TABLE ' + rtrim(@name) + ' DROP CONSTRAINT '+@constraintprint @nameEXEC sp_executesql @nameFETCH NEXT FROM list_rowguid_constraints INTO @name, @constraint ENDCLOSE list_rowguid_constraintsDEALLOCATE list_rowguid_constraintsGODeclare @name nvarchar(129), @constraint nvarchar(129)DECLARE list_rowguid_indexes CURSOR FORselect sysusers.name+'.'+object_name(sysindexes.id), sysindexes.namefromsysindexes, sysobjects,sysusers where sysindexes.name like 'index%' andsysobjects.id=sysindexes.id and sysusers.uid=sysobjects.uidOPEN list_rowguid_indexesFETCH NEXT FROM list_rowguid_indexes INTO @name, @constraint WHILE@@FETCH_STATUS = 0 BEGINPRINT 'dropping rowguid indexes ' +@nameselect @name='drop index ' + rtrim(@name ) + '.' +@constraintEXEC sp_executesql @nameFETCH NEXT FROM list_rowguid_indexes INTO @name, @constraint ENDCLOSE list_rowguid_indexesDEALLOCATE list_rowguid_indexesGODeclare @name nvarchar(129), @constraint nvarchar(129)DECLARE list_ms_bidi_tables CURSOR FORselect sysusers.name+'.'+sysobjects.name fromsysobjects,sysusers where sysobjects.name like 'ms_bi%'and sysusers.uid=sysobjects.uidand sysobjects.type='u'OPEN list_ms_bidi_tablesFETCH NEXT FROM list_ms_bidi_tables INTO @nameWHILE @@FETCH_STATUS = 0BEGINPRINT 'dropping ms_bidi ' +@nameselect @name='drop table ' + rtrim(@name )EXEC sp_executesql @nameFETCH NEXT FROM list_ms_bidi_tables INTO @nameENDCLOSE list_ms_bidi_tablesDEALLOCATE list_ms_bidi_tablesGODeclare @name nvarchar(129)DECLARE list_rowguid_columns CURSOR FORselect sysusers.name+'.'+object_name(syscolumns.id) from syscolumns,sysobjects,sysusers where syscolumns.name like 'rowguid' andobject_Name(sysobjects.id) not like 'msmerge%'and sysobjects.id=syscolumns.idand sysusers.uid=sysobjects.uidand sysobjects.type='u' order by 1OPEN list_rowguid_columnsFETCH NEXT FROM list_rowguid_columns INTO @nameWHILE @@FETCH_STATUS = 0BEGINPRINT 'dropping rowguid columns ' +@nameselect @name='Alter Table ' + rtrim(@name ) + ' drop column rowguid'print @nameEXEC sp_executesql @nameFETCH NEXT FROM list_rowguid_columns INTO @nameENDCLOSE list_rowguid_columnsDEALLOCATE list_rowguid_columnsgoDeclare @name nvarchar(129)DECLARE list_views CURSOR FORselect name From sysobjects where type ='v' and status =-1073741824 andname<>'sysmergeextendedarticlesview'OPEN list_viewsFETCH NEXT FROM list_views INTO @nameWHILE @@FETCH_STATUS = 0BEGINPRINT 'dropping replication views ' +@nameselect @name='drop view ' + rtrim(@name )print @nameEXEC sp_executesql @nameFETCH NEXT FROM list_views INTO @nameENDCLOSE list_viewsDEALLOCATE list_viewsgoDeclare @name nvarchar(129)DECLARE list_procs CURSOR FORselect name From sysobjects where type ='p' and status = -536870912OPEN list_procsFETCH NEXT FROM list_procs INTO @nameWHILE @@FETCH_STATUS = 0BEGINPRINT 'dropping replication procedure ' +@nameselect @name='drop procedure ' + rtrim(@name )print @nameEXEC sp_executesql @nameFETCH NEXT FROM list_procs INTO @nameENDCLOSE list_procsDEALLOCATE list_procsgoif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysmergepublications]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM sysmergepublicationsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM sysmergesubscriptionsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM syssubscriptionsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysarticleupdates]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM sysarticleupdatesGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[systranschemas]') and OBJECTPROPERTY(id,N'IsUserTable')= 1)DELETE FROM systranschemasGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysmergearticles]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM sysmergearticlesGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysmergeschemaarticles]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM sysmergeschemaarticlesGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysmergesubscriptions]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM sysmergesubscriptionsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY(id,N'IsUserTable') =1)DELETE FROM sysarticlesGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysschemaarticles]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM sysschemaarticlesGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[syspublications]') and OBJECTPROPERTY(id,N'IsUserTable')= 1)DELETE FROM syspublicationsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysmergeschemachange]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM sysmergeschemachangeGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysmergesubsetfilters]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM sysmergesubsetfiltersGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSdynamicsnapshotjobs]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSdynamicsnapshotjobsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSdynamicsnapshotviews]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSdynamicsnapshotviewsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSmerge_altsyncpartners]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSmerge_altsyncpartnersGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSmerge_contents]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSmerge_contentsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSmerge_delete_conflicts]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSmerge_delete_conflictsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSmerge_errorlineage]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSmerge_errorlineageGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSmerge_genhistory]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSmerge_genhistoryGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSmerge_replinfo]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSmerge_replinfoGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSmerge_tombstone]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSmerge_tombstoneGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSpub_identity_range]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSpub_identity_rangeGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSrepl_identity_range]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSrepl_identity_rangeGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSreplication_subscriptions]') andOBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSreplication_subscriptionsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[MSsubscription_agents]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)DELETE FROM MSsubscription_agentsGOif not exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[syssubscriptions]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)create table syssubscriptions (artid int, srvid smallint, dest_dbsysname,status tinyint, sync_type tinyint, login_name sysname,subscription_typeint, distribution_jobid binary, timestamp timestamp,update_modetinyint,loopback_detection tinyint, queued_reinit bit)CREATE TABLE [dbo].[syspublications] ([description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[name] [sysname] NOT NULL ,[pubid] [int] IDENTITY (1, 1) NOT NULL ,[repl_freq] [tinyint] NOT NULL ,[status] [tinyint] NOT NULL ,[sync_method] [tinyint] NOT NULL ,[snapshot_jobid] [binary] (16) NULL ,[independent_agent] [bit] NOT NULL ,[immediate_sync] [bit] NOT NULL ,[enabled_for_internet] [bit] NOT NULL ,[allow_push] [bit] NOT NULL ,[allow_pull] [bit] NOT NULL ,[allow_anonymous] [bit] NOT NULL ,[immediate_sync_ready] [bit] NOT NULL ,[allow_sync_tran] [bit] NOT NULL ,[autogen_sync_procs] [bit] NOT NULL ,[retention] [int] NULL ,[allow_queued_tran] [bit] NOT NULL ,[snapshot_in_defaultfolder] [bit] NOT NULL ,[alt_snapshot_folder] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[pre_snapshot_script] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[post_snapshot_script] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[compress_snapshot] [bit] NOT NULL ,[ftp_address] [sysname] NULL ,[ftp_port] [int] NOT NULL ,[ftp_subdirectory] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CI_ASNULL ,[ftp_login] [sysname] NULL ,[ftp_password] [nvarchar] (524) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[allow_dts] [bit] NOT NULL ,[allow_subscription_copy] [bit] NOT NULL ,[centralized_conflicts] [bit] NULL ,[conflict_retention] [int] NULL ,[conflict_policy] [int] NULL ,[queue_type] [int] NULL ,[ad_guidname] [sysname] NULL ,[backward_comp_level] [int] NOT NULL) ON [PRIMARY]GOcreate view sysextendedarticlesviewasSELECT *FROM sysarticlesUNION ALLSELECT artid, NULL, creation_script, NULL, description,dest_object,NULL, NULL, NULL, name, objid, pubid, pre_creation_cmd, status, NULL,type,NULL,schema_option, dest_ownerFROM sysschemaarticlesgoif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysarticles]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[sysarticles]GOCREATE TABLE [dbo].[sysarticles] ([artid] [int] IDENTITY (1, 1) NOT NULL ,[columns] [varbinary] (32) NULL ,[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[del_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[dest_table] [sysname] NOT NULL ,[filter] [int] NOT NULL ,[filter_clause] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[ins_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[name] [sysname] NOT NULL ,[objid] [int] NOT NULL ,[pubid] [int] NOT NULL ,[pre_creation_cmd] [tinyint] NOT NULL ,[status] [tinyint] NOT NULL ,[sync_objid] [int] NOT NULL ,[type] [tinyint] NOT NULL ,[upd_cmd] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[schema_option] [binary] (8) NULL ,[dest_owner] [sysname] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sysschemaarticles]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[sysschemaarticles]GOCREATE TABLE [dbo].[sysschemaarticles] ([artid] [int] NOT NULL ,[creation_script] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL,[description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[dest_object] [sysname] NOT NULL ,[name] [sysname] NOT NULL ,[objid] [int] NOT NULL ,[pubid] [int] NOT NULL ,[pre_creation_cmd] [tinyint] NOT NULL ,[status] [int] NOT NULL ,[type] [tinyint] NOT NULL ,[schema_option] [binary] (8) NULL ,[dest_owner] [sysname] NULL) ON [PRIMARY]GOdeclare @dbname varchar(130)select @dbname ='sp_replicationdboption'+char(39)+db_name()+char(39)+',''merge publish'',''false'''exec (@dbname)select @dbname ='sp_replicationdboption'+char(39)+db_name()+char(39)+',''publish'',''fals e'''exec (@dbname)reconfigure with overridegoselect db_name()</code>Can any one please help me as this is a production machine and needsfixing ASAP.Regards,Ben

View 2 Replies View Related

Wrong Version Of SQL Server Mobile Deployed To Emulator?

Feb 2, 2006

I am developing a mobile application with Visual Studio 2005 RTM. I created a .SDF database from scratch within the Server Explorer window. Based on the properties of the database, this is a version 3.0 DB. I then deploy the application to the Pocket PC 2003 SE Emulator and I receive an error when trying to open the database from my app. It's a very generic error:

System.SData.SqlServerCE.SqlCEException

I trap the error in my code, but there is no message in the exception object.

If I try to open it with Query Analyzer on the device, I get an error stating "The file is not a valid database file". It appears that SQL CE 2.0 was deployed to the emulator.

I noticed that when I add a reference in my project to the System.Data.SQLServerCE namespace, it defaults to this DLL:

C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKSQL ServerMobilev2.0System.Data.SqlServerCe.dll

I tried removing that and manually adding a reference by browsing to this 3.0 DLL:

C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKSQL ServerMobilev3.0System.Data.SqlServerCe.dll

I then receive the following build error:

Unable to load referenced library 'C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKSQL ServerMobilev3.0System.Data.SqlServerCe.dll': Version 2.0 is not a compatible version.

If I build the database in my application from scratch from within my code, everything works fine, which I believe is because it is a 2.0 database that is generated.

To make a long story short:

How do I get my project to reference the 3.0 SQL Server CE namespace and deploy it to the emulator?

View 1 Replies View Related

SQL Server Agent Job Fails Because Of Wrong Log Provider Type?

Jan 10, 2008

I created a SSIS package which logs to the event log (containing a script task and a file system task). I deployed the package using the manifest utility on a 2003 server. If I run the package using the DTexec utility logged on with a domain user account it works fine but if I use the same domain account as credential for my proxy used to run a job with the SQL Server Agent, the job fails. The log viewer tells me the following:
Started: 13:07:19 Error: 2008-01-10 13:07:19.97 Code: 0xC001403D Source: Description: The log provider type "DTS.LogProviderEventLog.1" specified for log provider "SSIS log provider for Windows Event Log" is not recognized as a valid log provider type. This error occurs when an attempt is made to create a log provider for unknown log provider type. Verify the spelling in the log provider type name. End Error Error: 2008-01-10 13:07:19.97 Code: 0xC0010018 Source: Description: Error loading value "<DTS:LogProvider xmlnsTS="www.microsoft.com/SqlServer/Dts"><DTSroperty DTS:Name="ConfigString"></DTSroperty><DTSroperty DTS:Name="DelayValidation">0</DTSroperty><DTSroperty DTS:Name="ObjectName">SSIS log provider for Windows Event Log</DTSrop" from node "DTS:LogProvider". End Error Could n... The package could not be lo... The step failed.
The job succeeds if I grant the domain account local administrative rights.
What's wrong, who can help? Thanks.

View 5 Replies View Related

SQL Server 2008 :: Merge Moves Data Into Wrong Filegroup?

Feb 18, 2015

I've got a partitioned table where I am trying to switch the first partition into a staging table, merge the first boundary and later drop the file and the file group.

This is my starting point:

boundary_idBoundary_valuePartitionNumber
11/09/2012 0:002
21/10/2012 0:003
PartitionNumberPartitionRowsFileGroupName
1799AdtLog_Archive_201208
2300AdtLog_Archive_201209

After I switch partition 1 to a staging table I run:

ALTER PARTITION SCHEME My_ps NEXT USED AdtLog_Archive_201209;

and

ALTER PARTITION FUNCTION My_pf() MERGE RANGE ('2012-09-01 00:00:00.000');

I expect the 300 records from the former 2nd partition to stay in AdtLog_Archive_201209, however I get this:

PartitionNumberPartitionRowsFileGroupName
1300AdtLog_Archive_201208
2310AdtLog_Archive_201210

How do I make sure that the data stays in AdtLog_Archive_201209 file group?

View 1 Replies View Related

Problem Restoring SQL Svr 2000 Master Db - Wrong Server Version?

Jul 20, 2005

I'm trying to rebuild from a meltdown. I'm using disk backup files. Icould have sworn that I had SQL Server 2000 SP3 installed on the oldmachine, but maybe not.With SP3 installed, I get a message along the lines that the restorecan't be done because the backup was created using server version134218262 and this server is version 134218488.I've searched the documentation and can't find any reference to thoseversion numbers.In any case, I thought SQL Server 2000 could restore backups createdon any SP level to any SP level.Any ideas?--Regards.Richard.

View 4 Replies View Related

In The Server Explorer Generate Create Script Is Greyed Out, Whats Wrong?

Jan 14, 2005

In the server explorer "generate create script" is greyed out, what am I doing wrong?

I want to generate a script that will re-create my DB schema on another machine.

Any help would be appreciated.
Thanks

View 3 Replies View Related

SQL Server 2008 :: Balance Sheet Query Showing Wrong Amounts On VAT Accounts Only

May 21, 2015

I have one query which is pulling Balance sheet amounts from SAP Business One database. The query is giving the correct figures for the rest of the accounts except for the VAT Input refundable account 123600 and VAT Output Payable account 221400. The query sums up totals at Title account level(FatherNum) and the above accounts are the title accounts:

SELECT CAST(T0.TransId AS Varchar(30)) AS TransId, CASE WHEN t3.FatherNum IN ('100000', '350000') THEN '-3 OK' ELSE CAST(T0.TransType AS Varchar(30))
END AS TransType, CAST(T0.BaseRef AS VarChar(30)) AS BaseRef, T0.RefDate,T0.Number as Docnum, DATEPART(Month, T0.RefDate) AS JrnMonth, T0.FinncPriod, T1.Account, T1.Debit,
T1.Credit, T1.Debit - T1.Credit AS JrnAmt, ISNULL(T1.SYSCred, 0) AS SysCred, ISNULL(T1.SYSDeb, 0) AS SysDeb, T1.ShortName, T1.Ref1, T1.Ref2,

[code]....

View 8 Replies View Related

Common Table Expression (CTE):How To Delete A Wrong CTE That Is In SQL Server Management Studio Express (SSMSE)?

Feb 25, 2008

Hi all,

I ran the following CTE sql code:


Use ChemDatabase

GO

WITH PivotedTestResults AS

(

SELECT TR.AnalyteName, TR.Unit,

Prim = MIN(CASE S.SampleType WHEN 'Primary' THEN TR.Result END),

Dupl = MIN(CASE S.SampleType WHEN 'Duplicate' THEN TR.Result END),

QA = MIN(CASE S.SampleType WHEN 'QA' THEN TR.Result END)

FROM TestResults TR

JOIN Samples S ON TR.SampleID = S.SampleID

GROUP BY TR.AnalyteName, TR.Unit

)

SELECT AnalyteName, UnitForConc,

avg1 = abs(Prim + Dupl) / 2,

avg2 = abs(Prim + QA) / 2,

avg3 = abs(Dupl + QA) / 2,

RPD1 = abs(Prim - Dupl) / abs(Prim + Dupl) * 2,

RPD2 = abs(Prim - QA) / abs(Prim + QA) * 2,

RPD2 = abs(Dupl - QA) / abs(Dupl + QA) * 2

FROM PivotedTestResults

GO

//////////////////////////////////////////////////////////////////////////////////////
I got the following errors:

Msg 207, Level 16, State 1, Line 9

Invalid column name 'Unit'.

Msg 207, Level 16, State 1, Line 3

Invalid column name 'Unit'.

////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I guess that I had "Unit" (instead of "UnitForConc"), when I executed the sql code last time!!!???
How can I delete the old, wrong CTE that is already in the ChemDatabase of my SSMSE?

Please help and advise.

Thanks in advance,
Scott Chang

View 5 Replies View Related

Is Wrong Version Of SQL Server Active On Server

Dec 22, 2006

A couple of days ago I installed SQL Server Developer Edition onto my server "DELLNOV2006" which already had an instance of SQL Server 2000 Developer Edition.

All seemed fine, but today trying to use SQL SMAS, I received the error "This version of SQL server is not supported. You must upgrade to an instance of SQL server 2005.

So I opened MSFT SQL Server Management Studio and in object explorer saw the highest object was "DELLNOV2006 (SQL Server 8.00.2039 DELLNOV2006MST).

In the query window SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') is returning 8.00.2039

That's SQL Server 200 SP 4, so it looks like SL Server 2005 is either not installed or is somehow not the default.

According to 'Remove Software' window, I have both SQL Server 200 and 2005 installed.

How can I get SQL 2005 as the default? Then I can get rid of 2000.

Thanks
Mike Thomas

View 3 Replies View Related

Something Is Wrong With This

May 1, 2008

i can't seem to get this query to work, it just keep returning nulls with ever values i set .  1 SELECT Bedrooms, Description, Image,
2 (SELECT Location
3 FROM Location_Table
4 WHERE (Property_Table.LocationID = LocationID)) AS Location, LocationID, Price, Price AS PriceMax, PropertyID, Title, TypeID,
5 (SELECT TypeOfProperty
6 FROM Type_Table
7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty
8 FROM Property_Table
9 WHERE (TypeID = @TypeID OR
10 TypeID IS NULL) AND (LocationID = @LocationID OR
11 LocationID IS NULL) AND (Price >= @MinPrice OR
12 Price IS NULL) AND (PriceMax <= @MaxPrice OR
13 PriceMax IS NULL)
  

View 7 Replies View Related

What's Wrong Here ???

May 14, 2004

This is working:

SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
"MONTH(Some_Date) >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
This is NOT:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
Month >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
it says - Invalid column name 'Month'

Why ? Why ? Why ?

View 3 Replies View Related







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