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
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 thatAlso, 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 FunctionThanks!
View 1 Replies View RelatedOur 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
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 RelatedHi 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 RelatedHello,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 RelatedMy 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
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
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 RelatedA 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
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?
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
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 RelatedI 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?
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.
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?
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 RelatedHi 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
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?
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
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.
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]....
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
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
After updating TempDB path to a wrong path (without file name only folder name) the service is not starting. How can i sovle this and start the service
thanks
Leena
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)
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 ?
I'm just learning SQL after using it for about a year now and I'm trying to add a Check constraint to a Social Security Field (See Below) and I can't figure out what is wrong with the syntax. In QA it errors out stating: Line 4: Incorrect syntax near '0-9'.
use Accounting
Alter Table Employees
Add Constraint CK_SNN
Check (SSN Like [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9])
Any help would be nice. Thanks in advance.
Hello !! I have just createt a simple login page and reg page, login is working when I make one useraccound directly on to MsSql server, I can login successfully, but the problem is REGISTER PAGE with INSERT code. Here down is the code ov the login.aspx page
Function AddUser(ByVal userID As Integer, ByVal userName As String, ByVal userPassword As String, ByVal name As String, ByVal email As String) As Integer
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='music'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [users] ([UserID], [UserName], [UserPassword], [Name], [Email]) VALUE"& _
"S (@UserID, @UserName, @UserPassword, @Name, @Email)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userID.ParameterName = "@UserID"
dbParam_userID.Value = userID
dbParam_userID.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_userID)
Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userName.ParameterName = "@UserName"
dbParam_userName.Value = userName
dbParam_userName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userName)
Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userPassword.ParameterName = "@UserPassword"
dbParam_userPassword.Value = userPassword
dbParam_userPassword.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userPassword)
Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_name.ParameterName = "@Name"
dbParam_name.Value = name
dbParam_name.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_name)
Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_email.ParameterName = "@Email"
dbParam_email.Value = email
dbParam_email.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_email)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
Sub LoginBtn_Click(sender As Object, e As EventArgs)
If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Message.Text = "Register Successed, click on the link WebCam for login"
Else
Message.Text = "Failure"
End If
End Sub
and here is the error I receive when I try to open this register.aspx page:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30455: Argument not specified for parameter 'email' of 'Public Function AddUser(userID As Integer, userName As String, userPassword As String, name As String, email As String) As Integer'.
Source Error:
Line 52: Sub LoginBtn_Click(sender As Object, e As EventArgs)
Line 53:
Line 54: If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Line 55: Message.Text = "Register Successed, click on the link WebCam for login"
Line 56:
Source File: c:inetpubwwwrootweb_sitemusic
egister.aspx Line: 54
In the DB the fields are added as :
UserID as int
UserName as varchar
UserPassword as varchar
Name as varchar
Email as varchar
and I have TRYED to change from "varchar" on to "text" but I receive same error message.
PLEASE HELP !!!!!!!! this is not first time I get the same errors on the all reg pages :( WHY ? WHAT LINE I HAVE TO EDIT ?
Thank You !!!
Regards
Hi please lok at this SP I have written and point where am I commiting mistake.
The PROD_ID_NUM field is a varchar field in the actual database.
Any help appreciated.
CREATE PROCEDURE [cp_nafta_dws].[spMXGetProductDetails]
(
@ProductCode Int = null
)
AS
DECLARE @sqlString AS nvarchar(2000)
SET @sqlString = 'SELECT Master.PROD_ID_NUM AS ProductCode,
Master.PROD_DESC_TEXT AS ProductName,
Detail.PiecesPerBox, Detail.Price
FROM cp_nafta_dws.PRODUCT AS Master
INNER JOIN cp_nafta_dws.PRODUCT_MEXICO AS Detail
ON Master.PROD_ID_NUM = Detail.PROD_ID_NUM'
BEGIN
IF NOT (@ProductCode = NULL)
BEGIN
SET @sqlString = @sqlString + ' WHERE Master.PROD_ID_NUM = ' + @ProductCode
END
END
EXEC @sqlString
GO
Thanks
Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does it not bring anything when Where is there?
Thanks,
This code is not updating the database, please help me
SqlCommand UpdCmd; SqlCommand SelCmd; SqlDataAdapter da; DataSet ds = new DataSet();
da = new SqlDataAdapter(); if (!(Conn.State == ConnectionState.Open)) { Conn.Open(); }
SelCmd = null; SelCmd = new SqlCommand("sp_SelectUserInfo",Conn); SelCmd.CommandType = CommandType.StoredProcedure;
oSelCmd.Parameters.Add("@UserID",userid);
da.SelectCommand = oSelCmd;
da.Fill(ds,"UserTab"); oUpdCmd = null; UpdCmd = new SqlCommand("sp_UpdateUserInfo",Conn); UpdCmd.CommandType = CommandType.StoredProcedure;
UpdCmd.Parameters.Add("@UserID",userid); UpdCmd.Parameters.Add("@FirstName",firstName); UpdCmd.Parameters.Add("@LastName",lastName); UpdCmd.Parameters.Add("@Region",region); da.UpdateCommand = UpdCmd; da.Update(ds,"UserTab");
Conn.Close();
Here is the codeLine 84: Line 85: searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title=" & title, searchConnection)Line 86: searchDataAdapter.Fill(objItemInfo, "ItemInfo")Line 87: Line 88: Return objItemInfohere is the errorLine 1: Incorrect syntax near '='. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.Source Error:
View 3 Replies View Related