Microsoft SQL Server

158K

Solutions

48K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hello,
I would like to delete a Table from a database.

Name of the Table = "customer"
Name of the Database = "Sicherung_Thomas"
SQL Server 2000

I tried this:

CString str_Tablelle;
str_Tabelle="customer.dbo.Sicherung_Thomas";
 
//-----------------------------------------------------------
sql.Format("DROP TABLE %s ",str_Tabelle);
//-----------------------------------------------------------

My Error Message is: deleting is not possible because table do not exist in  system catalog

But the table is there....what do I did wrong?
Please help.
Best regards,
Thomas
0
Online Training Solution
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Hello,
i'have question about reporting service reportservices,I'm trying to connect to the reporting  web service sql server but an exception is raised, password or user are wrong error 401 unauthorized .
But i'm sure that they are correct this is how i use it :
I have imported a wsdl and i have got reportservice2005.pas.
Why i can't Authentify knowing that i can with browser how to use the methode LogOnUser() ? or THttpRio? to authtify.
this is the procedure i'm using :
procedure TfMenu.Button1Click(Sender: TObject);
 const
   defURL  = 'http://khali-pc:80/ReportServerSQL2016/ReportService2005.asmx';
var
   Report : CreateReport;
   ReportingService :  ReportingService2005Soap;
   stream           :   TFileStream;
   tailleBuffer,I     : Integer;
   UserLog          : logonUser;
   Rio1              : THttpRio;

begin
 
 Rio1 := THttpRIo.Create(self);
   Rio1.HTTPWebNode.UserName      := 'SomeDomain\SomeUser';
   Rio1.HTTPWebNode.Password      := 'xxxxxxx';

ReportingService := GetReportingService2005Soap(false, defURL, nil) ;

/////********** Second way to authenificate*********************// PS: and the first way to athentificate is to include password and user in RIO (THttpRio) above

/// second method to authetify to SSRS
//   UserLog                  :=       LogonUser.Create;
//   UserLog.userName  := 'SomeDomain\SomeUser';  
//   UserLog.password   := 'xxxxxx';
//   UserLog.authority     :='' ;
//   …
0
We are importing (Bulk Insert) of around half million records to the staging table and then to the production table.

More often column data type or size validation fails the bulk insert process from Staging table to the Production table.

I will have to redesign the process by SSIS packages by inserting all the good records from Staging to the Production and load the bad / error out data to the staging table. Those bad or error out data with proper validation message.

Validation message is based on first error out column. If the all the columns in that row are erroring, we can consider with the first column error and reload the data after fixing that data.

Example Error Message: Column PurchaseDate has invalid data

How do I achieve this complete workflow SSIS package. What are the controls, I need to use to start with?

Please :)
0
How to design a Hierarchy based Table Relation?


To give an example: Organizational Chart. I am actually working on a Product based database.  Lacking in the design.
0
Hi,
I have created user and given permssion to  db_owner for Test database.
But user complaining user can't create stored procedure or View.
I have given grant using following command but still user can't create store procedure.
What iam missing

use [App_Test]
GRANT EXECUTE ON SCHEMA::[dbo] TO [user1]
GRANT EXECUTE ON DATABASE:: App_Test  TO [user1]

USE  [App_Test]
GRANT CREATE PROCEDURE TO [user1]    
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::dbo TO [user1]
0
Hi All,

Current SQL memory settings :
Min Memory: 64000 MB
Max Memory setting : 216000 MB.
User DB size: 540 GB
In this DB server we are running SSIS & SSIS as well.

1. On a server running SQL Server 2014 with 256 GB RAM what is best practice for setting the page file size?
2. Can we resize page file as minimum 16 MB & 2000 MB?
3. If we resize page file as mentioned above, is there any impact on SQL Server performance?

The current page file settings are given below.
page file
0
We have a scenario where the local web app needs to access a remote internet based mssql instance.  I have a few questions?

Assuming the transactions are low and not high disk IO is this even possible?  There will be a 20mbps bottleneck

Is there an encrypted sql client?  If so, does this run on a different port?
0
We are importing (Bulk Insert) of around half million records to the staging table and then to the production table.

More often column data type or size validation fails the bulk insert process from Staging table to the Production table.

I will have to redesign the process by SSIS packages by inserting all the good records from Staging to the Production and load the bad / error out data to the staging table. Those bad or error out data with proper validation message.

