SQLserver on Small Business Server for Win 2003.
Client application updates records in DB. i.e. the content of a field is
updated from "First" to "Second"
Client application closed and opend again. DB returns value "Second".
Next morning _SOME_ - not all - of the updates seems missing. DB returns
the value "First"
The client software has run for years i several thousand installations,
with MSDE/SQLserver on w2k and 2003 servers.
This is the first time we have seen this spooky behavior. And it is the
first time we are targeting SBS.
Could there be some unflushed cache between client and SQLserver?
I will be installing a new, SBS2003 R2 server that comes with SQL 2005 Workgroup Edition. We currently have a SQL 2000 Standard server. Can I installed SQL2000 Standard on SBS2003 R2? The reason for this is that we're not ready to migrate to SQL2005 and we need to continue running SQL2000.
I am posting this to hopefully help someone else that encounters the same issue in the future...
Server: SBS 2003 Premium, with exchange and with all service packs/patches applied. Server-name: NEWSERVER Server migrated from: OLDSERVER Important notes:
This server was migrated from another SBS 2003 on different HW following the instructions provided by microsoft. The oldserver had exchange and sql 2005 installed on it. The new server has Office Accounting 2005 installed, but I don't think that matters...
I am trying to install SQL 2005 from the SBS2003-R2 DVD onto the new server, and get the following error:
Error: ---
TITLE: Microsoft SQL Server 2005 Setup ------------------------------
SQL Server Setup failed to modify security permissions on registry key SOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServerSuperSocketNetLib for user Administrator. To proceed, verify that the account and domain running SQL Server Setup exist, that the account running SQL Server Setup has administrator privileges, and that the registry key exists on the destination drive.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29508&EvtType=sqlca%5csqlsddlca.cpp%40Do_sqlRegSDDL%40ExceptionInSDDL%40x7344
I looked in the registry, and the administrators group has full control over this key.
Digging into the SQLSETUP log file, at the end I see: ---
Configuring ACL: Object: HKLMSOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2MSSQLServerSuperSocketNetLib ACL: (A;CI;KR;;;[SQLServer2005SQLBrowserUser$NEWSERVER])(A;CI;KR;;;NS) Action: 0x100 Failed ACL: ReplaceSDDLSid is failed at the error code 1332; Converted SDDL: '(A;CI;KR;;;[SQLServer2005SQLBrowserUser$NEWSERVER])(A;CI;KR;;;NS)' Error Code: 0x80077344 (29508) Windows Error Text: Source File Name: sqlcasqlsddlca.cpp Compiler Timestamp: Tue Sep 13 01:08:29 2005 Function Name: ExceptionInSDDL Source Line Number: 65
---
Looking into AD Users+Computers, there is not a group present for SQLServer2005SQLBrowserUser$NEWSERVER but there is one for SQLServer2005SQLBrowserUser$OLDSERVER.
It appears that the install did not create the new group that was necessary..
Once I duplicated the OLDSERVER group, renaming it to have NEWSERVER, the installation completed without error.
I hope this saves someone else a few hours of pain.
My Problem is Hoe the Stored Procedure returns data to the Calling Env.If it is fieds values then we can use the OUT Parameters.In Case of My SP returns more number of rows .Then how we capture the data and returs to the Calling Env.i know that we can use the Cursors in Oracle but how is it in SQLSERVER???????pls
pls Help me throw some samll exps.Any help Really appriciate..ThaksRams
I have a table which has a row that contains a subreport. I'd like to set the visibility of the row to hidden if the subreport returns no data. I've played with a few techniques but can't seem to get it right. Any ideas?
DECLARE @strRole nvarchar(15), @ContactID int SELECT @strRole = Role, @ContactID = ContactID FROM Contact WHERE UserName = @strUserName Select DISTINCT Contact.ContactID ID, UPPER(Surname + 'gd ' + Forename ) AS Description, UPPER(Surname + ' ' + Forename + ' ' + ContactReference) AS Description_CR , UPPER(ISNULL(ContactReference,'')) ContactReference FROM Contact LEFT JOIN AssignedEmployee on Contact.ContactID = AssignedEmployee.ContactID WHERE RUEmployee=1 AND ( ((@CourseID = 0 AND @blnIsSearch=1 ) OR COALESCE(AssignedEmployee.CourseID,0) = @CourseID)) AND (@ProjectID = 0 OR COALESCE(AssignedEmployee.ProjectID,0) = @ProjectID) AND ( (@strRole = 'MIS_TUTOR' AND (AssignedEmployee.ContactID = @ContactID OR CourseID IN (SELECT CourseID FROM AssignedEmployee WHERE ContactID = @ContactID AND Lead = 1) OR AssignedEmployee.ProjectID IN (SELECT ProjectID FROM AssignedEmployee WHERE CourseID IS NULL AND Lead = 1 AND ContactID = @ContactID)) ) OR (@strRole <> 'MIS_TUTOR') ) SET CONCAT_NULL_YIELDS_NULL ON GO
now if i run this stored procedure in Query Analyzer like so...
but if i lift the SQL out of the stored procedure and run it in Query Analyzer like so....
Code Block SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @ProjectID int, @CourseID int, @blnIsSearch int, @strUserName nvarchar(20) SET @ProjectID = 0 SET @CourseID = 0 SET @blnIsSearch = 1 SET @strUserName = 'K_T'
DECLARE @Role nvarchar(15), @ContactID int SELECT @Role = Role, @ContactID = ContactID FROM Contact WHERE UserName = @strUserName PRINT @ContactID Select DISTINCT Contact.ContactID ID, UPPER(Surname + ' ' + Forename ) AS Description, UPPER(Surname + ' ' + Forename + ' ' + ContactReference) AS Description_CR , UPPER(ISNULL(ContactReference,'')) ContactReference FROM Contact LEFT JOIN AssignedEmployee on Contact.ContactID = AssignedEmployee.ContactID WHERE RUEmployee=1 AND ( ((@CourseID = 0 AND @blnIsSearch=1 ) OR COALESCE(AssignedEmployee.CourseID,0) = @CourseID)) -- the above line was modified to make sure only employees explicitly assigned to a project are brought back. unless it's a search AND (@ProjectID = 0 OR COALESCE(AssignedEmployee.ProjectID,0) = @ProjectID) AND ( (@Role = 'MIS_TUTOR' AND ( (AssignedEmployee.ContactID = @ContactID OR CourseID IN (SELECT CourseID FROM AssignedEmployee WHERE ContactID = @ContactID AND Lead = 1))) OR AssignedEmployee.ProjectID IN (SELECT ProjectID FROM AssignedEmployee WHERE CourseID IS NULL AND Lead = 1 AND ContactID = @ContactID) ) OR (@Role <> 'MIS_TUTOR') )
SET CONCAT_NULL_YIELDS_NULL ON
i only get 5 records returned???
so why do i get a difference when its the same SQL??
Username 'K_T' is of role 'MIS_TUTOR' therefore @Role = 'MIS_TUTOR'
any help on unravelling this mystery is appreciated!
I'm working with PivotTable on Excel 2000 which is connected to an OLAP server (from SQL Server 7 installation). The pivot is intended to analyze Sales during April 2001. Yesterday I found out that OLAP/Excel returned/displayed inconsistent data. The 'April Total' value is NOT equal to the 'Quarter 2 Total' (I already inspected the underlying database and sure that there is absolutely NO data for months after April 2001). The value for 'April Total' is the correct one. I'm not sure whether the problem resides on the OLAP Server or Excel (pivot) itself. For ones who like to help me I would be glad to supply you with the screenshots (just email me). Please help.
I am migrating from 2000 to 2005 and in the process of rewriting DTS to SSIS. So far so good.
I have an import of a flat file that I need to reproduce that works fine in DTS but throws SSIS.
The file contains details of products, and has a Long Description field that may contain lots of different chars - CR & LF amongst them. It's source and destination columns are Text type.
The file is tab delimited, text delimited with double quotes, with row delimiters of CRLF and column names on row 1.
The error SSIS throws is "The column delimiter for column "LongDescription" was not found"
I imagine (perhaps wrongly) that this is because of the extra CRLFs but it worked fine in DTS. It also previews fine, and lets me define the column properties OK in SSIS package designer.
Any help greatly appreciated. I am trying to avoid the obvious thing of replacing those chars in that field at this point as currently the export is shared between a live production server, and my test 2005 rig.
The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"
I get the result as: Name Address ---- ------- No rows affected. (1 row(s) returned)
But not the actual row is displayed whereas I would expect to see something like:
Name Address
---- -------
John Doe
No rows affected.
(1 row(s) returned)
I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.
I created a Market Basket Data Mining Model with Association Rules, which I want to query and show in a Report. Everything works fine, when I preview the result in the Reporting Services Data tab I see some sort of table which I can expand and then see the related products.
Unfortunately this result seems to be an ADOMD Datareader which I cannot place in a table, matrix or textfield.
If anyone knows how I can make this Informationen available in my Report please let me know.
I have a Gridview bound to a SQLDataSource that uses a Stored Procedure expecting 1 or 2 parameters. The parameters are bound to textbox controls. The SP looks up a person by name, the textboxes are Last Name, First Name. It will handle last name only (ie. pass @ln ='Jones' and @fn=null and you get all the people with last name=Jones. I tested the SP in Management Studio and it works as expected. When I enter a last name in the Last Name textbox and no First Name I get no results. If I enter a Last Name AND First Name I get results. I don't understand. Here's the HTML View of the page. The only code is to bind the Gridview when the Search button is pressed. <%@ Page Language="VB" AutoEventWireup="true" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:TextBox ID="TextBox1" runat="server" TabIndex=1></asp:TextBox> <asp:TextBox ID="TextBox2" runat="server" TabIndex=2></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Search" TabIndex=3 /> <hr /> </div> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="EmpID" CellPadding="4" EnableSortingAndPagingCallbacks="True" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="EmpID" HeaderText="Emp ID" ReadOnly="True" SortExpression="EmpID" /> <asp:BoundField DataField="FullName" HeaderText="Full Name" SortExpression="FullName" /> <asp:BoundField DataField="Nickname" HeaderText="Nickname" ReadOnly="True" SortExpression="Nickname" /> <asp:BoundField DataField="BGS2" HeaderText="BGS2" SortExpression="BGS2" /> <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /> <asp:BoundField DataField="email" HeaderText="Email" SortExpression="email" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EmpbaseConnectionString %>" SelectCommand="GetByName" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:ControlParameter ControlID="TextBox2" Name="fn" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> <asp:ControlParameter ControlID="TextBox1" Name="ln" PropertyName="Text" Type="String" ConvertEmptyStringToNull=true/> </SelectParameters> </asp:SqlDataSource> </form> </body></html>
I'm pretty new to .NET and am looking for advice on how to speed up a simple stored procedure that returns 35,000 plus rows. It's a super simple query that returns a client list. It's just that there is soooooo many rows, it's super slow when the page loads.
Hi, whenever the underlying query being called by EXEC in the followinghas an empty result set I get the following error -- Invalid Data for'Numeric' when EXEC returns empty row. However if I call the querywithout using REPLACE (which I'm forced to do, because openquery doesnot allow variables), I get just an empty result set. Whenever theunderlying query returns a non-empty result set, the code works withouterror (regardless of wether there are nulls in the numeric column).set @switch ='5707550'set @start_date = '01-JAN-2006'set @end_date = '27-JAN-2006'set @month = 1set @year = 2006set @sql_str='SELECT * FROM(select MSC_KEY,to_char(trunc(TSTAMP), ''yyyy-Mon-dd'') as "Timestamp",ROUND( NVL(SUM(SUNRGMMSCBHCP1.XASUTIL),0) / DECODE (NVL(SUM(SUNRGMMSCBHCP1.XASNXFR),0),0,NULL,NVL(SUM( SUNRGMMSCBHCP1.XASNXFR),0)), 5)as "PER_CPU_UTIL"FROM NOR_GSM_COMPOSITE_MSC1_BHCPP SUNRGMMSCBHCP1,mscs_view vWHERE SUNRGMMSCBHCP1.gsm_msc_key = v.msc_key and v.MSC_KEY in (' +@switch + ')and SUNRGMMSCBHCP1.TSTAMP between to_date(''' + @start_date + '00:00:00'', ''DD-MON-YYYY HH24:MI:SS'') andto_date(''' + @end_date + ' 23:59:00'', ''DD-MON-YYYYHH24:MI:SS'')group by MSC_KEY, trunc(tstamp))WHERE rownum < 10000'SET @sql_str = N'select * from OPENQUERY(VISION, ''' +REPLACE(@sql_str, '''', '''''') + ''')'EXEC (@sql_str);Is there anyway to prevent this error?Thanks,Crazy
I am trying to get data from an Oracle view using an OLE DB data source and a "SQL Command". When I "preview" the data it looks fine, but when I execute the package I get the following error:
Error: 0xC0202009 at Data Flow Task, CEDAR View [1]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-01403: no data found". Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "CEDAR View" (1) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. Error: 0xC0047021 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. Error: 0xC0047039 at Data Flow Task, DTS.Pipeline: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?
Hi there everyone. I have a stored procedure called “PagingTable� that I use for performing searches and specifying how many results to show per ‘page’ and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:
The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In ‘classic’ ASP I did this like this.
'Open the recordset rsItems.Open strSQL, conn, 0, 1
'Get the values required for drawing the paging table intCurrentPage = rsItems.Fields("CurrentPage").Value intTotalPages = rsItems.Fields("TotalPages").Value intTotalRows = rsItems.Fields("TotalRows").Value
'Advance to the next recordset Set rsItems = rsItems.NextRecordset
I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:
A) Bind the repeater control to the second resultset B) Build a “pager� of some sort using the values from the first resultset
I have installed the trial enterprise edition of sql server 2005. I am trying to recreate a dts packages. I am using an iseries OLE DB provider. In the data flow the connection to a physical file that has string data gets translated into binary data. Has any one gotten a download to work with the correct data?
When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.
Create and populate table: create table maxtable ( tablename varchar (18) not null, [...] )
Try to insert into test3: insert into test3 (name, tbname) select i.name, o.name from dbo.sysindexes i, sysobjects o, maxtable m where i.indid > 0 and i.indid < 255 and i.id = o.id and i.indid = 1 and o.name = lower(m.tablename)
And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.
The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.
OK so there is some data in an Oracle DB that I need to query and analyze. Unfortunately, the criteria for selecting/grouping the data is stored in a MS Sql Server DB. This cannot be changed. SqlServerGroup Name ID# Item ConditionAAA123 1 a 1AAA123 2 a 1AAA123 3 a 1AAA123 4 a 2AAA123 5 a 2AAA123 1 b 3AAA123 2 b 4AAA123 3 b 3AAA123 4 b 4AAA123 5 b 3BBB123 1 a 1BBB123 2 a 1BBB123 3 a 2BBB123 4 a 2BBB123 5 a 2 OracleGroup Name ID# ValueAAA123 1 50%AAA123 2 55%AAA123 3 60%AAA123 4 80%AAA123 5 70%BBB123 1 35%BBB123 2 45%BBB123 3 50%BBB123 4 50%BBB123 5 80% I need to be able to get this:Group Name Item Condition ValueAAA123 a 1 55%AAA123 a 2 75%AAA123 b 3 60%AAA123 b 4 67.5%BBB123 a 1 40%BBB123 a 2 60% Any idea how I can get the data from these two DBs to talk to each other? Thanks.
can anybody tell me that: 1)How can i retrieve a binary field (image field) that stored in SqlServer2000 with Vb.Net and save it again in my hard disk that i have again that file? 2)can SqlServer itself convert image field to file with its store procedures? 3)can sqlserver run an exe file on local computer from its storeprocedure?
I have a development machine that I have prototyped with access. I don't know how to get the access data into Sql Server on the Internet. Can you help me? I'm not sure where to start. Thank you for any help.
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
Here is a structure of my table create table events( event_id integer primary key identity(1,1), event_name varchar(200), event_date datetime,) Note: The event_id is a automacally and we don't need to insert record in this field. for example i have inserted 10 records in a table then i deleted all records manually from sql server but when again I save the record the event_id field shows started from 11 even the table was empty, although it should be start from 1?
Can i fatch the data with to different sqlserver.i want a query i.e select query fatch the data this different database and diff.table.i.e. sqlserver to sqlserver and oracle to sqlserver
Hello sir,I have installed .net1.1version on windows2003 operating system.I have also installed sqlserver2000 on the same system.My problem is that when i am trying to get data from sqlserver from asp.net program i am getting error as 'access denied to user NT AUTHORITY/NETWORK USER'.I request you to kindly suggest me with an appropriate solution.Thanking you.Please email me on:-aanandkumar786@yahoo.com
Hi, How to expor data from Filemaker to Sqlserver? From the Filemaker, I am exporting data into Tab File(values are separated by tab). How to put data from that file into Sqlserver tables?
I have a report which contains a subreport placed inside a list. when the subreport returns no data. it appears as a blank space in the main report. So i want to suppress the list when the subreport returns no data. Can somebody help me with this?
