Recently Indexed Messages:-



DB Engine :: Migrate Table Data When Dealing With Constraints

Nov 19, 2015

I need to script out data in several tables (30+) and then reload those tables on a different database.  The "target" database table data will get overwritten each time, not appended to.  Several of these tables have numerous foreign keys and other constraints that must be dealt with.  None of them have more than 100 records or so.  Then I need to keep this script in source control and reference it as part of a post-deployment step with a Visual Studio DB project.

I have redgate, dbghost and visual studio.  Are there any tools will generate the script for me complete with dropping all the constraints, truncating the tables, then re-adding the constraints back?  Or, am I looking at simply scripting the table data out, then modifying the script myself to add in the steps to drop/readd the constraints?  Further, since some of these tables might have relationships, not just to other tables, but to those in question, there will likely be an order in which the tables need to get loaded.

The approach I am thinking will be needed, since ive done this sort of thing before except with SSIS, is to run something like dbghost or the "generate scripts" option in SSMS as a starting point.  From there, change the order around as needed, etc.  

View 3 Replies

Query List Of All Tables In A Database

Mar 5, 2014

I'm running SQL Server 2012 Express. I need to query a database and return the list of tables in the database. How to construct this query?

View 4 Replies

Reporting Services :: Format Month Into Three Characters In SSRS Expression?

Apr 22, 2015

I am using the following expression to place the month in the heading of a column.

=MonthName(Month(DateAdd("M",-1,Now)))

The expression above gives me the previous month, but I need to have it as "Jan", not "January".

expression that will give me the three character month name instead of the full month.  I've tried substituting the "M" with "MMM", but get an error.  I've also tried "Mon", but again, I
get an error.

View 4 Replies

SQL Server 2012 :: Return Values With Multiple Entries Only

May 9, 2014

Very basically, I need to return a result set based on another value and only if there are multiples of that other value.

Example. select * from mytable

Returns (--column separater)

John---1---1
John---1---1
John---2---2
John---3---3
John---3---3
John---4---4

So I want a query that would return only

John---1---1
John---1---1
John---3---3
John---3---3

The query cannot be something as simple as this:

Select * from mytable where John=1 or John=3

I have to many possible results to plan like that. I am thinking some kind of aggregate with a group.

View 9 Replies

Transact SQL :: Login Name Of User Inserting Into Table With Execute As DML Trigger?

Jun 24, 2015

Suppose you have a simple DML "execute as..." trigger that looks like this:

CREATE TRIGGER dbo.myTrigger
 ON dbo.myTable
 WITH Execute As Owner
 AFTER INSERT
AS

[code]...

which runs as "owner" so that the user inserting into "myTable" won't have the privs to edit the audit table.How does one capture the identity of the user whose insert statement caused the trigger to fire?  I've tried "current_user" and "system_user" without success when "execute as ..." is used: they each return a value not related to the identity of the user running the original insert.without an "execute as" clause, what prevents a user from adding to or editing the audit table directly?

View 6 Replies

Does NULL Inherit A Datatype After Convert

Jun 16, 2015

i have a little confusion...

DECLARE @name varchar(30)
SET @name = NULL
SELECT ISNULL(CONVERT(DATE,@name),'')
Result: 1900-01-01

But..

DECLARE @name varchar(30)
SET @name = NULL
SELECT ISNULL(@name,'')

Does NULL inherit a datatype after convert?

View 7 Replies

SQL Server Admin 2014 :: PowerShell Security Error

Jul 16, 2015

I have a SQL Server 2014 instance running on a SQL Server 2008 R2 server. The server is not clustered, it is just a stand alone SQL Server. The syspolicy_ purge_history job fails every now and then with the error message: "A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Access to the path 'PowerShell_CommandAnalysis_Lock' is denied. '. Process Exit Code -1. The step failed."

Google isn't bringing up much besides the whole"If this is a clustered server make sure you have the right server name in the command" answer, which isn't the case here. Some days this job fails and some days it succeeds. I have checked out task scheduler to see if there were any conflicts there, found nothing. Nothing in the event viewer either.

View 2 Replies

Export Data From Server To Excel

Jul 22, 2015

I faced with an issue: there is a need to export data from SQL Server to excel.How to do this? I'm on SQL Server 2008 R2

View 7 Replies

Analysis :: Excel Not Connecting To SSAS

Sep 21, 2015

I have created SSAS package successfully.

When I try to connect from excel , to SSAS Getting error message like

A Connection attempt failed because the connected party did not properly respond after a period of time or established connection failed because connected host has failed to respond.