Validation message is based on first error out column. If the all the columns in that row are erroring, we can consider with the first column error and reload the data after fixing that data.

Example Error Message: Column PurchaseDate has invalid data

How do I achieve this complete workflow SSIS package. What are the controls, I need to use to start with?

Please :)
0
 
LVL 11

Administrative Comment

by:Andrew Leniart
Hi chokka,

What you have done is made a "Post" here. To get help from the experts, you need to "Ask a Question" so that more experts are able to see that you need help. Click the Big blue button near the top of your screen.

Ask a Question

The following link also explains more about asking for help at Experts Exchange..
http://support.experts-exchange.com/customer/portal/articles/336330

Hope that's helpful.

Regards,
Andrew
EE Topic Advisor
0
Hello All,

We have SQL 2014 with Ent edition and our DB size is 340 GB. Can you please review and suggest below SQL memory settings are correct or do I need to change min or max memory settings.

Physical memory : 256GB
Min memory: 64000 MB
Max memory: 216000

We have dedicated DB (VM machine) and on same DB we are running SSIS, SSRS.

In DB server-->task manager-->we always see sql server management studio.exe, the memory utilization is 98-99%. Is there an way to reduce this value by memory settings?

Thanks in advance.
0
How to design a Hierarchy based Table Relation?


To give an example: Organizational Chart. I am actually working on a Product based database.  Lacking in the design.
0
 
LVL 11

Administrative Comment

by:Andrew Leniart
Hi chokka,

What you have done is made a "Post" here. To get help from the experts, you need to "Ask a Question" so that more experts are able to see that you need help. Click the Big blue button near the top of your screen.

Ask a Question

The following link also explains more about asking for help at Experts Exchange..
http://support.experts-exchange.com/customer/portal/articles/336330

Hope that's helpful.

Regards,
Andrew
EE Topic Advisor
0
MS Dynamics Made Instantly Simpler
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Please see the attached error message
The sql connection just isn't being made
I have tried all combinations possible
If I log onto the server with the sql instance I can login to the sql management studio with no problem with the same credentials
Surface area has been configured to allow connections
no firewalls are active
no ports blocked
I can ping the sql server from the machine which is trying to make the connection
I can get into all the shares on the machine
Capture.PNG
0
Hi ,

I have two tables, Items and Rate.
Items table having columns - itemcode,uid,itemname and rate.
Rate table having columns - itemcode, uid, rate and acntcode.

Records in Items table
Items record
Records in Rate table
Rate records
I have written a query that used the left join to display the records

select i.itemcode,i.rate,i.itemname,r.rate,r.acntcode 
from rate r left join items i on i.itemcode=r.itemcode 

Open in new window

Join Output
I Want the records in the manner that all items along with rate from Rate table and append NULL for acntcode and Rate if no record found in Rate table as explained below:

Required output
So i can retrieve all the items for a particular acntcode type.

Thanks in advance for the help.
0
When I execute a stored procedure
Is there any way to log or track what table triggers execute?
0
I have a table that has 4 triggers on it.
An api does an insert or update on the table and these triggers fire.

On that same server I have an assembly installed... which a function calls

How can I add a call to that assembly function after the LAST trigger fires?
Is there a way to determine the ORDER of the triggers...
And then should I add that function to the Trigger...??
0
Hi All

( This isn't a question as much as it's a member poll, and it's more of a process question then a code question, so points will be split across any meaningful answer. )

Does anyone have a process (not necessarily code) for auditing a data warehouse?  Specifically, a process or code that does something like this..
  • Compare total sums/counts in source data to sums/counts in target DW?
  • Compare incremental load (day, hour, etc.) sums/counts in source data to sums/counts in target DW?
  • Compare by table/column blank/NULL values in source data to same in DW?
  • (Kinda dreaming here) Use of DMV's to show metrics of how much the DW tables are used?

I've inherited a DW (really a datamart, but that's another story) that users have suspect for awhile now, and I need to nail down a process to audit this DW to verify that the data accurately reflects the source data, and communicate that in a simple way with a few more details than executive red/yellow/green.   Also I have a selfish interest in being able to communicate my progress in resolving these issues clearly.

I tried Googling it myself, and all the results were either SQL Server auditing or consulting companies advertising DW projects.

Thanks in advance.
Jim
0
Hello,

