Wednesday, September 18, 2013

[how to] How to UPDATE a table for the level of parents in a column?

[how to] How to UPDATE a table for the level of parents in a column?

How to UPDATE a table for the level of parents in a column?

Posted: 18 Sep 2013 09:06 PM PDT

I have a table with internal parents as

id    parent    parent_level  1     NULL      1  2     1         2  3     1         2  4     3         3

where parent has a FK to id.

How can I update the table to assign the values of parent_level, according to the parent, and parent of parent of each row.

Where can I see the log in MSSQL 2008 R2 [duplicate]

Posted: 18 Sep 2013 07:33 PM PDT

This question already has an answer here:

I want to know if is possible that MSSQL 2008 r2 has a log, when someone run an alter table or modify a SP MSSQL save this action and who did it, if this is possible how can I see the log. Sorry for my English

What am I doing wrong in mysql database?

Posted: 18 Sep 2013 08:56 PM PDT

I'm creating a trigger that deletes rows from a table when they are inserted. However, when the trigger executes I get the following message:

MYSQL ERROR: Can't update table 'player_viplist' in stored function/trigger because it is already used by statement which invoked this stored function/trigge

What do I need to do for this trigger to work?

DROP TRIGGER dele;    DELIMITER |    CREATE DEFINER = 'root'@'localhost' TRIGGER dele    AFTER INSERT    ON player_viplist    FOR EACH ROW  BEGIN DELETE FROM player_viplist ;     END|    DELIMITER ;

Several stays: many-to-many relationship

Posted: 18 Sep 2013 07:36 PM PDT

Could you please help me with the following problem? I'm a total novice in DB design.

I have a table with researchers who visit our university. They come for some period of time (from, till). Now, how can I save in the database if somebody came several times? I have an entry already for a particular person and need to add some other stays and be able to see them in a form.

My idea was: this must be a many-to-many relationship. Ok. Then I need a third table Researcher/Stay. And then I don't know what to do next. In Stay-table "from+till" is a key. I haven't seen examples with double-value key in a many-to-many relationship and don't know what to do next.

enter image description here

FOR loop oracle query with calulation

Posted: 18 Sep 2013 03:36 PM PDT

I have a list of distinct areas, and would like to track statistics for those districts in a SQL statement. I currently have a setup that runs some summary information, but it doesn't include the whole list.

What I'd like to do is Select a distinct array of values from 1 table, then run SQL for each of those values against a few other tables and populate a third table. I've done a fair amount of Googling, and see that I can loop using "LOOP" or feed in the distinct values via a CURSOR.

