Tag: SQL

How do you make structural changes to Azure SQL Server DBs when you don’t have access via SSMS?

We’re no longer allowed to have access to UAT/PROD environments via SSMS at my organization. The way our deployment process works is tied to git pushes. So when you push to the DEV branch, the DEV web code and DB is updated, likewise when pushing to the QA branch and the UAT branch.

The problem is when there is a structural change to the DB, very often the deployment fails with the error data loss may occur. In the past, with on-prem solutions, when we would publish the DB, if we encountered the data loss may occur error we could uncheck Block incremental deployment if data loss might occur in Visual Studio and the deployment would work. We never incurred any data loss either. Since this option is no longer available, it was suggested we use pre and post deployment scripts.

What do I need to put in the pre and post deployment scripts to prevent the data loss error? Our Visual Studio DB Project already contains all the table/view/SP/function definitions.

sql server – Cannot run query to get deadlock graph in a timely fashion

I am trying to get deadlock information from a sql-server by using this query

select XEventData.XEvent.value('(data/value)(1)', 'varchar(max)') as DeadlockGraph
FROM
(select CAST(target_data as xml) as TargetData
from sys.dm_xe_session_targets st
join sys.dm_xe_sessions s on s.address = st.event_session_address
where name = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

The query however takes forever and returns an empty result.

Why does it take that long time and is the deadlock information you get from this views retroactive so I will be able to pinpoint a deadlock that occured some time ago?

Connection string for my SQL Server connection from C on Linux

I am trying to connect to SQL Server using C program on Linux.

My connection string is this:

DRIVER = {ODBC Driver 17 for SQL Server}; Server = 192.168.0.25,52000;Database = db; UID = ud, PWD = pw;

When I want to perform a test connection, I get this error:

[unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login failed for
user ”

Something must be wrong with the connection string because it shows empty user ”, not ‘ud’ as in the string above.

So I tried to connect with:

isql -v test

My odbc.ini file is:

[test]
Driver = ODBC Driver 17 for SQL Server
Server = tcp:192.168.0.25,52000
UID = ud
PWD = pw
Database = db

Unfortunately the results were the same:

[unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login failed for
user ”

So I decided to connect with

isql -v test ud "pw"

And now I finally connected. What is wrong with my connection string/odbc.ini file? I read many tutorials and they seem to be ok. But somehow UID and PWD seems to be ommited.

sql – How to search to see if values of a column exist in another table as sub-string in BQ

I am familiar with the CONTAINS_SUBSTR function in BigQuery.
 Here’s an example from the official BigQuery documentation of how it works (with minor modification):



WITH Recipes AS
 (SELECT 'Blueberry pancakes' as Breakfast, 'Egg salad sandwich' as Lunch, 'Potato dumplings' as Dinner UNION ALL
  SELECT 'Potato pancakes', 'Toasted cheese sandwich', 'Beef stroganoff' UNION ALL
  SELECT 'Ham scramble', 'Steak avocado salad', 'Tomato pasta' UNION ALL
  SELECT 'Avocado toast', 'Tomato soup', 'Blueberry salmon' UNION ALL
  SELECT 'Corned beef hash', 'Lentil potato soup', 'Glazed ham')
SELECT * FROM Recipes;

Recipes:

Breakfast Lunch Dinner
Blueberry pancakes Egg salad sandwich Potato dumplings
Potato pancakes Toasted cheese sandwich Beef stroganoff
Ham scramble Steak avocado salad Tomato pasta
Avocado toast Tomato soup Blueberry salmon
Corned beef hash Lentil potato soup Glazed ham 
SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch), 'potato');

Result:

Breakfast Lunch Dinner
Corned beef hash Lentil potato soup Glazed ham

However, instead of a single STRING literal (potato in the above example), I want to be able to use a table with a single column that contains the keywords that I’m looking for:

Keywords AS
(SELECT 'potato' as ingredient UNION ALL
 SELECT 'cheese' UNION ALL
 SELECT 'egg')

Desired output:

Breakfast Lunch Dinner
Corned beef hash Lentil potato soup Glazed ham
Potato pancakes Toasted cheese sandwich Beef stroganoff
Blueberry pancakes Egg salad sandwich Potato dumplings

Is there a way to use CONTAINS_SUBSTR or any other function and pass the ingredient column of Keywords as input to it to get the desired output?


Example of a query that I’m looking for (this does not work):


SELECT * FROM Recipes WHERE CONTAINS_SUBSTR((Lunch), (SELECT ingredient from Keywords));

mysql – SQL error 1677 cannot be converted from type ‘datetime’ to type ‘varchar(255)’

I got error 1677 on my slave.

Last_Errno: 1677
Last_Error: Column 25 of table 'db.table' cannot be converted from type 'datetime' to type 'varchar(255)'

I’m try to solved this by copy the same db from master to the slave, but after I restart the slave it still showing the same error. I’ve done too walk around to find the solution. one of them are set slave_type_conversions=all_non_lossy but, I didnt sure to take this because it running well before.

running show create table db.tableG on both master and duplicate showing same output without any different.

There is anything I can do with this?

SQL Server 2019: Two Aliases point to same instance

We have a SQL Server 2008 server that I successfully migrated to a new computer with SQL Server 2019.

The SQL Server 2008 server name is 123.123.123.123.

It has two instances named: 123.123.123.123 and 123.123.123.123ABC.

The SQL Server 2019 server name is CMP123456.

The instances are named: CMP123456 and CMP123456ABC.

I would like to use aliases on the 2019 server because unfortunately some programmers hardcoded the instance names into their code.

I created two aliases:

(123.123.123.123      TCP    1433    localhost)    
(123.123.123.123ABC  TCP    1433    localhostABC)

When I use SSMS to login, however both aliases connect me to CMP123456. I am unable to create an alias that will connect me to CMP123456ABC.

These are the aliases I have tried.

They all fail as “server not found” errors:

(123.123.123.123ABC  TCP    1434   localhostABC)   
(123.123.123.123ABC  TCP    1433   CMP123456ABC)    
(123.123.123.123ABC  TCP    1433   ABC)

((No Alias) In this case I was hoping the one localhost alias would work for both instances.)

Any ideas? Is not allowing two aliases a bug/feature of SQL Server 2019?

sql – Consulta a una BD con Python

verán, tengo una GUI en python que se encarga de buscar datos en una BD en sqlite. puedo hacer consultas sencillas pero ahora necesito mandar a llamar a todos los “ID” iguales y asignarles un mismo valor.

Estos son mis valores en la BD:

introducir la descripción de la imagen aquí

En el programa necesito que Por Ejemplo: si en mi interfaz gráfica agrego un “Hola” y selecciono “ID = 1” se imprima “Hola”. en todos los países que tengan el mismo “ID”

Este es un ejemplo de consulta en mi programa donde (Combo1.get()) es un combobox donde elijo el “ID”:

conn = sqlite3.connect('FutDB.db')
cur = conn.cursor()
query = cur.execute("SELECT * FROM Equipos where ID=?",(Combo1.get(),))

Con esa consulta solo se agrega el “Hola” es un solo “ID” y no en todos como quisiera. Todo esto esta dentro de una función mayor y se ejecuta con un botón.

Gracias a todos y espero haberme explicado bien.

Can I make a short-lived readonly snapshot for reporting in Azure SQL DB?

I have an Azure SQL DB (OLTP), that is under considerable load, let’s call it AppDB. AppDB is transactionally consistent, but new rows are written to it constantly. Now I need to populate ReportingDB based on AppDB state every 30 minutes. Reporting population job runs several moderately big queries against AppDB, those queries, unfortunately, can’t be wrapped in a transaction but still have to all run on consistent data. That is, I can’t have situation Query 1 runs=> new rows inserted into AppDB => Query 2 runs. All my queries have to see data the way it was when Query 1 started.

I was hoping I can use Snapshots to create a read-only snapshot for Reporting job to use. From description, creation of such a snapshot should be fast and subsequent “copy on write” performance hit should be manageable. Snapshots lifetime would be under 10 minutes on average.

But now it looks like Azure SQL does not support CREATE DATABASE ... AS SNAPSHOT OF ..., it only supports CREATE DATABASE ... AS COPY OF ..., which I expect to be a lot slower (probably not meant to be used for reporting snapshot).

What would be a good way to create quick and short-lived read-only snapshots for reporting in Azure SQL DB?

P.S. We have considered replication, but it is not an option for us at the moment due to policy limitations.

Estoy tratando de instalar Sql server 2012, pero me sale un error: Windows Management Instrumentation (WMI)

He estado instalando sql server y me he encontrado con este error, intente ver si tengo el servicio WMI, peero me encuentro de que creo que no esta instalado [![introducir la descripción de la imagen aquí][1]][1]



  [1]: https://i.stack.imgur.com/iYQl0.png

php – SQLSTATE[HY000] [1049] Unknown database ‘laravel_instagram_master’ (SQL: select * from `images`)

Estoy con php y laravel y quiero traer datos de la base y mostrarlos por pantalla y me tira SQLSTATE(HY000) (1049) Unknown database 'laravel_instagram_master' (SQL: select * from images) ..lo unico que me hace ruido es que yo tuve que cambiar el puerto 3306 al 3307 en mysql(xampp) Y ADEMAS cuando voy a localhost/phpmyadmin debo ingresar password(la pass que me pide es la que puse en workbench hace un tiempo cuando tuve que trabajar con java para la facultad), desde mi ignorancia creo que tengo un temita con la configuracion pero no encuentro como solucionarlo!

use AppImage;

Route::get("https://es.stackoverflow.com/", function () {
    $images = Image::all();
    foreach($images as $image){
        echo $image->image_path."<br/>";
        echo $image->description."<br/>";
        echo $image->user->name.''.$image->user->surname."<br/>";
        
        if(count($image->comments)>=1){
            echo '<h4>COMENTARIOS</h4>';
            foreach($image->comments as $comment){
                echo $comment->user->name.'  '.$comment->user->surname.':';
                echo $comment->content.'<br>';
            }
        }
        echo 'Me gusta: '.count($image->likes);
        echo "<hr/>";
    }

    return view('welcome');
});

y me devuelve este error

IlluminateDatabaseQueryException
SQLSTATE(HY000) (1049) Unknown database 'laravel_instagram_master' (SQL: select * from `images`)

.env

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3307
DB_DATABASE=laravel_instagram_master
DB_USERNAME=root
DB_PASSWORD=