I need to create a script that can automatically create username1 and username2 but also add sysadmin role to the NT AUTHORITY\SYSTEM username. It keeps saying that I do not have access to do it. If I manually add sysadmin role to NT AUTHORITY\SYSTEM and run the script it works fine and creates both usernames and their policies and roles but I have not been able to automate the sysadmin assignment. Below is my script:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
CREATE LOGIN username1 WITH PASSWORD = 'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
EXEC sp_addsrvrolemember 'username1', @rolename = N'sysadmin';
GO
EXEC sp_addsrvrolemember 'username1', @rolename = N'dbcreator';
GO
CREATE LOGIN username2 WITH PASSWORD = 'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
EXEC sp_addsrvrolemember 'username2', @rolename = N'sysadmin';
GO
EXEC sp_addsrvrolemember 'username2', @rolename = N'dbcreator';

Thank you in advance!
0
Hi All,

New to SQL and wondering what's the best way to organize my data in the following format.

                       1/1/2016 | 2/1/2016 | 3/1/2016 | 4/1/2016
Territory 1          500            1000            750             500
Territory 2          350             900             820             700
Territory 3          600             800             800             200
etc.

My existing code right now:

SELECT Territory_ID, SUM(CAST(Total_Quantity as DECIMAL(18,2)) AS 'Total Quantity by Territory'
FROM dbo.database
WHERE Total_Quantity is not null
GROUP BY Territory_ID

I have a column named "Period" in this database that contains 13 different values 1/1/2016 - 1/1/2017 by month, is there a way to break these 13 individual dates in "Period" and spread them across columns to break my data up. Because right now with my current code, the output just displays Territory_ID and then the Total_Quantity for respective Territory.

Any help would be greatly appreciated, thank you!
0
I have a table that stores

UID  (BIGINT)     ExternalID (VarChar(50))   Active(BIT)

I need a select
Groups by ExternalID
And BOTH records are active
0
Hi Experts,

I am trying to write a value to a field when it is either null or blank.

I have tried:
Select
IsNull(NULLIF(SiteCode, ''),'NON-VHA') as SITECODE,
From Site_CTE

but it does not work

Please help and thanks
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I am having issues with TMG 2010 trying to install SQL server 2008. TMG 2010 server is associated with SQL Express. How do I unlink the SQL Express and link the new SQL server 2008? How do I keep a copy of the ISA logs on TMG 2010 and SQL server 2008? I have read most of the Microsoft TechNet files on the TMG 2010.
0
Hi EE,

I would like to transfer all data from a prod instance DB to a UAT instance DB. However, I would like to omit tables between the two DB where the schemas are different. Is there a way to do this via the GUI if not what would be the command to check the compare the schemas before transfer?

I am using SQL server 2012.

Any assistance is appreciated.

Thank you.
0
I have a stored procedure that I know is probably set up wrong but what is causing problems consistently is the attempt to return the row number.  I have the following code but I'm not sure since the table wasn't specifically designed with an identity row that this will work. Sorry, I'm new to trying to pull in row and not an expert at testing stored procedures either.
I was hoping to test executing the sp and return all declared output columns. I attached a file with a screenshot of the table columns.

 
ALTER PROCEDURE [dbo].[cpsp_preg_num]
(
--@Design Bit,
@pi_enterprise_id  CHAR(5),
@pi_practice_id  CHAR(4),
@pi_cur_person_id  UNIQUEIDENTIFIER,
@txt_date			VARCHAR(10) OUTPUT,
@txt_acttext		VARCHAR(75) OUTPUT,
@txt_method			VARCHAR(30) OUTPUT,
@txt_comments		VARCHAR(100) OUTPUT,
@txt_completedby    VARCHAR(100) OUTPUT,
@txt_time			INT OUTPUT,
@new_identity		INT OUTPUT
)
AS

-- SELECT @new_identity = SCOPE_IDENTITY()
 select top 1
 @txt_date = txt_date,		
@txt_acttext = txt_acttext, 	
@txt_method	= txt_method,	
@txt_comments	= txt_comments,
@txt_completedby = txt_completed_by,    
@txt_time		=	txt_time,
@new_identity	 = SCOPE_IDENTITY()
From cpsp_ext_ 
where txt_pregNum = 1
order by create_timestamp asc

Open in new window

cpsp-table.jpg
0
Is there a SQL command that ignores errors and instructs it to move on to the next statement?

example:

begin
INSERT INTO OPERATION_MASTER (OPM_AUTO_KEY,SYSUR_AUTO_KEY,OPERATION_ID,DESCRIPTION,BER_THRESHOLD,FLAT_RATE,PARTS_FLAT_PRICE,LABOR_FLAT_PRICE,MISC_FLAT_PRICE,FLAT_RATE_LABOR,DEFAULT_REPAIR,TURN_AROUND_TIME,IN_CAPABILITIES,CONSOLIDATE,TEMPLATE_TYPE,YIELD_PERC,MAX_LOT_SIZE,PART_COST,LABOR_COST,FO_COST,VO_COST,OSV_COST,COSTING_METHOD,FIXED_UNIT_COST,SEQUENCE,ACTIVE_FLAG,MISC_COST,MAIN_ASSY,HISTORY_FLAG,LABOR_FIXED_COST,DO_COST_UPDATE,ACTIVE_VERSION,GLOBAL_DESIGN,CONVERTED,BATCH_JOB,SYSCM_AUTO_KEY,BLOCK_FLAG,BGM_AUTO_KEY,PNM_AUTO_KEY,WWT_AUTO_KEY) VALUES(G_OPM_AUTO_KEY.NEXTVAL,1,'233N3223-1027 STD BIC','233N3223-1027 STD BIC',0,'F',0,0,0,'F','F',0,'F','F','W',0,0,0,0,0,0,0,'A',0,0,'F',0,'F','F',0,'F','F','F','T','F',1,'F','',38249,'');
INSERT INTO OPERATION_MASTER (OPM_AUTO_KEY,SYSUR_AUTO_KEY,OPERATION_ID,DESCRIPTION,BER_THRESHOLD,FLAT_RATE,PARTS_FLAT_PRICE,LABOR_FLAT_PRICE,MISC_FLAT_PRICE,FLAT_RATE_LABOR,DEFAULT_REPAIR,TURN_AROUND_TIME,IN_CAPABILITIES,CONSOLIDATE,TEMPLATE_TYPE,YIELD_PERC,MAX_LOT_SIZE,PART_COST,LABOR_COST,FO_COST,VO_COST,OSV_COST,COSTING_METHOD,FIXED_UNIT_COST,SEQUENCE,ACTIVE_FLAG,MISC_COST,MAIN_ASSY,HISTORY_FLAG,LABOR_FIXED_COST,DO_COST_UPDATE,ACTIVE_VERSION,GLOBAL_DESIGN,CONVERTED,BATCH_JOB,SYSCM_AUTO_KEY,BLOCK_FLAG,BGM_AUTO_KEY,PNM_AUTO_KEY,WWT_AUTO_KEY) VALUES(G_OPM_AUTO_KEY.NEXTVAL,1,'233N3223-1016 STD BIC','233N3223-1016 STD 

Open in new window

0
I have a select that puts things in the proper order

What I need to do is when there are two ExternalID records
And the second record has an ExternalID2 <> '0'
AND the first record IndividualTypeID = 1
Set active in the first record = 0

Screen print of results and query follows
SP

SELECT   TOP 3070 i.IndividualTypeID ,
         i.Active ,
         i.ExternalID ,
         i.ExternalID2 ,
         ROW_NUMBER() OVER ( PARTITION BY i.ExternalID
                             ORDER BY i.ExternalID2 ASC
                           ) AS RN
FROM     Individuals i
         JOIN dbo.ewSalesSitesToIndividuals s ON i.IndividualID = s.IndividualID
         JOIN SalesSites ss ON s.SalesSiteID = ss.SalesSiteID
WHERE    ss.BusinessID IN (   SELECT BusinessID
                              FROM   ewBusinesses
                              WHERE  BusinessName LIKE 'Breckenridge Grand Vacations' + '%'
                          )
         AND i.DateAdded > GETDATE() - 7
         AND ISNULL(i.ExternalID, '0') NOT IN ( '', '0' )
ORDER BY i.ExternalID ,
         i.ExternalID2 ,
         i.Active ,
         i.IndividualTypeID;

Open in new window

0
I am trying to use the Import 32 Bit Data Wizard as I need to connect to a Visual Fox Pro Database, is this Possible, I was able to do this using SQL 2008, we have since upgraded our SharePoint Infrastructure and need to be able to read that data in?

John
0

Microsoft SQL Server

158K

Solutions

48K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.