internal static string SelectAllValuesFromTransmissionQueue(Urn urn) { string script = string.Empty; StringBuilder selectQuery = new StringBuilder(); /* * SELECT TOP *, casted_message_body = * CASE MESSAGE_TYPE_NAME WHEN 'X' * THEN CAST(MESSAGE_BODY AS NVARCHAR(MAX)) * ELSE MESSAGE_BODY * END * FROM [new].[sys].[transmission_queue] */ selectQuery.Append("SELECT TOP (1000) "); selectQuery.Append("*, casted_message_body = \r\nCASE message_type_name WHEN 'X' \r\n THEN CAST(message_body AS NVARCHAR(MAX)) \r\n ELSE message_body \r\nEND \r\n"); // from clause selectQuery.Append("FROM "); Urn dbUrn = urn; // database while (dbUrn.Parent != null && dbUrn.Type != "Database") { dbUrn = dbUrn.Parent; } selectQuery.AppendFormat("{0}{1}{2}", ScriptingGlobals.LeftDelimiter, ScriptingUtils.QuoteObjectName(dbUrn.GetAttribute("Name"), ScriptingGlobals.RightDelimiter), ScriptingGlobals.RightDelimiter); //SYS selectQuery.AppendFormat(".{0}sys{1}", ScriptingGlobals.LeftDelimiter, ScriptingGlobals.RightDelimiter); //TRANSMISSION QUEUE selectQuery.AppendFormat(".{0}transmission_queue{1}", ScriptingGlobals.LeftDelimiter, ScriptingGlobals.RightDelimiter); script = selectQuery.ToString(); return(script); }
internal static string SelectAllValues(Urn urn) { string script = string.Empty; StringBuilder selectQuery = new StringBuilder(); selectQuery.Append("SELECT TOP (1000) "); selectQuery.Append("*, casted_message_body = \r\nCASE message_type_name WHEN 'X' \r\n THEN CAST(message_body AS NVARCHAR(MAX)) \r\n ELSE message_body \r\nEND \r\n"); // from clause selectQuery.Append("FROM "); Urn dbUrn = urn; // database while (dbUrn.Parent != null && dbUrn.Type != "Database") { dbUrn = dbUrn.Parent; } selectQuery.AppendFormat("{0}{1}{2}", ScriptingGlobals.LeftDelimiter, ScriptingUtils.QuoteObjectName(dbUrn.GetAttribute("Name"), ScriptingGlobals.RightDelimiter), ScriptingGlobals.RightDelimiter); // schema selectQuery.AppendFormat(".{0}{1}{2}", ScriptingGlobals.LeftDelimiter, ScriptingUtils.QuoteObjectName(urn.GetAttribute("Schema"), ScriptingGlobals.RightDelimiter), ScriptingGlobals.RightDelimiter); // object selectQuery.AppendFormat(".{0}{1}{2}", ScriptingGlobals.LeftDelimiter, ScriptingUtils.QuoteObjectName(urn.GetAttribute("Name"), ScriptingGlobals.RightDelimiter), ScriptingGlobals.RightDelimiter); //Adding no lock in the end. selectQuery.AppendFormat(" WITH(NOLOCK)"); script = selectQuery.ToString(); return(script); }
internal string SelectFromTableOrView(Server server, Urn urn, bool isDw) { DataTable dt = GetColumnNames(server, urn, isDw); StringBuilder selectQuery = new StringBuilder(); // build the first line if (dt != null && dt.Rows.Count > 0) { selectQuery.Append("SELECT TOP (1000) "); // first column selectQuery.AppendFormat("{0}{1}{2}\r\n", ScriptingGlobals.LeftDelimiter, ScriptingUtils.QuoteObjectName(dt.Rows[0][0] as string, ScriptingGlobals.RightDelimiter), ScriptingGlobals.RightDelimiter); // add all other columns on separate lines. Make the names align. for (int i = 1; i < dt.Rows.Count; i++) { selectQuery.AppendFormat(" ,{0}{1}{2}\r\n", ScriptingGlobals.LeftDelimiter, ScriptingUtils.QuoteObjectName(dt.Rows[i][0] as string, ScriptingGlobals.RightDelimiter), ScriptingGlobals.RightDelimiter); } } else { selectQuery.Append("SELECT TOP (1000) * "); } // from clause selectQuery.Append(" FROM "); if (server.ServerType != DatabaseEngineType.SqlAzureDatabase) { // Azure doesn't allow qualifying object names with the DB, so only add it on if we're not in Azure database URN Urn dbUrn = urn.Parent; selectQuery.AppendFormat("{0}{1}{2}.", ScriptingGlobals.LeftDelimiter, ScriptingUtils.QuoteObjectName(dbUrn.GetAttribute("Name"), ScriptingGlobals.RightDelimiter), ScriptingGlobals.RightDelimiter); } // schema selectQuery.AppendFormat("{0}{1}{2}.", ScriptingGlobals.LeftDelimiter, ScriptingUtils.QuoteObjectName(urn.GetAttribute("Schema"), ScriptingGlobals.RightDelimiter), ScriptingGlobals.RightDelimiter); // object selectQuery.AppendFormat("{0}{1}{2}", ScriptingGlobals.LeftDelimiter, ScriptingUtils.QuoteObjectName(urn.GetAttribute("Name"), ScriptingGlobals.RightDelimiter), ScriptingGlobals.RightDelimiter); // In Hekaton M5, if it's a memory optimized table, we need to provide SNAPSHOT hint for SELECT. if (urn.Type.Equals("Table") && ScriptingUtils.IsXTPSupportedOnServer(server)) { try { Table table = (Table)server.GetSmoObject(urn); table.Refresh(); if (table.IsMemoryOptimized) { selectQuery.Append(" WITH (SNAPSHOT)"); } } catch (Exception ex) { // log any exceptions determining if InMemory, but don't treat as fatal exception Logger.Write(TraceEventType.Error, "Could not determine if is InMemory table " + ex.ToString()); } } return(selectQuery.ToString()); }