The objects "MYTEST2.TEST" and "mytest.TEST" in the FROM clause have the same exposed names. Use correlation names to distinguish them.use of fully qualified names is allowed without having to alias them.One workaround is changing compatibility mode to 80.Is there another (startup flag?).Reason for no alias: MS Reportbuilder doesn't provide them when building queries
CREATE SCHEMA MYTEST;
go
CREATE SCHEMA MYTEST2;
go
CREATE TABLE MYTEST.TEST
Hi all!In a insert-trigger I have two joins on the table named inserted.Obviously this construction gives a name collition beetween the twojoins (since both joins starts from the same table)Ofcourse I thougt the usingbla JOIN bla ON bla bla bla AS a_different_name would work, but itdoes not. Is there a nice solution to this problem?Any help appriciated
I've been trying to access the file table shares exposed on a server (I can select, delete, update, etc via SQL) via untransacted access with no luck (the share just sits there doing something when I try to access it, no error message of any kind).
So I thought I'd look to see if there were any open file handles via
SELECT * FROM sys.dm_filestream_non_transacted_handles;
I found there were 10 non transacted handles open, and they've been open for 3 days for automated processes and the files are about 1-2Kb in size.
so I tried to kill them via exec sp_kill_filestream_non_transacted_handles
that's normally not something that takes very long (in fact when I cleared two for a different database it took just a second or two).
So leaving the stored proc running, I figured I'd connect and fire up Adam Mechanic's sp_whoisactive.
I see the processes to kill the open file handles, with a wait state of FFT_NSO_FILEOBJECT
So I have an MDX query in an SSRS data set. Here is my MDX query:
Code SnippetSELECT { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, { ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
This query returns 4 fields. Media Property, Promo Code Description, Gross Sales, and Net Sales. For the given query the measures are empty or null. I do not want any data to show up when the measures are null so I put in NON EMPTY clauses before the COLUMNS and before the ROWS. So now my query looks like this: (I only added the NON EMPTY clauses)
Code Snippet SELECT NON EMPTY { [Measures].[Gross Sales Amount USD], [Measures].[Net Sales Amount USD] } ON COLUMNS, NON EMPTY{ ([Promotion].[Media Property].[Promo Code Description].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOMEMBER(@BeginDateConvert, CONSTRAINED) : STRTOMEMBER(@EndDateConvert, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@PromotionMediaProperty, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Promotion].[Campaigns].[Campaign].&[Paid Partner] } ) ON COLUMNS FROM ( SELECT ( { [Products].[Product Line].[Line].&[Merchandise] } ) ON COLUMNS FROM ( SELECT ( { [BusinessUnit].[Business Unit].[Product Business Unit].&[40] } ) ON COLUMNS FROM [Net Sales]))))) WHERE ( [BusinessUnit].[Business Unit].[Product Business Unit].&[40], [Products].[Product Line].[Line].&[Merchandise], [Promotion].[Campaigns].[Campaign].&[Paid Partner] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Adding the NON EMPTY returns nothing... not even field names. This is a problem because, I have a table in the report that looks at this data set and when there are no fields, the report can't run.
How can I still have NON EMPTY functionality and still show the fields? Is this a problem in SSRS?
I've seen examples of creating a Web Service that executes an SSIS package through code. But that involves developing the web service, configuring IIS on the same server as SSIS to expose the web service. I was wondering if it is easy to use the HTTP endpoints within SQL Server 2005 to create a web service interface that calls an SSIS package, supplying any parameters the package needs to run. Also, this would be called from within Excel VBA that uses a web-reference via the Web Reference Toolkit.
Im trying to find the error in this statement:CREATE PROCEDURE STP_selectmainASselect a.inventoryid, b.firstname, b.lastname, art.title, art.medium,a.cost, a.inventoryid, a.receivedate, a.dimensions,a.reference, art.provenance, sum(c.restorationcost),sum(d.framingcost), sum(e.cost)from art as a left outer join artist as b on a.artistid =b.artistid,a left outer join restoration as c on a.inventoryid =c.inventoryid,a left outer join outframing as d on a.inventoryid =d.inventoryid,a left outer join basiccosts as e on a.inventoryid =e.inventoryidgroup by a.inventoryid, b.firstname, b.lastname, a.title, a.medium,a.cost, a.inventoryid, a.receivedate, a.dimensions, a.reference,a.provenanceorder by a.inventoryid descGOeveytime I do a syntax check on it I get this error.error 1012: the correlation name 'a' has the same exposed name astable 'a'.Whats the syntax to fix this?thanks-Jim
Is there a performance hit if I use CAPITOL LETTERS (like this) in column names for my SQL Server tables? Logically I would think that lower case letters would have better performance because they are smaller characters and easier to store.
I'm working in VS 2010 for SSIS 2012. I have a project whose deployment is done via project deployment to SSISDB. I've got a local flatfile connection manager within one of its packages. When I deploy I'm unable to override the file name property in either package configuration or job step configuration on the SQL Server. If the flatfile connection were at the project level, I could. Not sure of the rationale for this or maybe I'm doing something wrong.
select 1 as ID, 'Kenneth' as Name, '1963-02-26 00:00:00.000' as DOB, 'M' as Sex,'C' as Race, 516 as Height, 160 as Weight union select 2, 'Kenneth', '1963-02-26 00:00:00.000', 'M','C', 516, 160 union select 3, 'William', '1962-06-28 00:00:00.000', 'M','C', 600, 223 union
As part of my backup routine, I have a SQL job for each DB which which is called "DB-NAME - Backup Job"
I need to put a script together to check that each database has a backup job associated to it.
select * From sys.sysdatabases where name not in ('msdb','model', 'master', 'Tempdb', 'DBA') order by name desc
select * from msdb.dbo.sysjobs order by name desc
I know all the details i need are in there, but i cant figure out the best way to tackle it. Do I need to do a cursor to go through each DB name, put it into a variable, then select the job name where name like '$variable%' ?
Is there a way in SQL server to alias column names for a particular table and store the aliases somewhere such that you can access the aliases while querying? I have a table where I cannot change column names and I am trying to figure out if there is a way to alias them to make them more user friendly.
I have a field in a table that contains a different formula (varchar(1000)) for each record. It's along the lines of something like this, although each formula is different: ([ColumnA] - [ColumnB])/([ColumnC] - [ColumnD]). I plug that into a dynamic SQL statement so that it can get executed in a select statement.
Due to the variations of the formulas, checking for Divide by Zero, etc, we want to move this to a .NET method. We'd like to replace "ColumnA" and "ColumnB", etc., with the actual values so that we're passing something like (5-3)/(6-2). I haven't been able to figure out a way to do this without actually executing it. We don't want to pass the solution, but the equation filled with the actual values rather than the column names.
I need to ensure some naming standards to users while creating tables.
All table name should be in same pattern
(ie., ) TeamName_EmpId_tablename
I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.
I'm getting a rule check fail "Long path names to files on SQL Server Installation media failed" when installing SQL 2012 Standard edition from a network share.
how to find the names of the tables owned by the particular user in sql server and how to display the distinct object types owned by the particular user.
I have query which is used to dynamically insert value but not working. We are trying to get all table names and insert dynamically to all tables by looping through table names.
declare @a varchar(max),@i int; declare @table table(rno int, name varchar(max)) declare @b varchar(max) insert into @table select row_number() over( order by table_name) rno, table_name from INFORMATION_SCHEMA.tables declare @tblname varchar(max)
I am trying to replace names found in 'xml' field name as hyperlinks, by matching with the names in database_tags.I am using the following function below, however the UDF is only recognizing one name from the XML fieldname data, instead of all the names present in the XML data.
ALTER FUNCTION [dbo].[ReplaceTags](@XML VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN
[code]...
for example, if the XML input data is the following: It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.But the updated function is only recognizing two of names BFTH, BFTH, as hyperlinks, from the database_tags table.Is there a way to get the function to recognize more than one names as hyperlinks.
I am trying to query the SharePoint database to get a list of how many times a table name is being used in our SSRS reports.Here is my current query:
DECLARE @TEXTTOSEARCH AS VARCHAR(200) SET @TEXTTOSEARCH = 'tablenamehere' ;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition', 'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd)
[code]...
This works for some tables since the names are unique and I can just do a search for that. However, some of the table names are simple and can also be used as aliases.if there was some way to get the list of table names easier.
In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable SET [columName] = (SELECT SUM(columName) FROM RegularTable WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
Looking to coalesce a WHERE clause with XML data. I cannot supply test data.
If 472 is null, check the NEXT XML field at grab the 150, if both 472 AND 150 do NOT exist let it be NULL.
set @Date = (select top 1 XL.value('(DTM/DTM.02/DTM.02.1)[1]', 'varchar(100)') from #src where coalesce(XL.value('(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '472', '(DTM/DTM.01/DTM.01.1)[1]', 'varchar(50)') = '150')
I have an SSIS package which uses variables and foreach loop containers so connect to multiple instances to retrieve config data. I am adding an extra step to include the port of each instance.
I used a select from a central table to get the connection strings, which is put into [User::Server2]
I have a temporary table #Temp with, among others, a column CountryCode and a column Lastname.I would like to change the ü that appears in some names to u (u umlaut to a plain u), but only for those that have the nationality 'Ned'
My code so far:
Update #Temp set LastName = replace(Lastname, 'ü', 'ue') WHERE CountryCode = 'Ned'
This code deletes all entries in the column Lastname
I think it must be easy, but I keep staring at the code.
From MS Dynamics NAV 2013 I get a lot of querries that have a where clause like this:
where [Field1] like @p1 and [Field1] < @p2. Field1 is the only primary key field and clustered index. The query also has a TOP 50 clause. @p1 is always a "Starts-With"-value (something like N'abc%').
The query plan uses a clustered index seek but the number of reads look more like a clustered index scan.
Depending on the table size I see 1M or more reads for these querries.
If I rebuild the query in SSMS, but replace the paramerters with actual values I only see a few reads.
I was able to reproduce the issue with a temp table. See code below.
Is there a way to make SQL Server use another strategy when using the parameterized query?
SQL Server Version is 11.0.3401. if object_id('tempdb..#tbl') is not null drop table #tbl; create table #tbl ( [No] nvarchar(20) ,[Description1] nvarchar(250)
Right now I have to do something like this and it is time consuming every time I have to query a specific table...
SELECT lots_of_columns FROM table WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1') AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