Here's my SQL I'm using now, but if there isn't a dispatchgroup that is in the current outages table, it doesn't show statistics for that dispatch group. 

 select a.DISPATCHGROUP, a.WEATHER, SUM(a.NUM_CUST) as out_cust, count(a.eventnum) as events,  (select count(*) from v_outcall_rpt b, v_current_outages_rpt c where b.isopen ='T' and b.dgroup = a.dispatchgroup )  as calls,   (select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup  and c.event_type_code = 'TOEL')   as ISOLATED,   (select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOTO' or c.event_type_code = 'TOTU' or c.event_type_code = 'TOVT' or c.event_type_code = 'TOPM'))   as TSTATION,   (select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOFS' or c.event_type_code = 'TOSC' or c.event_type_code = 'TORL' or c.event_type_code = 'TOLC' or c.event_type_code = 'TOSW'))  as lateral,  (select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOCR' or c.event_type_code = 'TOSB' or c.event_type_code = 'TOTL' ))   as mainline,   (select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOEE' ))   as emergency,   (select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOEC' ))   as cleanup,   (select count(*) from v_outage_duration_rpt where substr(restore_dts,1,8) = (select SUBSTR(max(view_dts),1,8) from v_current_outages_rpt) and a.dispatchgroup = dgroup) as restored,   (select count(*) from v_outcall_rpt where substr(offtime,1,8) = (select SUBSTR(max(view_dts),1,8) from v_current_outages_rpt) and a.dispatchgroup = dgroup) as callstoday   from v_current_outages_rpt a where weather is not null group by a.DISPATCHGROUP, WEATHER;

When I try adding the select distinct dgroup from MV_WE_AGENCY_LOOKUP as dispatchgroup it errors out with single query returns more than one row.

Concatenate one-to-many field in single result?

Posted: 18 Sep 2013 02:06 PM PDT

Say I have the following query:

SELECT *   FROM AppDetails, AppTags   WHERE AppDetails.AppID = '1'     AND AppDetails.AppID = AppTags.AppID

Which gives the following results:

AppID    AppName        AppType    Tag  1        Application1   Utility    Test1  1        Application1   Utility    Test2  1        Application1   Utility    Test3

How can I modify the query to return something like this:

AppID    AppName        AppType    Tags  1        Application1   Utility    Test1,Test2,Test3

How to increase MySQL performance with joins

Posted: 18 Sep 2013 03:13 PM PDT

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below:

SELECT t1.`ARTREF1`   FROM `lijnen` AS t3     INNER JOIN       (`kortingen` AS t2, `raiproduct` AS t1)     ON (t1.LEV=t3.LEV     AND t2.LEV=t3.LEV    AND t1.KCP1=t3.SPLC    AND t2.SPLC=t3.SPLC     AND t1.LEV=t2.LEV     AND t2.SPLC=t1.KCP1    AND t1.`ARTREF1`='".$xartc."'     AND t2.ALDOC='".$supcodec."'     AND t3.BVNR='".$bvnr."'      )

How it works: I get through SOAP webservice an XML document. In the XML document I get the article number and supplier number and I query my database to see if the article exists.
It takes nearly 5 to 8 seconds to show the results on screen.

I use indexes on the rows which I search. Every day I purge empty fields to optimize my database How can I increase the speed of the query?

Some database and hardware information:

  • raiproduct: 1.500.000 rows
  • lijnen: 4100 rows
  • kortingen: 1200 rows
  • CPU: 4 cores
  • Processors: Intel 64bit hexacore (6 cores) 2.5 Ghz
  • RAM: 4096 MB
  • SWAP RAM: 1024 MB

I hope someone can help me with this problem.

MDX Crossjoin filtering on different hierarchies from the same dimension is very slow

Posted: 18 Sep 2013 01:42 PM PDT

I have an MDX query that was written by an MDX novice that is excessively slow (the query, that is, not the MDX novice). And I am also an MDX novice. Here is the query:

SELECT  NON EMPTY  (      [Measures].[Status]  )  ON COLUMNS,  NON EMPTY  (      Filter      (           Crossjoin          (              {                  [RiskType].[RiskType].[MemberValue123],                  [RiskType].[RiskType].[MemberValue456]              },              [Trade].[TradeType].[TradeType],              [Expiry].[Expiry].[Expiry].ALLMEMBERS,              [Tenor].[Tenor].[Tenor].ALLMEMBERS,              [YieldCurveCurrency].[YieldCurveCurrency].[YieldCurveCurrency],              [Trade].[TradeBook].[XYZ1],              [Index].[Index].[Index],              [EffectiveStrike].[Effective Strike Name].[Effective Strike Name]          ),          (              [Measures].[Status]          ) > 0      )  )  ON ROWS  FROM [RePro]  WHERE  (      [RiskSet].[RiskSet].[ABC],      [Portfolio].[Portfolio].[XYZ],      [RunDate].[RunDate].[17 Sep 2013]  )  CELL PROPERTIES VALUE

I have very little knowledge of MDX, but through some trial-and-error, I have found that removing the two [RiskType].[RiskType] entries from the Crossjoin makes the query return quickly. Or, removing both the [Trade].[TradeType] and [YieldCurveCurrency] entries also makes it return quickly. But, obviously, this is changing the query, so is not the solution, but perhaps it provides clues as to where I should look in terms of indexes or suchlike if there is anything like this in SQL Analysis Services (I'm more familiar with SQL Server databases)?

One thing I did try, is to put & before [MemberValue123] and [MemberValue456]. This caused the query to return very quickly with no results. Unfortunately, I don't know if this is correct because the query without this change takes too long to be able to see if there are any results. I don't know what difference & is supposed to make, but is this the obvious answer or does it change the query to be different? It is equally possible that the original writer of this query should have used & anyway, but never got to test the query with a real data set.

Any help would be greatly appreciated.

MS SQL 2012 Express dying randomly

Posted: 18 Sep 2013 11:39 AM PDT

I have a Windows 2008R2 box with MS SQL 2012 Express. The problem is that randomly(sometimes twice a day, sometimes once a month) the SQL Server dies leaving those messages in error log:

Error: 49910, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.  Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.  Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

When I try to start the service again, it says:

Error: 17300, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.  Error: 17312, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.  Error: 33086, Severity: 10, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

After reboot of the machine it starts working again. How can I fix this problem?

Restore of database failed

Posted: 18 Sep 2013 12:03 PM PDT

I wanna restore my database and i use sql server 2012 express edition but when i restore my backup file in sql server management studio, i take this error:

System.Data.SqlClient.SqlError: There is insufficient free space on disk volume 'c:\' to create the database. The database requires 84260749312 additional free bytes, while only 47428677632 bytes are available. (Microsoft.SqlServer.SmoExtended)

My .bak file is 8.27 gb and my C disk has 44.1 gb free space. Why do i take this error ? how can i fix this error ? Thanks in advance.

How to transfer logins from sql server 2012 to 2008?

Posted: 18 Sep 2013 11:51 AM PDT

I need to transfer dynamically logins from SQL Server 2012 to SQL Server 2008 and I can't because when I get the hashed password on 2012 does not have a valid format to be used on 2008. I know that SQL Server change its legacy of hashing on 2012. So that the hash is bigger than those of 2008.

Is there a way to perform this?

Regards.

is oracle free to download

Posted: 18 Sep 2013 03:24 PM PDT

I am a Java developer and currently I use PostgreSQL as the RDBMS. However, I would also like to learn Oracle. So can I download a latest version of Oracle (11g I guess) for free ?

I found this link : http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-linx8664soft-100572.html

and it seems that it is downloadable. However, I know that Oracle RDBMS (or ORDBMS) is a proprietary one. So how does that work ?

Also, will there be PL/SQL along with the download of Oracle 11g ?

pg_upgrade unrecognized configuration parameter "unix_socket_directory"

Posted: 18 Sep 2013 09:00 AM PDT

I'm trying to upgrade Postgresql from 9.2 to 9.3 in Fedora 18 using this command as the postgres user

$ pg_upgrade -b /bin -B /usr/pgsql-9.3/bin -d /var/lib/pgsql/data -D /var/lib/pgsql/9.3/data/ -j 2 -u postgres

The error in the log

command: "/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/data" -o "-p 50432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql'" start >> "pg_upgrade_server.log" 2>&1 waiting for server to start....FATAL: unrecognized configuration parameter "unix_socket_directory" .... stopped waiting pg_ctl: could not start server

As pointed by a_horse in the comments that parameter was replaced by unix_socket_directories (plural) in 9.3. But the server version being started is the old one 9.2:

$ /bin/pg_ctl --version  pg_ctl (PostgreSQL) 9.2.4

Any ideas?

When was Torn Page Detection introduced to SQL Server and what is its upgrade behavior?

Posted: 18 Sep 2013 03:22 PM PDT

There are two different options in modern SQL Server for page verify; being TORN PAGE DETECTION and CHECKSUM. NONE is also of course an option.

I believe CHECKSUM was introduced in SQL Server 2005 and that upgrading or restoring a DB from a prior version would maintain its previous page verify method. i.e. there was no implicit upgrade.

I have a production database that went into production using SQL Server 2000, though may have been developed against SQL Server 7.0, and has since moved to a SQL Server 2008 R2 server. Page Verify is set to NONE though I expected it to be TORN PAGE DETECTION.

I was wondering when TORN PAGE DETECTION became a Page Verify feature and SQL Server, and how it behaves when migrated or upgraded to newer editions.

Lock a MySQL database for upgrade

Posted: 18 Sep 2013 08:54 AM PDT

I've looked around for solutions to lock a whole database, and the only one I found so far is FLUSH TABLES WITH READ LOCK.

This is fine for backups, but unfortunately I can't use that to prevent other accesses to the database while I'm patching my database with schema changes. If I run an ALTER TABLE after FLUSH TABLES WITH READ LOCK, I get the following message:

Can't execute the query because you have a conflicting read lock

Is there a way to just prevent other database connections from accessing the database temporarily while I'm patching the database?

I don't want to actively refuse the other connections, I'd just like to put them "on hold" until the patching is finished (a matter of seconds).

How can I do that?

retrieve data based on a condition

Posted: 18 Sep 2013 11:29 AM PDT

I have 2 tables, TA and TB:

TA  --------  id  name  1   a  2   b  3   c  4   d  5   e  6   f    TB  --------  id  p_id  name  1   1     a  2   1     b  3   1     c  4   2     a  5   2     b  6   2     d

I need : if p_id = 1 then :

id  name match  1   a    1   2   b    1  3   c    1  4   d    0  5   e    0  6   f    0

If p_id = 2 then :

id  name match  1   a    1   2   b    1  3   c    0  4   d    1  5   e    0  6   f    0

Is there a way to achieve Workload Management in MySQL?

Posted: 18 Sep 2013 06:18 PM PDT

Is there a way to set up Workload Management in MySQL (in particular v5.5 on AWS RDS)?

By Workload Management I mean the functionality implemented in e.g. Teradata, RedShift that allows prioritization of queries. In particular, I want to give high priority to tactical, short running queries from applications and give low priority to longer running background queries.

Duration Field in Profiler

Posted: 18 Sep 2013 01:05 PM PDT

I would like to monitor the script which takes more than 25 Sec. When i use filter i am not able to see the duration taken by the script. How can we get the time taken to execute the query.

My Requirement is simple:
Get all the scripts which takes more than 25 Sec(Time taken for execution of each statement is required).

Reasons to Backup (or not backup) QA and Dev Databases

Posted: 18 Sep 2013 01:32 PM PDT

Pretty simple question: is it standard/best practice to backup dev and QA instances of the database?

Assume that the schema information is kept in some form of source control.

How to store object with property of generic type in DB

Posted: 18 Sep 2013 03:37 PM PDT

In my business domain there exists an entity parameter. Beside other properties which are identical for each parameter, they have values. These values can be of different data types. There might be values that are strings, integers, timestamps, IP-Adresses, etc. It is also possible that I need to add another data type later but this should happen not very frequently. New parameters will be inserted frequently. Values of existing parameters will probably be updated several different times. Parameters and values will be read very frequently but only for displaying them to the user, so without any special analytical operation.

For the later code (which will be C++) I was thinking of a parameter base class from which subclasses for each data type will be derived. StringParameter, TimestampParameter, etc.

I was now wondering how to store parameters in an Oracle 10g database. Ideas so far are

  • Single table, string column: A single database table for all parameters where the column type for the value is VARCHAR2. The code can then transform the values to the correct data type by using functions like atoi. Drawback is I would need an extra type column and transforming values back and forth with atoi and itoa is not very safe and neither good maintainable (e.g. if the data type of one parameter changes from int to long).

  • Single table, raw column: A single database table for all parameters where the column type for the value is RAW. The code then just stores and retrieves the property "as-is" in binary. Drawback is I would need an extra type column and changing the data type of a parameter would break backward compatibility (or one would need to perform a database upgrade which converts all old values to the new ones.

  • Multiple tables, one for each data type: A basic parameter table for all common properties and several extra tables only for the value so that the column can be of the correct data type. Drawback: Not all data types from my domain can be mapped to an oracle data type. Additionally I would need a lot of new tables and joins. Also introducing new parameters would need a change in the database schema.

For which option would you go and why? Is there another solution I have not yet thought of?

How to access a SQL Server database from other computer connected to the same workgroup?

Posted: 18 Sep 2013 07:26 PM PDT

I have created a C# application which uses a SQL Server database. I have other computers connected to me and to each other in a workgroup. I have shared my C# application with others. When they open the application they get the error

A network related or instance-specific error occured while establishing a connection to SQL Server. the server was not found or was not accessible

But the application is working fine on my PC. The connection string I am using is 

Data Source=ASHISHPC1\SQLEXPRESS;Initial Catalog=ACW;User ID=ash159;Password=ashish159

which is stored in a .config file.

The application is working fine on my PC. What must I do? I have enabled the TCP/IP in the server but the same error persists. Some change in connection string or something else?

Please help.. Thank you..

Improve performance of transactional replication

Posted: 18 Sep 2013 09:21 AM PDT

I 'm testing implementing transactional replication . The replication works fine if I insert/update handful of rows. The problem occurs when I have a sql job running which deletes/updates & inserts 1000's 15000 plus rows in each database(more than 100) on the server. And this job runs every n minutes. It eventually causes locking issues and slows down replication.

The main objective of doing replication is to able to scale horizontally and also improve performance. Following is a quick overview of test environment

2 database server Each server has 100 unique databases & 5 shared database(which are replicated) Any suggestion how to get around the locking issues?

Thanks

Moving large databases

Posted: 18 Sep 2013 09:21 PM PDT

I have a centos server and /var/lib/mysql/ is 125GB (disk has 1GB free space).

Ordinarily I would use mysqldump to backup the databases, but I don't normally work with such large databases, so I need to know the safest way of copying the databases over to a new server.

All advice appreciated!

How should I set up my social network database design?

Posted: 18 Sep 2013 10:21 AM PDT

I am designing a db for a Social Network type website where users enter lot of information varying from family member details, education, employment, personal favorite such as TV, movie, music, food, books etc using InnoDB and expect exponentially increasing writes but far few reads. I already have 26 tables.
My question is it better to have large number of individual tables for example as TV, movie, music, food, books or put them under one big table as MyPersonal Favorite to reduce the number of tables as I fear that there will be 26 individual disk I/Os to write one persons information with my current design

Database user specified as a definer

Posted: 18 Sep 2013 11:21 AM PDT

I have a view in my database. problem is below

Error SQL query: 

SELECT *   FROM `lumiin_crm_prod`.`v_contact`   LIMIT 1 ;

MySQL said:

1449 - The user specified as a definer ('lumicrm'@'%') does not exist

i Google for a solution

User is created for Host & not for Global.

How to create the User for Global ????

How do I execute an Oracle SQL script without sqlplus hanging on me?

Posted: 18 Sep 2013 04:21 PM PDT

For an automated task I would very much like to run some SQL scripts and make sure that sqlplus does not hang under any circumstancees, i.e.:

  • If the script contains any named substitution variable that has to be entered by the user, sqlplus should return with an error instead of prompting for the variable -- I cannot use set define off, as some of these scripts need to accept command line parameters that have to be resolved with &1

  • The script must not "hang" when it doesn't contain an exit; at the end.

    Solved: I think now that I can achieve this by wrapping the sql-script in a secondary "caller script". I.e., the caller script calls the other script with @ and then has a fixed exit; after that. The other script doesn't need an exit that way.

  • Anything else: If it would require a prompt, it should return with an error.

How can i do this with Oracle (and sqlplus or something else)?

SSRS appears to be ignoring Permissions set using Report Manager

Posted: 18 Sep 2013 07:21 PM PDT

I have setup SSRS on SQL Server 2008 in native mode.

As an administrator I can login to report manager, upload reports and run them, and also use the Web Service URL to generate reports.

I have also created a local user on the machine, I went into Report Manager as Admin, and at the top level set permissions that should assign the local user to all roles.

When I login to the machine as that user, and then navigate to Report Manager I just get the heading for the page, but do not see any of the folders that are configured.

I've checked and the folders are set to inherit parent permissions and they are showing the newly created local user in there too.

It seems odd that I have set the permissions, yet SSRS is still not showing what I should be able to see. Is there another step I need to take other than configuring the permissions in Report Manager?

When logged in as the newly created local user:

Report Manager - Shows the heading for the page, but no folders/items    Web Service URL (http://machine/ReportServer) - rsAccessDeniedError

Powershell to dump database schema to text file

Posted: 18 Sep 2013 10:07 AM PDT

I want to be able to dump a database schema to a sql text file using Powershell. I want this because our DBAs want to be able to do a diff on the file that is committed to SVN. They won't execute it if they can't see what it contains.

I've found many scripts that backup to a binary .bak file, but I can't find any that dump as a sql text file.

How do you do this in Powershell?
Posted by at No comments:

[SQL Server] How to insert in a 3 table

[SQL Server] How to insert in a 3 table

How to insert in a 3 table

Posted: 18 Sep 2013 02:14 PM PDT

I Have 3 tables....namely Bio, Sex, Status*BioBioIDFirsNameMiddleNameLastNameSexIDStatusID*SexSexIDSex(Male or Female)*StatusStatusIDStatus(Single, In Relationship or Married)here is my question...How can i insert in Table Bio in such a way that the SexID and StatusID would also be insert a data???

Inserting into two tables - little help needed

Posted: 18 Sep 2013 04:24 AM PDT

Hello all.First of all, I wanted to [b]thank [/b]everyone for participating in these forums and helping those who need help. Like me! I know you're taking time out of your day to help others. Sometimes, you just can't get the answer from a book or more importantly, some feedback!I am writing a SP which will take data from an old table (tens of thousands of records) and insert it into two related tables. I'm still early int he writing and testing phase so the code below is an early draft and a mock up.I have a table which contains basic customer information and account numbers. As a precautionary measure, I plan to take the account numbers out of the new table and store them in a separate table. So my old table may look like this:OldCustomers-------------OldCustomerIDOldAccountNumFullNameAddressAnd my two new tables may look like this:NewCustomers------------------NewCustomerIDAccountID (FK)FullNameAddressAccounts----------------AccountIDAccountNumberSo my LOOP logic is to:*Grab the first Account # from OldCustomers and insert into the Accounts Table.*Grab the ID from Accounts that was just created.*Insert the rest of the record into NewCustomers and take that ID and insert it as the FK.*Go to next record.So I have written code like this:[font="Courier New"]--INSERT Account Nums into ACCOUNTS Table FirstINSERT INTO ACCOUNTS ([AccountNumber],[Active])VALUES(( --Pull fron OldCustomers and get Account #. If empty (''), insert all zeros. CASE WHEN (SELECT [OldAccountNum] FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5) LIKE '' THEN '000000000' -- Insert Account # from OldCustomers ELSE (SELECT [OldAccountNum] FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5) END ),1)--INSERT into NewCustomers Table and also grab and insert ID of record from Accounts.INSERT INTO NewCustomers([AccountID],[FullName],[Address])(SELECT (SELECT IDENT_CURRENT('ACCOUNTS')),[FullName],[Address] FROM OldCustomers WHERE OldCustomerID BETWEEN 1 AND 5)END[/font]In case you're wondering, the BETWEEN 1 and 5 clause is just for testing. Don't want to insert 75,000 records on a test.When I try to execute this, SQL Server barks at me:[font="Courier New"]sg 512, Level 16, State 1, Procedure sp_Insert...... Line 21Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Msg 547, Level 16, State 0, Procedure sp_Insert........, Line 40The INSERT statement conflicted with the FOREIGN KEY constraint "FK_NewCustomers_Accounts". The conflict occurred in database "MyDatabase", table "dbo.Accounts", column 'AccountID'.The statement has been terminated.[/font]I'm going to assume that both errors are coming from my lack of a loop. I am also assuming that it is trying to insert ALL of the Account Numbers from OldCustomer into Accounts FIRST before trying to insert even the first NewCustomer Record.Correct?Some guidance would be appreciated!!

SQL Report Help Pls

Posted: 17 Sep 2013 10:01 PM PDT

HiI've got 2 tables in my db ( Property and Contact ) which I need to report on ( extracts below ) :Property :Prop Id.....Choices 48...........1_ChoiceA, 1_ChoiceB, 1_ChoiceC, 1_ChoiceD49...........2_ChoiceA, 2_ChoiceB, 2_ChoiceC50...........3_ChoiceA, 3_ChoiceB, 3_ChoiceC, 3_ChoiceD, 3_ChoiceEContact :Interact Id....p48 1.................1_ChoiceA 2.................1_ChoiceC 3.................NULL 4.................1_ChoiceA 5.................NULL 6.................1_ChoiceB 7.................1_ChoiceA8.................1_ChoiceC I need to create a report which gives a list of totals for all possible instances of Property Id 48 from the Contact table including zeros. The users can add more 'choices' to this list so it needs to check each time I run it for the number of values in the 'Choices' field.So basically, for each possible outcome in Choices field for Property Id 48 in Property table, total up the number of times it appears in the Contact table col p48. e.g. it should return :p48 Options Total1_ChoiceA 31_ChoiceB 11_ChoiceC 21_ChoiceD 0I have written this code ( using a Tally table ) to get list of possible Choices in the comma sep field :SELECT SubString(',' + P.Choices + ',' , T.Inc_No ,CharIndex(',' , ',' + P.Choices + ',' , T.Inc_No) - T.Inc_No)FROM S_Tally T, Property PWHERE T.Inc_No <= LEN(',' + P.Choices + ',')AND SubString(',' + P.Choices + ',' , T.Inc_No - 1, 1) = ','AND P.PropertyId = 48This gives a list of all possible Choices but how do I join this to table Contact to get totals?Hope this ramble makes sense!

Incorrect syntax near 'JOIN'

Posted: 18 Sep 2013 04:46 AM PDT

I am attempting to run this script:[b]SELECT a.GRGR_CK, a.GRGR_ID, a.GRGR_NAME, s.SBSB_ID, s.SBSB_LAST_NAME, s.SBSB_FIRST_NAME, e.CLCL_ID, e.CLCL_CUR_STS, e.CLST_MCTR_REAS, e.CLCL_CL_TYPE, e.CLCL_RECD_DT, GETDATE() AS today, e.Age, CASE WHEN e.Age <= 15 THEN 15 WHEN (e.Age > 15 AND e.Age <= 30) THEN 30 WHEN (e.Age > 30 AND e.Age <= 45) THEN 45 WHEN (e.Age > 45 AND e.Age <= 60) THEN 60 WHEN (e.Age > 60 AND e.Age <= 90) THEN 90 WHEN (e.Age > 90 AND e.Age <= 120) THEN 120 WHEN (e.Age > 120 AND e.Age <= 180) THEN 180 ELSE 181 END AS AgeRange, p.PRPR_ID, p.PRPR_NAME, CASE WHEN (i.CLST_MCTR_REAS IS NULL) THEN g.SYMD_MSG_CD ELSE i.CLST_MCTR_REAS END FROM #temp1112b e INNER JOIN [rpt_ALL].[dbo].[CER_SYMD_MSG_DEF] f INNER JOIN [rpt_ALL].[dbo].[CER_SYML_MSG_LOG] g ON g.SYMD_ID = f.SYMD_ID AND g.SYMD_MSG_CD = f.SYMD_MSG_CD INNER JOIN [rpt_ALL].[dbo].[CMC_GRGR_GROUP] a INNER JOIN [rpt_ALL].[dbo].[CMC_CLCL_CLAIM] i ON a.GRGR_CK = i.GRGR_CK LEFT JOIN [rpt_ALL].[dbo].[CMC_MCTR_CD_TRANS] j ON i.CLST_MCTR_REAS = j.MCTR_ENTITYI receive the following error: Msg 102, Level 15, State 1, Line 14Incorrect syntax near 'JOIN'.Can someone assist me in resolving this error?

Run Job with LAN ID

Posted: 18 Sep 2013 05:37 AM PDT

Hi,I have this issue: a developer told me that her SQL job (run a simple DOS command with her LAN id) failed. It worked before. The history shows: "A required privilege is not held by the client". I checked the setting, proxy account was created and linked to her credential (LAN ID) and "Operating system (CmdExec) was selected with her proxy in "proxy account properties". The account is in "SQLAgentOperatorRole", "SQLAgentReaderRoler" and "SQLAgentUserRole". The account is the local admin group.I modify the job step to run it with "SQL Server Agent Service Account". No problem. Any idea or suggsetion? xp_cmdshell is not allowed to use.Thank youYan

Manualyl update autoincemental ID field

Posted: 17 Sep 2013 06:28 PM PDT

Hi. I have a table where the UserID is set to primary key and autoincrement, i need to import data to that table, and i need the same UserID fields from my old DB. The problem is that i can't write to that field since first it is the primary key, and second it seeams to be read only and it autoincrements. I have removed the Primary key, but it's still read only. I plan to set the column back to it's original state as long as i can import some data one time to it. I have checked that the table has no dependencies, i only need the userID to be writable in the table.

Last executed queries

Posted: 17 Sep 2013 06:58 PM PDT

Dear AllI am using foloing query to get list of last executed queriesSELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.*FROM sys.dm_exec_query_stats AS deqsCROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS destORDER BY deqs.last_execution_time DESCBut noticed that for many rows dbid column is null. What does this null value means?Regards

load Report Model in Report Builder - Entities/daataset not shown

Posted: 02 May 2012 05:17 PM PDT

When report model is open in report builder[b][u]3.0[/u][/b]from report manager dataset is not populated by default(i.e. entities are not shown). where as Report builder 1.0 used to show that.Showing entity helps enduser to avoid unecessary cluter.steps to Replicate problem: 1) Open ReportManager->ReportModel2) click on dropdown and select "Load in report builder"3) it will open report builder 3.0 and will not show entities.(i.e it will show nothing under datasets)4) if you changer default report builder for reportmanager to 1.0 version it will show enitities.another way to replicate problem1) use follwoing url : [b]http://<Server>/ReportServer/reportbuilder/reportbuilder.application?model=/Models/<ModelName> [u][/u][/b] it will open report builder 1.0 and will [b]show [/b]entites.2) use follwoing url : [b]http://<Server>/ReportServer/reportbuilder/reportbuilder_3_0_0_0.application?model=/Models/<ModelName> [u][/u][/b] it will open report builder 3.0 and will [b][u]not[/u][/b] show entites.
Posted by at No comments:

