I've a dataset which is filled using a stored procedure in either MSSQL2005 or 2000.
The procedure may return sql-errors (f.ex 208 if a table is not found), but the procedure will anyway return data which shall populate the dataset, independent on 208 errors.
The procedure works fine if running it from f.ex. SqlMgtStudio, and the dataset is filled if the procedure triggers no errors.
However, if the procedure triggers an error like 208, then I get an exception in the application and the dataset is not populated with the data returned by the procedure.
Is there a way of telling the DataSet/SqlDataAdapter to fill the dataset even if the sql-code genererates som errors?
Hi the following code generates an error while executing dbServer.Databases("InvModel").Transfer dbTransfer error description [SQL-DMO]Invalid parameter type; must be string or numeric ordinal, or an object; check documentation for this property or method. I am trying with sql2005 express edition. the code was one which we used for sql 2000
Code Block Dim dbTransfer As New SQLDMO.Transfer2 Dim dbServer As New SQLDMO.SQLServer2 dbServer.Connect gStrSQLServerName, "sa", "NEWTECH001" With dbTransfer .DestDatabase = Trim(txtCompCode) .DestLogin = "sa" .DestPassword = "NEWTECH001" .DestServer = gStrSQLServerName .DropDestObjectsFirst = True .CopyAllObjects = True .CopyAllTables = True .CopyAllViews = True .CopyData = SQLDMOCopyData_Replace .IncludeDependencies = False End With Screen.MousePointer = vbHourglass prgbar.Value = 35 dbServer.Databases("InvModel").Shrink 1, SQLDMOShrink_Default DoEvents Screen.MousePointer = vbHourglass prgbar.Value = 50 DoEvents dbServer.Databases("InvModel").Transfer dbTransfer DoEvents Screen.MousePointer = vbHourglass
Hello All ...I am attempting to create a program that will run on the PocketPC 2003 environment. I have upgraded Visual Studio to SP1, I have installed SQL Server Compact Edition on my development machine and I have installed SQL Server Compact Edition Tools For Visual Studio on the development machine.I have created a new project Visual Basic - Smart Device - Pocket PC 2003. I have created a form for user input. I build and deploy the form to the Symbol Pocket PC to test - no connection to data and it works.I then add a reference to System.Data.SqlServerCE.dll and rebuild and redeploy the application to the handheld. When I attempt to open the form I receive the following error:psmPocket.exeNotSupportedExceptionSystem.Drawing.Bitmapat System.Resources.ResourceReader.LoadObjectV2() at System.Resources.ResourceReader.LoadObject() at System.Resources.RuntimeResourceSet.GetObject() at System.Resources.ResourceManager.GetObject() at System.Resources.ResourceManager.GetObject() at pmsPocket.frmSetSOPType.InitializeComponent() at psmPocket.frmSetSOPType..ctor() at System.Reflection.RuntimeContructorInfo.InternalInvoke() at System.Reflection.RuntimeContructorInfo.InternalInvoke() at System.Reflection.ContrcutorInfo.Invoke() at System.Activator.CreateInstance() at MyForms.Create__Instance__() at MyForms.get_frmSetSOPType() at psmPocket.SetSOPType.Main()Now the confusing part is that I haven't changed any of the forms or the code behind the forms, I have simply added the reference to the project.Any idea why adding the reference to System.Data.SqlServerCE.dll would cause the system to start generating these errors?I've checked, the install process has loaded the .NET 2 framework to the handheld. And as indicated at the beginning of this message, the application showed the form prior to my adding the reference.Thoughts?Thanks ...
I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.
SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME) ,d.fmc_prodlnk, d. fmc_cost INTO #TestTable FROM ( SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL
It's OS / SQL related. I backup to a UNC device, and frequently get a network related error - but not always. And the error is also OS related...
The scene: 64 bit Windows Server 2008 hosts 64 bit SQL 2008 Standard, and devices are created to point to 64 Server 2008 Server B (ServerBSQL_backupsdvc_DB.bak). The database is backed up daily, on a job, and when run the following error is returned:
Date2009/08/08 06:43:30 AM LogJob History (Backup_AssetData_2009-08-08)
Step ID3 ServerSQL01 Job NameBackup_AssetData_2009-08-08 Step NameBackup Database - Second Attempt Duration00:05:33 Sql Severity16 Sql Message ID3013 Operator Emailed Operator Net sent Operator Paged Retries Attempted0
Message Executed as user: COMPANYSERVICE_USER. Processed 94096 pages for database 'AssetData', file 'AssetData' on file 1. [SQLSTATE 01000] (Message 4035) Processed 1 pages for database 'AssetData', file 'AssetData_log' on file 1. [SQLSTATE 01000] (Message 4035) The operating system returned the error '64(failed to retrieve text for this error. Reason: 15105)' while attempting 'FlushFileBuffers' on 'dvc_AssetData( iscsrv-dcm04RISCSRV-SQL01_Backupsdvc_AssetData.bak)'. [SQLSTATE 42000] (Error 3634) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
The file is generated, the same size as a successful backup, but the job terminates unsuccessful.I have tried to delete the devices' file, and then the backup usually succeeds. This may point to a permissions' type error, but the user the job is run under is a domain admin. The destination server is not unavailable during this time, and the network shares also remain active throughout the excersize - although I haven't got a way to "prove" this.
I have a few databases that backup to the UNC described above (different files) that don't fail..My SQL2000 server backs up it's databases to the SQL2008 server, and those backups don't fail with this error.
My research has led me to understand that the 64bit OS tries to buffer the files it receives from another server, and I was wondering if this could be influencing the backups as the destination server is also 64 bit Windows Server 2008.
##20090812## UPDATE
I tried another way to do the backup; with the same results:
Backup database Infovest to disk = ' iscsrv-dcm04 iscsrv-sql01_backupsdvc_Infovest_Z.bak' with INIT,STATS=1
When I click on the DataSet Query Desginer there is an Error Source:.NetSqlClient Data Provider. why this error comes and I have executed the SQL query in SSMS succesfully and the result comes. However this is causing the report from the report manager to fail.
Here's a really annoying problem. Let's say you have a text file with 2 million rows.Delimiters all look good and rows are previewed well but the file has a missing row at say lin 1234567 - way deep in the file. When SSIS encounters the blank row, an error is raised and processing on the file STOPS! I verified this in by checking the SSIS log and have even developed an error routine to notify me via email when the error occurs (really cool if I do say so myself ). The main problem still remains - how to resume processing from the point of failure in the file? Any help is appreciated. Thanks.
Hi Guys, we have a database where we have backup and restore done daily basis by a automated task. We are having errors on MSDB the saying database is reached the capacity(as SYSBACKUPHISRY getting populated every day). After I increase the size of MSDB,the error msg disappeared.
Does anyone know rather than increasing the size, Can we somehow store the data upto somany records and then when it reach the max, to rewrite the SYSBACKUPHISRY table all over again?
Is it advisable to truncate the sysbackup history table?
I would really like some one to give an idea as soon as posible. I appreciate it really very much. Thanks, Jay.
I want to be able to return the last date, e.g.(select Max(DateColumn) From Table)However if any of the DateColumn fields are null I want to return anull, is there a simple way to do this?
Upon entering data into the database, my fields fill the entire length specified. For Example a name field with char type and allocated for 50 spaces when entered with SAM returns SAM and 47 spaces. When I have to modify the record, I need to first delete the 47 spaces. Any ideas as to why this is happening or what I can do to alleviate the problem? This happens with all types and lengths. Thanks.
select * from posts where id = @id and author =@author and message like @keyword
in my case a user can choose to search by one or more of this elements.
what will be the best approach to write a statement that will account for the posibility of the user leaving some "parameters" empty, while also allowing for the posibility that all parameters could be filled
one approach I have thought out is this
if (@id IS NOT null) select * from posts where id = @id
if (@author IS NOT null) select * from posts where author= @author
if (@keyword IS NOT null) select * from posts where keyword = (@keyword
but this does not really take care of the posibility that all of them or some of them will be null while others will not
Hi, I'm trying to write a restore script. In the past I've always used the SSMS GUI to restore. When running the script below, the process got stuck at around 90%. Eventually I aborted the restore and found out that my C drive was full. The log file of the database I was restoring was 32GB. Can anyone tell me what I'm doing wrong? Thanks.
Code Snippet ALTER DATABASE [oms] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
RESTORE DATABASE [oms] FROM DISK = N'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupOMS_Blank_Production.bak' WITH FILE = 1, REPLACE, STATS = 10 GO
CREATE TABLE #TEMP ( TYPE VARCHAR(10), SEQ INT, SUB_TYPE VARCHAR(10))
[Code] ....
Now for each type the seq is very important. Effectively by order of seq the subtype stays the same until another subtype changes it. So for TYPE1 100,110 and 150 are A. 170, 200,220 are B. 230 and 250 are C and so on.
However as you can see the data isnt actually stored in the row. I need a select statement that shows this data.
I have done this:
SELECT t1.*,t3.SUB_TYPE FROM #TEMP t1 CROSS APPLY (SELECT MAX(SEQ) SEQ FROM #TEMP AS t2 WHERE t1.SEQ >= t2.seq AND t2.SUB_TYPE <>'' AND t1.TYPE = t2.TYPE GROUP BY t2.TYPE) t2 INNER JOIN #TEMP t3 ON t3.TYPE = t1.TYPE AND t2.SEQ = t3.SEQ
And it seems to work. Is this the easiest way to do it or am i missing something?
I've got a large table (3mil records) with a number of columns, but currently no way to refer to any individual column. I therefore need a primary key, but does anyone know of a SQL statement I can use that will create a column (say, ID) that is automatically filled with an incrementing 'counter'? Or, instead, how can I set unique incremental values after first creating the column?
I want to use SSB as a mean to thread multiple SP invocation of the same procedure.
When I launched the initator processes it created in the transmission queue error messages saying for each of them that it is an invalid target service name.
The view (sys.transmission_queue) records them in this way in the status column:
"The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied."
Services and queues and activation procedure are all defined into the same database.
I drop services and queues, and i tried to create a service with the initial wrong name. Nothing happened.
I have a report that I would like to run in the evening due to high database usage that required a user to fill in selected parameters. Is there anyway to allow a user to fill in the report parameters and have it scheduled to run at 3:00 AM.
I have a report in wich I show a list of countries. The length of this list differs each month and comes from a SSAS datasource. I want to show this list on my report in a matrix like this:
currently I am facing a complex escenario related with gaps and sequences, but I was trying with diferent cases but I did not get the correct results, I am sure about the use of windows functions. I have a table with the information grouped by PublicationId, Provider, MetricId and Amount by Date, one row by each month, but in some cases these data don't have a sequencial values, for example I have the data for the next sequence:
I need to get the sequence by each month, in this case I need to project the month from February to May (with the last previous value, for this case of January) , this is:
The data for testing are:
DECLARE @PublicationsByUser AS TABLE ( Id INT, PublicationId INT, MetricId INT, ProviderId INT, DateCreated DATE, Amount FLOAT
I create a Query in Dataset that get values from multiple tables when i execute query in query builder its execute successfuly but whan i am save this query found following message. "The new command text returns data with schema different from the schema of the main query. Check your query's command text if this is not desired" after OK i am check my query result in Preveiwdata its show deffrent result.
Im looking for a query that returns record 3. Ruturning just the records that have a record in which column 2 is a 10 but not a 20. If I enter WHERE = 10 I get 1,3,4. Please help. Thanks!!
I've faced the following problem while forming DataSet. I'd like to ask the following SQL query: ___ select Users.UserID,Users.FirstName,Users.LastName, Users.Email, data.Suffix, data.Unit,data.Telephone, data.IM, data.Websitefrom(select UserID, max(case PropertyName when 'Suffix' then PropertyValue end)Suffix, max(case PropertyName when 'Unit' then PropertyValue end)Unit, max(case PropertyName when 'Telephone' then PropertyValue end)Telephone, max(case PropertyName when 'IM' then PropertyValue end)IM, max(case PropertyName when 'Website' then PropertyValue end)Website from (SELECT UserProfile.UserID, UserProfile.PropertyValue, UserProfile.PropertyDefinitionID, ProfilePropertyDefinition.PropertyName FROM UserProfile,ProfilePropertyDefinition WHERE UserProfile.PropertyDefinitionID=ProfilePropertyDefinition.PropertyDefinitionID )table2 group by UserID)datainner join Users on Users.UserID = data.UserIDWHERE data.Suffix = 'IT'; __ Nothing happens while fulfilling as well (i.e. if to set up Breakpoint and watch the DataSet condition - it will be empty there). If to change the request to let's say some standart - then DataSet downloading begins - it seems there's something wrong with the request I'm checking....If to look with the holp of request master and press "perform", data is reflected in a normal way...But as soon as i press "Bild", emptiness is shown as usual Please, prompt me what to do Initial code in the file .aspx.cs: ------------------------------ UserInfo ds = new UserInfo();UserInfoTableAdapters.UserInformationTableAdapter da = new UserInfoTableAdapters.UserInformationTableAdapter(); da.Fill(ds.UserInformation); Info.DataSource = ds.UserInformation; Info.DataBind();
I popped into the Transact-SQL forum to get some help and created the dbo.selectaudit1 table, which can be queried succesfully.
Code Blockcreate table dbo.selectaudit1 (startkey varchar(10) not null, endvalue varchar(10) null, endkey as coalesce(endvalue, startkey))
insert into dbo.selectaudit1 (startkey) select '042' union all select '140' union all select '2089' union all select '2031' union all select '2051' union all select '2100' union all select '2299' union all select '2300' union all select '2349' union all select '2350' union all select '2389' union all select '2390' union all select '2399' union all select '2732' union all select '2733' union all select '2849' union all select '2850' union all select '2883' union all select '2898' union all select 'V073' union all select 'V078' union all select 'V100' union all select 'V109' union all select 'V580' union all select 'V581' union all select 'V661' union all select 'V662' union all select 'V671' union all select 'V672' union all select 'V711' union all select 'V760' union all select 'V769' insert into dbo.selectaudit1 (startkey, endvalue) select '1400' ,'2089' union all select '2100', '2299' union all select '2300' ,'2349' union all select '2350' ,'2389' union all select '2390' ,'2399' union all select 'V100' ,'V109' union all select 'V760' ,'V769'
Then in reporting services, I created a report with the following query in my dataset. This query works fine in a BIDS query windows, however, when I refresh my dataset and attempt to execute the query I am getting the following error:
Cannot find the object "selectaudit1" because it does not exist or you do not have permissions. (Microsoft SQL Server, Error: 1088)
Code Block Select p.MRN , p.PatientName , CONVERT(CHAR(20),p.AdmitDate,101) AS AdmitDate , CONVERT(CHAR(20),p.DischDate,101) AS DischDate , p.VisitTypeCode , d.VisitTypeName , p.AnyDx , p.PrinDxCode , p.PrinDxDesc , p.SecDx1Code , p.SecDx2Code , p.SecDx3Code , p.SecDx4Code , p.SecDx5Code , p.SecDx6Code , p.SecDx7Code , p.SecDx8Code , p.SecDx9Code , p.SecDx10Code , p.SecDx11Code , p.SecDx12Code , p.SecDx13Code , p.SecDx14Code , p.SecDx15Code From dbo.PtMstr p inner join ampfm.dct_VisitType d on d.VisitTypeCode=p.VisitTypeCode INNER JOIN dbo.selectaudit1 sel ON (PrinDxCode between sel.startkey and sel.endkey AND DischDate between '2006-11-01' and '2007-09-30') OR (SecDx1Code between sel.startkey and sel.endkey AND DischDate between '2006-11-01' and '2007-09-30')
Hello, I have two datasets. One dataset has some delivery informations the other dataset has billing informations. The first dataset builds the resultset out of the selected parameters (those parameters do not correspond to the values in table two). What I wanna do is pass some informations (like custno, artno) to the other dataset so that I can only display the corresponding information. Any ideas?
I am trying to build a report with a prompt to select building. It all works fine except I in the prompt building is list as may times as there is data for it. I want it to only display once, but not effect the other data is there a way I can query this?
I have my 2 datasets and my parameter set up to display building to select from. My issue is I get multiple values in the Select Value Prompt. It will pull the correct data I just want to clean up the Select Value Prompt.
I need to write a query in which some of the columns will be derived from a dataset (join) and the other columns will be derived from another dataset. Is there a way to do this?
I have a problem using more than one dataset to create my report. I wish to use data from one stored procedure to create the values for a combobox, that I use as input to another stored procedure.
When I create more than one dataset and set up my report parameters to use one of them to create comboboxes, I get the following error, when I want to see the preview:
An error occured during local report processing.
The definition of the report '/StockInTrade' is invalid.
The data set name is missing in the data region 'DataSetName'
------------------------------
The report worked just fine when I used hardcodede values for my combobox. The error occurs as soon as I define more than one dataset (meaning it fails even before I try to use the second dataset as report parameter values).
I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value. This is the code for the Method to return a datareader
public SqlDataReader GetOrgID() { Singleton s1 = Singleton.Instance(); Guid uuid; uuid = new Guid(s1.User_id); SqlConnection con = new SqlConnection(conString); string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID"; SqlCommand cmd = new SqlCommand(selectString, con); cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid;
SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd;
adapter.Fill(dataset); return dataset;
}
Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well. So assume that the Guid is a valid entry..I should return a valid dataset but its null.
