We help IT Professionals succeed at work.

Databases

58K

Solutions

40K

Contributors

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.

I need some help for the SQL Query. I have a loan table (1st lien and 2nd lien). If the loan program is 'Bond' for the 1st lien then I need to change 2nd lien loan program to 'Bond'.

Example:

Guid        LinkGuid     LoanNumber     LienType         ProgramCode
{f1244}  {2077a}       123456789      First Lien         Bond
{2077a} {f1244}        567345673      Second Lien    C30

Result
Guid        LinkGuid     LoanNumber     LienType         ProgramCode
{f1244}  {2077a}       123456789      First Lien         Bond
{2077a} {f1244}        567345673      Second Lien    Bond <-- should change to 'Bond'
0
We have tables out in the field and we have to drop any indexes that we are not aware of.  How do you drop an index from a table without knowing the name.

Thanks
John
0
Deleted a database in sql 2016 a week ago, and now logs are showing login failed for user xxxx\xxxx (domain account replaced with x's), cannot open database.  I have scrubbed everything i can think of for hours and can't find the culprit.

SqlServer Logs:
04/02/2020 09:46:15,Logon,Unknown,Login failed for user 'xxxx\xxxxxxx'. Reason: Failed to open the explicitly specified database 'SWPDM_CortecPDM'. [CLIENT: <local machine>]
04/02/2020 09:46:00,Logon,Unknown,Login failed for user 'xxxx\xxxxxxx'. Reason: Failed to open the explicitly specified database 'SWPDM_CortecPDM'. [CLIENT: <local machine>]
04/02/2020 09:41:00,Logon,Unknown,Login failed for user 'xxxx\xxxxxxx'. Reason: Failed to open the explicitly specified database 'SWPDM_CortecPDM'. [CLIENT: <local machine>]
04/02/2020 09:40:45,Logon,Unknown,Login failed for user 'xxxx\xxxxxxx'. Reason: Failed to open the explicitly specified database 'SWPDM_CortecPDM'. [CLIENT: <local machine>]
04/02/2020 09:35:45,Logon,Unknown,Login failed for user 'xxxx\xxxxxxx'. Reason: Failed to open the explicitly specified database 'SWPDM_CortecPDM'. [CLIENT: <local machine>]


SqlServer Agent Logs:
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 4060<c/> Cannot open database "SWPDM_CortecPDM" requested by the login. The login failed. [SQLSTATE 42000]
04/02/2020 09:46:15,,Error,[298] SQLServer Error: 18456<c/> Login failed for user 'xxxx\xxxxxxxx'. [SQLSTATE 28000]
04/02/2020 09:46:00,,Error,[298] SQLServer Error: 4060<c/> …
0
I have a coldfusion form that allows sending an entered text message and a pdf attachment to email addresses in a database using the cfmail tag. The database holds approx 700 addresses. I want to be able to send to no more than 100 addresses per hour once the form is submitted. The email script would insure that each address receives the message only once and if needed can add a date sent to insure that it is only sent once per month to the same address. I think I should be using cfschedule but just not sure how.
0
perHello All;

I have just run into a situation where I cannot find any information on it.
Installation of SQL Server 2019 WITHOUT installing Data Quality Services
If I uncheck it, it unchecks EVERYTHING.
So, the question is...
How do you install the SQL Server Database Engine, without installing the Data Quality Services

The reason:
I am installing the "SQL Server Failover Cluster" on the 2016 Desk Experience Server.
After this, I will be installing "Add node to a SQL Server failover cluster" on the 2016 CORE Server.

From my experience, adding the NODE fails every time I have tried to install it on the core.
A person posted but without proof that the Data Quality Services does not run on Server Core.
(This is my thread about the issue)

I am building my SQL Servers Clusters from scratch, and want to get the Core servers running SQL Server database engine.
And whatever I install on the Main SQL Cluster, will filter down into the NODE installations.
So I need to be cautious about what gets installed to make sure everything is going to go smoothly on this build.
These are the typical features that get installed on the NODE's
SQLEngine, SQLEngine\Replication, SQLEngine\FullText, SQLEngine\DQ
So please, if anyone has any information on this TA or the other TA, please let me know.

Thanks.
Wayne
0
Hi,

I am migrating from ms/access to filemaker 18. So, first, I am exporting the base tables to ms/excel and then importing the excel to filemaker.
As I am cleaning up and streamlining the database I first designed the new tables on Filemaker and  then started the import mechanism. But I am a real rookie on Filemaker and it looks that I am not able to complete the import.

The source Table is "tblMMR_base" and the target table is "milBase" (I want only some of the fields). But on the target, the milBase is greyed out and if I select a new Table to ba named the same as the spreadsheet, it will not accept the 1st record as field names, and will import everything

Screenshot-2020-03-28-at-17.54.13.png
can anyone help?
0
Here's the ticket I'm working on:

When the Prospects service queries for contacts, if values are undefined, the defaults are hardcoded and caught later on.

I.e in the backend:
phone number -> 000-000-0000
name -> . //Not kidding, it's literally a period
email -> noemail@email.com

Then in the front end, it checks for each of these hardcoded values before converting them to booleans. This seems very loose and confusing.

Basically, what's happening is if a user adds a contact to the database and they enter very little info (no email, no name or no phone number), the database will interpret those NULL values, in whatever combination they may be, and replace those NULLs with either a period, or what you see above.

The team member who's identified this anomaly is an exceptional programmer and I'm not cynical at all. My question: What would be the better approach?

I've got three fields available to me. The only required field is the, "name" - although I'm thinking that could also be left blank in light of there being the "period" accommodation mentioned above.

I've asked about making all three fields required and that was nixed.

So, what then, is the better approach to handling NULL values in this scenario?

Right now, rather than inserting a NULL value, things like "noemail@email.com" are being inserted instead. And then the system looks for "noemail@email.com" to determine whether or not there's a legitimate email address in the database.

It's a …
0
hi table not showing in h2 database
tabletable32
0
How do I add a "sign-up" form for my website? I would want a form for the user to fill-in, then the information goes to a database, I suppose. THEN a Thank You notice. I believe this has to be done in javascript.
0
Just before I pull all of my hair out for no reason How do you update or convert an access 32 bit database to a 64 bit. In access 16 I cannot even import forms fields etc from 32 to 64 bit

I must be missing something again
0
Hi Experts,

I am trying to create a view and getting datatype mismatch

V_Screening_View
SELECT CStr("-" & Mid([Caregivers_CaregiverCode],InStr([Caregivers_CaregiverCode],"-")+1)) AS CG_Code, InStr([Caregivers_CaregiverCode],"-") AS 1, V_Screening_Covid.*
FROM V_Screening_Covid
WHERE (((InStr([Caregivers_CaregiverCode],"-"))<>0));

Open in new window

VisitReport_View
SELECT "-" & CStr(Replace(Mid([Caregiver],InStr([caregiver],"(")+1),")","")) AS CG_Code, [Visit Report].*
FROM [Visit Report]
WHERE ((([Visit Report].Caregiver) Is Not Null));

Open in new window


Now this one is giving me the error

SELECT VisitReport_View.*
FROM V_Screening_View RIGHT JOIN VisitReport_View ON V_Screening_View.CG_Code = VisitReport_View.CG_Code
WHERE (((V_Screening_View.CG_Code) Is Null));

Open in new window


Any idea?

Thanks
0
Hello Experts,

I am experiencing one issue on setting IDLE_TIME  under profile doesn't work , system still shows inactive sessions on Oracle database.

As per my understanding the setting should expire the sessions that are inactive and cross the setting time. in my case I have 180 as a value .

Any suggestions on any additional steps that I am missing ?
0
I need to implement or add a sleep sub routine in Ms Access between the commwrite & commread so that I'm sure that all the data received from the gadget is read and received into my database. Now below is my draft sub routine see how to improve it. The gadget writes all the invoices without problems see attached screen, the only issue here is receiving that why the suggestion for a sleep sub:

Dim strData  as string
Dim strDataComplete as string

        strDataComplete = ""
        Do While True ' Loop until serial port is read
            Sleep 1000
            lngStatus = CommRead(intPortID, strData, 100000) 'For 100000 is the max data length you want read, change to suite
            Sleep 1000
                            
            If lngStatus = 0 Then
               'Keep polling to see if serial has data
            ElseIf lngStatus <> 0 Then
                strDataComplete =  strData 'Put data pulled from serial port here 
            End If
                    
            If lngStatus = 0 And strDataComplete <> "" Then
                Exit Do    ' Exit loop when all data is read
            End If
            
        Loop 

Open in new window



parsing-Json.png
0
Hi Expert,
I am having a big problem, The SolarWindsOrion database went to suspect mode after windows update. And we have tried all option to recover the database. No luck.

Tried repair option and it failed. Tried last full backup restore and the got the below error even resource governor is disabled.

Msg 41379, Level 16, State 0, Line 2
Restore operation failed for database 'SolarWindsOrion' due to insufficient memory in the resource pool 'default'. Close other applications to increase the available memory, ensure that both SQL Server memory configuration and resource pool memory limits are correct and try again. See 'http://go.microsoft.com/fwlink/?LinkID=507574' for more information.
Msg 1813, Level 16, State 2, Line 3
Could not open new database 'SolarWindsOrion'. CREATE DATABASE is aborted.

Any idea?
0
Hi Experts,

I am working with an old MS SQL database where the date is stored as Varchar, but I keep getting the following error

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Open in new window


You will see that I am trying two formats, both unsuccessfull
SELECT SUM(family) AS totFam, SUM(adults) AS totAdult, SUM(Seniors) AS totSen, SUM(students) AS totStud, 
SUM(children) AS totChild, SUM(infants) AS totInf 
FROM Invoices 

WHERE CAST( CASE WHEN responseCode IS NULL OR ISNUMERIC(ResponseCode) = 0 THEN '99' else responsecode end AS INT) < 50 
AND (Company = 'CSST' OR Company = 'COMBO') 

/*AND CAST(paymentDate AS DATE) >= CAST('12/5/2019' AS DATE) AND CAST(paymentDate AS DATE) <= CAST('3/24/2020' AS DATE)*/
AND CAST(paymentDate AS DATE) >= CAST('2019-12-05' AS DATE) AND CAST(paymentDate AS DATE) <= CAST('2020-03-24' AS DATE)

Open in new window


When I do a simple SELECT ..... ,CAST(paymentDate AS DATE) AS dt I get YYYY-MM-DD

Thank you,
0
How to search SQL database for all tables containing a specific column name and certain database properties of that column?
Example I need to find all the tables in a database that column(s) with the word Category in them and what the properties of that column are (the Length and if ANSI Padding Status is set to True or False)
0
I need to compare two databases and find the tables that have different schema.

I don't need to compare the data. Company doesn't have Redgate. I Googled for a script and didn't find one.

Anyone has a script to find which tables have different schema?

I need to compare the stored procs too if there's a script for that.

If there's no script, I'll look into SQL Adpet (someone on the team mentioned it)
0
hi am geting the following error
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 805
ORA-06512: at "Iusername.LOAD_IMAGES", line 17
ORA-06512: at line 1

am using this script
create or replace procedure load_images
(
   p_id         in   number,
   p_file_name      in   varchar2
)
is
   v_bfile          bfile := bfilename( 'IMAGES' , p_file_name);
   v_blob          blob;
   v_src_offset  number := 1;
   v_dst_offset  number := 1;
begin

     insert into noc_image_liabrary (id, image_name, image_file)
        values (p_id, p_file_name, empty_blob())
        return images into v_blob;

   dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);
   dbms_lob.loadblobfromfile(v_blob, v_bfile, dbms_lob.getlength(v_bfile),v_src_offset,v_dst_offset);
   dbms_lob.close(v_bfile);
   commit;