[MS SQL Server] Where to see when error occurs during installation of SQL Server 2008

[MS SQL Server] Where to see when error occurs during installation of SQL Server 2008

Where to see when error occurs during installation of SQL Server 2008

Posted: 17 Sep 2013 08:53 PM PDT

Hi friends,when error occurs during installation where i can find the error than means how can i know the cause of that error.Iam new to dba....please help..thanks and regards

Login Failed for user - Password did not match

Posted: 18 Sep 2013 02:13 AM PDT

Hi,So i am migrating an application database from an SQL Server 2000 instance to an SQL Server 2005 instance.When i try to connect to the database on the new instance using the application, "i get the login failed for user: password did not match that for the login provided" 18456 error appearing in the SQL Server log.The application uses SQL Server Authentication and i have re-created the login and re-mapped it to the database on the new server.I am also able to login using management studio with the same account without a problem, it just seems to be when i try to connect via the application. It's almost as if the password is being lost or changed before it reaches the SQL Server to be authenticated.Could anyone offer and advice on this?Thanks in advance.

Kerberos Authentication stopped working

Posted: 17 Sep 2013 06:44 PM PDT

Hi guys, i´ve only been a DBA for 2 years and still i feel like a rookie at this, need some help with kerberos not working.We had a service weekend this weekend, meaning we installed a boatload of windows fixes and restarted all our servers.after that one of our linked servers stopped working.its a double hop thing, user executes SP on server a, going though LS to server B and gathers dataafter looking into it, it seemed like my colleague had installed a developement instance in our test cluster using the same sql service account we use in a production instance, so... the SPNs where tied to 2 different sql server instances.i removed the SPN relating to the dev instance so now it looks like this:MSSQLSvc/SERVERNETWORKNAME.domain.com:<port from sql server log>MSSQLSvc/SERVERNETWORKNAME.domain.com:INSTANCENAMEand this is how all my other instances look, that have a working kerberos authentication... but still it doesnt workServer A has been restarted after removing the bad SPNs and the linked server has been recreated (i read that somewhere)select auth_scheme from sys.dm_exec_connections where session_id=@@spidgives the result "Kerberos" on both server A and server Bstill, the query:select auth_scheme from sys.dm_exec_connections where session_id=@@spidselect * from openquery(DWH, 'select auth_scheme from sys.dm_exec_connections where session_id=@@spid');is getting this result:Msg 18456, Level 14, State 1, Line 1Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.Msg 4060, Level 11, State 1, Line 1Cannot open database "DATA" requested by the login. The login failed.I just dont see what i have missed, it all worked for the last year at least without a problem, the dev instance that used the prod instance service account is an old one, its been around for a loong time and although we had some issues with it before, we decided to change the service account on the dev instance now, so it doesnt have anything to do with the prod instance.any thoughts?

Replace xp_sqlMaint (RebldIdx) and (RmUnusedSpace) for SQL 2008 R2

Posted: 17 Sep 2013 05:40 AM PDT

Hi,I have to update a SQL script that was running on SQL Server 2000.This is the command line:[code="sql"]EXECUTE master.dbo.xp_sqlmaint '-D MYDATABASENAME -RebldIdx 10 -RmUnusedSpace 50 10'[/code]This line does not work in SQL Server 2008 R2. I receive this error:The SQLDMO 'Application' object failed to initialize (specific error: One of the library files needed to run this application cannot be found.)This is a common error. Tons of post on Google, but no real alternatives.I do not want to install any backward compatibility stuff. I need to find new SQL Server 2008 commands to replace it.----To replace the RmUnusedSpace parameter, I could maybe use :--> DBCC SHRINKFILE (LOG, 50) Is 50 the value that would replicate the old behaviour ?But which SQL command would replicate the (-RebIdIdx 10) parameter?Thank you for helping.Dom.

Rename Server & SQL Instance ??

Posted: 17 Sep 2013 06:22 AM PDT

We have Server_A used for reporting. As a backup if Server_A dies, I would like to copy the databases to Server_B everyday. If Server_A is down, could server_B be renamed to Server_A, and rename the default instance of SQL from Server_B to Server_A ??Otherwise I think I need to have all the users change their various connections to point to Server_B.Is there a better way ?This is a non-production scenario, where it is ok for Server_B to be a bit out of date, based on whenever the last backup was. But minimal cutover time is more important so users are not wasting a lot of time.
Posted by at No comments:

[Articles] Job Worries over Automation

[Articles] Job Worries over Automation

Job Worries over Automation

Posted: 17 Sep 2013 11:00 PM PDT

Are you worried about your job in the future? Steve Jones thinks you shouldn't be.
Posted by at No comments:

[SQL 2012] FileStream

[SQL 2012] FileStream

FileStream

Posted: 18 Sep 2013 02:07 AM PDT

Hello.I have a database with 2 filestreams and i create tables with a column varbinary(max) to a filestream and others tables with that column to the other filestream.How do i can to know which parent filestream a table?Thanks.

Would Always-On Groups be a decent solution for this?

Posted: 17 Sep 2013 11:59 PM PDT

So, my employer (may) be frowning on after-hours work (even though it's a *bad* thing to take down a production SQL during the day to reboot from OS updates that they require to be installed by a certain date) and I'm looking to get some options lined up to resolve this.While a SQL cluster would be a solution, they started migrating to a VMWare environment before I started, and just recently decomissioned the last physical SQL servers (in a cluster no less) a couple months ago.So, I thought of AAGs. It sounds like this would let me do the OS updates (which on my SQL servers I am responsible for) by the following method:1. Update the replica (which would not be configured for read-only access)2. Reboot the replica3. Wait for the DBs to re-synch4. Manually (planned) failover the AAG DBs to the replica, making it the Primary5. Repeat steps 1-4, replacing "replica" with "primary" (although technically the former primary would now be the replica because of step 4)6. Go home at my normally scheduled time, and only connect in to work if something breaks...I've set up a very basic AAG at home in a VM environment without to much hassle, so that part I think I could manage.I'm also presuming that any SQL Agent jobs would need to be configured on *both* hosts, as you can't make system DBs part of an AAG (one downside compared to a cluster, at least.)Am I on the right track? Or at least a reasonable one, if this becomes a requirement?Thanks,Jason

Printed Books Vs E-Books

Posted: 17 Sep 2013 10:43 PM PDT

;-)