If I connect to SSIS , I'm able to connect correctly.

Why I'm getting this error and how to overcome this?

View 4 Replies

T-SQL (SS2K8) :: To Get Top 10 From A Table

May 18, 2015

I have a more than 50 departments in my table and trying to get top 10, something I need to show in a report like top9 departments and then rest of them has to show as Others in 10th row. is it possible ?

when I use top 10 from table I am getting top 10 rows but I need the top 9 list and rest as others in 10th row.

I have table like below

Custid, department, location
1 Fin NY
2 Acc NY
3 HR MI
4
5
6

View 3 Replies

DB Design :: Maximum Number Of Columns

Jun 3, 2015

I am adding a table within my vb.net program using New datatable(tblname) function, then adding 22 columns (col01 to col22) to this table using .columns.add (colname) function without any error.The program however throws an exception when trying to assign a value to column number 14 (col14) saying this column does not belong to table tblname. Assigning a value from col01 to col13 is working fine.Is there a limitation on number of columns can be added to a table using code?

View 2 Replies

SQL 2012 :: Concatenate Column Value - Output In SSRS Report

Jul 30, 2015

I have a column name Classname and I would like to to Concatenate value.

ID ClassName
1 Class A
2 Class B
3 Class C
4 Class D

I need a output in ssrs report with title like (Class A, Class B, Class C, Class D) .

Can I do in SSRS as well ?

I tried join function in ssrs and I am getting #error join(Field!classname,",")

View 1 Replies

Transact SQL :: How To Convert Row To Column In Server

May 11, 2015

I have a store procedure , a want to convert table result from row to column .My store

CREATE PROC GET_FUNCTION @GROUP_ID CHAR(3)
AS
SELECT A.GROUP_MOD_ID, B.FUNCTION_MOD_NAME,A.ALLOW FROM
FUNCTION_GROUP A INNER JOIN FUNCTION_MOD B ON A.FUNCTION_MOD_ID=B.FUNCTION_MOD_ID
WHERE A.GROUP_MOD_ID=@GROUP_ID

And this is my result

Ok , i want to convert same. NOTE : Number of row is can more , G02   Add-Delete    1 , G02    Add-Edit    1 , ..etc. . I want it dynamic column follow row

GROUP_MOD_ID       Add      Edit      Delete       ABC       XYZ     etc....

G02                          1             1            0            0             1      

View 9 Replies

Analysis :: Attribute Relationships Impacts On Members Full Unique Name

Aug 17, 2015

Our SSAS integration didn't initially use attribute relationships.Now that our system has been running for a few years and we have bigger databases, we think we need to add them to improve performance. So we're in the process of adding them but we found out that, when attribute relationships are added, the full unique name of our members all go from something like:

[DIM].[HIERARCHY].[LEVEL].&[GRANDPARENT].&[PARENT].&[MEMBER]
to something like:
[DIM].[HIERARCHY].[LEVEL].&[MEMBER]

It looks nice and SSAS will accept the longer names fine but it will return the short ones in response to 'discovery' requests and in the XMLA response of MDX queries. This is causing problems in our low level XMLA-based modules that assume the long names in and out. is there any clean way to use attribute relationships and still have SSAS generate the long member names. We fiddled with the various documented dim/attribute properties but to no avail. It also appears that some switches are obsolete.

View 6 Replies

Transact SQL :: Pivot With Multiple Columns

Sep 1, 2015

I have one table like this.

-- drop table #temp
create table #temp(ID bigint, Description varchar(50), ET varchar(200), ET_Status varchar(50), ET_Date datetime, ET_IsValid varchar(3))

insert into #temp
select * from (values (1,'Test','A', 'Ack','08/15/2015', 'Yes'),(1,'Test','B', 'Nack','08/17/2015', 'Yes'),(1,'Test','C', 'Ack','08/21/2015', 'Yes')) a(ID, Description, ET, ET_Status, ET_Date, ET_IsValid)

I want to pivot this. My expected result look like this.

ID - Description - ET_A_Status - ET_A_Date
- ET_A_IsValid -  ET_B_Status - ET_B_Date
  - ET_B_IsValid - ET_C_Status  - ET_C_Date
-
ET_C_IsValid 

1  - Test    - 'Ack'       - '2015-08-15 00:00:00.000'  - 'Yes'   -  'Nack'  - '2015-08-17 00:00:00.000'  - 'Yes'  - 'Ack'   - '2015-08-21 00:00:00.000' -  'Yes'

View 6 Replies



Copyrights 2005-15 www.BigResource.com, All rights reserved