end;
/

Open in new window

0
Does anyone know what happens when you re-enable SMBLeasing. It was a fix to stop back end databases going into an inconsistent state when used with Windows 10 and office 365 but apparently it can cause network speed issues. I have a client that can only use one front end out of three. One works perfect but reduces the access speed down to intolerable levels on the other two. I have applied every other fix I can see but problem persists. I just wondered if the fix below would still need the SMBLeasing turned off?

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters]
"FileInfoCacheLifetime"=dword:00000000
"FileNotFoundCacheLifetime"=dword:00000000
"DirectoryCacheLifetime"=dword:00000000
0
I have this Grid in my c# application

The Display Value in column 1  has an ID and Description  ( see  screen below)

Selected Record From Combo Box withing Grid

When i change values in column1 I want the description written into column2

I am trying to use LINQ  and a LAMDA Expression  . My script is as shown below

[private void dgvDetailsTable_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 1)
            {

                switch (this.dgvDetailsTable.Columns[e.ColumnIndex].Name)
                {
                    case "dgvcmbarsaleditemid":
                        var result = ((List<InventoryItemModel>)dgvcmbarsaleditemid.DataSource).Find(x => x.INVITEMId.Equals(dgvcmbarsaleditemid.Selected));
                        this.dgvDetailsTable.CurrentRow.Cells[2].Value = result.INVITEMDescription;
                       

                        break;


                }
            }
        }[/code]