SSRS reportsolution file

Posted: 17 Sep 2013 09:50 PM PDT

Hi,I have a report URL and I can see some of the reports on the URL. But where can I see the visual studio solution file to edit the report. Is there a way/option to find the solution file part from the report URL?Thanks.

Sql Server Authentication

Posted: 17 Sep 2013 04:29 PM PDT

Please help me, what are the system procedures and system functions that are used for validating whether a user is a valid user or not when we click on "connect" button for sql server 2008.

SSAS Forum

Posted: 17 Sep 2013 06:14 PM PDT

All,My first question is that whether this is right forum for Analysis services posts because I have posted my question relating to only T-SQL and Administration. If not Please suggest me a better forum for that.

HELP! Service Broker is dropping messages

Posted: 17 Sep 2013 09:42 AM PDT

I've set up a simple service broker implementation where I have a sender queue on one database and a reciever queue on another database. I've noticed that randomly, about 50% of the messages are dropped and do not show up in either the sender or reciever queues, even after waiting several minutes. To test this, I run a query that puts four messages in the queue, then shows the contents of the sender and receiver queues. After sending four messages, I never see anything in the sender queue, but I'll see anwhere from 1-4 messages in the receiver queue and there is no pattern to what messages are getting dropped. I tried tracing all of the broker messages and don't see any indication of errors. Instead, I see this pattern of traces show up for all four messages, regardless of whether they are dropped or not: STARTED_OUTBOUND CONVERSING STARTED_INBOUND CONVERSING DISCONNECTED_OUTBOUND DISCONNECTED_INBOUND CLOSEDI am running out of ideas of how to get brokering to work right. Any advice?-eHere's my test script:[code]Use SenderDatabaseEXEC [MySignaling].[SendSignal] '<Foo>A1</Foo>'EXEC [MySignaling].[SendSignal] '<Foo>B1</Foo>'EXEC [MySignaling].[SendSignal] '<Foo>C1</Foo>'EXEC [MySignaling].[SendSignal] '<Foo>D1</Foo>'SELECT * From MySignaling.SignalDefaultSenderQueueUse ReceiverDatabaseSELECT * From MySignaling.SignalReceiverQueue[/code] Here is the SendSignal SP:[code]CREATE PROCEDURE [MySignaling].[SendSignal] ( @signal XML, @senderService SYSNAME = 'MySignaling_SignalDefaultSenderService' ) AS DECLARE @SBDialog UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @SBDialog FROM SERVICE @senderService TO SERVICE 'MySignaling_SignalReceiverService' ON CONTRACT [MySignaling_SignalContract] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @SBDialog MESSAGE TYPE [MySignaling_Signal] (@signal); END CONVERSATION @SBDialog; RETURN;[/code]And here's the setup code for the two databases:[code]ALTER DATABASE ReceiverDatabase SET ENABLE_BROKERGoALTER DATABASE ReceiverDatabase SET TRUSTWORTHY ONGoALTER DATABASE SenderDatabase SET ENABLE_BROKERGoALTER DATABASE SenderDatabase SET TRUSTWORTHY ONGo----------------------------------------------------- Set up Receiver---------------------------------------------------USE ReceiverDatabaseGoCREATE SCHEMA [MySignaling];GoCREATE MESSAGE TYPE [MySignaling_Signal] VALIDATION = WELL_FORMED_XML;GOCREATE CONTRACT [MySignaling_SignalContract] ([MySignaling_Signal] SENT BY INITIATOR);GOCREATE QUEUE [MySignaling].[SignalReceiverQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = OFF);GOCREATE SERVICE [MySignaling_SignalReceiverService] ON QUEUE [MySignaling].[SignalReceiverQueue] ([MySignaling_SignalContract]);Go----------------------------------------------------- Set up Sender---------------------------------------------------USE SenderDatabaseGoCREATE SCHEMA [MySignaling];GoCREATE MESSAGE TYPE [MySignaling_Signal] VALIDATION = WELL_FORMED_XML;GOCREATE CONTRACT [MySignaling_SignalContract] ([MySignaling_Signal] SENT BY INITIATOR);GOCREATE QUEUE [MySignaling].[SignalDefaultSenderQueue] WITH STATUS = ON , RETENTION = OFF , POISON_MESSAGE_HANDLING (STATUS = OFF);GOCREATE SERVICE [MySignaling_SignalDefaultSenderService] ON QUEUE [MySignaling].[SignalDefaultSenderQueue] ([MySignaling_SignalContract]);GOCREATE PROCEDURE [MySignaling].[SendSignal] ( @signal XML, @senderService SYSNAME = 'MySignaling_SignalDefaultSenderService' ) AS DECLARE @SBDialog UNIQUEIDENTIFIER; BEGIN DIALOG CONVERSATION @SBDialog FROM SERVICE @senderService TO SERVICE 'MySignaling_SignalReceiverService' ON CONTRACT [MySignaling_SignalContract] WITH ENCRYPTION = OFF; SEND ON CONVERSATION @SBDialog MESSAGE TYPE [MySignaling_Signal] (@signal); END CONVERSATION @SBDialog; RETURN;[/code]

HELP - MDX QUERY - EXCEPTION or MINUS

Posted: 17 Sep 2013 08:36 AM PDT

HY guys, my second time here!I'm work with MDX query in my job and i need a little help please.I need to use the hierarchy [CFOP]. [FINANCIAL OPERATION]be removed from the items below in the query in the end of this post: [CFOP].[OPERAÇÃO FINANCEIRA].&[7105], [CFOP].[OPERAÇÃO FINANCEIRA].&[511], [CFOP].[OPERAÇÃO FINANCEIRA].&[611], [CFOP].[OPERAÇÃO FINANCEIRA].&[512]I can not select all the items they are more than 5000 and makes difficult understanding of query and the same will be used in a procedure.Is there any way? found minus and except it did not work for meI appreciate if someone can help.QUERY:SELECT { [Measures].[REC LIQ FRETE], [Measures].[DESP FRETE], [Measures].[REC LIQ PROD] } ON COLUMNS, { ([LOJA].[ORGANIZAÇÃO].[MARCA].ALLMEMBERS * [ITEM].[ESTRUTURA ITEM].[DEPARTAMENTO].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [CFOP].[OPERAÇÃO FINANCEIRA].[All] } ) ON COLUMNS FROM ( SELECT ( { [GERENCIA].[ESTRUTURA GERENCIAL].[All] } ) ON COLUMNS FROM ( SELECT ( { [TIPO_DOCUMENTO].[TIPO DOCUMENTO].&[8], [TIPO_DOCUMENTO].[TIPO DOCUMENTO].&[9], [TIPO_DOCUMENTO].[TIPO DOCUMENTO].&[2], [TIPO_DOCUMENTO].[TIPO DOCUMENTO].&[1], [TIPO_DOCUMENTO].[TIPO DOCUMENTO].&[7], [TIPO_DOCUMENTO].[TIPO DOCUMENTO].&[6], [TIPO_DOCUMENTO].[TIPO DOCUMENTO].&[-1], [TIPO_DOCUMENTO].[TIPO DOCUMENTO].&[5] } ) ON COLUMNS FROM ( SELECT ( { [ORGANIZACAO_CD_AF].[ORGANIZAÇÃO CD].[All] } ) ON COLUMNS FROM ( SELECT ( { [MOVIMENTO].[MOVIMENTO].&[2] } ) ON COLUMNS FROM ( SELECT ( { [STATUS_FATURAMENTO].[STATUS FATURAMENTO].[STATUS].&[ATIVO] } ) ON COLUMNS FROM ( SELECT ( { [LOJA].[ORGANIZAÇÃO].[MARCA].&[1], [LOJA].[ORGANIZAÇÃO].[MARCA].&[2], [LOJA].[ORGANIZAÇÃO].[MARCA].&[3], [LOJA].[ORGANIZAÇÃO].[MARCA].&[7], [LOJA].[ORGANIZAÇÃO].[UNIDADE DE NEGÓCIO].&[26], [LOJA].[ORGANIZAÇÃO].[UNIDADE DE NEGÓCIO].&[28], [LOJA].[ORGANIZAÇÃO].[UNIDADE DE NEGÓCIO].&[34], [LOJA].[ORGANIZAÇÃO].[UNIDADE DE NEGÓCIO].&[22], [LOJA].[ORGANIZAÇÃO].[UNIDADE DE NEGÓCIO].&[29], [LOJA].[ORGANIZAÇÃO].[UNIDADE DE NEGÓCIO].&[35], [LOJA].[ORGANIZAÇÃO].[UNIDADE DE NEGÓCIO].&[43] } ) ON COLUMNS FROM ( SELECT ( { [DATA_CONTABIL].[DATA CONTÁBIL].[MÊS].&[2013]&[1] } ) ON COLUMNS FROM [ANALISE_FATURAMENTO_SB])))))))) WHERE ( [DATA_CONTABIL].[DATA CONTÁBIL].[MÊS].&[2013]&[1], [STATUS_FATURAMENTO].[STATUS FATURAMENTO].[STATUS].&[ATIVO], [MOVIMENTO].[MOVIMENTO].&[2], [ORGANIZACAO_CD_AF].[ORGANIZAÇÃO CD].[All], [TIPO_DOCUMENTO].[TIPO DOCUMENTO].CurrentMember, [GERENCIA].[ESTRUTURA GERENCIAL].[All], [CFOP].[OPERAÇÃO FINANCEIRA].[All] ) THNK YOU

Passing parameters From One Project To Another Project In SSIS 2012

Posted: 17 Sep 2013 05:41 AM PDT

I am Using SSIS 2012 & Created 2 Projects A & BProject A Master_Package (Includes Project A/Packages 1,2,3) Package1 Package2 Package3 Project B Master_Package (Includes Project B/Packages 1,2,3) Package1 Package2 Package3 I Am Calling Project B (Master_Package) From Project A (Master_Package) Using (EXEC [SSISDB].[catalog].[start_execution])But i Also Want Pass Some Values From Project A (Master_Package) To Project B (Master_Package)..... How Can i Do this ?
Posted by at No comments:

[T-SQL] Return text from a PDF stored in the database (Adobe iFilter)

[T-SQL] Return text from a PDF stored in the database (Adobe iFilter)

Return text from a PDF stored in the database (Adobe iFilter)

Posted: 18 Sep 2013 01:03 AM PDT

We are storing PDF files inside a SQL Server 2008R2 DB. We have installed the Adobe iFilter to create a full-text catalog in order to search these files. Everything was working great, until.... we tried to get the text out of that PDF for display on a website. I am at a loss. We want to be able to return the text of a PDF file as varchar(max) using just straight-up T-SQL. I assume we would need to create a function and *somehow* use the iFilter to pull out the text, but I cannot find any documentation on how to do such a thing. I have searched the WWW for hours and found nothing. Has anyone done this? or.. Does anyone have a link to some documentation that can show me how to do it?Thanks,Murphy

RaiseError in SP

Posted: 17 Sep 2013 05:27 PM PDT

Hi All, For error handling mechanism in SP, we can use the RAISEERROR method in the catch block of the SP to throw the system defined/custom error to the calling method and rollback the transaction. My question is that whether we have to raise the error first and then do the rollback or first rollback the transaction and then raise the error. I think the order is not going to impact anything. Am i correct? Sample Code isBEGIN CATCH DECLARE @ErrorMessage NVARCHAR(4000) DECLARE @ErrorSeverity INT DECLARE @ErrorState INT SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE() RAISERROR (@ErrorMessage, -- Message text. @ErrorSeverity, -- Severity. @ErrorState -- State. ) ROLLBACK TRAN T1END CATCH

Using SQLCMD as an alternative to xp_cmdshell

Posted: 12 Sep 2011 08:42 AM PDT

