I'm curious if there is a way to gather any metadata about a query that was just ran, or about a dynamic query passed in as a parameter, such as being able to "dynamically" return a list of columns in the query, along the lines of:
SELECT * FROM [myTable];
SELECT @@COLUMNS --Expecting this to return a recordset of columns for the previously run query
I'm not talking about querying metadata about the tables themselves, I'm really looking for what "metadata" might be available about the query itself.
This would be useful for me to dynamically generate some code for ad-hoc queries with a stored procedure call, or to simply return a list of just column names only without the data.
I have a heavy database , More than 100 GB only for six month .every Query on it takes me along time and I dont have enough space to add more indexes.by a way I decided to do partitioning. I create a partition function , on date filed and all Data records per month was appointed to a separate file.And is partitioning only for Future data entry?
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET ( 'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES', 'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR, ''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item' COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns) ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order) DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
Greetings, I'm interested in learning how to create queries against a cubes metadata. Specifically I'm interested in writing a aquery, using VB, to return a collection of bottom level members relative to a given member name.
Using the MDX Sample appication I can execute the query:
select Descendants([Customer].[All Customer].[Canada],,leaves) on columns from Sales
to get the members, however, the query appears to attempt to bring back data, thus is probably an expensive method.
It would be great to understand how to implement a meta data query without have to resort to recursion,
Hi Friends, I am trying to write SQL statement for getting information about Cube metadata. When we create any table in MSSQL server, we can trace out its information from system tables in master database by querying tables like sysobjects, syscolumns etc. Like that I have created a cube using MS Analysis services and I can see Metadata (like Creation Date, Processed on etc) on 'Metadata' tab of cube. But I want to access its contents by writting SQL statement. For this I could not locate any system level table where this information gets stored. Could any one help me for writting htis system table level query for accessing metadata info of cube?
INSERT INTO Details SELECT '','7419-01',0 union all SELECT '7419-01','44342-00',1 union all SELECT '7419-01','45342-00',1 union all SELECT '7419-01','46342-00',1 union all SELECT '7419-01','47342-00',1 union all SELECT '7419-01','48342-00',1 union all SELECT '7419-01','49342-00',1 union all
[code]....
From the above table data i want a search query , for example if I search data with "52342-00" I want output to be below format using CTE.
create table [where] ([where] char(5)) go insert into [where] ([where]) values ('where') go select [where] from [where] where [where] = 'where' go drop table [where] go
Installed SQL Server 2014 CU1. While testing sp_send_dbmail I noticed the query results, when attached are cut off or truncated. Max file size has been 64k -65k. I set the max file size to 104857600 and set @query_no_truncate = 1.
As SQL Server 2016 category is not created so I am posting this query in SQL Server 2014 window.
I have enabled Query Store in SQL Server 2016 using ALTER DATABASE and later tried with Properties of Database too. But I am not able to see the Query Store subfolder under database.
We are currently using a Merged replication and Push subscriber to replicate the databse from DB1 to DB2 every hours. The replication process successed for first 20 hours but we found that the replication process cannot be completed after 20 hours with the following error codes:
Action Code Last Action Msg
4 The process could not query row metadata at the 'Subscriber'
363 The process could not deliver insert(s) at the 'Subscriber'.
I can easily query multiple servers using the multi-server query function in Central Management Server and write some of the results to logging tables. I would like to be able to do this via a scheduled job. So far I am finding that even setting up Master/Target Servers this may not work and the only workaround is either using SSIS, SQLCMD (by basically hard coding the servername) and possibly Powershell.
tell me if they have been successful just using standard jobs and querying against multiple servers?
If I can't save the results to a 'central' database/table (I can do this when in SSMS), but can still query against multiple servers I was thinking I could write the results to a CSV file that a SSIS job picks up.
I have attempted using SSIS to iterate through servers and have been plagued with intermittent connection issues when using a For...Loop container.
I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:
- OPTION (RECOMPILE) - Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.
To explain wat I'm doing is this:
1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name. 2. I've created a CHECK CONSTRAINT on the server name column on each server. 3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2. 4. On Server 3 I've created a partioned view that is build up like this:
SELECT * FROM [server1].[database].[dbo].[table] UNION ALL SELECT * FROM [server2].[database].[dbo].[table] UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:
SELECT * FROM [database].[dbo].[partioned_view_name] WHERE [server_name] = 'Server2'
Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.
I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -
SELECT Column_1, Column_2, Column_3, 10*Column_1 AS Column_4, 10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1 First 3 Columns are available within the Original Table_1 The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.
Is there any single TSQL query which provides below info.When did my AlwaysOn Availability group failed over and from which node it failed to which new node(i.e. replica)?
I have a string that contains series of parameters with separators.i need to split the parameters and its values as rows and columns.e.g string = "Param1 =3;param2=4,param4=testval;param6=11;..etc" here the paramerter can be anything and in any number not fixed parameters. Currently am using the below function and getting the parameters by each in select statement as mentioned below.
select [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param1=',';') as param1, [dbo].[rvlf_fn_GetParamValueWithIndex]('Param1=3;param2=4,param4=testval;param6=11;','param2=',';') as param2 CREATE FUNCTION [dbo].[rvlf_fn_GetParamValueWithIndex] ( @CustomProp varchar(max),
Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.
DECLARE @email varchar(500) ,@intFlag INT ,@INTFLAGMAX int ,@TABLE_NAME VARCHAR(100)
i use a single stored procedure to update many tables in sql server 2014 database, using defalut transaction isolation level we got random performance issues.
maybe it would better to use read uncommitted isolation level?
what's happens ,in the both cases( read committed, uncommitted) if the sp is called at the same time passing the same @key parameter?
this is a sample to show of the real stored procedure works:
This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.
I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.
CREATE PROCEDURE procedure1 AS DECLARE cursor_1 CURSOR FOR SELECT 'This is a executable query' FROM table_1 DECLARE @table_2 DECLARE @stoptime DATETIME = NULL;