My database  field name is INVITEMId   and my   DataGrid  column 1 name is   dgvcmbarsaleditemid.


In the debugger  I can see that the 1188 records  were selected  and in the debugger i can see the loop search going on

See  Debugger  Screen below

Debugger Screen showing that I have ALL the 1188 records in my List

At the end of the looping it can not find the selected record and put it in the result variable even though the selected record
is on screen and coming from the database.  See  Error screen below

Error Screen with result  variable having null
0
Access to SQL Database to run queries.

I would like to know how to give permissions to a specific user to access specific database and be  able to run all queries.
I also would like to know which tool the user will use to run the queries on.

Thank you
0
WordPress Query is very slow.

I inherited a site with one piece of custom code.  It basically grabs products from the Database and orders them and displays them through a shortcode.  The issue is, when I turn off the code the page loads very fast but with the code working it takes about 3 seconds to return the query to return.  I tried to switch over to the proprietary way that woo would grab products but it is still really slow.  Can anyone look at the following code and suggest how to speed it up?  I even thought of forcing the code to load last?

NOTE: if I change this to "Not Empty" it does not do what it is designed to do but it speeds up:
if ( empty($prodData) ) {


<?php
$shortcodeId = 'swift-cross-sell item-' . random_int(9999, 99999999);
$skus = explode(",", $atts['skus']);


//if( !is_admin() ){
  $cacheKey = 'key_products_' . implode('_', $skus);

 
$loopData = WC()->session->get( $cacheKey );


  if ( empty($loopData) ) {
    $args = [
      'post_type'  => ['product', 'product_variation'],
      'meta_query' => array(
        [
          'key'   => '_sku',
          'value' => $skus,
          'compare' => 'IN',
        ],
      )
    ];

    if ( ! empty( $atts['tag'] ) ) {
      $args['product_cat'] = $atts['tag'];
    }
    $loopData = new WP_Query( $args );
    WC()->session->set( $cacheKey , $loopData );
    
  }
//}
  


$loop = [];
while ( $loopData->have_posts() ) {
  $loopData->the_post();
  $productId = get_the_ID();
  

  //if( 

Open in new window

0
I have a SQL 2016 database that is set to a Compatibility level of 2014.  I have detached the database and am trying to mount it to a server running SQL Server 2014.

When I mount it I get the error message:

The database ‘MyDatabase’ cannot be opened because it is version 852.  This server supports version 782 and earlier.  A downgrade path is not support.  Could not open new database ‘MyDatabase’.  CREATE DATABASE is aborted.  (Microsoft SQL Server, Error: 948).

When creating the database, I purposely set the Compatibility level to be 2014 with anticipation of having to move it.  I had also tried using a SQL backup with no success.

Are there any suggestions on how I can mount my database?

Thanks!
0
I have a Scheduled Task that runs every 4 hours. This Scheduled Task does some Reporting automation in that it opens up an Excel doc, updates some cells from an Access database and ultimately pushes those changes to some Pivot Tables.

Over the weekend, four instances of Excel were running, likely from the process taking longer than expected. This caused a Memory issue, with the "Resource Exhaustion Detector" showing in the Event Viewer.

This process has ran flawlessly for years (e.g. without multiple instances of Excel showing in the Task Manager).

Is there a way I can programmatically limit the number of processes of Excel that are running? Meaning, is there a directive (or "switch") I can put in the Scheduled Task so that it stops after 3.5 hours? I'm open to suggestions to fix this one.

Thanks!
0
I was working on an Access form in design mode when Access locked up.  I has to exit Access and when I attempted to reopen the database I received an error message, see attached screenshot.Error Screen SHot
Does anyone know how I can open my form, unfortunately I had made several changes since mt last save,
0

Databases

58K

Solutions

40K

Contributors

Databases are organized collections of data, most commonly accessed through management systems including schemas, tables, queries and processes that allow users to enter and manipulate the information or utilize it in other fashions, such as with web applications or for reporting purposes.