Hi,I have a development question that i would like to ask the group.I am working on building a stored procedure that would do a simple add linked server operation but with a few security concerns built into it.The stored procedure will be given the name of the linked server and the linked server properties and the user id to use for connecting to the linked server.But the password for the user account is stored in another server which can be retrieved from executing an operating system command from the command prompt.I know that I can use the xp_cmdshell extended stored procedure from within my stored procedure to execute the same command and get the password.But, the catch is, our company standards restrict us from directly using the xp_cmdshell from within the queries.I know that i can use the SQLCMD to execute operating system commands by indicating the " !! " before the command.My question is how do i invoke the SQLCMD from within my stored procedure, execute the operating system command using the sqlcmd, get back the password, store it in a variable and then continue with adding the linked server.Ultimately, this stored procedure will be replacing a legacy script that the application has been using.Suggestion would be much appreciated.Thanks-John

performing insert statement in a linked server

Posted: 17 Sep 2013 07:02 AM PDT

Hello guys, i need so much your help!I was doing an insert statement with union all using a linked server.I have two dabases in two different servers. These databases are connected through a linked server.When i try to do an insert statement with union all from a database to another, using linked server, i receive the following message: "Msg 8624, Level 16, State 17, Line 1Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."Someone could help me?!?!I'm sorry for the language, I'm brazilian!!

Sql Server Authentication

Posted: 17 Sep 2013 04:28 PM PDT

Please help me, what are the system procedures and system functions that are used for validating whether a user is a valid user or not when we click on "connect" button for sql server 2008.

Intelligently Flatten Ragged Hierachy to Fixed Levels for SSAS

Posted: 17 Sep 2013 05:41 AM PDT

