Using SQLCMD in Views and stored procedures (SSMS 2005)

I know I can get the results I want by using a Dynamic SQL string but wondered if I could utilise SQLCMD for some of my more complex queries.

I have a situation where I'm running a query on one database that obtains some of its data from a separate database. So far so straightforward!

However, this separate database changes name each year(in this example db_2016). So I have a query like this (but longer and more complex!)

Mycte1 as (
select x,y,z from main_database.dbo.table
 ),
Mycte2 as (
select db_2016.dbo.field1,db_2016.dbo.field2 from 
       db_2016.dbo.another_table
)

Now with SQLCMD I can add

:isvar SourceDatabase db_2016

and the select now becomes

Mycte1 as (
select x,y,z from main_database.dbo.table
 ),
Mycte2 as (
select $(SourceDatabase).dbo.field1,$(SourceDatabase).dbo.field2 from 
       $(SourceDatabase).dbo.another_table
)

So at the end of the year all I need to change is the SourceDatabase field.

This works fine in Management Studio when I use Query>SQLCMD, but how can I achieve this when the SQL is part of a View or Stored Procedure?

parent child relationship between composite key and foreign key

Sorry I have to post this again because I thought my first post was confusing I need a design suggestion from all you experts

So I have tables as below:

Table 1

( A integer not null , B char(1) not null , C integer not null , D not null , primary key (A, B, C, D) i.e. composite key )

Table 2

( A integer not null , B char(2) not null , C integer not null primary key (A, B, C) composite key )

Table 3

( A integer not null , B char(2) not null , D not null primary key (A, B, D) )

I want to create a relationship between table 1 and 2 and Table 1 and 3.

Table 1 being the parent table and table 2 and 3 being the child table of Table 1. I can easily creat a relationship between table 1 and table 2, but I get an error when I try to create a relationship between 1 and 3 because the foreign key can not be the part of the composite key and it has to be the whole composite key. I would highly appreciate any suggestions.

VBScript and SQL Server 2005 compare current row to previous row

I am dealing with some legacy tech at my new job. Everything is either VBS or VBA and SQL Server 2005 is still running for at least 1 server, and 2008 on another. I can't change the tech I have to work with, so please don't respond with "Just use PowerShell" ... which is all I got from the Microsoft forums.

I need to modify an existing VBScript to iterate through the records of a table and give each page of a document a page number. The page number starts at 1 and resets to 1 every time the location folder changes, as defined by the row's value in the column Path. So I need to compare the current row's Path to the previous row's Path to see if there is a change.

From the specs request:

Use the following structure:

• Outermost folder = Box field

  • Sub-folder = File Folder field
    • Docs within will each begin at "0000001"
      • Sub-folder = File Folder field
        • Docs within will each begin at "0000001"

  • Sub-folder = File Folder field

    • Docs within will each begin at "0000001"

Here is a very simple but effective Excel script that works, unfortunately I have to use VBScript so a third-party software can run the script:

=IF  "Folder field in this row" = "Folder field in row above"
True = "above column" (beg doc of previous doc) + "pgcount of previous doc"
False = "1"

Here is the existing SQL statement from within the VBScript (Sorry for the naming convention - it is not my choice):

UPDATE tblpage 
SET tblpage.UID = newtable.keyid 
FROM tblpage inner join (SELECT PKEY, '" & prefix & "' + REPLICATE(0, " & padnum & " -
LEN(convert(varchar(" & padnum & "),(" & startnum & " + rank() OVER (ORDER
BY TBLDOC." & sortfield & ") + Page - 2)))) + CONVERT(nvarchar(" & padnum &
"), convert(varchar(" & padnum & "),(" & startnum & " + rank() 
OVER (ORDER BY TBLDOC." & sortfield & ") + Page - 2))) AS keyid 
FROM tblpage 
inner join tbldoc on tblpage.id = tbldoc.id) as newtable ON newtable.pkey = tblpage.pkey;

variables taken from user input in VBScript - explanation

prefix - optional text prefix to the iterative number ie "SET" for "SET0000001"

padnum - number of 0s before iteration begins ie "000000" for "SET0000001"

startnum - starting number for the iteration ie "1" in "SET0000001"

sortfield - which table column to sort by ie a table's UID.

The above SQL does the iteration correctly but doesn't reset on new folder. I have been looking into http://ift.tt/19vQWo7 but I am still quite confused.

My thoughts are that I join just the Path to the newtable based on the row's UID - 1, and then a case statement to compare them. If different, reset the page number. If the same, continue as normal.

Path is found in tblDoc. Look at UID in tblPage for the page numbering structure

Thanks!!

execute storedprocedure with one arg in server side and sql recognizes as two args

i'm currently trying to execute a stored procudure with one argument. the database is SQL Server 2005 and i am calling the sp from C# using OdbcCommand.

OdbcCommand oCmd = new OdbcCommand("{call sp_dominioMaquinas(?)}");
oCmd.Connection = new OdbcConnection(Config.Config.GetConnectionStringSqlConnection());

string psTipoExportarAux = "'" + psTipoExportar + "'";

oCmd.Parameters.Clear();
oCmd.Parameters.Add("@psTipoExportar", OdbcType.VarChar);
oCmd.Parameters["@psTipoExportar"].Value = psTipoExportarAux;

my problem is that my parameter psTipoExportar is a string with a coma (,) in it. (ex: a', 'b) and so sql server recognizes it as two parameters instead of one.

using a tool to see requests to my DB, I can see that my request is beeing misinterpreted (ex: exec sp_dom '''a'', ''b''') as two arguments when I want it to be processed as one.

if anyone could help out, that you

I'm following MSDN

and i've checked this questions here , here and here.

How to conditionally exclude or add columns when calculating row total

I have a stored procedure with a parameter defined like below

    @CategoryNames  NVARCHAR(MAX)

The above parameter gets a comma separated string from the application, for example the value can be like this @StaffCategoryIds=N'Category1,Category2,Category3,Category4,Category5'

Then i have the sample below sql query

SET @sql = @sql + 'TypeId, 
        TypeName, 
        Condition,
        Region, 
        (Category1 + Category2 + Category3 + Category4 + Category5) AS Total INTO ##QueryResults
FROM Table1;'

When computing my Total column in the query above, how can i first check whether a category column (say Category2 or Category5) exists in my @StaffCategoryIds comma separated string before including it in the computation of my Total column?

Eg. if 'Category2' exists in @StaffCategoryIds, then i include it in computation of Total else i exclude it.

Limit script to run only on specific databases

I have databases

DEV_a    TST_a    REAL_a
DEV_b    TST_b    REAL_b
DEV_c    TST_c    REAL_c

These can be on same server or on different server. Now I'm writing a script/transaction that's relevant only for DEV_a, TST_a and REAL_c.

Is there any way I can limit script to run only on those databases?

I thought this might work:

USE [DEV_a]

but that would force it to run only on one database, but I have a list. Is this doable?

Extra question: if it is doable, can I run parts of the script for DEV_a and TST_a but different part of script for REAL_c?

SQL update for the varchar column

I want to change the string of AttachmentCopyLoc columns from D:\IT\Public\FTX_RobotAlerts\336 to V:\IT\Public\FTX_RobotAlerts\336 only change here is D to V, the remaining string is the same (I don't event want to change that).

How can I do that?

Thanks in advance for your help.