We just upgraded our organization from SQL 2000 to SQL 2008/2012 (must say I've been loving the CTE's, Agg functions, and APPLY!). Now I am upgrading the SSAS and I want to give my accounting / analyst group the ability to create/modify their different GL account structures without relying on me. So they have a GUI tool where they can drag and drop their accounts and make their ragged (parent/child) chart of accounts that I want to flow into the OLAP cubes ... but of course according to multiple sources I should not use parent/child hierarchies, but convert them into fixed levels. The accountants like this too ... they want to be able to drill to a specific level.So the GUI tool creates a table (with a little love from me) like this (although there is a TreeID as well that I have left out for simplicity) which matches up with my ERP data:[code="sql"]CREATE TABLE [tmpAcctTree]( [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [AcctID] [numeric](18, 0) NULL, [iLevel] [numeric](18, 0) NULL, [iOrder] [numeric](18, 0) NULL, [ParentID] [numeric](18, 0) NULL, [IsLeaf] [numeric](18, 0) NULL )[/code]This will add a fairly simple tree (is this too much to paste into a post?):[code="sql"]INSERT INTO [tmpAcctTree] ([AcctID] ,[iLevel] ,[iOrder] ,[ParentID] ,[IsLeaf])SELECT '692699', '0', '1', '0', '0' UNION ALLSELECT '692695', '1', '1', '692699', '0' UNION ALLSELECT '692698', '1', '2', '692699', '0' UNION ALLSELECT '692616', '2', '1', '692695', '0' UNION ALLSELECT '692694', '2', '2', '692695', '0' UNION ALLSELECT '692697', '2', '1', '692698', '0' UNION ALLSELECT '692598', '3', '1', '692616', '0' UNION ALLSELECT '692606', '3', '2', '692616', '0' UNION ALLSELECT '692615', '3', '3', '692616', '0' UNION ALLSELECT '692651', '3', '1', '692694', '0' UNION ALLSELECT '692655', '3', '2', '692694', '0' UNION ALLSELECT '692660', '3', '3', '692694', '0' UNION ALLSELECT '692665', '3', '4', '692694', '0' UNION ALLSELECT '692669', '3', '5', '692694', '0' UNION ALLSELECT '692675', '3', '6', '692694', '0' UNION ALLSELECT '692683', '3', '7', '692694', '0' UNION ALLSELECT '692690', '3', '8', '692694', '0' UNION ALLSELECT '692693', '3', '9', '692694', '0' UNION ALLSELECT '692596', '4', '1', '692598', '0' UNION ALLSELECT '692597', '4', '2', '692598', '0' UNION ALLSELECT '692600', '4', '1', '692606', '0' UNION ALLSELECT '692601', '4', '2', '692606', '0' UNION ALLSELECT '692602', '4', '3', '692606', '0' UNION ALLSELECT '692603', '4', '4', '692606', '0' UNION ALLSELECT '692604', '4', '5', '692606', '0' UNION ALLSELECT '692605', '4', '6', '692606', '0' UNION ALLSELECT '692612', '4', '1', '692615', '0' UNION ALLSELECT '692613', '4', '2', '692615', '0' UNION ALLSELECT '692614', '4', '3', '692615', '0' UNION ALLSELECT '692619', '4', '1', '692651', '0' UNION ALLSELECT '692620', '4', '2', '692651', '0' UNION ALLSELECT '692623', '4', '3', '692651', '0' UNION ALLSELECT '692624', '4', '4', '692651', '0' UNION ALLSELECT '692631', '4', '5', '692651', '0' UNION ALLSELECT '692632', '4', '6', '692651', '0' UNION ALLSELECT '692633', '4', '7', '692651', '0' UNION ALLSELECT '692634', '4', '8', '692651', '0' UNION ALLSELECT '692635', '4', '9', '692651', '0' UNION ALLSELECT '692645', '4', '10', '692651', '0' UNION ALLSELECT '692646', '4', '11', '692651', '0' UNION ALLSELECT '692647', '4', '12', '692651', '0' UNION ALLSELECT '692648', '4', '13', '692651', '0' UNION ALLSELECT '692649', '4', '14', '692651', '0' UNION ALLSELECT '692650', '4', '15', '692651', '0' UNION ALLSELECT '692653', '4', '1', '692655', '0' UNION ALLSELECT '692654', '4', '2', '692655', '0' UNION ALLSELECT '692657', '4', '1', '692660', '0' UNION ALLSELECT '692658', '4', '2', '692660', '0' UNION ALLSELECT '692659', '4', '3', '692660', '0' UNION ALLSELECT '692662', '4', '1', '692665', '0' UNION ALLSELECT '692663', '4', '2', '692665', '0' UNION ALLSELECT '692664', '4', '3', '692665', '0' UNION ALLSELECT '692667', '4', '1', '692669', '0' UNION ALLSELECT '692668', '4', '2', '692669', '0' UNION ALLSELECT '692671', '4', '1', '692675', '0' UNION ALLSELECT '692672', '4', '2', '692675', '0' UNION ALLSELECT '692673', '4', '3', '692675', '0' UNION ALLSELECT '692674', '4', '4', '692675', '0' UNION ALLSELECT '692677', '4', '1', '692683', '0' UNION ALLSELECT '692678', '4', '2', '692683', '0' UNION ALLSELECT '692679', '4', '3', '692683', '0' UNION ALLSELECT '692680', '4', '4', '692683', '0' UNION ALLSELECT '692681', '4', '5', '692683', '0' UNION ALLSELECT '692682', '4', '6', '692683', '0' UNION ALLSELECT '692685', '4', '1', '692690', '0' UNION ALLSELECT '692686', '4', '2', '692690', '0' UNION ALLSELECT '692687', '4', '3', '692690', '0' UNION ALLSELECT '692688', '4', '4', '692690', '0' UNION ALLSELECT '692689', '4', '5', '692690', '0' UNION ALLSELECT '692692', '4', '1', '692693', '0' UNION ALLSELECT '692609', '5', '1', '692612', '0' UNION ALLSELECT '692610', '5', '2', '692612', '0' UNION ALLSELECT '692611', '5', '3', '692612', '0' UNION ALLSELECT '692622', '5', '1', '692623', '0' UNION ALLSELECT '692626', '5', '1', '692631', '0' UNION ALLSELECT '692627', '5', '2', '692631', '0' UNION ALLSELECT '692628', '5', '3', '692631', '0' UNION ALLSELECT '692629', '5', '4', '692631', '0' UNION ALLSELECT '692630', '5', '5', '692631', '0' UNION ALLSELECT '692637', '5', '1', '692645', '0' UNION ALLSELECT '692638', '5', '2', '692645', '0' UNION ALLSELECT '692639', '5', '3', '692645', '0' UNION ALLSELECT '692640', '5', '4', '692645', '0' UNION ALLSELECT '692641', '5', '5', '692645', '0' UNION ALLSELECT '692642', '5', '6', '692645', '0' UNION ALLSELECT '692643', '5', '7', '692645', '0' UNION ALLSELECT '692644', '5', '8', '692645', '0' UNION ALLSELECT '1110', '5', '1', '692596', '-1' UNION ALLSELECT '1111', '5', '2', '692596', '-1' UNION ALLSELECT '1112', '5', '3', '692596', '-1' UNION ALLSELECT '1113', '5', '4', '692596', '-1' UNION ALLSELECT '1114', '5', '5', '692596', '-1' UNION ALLSELECT '1115', '5', '6', '692596', '-1' UNION ALLSELECT '1119', '5', '7', '692596', '-1' UNION ALLSELECT '1121', '5', '8', '692596', '-1' UNION ALLSELECT '1630', '5', '9', '692596', '-1' UNION ALLSELECT '1847', '5', '10', '692596', '-1' UNION ALLSELECT '1903', '5', '11', '692596', '-1' UNION ALLSELECT '2004', '5', '12', '692596', '-1' UNION ALLSELECT '2161', '5', '13', '692596', '-1' UNION ALLSELECT '2181', '5', '14', '692596', '-1' UNION ALLSELECT '2197', '5', '15', '692596', '-1' UNION ALLSELECT '1116', '5', '1', '692597', '-1' UNION ALLSELECT '1652', '5', '2', '692597', '-1' UNION ALLSELECT '1713', '5', '3', '692597', '-1' UNION ALLSELECT '1777', '5', '4', '692597', '-1' UNION ALLSELECT '1778', '5', '5', '692597', '-1' UNION ALLSELECT '1779', '5', '6', '692597', '-1' UNION ALLSELECT '1852', '5', '7', '692597', '-1' UNION ALLSELECT '2059', '5', '8', '692597', '-1' UNION ALLSELECT '2060', '5', '9', '692597', '-1' UNION ALLSELECT '2071', '5', '10', '692597', '-1' UNION ALLSELECT '2077', '5', '11', '692597', '-1' UNION ALLSELECT '1130', '5', '1', '692600', '-1' UNION ALLSELECT '1131', '5', '2', '692600', '-1' UNION ALLSELECT '2234', '5', '3', '692600', '-1' UNION ALLSELECT '1132', '5', '1', '692601', '-1' UNION ALLSELECT '1996', '5', '1', '692602', '-1' UNION ALLSELECT '2092', '5', '2', '692602', '-1' UNION ALLSELECT '2099', '5', '3', '692602', '-1' UNION ALLSELECT '2126', '5', '4', '692602', '-1' UNION ALLSELECT '1134', '5', '1', '692603', '-1' UNION ALLSELECT '1140', '5', '2', '692603', '-1' UNION ALLSELECT '1611', '5', '1', '692604', '-1' UNION ALLSELECT '1878', '5', '2', '692604', '-1' UNION ALLSELECT '1986', '5', '3', '692604', '-1' UNION ALLSELECT '1989', '5', '4', '692604', '-1' UNION ALLSELECT '1990', '5', '5', '692604', '-1' UNION ALLSELECT '2222', '5', '6', '692604', '-1' UNION ALLSELECT '1129', '5', '1', '692605', '-1' UNION ALLSELECT '1137', '5', '2', '692605', '-1' UNION ALLSELECT '1143', '5', '3', '692605', '-1' UNION ALLSELECT '2199', '5', '4', '692605', '-1' UNION ALLSELECT '2200', '5', '5', '692605', '-1' UNION ALLSELECT '2201', '5', '6', '692605', '-1' UNION ALLSELECT '2205', '5', '7', '692605', '-1' UNION ALLSELECT '2206', '5', '8', '692605', '-1' UNION ALLSELECT '2224', '5', '9', '692605', '-1' UNION ALLSELECT '2225', '5', '10', '692605', '-1' UNION ALLSELECT '1158', '6', '1', '692609', '-1' UNION ALLSELECT '1159', '6', '2', '692609', '-1' UNION ALLSELECT '1160', '6', '3', '692609', '-1' UNION ALLSELECT '1161', '6', '4', '692609', '-1' UNION ALLSELECT '1162', '6', '5', '692609', '-1' UNION ALLSELECT '1165', '6', '6', '692609', '-1' UNION ALLSELECT '1166', '6', '7', '692609', '-1' UNION ALLSELECT '1167', '6', '8', '692609', '-1' UNION ALLSELECT '1170', '6', '9', '692609', '-1' UNION ALLSELECT '1774', '6', '10', '692609', '-1' UNION ALLSELECT '1853', '6', '11', '692609', '-1' UNION ALLSELECT '1899', '6', '12', '692609', '-1' UNION ALLSELECT '1931', '6', '13', '692609', '-1' UNION ALLSELECT '2015', '6', '14', '692609', '-1' UNION ALLSELECT '2016', '6', '15', '692609', '-1' UNION ALLSELECT '2024', '6', '16', '692609', '-1' UNION ALLSELECT '2064', '6', '17', '692609', '-1' UNION ALLSELECT '2065', '6', '18', '692609', '-1' UNION ALLSELECT '2066', '6', '19', '692609', '-1' UNION ALLSELECT '2067', '6', '20', '692609', '-1' UNION ALLSELECT '2089', '6', '21', '692609', '-1' UNION ALLSELECT '2195', '6', '22', '692609', '-1' UNION ALLSELECT '2196', '6', '23', '692609', '-1' UNION ALLSELECT '2228', '6', '24', '692609', '-1' UNION ALLSELECT '1163', '6', '1', '692610', '-1' UNION ALLSELECT '1164', '6', '2', '692610', '-1' UNION ALLSELECT '1169', '6', '3', '692610', '-1' UNION ALLSELECT '1171', '6', '4', '692610', '-1' UNION ALLSELECT '1176', '6', '5', '692610', '-1' UNION ALLSELECT '1568', '6', '6', '692610', '-1' UNION ALLSELECT '1591', '6', '7', '692610', '-1' UNION ALLSELECT '2093', '6', '8', '692610', '-1' UNION ALLSELECT '2094', '6', '9', '692610', '-1' UNION ALLSELECT '2095', '6', '10', '692610', '-1' UNION ALLSELECT '2096', '6', '11', '692610', '-1' UNION ALLSELECT '2097', '6', '12', '692610', '-1' UNION ALLSELECT '2098', '6', '13', '692610', '-1' UNION ALLSELECT '2155', '6', '14', '692610', '-1' UNION ALLSELECT '2163', '6', '15', '692610', '-1' UNION ALLSELECT '2170', '6', '16', '692610', '-1' UNION ALLSELECT '2232', '6', '17', '692610', '-1' UNION ALLSELECT '1157', '6', '1', '692611', '-1' UNION ALLSELECT '1922', '6', '2', '692611', '-1' UNION ALLSELECT '2176', '5', '1', '692613', '-1' UNION ALLSELECT '2177', '5', '2', '692613', '-1' UNION ALLSELECT '2182', '5', '3', '692613', '-1' UNION ALLSELECT '2183', '5', '4', '692613', '-1' UNION ALLSELECT '2187', '5', '5', '692613', '-1' UNION ALLSELECT '2188', '5', '6', '692613', '-1' UNION ALLSELECT '2190', '5', '7', '692613', '-1' UNION ALLSELECT '2138', '5', '1', '692614', '-1' UNION ALLSELECT '2141', '5', '2', '692614', '-1' UNION ALLSELECT '2239', '5', '3', '692614', '-1' UNION ALLSELECT '2090', '5', '1', '692619', '-1' UNION ALLSELECT '2204', '5', '2', '692619', '-1' UNION ALLSELECT '1366', '5', '1', '692620', '-1' UNION ALLSELECT '1461', '5', '2', '692620', '-1' UNION ALLSELECT '1462', '5', '3', '692620', '-1' UNION ALLSELECT '1463', '5', '4', '692620', '-1' UNION ALLSELECT '1466', '5', '5', '692620', '-1' UNION ALLSELECT '1467', '5', '6', '692620', '-1' UNION ALLSELECT '1468', '5', '7', '692620', '-1' UNION ALLSELECT '1469', '5', '8', '692620', '-1' UNION ALLSELECT '1470', '5', '9', '692620', '-1' UNION ALLSELECT '1471', '5', '10', '692620', '-1' UNION ALLSELECT '1472', '5', '11', '692620', '-1' UNION ALLSELECT '1609', '5', '12', '692620', '-1' UNION ALLSELECT '1680', '5', '13', '692620', '-1' UNION ALLSELECT '2006', '5', '14', '692620', '-1' UNION ALLSELECT '2198', '5', '15', '692620', '-1' UNION ALLSELECT '2229', '5', '16', '692620', '-1' UNION ALLSELECT '2235', '5', '17', '692620', '-1' UNION ALLSELECT '1180', '5', '1', '692624', '-1' UNION ALLSELECT '1186', '5', '2', '692624', '-1' UNION ALLSELECT '1288', '5', '3', '692624', '-1' UNION ALLSELECT '1557', '5', '4', '692624', '-1' UNION ALLSELECT '1562', '5', '5', '692624', '-1' UNION ALLSELECT '1674', '5', '6', '692624', '-1' UNION ALLSELECT '1708', '5', '7', '692624', '-1' UNION ALLSELECT '1952', '5', '8', '692624', '-1' UNION ALLSELECT '1953', '5', '9', '692624', '-1' UNION ALLSELECT '1954', '5', '10', '692624', '-1' UNION ALLSELECT '1963', '5', '11', '692624', '-1' UNION ALLSELECT '1978', '5', '12', '692624', '-1' UNION ALLSELECT '1979', '5', '13', '692624', '-1' UNION ALLSELECT '1980', '5', '14', '692624', '-1' UNION ALLSELECT '1981', '5', '15', '692624', '-1' UNION ALLSELECT '1982', '5', '16', '692624', '-1' UNION ALLSELECT '1983', '5', '17', '692624', '-1' UNION ALLSELECT '2043', '5', '18', '692624', '-1' UNION ALLSELECT '2044', '5', '19', '692624', '-1' UNION ALLSELECT '2149', '5', '20', '692624', '-1' UNION ALLSELECT '2166', '5', '21', '692624', '-1' UNION ALLSELECT '2244', '5', '22', '692624', '-1' UNION ALLSELECT '1296', '6', '1', '692626', '-1' UNION ALLSELECT '1297', '6', '2', '692626', '-1' UNION ALLSELECT '1298', '6', '3', '692626', '-1' UNION ALLSELECT '1299', '6', '4', '692626', '-1' UNION ALLSELECT '1300', '6', '5', '692626', '-1' UNION ALLSELECT '1301', '6', '6', '692626', '-1' UNION ALLSELECT '1308', '6', '7', '692626', '-1' UNION ALLSELECT '1309', '6', '8', '692626', '-1' UNION ALLSELECT '1310', '6', '9', '692626', '-1' UNION ALLSELECT '1311', '6', '10', '692626', '-1' UNION ALLSELECT '1312', '6', '11', '692626', '-1' UNION ALLSELECT '1313', '6', '12', '692626', '-1' UNION ALLSELECT '1314', '6', '13', '692626', '-1' UNION ALLSELECT '1315', '6', '14', '692626', '-1' UNION ALLSELECT '1318', '6', '15', '692626', '-1' UNION ALLSELECT '1319', '6', '16', '692626', '-1' UNION ALLSELECT '1320', '6', '17', '692626', '-1' UNION ALLSELECT '1322', '6', '18', '692626', '-1' UNION ALLSELECT '1323', '6', '19', '692626', '-1' UNION ALLSELECT '1324', '6', '20', '692626', '-1' UNION ALLSELECT '1325', '6', '21', '692626', '-1' UNION ALLSELECT '1327', '6', '22', '692626', '-1' UNION ALLSELECT '1328', '6', '23', '692626', '-1' UNION ALLSELECT '1329', '6', '24', '692626', '-1' UNION ALLSELECT '1330', '6', '25', '692626', '-1' UNION ALLSELECT '1331', '6', '26', '692626', '-1' UNION ALLSELECT '1332', '6', '27', '692626', '-1' UNION ALLSELECT '1333', '6', '28', '692626', '-1' UNION ALLSELECT '1334', '6', '29', '692626', '-1' UNION ALLSELECT '1335', '6', '30', '692626', '-1' UNION ALLSELECT '1336', '6', '31', '692626', '-1' UNION ALLSELECT '1337', '6', '32', '692626', '-1' UNION ALLSELECT '1338', '6', '33', '692626', '-1' UNION ALLSELECT '1571', '6', '34', '692626', '-1' UNION ALLSELECT '1601', '6', '35', '692626', '-1' UNION ALLSELECT '1602', '6', '36', '692626', '-1' UNION ALLSELECT '1632', '6', '37', '692626', '-1' UNION ALLSELECT '1650', '6', '38', '692626', '-1' UNION ALLSELECT '1662', '6', '39', '692626', '-1' UNION ALLSELECT '1667', '6', '40', '692626', '-1' UNION ALLSELECT '1669', '6', '41', '692626', '-1' UNION ALLSELECT '1697', '6', '42', '692626', '-1' UNION ALLSELECT '1781', '6', '43', '692626', '-1' UNION ALLSELECT '1782', '6', '44', '692626', '-1' UNION ALLSELECT '1783', '6', '45', '692626', '-1' UNION ALLSELECT '1858', '6', '46', '692626', '-1' UNION ALLSELECT '1879', '6', '47', '692626', '-1' UNION ALLSELECT '1958', '6', '48', '692626', '-1' UNION ALLSELECT '2003', '6', '49', '692626', '-1' UNION ALLSELECT '2014', '6', '50', '692626', '-1' UNION ALLSELECT '2160', '6', '51', '692626', '-1' UNION ALLSELECT '2171', '6', '52', '692626', '-1' UNION ALLSELECT '2173', '6', '53', '692626', '-1' UNION ALLSELECT '2208', '6', '54', '692626', '-1' UNION ALLSELECT '2246', '6', '55', '692626', '-1' UNION ALLSELECT '1294', '6', '1', '692627', '-1' UNION ALLSELECT '1295', '6', '2', '692627', '-1' UNION ALLSELECT '1316', '6', '1', '692628', '-1' UNION ALLSELECT '1317', '6', '2', '692628', '-1' UNION ALLSELECT '1326', '6', '3', '692628', '-1' UNION ALLSELECT '1862', '6', '4', '692628', '-1' UNION ALLSELECT '1863', '6', '5', '692628', '-1' UNION ALLSELECT '1864', '6', '6', '692628', '-1' UNION ALLSELECT '1865', '6', '7', '692628', '-1' UNION ALLSELECT '1866', '6', '8', '692628', '-1' UNION ALLSELECT '1867', '6', '9', '692628', '-1' UNION ALLSELECT '1868', '6', '10', '692628', '-1' UNION ALLSELECT '1869', '6', '11', '692628', '-1' UNION ALLSELECT '1302', '6', '1', '692629', '-1' UNION ALLSELECT '1780', '6', '2', '692629', '-1' UNION ALLSELECT '1789', '6', '3', '692629', '-1' UNION ALLSELECT '1801', '6', '1', '692630', '-1' UNION ALLSELECT '1803', '6', '2', '692630', '-1' UNION ALLSELECT '1804', '6', '3', '692630', '-1' UNION ALLSELECT '1805', '6', '4', '692630', '-1' UNION ALLSELECT '1806', '6', '5', '692630', '-1' UNION ALLSELECT '1807', '6', '6', '692630', '-1' UNION ALLSELECT '1808', '6', '7', '692630', '-1' UNION ALLSELECT '1321', '5', '1', '692632', '-1' UNION ALLSELECT '1202', '5', '1', '692633', '-1' UNION ALLSELECT '1208', '5', '2', '692633', '-1' UNION ALLSELECT '1558', '5', '3', '692633', '-1' UNION ALLSELECT '1563', '5', '4', '692633', '-1' UNION ALLSELECT '1670', '5', '5', '692633', '-1' UNION ALLSELECT '1709', '5', '6', '692633', '-1' UNION ALLSELECT '2045', '5', '7', '692633', '-1' UNION ALLSELECT '2046', '5', '8', '692633', '-1' UNION ALLSELECT '2243', '5', '9', '692633', '-1' UNION ALLSELECT '1572', '5', '1', '692634', '-1' UNION ALLSELECT '1573', '5', '2', '692634', '-1' UNION ALLSELECT '1574', '5', '3', '692634', '-1' UNION ALLSELECT '1575', '5', '4', '692634', '-1' UNION ALLSELECT '1576', '5', '5', '692634', '-1' UNION ALLSELECT '1577', '5', '6', '692634', '-1' UNION ALLSELECT '1578', '5', '7', '692634', '-1' UNION ALLSELECT '1725', '5', '8', '692634', '-1' UNION ALLSELECT '1973', '5', '9', '692634', '-1' UNION ALLSELECT '1994', '5', '10', '692634', '-1' UNION ALLSELECT '1224', '5', '1', '692635', '-1' UNION ALLSELECT '1230', '5', '2', '692635', '-1' UNION ALLSELECT '1246', '5', '3', '692635', '-1' UNION ALLSELECT '1252', '5', '4', '692635', '-1' UNION ALLSELECT '1268', '5', '5', '692635', '-1' UNION ALLSELECT '1274', '5', '6', '692635', '-1' UNION ALLSELECT '1559', '5', '7', '692635', '-1' UNION ALLSELECT '1560', '5', '8', '692635', '-1' UNION ALLSELECT '1561', '5', '9', '692635', '-1' UNION ALLSELECT '1564', '5', '10', '692635', '-1' UNION ALLSELECT '1565', '5', '11', '692635', '-1' UNION ALLSELECT '1566', '5', '12', '692635', '-1' UNION ALLSELECT '1671', '5', '13', '692635', '-1' UNION ALLSELECT '1672', '5', '14', '692635', '-1' UNION ALLSELECT '1673', '5', '15', '692635', '-1' UNION ALLSELECT '1710', '5', '16', '692635', '-1' UNION ALLSELECT '1711', '5', '17', '692635', '-1' UNION ALLSELECT '1712', '5', '18', '692635', '-1' UNION ALLSELECT '2047', '5', '19', '692635', '-1' UNION ALLSELECT '2048', '5', '20', '692635', '-1' UNION ALLSELECT '2049', '5', '21', '692635', '-1' UNION ALLSELECT '2242', '5', '22', '692635', '-1' UNION ALLSELECT '2245', '5', '23', '692635', '-1' UNION ALLSELECT '1410', '6', '1', '692637', '-1' UNION ALLSELECT '1412', '6', '2', '692637', '-1' UNION ALLSELECT '1504', '6', '3', '692637', '-1' UNION ALLSELECT '1506', '6', '4', '692637', '-1' UNION ALLSELECT '1507', '6', '5', '692637', '-1' UNION ALLSELECT '1508', '6', '6', '692637', '-1' UNION ALLSELECT '1509', '6', '7', '692637', '-1' UNION ALLSELECT '1880', '6', '8', '692637', '-1' UNION ALLSELECT '1881', '6', '9', '692637', '-1' UNION ALLSELECT '1882', '6', '10', '692637', '-1' UNION ALLSELECT '1883', '6', '11', '692637', '-1' UNION ALLSELECT '1884', '6', '12', '692637', '-1' UNION ALLSELECT '1885', '6', '13', '692637', '-1' UNION ALLSELECT '1886', '6', '14', '692637', '-1' UNION ALLSELECT '1887', '6', '15', '692637', '-1' UNION ALLSELECT '1888', '6', '16', '692637', '-1' UNION ALLSELECT '1889', '6', '17', '692637', '-1' UNION ALLSELECT '1890', '6', '18', '692637', '-1' UNION ALLSELECT '1891', '6', '19', '692637', '-1' UNION ALLSELECT '1892', '6', '20', '692637', '-1' UNION ALLSELECT '1893', '6', '21', '692637', '-1' UNION ALLSELECT '1934', '6', '22', '692637', '-1' UNION ALLSELECT '1809', '6', '1', '692638', '-1' UNION ALLSELECT '1810', '6', '2', '692638', '-1' UNION ALLSELECT '1811', '6', '3', '692638', '-1' UNION ALLSELECT '1812', '6', '4', '692638', '-1' UNION ALLSELECT '1813', '6', '5', '692638', '-1' UNION ALLSELECT '1814', '6', '6', '692638', '-1' UNION ALLSELECT '1815', '6', '7', '692638', '-1' UNION ALLSELECT '1816', '6', '8', '692638', '-1' UNION ALLSELECT '1738', '6', '1', '692639', '-1' UNION ALLSELECT '1739', '6', '2', '692639', '-1' UNION ALLSELECT '1740', '6', '3', '692639', '-1' UNION ALLSELECT '1741', '6', '4', '692639', '-1' UNION ALLSELECT '1742', '6', '5', '692639', '-1' UNION ALLSELECT '1743', '6', '6', '692639', '-1' UNION ALLSELECT '1744', '6', '7', '692639', '-1' UNION ALLSELECT '1745', '6', '8', '692639', '-1' UNION ALLSELECT '1746', '6', '9', '692639', '-1' UNION ALLSELECT '1747', '6', '10', '692639', '-1' UNION ALLSELECT '1748', '6', '11', '692639', '-1' UNION ALLSELECT '2018', '6', '12', '692639', '-1' UNION ALLSELECT '2019', '6', '13', '692639', '-1' UNION ALLSELECT '1387', '6', '1', '692640', '-1' UNION ALLSELECT '1388', '6', '2', '692640', '-1' UNION ALLSELECT '1389', '6', '3', '692640', '-1' UNION ALLSELECT '1390', '6', '4', '692640', '-1' UNION ALLSELECT '1391', '6', '5', '692640', '-1' UNION ALLSELECT '1392', '6', '6', '692640', '-1' UNION ALLSELECT '1393', '6', '7', '692640', '-1' UNION ALLSELECT '1394', '6', '8', '692640', '-1' UNION ALLSELECT '1395', '6', '9', '692640', '-1' UNION ALLSELECT '1396', '6', '10', '692640', '-1' UNION ALLSELECT '1397', '6', '11', '692640', '-1' UNION ALLSELECT '1398', '6', '12', '692640', '-1' UNION ALLSELECT '1399', '6', '13', '692640', '-1' UNION ALLSELECT '1400', '6', '14', '692640', '-1' UNION ALLSELECT '1401', '6', '15', '692640', '-1' UNION ALLSELECT '1402', '6', '16', '692640', '-1' UNION ALLSELECT '1403', '6', '17', '692640', '-1' UNION ALLSELECT '1404', '6', '18', '692640', '-1' UNION ALLSELECT '1405', '6', '19', '692640', '-1' UNION ALLSELECT '1406', '6', '20', '692640', '-1' UNION ALLSELECT '1407', '6', '21', '692640', '-1' UNION ALLSELECT '1408', '6', '22', '692640', '-1' UNION ALLSELECT '1409', '6', '23', '692640', '-1' UNION ALLSELECT '1592', '6', '24', '692640', '-1' UNION ALLSELECT '1593', '6', '25', '692640', '-1' UNION ALLSELECT '1595', '6', '26', '692640', '-1' UNION ALLSELECT '1596', '6', '27', '692640', '-1' UNION ALLSELECT '1598', '6', '28', '692640', '-1' UNION ALLSELECT '1607', '6', '29', '692640', '-1' UNION ALLSELECT '1755', '6', '30', '692640', '-1' UNION ALLSELECT '1756', '6', '31', '692640', '-1' UNION ALLSELECT '1757', '6', '32', '692640', '-1' UNION ALLSELECT '1790', '6', '33', '692640', '-1' UNION ALLSELECT '1791', '6', '34', '692640', '-1' UNION ALLSELECT '1792', '6', '35', '692640', '-1' UNION ALLSELECT '1861', '6', '36', '692640', '-1' UNION ALLSELECT '1444', '6', '1', '692641', '-1' UNION ALLSELECT '1445', '6', '2', '692641', '-1' UNION ALLSELECT '1446', '6', '3', '692641', '-1' UNION ALLSELECT '1448', '6', '4', '692641', '-1' UNION ALLSELECT '1449', '6', '5', '692641', '-1' UNION ALLSELECT '1371', '6', '1', '692642', '-1' UNION ALLSELECT '1372', '6', '2', '692642', '-1' UNION ALLSELECT '1373', '6', '3', '692642', '-1' UNION ALLSELECT '1374', '6', '4', '692642', '-1' UNION ALLSELECT '1375', '6', '5', '692642', '-1' UNION ALLSELECT '1376', '6', '6', '692642', '-1' UNION ALLSELECT '1377', '6', '7', '692642', '-1' UNION ALLSELECT '1378', '6', '8', '692642', '-1' UNION ALLSELECT '1379', '6', '9', '692642', '-1' UNION ALLSELECT '1380', '6', '10', '692642', '-1' UNION ALLSELECT '1381', '6', '11', '692642', '-1' UNION ALLSELECT '1382', '6', '12', '692642', '-1' UNION ALLSELECT '1383', '6', '13', '692642', '-1' UNION ALLSELECT '1384', '6', '14', '692642', '-1' UNION ALLSELECT '1385', '6', '15', '692642', '-1' UNION ALLSELECT '1629', '6', '16', '692642', '-1' UNION ALLSELECT '1651', '6', '17', '692642', '-1' UNION ALLSELECT '1751', '6', '18', '692642', '-1' UNION ALLSELECT '1752', '6', '19', '692642', '-1' UNION ALLSELECT '1854', '6', '20', '692642', '-1' UNION ALLSELECT '1857', '6', '21', '692642', '-1' UNION ALLSELECT '1965', '6', '22', '692642', '-1' UNION ALLSELECT '1970', '6', '23', '692642', '-1' UNION ALLSELECT '1415', '6', '1', '692643', '-1' UNION ALLSELECT '1416', '6', '2', '692643', '-1' UNION ALLSELECT '1417', '6', '3', '692643', '-1' UNION ALLSELECT '1451', '6', '1', '692644', '-1' UNION ALLSELECT '1452', '6', '2', '692644', '-1' UNION ALLSELECT '1453', '6', '3', '692644', '-1' UNION ALLSELECT '1454', '6', '4', '692644', '-1' UNION ALLSELECT '1455', '6', '5', '692644', '-1' UNION ALLSELECT '1456', '6', '6', '692644', '-1' UNION ALLSELECT '1457', '6', '7', '692644', '-1' UNION ALLSELECT '1458', '6', '8', '692644', '-1' UNION ALLSELECT '1715', '6', '9', '692644', '-1' UNION ALLSELECT '1758', '6', '10', '692644', '-1' UNION ALLSELECT '1759', '6', '11', '692644', '-1' UNION ALLSELECT '1760', '6', '12', '692644', '-1' UNION ALLSELECT '1761', '6', '13', '692644', '-1' UNION ALLSELECT '1762', '6', '14', '692644', '-1' UNION ALLSELECT '1763', '6', '15', '692644', '-1' UNION ALLSELECT '1764', '6', '16', '692644', '-1' UNION ALLSELECT '1765', '6', '17', '692644', '-1' UNION ALLSELECT '1766', '6', '18', '692644', '-1' UNION ALLSELECT '1768', '6', '19', '692644', '-1' UNION ALLSELECT '1769', '6', '20', '692644', '-1' UNION ALLSELECT '1770', '6', '21', '692644', '-1' UNION ALLSELECT '1771', '6', '22', '692644', '-1' UNION ALLSELECT '1772', '6', '23', '692644', '-1' UNION ALLSELECT '1773', '6', '24', '692644', '-1' UNION ALLSELECT '1793', '6', '25', '692644', '-1' UNION ALLSELECT '1794', '6', '26', '692644', '-1' UNION ALLSELECT '1358', '5', '1', '692646', '-1' UNION ALLSELECT '1359', '5', '2', '692646', '-1' UNION ALLSELECT '1360', '5', '3', '692646', '-1' UNION ALLSELECT '1361', '5', '4', '692646', '-1' UNION ALLSELECT '1363', '5', '5', '692646', '-1' UNION ALLSELECT '1364', '5', '6', '692646', '-1' UNION ALLSELECT '1365', '5', '7', '692646', '-1' UNION ALLSELECT '1367', '5', '8', '692646', '-1' UNION ALLSELECT '1368', '5', '9', '692646', '-1' UNION ALLSELECT '1538', '5', '10', '692646', '-1' UNION ALLSELECT '2017', '5', '11', '692646', '-1' UNION ALLSELECT '2133', '5', '1', '692647', '-1' UNION ALLSELECT '2134', '5', '2', '692647', '-1' UNION ALLSELECT '2135', '5', '3', '692647', '-1' UNION ALLSELECT '2142', '5', '4', '692647', '-1' UNION ALLSELECT '2143', '5', '5', '692647', '-1' UNION ALLSELECT '2144', '5', '6', '692647', '-1' UNION ALLSELECT '2147', '5', '7', '692647', '-1' UNION ALLSELECT '2148', '5', '8', '692647', '-1' UNION ALLSELECT '2236', '5', '9', '692647', '-1' UNION ALLSELECT '2240', '5', '10', '692647', '-1' UNION ALLSELECT '1441', '5', '1', '692648', '-1' UNION ALLSELECT '1938', '5', '2', '692648', '-1' UNION ALLSELECT '1939', '5', '3', '692648', '-1' UNION ALLSELECT '1940', '5', '4', '692648', '-1' UNION ALLSELECT '1941', '5', '5', '692648', '-1' UNION ALLSELECT '1942', '5', '6', '692648', '-1' UNION ALLSELECT '1943', '5', '7', '692648', '-1' UNION ALLSELECT '1420', '5', '1', '692649', '-1' UNION ALLSELECT '1421', '5', '2', '692649', '-1' UNION ALLSELECT '1422', '5', '3', '692649', '-1' UNION ALLSELECT '1423', '5', '4', '692649', '-1' UNION ALLSELECT '1424', '5', '5', '692649', '-1' UNION ALLSELECT '1426', '5', '6', '692649', '-1' UNION ALLSELECT '1428', '5', '7', '692649', '-1' UNION ALLSELECT '1429', '5', '8', '692649', '-1' UNION ALLSELECT '1432', '5', '9', '692649', '-1' UNION ALLSELECT '1433', '5', '10', '692649', '-1' UNION ALLSELECT '1435', '5', '11', '692649', '-1' UNION ALLSELECT '1442', '5', '12', '692649', '-1' UNION ALLSELECT '1529', '5', '13', '692649', '-1' UNION ALLSELECT '1530', '5', '14', '692649', '-1' UNION ALLSELECT '1531', '5', '15', '692649', '-1' UNION ALLSELECT '1604', '5', '16', '692649', '-1' UNION ALLSELECT '1610', '5', '17', '692649', '-1' UNION ALLSELECT '1612', '5', '18', '692649', '-1' UNION ALLSELECT '1679', '5', '19', '692649', '-1' UNION ALLSELECT '1838', '5', '20', '692649', '-1' UNION ALLSELECT '1964', '5', '21', '692649', '-1' UNION ALLSELECT '1968', '5', '22', '692649', '-1' UNION ALLSELECT '2080', '5', '23', '692649', '-1' UNION ALLSELECT '1956', '5', '1', '692650', '-1' UNION ALLSELECT '1139', '5', '16', '692653', '-1' UNION ALLSELECT '2193', '5', '92', '692653', '-1' UNION ALLSELECT '2241', '5', '346', '692654', '-1' UNION ALLSELECT '2233', '5', '1', '692657', '-1' UNION ALLSELECT '1872', '5', '1', '692658', '-1' UNION ALLSELECT '2154', '5', '1', '692659', '-1' UNION ALLSELECT '2145', '5', '1', '692667', '-1' UNION ALLSELECT '2184', '5', '1', '692668', '-1' UNION ALLSELECT '1151', '5', '1', '692677', '-1' UNION ALLSELECT '1152', '5', '2', '692677', '-1' UNION ALLSELECT '1153', '5', '3', '692677', '-1' UNION ALLSELECT '1154', '5', '4', '692677', '-1' UNION ALLSELECT '1156', '5', '5', '692677', '-1' UNION ALLSELECT '1714', '5', '6', '692677', '-1' UNION ALLSELECT '1930', '5', '7', '692677', '-1' UNION ALLSELECT '2009', '5', '8', '692677', '-1' UNION ALLSELECT '2083', '5', '9', '692677', '-1' UNION ALLSELECT '2084', '5', '10', '692677', '-1' UNION ALLSELECT '2085', '5', '11', '692677', '-1' UNION ALLSELECT '2086', '5', '12', '692677', '-1' UNION ALLSELECT '2087', '5', '13', '692677', '-1' UNION ALLSELECT '2088', '5', '14', '692677', '-1' UNION ALLSELECT '2167', '5', '15', '692677', '-1' UNION ALLSELECT '2168', '5', '16', '692677', '-1' UNION ALLSELECT '2169', '5', '17', '692677', '-1' UNION ALLSELECT '2227', '5', '18', '692677', '-1' UNION ALLSELECT '1653', '5', '1', '692678', '-1' UNION ALLSELECT '2023', '5', '1', '692679', '-1' UNION ALLSELECT '1496', '5', '1', '692680', '-1' UNION ALLSELECT '1503', '5', '2', '692680', '-1' UNION ALLSELECT '1510', '5', '3', '692680', '-1' UNION ALLSELECT '1438', '5', '1', '692681', '-1' UNION ALLSELECT '1439', '5', '2', '692681', '-1' UNION ALLSELECT '1440', '5', '3', '692681', '-1' UNION ALLSELECT '1473', '5', '4', '692681', '-1' UNION ALLSELECT '1475', '5', '5', '692681', '-1' UNION ALLSELECT '2007', '5', '6', '692681', '-1' UNION ALLSELECT '2172', '5', '7', '692681', '-1' UNION ALLSELECT '2192', '5', '8', '692681', '-1' UNION ALLSELECT '2020', '5', '1', '692682', '-1' UNION ALLSELECT '2022', '5', '2', '692682', '-1' UNION ALLSELECT '1059', '5', '1', '692685', '-1' UNION ALLSELECT '1060', '5', '2', '692685', '-1' UNION ALLSELECT '1061', '5', '3', '692685', '-1' UNION ALLSELECT '1062', '5', '4', '692685', '-1' UNION ALLSELECT '1063', '5', '5', '692685', '-1' UNION ALLSELECT '1064', '5', '6', '692685', '-1' UNION ALLSELECT '1065', '5', '7', '692685', '-1' UNION ALLSELECT '1066', '5', '8', '692685', '-1' UNION ALLSELECT '1067', '5', '9', '692685', '-1' UNION ALLSELECT '1694', '5', '10', '692685', '-1' UNION ALLSELECT '1069', '5', '1', '692686', '-1' UNION ALLSELECT '1070', '5', '2', '692686', '-1' UNION ALLSELECT '1827', '5', '3', '692686', '-1' UNION ALLSELECT '1829', '5', '4', '692686', '-1' UNION ALLSELECT '1999', '5', '5', '692686', '-1' UNION ALLSELECT '2027', '5', '6', '692686', '-1' UNION ALLSELECT '2054', '5', '7', '692686', '-1' UNION ALLSELECT '2156', '5', '8', '692686', '-1' UNION ALLSELECT '2070', '5', '1', '692687', '-1' UNION ALLSELECT '2180', '5', '1', '692688', '-1' UNION ALLSELECT '2186', '5', '2', '692688', '-1' UNION ALLSELECT '2189', '5', '3', '692688', '-1' UNION ALLSELECT '1055', '5', '1', '692689', '-1' UNION ALLSELECT '2185', '5', '2', '692689', '-1' UNION ALLSELECT '1721', '5', '1', '692692', '-1' UNION ALLSELECT '2050', '3', '422', '692697', '-1' UNION ALLSELECT '2063', '3', '426', '692697', '-1' UNION ALLSELECT '2238', '3', '515', '692697', '-1'[/code]You can see that the maximum level is 6, and this can be static ... I can just tell them they can only have 6 levels which is fine. So of course the level 6 can easily be converted into my dimensions through a query similar to this:[code="sql"]/* Level 6 leaf accounts roll straight up the six levels */SELECT t1.AcctID, t1.iOrder, t2.AcctID, t2.iOrder, t3.AcctID, t3.iOrder, t4.AcctID, t4.iOrder, t5.AcctID, t5.iOrder, t6.AcctID, t6.iOrder, t7.AcctID, t7.iOrderFROM tmpAcctTree t1 INNER JOIN tmpAcctTree t2 ON t1.ParentID = t2.AcctID INNER JOIN tmpAcctTree t3 ON t2.ParentID = t3.AcctID INNER JOIN tmpAcctTree t4 ON t3.ParentID = t4.AcctID INNER JOIN tmpAcctTree t5 ON t4.ParentID = t5.AcctID INNER JOIN tmpAcctTree t6 ON t5.ParentID = t6.AcctID INNER JOIN tmpAcctTree t7 ON t6.ParentID = t7.AcctIDWHERE t1.IsLeaf = -1 AND t1.iLevel = 6[/code]The problem arises with leaves at less than the maximum level. They need to be expanded somewhere between the second and the fifth level with a group that rolls into itself ... but of course that group can not exist elsewhere in the dimension at a different level ... or can it?

Finding values with only one occurrence

Posted: 17 Sep 2013 07:04 AM PDT

Hi Everyone I have the following query which I wish to turn into a view. The query uses 3 tables to compare old and new values. SELECT Table1.QM, Table1.GD, Table1.Geometry, Table2.QM AS QM_OLD, Table2.GD AS GD_OLD, Table2.Geometry AS GEOMETRY_OLDFROM Table3 INNER JOIN Table2 ON Table3.GD_OLD = Table2.GD INNER JOIN Table1 ON Table3.GD = Table1.GDhere is some sample outputQM GD Geometry QM_OLD GD_OLD GEOMETRY_OLD024068-002 24068002 Value 037731-003 37731003 Value2055257-004 55257004 Value 006061-001 6061001 Value2055257-005 55257005 Value 006061-001 6061001 Value2055257-006 55257006 Value 006061-001 6061001 Value2055203-004 55203004 Value 034536-003 34536003 Value2055257-003 55257003 Value 053564-005 53564005 Value2what I have been trying to do with out luck is to include only the rows which there is only one value for GD_OLD. As one can see from the above sample output the 2-4 lines all have the same GD_OLD, so I do not to include them in the output. I want the output to look like the following, only including records with unique GD_OLD.QM GD Geometry QM_OLD GD_OLD GEOMETRY_OLD024068-002 24068002 Value 037731-003 37731003 Value2055203-004 55203004 Value 034536-003 34536003 Value2055257-003 55257003 Value 053564-005 53564005 Value2Any help with this would be valued. Thanks.

finding value in a string

Posted: 17 Sep 2013 06:24 AM PDT

I have a text value in a string which I can convert to varchar(1000). field I am going to convert look like this. I need to extract the values between <EntryID> and </EntryID> I was thinking charindex but am stuck<NewOrder><InstID>ED</InstID><PatientID>22164</PatientID><LName>fACKSON</LName><FName>JAMES</FName><EntryID>1923236</EntryID><OrderID></NewOrder>

converting nvarchar to decimal

Posted: 17 Sep 2013 04:06 AM PDT

Hi,i am trying to convert a nvarchar column to a decimal value. the nvarchar column has empty records as well as NULL values. now when i try to convert the values with: select CAST(round(column,2) AS decimal(18,2)) as columnfrom tablenameall goes well, but the empty and NULL values appear as 0.00 value but i want them to stay emptycan anyone help me on this , how to achieve this.regards,Bryan
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog